Using SQL Helpers

Connecting to the Database

Doing a simple query with the SQL package can be more complicated than just opening a file and reading or writing data. The reason is that the SQL package relies on Java Database Connection (JDBC). To get any JDBC connection running, you need a database server, which means more installation and configuration issues. The SQL package downloads and uses the Axion and HSQL databases. Both of these databases are Java-based relational databases that use the SQL language, and they are both lighter weight in distribution terms than a database like Oracle. However, the SQL package is not limited to these databases and uses the following list of supported databases: Axion, DB2, HSQL, Microsoft SQL Server , MySQL , Oracle , SAP SQL , and Sybase . Once you have established the JDBC connection parameters, you can create a simple demo, which connects to a database. An example is shown in Listing 5.1.

Listing 5.1

DataSourceWrapper datasource = new DataSourceWrapper();
datasource.setDriverClassName("com.mysql.jdbc.Driver");
datasource.setJdbcURL("jdbc:MySQL://localhost/test ");
datasource.setUserName(null);
datasource.setPassword(null);

Connection connection = datasource.getConnection();
JdbcModelReader modelReader = new 
JdbcModelReader(connection);
Database database = modelReader.getDatabase();

DynaSql dynaSql = new DynaSql(datasource, database);

When you connect to a database using the SQL package, you must take two major steps. The first step is to connect the SQL package to the database at the JDBC level. In Listing 5.1, we took care of this by setting the correct parameters for the class DataSourceWrapper . We showed you four methods of the class DataSourceWrapper in Listing 5.1 so that you could see the basics. These four methods basically show all of the functionality that is needed for most situations. The method setDriverClassName is the name of Java driver we'll use to connect to the database. The method setJdbcURL assigns the database connection string. Lastly, the methods setUserName and setPassword define the username and password. The class DataSourceWrapper can then be used to retrieve a connection to the database using the method getConnection . The connection retrieved is based on the JDBC class Connection . However, it is not necessary to call the method getConnection because other parts of the SQL package will do that automatically.

Once a connection has been established, the second step is to read the metadata from the database. The metadata can be read from an XML configuration file or from the JDBC connection directly. In Listing 5.1, the metadata is read from the JDBC connection using the class JdbcModelReader . Once the connection and metadata have been read, you can instantiate the class DynaSql . The class DynaSql is the central class to use when you are performing SQL-type operations such as querying for records or inserting new data.

Simplifying Connection Management

Once the developer has made the connection to the database, he will want to query, add, modify, or delete data from the database. The SQL package is a library that simplifies all of the SQL tasks by adding helper routines to manage the mundane tasks. For example, the manipulation of SQL tables is abstracted into an XML file, and the generation of result sets is managed using Java Beans.

When you use the SQL package, you use several dependencies and only a few classes. The typical dependencies are JDBC URL, where the database is located, JDBC class name, and database type. The SQL package uses the database type to determine a SQL builder implementation. These dependencies are defined in a user -implemented class that subclasses a yet-to-be-defined SQL package helper class, as shown in Listing 5.2.

Listing 5.2

class MySQLImpl extends SQLHelper {
public String getURL() {
return "jdbc:MySQL://localhost/test";
}
public String getDBClass() {
return "com.mysql.jdbc.Driver";
}
public String getDatabaseType() {
return "mysql";
}
}

In Listing 5.2, the class MySQLImpl implements three methods that are defined as abstract in the subclassed class SQLHelper . We will explain the details of the class SQLHelper in various places throughout the source code, whenever it is appropriate. For now, let's just say that the code presented in Listing 5.1 has been abstracted into the methods defined in Listing 5.3.

Listing 5.3

protected DataSource createDataSource() throws Exception {
DataSourceWrapper wrapper = new DataSourceWrapper();
wrapper.setDriverClassName( getDBClass());
wrapper.setJdbcURL( getURL());
wrapper.setUserName( getUsername());
wrapper.setPassword( getPassword());
return wrapper;
}

public Database createJDBCDatabase() throws Exception {
DataSource datasource = createDataSource();
Connection connection = datasource.getConnection();
JdbcModelReader modelReader =
new JdbcModelReader(connection);
return modelReader.getDatabase();
}

public Database createXMLDatabase() throws Exception {
DatabaseReader reader = new DatabaseReader ();
return (Database)reader.parse(
new FileInputStream(getXMLDBMetaData()));
}

The method createDataSource defined in Listing 5.3 prepares a connection to the database. The method createJDBCDatabase and createXMLDatabase are used to provide metadata about the database. The difference between the two methods is that createJDBCDatabase retrieves the metadata information from the JDBC driver, whereas createXMLDatabase retrieves it from an XML configuration file.

The SQL package requires meta information to delete, update, and manipulate records from tables. The package needs metadata because it is fairly difficult to reference a column by name if you do not have the metadata that contains the name of the column.

When metadata is retrieved from the JDBC driver, it is a simple process because the class JdbcModelReader does all of the hard work. The class JdbcModelReader reads the data from the database and then generates a model using the classes defined in the package org.apache.commons.sql.model . However, the JDBC approach requires that the information exist in the first place. It would not be possible to create a table using the JDBC metadata since the table would not exist.

The solution the SQL package uses is to define an XML file that contains a number of XML elements that represent a database. It is then possible to read in the XML file and use that configuration to create a database. The XML configuration file is written using neutral data types. Various XML-based mapping files are available to the SQL package. Contained within the mapping files are data-type mappings from the neutral XML configuration file to the database-expected data type. The end programmer doesn't generally modify the XML mapping files.

Configuring the XML Database

In Listing 5.3, the method createXMLDatabase loads and processes an XML configuration file. The XML configuration file contains the database configuration that defines the various tables in the database. Listing 5.4 shows a simple XML configuration file.

Listing 5.4

<database name="bookstore">
<table name="author">
<column name="author_id" type="INTEGER"
primaryKey="true" required="true"/>
<column name="name" type="VARCHAR"
size="50" required="true"/>
<column name="organization" type="VARCHAR"
size="50" required="false"/>
</table>
</database>

In Listing 5.4, the configuration file is based on the idea of defining a database with a number of tables that have various columns . The configuration file items are all based on XML tags like database, table, and column. The configuration file is read in using some configuration libraries that will be explained in later chapters. The XML structure is converted into a number of Java objects, all of which are located in the package org.apache. commons.sql.model . Here are all of the possible XML tags (and they are all from the package org.apache.commons.sql.model ); the child bullets represent XML attributes associated with the XML tag:

• database : This represents a database and is the root element of a configuration file. The only acceptable child XML element is table . This tag cross-references with the class Database .

  • name : This is used to define the database's name. The name is not used to create a database but is purely for identification purposes.

• table : This represents a table in the database. The only acceptable child XML elements are column , foreign-key , index , and unique . This tag cross-references with the class Table . Note that the class Table has more properties, but the package doesn't use them. For example, you can set the attribute catalog and have it read by the configuration libraries, but that data will not be used.

  • name : This is used to define the name of the table. You use this identifier when creating the tables.

  • description : This is used to define the purpose or description of the table.

• column : This represents a table column. This tag cross-references with the class Column.

  • name : This is used to define the identifier of the column.

  • primaryKey : This is a Boolean value used to indicate if the column is a primary key for indexing purposes.

  • required : This is a Boolean value used to indicate if the column must have a value associated with it when a new row is added.

  • autoIncrement : This is a Boolean value used to indicate if the column is an auto-increment type. If this attribute is set, then the type of the column must be integer- based.

  • type : This is used to define the column's data type. Be sure that the database recognizes the data type, and that the data type has a mapping, so that an appropriate JDBC data type can be used. The class TypeMap contains references to the most commonly used data types.

  • size : This is an integer-based value that defines the size of the data type.

  • defaultValue : This is the initial value assigned to a record. When a new record is created and no value is assigned to the record, the value of defaultValue is used.

  • scale : When you use the DECIMAL and NUMERIC SQL data types, you can specify a number format. The attribute scale is one part of the number format. It indicates that a number is allowed to have as many decimal places as indicated by the value of scale. For example, if the value of scale is 2, then the number 89.99 is acceptable, whereas 89.999 is not.

  • precisionRadix : When you use the DECIMAL and NUMERIC SQL data types, you can specify a number format. The attribute precisionRadix is one part of the number format. It indicates the total number of spaces that a value takes. For example, if the value of precisionRadix is 5, then the number 99.99 is valid, whereas 999.99 is not. This particular number is invalid because the positive indicator (which is not shown) is part of the precisionRadix , as is the decimal point. When the sign indicator and numbers before and after the decimal point are added in terms of space, they must be equal to or less than the length specified by precisionRadix .

• index : This is used to specify a specific index used to organize a specific table. The only acceptable child XML element is index-column . This tag cross-references with the class Index .

  • name : This specifies the name of the index, which is used to reference the index in the database.

• index-column : This is used to specify a column used to create an index. The name of the column must equal the name of a column described in the parent XML element table . This tag cross-references with the class IndexColumn .

  • name : This is the name of the column used in the index.

• foreign-key : This is used to specify a specific foreign key that associates two column values between the parent XML element table and another table defined in the parent XML element database . The only acceptable child XML element is reference . This tag cross-references with the class ForeignKey .

  • foreignTable : This is the name of the foreign table being referenced.

• reference : This is used to cross-reference two columns in a foreign key relationship.

  • local : This is the name of a table column in the parent XML element table .

  • foreign : This is the name of a table column in the parent XML element foreign-key referenced table.

Going back to Listing 5.4, we can use the XML configuration file to generate a database and its tables using Listing 5.5.

Listing 5.5

Database database = createXMLDatabase()
DataSource datasource = createDataSource();
SqlBuilder sqlBuilder =
SqlBuilderFactory.newSqlBuilder( getDatabaseType());
DDLExecutor executor = new DDLExecutor( datasource, 
sqlBuilder);
executor.createDatabase( database, true);

When you're creating a database and its associated tables, the database mappings discussed earlier are provided by the interface SqlBuilder . The interface SqlBuilder is a Commons Bridge implementation, where the implementations reside in the package org.apache.commons.sql.builder . The class method SqlBuilderFactory.newSqlBuilder is a factory used to instantiate the correct implementation. The class DDLExecutor ties together the JDBC connection to the database, the database metadata, and the database implementation details. Using the method createDatabase , all of the classes discussed in Listing 5.5 are pulled together and used to create a database. The second parameter to the method is a Boolean used to indicate whether or not the tables should be dropped if they already exist. In Listing 5.5, the value is true and therefore the tables will be dropped.

Using a configuration file is a nice idea because it allows you to create a database on short notice. However, using a configuration file to create a database can also take a long time; Listing 5.5 has to gather and process data from various sources. Also problematic is that Listing 5.5 can easily drop tables and add new ones, which could happen if the developer is not careful. Another potential problem occurs when a Database Administrator (DBA) updates the metadata in the database, but does not update the XML configuration file. At that point, the program using the XML configuration file and the database will not be in synch, which could cause data-corruption issues. Using an XML configuration file is very appropriate where systems create their own local databases. In a corporate environment, it's probably not an ideal solution to use an XML configuration file, except if you created an administrative tool based on the XML configuration file. At that point, the administrator would always be in charge and could keep the database metadata in synch with the XML configuration metadata.

Running a Batch Command

The SQL language contains commands that return data as well as those that process, but do not return, data (such as the SQL DELETE or CREATE TABLE commands). In either case, the only output that is returned from the command is a success or a failure. If a failure is returned, then the cause and details of the error are returned. The technique used to run a batch command is to use the class defined in Listing 5.2 to retrieve the class DDLExecutor and then execute the command, as shown in Listing 5.6.

Listing 5.6

MySQLImpl impl = new MySQLImpl();
DDLExecutor executor = impl.createDDLExecutor();
executor.evaluateBatch(
"CREATE TABLE 'example_table'(" +
"'integer_value' int(11) default '0'," +
"'string_value' varchar(100) default ''," +
"'double_value' double default '0')");

In Listing 5.6, we have used the method evaluateBatch to create a table based on a SQL statement. The method works, but as of this writing the method evaluateBatch was a protected method and not public. A patch has been put in to make the method public, but we don't know whether or not the patch has been accepted. If the patch is rejected, the programmer will have to use Listing 5.7, which has exposed the method evaluateBatch .

Listing 5.7

MySQLImpl impl = new MySQLImpl();
impl.evaluateBatch(
"CREATE TABLE 'example_table'(" +
"'integer_value' int(11) default '0'," +
"'string_value' varchar(100) default ''," +
"'double_value' double default '0')");

We use this type of batch processing to allow specific commands to be executed. The SQL command demonstrated in Listings 5.6 and 5.7 shows how to create a table in a database. This type of SQL command is a good example of what not to do. The problem with the CREATE TABLE command is that the command could be very specific to the database used. If we use the CREATE TABLE command, we'll be defeating the purpose of the SQL package. Other SQL commands ”such as SELECT and INSERT ”are not as database specific and hence could make effective use of the method evaluateBatch .

Manipulating Database Data

Certain SQL commands allow you to add, update, or remove data. In SQL terms, we're talking about the commands INSERT , UPDATE , and DELETE . However, in terms of the SQL package, we mean using dynamic beans that allow you to add, modify, or delete data. In the SQL package, there is a notion of property bag, which stores data using key value pairs. The property bag is a Java class that can be used in conjunction with SQL commands executed using the SQL builder class. In Listing 5.8, a record is inserted into the SQL table example_table .

Listing 5.8

MySQLImpl impl = new MySQLImpl();
DynaSql dynaSql = impl.createJDBCDynaSql();
DynaBean bean = dynaSql.newInstance( "example_table");
bean.set( "integer_value", new Integer( 1234));
bean.set( "string_value", "hello how are you doing");
bean.set( "double_value", new Double( 1234.567890));
dynaSql.insert( bean);

Regardless of which SQL data manipulation command is called, you need an instance of the class DynaSql (as is done in Listing 5.8). If you want to insert data into a specific table, you need to retrieve a reference to the table using the method newInstance . The method newInstance has one parameter, which is the name of the table, and it returns an instance to the class DynaBean . The class DynaBean is a sort of property bag where specific key value pairs are stored. In Listing 5.8, the key value pairs are column names and their associated values. Notice that when a numeric value is assigned, the classes Integer and Double are used. This is because the class DynaBean manipulates objects and not primitives like int and long . To add the contents of the class DynaBean , you call the method insert , where the parameter of the method is the instance of class DynaBean .

The technique that the method insert in Listing 5.8 uses is introspection of the Dyna-Bean to generate a SQL command. Once the SQL command has been generated, it is executed. In the same fashion, the method delete will generate a SQL DELETE command. The SQL DELETE command has an additional requirement in that the record has to be specified. The SQL package searches for the foreign key collection and uses the foreign keys to specify which records to delete. If the table does not have a foreign key collection, you can't use the SQL package to delete or update records.

Using a query is very similar to inserting data, except that the query requires a string that contains a SQL SELECT command. When the query returns a result set, the result set is converted by the SQL package to an iterator. Listing 5.9 shows a sample query.

Listing 5.9

Iterator iter = dynaSql.query(
"select integer_value, linked_value from " +
"example_table, linked_table " +
"where example_table.integer_value = " +
"linked_table.linked_value");
while( iter.hasNext() == true) {
DynaBean foundBean = (DynaBean) iter.next();
System.out.println( "Integer value is " +
foundBean.get( "integer_value"));
}

In Listing 5.9 the method query accepts any SQL command that returns a dataset. Two tables are linked together to generate the output. The method query returns an instance of the class Iterator . The Iterator class instance contains the entire dataset of the SQL query in terms of individual DynaBean class instances. To iterate the individual classes, you use the methods hasNext and next . To retrieve an individual value from the DynaBean class instance, you use the method get , where the first parameter is the name of the column to retrieve.

For the method query, the SQL command can be the SQL SELECT command, or it can be a stored procedure. In any case, SQL package does not translate or fix up the SQL command. When you use a stored procedure, you will have to be database-specific . A stored procedure is called like a function call, where there is function name and some parameters. Listing 5.9 is an example where the query string is a complete string. When you call a stored procedure, the parameters need to be converted into a complete string. This process is time-consuming and potentially error prone. Another solution is to use parameterized queries, as shown in Listing 5.10.

Listing 5.10

List params = new ArrayList();
params.add( new Integer( 1234));
Iterator iterpara = dynaSql.query(
"Select * from example_table where integer_value = ?",
params);
while( iterpara.hasNext() == true) {
DynaBean foundBean = (DynaBean)iterpara.next();
System.out.println( "Integer value is " +
foundBean.get( "integer_value"));
}