jodd.db
Class SqlUtil

java.lang.Object
  extended byjodd.db.SqlUtil
Direct Known Subclasses:
SqlUtilProfiler

public class SqlUtil
extends java.lang.Object

Smart-and-slick SQL facade. Class is not fool-proof and threadsafe.

To Do:
Add more PreparedStatement setXXX() methods

Constructor Summary
SqlUtil(java.sql.Connection conenction)
          Default constructor from common Connection.
SqlUtil(ConnectionPool cp)
          Default constructor from ConnectionPool.
 
Method Summary
 void close()
          Closes result set (if previously created) and the statement.
 void close(java.sql.ResultSet rs)
          Deprecated. use close() instead
 void closeAll()
          Closes result set (if created), statements and returns connection to db pool when no ResultSets are used.
 void closeAll(java.sql.ResultSet rs)
          Deprecated. use closeAll() instead.
 void commit()
          Makes all changes made since the previous commit/rollback permanent and releases any database locks currently held by this object.
 java.sql.ResultSet executeQuery()
          Executes SELECT statements, but works with all statements.
 int executeUpdate()
          Executes UPDATE, INSERT or DELETE statements.
 java.lang.Boolean getAutoCommit()
          Retrieves the current auto-commit mode for this object.
static boolean getDebugMode()
          Returns current debug mode.
static java.lang.Boolean getDefaultAutoCommit()
          Returns default auto-commit mode or null if nothing set.
 java.lang.Boolean getStoredAutoCommit()
          Returns the stored auto-commit mode for the connection.
 void rollback()
          Undoes all changes made in the current transaction and releases any database locks currently held by this object.
 void setArray(int ndx, java.sql.Array value)
          Sets value of Array parameter in sql string.
 void setAutoCommit(boolean autoCommit)
          Sets this connection's auto-commit mode to the given state.
 void setBigDecimal(int ndx, java.math.BigDecimal value)
          Sets value of BigDecimal parameter in sql string.
 void setBlob(int ndx, java.sql.Blob value)
          Sets value of Blob parameter in sql string.
 void setBoolean(int ndx, boolean value)
          Sets value of boolean parameter in sql string.
 void setByte(int ndx, byte value)
          Sets value of byte parameter in sql string.
 void setClob(int ndx, java.sql.Clob value)
          Sets value of Clob parameter in sql string.
 void setDate(int ndx, java.sql.Date value)
          Sets value of java.sql.Date parameter in sql string.
static void setDebugMode(boolean b)
          Sets debug mode.
static void setDefaultAutoCommit(boolean autoCommit)
          Sets default auto-commit that SqlUtil must provide before executing new statement, neverness if setAutoCommit is used or not.
 void setDouble(int ndx, double value)
          Sets value of double parameter in sql string.
 void setFloat(int ndx, float value)
          Sets value of float parameter in sql string.
 void setInt(int ndx, int value)
          Sets value of int parameter in sql string.
 void setLong(int ndx, long value)
          Sets value of long parameter in sql string.
 void setSql(java.lang.String sql)
          Sets and prepares a SQL command by using forward only and read only prepared statement.
 void setSql(java.lang.String sql, int resultType, int resultSetConcurrency)
          Sets and prepares a SQL command by using prepared statements.
 void setStaticSql(java.lang.String sql)
          Sets and prepares a SQL command by using forward only and read only statement.
 void setStaticSql(java.lang.String sql, int resultType, int resultSetConcurrency)
          Sets and prepares a SQL command by using simple statements.
 void setString(int ndx, java.lang.String value)
          Sets value of String parameter in sql string.
 void setTimestamp(int ndx, java.sql.Timestamp value)
          Sets value of java.sql.Timestamp parameter in sql string.
 java.lang.String toString()
          Returns formated pseudo-SQL string.
 
Methods inherited from class java.lang.Object
clone, equals, finalize, getClass, hashCode, notify, notifyAll, wait, wait, wait
 

Constructor Detail

SqlUtil

public SqlUtil(ConnectionPool cp)
Default constructor from ConnectionPool.

Parameters:
cp - connection pool instance

SqlUtil

public SqlUtil(java.sql.Connection conenction)
Default constructor from common Connection.

Parameters:
conenction - opened db connection
Method Detail

setSql

public void setSql(java.lang.String sql)
            throws java.sql.SQLException
Sets and prepares a SQL command by using forward only and read only prepared statement.

Parameters:
sql - sql query string.
Throws:
java.sql.SQLException
See Also:
setSql(String, int, int)

setSql

public void setSql(java.lang.String sql,
                   int resultType,
                   int resultSetConcurrency)
            throws java.sql.SQLException
Sets and prepares a SQL command by using prepared statements.

It first closes existing resultset, if any availiable. Then it closes existing prepared statement. After, obtains connection from database if not already taken. Sets commit mode, if necessary.

Parameters:
sql - sql query string.
resultType - result type
resultSetConcurrency - result concurrency
Throws:
java.sql.SQLException

setStaticSql

public void setStaticSql(java.lang.String sql)
                  throws java.sql.SQLException
Sets and prepares a SQL command by using forward only and read only statement.

Parameters:
sql - sql query string.
Throws:
java.sql.SQLException
See Also:
setStaticSql(String, int, int)

setStaticSql

public void setStaticSql(java.lang.String sql,
                         int resultType,
                         int resultSetConcurrency)
                  throws java.sql.SQLException
Sets and prepares a SQL command by using simple statements.

It first closes existing resultset, if any availiable. Then it closes existing statement. After, obtains connection from database if not already taken. Sets commit mode, if necessary.

Parameters:
sql - sql query string.
resultType - result type
resultSetConcurrency - concurency type
Throws:
java.sql.SQLException

executeQuery

public java.sql.ResultSet executeQuery()
                                throws java.sql.SQLException
Executes SELECT statements, but works with all statements. Previously closes existing result set.

Returns:
ResultSet
Throws:
java.sql.SQLException

executeUpdate

public int executeUpdate()
                  throws java.sql.SQLException
Executes UPDATE, INSERT or DELETE statements.

Returns:
result of executeUpdate()
Throws:
java.sql.SQLException

close

public void close(java.sql.ResultSet rs)
Deprecated. use close() instead

Closes result set and the statement. Connection is not closed.

Parameters:
rs - ResultSet created by executeQuery(), or null if ResultSet is not being used.
See Also:
close()

close

public void close()
Closes result set (if previously created) and the statement. Connection is not closed.


closeAll

public void closeAll(java.sql.ResultSet rs)
Deprecated. use closeAll() instead.

Closes result set, preparead statements, sets default commit mode for connection and returns connection to db pool.

Parameters:
rs - ResultSet created by executeQuery(), or null if ResultSet is not being used.

closeAll

public void closeAll()
Closes result set (if created), statements and returns connection to db pool when no ResultSets are used.


setDefaultAutoCommit

public static void setDefaultAutoCommit(boolean autoCommit)
Sets default auto-commit that SqlUtil must provide before executing new statement, neverness if setAutoCommit is used or not. By using this method and setting default auto-commit value to be the same as it is for database, it is guaranteed that if it is not explicitly set different, the default mode will be used.

If default auto-commit mode is not used, connection will be examined on first usage, and that commit status will be taken as the default one.

Parameters:
autoCommit - default auto-commit mode

getDefaultAutoCommit

public static java.lang.Boolean getDefaultAutoCommit()
Returns default auto-commit mode or null if nothing set.

Returns:
default auto-commit mode

setAutoCommit

public void setAutoCommit(boolean autoCommit)
                   throws java.sql.SQLException
Sets this connection's auto-commit mode to the given state. If a connection is in auto-commit mode, then all its SQL statements will be executed and committed as individual transactions. Otherwise, its SQL statements are grouped into transactions that are terminated by a call to either the method commit or the method rollback.

If this method is not used, then default database commit mode is used, and this class doesn't change anything.

NOTE: if this method is called before very first setSql() or setStaticSql(), than it will just 'remember' the commit state that will be actually set in those commands, after connection is retrieved from database. This is prefered method.

If it is called after, then this method will directly change connection commit mode, with all consequences.

Parameters:
autoCommit - true to enable auto-commit mode; false to disable it
Throws:
java.sql.SQLException

getAutoCommit

public java.lang.Boolean getAutoCommit()
Retrieves the current auto-commit mode for this object. If null is returned then default database mode is used.

Returns:
the current auto-commit mode

getStoredAutoCommit

public java.lang.Boolean getStoredAutoCommit()
Returns the stored auto-commit mode for the connection. This value should match database initial commit mode. If null is returned, then connection is not yet obtained. Therefore, this method has to be called after setSql methods.

Returns:
default commit mode

commit

public void commit()
            throws java.sql.SQLException
Makes all changes made since the previous commit/rollback permanent and releases any database locks currently held by this object. This method should be used only when auto-commit mode has been disabled.

Throws:
java.sql.SQLException

rollback

public void rollback()
Undoes all changes made in the current transaction and releases any database locks currently held by this object. This method should be used only when auto-commit mode has been disabled.


setDebugMode

public static void setDebugMode(boolean b)
Sets debug mode.

Parameters:
b -

getDebugMode

public static boolean getDebugMode()
Returns current debug mode.

Returns:
current debug mode

toString

public java.lang.String toString()
Returns formated pseudo-SQL string. When PreparedStatement is used, all ? are replaced with String representation of assigned values. Just for debugging purposes.

Returns:
formated pseudo-SQL string

setInt

public void setInt(int ndx,
                   int value)
            throws java.sql.SQLException
Sets value of int parameter in sql string.

Parameters:
ndx - 1-based index od parameter
value - int value
Throws:
java.sql.SQLException

setBoolean

public void setBoolean(int ndx,
                       boolean value)
                throws java.sql.SQLException
Sets value of boolean parameter in sql string.

Parameters:
ndx - 1-based index of parameter
value - parameter value
Throws:
java.sql.SQLException

setLong

public void setLong(int ndx,
                    long value)
             throws java.sql.SQLException
Sets value of long parameter in sql string.

Parameters:
ndx - 1-based index of parameter
value - parameter value
Throws:
java.sql.SQLException

setByte

public void setByte(int ndx,
                    byte value)
             throws java.sql.SQLException
Sets value of byte parameter in sql string.

Parameters:
ndx - 1-based index of parameter
value - parameter value
Throws:
java.sql.SQLException

setDouble

public void setDouble(int ndx,
                      double value)
               throws java.sql.SQLException
Sets value of double parameter in sql string.

Parameters:
ndx - 1-based index of parameter
value - parameter value
Throws:
java.sql.SQLException

setFloat

public void setFloat(int ndx,
                     float value)
              throws java.sql.SQLException
Sets value of float parameter in sql string.

Parameters:
ndx - 1-based index of parameter
value - parameter value
Throws:
java.sql.SQLException

setString

public void setString(int ndx,
                      java.lang.String value)
               throws java.sql.SQLException
Sets value of String parameter in sql string.

Parameters:
ndx - 1-based index of parameter
value - parameter value
Throws:
java.sql.SQLException

setDate

public void setDate(int ndx,
                    java.sql.Date value)
             throws java.sql.SQLException
Sets value of java.sql.Date parameter in sql string.

Parameters:
ndx - 1-based index of parameter
value - parameter value
Throws:
java.sql.SQLException

setTimestamp

public void setTimestamp(int ndx,
                         java.sql.Timestamp value)
                  throws java.sql.SQLException
Sets value of java.sql.Timestamp parameter in sql string.

Parameters:
ndx - 1-based index of parameter
value - parameter value
Throws:
java.sql.SQLException

setArray

public void setArray(int ndx,
                     java.sql.Array value)
              throws java.sql.SQLException
Sets value of Array parameter in sql string.

Parameters:
ndx - 1-based index of parameter
value - parameter value
Throws:
java.sql.SQLException

setBigDecimal

public void setBigDecimal(int ndx,
                          java.math.BigDecimal value)
                   throws java.sql.SQLException
Sets value of BigDecimal parameter in sql string.

Parameters:
ndx - 1-based index of parameter
value - parameter value
Throws:
java.sql.SQLException

setBlob

public void setBlob(int ndx,
                    java.sql.Blob value)
             throws java.sql.SQLException
Sets value of Blob parameter in sql string.

Parameters:
ndx - 1-based index of parameter
value - parameter value
Throws:
java.sql.SQLException

setClob

public void setClob(int ndx,
                    java.sql.Clob value)
             throws java.sql.SQLException
Sets value of Clob parameter in sql string.

Parameters:
ndx - 1-based index of parameter
value - parameter value
Throws:
java.sql.SQLException


Jodd v0.24.5 Javadoc