com.bentofw.dao
Interface SimpleDaoHelper

All Known Implementing Classes:

DaoHelperForJdbc, DaoHelperForJndi, DaoHelperForPooledStatement, SimpleDaoHelperImpl

public interface SimpleDaoHelper

Given the QueryData object, this class creates the PreparedStatement object and executes it.

Given the QueryData object, the implementation of SimpleDaoHelper interface:

• Creates the PreparedStatement object
• Sets the specified values on the PreparedStatement object
• Executes the PreparedStatement object

On the QueryData object, you can specify:

• The arbitrary SQL statement
• A series of data to be inserted into the SQL statement

How to create PreparedStatement depends on the implementation. At this time, the following 3 implementations are available:

• DaoHelperForJndi: Creates PreparedStatement by JNDI DataSource
• DaoHelperForJdbc: Creates PreparedStatement by JDBC Connection
• DaoHelperForPooledStatement: Creates PreparedStatement by OOP PooledStatement

As for the first one, the JNDI DataSource must be found by:

• java:comp/env/jdbc/DaoHelperDataSource

As for the rest of them, the JDBC driver name and the related information must be written in the property resource file. For the details, please look into README.html of this API. All these implementations are packaged under com.bentofw.dao package.
Given the fully qualified class names, DaoHelperFactory returns the intended implementation of SimpleDaoHelper interface.

SimpleDaoHelper interface implements the 2 important methods, one for SELECT and another for INSERT, UPDATE or DELETE. And both of them takes the QueryData object as their parameters. They are:

• select(QueryData,int[])
• update(QueryData)

For example, please look into the very simple SQL table below:

Column Name	SQL Type	Java Type
id	DECIMAL(3,0)	int
name	VARCHAR(255)	String
age	DECIMAL(3,0)	int

If you'd like to insert the following row into the above table:

• id=50
• name=John Dow
• age=30

Your code will look like this:

   // Let's try DaoHelperForJndi, which creates
   // PreparedStatement from JNDI DataSource.
   // JNDI DataSource name must be available as the
   // environment valuable.
   String className="com.bentofw.dao.DaoHelperForJndi";
   SimpleDaoHelper helper=DaoHelperFactory.getDaoHelper(className);

   // Please specify your SQL statement here...
   String insert="INSERT INTO example (id,name,age) VALUES(?,?,?)";
   QueryData query=new QueryData(insert);

   // Specify the values to be set on the SQL statement.
   // The order is important!
   query.addInt(50);
   query.addString("John Dow");
   query.addInt(30);

   // Its time to execute your SQL statement...
   int result=helper.update(query);

 

In case of SELECT, the array of return types must also be specified. If you'd like to find the id and name for the person older than 20:

   // Let's try DaoHelperForJndi, which creates
   // PreparedStatement from JNDI DataSource.
   // JNDI DataSource name must be available as the
   // environment valuable.
   String className="com.bentofw.dao.DaoHelperForJndi";
   SimpleDaoHelper helper=DaoHelperFactory.getDaoHelper(className);

   // Please specify your SQL statement here...
   String select="SELECT id,name FROM example WHERE age > ?";
   QueryData query=new QueryData(select);

   // Specify the value to be set on the SQL statement.
   query.addInt(20);

   // Please specify the return type(s) as the int array.
   // This int array must consist of the pre-defined int
   // constants...
   // The order is important!
   int[] types={QueryData.INT,QueryData.STRING};

   // Its time to execute your SQL statement...
   List list=helper.select(query,types);

   // The returned List includes the selected rows.
   // You can iterate each row...
   Iterator it=list.iterator();
   while(it.hasNext()){

      // Each object in the returned List is also the List.
      // And this nested List object represents each row...
      List row=(List)(it.next());

      // Get the first column...
      int id=(Integer)(row.get(0)).intValue();
      // Get the second column...
      String name=(String)(row.get(1));

   }

 

In the above examples, the fully qualified class name of SimpleDaoHelper interface and the SQL statements are hard-coded within the source code. But, in the production code, all these Strings should be read from some external resource, such as the deployment descriptor or property resource file.

For example, if the JNDI environment valuables are available in your environment:

   InitialContext ic=new InitialContext();

   String className=(String)(ic.lookup("java:comp/env/ejb/example/DaoHelperClass"));
   String insert=(String)(ic.lookup("java:comp/env/ejb/example/Insert"));
   String select=(String)(ic.lookup("java:comp/env/ejb/example/SelectByAge"));

 

By this way, your code can be independent from the SQL table name and its column names.

But, as you know, looking up something on the InitialContext again and again will be somewhat resource-consuming task. To avoid this, JndiLocator is included in this package. You can think of it as a kind of ServiceLocator. Once the intended object is looked up, this class keeps its reference in the cache. So, the subsequent call with the same JNDI name can return very quickly, without looking up it on the InitialContext again. For example, the above 4 lines of codes can be:

   // Get the instance of JndiLocator
   JndiLocator locator=JndiLocator.getInstance();

   // Look up the environment values...
   String className=locator.getStringEnv("java:comp/env/ejb/example/DaoHelperClass");
   String insert=locator.getStringEnv("java:comp/env/ejb/example/Insert");
   String select=locator.getStringEnv("java:comp/env/ejb/example/SelectByAge");

 

Let's look into more practical example. Assuming that you are going to implement some very simple DAO (Data Access Object), your source code will look like this:

 public class ExampleDaoforOracle
    implements ExampleDao{

    private SimpleDaoHelper helper;
    private String insert;
    private String select;

    public ExampleDaoforOracle(){
       helper=DaoHelperFactory.getDaoHelper(JNDINames.HELPER_CLASS);
       JndiLocator locator=JndiLocator.getInstance();
       insert=locator.getStringEnv(JNDINames.INSERT);
       select=locator.getStringEnv(JNDINames.SELECT);
    }

    public void insert(ExampleValue value){
       QueryData query=new QueryData(insert);
       query.addInt(value.getId());
       query.addString(value.getName());
       helper.update(query);
    }

    public ExampleValue select(int id){
       QueryData query=new QueryData(select);
       query.addInt(id);
       int[] types={QueryData.INT,QueryData.STRING};
       List list=helper.select(query,types);
       Iterator it=list.iterator();
       ExampleValue value=null;
       if(it.hasNext()){
          List row=(List)(it.next());
          value=new ExampleValue();
          value.setId((Integer)(row.get(0)).intValue());
          value.setSubject((String)(row.get(1)));
       }
       return value;
    }

 }

 

As you see, the source code of your DAO (Data Access Object) can be free from JNDI/JDBC related stuff and be very simple.

Author:

Jun Inamori

See Also:

QueryData

Method Summary

void	closeResource(java.sql.ResultSet res, java.sql.PreparedStatement ps, java.sql.Connection con, boolean isOK) Close the specified ResultSet, PreparedStatement and Connection.
java.sql.Connection	getConnection() Return the JDBC Connection.
java.sql.PreparedStatement	getPrepareStatement(java.sql.Connection con, java.lang.String sql) Return the JDBC PreparedStatement for the given SQL statement.
java.util.List	select(QueryData data, int[] return_types) Based on the given QueryData object, this method executes PreparedStatement.
int	update(QueryData data) Based on the given QueryData object, this method executes PreparedStatement.

Method Detail

select

java.util.List select(QueryData data,
                      int[] return_types)
                      throws java.sql.SQLException,
                             SimpleException

Based on the given QueryData object, this method executes PreparedStatement.

Parameters:

data - QueryData object.

return_types - The int array which consists of the pre-defined int constants.

Returns:

List object which includes List objects, each of which represents the selected row.

Throws:

java.sql.SQLException

SimpleException

update

int update(QueryData data)
           throws java.sql.SQLException,
                  SimpleException

Based on the given QueryData object, this method executes PreparedStatement.

Parameters:

data - QueryData object.

Returns:

int value returned from PreparedStatement.

Throws:

java.sql.SQLException

SimpleException

getConnection

java.sql.Connection getConnection()
                                  throws java.sql.SQLException

Return the JDBC Connection. How to get the Connection depends on the implementation.

In case that you use DaoHelperForJndi, this method queries Connection on the JNDI DataSource. If you use DaoHelperForJdbc, Connection is created from DriverManager each time this method is called. Or, when DaoHelperForPooledStatement is used, this method returns null, because OOP PooledStatement keeps the Connection in its pool along with the PreparedStatement.

Returns:

Connection

Throws:

java.sql.SQLException

getPrepareStatement

java.sql.PreparedStatement getPrepareStatement(java.sql.Connection con,
                                               java.lang.String sql)
                                               throws java.sql.SQLException

Return the JDBC PreparedStatement for the given SQL statement.

In case that you use DaoHelperForJndi or DaoHelperForJdbc, this method creates PreparedStatement from the specified Connection.

If you use DaoHelperForPooledStatement, this method picks the PreparedStatement from the pool of OOP PooledStatement. Because Connection is always null, please specify null as the first parameter.

Parameters:

con - JDBC Connection

sql - SQL statement

Returns:

PreparedStatement

Throws:

java.sql.SQLException

closeResource

void closeResource(java.sql.ResultSet res,
                   java.sql.PreparedStatement ps,
                   java.sql.Connection con,
                   boolean isOK)

Close the specified ResultSet, PreparedStatement and Connection.

In case that you use DaoHelperForJndi or DaoHelperForJdbc, this method calls close method on them.

If you use DaoHelperForPooledStatement, this method closes only ResultSet. And PreparedStatement will be pushed back to the pool of OOP PooledStatement. As for Connection, this method does nothing, because it is always null.

Parameters:

res - ResultSet

ps - PreparedStatement

con - Connection

isOK - This parameter will be used only by DaoHelperForPooledStatement, because the PreparedStatement should not be pushed back to the pool if something is wrong. But, to make your code portable between the implementations, please specify the correct value. If SQLException is thrown while playing with PreparedStatement, please specify false. Otherwise, true.