DZone Snippets: JDBC code

How to produce daily database table dumps in CSV format

ejboy (Fyodor Kupolov) — Fri, 16 Nov 2007 21:08:12 GMT

The following code demonstrates how to produce CSV files with dynamic
file name pattern based on a current day. The produced files have the following naming format:
TABLE_NAME_MM_DD_YYYY.csv







     

        table_name=test

    

          classpath="ojdbc14.jar" user="scott" password="tiger"/>

    

     

        SELECT * FROM ${table_name}

Use Scriptella ETL to run the example.

See How to execute an ETL file from command line, Ant or directly from Java .

Importing XML into a database with Scriptella ETL

ejboy (Fyodor Kupolov) — Sun, 18 Feb 2007 19:07:06 GMT

The following Scriptella ETL simple usage example imports RSS file into a database table.







    

    

classpath="hsqldb.jar"/>

    

        /rss/channel/item

Here is the full version of the example described above. It creates an RSS table, downloads rss file, inserts rss records into a database, converts rss.xml to a plain text file and saves it to rss.txt.







    

    

    

    

    

        /rss/channel/item

Script to insert BLOB from file into a database

ejboy (Fyodor Kupolov) — Sun, 18 Feb 2007 18:26:57 GMT

Scriptella ETL allows inserting files into a database. This is achieved by a simple bind variables extension syntax ?{file ...}.
The following sample initializes table of music tracks. Each track has a DATA field containing a file loaded from an external location. File song1.mp3 is stored in the same directory as etl.xml and song2.mp3 is loaded from the web:

Copy table from one database to another

ejboy (Fyodor Kupolov) — Tue, 13 Feb 2007 18:06:19 GMT

This Scriptella ETL script copies all rows from Src_Table to Dest_Table.
Src_Table contains the following columns: id, first_name, last_name
Dest_Table contains the following columns: id, name
The name column of the Dest_Table is produced by a concatenation of first_name and last_name from the Src_Table
This example demonstrates HSQLDB-To-Oracle copy procedure, although it works between virtually any databases.







                classpath="hsqldb.jar" user="sa"/>

                classpath="ojdbc14.jar" user="scott" password="tiger"/>

  

  

      SELECT * FROM Src_Table --Selects all rows

Load CSV data into a database (Scriptella ETL tool)

ejboy (Fyodor Kupolov) — Tue, 13 Feb 2007 09:42:42 GMT

This example demonstrates usage of Scriptella ETL Tool to load CSV data into a database table.

Input CSV file data.csv:



id,priority,summary,status

1,Critical,NullPointerException in Main class,Open

5,Low,"Checkstyle, PMD, Findbugs issues",Reopened

7,Low,Maven integration,Open

10,High,SPI API,Closed

The CSV loading script has the following content:







  

        classpath="ojdbc14.jar" user="scott" password="tiger"/>

Use RegEx to filter CSV data:





    

    ,,,open|reopened

JDBC 101

JohnMunsch (John Munsch) — Wed, 24 May 2006 23:54:13 GMT

// Note the use of a prepared statement. You can gain a couple of different benefits
// from using a prepared statement versus a conventional statement. One is that the
// database can compile the statement once and simply insert different values into the
// variables each time you call it. For frequently used functions this can result in
// improved performance. The second advantage is where the values to be passed in might
// be changable by hostile users (e.g. a search term taken from a webpage). In those
// cases assembled SQL strings are suseptible to attack by cleverly phrased inputs but
// prepared statements are not because they are _never_ interpreted as actual SQL, only
// as values for variables.
//
// See my other code snippets "Getting A Data Source From Tomcat" and "Data Source 101"
// for more information on how to get the data source you pass into a routine like this.



    /**

     * In this case the Record object is something we are saving to the database.

     */

    public void persistRecord(DataSource dataSource, 

        Record record) throws Exception {



        Connection conn = null;

        PreparedStatement st = null;

        

        int retVal = 0;

        

        try {

            conn = dataSource.getConnection();

            

            st = conn.prepareStatement("insert into test(a, b) values(?, ?)");



            st.setInt(1, record.getA());

            st.setString(2, record.getB());

            

            int records = st.executeUpdate();

        } catch (SQLException se) {

            log.error(se, se);

            throw se;

        } finally {

            UtilityJDBC.closeSQLClasses(conn, st, null);   

        }

    }



    /**

     * NOTE!! if you close a statement the associated ResultSet

     * is closed too so you should copy the contents to some other

     * object. The result set is invalidated also if you recycle a

     * Statement and try to execute some other query before the result

     * set has been completely examined.

     * 

     * @param conn

     * @param st

     * @param rs

     * @throws SQLException

     */

    public static void closeSQLClasses(Connection conn, Statement st,

        ResultSet rs) {



        try {

            if (rs != null) {

                rs.close();

            }



            if (st != null) {

                st.close();

            }



            if (conn != null) {

                conn.close();

            }

        } catch (SQLException se) {

            log.error(se, se);

        }

    }

Open a JDBC connection

mikewilsonuk (Mike Wilson) — Sun, 14 May 2006 20:31:20 GMT

This opens a JDBC connection givine a driver class name, database URL, user name and password. It is possible this method is deprecated and some newer procedure is preferred. I need to look into that.



    /*

    ** driverClass is the JDBC driver class name as a String.

    */



    try

    {

	Class.forName (driverClass);

	connection = DriverManager.getConnection (url,

	    userName, password);

	connection.setAutoCommit (false);

    }

    catch (SQLException ex)

    {

	/*

	** "Connect error"...

	*/

    }

    catch (java.lang.ClassNotFoundException ex)

    {

	/*

	** "Driver error"...

	*/

    }

Execute arbitary SQL in JDBC & get column names etc from the meta data

mikewilsonuk (Mike Wilson) — Sun, 14 May 2006 20:25:52 GMT

This will execute an arbitary SQL string in JDBC and extract the column names. I extracted this code from a much larger module I wrote years ago, so it isn't complete and hasn't been tested. You have been warned.



    /*

    ** connection is a java.sql.Connection obtained in the usual way.

    */



    PreparedStatement statement =

	connection.prepareStatement (sqlString);

    statement.setMaxRows (configModel.getMaxRows ());



    if (statement.execute ())

    {

	ResultSet resultSet = statement.getResultSet ();

	ResultSetMetaData metaData = resultSet.getMetaData ();



	/*

	** Get the column names.

	*/



	for (int i = 0 ; i < metaData.getColumnCount () ; i++)

	{

	    int columnType = metaData.getColumnType (i + 1);

	    String columnName = metaData.getColumnLabel (i + 1);



	    /*

	    ** Do something with columnType & columnName.

	    */



	}



	/*

	** Fetch the rows.

	*/



	while (resultSet.next ())

	{

	    String value;



	    for (int i = 0 ; i < metaData.getColumnCount () ; i++)

		value = resultSet.getString (i + 1);

	}

    }

    else

    {

	/*

	** Query was probably update/insert/delete.

	*/



	int rowCount = statement.getUpdateCount ();

    }



    statement.close ();

Getting A Data Source From Tomcat

JohnMunsch (John Munsch) — Tue, 28 Feb 2006 04:13:48 GMT

// You can setup a data source in tomcat using a context file
// or you can set one up using the Administration web pages
// as well. Either way you do it, here is the simple code to
// get the data source from Tomcat so you can start pulling
// out database connections.



// Obtain our environment naming context

Context initCtx = new InitialContext();

Context envCtx = (Context) initCtx.lookup("java:comp/env");



// Look up our data source by the name we gave it when we created it. 

// In this case that's "jdbc/EmployeeDB".

DataSource ds = (DataSource) envCtx.lookup("jdbc/EmployeeDB");

Data Source 101

JohnMunsch (John Munsch) — Tue, 28 Feb 2006 04:10:53 GMT

// Way too many people hard code their JDBC connections to
// the database server(s). Use data sources to connect to
// your databases. They can be setup easily in any major
// Java application server as well as JSP/servlet engines
// like Tomcat and Spring.
//
// If you aren't running inside a container that provides
// you with easy access to a data source though, you can
// include the Jakarta Commons DBCP library and create a
// data source as shown in the code below. The result is
// a data source complete with pooling, caching, etc. that
// you can pass to other code that just expects to receive
// a data source.



BasicDataSource dataSource = new BasicDataSource();



dataSource.setDriverClassName(System.getProperty("driverClassName"));

dataSource.setUsername(System.getProperty("username"));

dataSource.setPassword(System.getProperty("password"));

dataSource.setUrl(System.getProperty("url"));