Dr. Dobb's is part of the Informa Tech Division of Informa PLC

This site is operated by a business or businesses owned by Informa PLC and all copyright resides with them. Informa PLC's registered office is 5 Howick Place, London SW1P 1WG. Registered in England and Wales. Number 8860726.


Channels ▼
RSS

JVM Languages

A JDBC Wrapper — In Python!


Aug01: A JDBC Wrapper — In Python!

By C.K. Tan

C.K. can be contacted at [email protected].


While generally straightforward, database access via JDBC can be verbose and mundane, making the likelihood of errors always a concern. To simplify the process and lessen the chance for errors, I'll present in this article a Python-based framework that wraps around JDBC. This framework (available electronically; see "Resource Center," page 5) is based upon a connection pool, transaction class, and JDBC code generator. For the purpose of illustration, I use an Oracle database with the table EMPLOYEE(id number, name varchar(50), salary number(9, 2), hired_date timestamp).

Listing One presents Insert/Update/Select/Delete queries on the EMPLOYEE table using regular JDBC calls. Contrast this to Listing Two, in which I use the Python-based framework for the same queries. As you can see, the framework results in considerable code savings, not to mention improved type checking. The execute() method of update queries and the getOneRow() method of select queries takes a typed argument list. The framework also does away with the getX(column#)/setX(column#) methods in the base JDBC interface. These methods are the usual bug originators when modifications to existing queries neglect updates of the column number constants in the getX/setX methods. The framework also provides facilities to instrument the queries for performance measurement, and helps in organizing source code for both developers and DBAs.

Connection Pool

The DbConnection class represents a database connection. Each DbConnection instance contains a java.sql.Connection object and a hashtable cache of prepared statements. A public static init() method must be called to initialize the framework. This method sets up a DbConnection pool, which is basically a stack of DbConnections. The init() method takes as arguments the number of connections to put in the pool, the database user ID, database user password, JDBC connection URL, and the JDBC driver name. Listing Three shows how I connect to an Oracle database and an IBM DB2 database.

DbTrans

The DbTrans class represents a database transaction. To start a new transaction (as in Listing Two), I simply do a new DbTrans(). To complete the transaction, I call the close() method, passing it the commit or abort flag. It is important that each transaction gets closed because each new Transaction instance checks out a DbConnection from the connection pool just discussed. The close() releases the DbConnection back into the pool. If the close() method is not invoked, I eventually run out of DbConnections in the pool. That is why I always use the Transaction class in the try...finally clauses, as in Listing Two.

The Python Scripts

The mksel.py script generates SQL Select JDBC calls, while the mkupd.py script generates the SQL Insert/Update/Delete JDBC calls. The Select queries are treated differently from Insert/Update/Delete queries because Select queries return multivalue result sets vis-à-vis the single integer row count returned by the Insert/Update/Delete queries.

Each query is specified in a Python tuple: (java-class-name, augmented-sql). The java-class-name states the desired Java class of the query. The augmented-sql contains the SQL statement augmented with Java hints. A little parser works inside the scripts to parse the augmented-sql queries. Mainly, it looks for the pattern [x:y], where x and y are strings enclosed in the square brackets and separated by the colon character. The x token is interpreted as a SQL expression. The y token is taken to be a Java type declaration. I have added a bytes type to mean byte[]. I have also handled the boolean type by mapping the value to the strings t or f in the database and back.

For example, Listing Four shows the SelectEmployeeById query. First of all, notice that I named the query SelectEmployeeById. Also, each result row of the query contains the employee's ID (matched to a Java long 'id'), the employee's name (matched to a Java String 'name'), the employee's salary (matched to a Java float 'salary'), and employee's hire date (matched to a Java Timestamp 'hireDate'). Finally, the parameter to the query is a Java long 'id'.

Listing Five is the InsertEmployee query. Likewise, I named the query InsertEmployee, which takes as parameters a Java long 'id', a Java String 'name', a Java float 'salary', and a Java Timestamp 'hireDate'.

Running the scripts result in the creation of the files SelectEmployeeById.java and InsertEmployee.java. You may want to read the generated code to get a deeper understanding of how it all works.

Instrumentation

The Statistic class associates a name to a set of numbers representing the invocation count, minimum execution time, maximum execution time, and total execution time. Internally, the Statistic class saves all its instances in a static sorted tree map based on the name; this map facilitates a search on all statistics in the system by name. To keep track of new statistics, I simply instantiate a Statistic object by giving it a name. The constructor of the Statistic class inserts the object into the Statistic map. From then on, I can add samples to the Statistic object by calling the add() method, or reset the Statistic object by calling the clear() method. The Statistic map is mainly used in finding instances of the Statistic object by name. This usually occurs when I need to dump some statistics to the screen.

The framework uses the Statistic class to instrument each database query. Because each query has a unique Java path name, I use this name to instantiate a static Statistic object for each query. For each invocation of the query, I calculate the elapsed time for the query and add this sample to the Statistic object. I usually code a dump function to print out all query statistics from time to time or on demand. The printout is helpful during debugging and performance evaluation as it shows how many times each query is invoked and how long it takes. The printout often points immediately to a subpar query that results in a full table scan in the database.

Limitations

There are, of course, limitations to the framework. When these times come, you can work around the problem by getting the raw JDBC Connection object from the DbTrans object and issue JDBC commands directly. An obvious limitation is the stored procedure. The framework does not support stored procedures because I did not find a need for it. Another limitation is in streaming. This concerns the getXStream() methods of the java.sql.Statement class. JDBC stipulates that when using streams, the stream content must be emptied before the next getX() method is invoked. I could not find a way to handle this gracefully, and thus, have elected not to handle it at all. Yet another limitation is when the query has a variable number of parameters.

Conclusion

I use Python primarily for its text processing capability and its readability. There is no question that the same thing could have been done with Perl or, for that matter, Java. But the code would not look as clean as Python.

A beneficial side effect that comes directly from the use of the framework is the fact that all your queries are now collected in the two Python scripts and not scattered all over; there are only two places to look for queries that have gone awry.

Generating the JDBC codes have resulted in considerable increase in productivity in the projects I was involved in. The ease of generating SQL queries, and the associated benefits that come with it, allow developers to quickly complete the data-access layer for any new project, and do so with less bugs.

References

The framework was written by the author and is published with permission from TaiLab Corp.

DDJ

Listing One

PreparedStatement ins = null, upd = null, sel = null, del = null;
Connection con = DriverManager.getConnection(url, "mylogin", "mypasswd");
boolean commit = false;
try {
    Calendar c = Calendar.getInstance();
    c.clear();
    c.set(2000, 0, 1);

    ins = con.prepareStatement(
       "insert into EMPLOYEE(id, name, salary, hire_date) " +
       "   values (?, ?, ?, ?)");
    upd = con.prepareStatement(
       "update EMPLOYEE set salary = ? " +
       "   where id = ?");
    sel = con.prepareStatement(
       "select id, name, salary, hire_date " +
       "  from EMPLOYEE " +
       " where id = ?");
    del = con.prepareStatement(
       "delete from EMPLOYEE where id = ?");

    ins.setLong(1, 1000);
    ins.setString(2, "John Smith");
    ins.setFloat(3, 100000);
    ins.setTimestamp(4, new Timestamp(c.getTime().getTime()));
    ins.executeUpdate();
    System.out.println("inserted 1000 John Smith 100000");

    upd.setFloat(1, 120000);
    upd.setLong(2, 1000);
    upd.executeUpdate();
    System.out.println("updated 1000 120000");

    sel.setLong(1, 1000);
    ResultSet rs = sel.executeQuery();
    try {
        if (rs.next()) {
            System.out.println("selected " + rs.getLong(1) + " "
                       + rs.getString(2) + " "
               + rs.getFloat(3) + " "
               + rs.getTimestamp(4));
        }
        else {
            System.out.println("error: cannot find employee id 1000");
        }
    }
    finally {
        rs.close();
    }
    del.setLong(1, 1000);
    del.executeUpdate();
    System.out.println("deleted 1000");    

    commit = true;
}
finally {
    if (ins != null)
        ins.close();
    if (sel != null)
        sel.close();
    if (upd != null)
        upd.close();
    if (del != null)
        del.close();

    if (commit)
        con.commit();
    else
        con.rollback();
    con.close();
}

Back to Article

Listing Two

boolean commit = false;
DbTrans trans = new DbTrans();
try {
    Calendar c = Calendar.getInstance();
    c.clear();
    c.set(2000, 0, 1);
    InsertEmployee.execute(trans, 1000, "John Smith", 100000, 
                                new Timestamp(c.getTime().getTime()));
    System.out.println("inserted 1000 John Smith 100000");

    UpdateEmployeeSalaryById.execute(trans, 120000, 1000);
    System.out.println("updated 1000 120000");

    SelectEmployeeById.Row row = SelectEmployeeById.getOneRow(trans, 1000);
    if (row != null) {
    System.out.println("selected " + row.id + " " + row.name  + " "
                                       + row.salary + " " + row.hireDate);
    }
    else {
    System.out.println("error: cannot find employee id 1000");
    }
    DeleteEmployeeById.execute(trans, 1000);
    System.out.println("deleted 1000");

    commit = true;
}
finally {
    trans.close(commit ? trans.COMMIT : trans.ROLLBACK);
}
System.out.println("Statistics dump:");
Statistic.dump(System.out);

Back to Article

Listing Three

/* for oracle database */
DbConnection.init(20, "dev1", "dev1", 
          "jdbc:oracle:thin:@192.168.123.7:1521:SAMPLEDB",
          "oracle.jdbc.driver.OracleDriver");
/* for db2 database */
DbConnection.init(20, "dev1", "dev1", "jdbc:db2:SAMPLEDB",
                             "COM.ibm.db2.jdbc.app.DB2Driver");

Back to Article

Listing Four

("SelectEmployeeById", '''\
select [id:long id], [name:String name], [salary:float salary], 
                                         [hire_date:Timestamp hireDate]
  from EMPLOYEE
 where id = [?:long id]''')

Back to Article

Listing Five

("InsertEmployee", '''\
insert into EMPLOYEE(id, name, salary, hire_date)
 values ([?:long id], [?:String name], [?:float salary], 
                                       [?:Timestamp hireDate])''')

Back to Article


Related Reading


More Insights






Currently we allow the following HTML tags in comments:

Single tags

These tags can be used alone and don't need an ending tag.

<br> Defines a single line break

<hr> Defines a horizontal line

Matching tags

These require an ending tag - e.g. <i>italic text</i>

<a> Defines an anchor

<b> Defines bold text

<big> Defines big text

<blockquote> Defines a long quotation

<caption> Defines a table caption

<cite> Defines a citation

<code> Defines computer code text

<em> Defines emphasized text

<fieldset> Defines a border around elements in a form

<h1> This is heading 1

<h2> This is heading 2

<h3> This is heading 3

<h4> This is heading 4

<h5> This is heading 5

<h6> This is heading 6

<i> Defines italic text

<p> Defines a paragraph

<pre> Defines preformatted text

<q> Defines a short quotation

<samp> Defines sample computer code text

<small> Defines small text

<span> Defines a section in a document

<s> Defines strikethrough text

<strike> Defines strikethrough text

<strong> Defines strong text

<sub> Defines subscripted text

<sup> Defines superscripted text

<u> Defines underlined text

Dr. Dobb's encourages readers to engage in spirited, healthy debate, including taking us to task. However, Dr. Dobb's moderates all comments posted to our site, and reserves the right to modify or remove any content that it determines to be derogatory, offensive, inflammatory, vulgar, irrelevant/off-topic, racist or obvious marketing or spam. Dr. Dobb's further reserves the right to disable the profile of any commenter participating in said activities.

 
Disqus Tips To upload an avatar photo, first complete your Disqus profile. | View the list of supported HTML tags you can use to style comments. | Please read our commenting policy.