fc.jdbc
Class NamedParamStatement

java.lang.Object
  extended by 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
 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