Apache Commons Dbutils

1       Overview

The Commons dbutils class Library is a small collection of classes designed to be easy to use with JDBC . JDBC resource cleanup is trivial and error-prone, so that these classes abstract the cleanup code from your code, leaving the code you originally wanted to do with JDBC : Querying and updating data.

Use Some advantages of dbutils:

• There is no possibility of resource leaks. The correct JDBC encoding is not difficult, but it is time-consuming and tedious. This often results in a connection leak that can be difficult to track.

• A clear persistence code. The code that needs to persist data into the database is drastically reduced. The rest of the code clearly expresses your intentions without cluttering up the resources.

• from ResultSet automatically fills the JavaBean. You do not need to manually copy the column values to the bean instance through the setter method . each row of the ResultSet represents a fully populated bean instance.

2       scope of the package

The dbutils is designed to:

• Small - you should be able to understand the entire package in a very short time.

• Transparent-- Dbutils does not do any magic behind his back. You specify to it a query that executes the query and cleans up for you.

• Fast -- You don't need to create 1 million temporary objects using dbutils.

Dbutils is not:

• an object / Relationship Bridge -- There are a lot of good O/R tools. Dbutils is designed for developers who want to use JDBC .

• A data Access object ( DAO) Framework --dbutils can be used to build a DAO framework.

• An abstraction of an object-oriented Universal database object, like Table, Column, or PrimaryKey.

• any kind of heavyweight framework -the goal is a simple and easy -to-use JDBC Auxiliary Library.

3       Example3.1         Basic Use

Dbutils is a very small class library, so it does not take a long time to traverse the APIs of each class . The core classes or interfaces of Dbutils are queryrunner and Resultsethandler. You don't need to know about each of the other dbutils classes. The following examples show how to use these classes.

  Create a Resultsethandler implementation transformation first behavior one object[]resultsethandler<object[]> h = new  Resultsethandler<object[]> ()  {    public object[] handle (ResultSet &NBSP;RS)  throws SQLException {        if  (! Rs.next ())  {            return null;         }             resultsetmetadata meta = rs.getmetadata ();         int cols = meta.getcolumncount ();         Object[] result = new object[cols];        for   (int i = 0; i < cols; i++)  {              Result[i] = rs.getobject (i + 1);        }         return result;    }};//  Create a queryrunner that will use the specified DataSource connection Queryrunner run = new queryrunner (DataSource);//  Executes the query and gets the results returned by the processor Object[] result = run.query ("Select * from person where  name=? ", h, " John doe ");

You can also use the Java.sql.Connection object to execute the previous query instead of a DataSource. Note that you are responsible for shutting down Connection.

Resultsethandler<object[]> h = ...//And as above define a processor//no DataSource, so we must manually handle the connection Queryrunner run = new Queryrunner (); Connection conn = ...//open a connection try{object[] result = Run.query (conn, "select * from person WHERE name=?")         , h, "John Doe");   Use the result} finally {//Use this helper method, so we do not need to check for NULL dbutils.close (conn); }

You can not only fetch data from the database -- You can also insert or update data. The following example first inserts a person record to the database and then change person height.

Queryrunner run = new queryrunner ( dataSource ); try {      //  execute the SQL UPDATE statement and return the number we inserted      int inserts =  Run.update (  "insert into person  (name,height)  VALUES  (?,?)",                                  "John doe", 1.82 );      //  simplifying code by using variable parameters and automatic boxing      int updates =  Run.update (  "Update person set height=? where name=",                                 2.05,  "John doe"  );  } catch ( Sqlexception sqle)  {  &Nbsp;  //  handle it  } 

for long-running calls you can use Asyncqueryrunner executes an asynchronous call. the Asyncqueryrunner class has the same method as the Queryrunner; However, the method returns afuture.

Executorservice service = Executors.newcachedthreadpool (); Asyncqueryrunner Asyncrun = new Asyncqueryrunner (service); Connection conn = ... try {//Create an update call to callable future<integer> = Asyncrun.update (conn, "Update person SET Heigh T=?    WHERE name=? ", 2.05," John Doe "); Execute F.get ();} catch (SQLException Sqle) {//Handle exception}

3.2         ResultsethandlerImplement

in the example above, we implement The Resultsethandler interface converts the first line of ResultSet to object[]. This is a fairly generic implementation that can be reused across multiple projects. Dbutils Aware of this, A set of Resultsethandler is provided in the Org.apache.commons.dbutils.handlers package Implementations perform common conversions to arrays,maps, and JavaBeans. Each implementation has a version that converts only the first row in ResultSet and all the rows in the other transform ResultSet.

we'll start with an example that uses Beanhandler fetches each row from the ResultSet and transitions to JavaBean .

Queryrunner run = new Queryrunner (dataSource);//use Beanhandler to implement the first act of conversion resultset a person Javabeanresultsethandler <Person> h = new beanhandler<person> (person.class);// Executes the SQL statement using a placeholder parameter and returns the result of the new Person object Beanhandler produced by person p = run.query ("select * from person WHERE name=?", H, "John Doe");

at this point, we will use Beanlisthandler fetches all the rows from the ResultSet and transforms them into a JavaBeans list .

Queryrunner run = new Queryrunner (DataSource); Use Beanlisthandler to implement converting all rows of resultset to the list of person JavaBeans resultsethandler<list<person>> h = new Beanlisthandler<person> (Person.class); Executes the SQL statement and returns the list result of the person object produced by beanlisthandler list<person> persons = Run.query ("SELECT * from person", h);

3.3         CustomRowprocessor

each of the provided The Resultsethandler implementation receives a rowprocessor to do the actual conversion of the row-to-object. The default processor is implemented using Basicrowprocessor, but you can implement a custom version of the plugin. Perhaps the most common customization is to implement the Tobean () method to handle custom database data type issues.

3.4         CustomBeanprocessor

Basicrowprocessor uses a beanprocessor transformation ResultSet column to the JavaBean property. You can override the processing steps in the subclass to handle the data type mappings for your application. Provides an implementation that represents a data type conversion to a JDBC driver.

The Beanprocessor Mapping column to the bean attribute is recorded in the Javadoc of Beanprocessor.tobean () . The column name must match The Bean 's property name (ignoring case). For example,the FirstName column can be stored by calling the Bean's setfirstname () method. However, many database column names include characters that either cannot be used or are not typically used for Java method names. You can map these columns to the Bean properties:

• in the column aliases in SQL, so they can match the Java name:Select social_sec# as SocialSecurityNumber from person

• Implement Beanprocessor and overwrites the Mapcolumnstoproperties () method to remove offensive characters.

Apache Commons Dbutils