A DBConnStatement that handles a java.sql.PreparedStatement. See the documentation for PreparedStatement for information.
Source code: DBCSPrepared.java.
Note that any sql provided into this class must have "?" in all appropriate pramater locations, as described in the documentation for java.sql.PreparedStatement. Before executing any query against the prepared statement in this object, you must first set all parameters (using setString, setInt, ..., [more will be created as requested/necessary]).
Using Template and TemplateFiller can ease this process.
For example:
String sSql = INSERT INTO my_table
(col_name_1_str,
col_name_2_int,
col_name_3_int,
col_name_4_str)
VALUES
(\~G\~col_name_1_str\~EG\~,
\~G\~col_name_2_int\~EG\~,
\~G\~col_name_3_int\~EG\~,
\~G\~col_name_4_str\~EG\~)
All parameters need to have a "?", which will be filled in later by the setParameter functions. So...
Template tSql = new Template(<A HREF="../string/FLRString.html">FLRString</a>(sSql));
TemplateFiller tf = new TemplateFiller();
String sPreparedStatementReadySql = tf.getAllFilledWith(tSql, "?");
...where sPreparedStatementReadySql now equals:
INSERT INTO my_table
(col_name_1_str,
col_name_2_int,
col_name_3_int,
col_name_4_str)
VALUES
(?,
?,
?,
?)
Now, create this DBCSPrepared using this string:
DBCSPrepared dbcsPrepared = new DBCSPrepared(dbConn, sPreparedStatementReadySql);
You could set the parameters the old fashioned way:
dbcsPrepared.getPrepared().setString(1, "The string");
dbcsPrepared.getPrepared().setInt(2, 154);
dbcsPrepared.getPrepared().setInt(3, 583);
dbcsPrepared.getPrepared().setString(4, "The other string");
If you change the ordering of the parameters, you have to change this code to reflect the new indexes.
Or, using the just-created Template, in combination with this class, you can easily fill these parameters, without the normal headache of having to know the exact location of each parameter:
dbcsPrepared.setString(tSql, "col_name_1_str", "The string");
dbcsPrepared.setInt(tSql, "col_name_3_int", 583);
dbcsPrepared.setString(tSql, "col_name_4_str", "The other string");
dbcsPrepared.setInt(tSql, "col_name_2_int", 154);
If you change the order of the query's parameters around, it does not matter. This code can stay the same because the parameters "index" value is dynamically derived from the Template object. Besides, it's a lot clearer to have the paremeter's name (the column name, if you're smart) directly in the code.
@version 0.9b @author Jeff Epstein, http://sourceforge.net/projects/xbnjava. **/ public class DBCSPrepared extends DBConnStatement { private PreparedStatement pstmt = null; private StringBuffer sbParams = new StringBuffer(sES); public DBCSPrepared(DBConnDirect db_connDirect, String s_sql) throws SQLException { super(db_connDirect, s_sql); createStatement(); } public DBCSPrepared(DBConnDirect db_connDirect, String s_sql, int i_resultSetType, int i_resultSetConcurrency) throws SQLException { super(db_connDirect, s_sql, i_resultSetType, i_resultSetConcurrency); createStatement(); } public final Statement getStatement() { return getPrepared(); } public PreparedStatement getPrepared() { return pstmt; } public String getSqlAndParams() { String sParams = sbParams.toString(); sbParams.setLength(0); return getSql() + sParams; } private void createStatement() throws SQLException { try { if(wereExtraRSSettingsProvided()) { pstmt = getDBConnDirect().getConnection().prepareStatement(getSql(), getRSType(), getRSConcurrency()); } else { pstmt = getDBConnDirect().getConnection().prepareStatement(getSql()); } } catch(SQLException sqlx) { crashAtStatementCreate(sqlx, null); } catch(NullPointerException npx) { crashAtStatementCreate(null, npx); } } public void setString(int i_columnNumber, String s_value) throws SQLException { getPrepared().setString(i_columnNumber, s_value); appendParamInfo("setString: col# " + i_columnNumber + "='" + s_value + "'"); } public void setInt(int i_columnNumber, int i_value) throws SQLException { getPrepared().setInt(i_columnNumber, i_value); appendParamInfo("setInt: col# " + i_columnNumber + "=" + i_value); } public void setString(Template t_emplate, String s_columnName, String s_value) throws SQLException { getPrepared().setString((t_emplate.getAOSLHashtable().getUniqueString(s_columnName).getUnqArrIdx() + 1), s_value); appendParamInfo("setString: " + s_columnName + "='" + s_value + "'"); } public void setInt(Template t_emplate, String s_columnName, int i_value) throws SQLException { getPrepared().setInt((t_emplate.getAOSLHashtable().getUniqueString(s_columnName).getUnqArrIdx() + 1), i_value); appendParamInfo("setInt: " + s_columnName + "=" + i_value); } private void appendParamInfo(String s_text) { sbParams.append("\n\t"); sbParams.append(s_text); } }