Please make sure you have the appropriate libraries in your classpath (including the JDBC driver used to connect to your database) before starting the tutorials.
In this tutorial, you'll learn the basics of creating a new Connection Def and running the JDBCImporter with it. The table that will contain the rows imported is called employee and it has the following columns :
| employee | |
|---|---|
| Name | Type |
| id | number(6) |
| firstname | varchar(10) |
| lastname | varchar(10) |
| jobdescription | varchar(10) |
| managerid | number(6) |
| startdate | date |
| salary | number(9,2) |
| department | number(6) |
Make sure that these table(s) are created in the database that you'll be importing data. You can find the oracle creation script in the samples directory under the filename : 'tutorial7/createtable_ora.sql'.
Now that the database is setup, you can examine the architecture to see how the Connection Def is used during the import.
The Connection Def is used during the import for to allocate or retrieve a JDBC Connection. The JDBCImporter requires that each Connection Def be a Java Bean like object. All <property> tags defined inside the '<connection>' will be passed to the appropriate set method. For example, with the JDBC Connection Def, you define a <property> tag with the name 'driver' inside the '<connection>' tag. The JDBCImporter looks for the 'setDriver' method in the JDBCConnectionDef.java and calls it with the property value.
After the Connection Def is created, the JDBCImporter will call the 'getConnection()' method to retrieve a JDBC Connection that it will use for the import. Once the import is complete, the JDBCImporter will call the 'releaseConnection' method to return or dispose of the JDBC Connection.
The Connection Def that you will be creating will be similar to the JDBC Connection Def found under the package net.sourceforge.jdbcimporter.connection. Here are the requirements for the Xalan Connection Def :
The first thing to do is create the class XalanConnectionDef that implements the ConnectionDef interface.
import java.sql.Connection;
import net.sourceforge.jdbcimporter.ConnectionDef;
public class XalanConnectionDef implements ConnectionDef
{
public Connection getConnection()
{
/* @todo implement this method */
return null;
}
public void releaseConnection(Connection con)
{
/* @todo implement this method */
}
}
Initial Code
The user will have to configure the Xalan Connection Def with the following properties : the JDBC driver, the connection url, the username, the password, the connection pool name, the minimum number of connections to allocate (when creating the connection pool) and whether to cleanup the pool after the import has finished.
...
public class XalanConnectionDef implements ConnectionDef
{
...
protected String driver;
protected String url;
protected String username;
protected String password;
protected String name = "ext-pool";
protected int min = 2;
protected boolean cleanupPool = true;
public void setCleanupPool(boolean b)
{
cleanupPool = b;
}
public void setMin(int i)
{
min = i;
}
public void setName(String string)
{
name = string;
}
public void setDriver( String newDriver )
{
driver = newDriver;
}
public void setUsername( String newUsername )
{
username = newUsername;
}
public void setPassword( String newPassword )
{
password = newPassword;
}
}
driver, url, username, password, pool name, min and cleanupPool properties
You can now define the XML in the import config file that will be used to construct and configure the Xalan Connection Def. The XML will contain six property elements named : 'driver' (the JDBC Driver), 'url' (the JDBC Connection url), 'username' (the username), 'password' (the password), 'name' (the name of the connection pool) and 'min' (the minimum number of connections to allocate). Here is an example:
<connection type="tutorial_xconn">
<property name="driver" value="oracle.jdbc.driver.OracleDriver"/>
<property name="url" value="jdbc:oracle:thin:@localhost:1521:orcl"/>
<property name="username" value="scott"/>
<property name="password" value="tiger"/>
<property name="min" value="3"/>
<property name="name" value="xalan-pool"/>
</connection>
Sample XML for Xalan Connection Def
Now that the Xalan Connection Def is properly setup, the two methods need to be implemented. The first method 'getConnection()' will check the Xalan Connection Pool Manager for a connection pool. If it exists it will return a connection from the pool. Otherwise, it will create a connection pool and store it into the Xalan Connection Pool Manager.
import java.sql.SQLException;
import org.apache.xalan.lib.sql.ConnectionPool;
import org.apache.xalan.lib.sql.ConnectionPoolManager;
import org.apache.xalan.lib.sql.DefaultConnectionPool;
...
public class XalanConnectionDef implements ConnectionDef
{
...
protected ConnectionPoolManager poolManager = new ConnectionPoolManager();
public Connection getConnection()
{
ConnectionPool pool = poolManager.getPool( name );
if ( pool == null )
{
DefaultConnectionPool cp = new DefaultConnectionPool();
cp.setDriver( this.getDriver() );
cp.setURL( this.getUrl() );
cp.setUser( this.getUsername() );
cp.setPassword( this.getPassword() );
cp.setMinConnections( min );
cp.setPoolEnabled( (min > 1));
poolManager.registerPool( name, cp );
pool = cp;
created = true;
}
try
{
return pool.getConnection();
}
catch ( SQLException ex )
{
ex.printStackTrace();
}
return null;
}
...
}
getConnection Implementation
The second method 'releaseConnection()' will release the JDBC Connection and cleanup the connection pool (if it was created by the Connection Def and the cleanup flag is set to true).
...
public class XalanConnectionDef implements ConnectionDef
{
...
public void releaseConnection(Connection con)
{
ConnectionPool pool = poolManager.getPool(name);
try
{
pool.releaseConnection( con );
}
catch ( SQLException ex )
{
ex.printStackTrace();
}
if ( created && cleanupPool )
{
poolManager.removePool( name );
}
}
...
}
releaseConnection Implementation
This ends the tutorial for creating the custom Connection Def. The full source code of the Xalan Connection Def is found under the package samples.connection. What follows now is the instructions on how to use the custom Connection Def during the import. If you have read through the first tutorial then you may wish to skip to the connection definition section. The other sections are the same as the first tutorial.
Now that the database is setup, you can examine the import XML config file that will be used (in the samples directory under the filename : 'tutorial7/import.xml'). The file begins with the standard XML document declaration followed by the '<import>' tag. This tag indicates that there is an import to be processed. There are seven attributes specified on the '<import>' tag: the 'log' attribute, the 'bad' attribute, the 'commitCount', the 'batchCount' attribute, the 'preSQLFile' attribute, the 'postSQLFile' attribute and the 'trimValues' attribute. The 'log' attribute specifies a filename into which JDBCImporter writes all audit, error, and warnings that occur during the import. The 'bad' attribute specifies a filename into which JDBCImporter writes data that was not properly imported into the database. The 'commitCount' attribute specifies how many rows to import before calling commit on the JDBC Connection. The 'batchCount' attribute specifies how many rows to import before calling executeBatch on the import engine (when the JDBC driver supports batch mode). By default, the 'commitCount' and 'batchCount' attributes are set 1, auto commit is turned on and batch mode is not used. The 'preSQLFile' and the 'postSQLFile' attributes specify filenames that contain sql statements to be executed before and after the import , respectively. The 'trimValues' attribute specifies whether strings values read from the Delimiter Parser are trimmed (ie. remove leading and trailing whitespace). By default, it is set to false.
There are two parts inside the '<import>' tag that define how and where the data is imported: the connection definition and the entity definitions.
The connection definition begins with '<connection>' tag and contains the information needed to connect to the database. In this tutorial, you will be using the custom Connection Def to retrieve a connection from a connection pool. To indicate this, you must choose an identifier for the custom connection def (ex 'tutorial_xconn') and set the 'type' attribute's value, inside the '<connection>' tag, to that identifier. The specific connection information is found inside the '<connection>' tag as '<property>' tags. A '<property>' tag has two attributes: 'name' specifies the name of the property and 'value' specifies the string value of the property. For the Xalan Connection Def, you will need to specify the following information: the driver class name (with the property name 'driver'), the connection url (with the property name 'url'), the username (with the property name 'username'), the password (with the property name 'password'), the connection pool name (with the property name 'name'), the minimum number of connections to allocate (with the property name 'min'). The following is an example of the connection definition :
<connection type="tutorial_xconn">
<property name="driver" value="oracle.jdbc.driver.OracleDriver"/>
<property name="url" value="jdbc:oracle:thin:@localhost:1521:orcl"/>
<property name="username" value="scott"/>
<property name="password" value="tiger"/>
<property name="min" value="3"/>
<property name="name" value="xalan-pool"/>
</connection>
Sample XML for Xalan Connection Def
Since you will be importing data into one table, there will be only one entity definition. In general, you will need an entity definition for each table that you will be importing data. Remember to specify the entity definitions in the order that the import should occur. For example, if table 'ingredient' depends on table 'recipe' (ie. has a foreign key), the entity definition of table 'recipe' should be placed before the entity definition of table 'ingredient'. Every entity definition begins with '<entity>' tag.
The 'table' attribute must contain the name of the table. Optionally, you can further specify the table by providing values for the 'schema' and the 'catalog' attributes.
To specify a custom import engine to process the entity, you may add the 'engine' attribute, whose value is the classname of the import engine.In this tutorial, you will be using the default import engine.
The 'source' attribute must contain the data file location. From looking at the sample data (found under 'samples/tutorial3/employee.xml'), you will see that the file is a XML file.
There are three parts inside the '<entity>' tag : the delimiter parser definition, row translator definition, and the list of columns found in the data file.
The delimiter parser definition begins with the '<delimiter>' tag and contains the information needed to parse the input file into a set of rows that will be imported into the table.In this tutorial, you will be using the custom Delimiter Parser 'XML Delimiter Parser' created in tutorial 4. To indicate this, you must choose an identifier for the custom delimiter parser (ex. 'tutorial_xml') and set the 'type' attribute's value, inside the '<delimiter>' tag, to that identifier. The specific delimiter parser information is found inside the '<delimiter>' tag. For the XML Delimiter Parser, you will need to specify the following information (as '<property>' tags): the XSL filename (in the property named 'XSLFile' ). An XSL has been created for this tutorial (its name is employee.xsl), so the delimiter parser definition will look like this :
<delimiter type="tutorial_xml">
<property name="XSLFile" value="employee.xsl"/>
</delimiter>
Sampel XML for XML Delimiter Parser
The row translator definition is optional and begins with the '<translator>' tag. It contains the information needed to translate each row's values and may add, remove column values or skip the whole row. In this tutorial, you will not be using a row translator. Therefore the '<translator>' does not appear as a child inside the '<entity>' tag.
The final portion of the entity definition is the list of columns that are to be imported from the input file into the database. The list of columns should be the same order as they appear in the input file. Each column is defined inside the '<column>' tag. The name of the column must appear in the 'name' attribute of the '<column>' tag. Optionally, the java.sql.Type may be specified in the 'SQLType' attribute of the '<column>'.You will be letting the JDBC Importer figure out most of the column types (except for dates) in the database, so the 'SQLType' attribute is omitted except for the 'startdate' column. Here is an example of how the list of columns are defined in the import definition:
<column name="id"></column> <column name="firstname"></column> <column name="lastname"></column>Sample XML for List of Columns
By now, the import definition should look like this (with your appropriate connection information):
<import log="import.log bad="import.bad">
<connection type="tutorial_xconn">
<property name="driver" value="oracle.jdbc.driver.OracleDriver"/>
<property name="url" value="jdbc:oracle:thin:@localhost:1521:orcl"/>
<property name="username" value="scott"/>
<property name="password" value="tiger"/>
<property name="min" value="3"/>
<property name="name" value="xalan-pool"/>
</connection>
<entity table="employee" source="employee.xml">
<delimiter type="tutorial_xml">
<property name="XSLFile" value="employee.xsl"/>
</delimiter>
<column name="id"></column>
<column name="firstname"></column>
<column name="lastname"></column>
<column name="jobdescription"></column>
<column name="managerid"></column>
<column name="startdate" SQLType="DATE"></column>
<column name="salary"></column>
<column name="department"></column>
</entity>
</import>
Sample XML for Tutorial 7
Since you are using the custom Connection Def (Xalan Connection Def) and the custom Delimiter Parser (XML Delimiter Parser), you will have to create a property file with two entries that map the identifier to the full name of the classes that implement the Connection Def interface and the Delimiter Parser interface. The entry's key for the Connection Def should start with 'connection.' (this indicates that the custom component is a connection def). The entry's key for the Delimiter Parser should start with 'delimiter.' (this indicates that the custom component is a delimiter parser). It should look like this:
connection.tutorial_xconn=samples.connection.XalanConnectionDef delimiter.tutorial_xml=samples.delimiterparser.XMLDelimiterParserProperty File Entries for Xalan Connection Def and XML Delimiter Parser
You will also have to include an extra jar file in the classpath before you can use the custom Connection Def and the custom Delimiter Parser (the jar file 'jdbcimporter-samples.jar' under the directory 'lib' contains the both classes).
You can run the import by issuing the following command (assuming that the import definition and property file is in the current directory and is called 'import.xml' and 'custom.properties', respectively):
java net.sourceforge.jdbcimporter.Importer import.xml custom.properties
If all goes well then the two log files should be created. In the normal log file there should be an informational message indicating that all rows were imported. In the bad log file there should be a heading for the import table.