safejdbc
Class SQLExecuter

java.lang.Object
  |
  +--safejdbc.SQLExecuter

All Implemented Interfaces:

SQL

public class SQLExecuter

extends java.lang.Object

implements SQL

This is probably the most important class within the SafeJDBC framework, because this class provides methods to actually access the database.

Constructor Summary

SQLExecuter(ConnectionProvider conprovider)
To create an SQLExecuter instance a ConnectionProvider is needed.

Method Summary

void	executeTX(TXCodeBlock txCodeBlock) Use this method, to execute several database calls in a single transaction.
void	query(java.lang.String prepSql, FillingCommand cmd, ResultSetProcessor rsProcessor) Use this method to execute SQL-Query-Statements such as select from.
void	query(java.lang.String sqlStr, ResultSetProcessor rsProcessor) Use this method to execute SQL-Query-Statements such as select from. Example: sql.query("SELECT id, name FROM customer WHERE id > 0", new ResultSetIterator() { public void forEachRow(ResultSet rs) throws SQLException { System.out.println(rs.getString("name")); }});
int	singleIntQuery(java.lang.String sqlStmt) This method is a convenience method for executing a SQL query which has a single row with a single column containing a number as a result.
int	singleIntQuery(java.lang.String prepSqlStmt, FillingCommand cmd) This method is a convenience method for executing a SQL query which has a single row with a single column containing a number as a result.
int	update(java.lang.String sqlStr) Use this method to execute modifying SQL-statements such as insert into or update. Example: sql.update("INSERT INTO customer (id, name) VALUES (1, 'John Smith')");
int	update(java.lang.String prepSql, FillingCommand cmd) Use this method to execute modifying SQL-statements such as insert into or update, with a PreparedStatement.

Methods inherited from class java.lang.Object

clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Constructor Detail

SQLExecuter

public SQLExecuter(ConnectionProvider conprovider)

To create an SQLExecuter instance a ConnectionProvider is needed. Every method that accesses the database, addresses at first the ConnectionProvider to get a connection. So be sure to use a well designed ConnectionProvider implementation. A bad ConnectionProvider implementation is definitely a perfomance penalty for your application.

Method Detail

executeTX

public void executeTX(TXCodeBlock txCodeBlock)
               throws java.sql.SQLException

Use this method, to execute several database calls in a single transaction. If an exception occurs the transaction is rolled back.
Example 1:

 sqlexecuter.executeTX(new TXCodeBlock() {
     public void doTransaction (SQL sql) throws SQLException {
         sql.update ("INSERT INTO customer (id, name) VALUES (1, 'Arno Haase')");
         sql.update ("INSERT INTO customer (id, name) VALUES (2, 'Jan Hermanns')");
     }});
 

Note that the SQL instance passed to doTransaction() can also be handed to methods outside the TXCodeBlock. This is extremly usefull to factor out code.
Example 2:This example does the same as the above.

 private void insertArno(SQL sql) throws SQLException {
     sql.update ("INSERT INTO customer (id, name) VALUES (1, 'Arno Haase')");
 }

 private void insertJan(SQL sql) throws SQLException {
     sql.update ("INSERT INTO customer (id, name) VALUES (2, 'Jan Hermanns')");
 }

 sqlexecuter.executeTX(new TXCodeBlock() {
     public void doTransaction (SQL sql) throws SQLException {
         insertArno(sql);
         insertJan(sql);
     }});
 

Parameters:

txCodeBlock - contains a bunch of statements, to be executed in a single transaction.

update

public int update(java.lang.String sqlStr)
           throws java.sql.SQLException

Description copied from interface: SQL

Use this method to execute modifying SQL-statements such as insert into or update.
Example:

 sql.update("INSERT INTO customer (id, name) VALUES (1, 'John Smith')");
 

Specified by:

update in interface SQL

Following copied from interface: safejdbc.SQL

Parameters:

sqlStr - the statement to be executed

Returns:

the number of altered rows.

update

public int update(java.lang.String prepSql,
                  FillingCommand cmd)
           throws java.sql.SQLException

Description copied from interface: SQL

Use this method to execute modifying SQL-statements such as insert into or update, with a PreparedStatement. The statement is executed in a separate transaction, the change is committed immediately.
Example:

 final int id = 1;
 final String name = "John Smith";
  
 sql.update("INSERT INTO customer (id, name) VALUES (?, ?)",
            new FillingCommand() {
                public void fill(PreparedStatement s) throws SQLException {
                    s.setInt(1, id);
                    s.setString(2, name);
                }});
 

Specified by:

update in interface SQL

Following copied from interface: safejdbc.SQL

Parameters:

sqlStr - the prepared statement to be executed

Returns:

the number of altered rows.

query

public void query(java.lang.String sqlStr,
                  ResultSetProcessor rsProcessor)
           throws java.sql.SQLException

Description copied from interface: SQL

Use this method to execute SQL-Query-Statements such as select from.
Example:

 sql.query("SELECT id, name FROM customer WHERE id > 0",
           new ResultSetIterator() {
               public void forEachRow(ResultSet rs) throws SQLException {
                   System.out.println(rs.getString("name"));
               }});
 

Specified by:

query in interface SQL

Following copied from interface: safejdbc.SQL

Parameters:

sqlStr - contains the SQL query

rsProcessor - is used to process the ResultSet resulting from the query. The ResultSet of this SQL-Query will be handed to the ResultSetProcessor; if an iteration over all rows of the result set is desired, use a ResultSetIterator (like in the above example).

query

public void query(java.lang.String prepSql,
                  FillingCommand cmd,
                  ResultSetProcessor rsProcessor)
           throws java.sql.SQLException

Description copied from interface: SQL

Use this method to execute SQL-Query-Statements such as select from.
Example:

 final int id_threshold = 3;
      
 sql.query("SELECT id, name FROM customer WHERE id > ?",
           new FillingCommand() {
               public void fill(PreparedStatement s) throws SQLException {
                   s.setInt(1, id_threshold);
               }},
           new ResultSetIterator() {
               public void forEachRow(ResultSet rs) throws SQLException {
                   System.out.println(rs.getString("name"));
               }});
 

Specified by:

query in interface SQL

Following copied from interface: safejdbc.SQL

Parameters:

prepSql - contains the SQL query in the "PreparedStatement" form

cmd - is called to set the IN parameters of the prepared statement

rsProcessor - is used to process the ResultSet resulting from the query. The ResultSet of this SQL-Query will be handed to the ResultSetProcessor; if an iteration over all rows of the result set is desired, use a ResultSetIterator.

singleIntQuery

public int singleIntQuery(java.lang.String sqlStmt)
                   throws java.sql.SQLException

This method is a convenience method for executing a SQL query which has a single row with a single column containing a number as a result. This method is useful for queries such as "SELECT COUNT(*) FROM ... WHERE ...".
If the result set is empty or contains more than one row, an IllegalStateException is thrown.

Specified by:

singleIntQuery in interface SQL

Returns:

the number in the result set

singleIntQuery

public int singleIntQuery(java.lang.String prepSqlStmt,
                          FillingCommand cmd)
                   throws java.sql.SQLException

This method is a convenience method for executing a SQL query which has a single row with a single column containing a number as a result. This method is useful for queries such as "SELECT COUNT(*) FROM ... WHERE ...".
If the result set is empty or contains more than one row, an IllegalStateException is thrown.

Specified by:

singleIntQuery in interface SQL

Returns:

the number in the result set