|
JavaXT
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Database UtilitiesThe javaxt.sql package is used to connect to a databases, execute queries (SQL), view and update records, and browse database metadata. Here's a brief overview of some of the key classes found in the javaxt.sql package. Database ClassThe javaxt.sql.Database class is used to encapsulate all of the information required to connect to a database. This class is often used in conjunction with the javaxt.sql.Connection Class. Once a connection to the database is opened, you can use this class to execute queries and retrieve metadata (schema, primary keys, list of other databases, etc). Here's a simple example of how to instantiate the database object and set connection parameters to a database server.
javaxt.sql.Database database = new javaxt.sql.Database();
database.setDriver(javaxt.sql.Driver.PostgreSQL);
database.setHost("localhost:5432");
database.setUserName("postgres");
database.setPassword("password");
database.setName("test");
For file databases like SQLite or H2, you can specify the path the the database file using the setHost() method.
java.io.File db = new java.io.File(dir, name);
javaxt.sql.Database database = new javaxt.sql.Database();
database.setDriver("H2");
database.setHost(db.toString().replace("\\", "/"));
And of course, you can also use a JDBC connection string like this:
javaxt.sql.Database database = new javaxt.sql.Database("jdbc:sqlserver://localhost;database=Northwind;user=sa;password=MyPassword");
Open Database ConnectionThe primary purpose of the Database class is to help connect to a database. Example:
//Open connection
javaxt.sql.Connection conn = database.getConnection();
//Close the connection
conn.close();
Automatically Closing Database ConnectionsBeginning with javaxt-core 2.x you can now open a connection using an enhanced try/catch statement. The javaxt.sql.Connection implemented the AutoCloseable interface so the connection will be automatically closed when the try/catch is complete.
try (javaxt.sql.Connection conn = database.getConnection()){
//Do something!
}
catch(java.sql.SQLException e){
}
Executing Raw SQL CommandsThe javaxt.sql.Connection object is typically used to execute queries and scroll through records. However, you can also use the javaxt.sql.Connection object to execute raw SQL statements without invoking the Recordset object. Typically, these SQL commands don't yield a resultset (e.g. Create/Drop Table, Create/Drop Index, Grant Permissions, Backup/Restore, etc). Here's a really simple example of how to create a table in SQLServer using a live javaxt.sql.Connection.
try (javaxt.sql.Connection conn = database.getConnection()){
conn.execute(
"CREATE TABLE EMPLOYEE(" +
"ID INTEGER IDENTITY(1,1) NOT NULL UNIQUE," +
"FIRST_NAME nvarchar(50)," +
"LAST_NAME nvarchar(50)," +
"PHONE_NUMBER nvarchar(12)," +
"CONSTRAINT pk_employee PRIMARY KEY (ID)" +
");"
);
}
catch(java.sql.SQLException e){
}
You can delete the newly created table with a simple "DROP" command. Example:
conn.execute("DROP TABLE EMPLOYEE");
Execute QueriesBeginning with javaxt-core 2.x you can now execute queries directly from a database instance. Example:
for (javaxt.sql.Record record : database.getRecords("select * from employee")){
System.out.println(record.get("first_name") + " " + record.get("last_name"));
}
Under the hood the above method opens a database connection in a try/catch statement and calls the getRecord() in the javaxt.sql.Connection class like this:
try (javaxt.sql.Connection conn = database.getConnection()){
for (javaxt.sql.Record record : conn.getRecords("select * from employee")){
System.out.println(record.get("first_name") + " " + record.get("last_name"));
}
}
Single Record QueriesIn the query example above, we are iterating through multiple records. However, in some cases you may only need one record. Example:
Long count = database.getRecord("select count(*) from employee").get(0).toLong();
Creating/Updating RecordsCreating and updating records is handled by the javaxt.sql.Recordset which can be accessed via the javaxt.sql.Connection class like this:
try (javaxt.sql.Connection conn = database.getConnection()){
try (javaxt.sql.Recordset rs = conn.getRecordset("select * from employee where id=1", false)){
if (rs.EOF){
//Create new record
rs.addNew();
rs.setValue("first_name", "Peter");
rs.setValue("last_name", "Smith");
rs.setValue("phone_number", "555-555-5555");
rs.setValue("date_created", new java.util.Date());
rs.setValue("last_modified", new java.util.Date());
rs.update();
Integer id = rs.getGeneratedKey().toInteger();
System.out.println("Created new contact. ID is " + id);
}
else{
//Update phone number and last modified date
rs.setValue("phone_number", "555-555-5555");
rs.setValue("last_modified", new java.util.Date());
rs.update();
System.out.println("Updated contact " + rs.getValue("id"));
}
}
}
catch(java.sql.SQLException e){
}
Connection PoolingThe javaxt.sql package includes a lightwieght, standalone Java JDBC connection pool manager. The javaxt.sql.ConnectionPool can be instantiated directly from the javaxt.sql.Database class like this:
//Set general connection info to the database server
javaxt.sql.Database database = new javaxt.sql.Database();
database.setDriver(javaxt.sql.Driver.PostgreSQL);
database.setHost("localhost:5432");
database.setUserName("postgres");
database.setPassword("password");
database.setName("test");
//Set connection pool size
database.setConnectionPoolSize(100);
//Initialize the connection pool
database.initConnectionPool();
Once the connection pool is initalized you can get a connection from the pool directly from the javaxt.sql.Database class like this:
try (javaxt.sql.Connection conn = database.getConnection()){
//Do something!
}
catch(java.sql.SQLException e){
}
Note that the getRecord(), getRecords(), and getRecordset() methods in the javaxt.sql.Database class will use connections from the connection pool once initialized. Learn more about connection pooling here. Table MetadataOnce a connection to the database is established, you can retrieve schema information (tables, columns, keys, etc). Here's a simple example of how to print all tables and columns in a database.
for (javaxt.sql.Table table : database.getTables()){
System.out.println("\r\n" + table.getName());
System.out.println("----------------------");
for (javaxt.sql.Column column : table.getColumns()){
System.out.println(column.getName() + " (" + column.getType() + ")");
}
}
SQL ParserThe javaxt.sql.Parser class is used to parse SQL Select statments and return the constituent parts.
String sqlString =
"SELECT Customers.FirstName, Customers.LastName, SUM(Sales.SaleAmount) AS SalesPerCustomer " +
"FROM Customers LEFT JOIN Sales ON Customers.CustomerID = Sales.CustomerID " +
"GROUP BY Customers.FirstName, Customers.LastName;";
javaxt.sql.Parser sqlParser = new javaxt.sql.Parser(sqlString);
System.out.println("Columns:");
for (javaxt.sql.Parser.SelectStatement selectStatement : sqlParser.getSelectStatements()){
System.out.println(" - " + selectStatement.getColumnName());
}
System.out.println("Tables:");
for (String tableName : sqlParser.getTables()){
System.out.println(" - " + tableName);
}
System.out.println("Where:");
for (javaxt.sql.Parser.WhereStatement where : sqlParser.getWhereStatements()){
System.out.println(" - " + where);
}
DriversOne of the key connection properties required to connect to a database is the driver information. The javaxt.sql.Driver class is prepackaged with the following driver information that you can use in the setDriver() method in the Database class.
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
It is imperative that you close the connection after you are done using it.
Otherwise, you can quickly exhaust the available connections on the server.