SQLExecutor is a light weight, open source JDBC framework that makes it easier to write concise, readable, and database independent JDBC code. SQLExecutor automatically translates checked exceptions such as SQLException into unchecked (runtime) exceptions, enabling you to catch only those exceptions you want to catch, by calling methods that abstract away the specifics of a particular database.

Read the tutorial on SQLExecutor

Can I use JTA Transactions? (XA DataSource objects?)

use Transaction Service provided by your application server.

pseudo code as follows:

....
// lookup XA Datasource from JNDI tree
DataSource ds = ....
ConnectionPool pool = new ConnectionPool(ds.getConnection());
.....

SQLExecutor executor = new SQLExecutor(pool);

....

// now do CRUD operations via SQLExcecutor object

I'm wondering about performance of SQLExecutor.

Can I use a different connection pool? I think no. It would be a nice feature.

If you set the connection pool size to 1, you have effectively disabled the connection pooling feature. You could write your own code to pool ConnectionPool objects if that is desirable.

It is disappointing to see that an exception hierarchy wasn't used. Instead isXXX methods are used resulting in switch/cascading if statements.

What I'd like to see, instead, is something like:

try {
  PreparedStatement st = dbConnection.prepareStatement("insert into customer .....");
  st.executeUpdate();
  dbConnection.commit();
} catch (SQLIntegrityException e) {
  //this is OK - the customer is already there, so do nothing
} catch (SQLConnectionException e) {
  //this is not OK - fail over, or do some other recovery work here
}

http://www.freeroller.net/page/grom/Weblog

The Spring Framework provides this kind of exception hierarchy. See the Javadocs for DataAccessException. (I've linked to the no frames version to get a direct URL.) This approach and its benefits are also described in Chapter 9 of Expert One-on-One J2EE Design and Development.

Regards,
Rod

Rod I think it was good idea that you provided a sample chapter of your book. I brought it after reading that sample chapter.

Hopefully more authors/publishers will start to provide sample chapters.

I agree. I really liked the sample chapter. I told all my work collegues about it
and put it on my reading list.

>
> What I'd like to see, instead, is something like:
>
> try {
>   PreparedStatement st = dbConnection.prepareStatement("insert into customer .....");
>   st.executeUpdate();
>   dbConnection.commit();
> } catch (SQLIntegrityException e) {
>   //this is OK - the customer is already there, so do nothing
> } catch (SQLConnectionException e) {
>   //this is not OK - fail over, or do some other recovery work here
> }

There are advantages and disadvantages to using an exception hierarchy. It does give you more flexibility in catching related exceptions in a single block, but it also requires learning the framework exception hierarchy.

My goal with SQLExecutor was to create a very simple framework that could be learned in 5 minutes and also make JDBC programming easier (and more maintainable).

Thanks for the feedback, Cameron.

> does give you more flexibility in catching related exceptions in a single
> block, but it also requires learning the framework exception hierarchy.

How is using isXXX() methods easier then an exception hierarchy? The hierarchy should have a base class, so if you didn't want to learn all the exception hierarchy you could just catch the base class.

Another cool feature would be some easy way to handle (database) resources. The following code is messy.
<code>
Connection conn = null;
try {
  // get database resources
  conn = DbHelper.getConnection();
} catch (SQLException fatalException ) {
  // could not acquire database connection
  // throw exception for global exception handler
  throw new DatabaseFailure(fatalException, "failed to acquire connection");
}

try {
  // do stuff with conn
} finally {
  // clean-up database resources
  try {
    if (conn != null) conn.close();
  } catch (SQLException fatalException ) {
    // We lost the database connection
    // throw exception for global exception handler
    throw new DatabaseFailure(fatalException,
        conn, "failed to close database connection");
  }
}
</code>

I've handled this by using a resource manager:
<code>
Connection conn = null;
try {
  conn = DbHelper.acquireConnection();
  // do stuff
} finally {
  DbHelper.releaseConnection(conn);
}
</code>

Or with callbacks:
DbHelper.run(new DbCommandCallback(){
  public void execute(Connection conn) {
    // do stuff
  }
});

Closures make callbacks functions even cleaner, unforunately Java doesn't support this :( If you have a better way that is possible, please let me know.

Spring uses DataSources: the standard J2EE way of getting connections. This means you can get one from the container writing zero code in Spring (just specify the JNDI name in an XML config file). DataSource is a simple interface so we also provide simple implementations designed for testing outside a container.

Spring doesn't do its own connection pooling. We think this is the business of server vendors. We allow a subinterface of DataSource called SmartDataSource to say that it wants a connection to be left open, but typically Spring will both obtain and close the close the connection from the DataSource with no user intervention required.

Regards,
Rod

>hierarchy should have a base class, so if you didn't want to learn all the
>exception hierarchy you could just catch the base class.

I designed SQLExecutor to be extendable to other databases (it currently supports Oracle and MySQL). To extend it to a new database, all you have to do is extend the DatabaseException class for a new database. For example you could write something like this:

public class PostgresException extends DatabaseException
...

All you would need to do is implement the following abstract methods:
    public abstract boolean isDataIntegrityViolation();
    public abstract boolean isBadSQLGrammar();
    public abstract boolean isNonExistentTableOrViewOrCol();
    public abstract boolean isInvalidBindVariableName();
    public abstract boolean isDatabaseUnavailable();
    public abstract boolean isRowlockOrTimedOut();
    public abstract boolean isVarParameterUnbound();

and add your new database type to the ExceptionFactory class (switch statements).

So basically, you write a new class and add a line of code to two switch statements (in an overloaded getException() method). Pretty easy.

How would you make it easy to extend the framework for new databases with an exception hierarchy? I couldn't think of a way to do this as easy as just extending a class and implementing abstract methods. I think the way SQLExecutor does this is simple and elegant.

A potential advantage of an exception hierarchy would be that you could group related exceptions in the hierarchy. But I couldn't think of a logical grouping of database exceptions that would work across multiple databases and still be practical for application development. A isDataIntegrityViolation() is very different than a isRowlockOrTimedOut() database exception. Any grouping I might come up with would be contrived and of no practical use.

One small advantage of implementing the exception types as abstract methods is that programmers can use Java IDE's code completion feature to "explore" the various exception types available:

        catch (DatabaseException e)
        {
            if (e.isDataIntegrityViolation())
                applyDataIntegrityViolationRecovery();
        }

Anyway, that summarizes my thoughts on creating an exception hierarchy for SQLExecutor. My original plan was to implement such a hierarchy, but after considering the trade-offs, I opted for defining abstract methods instead.

Cheers,
Jeff

>
It is very trivial to extend the framework, map error code and vendor name to exception class:
<code>
void register(int code, String vendor, Class exceptionClass )throws Exception{
  
  map.put( new KeyPair( code, vendor ), exceptionClass.getConstructor( new Class[] {Throwable.class}) );

}
</code>
Properties file in classpath can be used for this mapping.

use something like this to rethrow an exeption:

<code>
void rethrow( SQLException cause )throws Exception{

  Constructor ec = (Constructor)map.get(new KeyPair( cause.getErrorCode(), config.getDbVendor() ));
  if(ec == null){

     throw cause/* or some "generic" exception*/;

   } else {
 
    throw (Exception)ec.newInstance(new Object[]{cause} );

  }

}

</code>

Very easily, as described in my book and as Spring does. Spring offers a non JDBC-specific exception hierarchy with subclasses of DataAccessException such as InvalidDataAccessResourceUsage. This in turn has JDBC-specific subclasses such as BadSqlGrammarException. We can have further RDBMS-specific subclasses as well if necessary, for example to support databases that can actually tell us which table name or column name was invalid. Application code typically catches only generic exceptions to ensure that DAOs aren't dependent on JDBC, but the exception subclasses thrown (and shown in stack traces) will carry all the information about the problem that was available.

Spring does the mapping to its own hierarchy by looking at the Connection metadata when it first connects to a database and looking up an XML file. This now supports all major databases out of the box (although we welcome contributions of new code mappings). Alternatively the user can provide a custom SQLExceptionTranslator to gather all RDBMS-specific code in one place.

Regards,
Rod

>JDBC-specific exception hierarchy with subclasses of DataAccessException such
>as InvalidDataAccessResourceUsage. This in turn has JDBC-specific subclasses

I really like your book, J2EE Design and Development, and your Spring Framework is a good framework. I just prefer the simplicity and concise code I write when using SQLExecutor.

With regards to an exception throwing mechanism, I guess it comes down to a matter of personal preference. Spring does have a flexible mechanism--your exception hierarchy (the 15 classes listed in your book).

But I believe SQLExecutor's exception throwing mechanism (namely implementing abstract methods in a database specific subclass) is easier to learn and extend. I've had a couple people write to me saying they have extended the framework to support other databases (DB2 and SQLServer).

I'm not sure either of us can empirically prove that one mechanism is superior to the other. That is the beauty of there being more than one JDBC framework--many people will choose Spring, and many people have emailed me saying they are using SQLExecutor.

Either framework is vastly better than writing verbose, and clumsy JDBC code.

I appreciate your feedback, Rod, and look forward to your next book.

Best regards,
Jeff

Jeff,

Either framework is vastly better than writing verbose, and clumsy JDBC code.
Certainly is. And having competing frameworks that work in different ways is fine. Users can choose.

I can't believe that so many people write raw JDBC, in many cases without even an in house abstraction.

Regards,
Rod

I like the way SQLExecutor works. Like the article on Developer.com said, it took me about five minutes to learn how to use it (and less than that to show someone else how to use it), and it abstracts away the clutter of JDBC code.

It's definitely cleaned up the code in our data access layer.

Support for large result sets would be a nice feature, but so far we haven't needed that.

Nice job!

I believe Jeff decided to make SQLExecutor the easiest JDBC framework to learn and use and he hit the mark. That he fit the framework into less than 10 classes is also a big plus.

At the time I looked at both the Spring JDBC framework and SQLExecutor, SQLExcecutor was much easier to use, and so I chose it for two smaller projects so far. For a larger project I am using the other (very nice features) of the Spring Framework with Hibernate for the persistence layer. This dramatically lowers the amount of data access coding that I have to do. I may eventually use Spring's JDBC framework since I like it so much, but Hibernate is fine for now.

It makes sense to evaluate the requirements of your projects and choose the appropriate framework to use for each individual project that you will work on. I still have SQLExecutor, Spring and Hibernate in my toolset, and I like the options that this gives me.

> needed that.

I've added a new method to SQLExecutor, runQueryStreamResults(), that enables you to stream a large result set. Here is the method signature:
  public ResultSet runQueryStreamResults(String sql)

Here is some sample code using it:

  String sql = "select CARRIER_ID, NAME from carrier";
  SQLExecutor sqlExec = new SQLExecutor(getConnectionPool());
  ResultSet rs = sqlExec.runQueryStreamResults(sql);
        
  System.out.println("SQL RESULTS FROM ResultSet:");
  try
  {
     while (rs.next()) //still have records...
     System.out.println(rs.getString("CARRIER_ID") + " " +
                        rs.getString("NAME"));
  }
  catch (SQLException sqle) { }
        
  sqlExec.closeConnection();

Thank you (everyone) for your suggestions.

-Jeff

> enables you to stream a large result set. Here is the method signature:
>   public ResultSet runQueryStreamResults(String sql)

Great! I'll try that out.

It seems to me, regarding the whole Exception hierarchy vs. isXXX() methods debate, that the argument has turned into some kind of pissing contest. Both mechanisms are a LOT better than straight JDBC. Spring is slightly more flexible, SQLExecutor is simpler to use/learn.

Both frameworks do a good job of abstracting away JDBC code. I chose SQLExecutor because it was easy to learn and my application code using it looks simpler than Spring code.

<carolyn>
It seems to me, regarding the whole Exception hierarchy vs. isXXX()
methods debate, that the argument has turned into some kind of pissing
contest. Both mechanisms are a LOT better than straight JDBC. Spring is
slightly more flexible, SQLExecutor is simpler to use/learn.
</carolyn>

OK, you have a point--I quit!

On the downside, I concede that Rod's exception hierarchy is slightly more flexible than SQLExecutor's isXXX() methods (at a slight cost in complexity) and that he has more money and fame than me! But hey, on the plus-side, I have (slightly) more hair on my head than Rod!

:-)

Thanks for the feedback,
Jeff

Jeff, if you managed to get Rod Johnson to concede a point in a public forum, the pillars of heaven would likely shake down to their fundament, and you would go down in history as a mythical figure of great stature and power.

   -Mike

P.S. I agree that a series of try catch blocks looks an awful, awful lot like an if-then-else block.

I'm pretty much done with this topic as well.

<jeff>
On the downside, I concede that Rod's exception hierarchy is slightly more flexible than SQLExecutor's isXXX() methods (at a slight cost in complexity) and that he has more money and fame than me! But hey, on the plus-side, I have (slightly) more hair on my head than Rod!
</jeff>
I only wish the money bit was true! What money I have I've made from consulting rather than writing.

Regards,
Rod

<rod>
I only wish the money bit was true! What money I have I've made from consulting rather than writing.
</rod>

Yeah, I've written a few articles (http://www.softtechdesign.com/media.html), but no books. Writing is fun but it is a hard way to pay the bills.
:-)

Well, euuhm .. maybe it's me, but how am I supposed to know wich error codes belong to a certain category ? I can search in the error strings for common words, but that's quite error-prone, plus it would take a lot of time.

So I am probably missing something, there must be some site out there that contains such info, .. right ? :s

To clarify, I looked up some error codes in the sysmessages table (SQL Server DB) and for the isDataIntegrityViolation method I found the following error codes : 10055, 10065, 11015 and 11040.
I got no guarantee however that these embed all the appropiate errors.

So ..

> I've had a couple people write to me saying they have extended the framework to support other databases (DB2 and SQLServer).

Well, euuhm .. maybe it's me, but how am I supposed to know wich error codes belong to a certain category ? I can search in the error strings for common words, but that's quite error-prone, plus it would take a lot of time.

For Oracle and mySQL, I just looked at the error code documentation to see which error codes corresponded with the exception categories in DatabaseException. Two separate developers are working on a SQLServerException class--hopefully they will send me the code soon so I can update the project on SourceForge.net and on my website at:
http://www.softtechdesign.com/media.html

Thanks,
Jeff

In your introduction, you dismiss Rod Johnson's JDBC support classes -- part of the Spring framework (http://www.springframework.org/) -- as "too complicated". Specifically, "it required creating a subclass for each query, learning a somewhat complicated exception hierarchy, and [required] framework users to implement anonymous inner classes".

Have we been using the same framework?

One of Spring's strengths is that it is can be used at many levels and in many ways. Yes, you can create a separate class for each query. Yes, if you use a JdbcTemplate you'll have to supply a RowCallbackHandler, and you this could be an anonymous inner class -- although, if you have a class per query, the query class itself could implement the interface if you don't like inner classes. But you don't have to do any of these things. You could use the higher level object API. You can instantiate JdbcTemplate directly, without subclassing, and initialise it using its setter methods.

I have to second Cameron above in not seeing how learning a raft of isFoo() methods is easier than learning a small hierarchy of a dozen or so clearly-named exceptions, but I do see how it is less flexible (since it's entirely flat) and how it fails to take advantage of object orientation. For goodness sake, I thought polymorphism was a good thing for allowing us to elmiminate if-then-elseif sequences such as the one shown in the article!

Ultimately, it's also a matter of taste, I guess. I prefer the flexibility of a callback interface above always having my results returned as a list (why doesn't SQLResults implement List, BTW? That would make it much easier to use, say, in a JSP using the JSTL or JSP 2.0 EL). I prefer my JDBC handler to be only the lowest tier in a comprehensive framework that allows me to pick and choose the right abstraction level for the job at hand. Others' mileage probably varies, and certainly SQLExecuter looks carefully thought out and implemented. I like the fact that (as in Spring) you can plug in your own ExceptionFactory and, say, replace the exceptions provided by a proper hierarchy ;)

- Peter

I don't think the Spring exception hierarchy is complex. The mapping from SQLException to the hierarchy is performed by the framework. Having an exception hierarchy enables you to use standard Java to choose exactly which exceptions you can recover from and react appropriately. Also different exceptions in a hierarchy can contain additional information that can't be carried in isWhateverProblemWrong() methods. Exceptions are objects, not just indications of what type of error occurred.

The other point of the Spring data access exception hierarchy is that it's not JDBC-specific. We support the same hierarchy with Hibernate, JDO and other persistence strategies. There are JDBC-specific subclasses such as BadSqlGrammarException, but your code needn't depend on them (although they will show useful detailed information in logs etc.) This is ideal for implementing the DAO pattern, as if JDBC-specific exceptions are used they have to be wrapped in persistence technology-specific DAO exceptions to avoid dependence on the underlying technology.

Regards,
Rod

I agree, callback is beter to hanle JDBC resultsets.
It can be used to copy results to datastructure :

 SQLResults res = (SQLResults)sqlExecWithCallback.runQueryCloseCon(sql,copyToSQLResultsCallback );
 ...........
 or can be used to hanle resultset using cursor to save memory:

  sqlExecWithCallback.runQueryCloseCon(sql, new HandleSQLResultsCallback(){

         void handle(ResultSet rs)throws Exception{
            while(rs.next())
             printNext(rs); // we do not need to load all results to memory,
                            // if we print results to stdout.
         }

  }


 );

Callback implementations are "normal" classes and they can be reused.
I use five or six callback implementation types, they copy resultset to common datastructures like List,Map or bean and I need "custom" callbacks/handlers for data export only.

I think Voruta can be interesting framework for JDBC users http://voruta.sf.net too. It uses callbacks to implement DAO pattern and "hides" this stuff in metadata and dynamicly generated code.
 
"Copy to StdOut " Code with static SQL looks like this in Voruta:

/**
 *@query "SELECT * FROM JDBC_TEST WHERE TEST_ID < $1 AND TEST_DT > $2"
 *@handler print
*/
 abstract void print(String s1, String s2, PrintSream out );

This kind of reusable "print" callback implementation is in test directory, It can be customized/modified to print XML or CSV using "cursored API".

> of the Spring framework (http://www.springframework.org/) -- as "too
> complicated".

I do not "dismiss" Rod Johnson's Spring framework. It is a good framework for writing many types of JDBC code. It is more flexible than SQLExecutor in some ways (but at the cost of additional complexity).

I do believe that SQLExecutor is simpler to learn and use, and may be more appropriate for writing most JDBC applications.

In my opinion, the bottom line with any framework is how easy is it to learn and use, how easy is the code to read, and how maintainable is the code. I believe that the SQLExecutor framework is a good choice for a wide variety of JDBC applications.

I designed SQLExecutor to solve 95% of the JDBC problems you may encounter. If you need to solve the other 5%, use straight JDBC or Spring. And if you prefer the Spring framework architecture, then by all means use it instead. It is a good framework.

Thank you for your feedback.

Yes, I think there is no way to implement simple high level API for JDBC and to have all JDBC features at the same time, but I think you need callbacks in SQLExecutor too. It can be used for internal implementation (copy callbacks),
but it must be usefull for users too, I found this way is very good for JDBC frontends, It makes JDBC code reusable and safe.

>
> Thank you for your feedback.

>to have all JDBC features at the same time, but I think you need callbacks in
>SQLExecutor too. It can be used for internal implementation (copy callbacks),
> but it must be usefull for users too, I found this way is very good for JDBC
>frontends, It makes JDBC code reusable and safe.

I will probably implement a feature to return a portion of a result set (either through callbacks or perhaps through some simpler mechanism). Like most programmers, I'll probably solve that problem when an application I'm writing requires that feature! :-)

Thanks for the suggestion,
Jeff

Yes, it is no meaning to solve not existing problems :)
 Most of my applications are web applications and I use streaming for large XML files, almost all of this kind applications have some way to download data, but I agree, it is more typical to copy resul set to data structure and set it as request attribute before to forward to view in web applications.

>
> Thanks for the suggestion,
> Jeff

> easier to use, say, in a JSP using the JSTL or JSP 2.0 EL).

Because I didn't think of it! :-)

I've just created a toList() method in SQLResults that returns the result set as a List.

Thanks for the suggestion,
Jeff

>methods is easier than learning a small hierarchy of a dozen or so clearly-
>named exceptions, but I do see how it is less flexible (since it's entirely
>flat) and how it fails to take advantage of object orientation. For goodness
>sake, I thought polymorphism was a good thing for allowing us to elmiminate
>if-then-elseif sequences such as the one shown in the article!

I've given this more thought. Since I still don't see a true, multilevel, hierarchy in SQL error codes, I think an exception hierarchy would be flat too (a DatabaseException class and numerous subclasses). Hence an exception hierarchy would offer no real advantage. The code would look like this:

try
{
  some database operation...
}
catch (BadSQLGrammarException be)
{
  ...
}
catch (RowlockOrTimedOutException re)
{
  ...
}

instead of this

try
{
  some database operation...
}
catch (DatabaseException e)
{
  if (e.isBadSQLGrammar())
    ...
  else if (e.isRowlockOrTimedOut())
    ...
}

I don't see any more polymorphism in the first exception block than in the second.

>sake, I thought polymorphism was a good thing for allowing us to elmiminate
>if-then-elseif sequences such as the one shown in the article!

And multiple catch blocks are just another form of if-then-elseif sequences.

I also disagree with Rod that less exception information is available this way. You still have the (translated) DatabaseException object to work with. The only purpose of the isXXX() methods is to decipher the SQL error codes in a clean manner. With standard JDBC, your code would look something like this:

try
{
  some database operation...
}
catch (SQLException e)
{
    int oracleErrorCode = e.getErrorCode();
    switch (oracleErrorCode)
    {
        case 1:
        case 1407: applyDataIntegrityViolationRecovery();
                   break;

        case 104:
        case 1013:
        case 2087:
        case 60: applyDeadlockRecovery();
                   break;
    }
}

The isXXX() methods eliminate this awkward switch statement bocode.

Moreover, by implementing the isXXX() routines as abstract methods, it will be easier for programmers to extend the framework to support new databases or new SQL error code isXXX() methods.

Of course, if you see a true, multilevel hierarchy in SQL error codes, then an exception hierarchy would be useful. I just don't see one. And I don't want to contrive one that will not be leveraged by "real" application code.

-Jeff

<jeff smith>
try
{
  some database operation...
}
catch (BadSQLGrammarException be)
{
  ...
}
catch (RowlockOrTimedOutException re)
{
  ...
}

instead of this

try
{
  some database operation...
}
catch (DatabaseException e)
{
  if (e.isBadSQLGrammar())
    ...
  else if (e.isRowlockOrTimedOut())
    ...
}

I don't see any more polymorphism in the first exception block than in the second.

>sake, I thought polymorphism was a good thing for allowing us to elmiminate
>if-then-elseif sequences such as the one shown in the article!

And multiple catch blocks are just another form of if-then-elseif sequences.

I also disagree with Rod that less exception information is available this way. You still have the (translated) DatabaseException object to work with. The only purpose of the isXXX() methods is to decipher the SQL error codes in a clean manner.
</jeff smith>

An exception hierarchy will allow extra information to be attached to each exception. For example, for a BadSQLGrammarException you could find out the syntax error such as the invalid column/table name. isXXX() methods only allow for type information. This was Rod's point about using an exception hierarchy.

<Cameron>
An exception hierarchy will allow extra information to be attached to each exception. For example, for a BadSQLGrammarException you could find out the syntax error such as the invalid column/table name. isXXX() methods only allow for type information. This was Rod's point about using an exception hierarchy.
</Cameron>

Ah, now I understand your point. Yes, you could customize your BadSQLGrammarException class to contain accessor methods for table name and column name.

Of course, this information is already available in the exception message. For example, if I try the following SQL:

  SELECT CARRIER_ID, NAME FROM CARRIER

and the CARRIER table doesn't exist, SQLExecutor returns the following error message:

  com.cexp.wms.jdbc.OracleException: ORA-00942: table or view does not exist
  SQL Failed: SELECT CARRIER_ID, NAME FROM CARRIER

So I am getting back all the information (including the table name that doesn't exist).

Of course, Rod's hierarchy could store this table name in an separate field (with an accessor method). But in a real world app, I'm not sure how this would help you. You're probably going to just display an error message anyway.

So I concede that the hierarchy gives you a little more flexibility, but I question whether this extra flexibility is worth the cost in extra complexity.

Thanks for the feedback,
Jeff

<jeff>
I've given this more thought. Since I still don't see a true, multilevel, hierarchy in SQL error codes, I think an exception hierarchy would be flat too (a DatabaseException class and numerous subclasses).
</jeff>
It's not flat. For example, not having a hierarchy means you can't provide additional information that may only be available for particular databases. Your single DatabaseException is a God object that must know all there is to know about exceptions. There is a natural hierarchy of database exceptions, with commonality between different broad types of problem. The Spring hierarchy differentiates from "resource usage" and "resource failure" exceptions. For example, I might want to know whether I sent bad SQL or whether my app couldn't connect to the database as separate high-level cases. With Spring's exception hierarchy I can do this; with isWhatever() methods I can't.

<jeff>
I also disagree with Rod that less exception information is available this way. You still have the (translated) DatabaseException object to work with. The only purpose of the isXXX() methods is to decipher the SQL error codes in a clean manner.
</jeff>
Isn't this saying you can rely on the useless SQLExceptions you wanted to get away from in the first place? Or are you saying there's really a hierarchy of DatabaseException and merely convenience methods to hide that?

<jeff>multiple catch blocks are just another form of if-then-elseif sequences</jeff>
No, they're not. The exception constitutes a separate context for each catch block. There's also the ability to write a catch block that picks up subclasses of a particular exception. It's also impossible to forget the "else" in one of the "else ifs"--easy enough to do and a nasty potential mistake.

Personally I think if/else chains are a code smell and try to avoid them as far as possible.

Regards,
Rod

<rod>
It's not flat. For example, not having a hierarchy means you can't provide additional information that may only be available for particular databases.
</rod>

This database specific information is available in SQLExecutor (if you want it) through the unique msg, SQLErrorCode, and SQLState. The vast majority of the time, however, I think people will be interested in the type of error (cleanly deciphered for you in the isXXX() methods).

<rod>
There is a natural hierarchy of database exceptions, with commonality between different broad types of problem.
</rod>

Some of your Spring framework exceptions are flat (direct subclasses of DataAccessException), and some are hierarchical (part of a multi-level hierarchy). So you have a point here, Rod. For example, you define a InvalidDataAccessResourceUsageException and its subclass, BadSqlGrammarException and could thus catch both exceptions in a single catch block.

But this requires a framework user to learn which database exceptions (SQLExceptions) will trigger a Spring framework, "InvalidDataAccessResourceUsageException" exception. I don't know which SQLException exceptions correspond to a InvalidDataAccessResourceUsageException exception.

My isXXX() more directly correspond to the actual SQLException exceptions, and thus are simpler to learn/use. So you gain a little flexibility with your exception hierarchy (being able to catch multiple exceptions in one catch block), but at the cost of some complexity. That was a tradeoff I didn't want to make with SQLExecutor--I wanted to keep the framework as simple as possible.

So SQLExecutor does have this limitation (not being able to catch multiple exceptions in a single CATCH block).

SQLExecutor does, however, throw runtime exceptions (enabling programmers to catch only the exceptions they want). And it does pretty-up the ugly switch statements that try to determine the SQL error codes. It makes handling exceptions much easier than standard JDBC and is very simple to learn.

I would recommend using Spring if you need the maximum flexibility of its exception hierarchy and are willing to put in the time to learn a more complicated framework. I would recommend SQLExecutor if you
1) want to greatly simplify your JDBC code
2) don't want to spend much time learning a framework
3) can live with not being able to trap multiple exceptions in a single CATCH block.

<jeff>
The only purpose of the isXXX() methods is to decipher the SQL error codes in a clean manner.
</jeff>
<rod>
Isn't this saying you can rely on the useless SQLExceptions you wanted to get away from in the first place? Or are you saying there's really a hierarchy of DatabaseException and merely convenience methods to hide that?
</rod>

No, I'm not saying that. The SQLExceptions contain the SQL error code and SQL state--this is not useless information. The only problem with SQLExceptions are that they require clumsy switch statement code to decipher. Here is an example of SQLException code:

catch (SQLException e)
{
    int oracleErrorCode = e.getErrorCode();
    switch (oracleErrorCode)
    {
        case 1:
        case 1407: applyDataIntegrityViolationRecovery();
                   break;

        case 104:
        case 1013:
        case 2087:
        case 60: applyDeadlockRecovery();
                   break;
    }
}

The same code using SQLExecutor will look like:

catch (DatabaseException e)
{
    if (e.isDataIntegrityViolation())
        applyDataIntegrityViolationRecovery();
    else if (e.isDeadlockOrTimedOut())
        applyDeadlockRecovery();
}

I think the SQLExecutor code is more elegant.

<jeff>
multiple catch blocks are just another form of if-then-elseif sequences
</jeff>
<rod>
No, they're not.
</rod>

Well, they are, IF each catch block is catching a single exception class. Since I see the database exception hierarchy as mostly flat, there would be a separate catch block for each exception (no polymorphism). Here is what the same exception code might look like with a distinct exception classes:

catch (DataIntegrityException die)
{
    applyDataIntegrityViolationRecovery();
}
catch (DeadlockOrTimeOutException dle)
{
    applyDeadlockRecovery();
}

This code looks similar to an if-then-elseif sequence to me.

As always, I appreciate your thoughts and feedback.

Regards,
Jeff

<jeff>
No, I'm not saying that. The SQLExceptions contain the SQL error code and SQL state--this is not useless information. The only problem with SQLExceptions are that they require clumsy switch statement code to decipher. Here is an example of SQLException code:
...
</jeff>
SQL error code and SQL state codes are useless if you care about portability between databases. For example, your example (after this quote) works only in Oracle. So SQL Executor can't help you if you want more information. PostgreSQL will always return 0 and null, for example.

Regards,
Rod

<rod>
SQL error code and SQL state codes are useless if you care about portability between databases. For example, your example (after this quote) works only in Oracle. So SQL Executor can't help you if you want more information. PostgreSQL will always return 0 and null, for example.
</rod>

That's very true. If you did directly access the SQL error code then you would lose your database independence. My assumption is that most programmers will just use the (database independent) isXXX() methods.

-Jeff

>would lose your database independence. My assumption is that most programmers
>will just use the (database independent) isXXX() methods.

The isXXX() methods have been working great for me.

-Mark

Thank you all for the feedback. I've made two improvements to the framework based on your input:

1) SQLResults can be returned as a List (by calling toList())
2) SQLExecutor can now support streaming result sets (useful if a select statement returns too much data to pack into a SQLResults object).

This thread has been very helpful.

Best regards,
Jeff Smith
www.SoftTechDesign.com

I admit I have not read through the full documentation of the SQLExecutor tool, but I see what to me is a HUGE RED FLAG:

<pre>
    SQLResults res = sqlExec.runQueryCloseCon("SELECT * FROM JDBC_TEST" +
     "WHERE TEST_ID = ? AND TEST_DT < ?");
    for (int row=0; row < res.getRowCount(); row++)
</pre>

What I see here is all my results being jammed into memory inside the SQLResults object. If the query has been run, and the connection is closed, there's no streaming of results from the db as I loop through them. While this is no problem if my result set is small, this can be a big problem if developers on the team thoughtlessly use this tool on large result sets.

Unless I'm missing something, this SQLExecutor tool will never scale as well as pure JDBC because too much memory will be used where I should have been streaming my results from the db JDBC-style.

Thats a good point . I wonder how Hibernate and various CMP implementations handle humoungous results set ?

I get this question alot for some reason even for simple web apps that would not want a large result set. If your dumping a result set that could not efficiently be retrofitted into a proper data independent model you need to rethink your applications view probably. If your talking about only a few but potentially huge binary blobs well then you might have a point. I'd wonder why your using an ORM or JDBC abstraction if your just doing batch processing though.

There are exceptions to every rule so it's all about design tradeoffs. If you value efficiency over maintanability that's your choice. If you can scale hardware and make your apps more adaptable, maintanable and aestheticlly pleasing then great look at introducing some abstractions to your design that help you achieve those goals. The good thing is that contractors will always be around later to help you do things like "move a million lines of pl/sql to the middle tier" etc. #;@)

I wonder how Hibernate and various CMP implementations handle humoungous results set ?

By only storing the primary keys of the result sets. Instead of 5,000 rows, the system has to store 5,000 primary keys. These systems will have various configurable lazy loading strategies (such as read-ahead and load-groups) that ensure data is only loaded into memory if and when it is needed.

>
> Thats a good point . I wonder how Hibernate and various CMP implementations handle humoungous results set ?

SQLExecutor wasn't designed to replace (or wrap) all JDBC capabilities. It was designed to be simple to learn and easy use for many JDBC tasks.

If you want to select large numbers of records, you wouldn't want to store them in a SQLResults object. The Spring framework might be better suited to that task.

Thanks for the feedback.

>

Well, you would not want to use Spring's MappingSqlQuery, since that would create a huge List of Objects with one entry for each row returned. You could however, implement your own RowCallbackHandler and pass that in to a JdbcTemplate. Then you could do whatever you wanted to with the returned row. This would not cause the framework to allocate memory for each row, and you could spin through a huge resultset using this technique.

Thomas

P.S.

It would look something like this:

JdbcTemplate jt = new JdbcTemplate(ds);
PreparedStatementCreator psc = new PreparedStatementCreator() {
  public PreparedStatement createPreparedStatement(Connection conn)
    throws SQLException {
    PreparedStatement ps = conn.prepareStatement("SELECT * FROM HUGE_TABLE");
    return ps;
  }
};
RowCallbackHandler rch = new RowCallbackHandler() {
  public void processRow(ResultSet rs) throws SQLException {
    rowCount++;
  }
};
jt.query(psc, rch);

hi Jeff,
the original constructor of ConnectionPool is :
<code>
    /**
     * Constructor creates a JDBC connection using given parameters
      * @param databaseType
     * @param driverName
     * @param conURL
     * @param username
     * @param password
     */
    public ConnectionPool(int numPooledCon,
                          String driverName,
                          String conURL,
                          String username,
                          String password)
    {
        this.dbType = DatabaseType.getDbType(driverName);

        this.driverName = driverName;
        this.conURL = conURL;
        this.username = username;
        this.password = password;
        conPool = new ArrayList();
        addConnectionsToPool(numPooledCon);
    }
</code>
there is something wrong with dbType, beacuse you getDbType via parsing the driverName.And as everyone knows, the driverName doesn't have uniform name formatter.When I set driverName="org.gjt.mm.mysql.Driver",the constructor fails.

so,my suggetion is below:

<code>
/**
 * Create an Connection Pool using the props file
 * @parm numPooledCon
 * @parm fileName Props file name
 */
public ConnectionPool(int numPooledCon,String fileName){

InputStream is = getClass().getResourceAsStream(fileName);
Properties dbProps = new Properties();
//load the props file
try {
dbProps.load(is);

} catch (Exception e) {
System.err.println("Can not read the properties file. "
                 + "Make sure db.properties is in the CLASSPATH");

return;

}

String driver = null ;
String url = null;
String user = null;
String password = null;
String dbName = null;

Enumeration propNames = dbProps.propertyNames();

while (propNames.hasMoreElements()) {
String name = (String) propNames.nextElement();
if (name.endsWith(".url")) {
dbName = name.substring(0, name.lastIndexOf("."));
url = dbProps.getProperty(dbName + ".url");
if (url == null) {
System.err.println(dbName +"'s connect URL is not sepcified");
return;
}
driver = dbProps.getProperty(dbName + ".driver");
if (driver == null){
System.err.println(dbName +"'s driver is not found");
return;
}
user = dbProps.getProperty(dbName + ".user");
password = dbProps.getProperty(dbName + ".password");
}
}
//System.out.println("[PoolConstructor]: driver = " + driver);
//System.out.println("[PoolConstructor]: url = " + url);
//System.out.println("[PoolConstructor]: user = " + user);
//System.out.println("[PoolConstructor]: passeord = " + password);

this.dbType = DatabaseType.getDbType(dbName);
this.driverName = driver;
this.conURL = url;
this.username = user;
this.password = password;
conPool = new ArrayList();
addConnectionsToPool(numPooledCon);
}

</code>

the sample of properties files like this:

<file>
mysql.url=jdbc:mysql://localhost:3306/test
mysql.driver=org.gjt.mm.mysql.Driver
mysql.user=root
mysql.password=*****
</file>

I have test the code,and it works well.

> driverName.And as everyone knows, the driverName doesn't have uniform name
> formatter.When I set driverName="org.gjt.mm.mysql.Driver",the constructor
> fails.

There are two overloaded methods for getting the dbType. One of them uses the driver name like so:

    public final static String ORACLE_NAME = "ORACLE";
    public final static String MYSQL_NAME = "MYSQL";

    /**
     * Parses the driver name to determine the database type
     * @param driverName String
     * @return int type of database (e.g. ORACLE)
     */
    static int getDbType(String driverName)
    {
        int dbType = 0;
        if (driverName.toUpperCase().indexOf(ORACLE_NAME) > -1)
            dbType = ORACLE;
        else if (driverName.toUpperCase().indexOf(MYSQL_NAME) > -1)
            dbType = MYSQL;
        return(dbType);
    }

This code correctly returns dbType = 2 (mysql) when I use the driverName you suggest, "org.gjt.mm.mysql.Driver". Basically as long as "mysql" is somewhere in the driver name, this code works. As far as I know, all mysql drivers and oracle drivers have the name "mysql" and "oracle" in them somewhere, so this code works.

I think it is much simpler to parse the driver name (automatically), than create yet another file, a property file, to store this information. I'm not sure why you say the "constructor" fails, because it doesn't fail when I test the code here with the driver you suggested. Is your mysql driver in the classpath?

Thanks for you feedback,
Jeff

Jeff,

I tried to use your framework some three months ago.

As far as I remember, the reason was getting NullPointerExceptions any time I queried an Oracle table containing a NULL value in a VARCHAR field. Or something like that.

Of course, I could have reported it, get it fixed and so on. However, (a) it didn't look to me like you were interested in maintenance (not having a proper infrastracture like a site at SourceForge etc), and (b) level of extra abstraction provided by the framework was not so big. So I decided not to bother.

> As far as I remember, the reason was getting NullPointerExceptions any time I
> queried an Oracle table containing a NULL value in a VARCHAR field. Or
> something like that.

I fixed that bug a couple of months ago.

> Of course, I could have reported it, get it fixed and so on. However, (a) it
> didn't look to me like you were interested in maintenance (not having a
> proper infrastracture like a site at SourceForge etc)

I do have a site at SourceForge.net now. Just search for "SQLExecutor".

Thank you for your feedback,
Jeff

Haven't visited this site for quite a while.

I tried it again some time in November. Lived happily ever after. Using it in an integration test suite to set up database fixtures and assert resulting database states. Subject under test is a large scale customer care and billing system for telecoms.

What makes SQLExecutor a good fit for this sort of thing is because it helps to keep test code plain and readable, even for the management :)

Jeff, thank you very much!

I'm glad to hear you like using the framework! I've been pleased with the slow but steady increase in the number of SQLExecutor users out there.

Thanks,
Jeff

I've been using SQLExecutor for over a month without any problems at all. I must've downloaded it after the NullPointer bug was squashed.

I looked at Spring but liked SQLExecutor better. Easier syntax, good concise code. I think this framework should be considered for inclusion in the JDBC4 specification. It is a well abstracted frontend to JDBC.

I think the complaints about exception hierarchy vs. abstract methods is kind of nitpicky. Either way you go, you wind up with about the same amount of code that is much better to read than JDBC SQLException case statements.

Just my two cents.

-Mark
P.S. if this message posts more than once, my apologies--I got an error from the site a few minutes ago.

Another similar project is JingSQL (and JingDAO). JingSQL uses Velocity to parse SQL templates and the API is surprisingly similar to SQLExecutor (I used the name SQLEvaluator).

When you combine this with a DAO framework (like JingDAO) you can easily switch databases (DAO contexts) without switching ANYTHING in your client code.

see http://jingdao.sf.net

It's very nice toy. I wrote something like You framework some years ago.
But it doesn't resolv the problems with O/R-Mapping and it is definitely not Thread save. I suggest to use Apache OJB in real projects.

> But it doesn't resolv the problems with O/R-Mapping and it is definitely
> not Thread save. I suggest to use Apache OJB in real projects.

SQLExecutor is not a O/R mapping technology. It is a framework for using JDBC. And believe it or not, JDBC is used in many (if not most) "real projects" in the J2EE world.

Thanks for the input,
Jeff

Jeff's right: O/R mapping is a separate problem. A "simpler JDBC" API is essential at some point in most projects. So there's a real need for something like Spring JDBC or SQL Executor.

Regards,
Rod

In the framework we have to create a Connection Pool object and pass it in the constructor of the SQLExecutor class to create its object.

String sql = "SELECT * FROM JDBC_TEST WHERE TEST_ID < ? AND TEST_DT > ?";
ConnectionPool conPool = new ConnectionPool(1, driverName, conURL,
     username, password);
SQLExecutor sqlExec = new SQLExecutor(conPool);

In my application (using Weblogic 5.1 application) I use the connection from the datasource i.e., using the Connection Pool defined in the Weblogic.properties file.
How can I use the framework in my application??

> the constructor of the SQLExecutor class to create its object.
>
> String sql = "SELECT * FROM JDBC_TEST WHERE TEST_ID < ? AND TEST_DT > ?";
> ConnectionPool conPool = new ConnectionPool(1, driverName, conURL,
>      username, password);
> SQLExecutor sqlExec = new SQLExecutor(conPool);
>
> In my application (using Weblogic 5.1 application) I use the connection from
> the datasource i.e., using the Connection Pool defined in the
> Weblogic.properties file.
> How can I use the framework in my application??

The ConnectionPool constructor is overloaded (see the Javadocs) so it should work with your datasource. Use the version with this signature:

ConnectionPool(java.sql.Connection con)
    (constructor uses the given connection (con) as its connection.)

So you might write code like this:
  ConnectionPool conPool = new ConnectionPool(myDataSource.getConnection());
  SQLExecutor sqlExec = new SQLExecutor(conPool);

Thank you for your question,
Jeff Smith

Thanks for framework! It has made my life easier already. I have been wondering how I should go about using the Statement.getGeneratedKeys() feature of JDBC3. Can anyone shed any light on how I might go about using this?

Thanks.

First of all, I am poor at english so, please forgive me incorrect sentences.

In connection Pooling mechanism, I think that it doesn't work properly.

I made up following method for testing the connection pooling.

During the testing, I got something strange results.


<
public static void testConnectionPoolingWithClosing()
    {
        ConnectionPool conPool = new ConnectionPool(3, driverName, connURL, username, password);
        String out = "";
        try
        {
            SQLExecutor sqlExec1 = new SQLExecutor(conPool);
            System.out.println("1st sqlExec1 creation : Connection Pool' size = "+sqlExec1.getConnectionPoolSize());
            System.out.println("1st sqlExec1 creation : Number of connections in Pool = "+sqlExec1.getConnectionNumInPool());
            SQLResults res1 = sqlExec1.runQueryCloseCon("select * from JDBC_TEST where CODE < 'E'");

            Connection [] result1 = conPool.getConnections();
            for(int i=0; i < result1.length; i++)
            {

              System.out.println("1st : ID of connection("+i+") = "+result1[i]);
            }

            boolean [] availability1 = sqlExec1.isAvailableConnection();
            for(int i=0; i < availability1.length; i++)
            {

              System.out.println("1st : connection availability("+i+") = "+availability1[i]);
            }
            System.out.println("\n");



            SQLExecutor sqlExec2 = new SQLExecutor(conPool);
            System.out.println("2nd sqlExec2 creation: Connection Pool' size = "+sqlExec2.getConnectionPoolSize());
            SQLResults res2 = sqlExec2.runQueryCloseCon("select * from JDBC_TEST where CODE > 'E'");
   
            Connection [] result2 = conPool.getConnections();
            for(int i=0; i < result2.length; i++)
            {

              System.out.println("2nd : ID of connection("+i+") = "+result2[i]);
            }

            boolean [] availability2 = sqlExec2.isAvailableConnection();
            for(int i=0; i < availability2.length; i++)
            {
              System.out.println("ID of : connection availability("+i+") = "+availability2[i]);
            }
            System.out.println("\n");



            SQLExecutor sqlExec3 = new SQLExecutor(conPool);
            System.out.println("3rd sqlExec3 creation : Connection Pool' size = "+sqlExec3.getConnectionPoolSize());
            SQLResults res3 = sqlExec3.runQueryCloseCon("select * from JDBC_TEST where CODE > 'E'");

            Connection [] result3 = conPool.getConnections();
            for(int i=0; i < result3.length; i++)
            {

              System.out.println("3rd : ID of connection("+i+") = "+result3[i]);
            }

            boolean [] availability3 = sqlExec3.isAvailableConnection();
            for(int i=0; i < availability3.length; i++)
            {
              System.out.println("3rd : connection availability("+i+") = "+availability3[i]);
            }
            System.out.println("\n");




            SQLExecutor sqlExec4 = new SQLExecutor(conPool);
            System.out.println("4th sqlExec4 &#49373;&#49457; : Connection Pool's size = "+sqlExec4.getConnectionPoolSize());
            SQLResults res4 = sqlExec4.runQueryCloseCon("select * from JDBC_TEST where CODE > 'E'");

            Connection [] result4 = conPool.getConnections();
            for(int i=0; i < result4.length; i++)
            {

              System.out.println("4th : ID of connection("+i+") = "+result4[i]);
            }

            boolean [] availability4 = sqlExec4.isAvailableConnection();
            for(int i=0; i < availability4.length; i++)
            {
              System.out.println("4th : connection availability("+i+") = "+availability4[i]);
            }
            System.out.println("\n");




            System.out.println("Last : Number of connections in Pool = "+sqlExec4.getConnectionNumInPool()+"\n\n");
        }
        finally
        {
          conPool.closeAllConnections();
        }
    }
    
    
 <    
    
    
1st sqlExec1 creation : Connection Pool' size = 3

1st sqlExec1 creation : Number of connections in Pool = 3

1st : ID of connection(0) = oracle.jdbc.driver.OracleConnection@18aaa1e

1st : ID of connection(1) = oracle.jdbc.driver.OracleConnection@a6aeed

1st : ID of connection(2) = oracle.jdbc.driver.OracleConnection@126804e

1st : connection availability(0) = false

1st : connection availability(1) = true

1st : connection availability(2) = true




2nd sqlExec2 creation : Connection Pool' size = 2

2nd : ID of connection(0) = = oracle.jdbc.driver.OracleConnection@a6aeed

2nd : ID of connection(1) = = oracle.jdbc.driver.OracleConnection@126804e

2nd : connection availability(0) = false

2nd : connection availability(1) = true




3rd sqlExec3 creation : Connection Pool' size = 1

3rd : ID of connection(0) = oracle.jdbc.driver.OracleConnection@126804e

3rd : connection availability(0) = false




4th sqlExec4 creation : Connection Pool' size = 1

4th : ID of connection(0) = oracle.jdbc.driver.OracleConnection@16a786

4th : connection availability(0) = true




Last : Number of connections in Pool = 1


<
Why used connections are removed instead of returning to connection pool?

When using SQLExecutor, how can I insert null values in INSERT queries?