com.jfw.database
Class QueryGateway

java.lang.Object
  com.jfw.database.QueryGateway

public final class QueryGateway
extends java.lang.Object

Used for execute a sql command. The same instance can be used for more executions.
The result set and prepared statement objects are closed automatically when the last row is readed. For change the default behavior the setAutoClose(boolean) method must by executed (with value false) before the use of the method executeQuery(java.lang.String, java.util.ArrayList).
The sql command parameters can be the null object, String, ArrayList (for multi values), byte[] (for BLOB) and FunctionObject (for sql commands like sysdate).

Example 1 (valid for a select)

 String query = "select name from table where position=?";
 ArrayList params = new ArrayList();
 params.add("1");
 
 QueryGateway queryGateway = new QueryGateway();
 queryGateway.executeQuery(query, params);
 
 while(queryGateway.next())
 {
   String value = queryGateway.getString("name");
 }
 
 queryGateway.close();

Example 2 (valid for a select)

 String query = "select name from table where position in (";
 query += QueryGateway.getMultiKey(1);
 query += ") and surname=? and date > ";
 query += QueryGateway.getMultiKey(2);
 
 ArrayList params = new ArrayList();
 
 ArrayList multiParams = new ArrayList();
 params.add(multiParams);
 multiParams.add("1");
 multiParams.add("2");
 
 params.add("surname1");
 
 ArrayList functionParam = new ArrayList();
 params.add(functionParam);
 functionParam.add(new FunctionObject("sysdate()"));
 
 QueryGateway queryGateway = new QueryGateway();
 queryGateway.executeQuery(query, params);
 HashMap outputdata = new HashMap();
 queryGateway.getAllData(outputdata);
 queryGateway.close();

Example 3 (valid for all others sql commands)

 boolean commit = true;
 String query = "insert into table(column1, column2) values(?,?)";
 
 ArrayList params = new ArrayList();
 params.add("1");
 params.add("2");
 
 QueryGateway queryGateway = new QueryGateway();
 int rowsChanged = queryGateway.execute(query, params);
 
 if(rowsChanged == 1)
   commit = true;
 else
   commit = false;
 
 queryGateway.close(commit);

Field Summary

static int	CONNECTION Used by the method close().
static int	RESULTSET Used by the method close().
static java.lang.String	ROWS_CHANGED_KEY The key paired with the numbers of rows changed by the query.
static int	STATEMENT Used by the method close().

Constructor Summary

QueryGateway()
Use the default db resource from the jfw.properties.

QueryGateway(java.sql.Connection conn)
Use the connection object gived.

QueryGateway(java.lang.String resourceName)
Use the parameter resourceName as db resource.

QueryGateway(Transaction transaction)
Use the Transaction for get the Connection to use.

Method Summary

void	close() Release connection (if isTransaction is false and resourceName not null) and close result set and prepared statement.
void	close(int type) Release or close a specific type of resource.
void	commit() Commit the sql commands executed.
void	commit(boolean commit) Commit or rollback the sql commands executed.
int	execute(java.lang.String queryString) Execute a sql command different from select.
int	execute(java.lang.String queryString, java.util.ArrayList params) Execute a sql command different from select.
void	executeQuery(java.lang.String queryString) Execute a sql command of type select.
void	executeQuery(java.lang.String queryString, java.util.ArrayList params) Execute a sql command of type select.
void	finalize() Call the super method.
java.util.HashMap	getAllData() Use method {#result2Hash(HashMap,String)}.
void	getAllData(java.util.HashMap hashContainer) Use method {#result2Hash(HashMap,String)}.
void	getAllData(java.util.HashMap hashContainer, java.lang.String key) Use method {#result2Hash(HashMap,String,int,int)}.
void	getAllData(java.util.HashMap hashContainer, java.lang.String key, int rows2Read, int firstRow) Use method ReadDbData.result2Hash(java.sql.ResultSet, java.util.HashMap, java.lang.String, boolean, java.lang.String, int, int).
boolean	getAutoClose() Return the value of attribute autoClose.
byte[]	getBlob(int indexColumn) Return the value of a column.
byte[]	getBlob(java.lang.String nameColumn) Return the value of a column.
boolean	getClosed() Return the value of attribute closed.
java.sql.Connection	getConnection() Return value of attribute connection.
java.sql.ResultSet	getCursor() Return the ResultSet object.
static java.lang.String	getMultiKey(int index) Return a multi key place holder.
java.lang.String	getString(int indexColumn) Return the value of a column.
java.lang.String	getString(java.lang.String nameColumn) Return the value of a column.
boolean	next() Return true if exist a row to read.
void	rollback() Rollback the sql commands executed.
void	setAutoClose(boolean newValue) Set value of attribute autoClose.

Methods inherited from class java.lang.Object

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

Field Detail

ROWS_CHANGED_KEY

public static final java.lang.String ROWS_CHANGED_KEY

The key paired with the numbers of rows changed by the query.

See Also:

Constant Field Values

RESULTSET

public static final int RESULTSET

Used by the method close(). Indicates the result set object.

See Also:

Constant Field Values

STATEMENT

public static final int STATEMENT

Used by the method close(). Indicates the prepared statement object.

See Also:

Constant Field Values

CONNECTION

public static final int CONNECTION

Used by the method close(). Indicates the connection object.

See Also:

Constant Field Values

Constructor Detail

QueryGateway

public QueryGateway()

Use the default db resource from the jfw.properties.

QueryGateway

public QueryGateway(java.lang.String resourceName)

Use the parameter resourceName as db resource.

Parameters:

resourceName - the name of a resource as specified in the jfw.properties.

QueryGateway

public QueryGateway(Transaction transaction)
             throws java.lang.Exception

Use the Transaction for get the Connection to use.

Parameters:

transaction - a Transaction object.

Throws:

java.lang.Exception

QueryGateway

public QueryGateway(java.sql.Connection conn)
             throws java.lang.Exception

Use the connection object gived.

Parameters:

conn - the Connection object to use.

Throws:

java.lang.Exception

Method Detail

getConnection

public java.sql.Connection getConnection()

Return value of attribute connection.

Returns:

value of attribute connection.

executeQuery

public void executeQuery(java.lang.String queryString,
                         java.util.ArrayList params)
                  throws DatabaseException

Execute a sql command of type select.

Parameters:

queryString - the query to execute.

params - the paramaters used in the query. If null no parameters used by the query.

Throws:

DatabaseException

executeQuery

public void executeQuery(java.lang.String queryString)
                  throws DatabaseException

Execute a sql command of type select. Use the other method for the execution. The value of parameter params is null.

Throws:

DatabaseException

execute

public int execute(java.lang.String queryString,
                   java.util.ArrayList params)
            throws DatabaseException

Execute a sql command different from select.

Parameters:

queryString - the query to execute.

params - the paramaters used in the query. If null no parameters used by the command.

Returns:

number of rows changed.

Throws:

DatabaseException

execute

public int execute(java.lang.String queryString)
            throws DatabaseException

Execute a sql command different from select. Use the other method for the execution. The value of parameter params is null.

Throws:

DatabaseException

getMultiKey

public static java.lang.String getMultiKey(int index)

Return a multi key place holder.

Parameters:

index - the index to use for the multi key.

Returns:

a multi key place holder.

next

public boolean next()
             throws DatabaseException

Return true if exist a row to read. If autoClose is true then the result set and the prepared statement are closed when no other rows exist.

Returns:

true if exist a row to read, false in other case.

Throws:

DatabaseException

getString

public java.lang.String getString(int indexColumn)
                           throws DatabaseException

Return the value of a column.

Parameters:

indexColumn - the numeric index of the column.

Returns:

the value of the column.

Throws:

DatabaseException

getString

public java.lang.String getString(java.lang.String nameColumn)
                           throws DatabaseException

Return the value of a column.

Parameters:

nameColumn - the name of the column.

Returns:

the value of the column.

Throws:

DatabaseException

getBlob

public byte[] getBlob(int indexColumn)
               throws DatabaseException

Return the value of a column.

Parameters:

indexColumn - the numeric index of the column.

Returns:

the value of the column.

Throws:

DatabaseException

getBlob

public byte[] getBlob(java.lang.String nameColumn)
               throws DatabaseException

Return the value of a column.

Parameters:

nameColumn - the name of the column.

Returns:

the value of the column.

Throws:

DatabaseException

getCursor

public java.sql.ResultSet getCursor()

Return the ResultSet object.

Returns:

the ResultSet object.

getAllData

public java.util.HashMap getAllData()
                             throws DatabaseException

Use method {#result2Hash(HashMap,String)}. The value of parameter hashContainer is a new HashMap object. The value of parameter key is ReadDbData.ALL_ROWS_KEY.

Returns:

the HashMap with the data.

Throws:

DatabaseException

getAllData

public void getAllData(java.util.HashMap hashContainer)
                throws DatabaseException

Use method {#result2Hash(HashMap,String)}. The value of parameter key is ReadDbData.ALL_ROWS_KEY.

Parameters:

hashContainer - the HashMap with the data.

Throws:

DatabaseException

getAllData

public void getAllData(java.util.HashMap hashContainer,
                       java.lang.String key)
                throws DatabaseException

Use method {#result2Hash(HashMap,String,int,int)}. The value of parameter rows2Read is -1. The value of parameter firstRow is 1.

Parameters:

hashContainer - the HashMap with the data.

Throws:

DatabaseException

getAllData

public void getAllData(java.util.HashMap hashContainer,
                       java.lang.String key,
                       int rows2Read,
                       int firstRow)
                throws DatabaseException

Use method ReadDbData.result2Hash(java.sql.ResultSet, java.util.HashMap, java.lang.String, boolean, java.lang.String, int, int). The data are returned in the HashMap gived as parameter. The data container is paired with key gived by the parameter key.

Parameters:

hashContainer - the HashMap with the data.

key - the key to use in HashMap for pair the db data readed.

rows2Read - max number of rows to read. If the value is -1 then all the available rows will be read.

firstRow - the first row to read. The first row index is 1.

Throws:

DatabaseException

setAutoClose

public void setAutoClose(boolean newValue)

Set value of attribute autoClose.

Parameters:

newValue - if value is true then the result set and the prepared statement are closed when last rows has been readed. If false then the objects is not closed.

getAutoClose

public boolean getAutoClose()

Return the value of attribute autoClose.

Returns:

the value of attribute autoClose.

getClosed

public boolean getClosed()

Return the value of attribute closed.

Returns:

the value of attribute closed.

close

public void close()
           throws DatabaseException

Release connection (if isTransaction is false and resourceName not null) and close result set and prepared statement. The sql commands are rollbacked (if isTransaction is false) so an explicit commit must be done before the call of this method.

Throws:

DatabaseException

close

public void close(int type)
           throws DatabaseException

Release or close a specific type of resource. The connection is released only if isTransaction is false and resourceName not null.

Parameters:

type - possible values are give by attributes RESULTSET, STATEMENT, CONNECTION.

Throws:

DatabaseException

commit

public void commit()
            throws DatabaseException

Commit the sql commands executed.

Throws:

DatabaseException

commit

public void commit(boolean commit)
            throws DatabaseException

Commit or rollback the sql commands executed.

Parameters:

commit - if true do the commit, else do the rollback.

Throws:

DatabaseException

rollback

public void rollback()
              throws DatabaseException

Rollback the sql commands executed.

Throws:

DatabaseException

finalize

public void finalize()

Call the super method. If the close() method has not been explicity called then is called now.

Overrides:

finalize in class java.lang.Object