fc.jdbc

Class NamedParamStatement

java.lang.Object

fc.jdbc.NamedParamStatement

public final class NamedParamStatement
extends Object

This class wraps around PreparedStatement and allows the programmer to set parameters by name instead of by question mark index.

Inspired by a similar concept at: javaworld (although this class was rewritten from scratch).

Named parameters are written as @foo (that is, they start with a @ character). Named parameters can only contain alphanumeric, underscore and dashes, any character not in this allowed list automatically ends the named parameter and continues normal SQL. For example:

select * from foo where search = @search-term and radius = '@radius::int'

This contains two named parameters, @search-term and @radius. To use this in code, we say:

Connection con = getConnection(); //some way to get a connection
String query = 
  "select * from foo where search = @search-term and radius = '@radius::int'";

NamedParamStatement ps = NamedParamStatement.get(con, query);
ps.setString("search-term", "hello world");
ps.setInt("radius", 42);

ResultSet rs = ps.executeQuery();

Note: When setting a named paramter, the "@" must be omitted.

The same named parameter can appear multiple times in the query, and is replaced wherever it appears by its value.

The close() method should be called to release resources and help with garbage collection (right around the time close is called on the associated connection, which is after any retrieved data/resultset has been fully read).

Method Summary

Modifier and Type	Method and Description
void	addBatch()
void	clearParameters()
void	close()
boolean	execute()
ResultSet	executeQuery()
int	executeUpdate()
static NamedParamStatement	get(Connection con, String query) Returns a new instance of NamedParamStatement.
ResultSetMetaData	getMetaData()
ParameterMetaData	getParameterMetaData()
static NamedParamStatement	getScrollable(Connection con, String query) Returns a new instance of NamedParamStatement.
void	setArray(String name, Array x)
void	setAsciiStream(String name, InputStream x, int length)
void	setBigDecimal(String name, BigDecimal x)
void	setBinaryStream(String name, InputStream x, int length)
void	setBlob(String name, Blob x)
void	setBoolean(String name, boolean x)
void	setByte(String name, byte x)
void	setBytes(String name, byte[] x)
void	setCharacterStream(String name, Reader reader, int length)
void	setClob(String name, Clob x)
void	setDate(String name, Date x)
void	setDate(String name, Date x, Calendar cal)
void	setDouble(String name, double x)
void	setFloat(String name, float x)
void	setInt(String name, int x)
void	setLong(String name, long x)
void	setNull(String name, int sqlType)
void	setNull(String name, int sqlType, String typeName)
void	setObject(String name, Object x)
void	setObject(String name, Object x, int targetSqlType)
void	setObject(String name, Object x, int targetSqlType, int scale)
void	setRef(String name, Ref x)
void	setShort(String name, short x)
void	setString(String name, String x)
void	setTime(String name, Time x)
void	setTime(String name, Time x, Calendar cal)
void	setTimestamp(String name, Timestamp x)
void	setTimestamp(String name, Timestamp x, Calendar cal)
void	setURL(String name, URL x)
String	toString()

Methods inherited from class java.lang.Object

equals, getClass, hashCode, notify, notifyAll, wait, wait, wait

Method Detail

get

public static NamedParamStatement get(Connection con,
                                      String query)
                               throws SQLException

Returns a new instance of NamedParamStatement. This instance, internally, creates/wraps a new prepared statement. (the query string is not reparsed every time, just the first time this method is invoked for any particular query). The returned query is not scrollable (use getScrollable(java.sql.Connection, java.lang.String) for a scrollable result set).

Throws:

SQLException

getScrollable

public static NamedParamStatement getScrollable(Connection con,
                                                String query)
                                         throws SQLException

Returns a new instance of NamedParamStatement. This instance, internally, creates/wraps a new PreparedStatement. (the query string is not reparsed every time, just the first time this method is invoked for any particular query).

This method ensures that any ResultSet returned by the wrapped PreparedStatement is scrollable (the ResultSet.TYPE_SCROLL_INSENSITIVE flag is used when creating the PreparedStatement).

Throws:

SQLException

close

public void close()
           throws SQLException

Throws:

SQLException

toString

public String toString()

Overrides:

toString in class Object

executeQuery

public ResultSet executeQuery()
                       throws SQLException

Throws:

SQLException

executeUpdate

public int executeUpdate()
                  throws SQLException

Throws:

SQLException

setNull

public void setNull(String name,
                    int sqlType)
             throws SQLException

Throws:

SQLException

setBoolean

public void setBoolean(String name,
                       boolean x)
                throws SQLException

Throws:

SQLException

setByte

public void setByte(String name,
                    byte x)
             throws SQLException

Throws:

SQLException

setShort

public void setShort(String name,
                     short x)
              throws SQLException

Throws:

SQLException

setInt

public void setInt(String name,
                   int x)
            throws SQLException

Throws:

SQLException

setLong

public void setLong(String name,
                    long x)
             throws SQLException

Throws:

SQLException

setFloat

public void setFloat(String name,
                     float x)
              throws SQLException

Throws:

SQLException

setDouble

public void setDouble(String name,
                      double x)
               throws SQLException

Throws:

SQLException

setBigDecimal

public void setBigDecimal(String name,
                          BigDecimal x)
                   throws SQLException

Throws:

SQLException

setString

public void setString(String name,
                      String x)
               throws SQLException

Throws:

SQLException

setBytes

public void setBytes(String name,
                     byte[] x)
              throws SQLException

Throws:

SQLException

setDate

public void setDate(String name,
                    Date x)
             throws SQLException

Throws:

SQLException

setTime

public void setTime(String name,
                    Time x)
             throws SQLException

Throws:

SQLException

setTimestamp

public void setTimestamp(String name,
                         Timestamp x)
                  throws SQLException

Throws:

SQLException

setAsciiStream

public void setAsciiStream(String name,
                           InputStream x,
                           int length)
                    throws SQLException

Throws:

SQLException

setBinaryStream

public void setBinaryStream(String name,
                            InputStream x,
                            int length)
                     throws SQLException

Throws:

SQLException

clearParameters

public void clearParameters()
                     throws SQLException

Throws:

SQLException

setObject

public void setObject(String name,
                      Object x,
                      int targetSqlType,
                      int scale)
               throws SQLException

Throws:

SQLException

setObject

public void setObject(String name,
                      Object x,
                      int targetSqlType)
               throws SQLException

Throws:

SQLException

setObject

public void setObject(String name,
                      Object x)
               throws SQLException

Throws:

SQLException

execute

public boolean execute()
                throws SQLException

Throws:

SQLException

addBatch

public void addBatch()
              throws SQLException

Throws:

SQLException

setCharacterStream

public void setCharacterStream(String name,
                               Reader reader,
                               int length)
                        throws SQLException

Throws:

SQLException

setRef

public void setRef(String name,
                   Ref x)
            throws SQLException

Throws:

SQLException

setBlob

public void setBlob(String name,
                    Blob x)
             throws SQLException

Throws:

SQLException

setClob

public void setClob(String name,
                    Clob x)
             throws SQLException

Throws:

SQLException

setArray

public void setArray(String name,
                     Array x)
              throws SQLException

Throws:

SQLException

getMetaData

public ResultSetMetaData getMetaData()
                              throws SQLException

Throws:

SQLException

setDate

public void setDate(String name,
                    Date x,
                    Calendar cal)
             throws SQLException

Throws:

SQLException

setTime

public void setTime(String name,
                    Time x,
                    Calendar cal)
             throws SQLException

Throws:

SQLException

setTimestamp

public void setTimestamp(String name,
                         Timestamp x,
                         Calendar cal)
                  throws SQLException

Throws:

SQLException

setNull

public void setNull(String name,
                    int sqlType,
                    String typeName)
             throws SQLException

Throws:

SQLException

setURL

public void setURL(String name,
                   URL x)
            throws SQLException

Throws:

SQLException

getParameterMetaData

public ParameterMetaData getParameterMetaData()
                                       throws SQLException

Throws:

SQLException