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 Delimiter Formatter and running the export with it. The table that contains the rows to be 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 tutorial3/import.xml with the JDBCImporter, several rows can be created. You can find the oracle creation script in the samples directory under the filename : 'tutorial3/createtable_ora.sql'.
Now that the database is setup, you can examine the architecture to see how the Delimiter Formatter is used during the export.
The Delimiter Formatter is used during the export for two functions: combine each rows' column values into a single string representing the row (the formatValues() method) and writing the string into the output file (the writeNextRow() method).
The JDBCExporter requires that each Delimiter Formatter be a Java Bean like object. All <property> tags defined inside the '<delimiter>' will be passed to the appropriate set method. For example, with the CSV Delimiter Formatter, you define a property called 'columnDelimiter' inside the '<delimiter>' tag. The JDBCExporter looks for the 'setColumnDelimiter' method in the CSVDelimtierFormatter.java and calls it with the property value.
After the Delimiter Formatter is created, the JDBCExporter will set the output writer (the setWriter() method) before calling the two methods. The JDBCExporter will call the formatValues() (followed by writeNextRow()) until all rows are exported whereupon it calls the finish() method.
The Delimiter Formatter that you will be creating will be similar to the CSV Delimiter Formatter found under the package net.sourceforge.jdbcexporter.formatter. Here are the requirements for the Fixed Delimiter Formatter :
The first thing to do is create the class FixedDelimiterFormatter that implements the DelimiterFormatter interface.
import java.io.IOException;
import java.io.Writer;
import net.sourceforge.jdbcexporter.DelimiterFormatter;
import net.sourceforge.jdbcimporter.ColumnValue;
public class FixedDelimiterFormatter implements DelimiterFormatter
{
protected Writer writer;
public void setWriter(Writer output)
{
this.writer = output;
}
public String formatValues(ColumnValue[] values)
{
/* todo implement this method */
return null;
}
public void writeNextRow(String row) throws IOException
{
/* todo implement this method */
}
public void finish() throws IOException
{
/* todo implement this method */
}
}
Initial Code
You would like the user to configure the Fixed Delimiter Formatter with the number ranges for each column. So, the FixedDelimiterFormatter class needs to have a method to set the ranges. The method that we will use will take a double-array of ints. The first index will define the column index, while the second index will define the start and end point of the column data :
...
public class FixedDelimiterFormatter implements DelimiterFormatter
{
...
protected int[][] columnPositions;
...
public void setColumnPositions( int[][] positions )
{
columnPositions = positions;
}
}
columnPositions property
You can define the XML in the export config file that will be used to construct and configure the Fixed Delimiter Formatter. The XML will contain one property element named 'columnPositions' that will have a comma-separated list of number ranges. Here is an example:
<delimiter type="tutorial_fixed">
<property name="columnPositions" value="1-5,6-20,25-35"/>
</delimiter>
Sample XML for Fixed Delimiter Formatter
You now need to convert the comma-separated list of number ranges into a double array of integers. Following the Java Bean specification, you will have to create two classes: 'FixedDelimiterFormatterBeanInfo' and 'FixedDelimiterFormatterColumnPositionsEditor'. The 'FixedDelimiterFormatterBeanInfo' simply tells the JDBCExporter that there is a property called 'columnPositions' and that it should use the 'FixedDelimiterFormatterColumnPositionsEditor' to convert the string representation into a double array of integers. Here are both classes :
import java.beans.SimpleBeanInfo;
import java.beans.PropertyDescriptor;
import java.beans.BeanDescriptor;
public class FixedDelimiterFormatterBeanInfo extends SimpleBeanInfo
{
PropertyDescriptor[] descriptors;
BeanDescriptor descriptor;
public FixedDelimiterFormatterBeanInfo()
throws java.beans.IntrospectionException
{
try
{
descriptors = new PropertyDescriptor[1];
descriptors[0] = new PropertyDescriptor( "columnPositions", FixedDelimiterFormatter.class, null, "setColumnPositions" );
descriptors[0].setPropertyEditorClass( FixedDelimiterFormatterColumnPositionsEditor.class );
}
catch ( java.beans.IntrospectionException e )
{
e.printStackTrace();
throw e;
}
descriptor = new BeanDescriptor(FixedDelimiterFormatter.class);
}
public BeanDescriptor getBeanDescriptor()
{
return descriptor;
}
public PropertyDescriptor[] getPropertyDescriptors()
{
return descriptors;
}
}
Bean Info for Fixed Delimiter Formatter
import java.beans.PropertyEditorSupport;
import java.util.StringTokenizer;
public class FixedDelimiterFormatterColumnPositionsEditor extends PropertyEditorSupport
{
public void setAsText( String val ) throws IllegalArgumentException
{
StringTokenizer tokenizer = new StringTokenizer( val, "," );
int[][] realColumnDefs = new int[tokenizer.countTokens()][2];
int index = 0;
while (tokenizer.hasMoreTokens())
{
String columnDef = tokenizer.nextToken();
StringTokenizer rangeTokenizer = new StringTokenizer( columnDef, "-" );
if ( rangeTokenizer.countTokens() != 2 )
{
throw new IllegalArgumentException( "Invalid column range '"+columnDef+"'" );
}
try
{
realColumnDefs[index][0] = Integer.parseInt( rangeTokenizer.nextToken() );
if ( realColumnDefs[index][0] < 1 )
{
}
realColumnDefs[index][1] = Integer.parseInt( rangeTokenizer.nextToken() );
if ( realColumnDefs[index][1] < 1 )
{
}
if ( realColumnDefs[index][0] >= realColumnDefs[index][1] )
{
throw new IllegalArgumentException(
"Invalid column range : "+realColumnDefs[index][0]+" >= "+realColumnDefs[index][1]);
}
}
catch ( NumberFormatException n )
{
throw new IllegalArgumentException( "Invalid column range '"+columnDef+"'" );
}
index++;
}
setValue( realColumnDefs );
}
}
Property Editor for columnPositions editor
The 'FixedDelimiterFormatterColumnPositionEditor' parses the comma-separated string and validates each individual range. If the comma-separated string or the individual range is invalid then an IllegalArgumentException will be thrown. Otherwise, a properly configured Fixed Delimiter Formatter will be returned.
Now that the Fixed Delimiter Formatter is properly setup, the two methods need to be implemented. The first method 'formatValues()' is implemented by storing each columns value into the appropriate range:
...
public class FixedDelimiterFormatter implements DelimiterFormatter
{
...
public String formatValues(ColumnValue[] values)
{
StringBuffer nextLine = new StringBuffer("");
for ( int i = 0; i < columnPositions.length; i++ )
{
int start = columnPositions[i][0] - 1;
int length = columnPositions[i][1] - columnPositions[i][0] + 1;
String val = values[i].getString();
if ( nextLine.length() < start )
{
for ( int j = 0; nextLine.length() < start; j++ )
{
nextLine.append(' ');
}
}
for ( int j = 0; j < length; j++ )
{
char nextChar = ' ';
if ( val.length() > j )
{
nextChar = val.charAt(j);
}
if ( nextLine.length() > start + j )
{
nextLine.setCharAt(start+j, nextChar );
}
else
{
nextLine.append(nextChar);
}
}
}
return nextLine.toString();
}
...
}
formatValues Implementation
The second method 'writeNextRow()' will write the string into the output writer and the 'finish()' method does nothing.
...
public class FixedDelimiterFormatter implements DelimiterFormatter
{
...
public void writeNextRow(String row) throws IOException
{
writer.write( row );
writer.write( '\n' );
}
...
}
writeNextRow Implementation
This ends the tutorial for creating the custom Delimiter Formatter. The full source code of the Fixed Delimiter Formatter (with FixedDelimiterFormatterBeanInfo and FixedDelimiterFormatterColumnPositionsEditor) are found under the package samples.delimiterformatter. What follows now is the instructions on how to use the custom Delimiter Formatter during the export. If you have read through the first tutorial then you may wish to skip to the delimiter formatter definition 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 : 'tutorial3/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 the default export engine.
The 'target' attribute must contain the data file location.You will be exporting data into a dat file (called 'employee_export.dat'). The dat file will be a fixed delimited file.
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 custom Delimiter Formatter 'FixedDelimiterFormatter'. To indicate this, you must choose an identifier for the custom Delimiter Formatter (ex. 'tutorial_fixed') and set the 'type' attribute's value, inside the '<delimiter>' tag, to that identifier. The specific delimiter formatter information is found inside the '<delimiter>' tag.
For the Fixed Delimiter Formatter, you will need to specify the following information (as '<property>' tags): the number ranges for each column (in the property named 'columnPositions' ). The data file will have the following number ranges : 1-4,5-14,15-24,25-34,35-38,39-48,49-53,54-58 (note the first column is 1), the delimiter formatter definition will look like this :
<delimiter type="tutorial_fixed">
<property name="columnPositions" value="1-4,5-14,15-24,25-34,35-38,39-48,49-53,54-58"/>
</delimiter>
Sample XML for Fixed 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.dat"> <delimiter type="tutorial_fixed"> <property name="columnPositions" value="1-4,5-14,15-24,25-34,35-38,39-48,49-53,54-58"/> </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 3
Since you are using the custom Delimiter Formatter (Fixed Delimiter Formatter), you will have to create a property file with one entry that maps the identifier to the full name of the class that implements the DelimiterFormatter interface. The entry's key should start with 'delimiter.' (this indicates that the custom component is a Delimiter Formatter). It should look like this:
delimiter.tutorial_fixed=samples.delimiterformatter.FixedDelimiterFormatterProperty File Entry for Fixed Delimiter Formatter
You will also have to include an extra jar file in the classpath before you can use the custom Delimiter Formatter (the jar file 'jdbcimporter-samples.jar' under the directory 'lib' contains the custom Delimiter Formatter).
You can run the export by issuing the following command (assuming that the export definition and property file is in the current directory and is called 'export.xml' and 'custom_export.properties', respectively):
java net.sourceforge.jdbcexporter.Exporter export.xml custom_export.properties
If all goes well then the log file and the 'employee_export.dat' file should be created. In the log file there should be an informational message indicating that all rows were exported.