com.buzzsurf.sql
Class Select

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

Direct Known Subclasses:

StoredProcedure

public class Select
extends BuzzSQL

A Select object queries a database and can be iterated through to read results. It is a combination of the underlying JDBC classes;

• java.sql.Connection
• java.sql.PreparedStatement
• java.sql.ResultSet
• java.sql.ResultSetMetaData

After execution, you may iterate through the Select object using next(), and retrieve your results in any format using one of the variety of get methods. You must call next() at least once before any get call to set the index in the result set to the first result.

As an example, the following code fragment creates a Select object using the automatic default connection to the database. The SQL statement is passed via constructor, and the arguments are passed using the setArgs(Object...) method. execute() is called to execute the database call, and then results are iterated through using next(). The object is then closed to cleanup any resources and return the connection to the pool.

 Select select = new Select();
 select.setSQL("select col_pk, col_str, col_int from example.table_example1");
 select.execute();
 while (select.next())
 System.out.println(select.getLine());
 select.close();
 

Note: This code does not handle exceptions. You normally should wrap your calls in try/catch/finally blocks to handle any problems. It is advisable to put your close() call in a finally block to insure the connection is always released.

Author:

Paul Cowan (www.buzzsurf.com/sql)

See Also:

BuzzSQL

Field Summary

protected java.sql.ResultSet	rs
protected java.sql.ResultSetMetaData	rsmd

Fields inherited from class com.buzzsurf.sql.BuzzSQL

args, con, DATABASE_FORMATTER, dataSourceName, sql, stmt, usingExplicitConnection

Constructor Summary

Select()
A zero argument constructor is provided for simplified operation with JavBeans, SOAP, and reflection scenarios where having such a constructor is necessary or convenient.

Select(java.lang.String sql)
A single argument constructor that accepts your SQL statement and uses the default DataSource.

Select(java.lang.String sql, java.sql.Connection con)
A dual argument constructor that accepts your SQL statement and a java.sql.Connection object.

Select(java.lang.String sql, java.lang.String dataSourceName)
A dual argument constructor that accepts your SQL statement and the explicit name of a DataSource to use.

Method Summary

Select	close() Close and commit the SQL object.
Select	execute() During execution a database connection is obtained (if needed), SQL and arguments are merged, and the PreparedStatement is executed against the database.
boolean	getBoolean(int columnIndex) Retrieves the value of the designated column in the current row of this ResultSet object as a boolean in the Java programming language.
boolean	getBoolean(java.lang.String columnName) Retrieves the value of the designated column in the current row of this ResultSet object as a boolean in the Java programming language.
double	getByte(java.lang.String columnName) Retrieves the value of the designated column in the current row of this ResultSet object as a byte in the Java programming language.
byte[]	getBytes(int columnIndex) Retrieves the value of the designated column in the current row of this ResultSet object as a byte array in the Java programming language.
byte[]	getBytes(java.lang.String columnName) Retrieves the value of the designated column in the current row of this ResultSet object as a byte array in the Java programming language.
java.util.Calendar	getCalendar(int columnIndex) Retrieves the value of the designated column in the current row of this ResultSet object as a java.util.Calendar object in the Java programming language.
java.util.Calendar	getCalendar(java.lang.String columnName) Retrieves the value of the designated column in the current row of this ResultSet object as a java.util.Calendar object in the Java programming language.
int	getColumnCount() Returns the number of columns in this ResultSet object.
java.lang.String	getColumnName(int column) Get the designated column's name.
java.util.Date	getDate(int columnIndex) Retrieves the value of the designated column in the current row of this ResultSet object as a java.util.Date object in the Java programming language.
java.util.Date	getDate(java.lang.String columnName) Retrieves the value of the designated column in the current row of this ResultSet object as a java.util.Date object in the Java programming language.
double	getDouble(int columnIndex) Retrieves the value of the designated column in the current row of this ResultSet object as a double in the Java programming language.
double	getDouble(java.lang.String columnName) Retrieves the value of the designated column in the current row of this ResultSet object as a double in the Java programming language.
double	getFloat(int columnIndex) Retrieves the value of the designated column in the current row of this ResultSet object as a float in the Java programming language.
double	getFloat(java.lang.String columnName) Retrieves the value of the designated column in the current row of this ResultSet object as a float in the Java programming language.
int	getInt(int columnIndex) Retrieves the value of the designated column in the current row of this ResultSet object as an int in the Java programming language.
int	getInt(java.lang.String columnName) Retrieves the value of the designated column in the current row of this ResultSet object as an int in the Java programming language.
java.lang.String	getLine()
java.lang.String	getLine(java.lang.String delim)
java.lang.String	getLineCSV()
long	getLong(int columnIndex) Retrieves the value of the designated column in the current row of this ResultSet object as a long in the Java programming language.
long	getLong(java.lang.String columnName) Retrieves the value of the designated column in the current row of this ResultSet object as a long in the Java programming language.
java.sql.ResultSetMetaData	getMetaData() Get the internal java.sql.ResultSetMetaData.
java.sql.ResultSet	getResultSet() Get the internal java.sql.ResultSet.
short	getShort(int columnIndex) Retrieves the value of the designated column in the current row of this ResultSet object as an short in the Java programming language.
double	getShort(java.lang.String columnName) Retrieves the value of the designated column in the current row of this ResultSet object as a short in the Java programming language.
java.lang.String	getString(int columnIndex) Retrieves the value of the designated column in the current row of this ResultSet object as a String in the Java programming language.
java.lang.String	getString(java.lang.String columnName) Retrieves the value of the designated column in the current row of this ResultSet object as a String in the Java programming language.
boolean	next() Moves the ResultSet cursor down one row from its current position.
void	setCalendar(int columnIndex, java.util.Calendar c) Set the current time of the provided java.util.Calendar object to the value of the designated column in the current row of this ResultSet.
void	setCalendar(java.lang.String columnName, java.util.Calendar c) Set the current time of the provided java.util.Calendar object to the value of the designated column in the current row of this ResultSet.

Methods inherited from class com.buzzsurf.sql.BuzzSQL

addArgs, close, getArgs, getConnection, getDataSourceName, getReleaseInfo, getSQL, getStatement, merge, prepare, queryToString, setArgs, setConnection, setDataSourceName, setSQL, usingExplicitConnection

Methods inherited from class java.lang.Object

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

Field Detail

rs

protected java.sql.ResultSet rs

rsmd

protected java.sql.ResultSetMetaData rsmd

Constructor Detail

Select

public Select()

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.

Select

public Select(java.lang.String sql)

A single argument constructor that accepts your SQL statement and uses the default DataSource.

Parameters:

sql - The SQL statement

Select

public Select(java.lang.String sql,
              java.lang.String dataSourceName)

A dual argument constructor that accepts your SQL statement and the explicit name of a DataSource to use.

Parameters:

sql - The SQL statement

dataSourceName - The explicit name of the dataSource to obtain a connecton from in DataSourceManager.

Select

public Select(java.lang.String sql,
              java.sql.Connection con)

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.

Parameters:

sql - The SQL statement

con - The explicit database Connection

Method Detail

getResultSet

public java.sql.ResultSet getResultSet()

Get the internal java.sql.ResultSet. You normally do not need to access the internal ResultSet directly, however access is provided here if needed.

Returns:

The java.sql.ResultSet or null if the object is not executing.

getMetaData

public java.sql.ResultSetMetaData getMetaData()

Get the internal java.sql.ResultSetMetaData. You normally do not need to access the internal ResultSetMetaData directly, however access is provided here if needed.

Returns:

The java.sql.ResultSetMetaData or null if the object is not executing.

execute

public Select 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.

Specified by:

execute in class BuzzSQL

Throws:

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

close

public Select 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.

Overrides:

close in class BuzzSQL

Returns:

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

See Also:

BuzzSQL.usingExplicitConnection()

next

public boolean next()
             throws java.sql.SQLException

Moves the ResultSet cursor down one row from its current position. A ResultSet cursor is initially positioned before the first row; the first call to the method next makes the first row the current row; the second call makes the second row the current row, and so on.

If an input stream is open for the current row, a call to the method next will implicitly close it. A ResultSet object's warning chain is cleared when a new row is read.

Returns:

true if the new current row is valid; false if there are no more rows

Throws:

java.sql.SQLException - if a database access error occurs

getLine

public java.lang.String getLine()
                         throws java.sql.SQLException

Returns:

All columns in the current ResultSet concatenated togeather and delimited with a COMMA.

Throws:

java.sql.SQLException

getLine

public java.lang.String getLine(java.lang.String delim)
                         throws java.sql.SQLException

Parameters:

delim - The delimiter

Returns:

All columns in the current ResultSet concatenated togeather and delimited with the delimiter.

Throws:

java.sql.SQLException

getLineCSV

public java.lang.String getLineCSV()
                            throws java.sql.SQLException

Returns:

All columns in the current ResultSet concatenated togeather, enclosed in \"quotes\" and delimited with a comma.

Throws:

java.sql.SQLException

getColumnCount

public int getColumnCount()
                   throws java.sql.SQLException

Returns the number of columns in this ResultSet object.

Returns:

the number of columns or -1

Throws:

java.sql.SQLException - if a database access error occurs

getColumnName

public java.lang.String getColumnName(int column)
                               throws java.sql.SQLException

Get the designated column's name.

Parameters:

column - the first column is 1, the second is 2, ...

Returns:

column name

Throws:

java.sql.SQLException - if a database access error occurs

getString

public java.lang.String getString(int columnIndex)
                           throws java.sql.SQLException

Retrieves the value of the designated column in the current row of this ResultSet object as a String in the Java programming language.

Parameters:

columnIndex - the first column is 1, the second is 2, ...

Returns:

the column value; if the value is SQL NULL, the value returned is null

Throws:

java.sql.SQLException - if a database access error occurs

getBoolean

public boolean getBoolean(int columnIndex)
                   throws java.sql.SQLException

Retrieves the value of the designated column in the current row of this ResultSet object as a boolean in the Java programming language.

Parameters:

columnIndex - the first column is 1, the second is 2, ...

Returns:

the column value; if the value is SQL NULL, the value returned is false

Throws:

java.sql.SQLException - if a database access error occurs

getShort

public short getShort(int columnIndex)
               throws java.sql.SQLException

Retrieves the value of the designated column in the current row of this ResultSet object as an short in the Java programming language.

Parameters:

columnIndex - the first column is 1, the second is 2, ...

Returns:

the column value; if the value is SQL NULL, the value returned is 0

Throws:

java.sql.SQLException - if a database access error occurs

getInt

public int getInt(int columnIndex)
           throws java.sql.SQLException

Retrieves the value of the designated column in the current row of this ResultSet object as an int in the Java programming language.

Parameters:

columnIndex - the first column is 1, the second is 2, ...

Returns:

the column value; if the value is SQL NULL, the value returned is 0

Throws:

java.sql.SQLException - if a database access error occurs

getLong

public long getLong(int columnIndex)
             throws java.sql.SQLException

Retrieves the value of the designated column in the current row of this ResultSet object as a long in the Java programming language.

Parameters:

columnIndex - the first column is 1, the second is 2, ...

Returns:

the column value; if the value is SQL NULL, the value returned is 0

Throws:

java.sql.SQLException - if a database access error occurs

getFloat

public double getFloat(int columnIndex)
                throws java.sql.SQLException

Retrieves the value of the designated column in the current row of this ResultSet object as a float in the Java programming language.

Parameters:

columnIndex - the first column is 1, the second is 2, ...

Returns:

the column value; if the value is SQL NULL, the value returned is 0

Throws:

java.sql.SQLException - if a database access error occurs

getDouble

public double getDouble(int columnIndex)
                 throws java.sql.SQLException

Retrieves the value of the designated column in the current row of this ResultSet object as a double in the Java programming language.

Parameters:

columnIndex - the first column is 1, the second is 2, ...

Returns:

the column value; if the value is SQL NULL, the value returned is 0

Throws:

java.sql.SQLException - if a database access error occurs

getDate

public java.util.Date getDate(int columnIndex)
                       throws java.sql.SQLException

Retrieves the value of the designated column in the current row of this ResultSet object as a java.util.Date object in the Java programming language.

Parameters:

columnIndex - the first column is 1, the second is 2, ...

Returns:

the column value; if the value is SQL NULL, the value returned is null

Throws:

java.sql.SQLException - if a database access error occurs

getCalendar

public java.util.Calendar getCalendar(int columnIndex)
                               throws java.sql.SQLException

Retrieves the value of the designated column in the current row of this ResultSet object as a java.util.Calendar object in the Java programming language.

Parameters:

columnIndex - the first column is 1, the second is 2, ...

Returns:

the column value; if the value is SQL NULL, the value returned is null

Throws:

java.sql.SQLException - if a database access error occurs

setCalendar

public void setCalendar(int columnIndex,
                        java.util.Calendar c)
                 throws java.sql.SQLException

Set the current time of the provided java.util.Calendar object to the value of the designated column in the current row of this ResultSet.

Parameters:

columnIndex - the first column is 1, the second is 2, ...

c - the Calendar to set

Throws:

java.sql.SQLException - if a database access error occurs

getBytes

public byte[] getBytes(int columnIndex)
                throws java.sql.SQLException

Retrieves the value of the designated column in the current row of this ResultSet object as a byte array in the Java programming language. The bytes represent the raw values returned by the driver.

Parameters:

columnIndex - the first column is 1, the second is 2, ...

Returns:

the column value; if the value is SQL NULL, the value returned is null

Throws:

java.sql.SQLException - if a database access error occurs

getString

public java.lang.String getString(java.lang.String columnName)
                           throws java.sql.SQLException

Retrieves the value of the designated column in the current row of this ResultSet object as a String in the Java programming language.

Parameters:

columnName - the SQL name of the column

Returns:

the column value; if the value is SQL NULL, the value returned is null

Throws:

java.sql.SQLException - if a database access error occurs

getBoolean

public boolean getBoolean(java.lang.String columnName)
                   throws java.sql.SQLException

Retrieves the value of the designated column in the current row of this ResultSet object as a boolean in the Java programming language.

Parameters:

columnName - the SQL name of the column

Returns:

the column value; if the value is SQL NULL, the value returned is false

Throws:

java.sql.SQLException - if a database access error occurs

getByte

public double getByte(java.lang.String columnName)
               throws java.sql.SQLException

Retrieves the value of the designated column in the current row of this ResultSet object as a byte in the Java programming language.

Parameters:

columnName - the SQL name of the column

Returns:

the column value; if the value is SQL NULL, the value returned is 0

Throws:

java.sql.SQLException - if a database access error occurs

getShort

public double getShort(java.lang.String columnName)
                throws java.sql.SQLException

Retrieves the value of the designated column in the current row of this ResultSet object as a short in the Java programming language.

Parameters:

columnName - the SQL name of the column

Returns:

the column value; if the value is SQL NULL, the value returned is 0

Throws:

java.sql.SQLException - if a database access error occurs

getInt

public int getInt(java.lang.String columnName)
           throws java.sql.SQLException

Retrieves the value of the designated column in the current row of this ResultSet object as an int in the Java programming language.

Parameters:

columnName - the SQL name of the column

Returns:

the column value; if the value is SQL NULL, the value returned is 0

Throws:

java.sql.SQLException - if a database access error occurs

getLong

public long getLong(java.lang.String columnName)
             throws java.sql.SQLException

Retrieves the value of the designated column in the current row of this ResultSet object as a long in the Java programming language.

Parameters:

columnName - the SQL name of the column

Returns:

the column value; if the value is SQL NULL, the value returned is 0

Throws:

java.sql.SQLException - if a database access error occurs

getFloat

public double getFloat(java.lang.String columnName)
                throws java.sql.SQLException

Retrieves the value of the designated column in the current row of this ResultSet object as a float in the Java programming language.

Parameters:

columnName - the SQL name of the column

Returns:

the column value; if the value is SQL NULL, the value returned is 0

Throws:

java.sql.SQLException - if a database access error occurs

getDouble

public double getDouble(java.lang.String columnName)
                 throws java.sql.SQLException

Retrieves the value of the designated column in the current row of this ResultSet object as a double in the Java programming language.

Parameters:

columnName - the SQL name of the column

Returns:

the column value; if the value is SQL NULL, the value returned is 0

Throws:

java.sql.SQLException - if a database access error occurs

getDate

public java.util.Date getDate(java.lang.String columnName)
                       throws java.sql.SQLException

Retrieves the value of the designated column in the current row of this ResultSet object as a java.util.Date object in the Java programming language.

Parameters:

columnName - the SQL name of the column

Returns:

the column value; if the value is SQL NULL, the value returned is null

Throws:

java.sql.SQLException - if a database access error occurs

getCalendar

public java.util.Calendar getCalendar(java.lang.String columnName)
                               throws java.sql.SQLException

Retrieves the value of the designated column in the current row of this ResultSet object as a java.util.Calendar object in the Java programming language.

Parameters:

columnName - the SQL name of the column

Returns:

the column value; if the value is SQL NULL, the value returned is null

Throws:

java.sql.SQLException - if a database access error occurs

setCalendar

public void setCalendar(java.lang.String columnName,
                        java.util.Calendar c)
                 throws java.sql.SQLException

Set the current time of the provided java.util.Calendar object to the value of the designated column in the current row of this ResultSet.

Parameters:

columnName - the SQL name of the column

c - the Calendar to set

Throws:

java.sql.SQLException - if a database access error occurs

getBytes

public byte[] getBytes(java.lang.String columnName)
                throws java.sql.SQLException

Retrieves the value of the designated column in the current row of this ResultSet object as a byte array in the Java programming language. The bytes represent the raw values returned by the driver.

Parameters:

columnName - the SQL name of the column

Returns:

the column value; if the value is SQL NULL, the value returned is null

Throws:

java.sql.SQLException - if a database access error occurs