nu.staldal.lsp.framework
Class DBConnection

java.lang.Object
  nu.staldal.lsp.framework.DBConnection

public class DBConnection
extends java.lang.Object

Wrapper around a JDBC Connection with convenience methods.

Field Summary

protected java.text.DateFormat	dateFormatter Formatter for the SQL DATE type.
protected java.sql.Connection	dbConn The wrapped Connection.
static java.text.DateFormat	DEFAULT_DATE_FORMAT Default formatter for SQL DATE type.
static java.text.DateFormat	DEFAULT_TIME_FORMAT Default formatter for the SQL TIME type.
static java.text.DateFormat	DEFAULT_TIMESTAMP_FORMAT Default formatter for the SQL TIMESTAMP type.
protected java.lang.Object	nullReplacement The object to use when copying a ResultSet (row) and a JDBC NULL occurs.
protected java.text.DateFormat	timeFormatter Formatter for the SQL TIME type.
protected java.text.DateFormat	timestampFormatter Formatter for the SQL TIMESTAMP type.

Constructor Summary

DBConnection(java.sql.Connection dbConn)
Create a DBConnection.

Method Summary

void	close() Close connection.
void	closeResultSet(java.sql.ResultSet rs) Close a ResultSet and its Statement.
void	commit() Commit transaction.
java.util.List<java.util.Map<java.lang.String,java.lang.Object>>	copyResultSet(java.sql.ResultSet rs) Copy a ResultSet into a List of Maps.
java.util.Map<java.lang.String,java.lang.Object>	copyResultSetRow(java.sql.ResultSet rs) Copy a ResultSet row into a Map.
int	deleteRow(java.lang.String table, java.lang.String whereClause, java.lang.Object... params) Delete rows from a table.
java.sql.ResultSet	executeQuery(java.lang.String query, java.lang.Object... params) Execute a parameterized query.
java.util.List<java.util.Map<java.lang.String,java.lang.Object>>	executeQueryAndCopy(java.lang.String query, java.lang.Object... params) Execute a parameterized query, copy and close the ResultSet.
int	executeUpdate(java.lang.String query, java.lang.Object... params) Execute a parameterized update query.
boolean	exists(java.lang.String query, java.lang.Object... params) Execute a parameterized query which and check whether it returns any rows.
java.sql.Connection	getConnection() Return the wrapped Connection.
java.text.DateFormat	getDateFormatter() Get formatter for the SQL DATE type.
java.lang.Object	getNullReplacement() Get the object to use when copying a ResultSet (row) and a JDBC NULL occurs.
java.text.DateFormat	getTimeFormatter() Get formatter for the SQL TIME type.
java.text.DateFormat	getTimestampFormatter() Get formatter for the SQL TIMESTAMP type.
int	insertRow(java.lang.String table, java.lang.String colList, java.lang.Object... params) Insert a row into a table
boolean	lookupBoolean(java.lang.String query, java.lang.Object... params) Execute a parameterized query which and return the first object in the first row of the ResultSet as a boolean.
int	lookupInt(java.lang.String query, java.lang.Object... params) Execute a parameterized query which and return the first object in the first row of the ResultSet as an integer.
java.lang.Object	lookupObject(java.lang.String query, java.lang.Object... params) Execute a parameterized query which and return the first object in the first row of the ResultSet.
java.util.Map<java.lang.String,java.lang.Object>	lookupRow(java.lang.String query, java.lang.Object... params) Execute a parameterized query which and return a copy of the first row of the ResultSet.
java.lang.String	lookupString(java.lang.String query, java.lang.Object... params) Execute a parameterized query which and return the first object in the first row of the ResultSet as a string.
void	rollback() Rollback transaction.
void	setDateFormatter(java.text.DateFormat df) Set formatter for the SQL DATE type.
void	setNullReplacement(java.lang.Object o) Set the object to use when copying a ResultSet (row) and a JDBC NULL occurs.
void	setTimeFormatter(java.text.DateFormat df) Set formatter for the SQL TIME type.
void	setTimestampFormatter(java.text.DateFormat df) Set formatter for the SQL TIMESTAMP type.

Methods inherited from class java.lang.Object

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

Field Detail

DEFAULT_DATE_FORMAT

public static final java.text.DateFormat DEFAULT_DATE_FORMAT

Default formatter for SQL DATE type.

DEFAULT_TIME_FORMAT

public static final java.text.DateFormat DEFAULT_TIME_FORMAT

Default formatter for the SQL TIME type.

DEFAULT_TIMESTAMP_FORMAT

public static final java.text.DateFormat DEFAULT_TIMESTAMP_FORMAT

Default formatter for the SQL TIMESTAMP type.

nullReplacement

protected java.lang.Object nullReplacement

The object to use when copying a ResultSet (row) and a JDBC NULL occurs.

Default is the empty string.

dateFormatter

protected java.text.DateFormat dateFormatter

Formatter for the SQL DATE type.

Default is SimpleDateFormat("yyyy-MM-dd").

timestampFormatter

protected java.text.DateFormat timestampFormatter

Formatter for the SQL TIMESTAMP type.

Default is SimpleDateFormat("yyyy-MM-dd HH:mm:ss").

timeFormatter

protected java.text.DateFormat timeFormatter

Formatter for the SQL TIME type.

Default is SimpleDateFormat("HH:mm:ss").

dbConn

protected final java.sql.Connection dbConn

The wrapped Connection.

Constructor Detail

DBConnection

public DBConnection(java.sql.Connection dbConn)

Create a DBConnection.

Parameters:

dbConn - the JDBC Connection to wrap.

Method Detail

setNullReplacement

public void setNullReplacement(java.lang.Object o)

Set the object to use when copying a ResultSet (row) and a JDBC NULL occurs.

Default is the empty string.

Set to null to not do any replacement.

Parameters:

o - the replacement object

getNullReplacement

public java.lang.Object getNullReplacement()

Get the object to use when copying a ResultSet (row) and a JDBC NULL occurs.

Default is the empty string.

Returns:

the object to use when copying a ResultSet (row) and a JDBC NULL occurs

setDateFormatter

public void setDateFormatter(java.text.DateFormat df)

Set formatter for the SQL DATE type.

Default is SimpleDateFormat("yyyy-MM-dd").

Set to null to not do any formatting.

Parameters:

df - the DateFormat

getDateFormatter

public java.text.DateFormat getDateFormatter()

Get formatter for the SQL DATE type.

Returns:

the DateFormat

setTimestampFormatter

public void setTimestampFormatter(java.text.DateFormat df)

Set formatter for the SQL TIMESTAMP type.

Default is SimpleDateFormat("yyyy-MM-dd HH:mm:ss").

Set to null to not do any formatting.

Parameters:

df - the DateFormat

getTimestampFormatter

public java.text.DateFormat getTimestampFormatter()

Get formatter for the SQL TIMESTAMP type.

Returns:

the DateFormat

setTimeFormatter

public void setTimeFormatter(java.text.DateFormat df)

Set formatter for the SQL TIME type.

Default is SimpleDateFormat("HH:mm:ss").

Set to null to not do any formatting.

Parameters:

df - the DateFormat

getTimeFormatter

public java.text.DateFormat getTimeFormatter()

Get formatter for the SQL TIME type.

Returns:

the DateFormat

getConnection

public java.sql.Connection getConnection()

Return the wrapped Connection.

Returns:

the wrapped Connection

commit

public void commit()
            throws java.sql.SQLException

Commit transaction.

Throws:

java.sql.SQLException - if a database error occurs

See Also:

Connection.commit()

rollback

public void rollback()
              throws java.sql.SQLException

Rollback transaction.

Throws:

java.sql.SQLException - if a database error occurs

See Also:

Connection.rollback()

close

public void close()
           throws java.sql.SQLException

Close connection.

Throws:

java.sql.SQLException - if a database error occurs

See Also:

Connection.close()

executeQuery

public java.sql.ResultSet executeQuery(java.lang.String query,
                                       java.lang.Object... params)
                                throws java.sql.SQLException

Execute a parameterized query.

You should pass the returned ResultSet to closeResultSet(java.sql.ResultSet) when done (unless you pass it to copyResultSet(java.sql.ResultSet)).

Parameters:

query - the SQL query with '?' parameters

params - parameter values

Returns:

the ResultSet

Throws:

java.sql.SQLException - if a database error occurs

See Also:

PreparedStatement.executeQuery(), closeResultSet(java.sql.ResultSet)

executeQueryAndCopy

public java.util.List<java.util.Map<java.lang.String,java.lang.Object>> executeQueryAndCopy(java.lang.String query,
                                                                                            java.lang.Object... params)
                                                                                     throws java.sql.SQLException

Execute a parameterized query, copy and close the ResultSet.

Parameters:

query - the SQL query with '?' parameters

params - parameter values

Returns:

copy of the ResultSet

Throws:

java.sql.SQLException - if a database error occurs

See Also:

PreparedStatement.executeQuery(), setNullReplacement(java.lang.Object)

closeResultSet

public void closeResultSet(java.sql.ResultSet rs)
                    throws java.sql.SQLException

Close a ResultSet and its Statement. Does nothing if rs is null.

Parameters:

rs - the ResultSet to close

Throws:

java.sql.SQLException - if a database error occurs

See Also:

executeQuery(java.lang.String, java.lang.Object...)

copyResultSet

public java.util.List<java.util.Map<java.lang.String,java.lang.Object>> copyResultSet(java.sql.ResultSet rs)
                                                                               throws java.sql.SQLException

Copy a ResultSet into a List of Maps. Useful for passing a ResultSet to an LSP page.

The ResultSet is closed after copying.

Parameters:

rs - the ResultSet to copy

Returns:

copy of the ResultSet

Throws:

java.sql.SQLException - if a database error occurs

See Also:

setNullReplacement(java.lang.Object)

copyResultSetRow

public java.util.Map<java.lang.String,java.lang.Object> copyResultSetRow(java.sql.ResultSet rs)
                                                                  throws java.sql.SQLException

Copy a ResultSet row into a Map. The current row of the ResultSet is copied, and the ResultSet is not advanced.

Parameters:

rs - the ResultSet to copy a row from

Returns:

copy of the ResultSet row

Throws:

java.sql.SQLException - if a database error occurs

See Also:

setNullReplacement(java.lang.Object)

lookupRow

public java.util.Map<java.lang.String,java.lang.Object> lookupRow(java.lang.String query,
                                                                  java.lang.Object... params)
                                                           throws java.sql.SQLException,
                                                                  java.util.NoSuchElementException

Execute a parameterized query which and return a copy of the first row of the ResultSet. Any subsequent rows are ignored.

Will do the same translation of dates and null as copyResultSetRow(ResultSet).

Parameters:

query - the SQL query with '?' parameters

params - parameter values

Returns:

a copy of the first row of the ResultSet

Throws:

java.sql.SQLException - if a database error occurs

java.util.NoSuchElementException - if the query rerurns no rows

See Also:

PreparedStatement.executeQuery(), copyResultSetRow(ResultSet)

lookupObject

public java.lang.Object lookupObject(java.lang.String query,
                                     java.lang.Object... params)
                              throws java.sql.SQLException,
                                     java.util.NoSuchElementException

Execute a parameterized query which and return the first object in the first row of the ResultSet. Any subsequent rows are ignored.

Will not do any translation of dates and null.

Parameters:

query - the SQL query with '?' parameters

params - parameter values

Returns:

the first object in the first row of the ResultSet

Throws:

java.sql.SQLException - if a database error occurs

java.util.NoSuchElementException - if the query rerurns no rows

See Also:

PreparedStatement.executeQuery()

lookupString

public java.lang.String lookupString(java.lang.String query,
                                     java.lang.Object... params)
                              throws java.sql.SQLException,
                                     java.util.NoSuchElementException

Execute a parameterized query which and return the first object in the first row of the ResultSet as a string. Any subsequent rows are ignored.

Parameters:

query - the SQL query with '?' parameters

params - parameter values

Returns:

the first object in the first row of the ResultSet as a String

Throws:

java.sql.SQLException - if a database error occurs

java.util.NoSuchElementException - if the query rerurns no rows

See Also:

PreparedStatement.executeQuery()

lookupInt

public int lookupInt(java.lang.String query,
                     java.lang.Object... params)
              throws java.sql.SQLException,
                     java.util.NoSuchElementException

Execute a parameterized query which and return the first object in the first row of the ResultSet as an integer. Any subsequent rows are ignored.

Parameters:

query - the SQL query with '?' parameters

params - parameter values

Returns:

the first object in the first row of the ResultSet as an integer.

Throws:

java.sql.SQLException - if a database error occurs

java.util.NoSuchElementException - if the query rerurns no rows

See Also:

PreparedStatement.executeQuery()

lookupBoolean

public boolean lookupBoolean(java.lang.String query,
                             java.lang.Object... params)
                      throws java.sql.SQLException,
                             java.util.NoSuchElementException

Execute a parameterized query which and return the first object in the first row of the ResultSet as a boolean. Any subsequent rows are ignored.

Parameters:

query - the SQL query with '?' parameters

params - parameter values

Returns:

the first object in the first row of the ResultSet as a boolean (an integer equals to 1)

Throws:

java.sql.SQLException - if a database error occurs

java.util.NoSuchElementException - if the query rerurns no rows

See Also:

PreparedStatement.executeQuery()

exists

public boolean exists(java.lang.String query,
                      java.lang.Object... params)
               throws java.sql.SQLException

Execute a parameterized query which and check whether it returns any rows.

Parameters:

query - the SQL query with '?' parameters

params - parameter values

Returns:

true if the query returns any rows, false otherwise

Throws:

java.sql.SQLException - if a database error occurs

See Also:

PreparedStatement.executeQuery()

executeUpdate

public int executeUpdate(java.lang.String query,
                         java.lang.Object... params)
                  throws java.sql.SQLException

Execute a parameterized update query.

Parameters:

query - the SQL query with '?' parameters

params - parameters

Returns:

the row count

Throws:

java.sql.SQLException - if a database error occurs

See Also:

PreparedStatement.executeUpdate()

insertRow

public int insertRow(java.lang.String table,
                     java.lang.String colList,
                     java.lang.Object... params)
              throws java.sql.SQLException

Insert a row into a table

Parameters:

table - name of the table

colList - list of column names, separated with ','

params - values to insert into the columns

Returns:

number of rows inserted

Throws:

java.sql.SQLException - if a database error occurs

See Also:

executeUpdate(java.lang.String, java.lang.Object...)

deleteRow

public int deleteRow(java.lang.String table,
                     java.lang.String whereClause,
                     java.lang.Object... params)
              throws java.sql.SQLException

Delete rows from a table.

Parameters:

table - name of the table

whereClause - WHERE clause (without "WHERE") for the DELETE query, with with '?' parameters

params - parameter values to the WHERE clause

Returns:

number of rows deleted, or -1 if FOREIGN KEY contraint violation (SQLState "23") occurs

Throws:

java.sql.SQLException - if a database error occurs

See Also:

executeUpdate(java.lang.String, java.lang.Object...)