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 JDBCExporter export XML config file and running the export from the command line. The table that will contain the rows exported 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 exporting data and that you have several rows of data. By using the tutorial6/import.xml with the JDBCImporter, several rows can be created. You can find the oracle creation script in the samples directory under the filename : 'tutorial6/createtable_ora.sql'.
Now that the database is setup, you can examine the architecture to see how the Export Engine is used during the export.
The Export Engine is used during the export for retrieving the rows from the database that will be formatted by the DelimiterFormatter. It implements eight methods: setConnection, getConnection, setEntityDef, init, exportRow, and cleanup. The setConnection and getConnection methods are used to set/get the JDBC connection used by the Export Engine. The setEntityDef method indicates that the following calls to exportRow should read the database table defined in the given EntityDef. The init method is called after setEntityDef and before any calls to the exportRow are made. It should initialize any resources that the Export Engine needs. The exportRow method exports the array of columns from the database or null when all rows have been read from the database. After all rows are processed, the cleanup method is called so the Export Engine can free the resources that it initialized.
The Export Engine that you will be creating will insert a row into a different table that has a primary key and a timestamp of when the row was exported.
The first thing to do is create one class : StatusEngine that implements the ExportEngine interface.
import java.sql.*;
import net.sourceforge.jdbcexporter.ExportEngine;
import net.sourceforge.jdbcexporter.ExportEntityDef;
import net.sourceforge.jdbcimporter.ColumnValue;
public class StatusEngine implements ExportEngine
{
public void setConnection(Connection con)
{
}
public Connection getConnection()
{
return null;
}
public void setEntityDef(ExportEntityDef entityDef)
{
}
public void init() throws SQLException
{
}
public void cleanup() throws SQLException
{
}
public ColumnValue[] exportRow() throws SQLException
{
return null;
}
}
Initial Code
The implementation of setConnection, getConnection and setEntityDef are straightforward enough.
...
public class StatusEngine implements ExportEngine
{
Connection connection;
ExportEntityDef entityDef;
public void setConnection(Connection con)
{
connection = con;
}
public Connection getConnection()
{
return connection;
}
public void setEntityDef(ExportEntityDef entityDef)
{
this.entityDef = entityDef;
}
}
set/getConnection, setEntityDef Implementation
The init and cleanup methods are more complicated since they need to initialize and cleanup two statements: a select statement and an insert prepared statement. The select prepared statement is simple enough (the column names are put in the order defined in the EntityDef). For the insert prepared statement, the import engine examines the meta data for a table called 'employeestatus' and searches for the columns in that table. It then initializes an array to store the mapping between the position of the column in the insert statement and its position in the array defined in the EntityDef.
The implementation of the cleanup method is shorter since it just has to close the two statements.
import java.util.List;
import java.util.ArrayList;
...
public class StatusEngine implements ExportEngine
{
...
PreparedStatement insertStatement;
int exportDateIdx = -1;
int[] insertColumnMap;
Statement selectStatement;
ResultSet resultSet;
...
public void init() throws SQLException
{
ExportColumnDef[] columnDefs = entityDef.getColumns();
StringBuffer sql = new StringBuffer("SELECT ");
for ( int i = 0; i < columnDefs.length; i++ )
{
sql.append( columnDefs[i].getName() );
if ( i < columnDefs.length - 1 )
{
sql.append( ", " );
}
}
sql.append( " FROM ");
sql.append( entityDef.getTable() );
if ( entityDef.getWhereClause() != null &&
!"".equals( entityDef.getWhereClause() ) )
{
sql.append (" WHERE ");
sql.append ( entityDef.getWhereClause() );
}
selectStatement = connection.createStatement();
// System.out.println("SQL: "+sql.toString());
resultSet = selectStatement.executeQuery( sql.toString() );
String catalog = entityDef.getCatalog();
String schema = entityDef.getSchema();
String table = entityDef.getTable() + "status";
List columnNames = getColumnNames( catalog, schema, table );
if ( columnNames.size() == 0 )
{
if ( catalog != null ) catalog = catalog.toUpperCase();
if ( schema != null ) schema = schema.toUpperCase();
if ( table != null ) table = table.toUpperCase();
columnNames = getColumnNames( catalog, schema, table );
}
if ( columnNames.size() == 0 )
{
if ( catalog != null ) catalog = catalog.toLowerCase();
if ( schema != null ) schema = schema.toLowerCase();
if ( table != null ) table = table.toLowerCase();
columnNames = getColumnNames( catalog, schema, table );
}
if ( columnNames.size() == 0 )
{
System.out.println("Could not find entity : '"+entityDef.getTable()+"status'" );
return;
}
else
{
System.out.println("Column names for entity : '"+entityDef.getTable()+"status' : "+columnNames );
}
List mappings = new ArrayList();
StringBuffer insertStmtSql = new StringBuffer("insert into "+entityDef.getTable()+"status (");
for ( int i = 0; i < columnDefs.length; i++ )
{
String columnName = columnDefs[i].getName();
if ( columnNames.contains( columnName.toLowerCase() ) )
{
if ( mappings.size() > 0 )
{
insertStmtSql.append(",");
}
mappings.add( new Integer(i) );
insertStmtSql.append( columnName );
}
}
if ( mappings.size() == 0 )
{
System.out.println("No matching column names");
return;
}
if ( !columnNames.contains( "exportdate" ) )
{
System.out.println("Column 'exportdate' not found");
return;
}
insertStmtSql.append( ", exportDate ) values ( ");
insertColumnMap = new int[ mappings.size() ];
for ( int i = 0; i < insertColumnMap.length; i++ )
{
insertColumnMap[i] = ((Integer) mappings.get(i)).intValue();
if ( i > 0 )
insertStmtSql.append( ",");
insertStmtSql.append("?");
}
exportDateIdx = mappings.size();
insertStmtSql.append( ",? )");
System.out.println("Insert Stmt : "+insertStmtSql.toString());
insertStatement = connection.prepareStatement( insertStmtSql.toString() );
}
protected List getColumnNames( String catalog, String schema, String table ) throws SQLException
{
DatabaseMetaData dbMeta = connection.getMetaData();
System.out.println("Catalog : '"+catalog+"'" );
System.out.println("Schema : '"+schema+"'" );
System.out.println("Table : '"+table+"'" );
ResultSet rset = dbMeta.getColumns(
catalog,
schema,
table,
null
);
List columnNames = new ArrayList();
while ( rset.next() )
{
columnNames.add( rset.getString("COLUMN_NAME").toLowerCase() );
}
rset.close();
return columnNames;
}
public void cleanup() throws SQLException
{
resultSet.close();
selectStatement.close();
if ( insertStatement != null ) insertStatement.close();
}
}
init, cleanup Implementation
The exportRow method is more complicated because it must perform two tasks : get the next row from the database and insert a new row into the 'employeestatus' table.
The exportRow method also uses the JDBCParameterHelper class. The JDBCParameterHelper class is a utility class that exposes two methods: a method to retrieve a column value froma result set and a method to set a column value in a prepared statement.
...
public class StatusEngine implements ExportEngine
{
...
JDBCParameterHelper engineHelper = new JDBCParameterHelper();
...
public ColumnValue[] exportRow() throws SQLException
{
if (!resultSet.next())
{
return null;
}
ExportColumnDef[] columnDefs = entityDef.getColumns();
ColumnValue[] values = new ColumnValue[columnDefs.length];
for ( int i = 0; i < values.length; i++ )
{
values[i] = engineHelper.getColumn( resultSet, i+1, columnDefs[i] );
}
if ( insertStatement == null )
{
return values;
}
try
{
for ( int i = 0; i < insertColumnMap.length; i++ )
{
engineHelper.setColumn( insertStatement, i+1, columnDefs[insertColumnMap[i]], values[insertColumnMap[i]] );
}
insertStatement.setTimestamp( exportDateIdx+1, new Timestamp(System.currentTimeMillis()) );
insertStatement.executeUpdate();
if ( !connection.getAutoCommit() )
{
connection.commit();
}
}
catch ( MalformedDataException e )
{
throw new SQLException("Could not insert row into status table");
}
return values;
}
}
exportRow Implementation
This ends the tutorial for creating the custom ExportEngine. The full source code of the StatusEngine is found under the package samples.importengine. What follows now is the instructions on how to use the custom ExportEngine during the export.
If you have read through the first tutorial then you may wish to skip to the Running the Export section. The other sections are the same as the first tutorial.
Now that the database is setup, you can examine the export XML config file that will be used (in the samples directory under the filename : 'tutorial6/export.xml'). The file begins with the standard XML document declaration followed by the '<export>' tag. This tag indicates that there is an export to be processed. There is one attribute specified on the '<export>' tag: the 'log' attribute. The 'log' attribute specifies a filename into which JDBCExporter writes all audit, error, and warnings that occur during the export.
There are two parts inside the '<export>' tag that define how and where the data is exported: 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 JDBC DriverManager to initialize a connection to the database. To indicate this, the 'type' attribute's value, inside the '<connection>' tag, is 'jdbc'. 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 JDBC DriverManager, 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 following is an example of the connection definition :
<connection type="jdbc">
<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"/>
</connection>
Sample XML for JDBC Connection Def
Since you will be exporting data from one table, there will be only one entity definition. In general, you will need an entity definition for each table that you will be exporting data. 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 export engine to process the entity, you may add the 'engine' attribute, whose value is the classname of the export engine. In this tutorial, you will be using a custom export engine defined at the export level.
The 'target' attribute must contain the data file location.You will be exporting data into a csv file (called 'employee_export.csv'). The csv file will have 8 columns separated by the ',' character (similar to the file found under 'samples/tutorial1/employee.csv').
There are two parts inside the '<entity>' tag : the delimiter formatter definition and the list of columns that will be exported to the data file.
The delimiter formatter definition begins with the '<delimiter>' tag and contains the information needed to format a set of rows that will be exported from the table into the output file. In this tutorial, you will be using the CSV Delimiter Formatter. To indicate this, the 'type' attribute's value, inside the '<delimiter>' tag, is 'csv'. The specific delimiter formatter information is found inside the '<delimiter>' tag.
For the CSV Delimiter Formatter, you will need to specify the following information (as '<property>' tags): the string that delimits a column (in the property named 'columnDelimiter'), the string that encloses a column (optional, in the property named 'enclosedDelimiter'), whether the string that encloses a column is optional (in the property named 'enclosedOptional', it must have a value of 'true' or 'false'). Since, the data file will only have a column delimiter (',' is the string separating the columns), the delimiter formatter definition will look like this :
<delimiter type="csv">
<property name="columnDelimiter" value=","/>
</delimiter>
Sample XML for Delimiter Formatter
The final portion of the entity definition is the list of columns that are to be exported from the database into the output file. The list of columns should be the same order as they will appear in the output 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 Exporter 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 export definition:
<column name="id"></column> <column name="firstname"></column> <column name="lastname"></column>Sample XML for List of Columns
By now, the export definition should look like this (with your appropriate connection information):
<export log="export.log >
<connection type="jdbc">
<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"/>
</connection>
<entity table="employee" target="employee_export.csv">
<delimiter type="csv">
<property name="columnDelimiter" value=","/>
</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>
</export>
Sample XML for Tutorial 6
Since there is a custom export engine, you will have to include it in the classpath before you run the export (the jar file 'jdbcimporter-samples.jar' under the directory 'lib' contains the custom export engine). You must also define a system property 'jdbcexporter.engine' that defines the class name of the custom export engine. You can run the export by issuing the following command (assuming that the export definition is in the current directory and is called 'export.xml'):
java -Djdbcexporter.engine=samples.exportengine.StatusEngine net.sourceforge.jdbcexporter.Exporter export.xml
If all goes well then the log file and the 'employee_export.csv' file should be created. In the log file there should be an informational message indicating that all rows were exported.