com.buzzsurf.sql
Class BuzzSQL

java.lang.Object
  com.buzzsurf.sql.BuzzSQL

Direct Known Subclasses:

Select, Update

public abstract class BuzzSQL
extends java.lang.Object

Every SQL object in BuzzSQL extends from the base class com.buzzsurf.sql.BuzzSQL, and therefore all operate in a very similar way.

Four different constructors are provided for each object to handle the different cases of how a connection to the database is obtained. See Connecting to the Database for more information on obtaining a connection to the database explicitly or automatically.

• A zero argument constructor is provided for simplified operation with JavBeans, SOAP, and reflection scenarios where having such a constructor is necessary or convenient. At a minimum, you must call setSQL(String) before execution when using this constructor.
• A single argument constructor that accepts your SQL statement and uses the default DataSource.
• A dual argument constructor that accepts your SQL statement and the explicit name of a DataSource to use.
• A dual argument constructor that accepts your SQL statement and a java.sql.Connection object. This constructor provides a great deal of flexibility by allowing the use of "explicit" connections that are supplied by the user rather than being obtained automatically by BuzzSQL. Using a explicit connection also allows BuzzSQL to support database transactions.

All BuzzSQL objects use an internal java.sql.PreparedStatement object that expects argument placeholders in the SQL as question marks (?). The SQL statement can be passed in via constructor or using the setSQL(String) method.

Arguments are set using the setArgs(Object...) or addArgs(Object...) methods, which are Java 5 variable arguments methods. Therefore you can call setArgs(Object...) and pass any type of object or primitive in any combination. The order of your arguments must only match the order of your question marks in the SQL statement. The difference between setArgs and addArgs is that setArgs will first clear an previously set values.

BuzzSQL does not handle quoting or escaping of any arguments; the decision to quote or not to quote is left up the JDBC driver. Therefore it is important to pass your arguments in setArgs(Object...) as the appropriate native Java type.

For example;

• byte, short, int, long if the database type is numeric
• float, double if the database type is numeric with precision
• String, char if the database type is varchar based
• java.util.Date, java.util.Calendar if the database type is a date/time based
• com.buzzsurf.sql.OutParameter, com.buzzsurf.sql.InOutParameter if the database type is a stored procedure OUT or INOUT parameter. See Stored Procedure for more information.

During execution a database connection is obtained (if needed), SQL and arguments are merged, and the PreparedStatement is executed against the database. execute() throws an exception if any of these steps fails for any reason.

Typical post-execution steps are slightly different depending on the object subtype. Select based objects will obtain a ResultSet and ResultSetMetaData, while Update based objects will query and save the updated row count to a local variable.

execute() returns a reference to the current object to support method chaining. See Method Chaining for more information. You can assume execution succeeded if no exception is thrown.

It is import to call close() after you have finished using any BuzzSQL object. close() will release any resources including the database connection if appropriate. It will never throw an exception, so it is always safe to call close().

You may reuse a SQL object after calling close().

Author:

Paul Cowan (www.buzzsurf.com/sql)

See Also:

DataSourceManager, Select, Update, Insert, Delete, StoredProcedure

Field Summary

protected java.util.List<java.lang.Object>	args
protected java.sql.Connection	con
protected static java.lang.ThreadLocal<java.text.SimpleDateFormat>	DATABASE_FORMATTER
protected java.lang.String	dataSourceName
protected java.lang.String	sql
protected java.sql.PreparedStatement	stmt
protected boolean	usingExplicitConnection

Constructor Summary

protected

BuzzSQL()

Method Summary

<E extends BuzzSQL> E	addArgs(java.lang.Object... args) Add objects to the current SQL statement arguments.
<E extends BuzzSQL> E	close() Close and commit the SQL object.
<E extends BuzzSQL> E	close(boolean commit) Close the SQL object with a choice to commit.
abstract <E extends BuzzSQL> E	execute() During execution a database connection is obtained (if needed), SQL and arguments are merged, and the PreparedStatement is executed against the database.
java.util.List<java.lang.Object>	getArgs() Get array of the currently set args.
java.sql.Connection	getConnection() Get the current database connection.
java.lang.String	getDataSourceName() Get the current dataSource name.
static ReleaseInfo	getReleaseInfo() Get information on this BuzzSQL release.
java.lang.String	getSQL() Get SQL statement string.
java.sql.PreparedStatement	getStatement() Get the internal java.sql.PreparedStatement.
protected java.sql.PreparedStatement	merge(java.sql.PreparedStatement stmt)
protected void	prepare()
static java.lang.String	queryToString(java.lang.String sql, java.util.List<java.lang.Object> args) Utility method to create a nice readable representation of the results of merging a statment with ? placeholders with object args.
<E extends BuzzSQL> E	setArgs(java.lang.Object... args) Set the SQL statement arguments.
<E extends BuzzSQL> E	setConnection(java.sql.Connection con) Set the current connection to an explicit connection.
<E extends BuzzSQL> E	setDataSourceName(java.lang.String dataSourceName) Set the dataSource name.
<E extends BuzzSQL> E	setSQL(java.lang.String sql) Set the SQL statement.
boolean	usingExplicitConnection() usingExplicitConnection() will return true if you pass a java.sql.Connection in via the constructor, or call setConnection(Connection con) with a non-null java.sql.Connection object.

Methods inherited from class java.lang.Object

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

Field Detail

DATABASE_FORMATTER

protected static java.lang.ThreadLocal<java.text.SimpleDateFormat> DATABASE_FORMATTER

dataSourceName

protected java.lang.String dataSourceName

sql

protected java.lang.String sql

args

protected java.util.List<java.lang.Object> args

con

protected java.sql.Connection con

stmt

protected java.sql.PreparedStatement stmt

usingExplicitConnection

protected boolean usingExplicitConnection

Constructor Detail

BuzzSQL

protected BuzzSQL()

Method Detail

getDataSourceName

public java.lang.String getDataSourceName()

Get the current dataSource name. The name is used during execute() to obtain a connection from DataSourceManager.

Returns:

current dataSource name

See Also:

DataSourceManager

setDataSourceName

public <E extends BuzzSQL> E setDataSourceName(java.lang.String dataSourceName)

Set the dataSource name. Used during execute() to obtain a connection from DataSourceManager. dataSourceName will usually be passed via constructor, but access is provided here to support beans, soap, etc. Will have no effect if the object is currently executing or using an explicit connection.

Parameters:

dataSourceName - dataSource name

Returns:

Reference to this object which can be used for method call chaining

See Also:

usingExplicitConnection(), DataSourceManager

getConnection

public java.sql.Connection getConnection()

Get the current database connection. You normally do not need to access the internal connection, especially if you are not using an external connection. However access is provided here if needed.

Returns:

current database connection if statement is executing, or the external connection, or null otherwise

setConnection

public <E extends BuzzSQL> E setConnection(java.sql.Connection con)

Set the current connection to an explicit connection. If con is non-null, this will automatically set usingExplicitConnection true. When using an explicit connection you are responsible for opening and closing the connection. setConnection has no effect if the object is currently executing.

Parameters:

con - The external connection or null to make the connection null and not use an external connection

Returns:

Reference to this object which can be used for method call chaining

See Also:

usingExplicitConnection()

getSQL

public java.lang.String getSQL()

Get SQL statement string. Statements should have ? as placeholders for arguments.

Returns:

SQL statement string

setSQL

public <E extends BuzzSQL> E setSQL(java.lang.String sql)

Set the SQL statement. Statements should have ? as placeholders for args. The SQL statement is usually passed via constructor, but access is provided here to support beans, soap, etc. setSQL has no effect if the object is currently executing.

Parameters:

sql - The SQL statement

Returns:

Reference to this object which can be used for method call chaining

getArgs

public java.util.List<java.lang.Object> getArgs()

Get array of the currently set args. Args will be merged with SQL statement ? placeholders during execution.

Returns:

array of object args or null if there are no args to the SQL statment.

setArgs

public <E extends BuzzSQL> E setArgs(java.lang.Object... args)

Set the SQL statement arguments. Objects will be merged with SQL statement ? placeholders during execution. setArgs has no effect if the object is currently executing.

Parameters:

args - Object args or null if there are no args to the SQL statement.

Returns:

Reference to this object which can be used for method call chaining

addArgs

public <E extends BuzzSQL> E addArgs(java.lang.Object... args)

Add objects to the current SQL statement arguments. Objects will be merged with SQL statement ? placeholders during execution. setArgs has no effect if the object is currently executing.

Parameters:

args - Object args or null if there are no args to the SQL statement.

Returns:

Reference to this object which can be used for method call chaining

usingExplicitConnection

public boolean usingExplicitConnection()

usingExplicitConnection() will return true if you pass a java.sql.Connection in via the constructor, or call setConnection(Connection con) with a non-null java.sql.Connection object. You can reset the BuzzSQL object's state to use an automatic connections by passing null to setConnection(Connection con).

The explicit java.sql.Connection can come from any source such as an external connection pool, a JNDI lookup, or BuzzSQL's DataSource Manager. It is common for an application to use BuzzSQL's automatic connection handling for 99% of the database access, but need to wrap a few specific calls in a transaction. In this case you can call DataSourceManager.getConnection() to obtain an explicit connection for the calls that need to be executed in a transaction, and allow BuzzSQL to use automatic connection handling for the rest.

Returns:

true if the SQL object is using an explicit connection

getStatement

public java.sql.PreparedStatement getStatement()

Get the internal java.sql.PreparedStatement.

Returns:

the PreparedStatement or null if the object is not executing.

execute

public abstract <E extends BuzzSQL> E execute()
                                   throws java.sql.SQLException

During execution a database connection is obtained (if needed), SQL and arguments are merged, and the PreparedStatement is executed against the database. execute() throws an exception if any of these steps fails for any reason.

Typical post-execution steps are slightly different depending on the object subtype. Select based objects will obtain a ResultSet and ResultSetMetaData, while Update based objects will query and save the updated row count to a local variable.

execute() returns a reference to the current object to support method chaining. See Method Chaining for more information. You can assume execution succeeded if no exception is thrown.

Throws:

java.sql.SQLException - if any of the executing JDBC operations failed

close

public <E extends BuzzSQL> E close()

Close and commit the SQL object.

It is import to call close() after you have finished using any BuzzSQL object. close() will release any resources including the database connection if appropriate. It will never throw an exception, so it is always safe to call close().

You may reuse a SQL object after calling close().

The best practice to insure all BuzzSQL objects are closed is to put your call to close() in a finally block.

Returns:

Reference to this object which can be used for method call chaining

See Also:

usingExplicitConnection()

close

public <E extends BuzzSQL> E close(boolean commit)

Close the SQL object with a choice to commit.

It is import to call close() after you have finished using any BuzzSQL object. close() will release any resources including the database connection if appropriate. It will never throw an exception, so it is always safe to call close().

You may reuse a SQL object after calling close().

The best practice to insure all BuzzSQL objects are closed is to put your call to close() in a finally block.

Parameters:

commit - If true BuzzSQL will commit the transaction before closing the connection.

Returns:

Reference to this object which can be used for method call chaining

See Also:

usingExplicitConnection()

merge

protected java.sql.PreparedStatement merge(java.sql.PreparedStatement stmt)
                                    throws java.sql.SQLException

Throws:

java.sql.SQLException

prepare

protected void prepare()
                throws java.sql.SQLException

Throws:

java.sql.SQLException

getReleaseInfo

public static ReleaseInfo getReleaseInfo()

Get information on this BuzzSQL release.

Returns:

ReleaseInfo object with product name, version, build, etc

queryToString

public static java.lang.String queryToString(java.lang.String sql,
                                             java.util.List<java.lang.Object> args)

Utility method to create a nice readable representation of the results of merging a statment with ? placeholders with object args.

Parameters:

sql - Statement

args - Args

Returns:

Nice string representation of merged SQL.