package examples.dbo; /* * Auto generated on: Mon Oct 17 22:47:19 EDT 2016 * JDBC url: [jdbc:postgresql://127.0.0.1/test] * WARNING: Manual edits will be lost if/when this file is regenerated. */ import java.io.*; import java.math.*; import java.sql.*; import java.util.*; import fc.io.*; import fc.jdbc.*; import fc.jdbc.dbo.*; import fc.util.*; import fc.web.forms.*; /** Manages various operations on the mollytest table. <p>Most methods of this class take a {@link java.sql.Connection Connection} as an argument and use that connection to run various queries. The connection parameter is never closed by methods in this class and that connection can and should be used again. Methods of this class will also throw a <tt>IllegalArgumentException</tt> if the specified connection object is <tt>null</tt>. <p>Thread Safety: Operations on this class are by and large thread safe in that multiple threads can call the methods at the same time. However, seperate threads should use seperate connection objects when invoking methods of this class. */ public final class mollytestMgr extends fc.jdbc.dbo.DBOMgr { /* --- Fields used for collecting usage statistics --- Increments to these don't need to be synchronized since these are ints and not longs and memory visibility is not an issue in the toString() method (in which these are read). */ private static int __getall_called = 0; private static int __getlimited_called = 0; private static int __getbykey_called = 0; private static int __getwhere_called = 0; private static int __getusing_called = 0; private static int __getusing_ps_called = 0; private static int __getfromrs_called = 0; private static int __save_called = 0; private static int __delete_called = 0; private static int __deletebykey_called = 0; private static int __deletewhere_called = 0; private static int __deleteusing_called = 0; private static int __count_called = 0; private static int __countwhere_called = 0; private static int __countusing_called = 0; private static int __exists_called = 0; /* -------------- end statistics fields -------------- */ /** Constructor is private since class is never instantiated */ private mollytestMgr() { } static private final String getAllStmt = "SELECT uid, name, email, password, created_on, is_active from mollytest"; /** Returns all rows in the table. Use with care for large tables since this method can result in VM out of memory errors. <p>This method also takes an optional (can be null) <tt>clause</tt> parameter which is sent as is to the database. For example, a clause can be: <blockquote><pre> order by some_column_name </pre> </blockquote> @return a list containing {@link mollytest } objects <i>or an empty list</i> if there are no rows in the database*/ public static List getAll(final Connection con, final String clause) throws SQLException { __getall_called++; final List list = new ArrayList(); final String getAllStmtClaused = (clause == null) ? getAllStmt : getAllStmt + " " + clause; PreparedStatement ps = prepareStatement(con, getAllStmtClaused); log.bug("Query to run: ", ps); final ResultSet rs = ps.executeQuery(); while (true) { mollytest bean = decodeFromRS(rs); if (bean == null) { break; } list.add(bean); } rs.close(); return list; } /** Convenience method that invokes {@link getAll(Connection, mollytest, String) getAll} with an empty additional clause. */ public static List getAll(final Connection con) throws ValidateException, SQLException { return getAll(con, null); } static private final String getLimitedStmt = "SELECT uid, name, email, password, created_on, is_active from mollytest"; /** Returns all rows in the table starting from some row number and limited by a certain number of rows after that starting row. <p> This method takes a required (non-null) <code>order_clause</code>, since when using a limit clause, rows must be ordered for the limit to make sense. The clause should be of the form <font color=blue>order by ...</font> <p> The <code>limit</code> specifies the number of rows that will be returned. (those many or possibly lesser rows will be returned, if the query itself yields less rows). <p> The <code>offset</code> skips that many rows before returning rows. A zero offset is the same as a traditional query with no offset clause, where rows from the beginning are returned. If say, offset = 10, then rows starting from row 11 will be returned. <p> The sql-query generated by this method is database specific but will (typically) look like: <blockquote><pre> select &lt;column_list&gt; from &lt;table&gt; order by &lt;clause&gt; limit 5 offset 10 </pre> </blockquote> @return a list containing {@link mollytest } objects <i>or an empty list</i> if there are no rows in the database*/ public static List getLimited(final Connection con, final String order_clause, int limit, int offset) throws SQLException { __getlimited_called++; final List list = new ArrayList(); final String tmp = getLimitedStmt + " " + order_clause + " LIMIT " + limit + " OFFSET " + offset; PreparedStatement ps = prepareStatement(con, tmp); log.bug("Query to run: ", ps); final ResultSet rs = ps.executeQuery(); while (true) { mollytest bean = decodeFromRS(rs); if (bean == null) { break; } list.add(bean); } rs.close(); return list; } static private final String getByPKStmt = "SELECT uid, name, email, password, created_on, is_active from mollytest WHERE uid=?"; /** Returns <b>the</b> row corresponding to the specified primary key(s) of this table or <b><tt>null</tt></b> if no row was found. <p>This method uses a prepared statement and is safe from SQL injection attacks */ public static mollytest getByKey(final Connection con, int uid) throws SQLException { __getbykey_called++; PreparedStatement ps = prepareStatement(con, getByPKStmt); StringBuilder errbuf = null; //uid [int] is primitive, skipping null test ps.setInt(1, uid); if (errbuf != null) { throw new ValidateException(errbuf.toString()); } final ResultSet rs = ps.executeQuery(); log.bug("Query to run: ", ps); mollytest bean = decodeFromRS(rs); rs.close(); return bean; } /** Returns the rows returned by querying the table with the specified <tt>WHERE</tt> clause or <i>an empty list</i> if no rows were found. (note: the string <tt>"WHERE"</tt> does <b>not</b> have to be specified in the clause. It is added automatically by this method). <p>Queries can use database functions such as: <code>lower()</code>, <code>upper()</code>, <code>LIKE</code> etc. For example: <pre><blockquote>mollytestMgr.getWhere("lower(col_a) = 'foo'") //compares the lower case value of col_a with the string 'foo' </blockquote></pre> <p><b>The "where" clause is sent as-is to the database</b>. SQL injection attacks are possible if it is created as-is from a <b><u>untrusted</u></b> source. @throws IllegalArgumentException if the specified <tt>where</tt> parameter is null */ public static List getWhere(final Connection con, final String where) throws SQLException { __getwhere_called++; Argcheck.notnull(where, "the where parameter was null (and should not be null)"); final String where_stmt = "SELECT uid, name, email, password, created_on, is_active from mollytest WHERE " + where ; Statement stmt = QueryUtil.getRewindableStmt(con); log.bug("Query to run: ", stmt, " ", where_stmt); final List list = new ArrayList(); final ResultSet rs = stmt.executeQuery(where_stmt); while (true) { mollytest bean = decodeFromRS(rs); if (bean == null) { break; } list.add(bean); } stmt.close(); return list; } /** Returns the rows returned by querying the table with the value of the specified <tt>mollytest</tt> object or <i>an empty list</i> if no rows were found. As many fields in <tt>alltypes</tt> can be set as needed and the values of all set fields (including fields explicitly set to <tt>null</tt>) are then used to perform the query. <p> This method is often convenient/safer than the {@link #getWhere getWhere} method (because the <tt>getWhere</tt> method takes an arbitrary query string which has to be properly escaped by the user). <p>Essentially, this method is a more convenient way to use a PreparedStatement. Internally, a prepared statement is created and it's parameters are set to fields that are set in this object). Using PreparedStatements directly is also perfectly fine. For example, the following are equivalent. <p> Using a PreparedStatement: <blockquote><pre> String foo = "select * from table_foo where x = ? and y = ?"; PreparedStatement ps = con.prepareStatement(foo); ps.setString(1, "somevalue"); ps.setString(2, "othervalue"); ResultSet rs = ps.executeUpdate(); while (rs.next()) { table_foo bean = table_fooMgr.getFromRS(rs); } </pre> </blockquote> Using this method: <blockquote><pre> table_foo <font color=blue>proto</font> = new table_foo(); proto.set_x("somevalue"); //compile time safety proto.set_y("othervalue"); //compile time safety List beans = table_fooMgr.<font color=blue>getUsing(proto)</font>; </pre> </blockquote> <p>This method also takes an <tt>clause</tt> parameter which is sent as is to the database. For example, a clause can be: <blockquote><pre> List beans = table_fooMgr.<font color=blue>getUsing(proto, <b>"order by some_column_name"</b>)</font>; </pre> </blockquote> This clause is optional. Specify <tt>null</tt> to not use it at all. If the clause is specified, do <font size='+1'><b>NOT</b></font> include the word <tt>WHERE</tt>. The fields that are set in the proto object (as shown above) are sent as part of a WHERE clause constructed internally. If you are specifying a clause as well, you should not specify the word <tt>WHERE</tt>. However, you may have to specify <tt>AND</tt> to add to the internal WHERE clause, if you have set any fields in the proto object. For example <blockquote><pre> List beans = table_fooMgr.<font color=blue>getUsing(proto, <b>"and bar = 5"</b>)</font>; </pre> </blockquote> <p>Note: For a <i>very</i> large number of rows, it may be more efficient to use a prepared statement directly (as opposed to using this method). In most cases, this is not something to worry about, but your mileage may vary... */ public static List getUsing(final Connection con, final mollytest bean, final String clause) throws ValidateException, SQLException { __getusing_called++; Argcheck.notnull(bean, "the bean parameter was null (and should not be null)"); if (! bean.isModified()) { throw new ValidateException("bean=" + bean + " not modified, ignoring query"); } int count = 0; final StringBuilder buf = new StringBuilder(512); buf.append("SELECT uid, name, email, password, created_on, is_active from mollytest WHERE "); if (bean.isModified_uid()) { buf.append("uid=? and "); count++; } if (bean.isModified_name()) { if (bean.get_name() == null) { buf.append("name is NULL and "); } else{ buf.append("name=? and "); count++; } } if (bean.isModified_email()) { if (bean.get_email() == null) { buf.append("email is NULL and "); } else{ buf.append("email=? and "); count++; } } if (bean.isModified_password()) { if (bean.get_password() == null) { buf.append("password is NULL and "); } else{ buf.append("password=? and "); count++; } } if (bean.isModified_created_on()) { if (bean.get_created_on() == null) { buf.append("created_on is NULL and "); } else{ buf.append("created_on=? and "); count++; } } if (bean.isModified_is_active()) { if (bean.get_is_active() == null) { buf.append("is_active is NULL and "); } else{ buf.append("is_active=? and "); count++; } } buf.setLength(buf.length() - 4); if (clause != null) { buf.append(" "); buf.append(clause); } final String getUsingPKStmt = buf.toString(); PreparedStatement ps = prepareStatement(con, getUsingPKStmt); int pos = 0; if (bean.isModified_uid()) { pos++; int uid = bean.get_uid(); ps.setInt(pos, uid); } if (bean.isModified_name()) { if (bean.get_name() == null) { /* no value to set here, uses [xxx IS NULL] syntax*/ } else{ pos++; String name = bean.get_name(); ps.setString(pos, name); } } if (bean.isModified_email()) { if (bean.get_email() == null) { /* no value to set here, uses [xxx IS NULL] syntax*/ } else{ pos++; String email = bean.get_email(); ps.setString(pos, email); } } if (bean.isModified_password()) { if (bean.get_password() == null) { /* no value to set here, uses [xxx IS NULL] syntax*/ } else{ pos++; String password = bean.get_password(); ps.setString(pos, password); } } if (bean.isModified_created_on()) { if (bean.get_created_on() == null) { /* no value to set here, uses [xxx IS NULL] syntax*/ } else{ pos++; java.sql.Date created_on = bean.get_created_on(); ps.setDate(pos, created_on); } } if (bean.isModified_is_active()) { if (bean.get_is_active() == null) { /* no value to set here, uses [xxx IS NULL] syntax*/ } else{ pos++; Boolean is_active = bean.get_is_active(); ps.setObject(pos, is_active); } } log.bug("Query to run: ", ps); final List list = new ArrayList(); final ResultSet rs = ps.executeQuery(); while (true) { mollytest row = decodeFromRS(rs); if (row == null) { break; } list.add(row); } rs.close(); return list; } /** Convenience method that invokes {@link getUsing(Connection, mollytest, String) getUsing} with an empty <i><tt>clause</tt></i> parameter.*/ public static List getUsing(final Connection con, final mollytest bean) throws ValidateException, SQLException { return getUsing(con, bean, null); } /** This is a <i>convenience</i> method that runs the specified prepared statement to perform an arbitrary query. For example: <blockquote> <pre> PreparedStatement <font color=blue>ps</font> = con.prepareStatement( "select * from some_table where some_column = ?"); ps.setString(1, "foo"); List list = fooMgr.<font color=blue>getUsing</font>(con, <font color=blue>ps</font>); for (int n = 0; n < list.size(); n++) { sometable t = (sometable) list.get(n); //do something } </pre> </blockquote> The effect of the above is <u>equivalent</u> to the following (larger) block of code: <blockquote> <pre> PreparedStatement <font color=blue>ps</font> = con.prepareStatement( "select * from sometable where some_column = ?" ); ps.setString(1, "foo"); ResultSet rs = <font color=blue>ps.executeQuery()</font>; List list = new ArrayList(); while (rs.next()) { list.add(sometableMgr.<font color=blue>getFromRS(rs)</font>); } for (int n = 0; n < list.size(); n++) { sometable t = (sometable) list.get(n); //do something } </pre> </blockquote> Note: Just as with other get<i>XXX</i> methods, for large amounts of rows (say many thousands), it may be more efficient use and iterate through a JDBC result set directly. */ public static List getUsing(final Connection con, final PreparedStatement ps) throws ValidateException, SQLException { __getusing_ps_called++; log.bug("Query to run: ", ps); final List list = new ArrayList(); final ResultSet rs = ps.executeQuery(); while (true) { mollytest row = decodeFromRS(rs); if (row == null) { break; } list.add(row); } rs.close(); return list; } /** This is a <i>convenience</i> method that runs the specified {@link fc.jdbc.dbo.NamedParamStatement NamedParamStatement} to perform an arbitrary query. For example: <blockquote> <pre> NamedParamStatement <font color=blue>ps</font> = queryReadeer.getQuery("somequery"); ps.setString("some_placeholder", "foo"); List list = fooMgr.<font color=blue>getUsing</font>(con, <font color=blue>ps</font>); for (int n = 0; n < list.size(); n++) { sometable t = (sometable) list.get(n); //do something } </pre> </blockquote> Note: Just as with other get<i>XXX</i> methods, for large amounts of rows (say many thousands), it may be more efficient use and iterate through a JDBC result set directly. */ public static List getUsing(final Connection con, final NamedParamStatement ps) throws ValidateException, SQLException { __getusing_ps_called++; log.bug("Query to run: ", ps); final List list = new ArrayList(); final ResultSet rs = ps.executeQuery(); while (true) { mollytest row = decodeFromRS(rs); if (row == null) { break; } list.add(row); } rs.close(); return list; } /** Returns a <i>comma delimited list</i> of <i>all</i> columns in <tt>mollytest</tt>. These column names are fully qualified, i.e., they contain table name as a prefix to the column name. For example: <blockquote><pre> <tt>tablename.column1 AS tablename_column1, tablename.column2 AS tablename_column2 ...</tt> </pre></blockquote> <p>This list is suitable for placing in the column(s) clause of a select query, such as: <blockquote> <tt>Single table: select <i><font color=blue>[column_list_A]</font></i> from table_A</tt><br> <tt>Join: select <i><font color=blue>[column_list_A], [column_list_B]</font></i> from table_A, table_B</tt> </blockquote> The ResultSet returned by the query can be used directly or can be passed to the {@link #getFromRS getFromRS} method to convert it into a list of <code>mollytest </code> objects. If the query is a join across multiple tables, then the {@link #getFromRS getFromRS} method for each table manager can be called on the same ResultSet to retrieve the row object for that table. Note: the returned list of names has a trailing space, which is good when the rest of the query is appended to this list. */ public static String columns() throws SQLException { return "mollytest.uid as mollytest_uid, mollytest.name as mollytest_name, mollytest.email as mollytest_email, mollytest.password as mollytest_password, mollytest.created_on as mollytest_created_on, mollytest.is_active as mollytest_is_active "; } /** Returns a <i>comma delimited list</i> of <i>all</i> columns in <tt>mollytest</tt>. These column names are prefix with the specified prefix, which corresponds to the table abbreviation used in the "AS" clause. For example: <blockquote><pre> <tt>xyz.column1 AS xyz_column1, xyz.column2 AS xyz_column2 ...</tt> </pre></blockquote> <p>This list is suitable for placing in the column(s) clause of a select query, such as: <blockquote> <p><b>Note:</b> the "." will automatically be appended between the prefix and column name so the prefix should not end with a "." or "_", etc<p> <tt>Single table: select <i><font color=blue>[column_list_A]</font></i> from table_A <b>AS</b> xyz</tt><br> <tt>Join: select <i><font color=blue>[column_list_A], [column_list_B]</font></i> from table_A <b>AS</b> xyz, table_B <b>AS</b> zzz</tt> </blockquote> The ResultSet returned by the query can be used directly or can be passed to the {@link #getFromRS getFromRS(String)} method to convert it into a list of <code>mollytest </code> objects. If the query is a join across multiple tables, then the {@link #getFromRS getFromRS(String)} method for each table manager can be called on the same ResultSet to retrieve the row object for that table. Note: the returned list of names has a trailing space, which is good when the rest of the query is appended to this list. */ public static String columns(String prefix) throws SQLException { final StringBuffer buf = new StringBuffer(6 * 10); buf.append(prefix); buf.append("."); buf.append("uid"); buf.append(" as "); buf.append(prefix); buf.append("_"); buf.append("uid"); buf.append(", "); buf.append(prefix); buf.append("."); buf.append("name"); buf.append(" as "); buf.append(prefix); buf.append("_"); buf.append("name"); buf.append(", "); buf.append(prefix); buf.append("."); buf.append("email"); buf.append(" as "); buf.append(prefix); buf.append("_"); buf.append("email"); buf.append(", "); buf.append(prefix); buf.append("."); buf.append("password"); buf.append(" as "); buf.append(prefix); buf.append("_"); buf.append("password"); buf.append(", "); buf.append(prefix); buf.append("."); buf.append("created_on"); buf.append(" as "); buf.append(prefix); buf.append("_"); buf.append("created_on"); buf.append(", "); buf.append(prefix); buf.append("."); buf.append("is_active"); buf.append(" as "); buf.append(prefix); buf.append("_"); buf.append("is_active"); buf.append(" "); return buf.toString(); } /** Creates and returns a new <tt>mollytest</tt> object that represents a row from the specified ResultSet. The ResultSet is typically obtained via a handwritten query/PreparedStatement. The resulting ResultSet should contain all of the column names of table, and this will only happen if the handwritten query had a select statement that specified all fields or used a <tt>select <b>*</b>..</tt> clause. <p> In the select clause, we could also be selecting multiple tables. To disambiguate between the same field names that may exist in multiple tables, this method also requires that the query should use <font color=blue>fully qualified</font> (prefixed with the table name) column names, such as: <blockquote><pre> <font color=blue>tablename</font>_column1 <font color=blue>tablename</font>_column2 ...etc. </pre></blockquote> <p> For example: <blockquote> <code>select <font color=blue>foo</font>.a <b>AS</b> <font color=blue>foo</font>_a, <font color=red>bar</font>.a <b>AS</b> <font color=red>bar</font>_a from <font color=blue>foo</font>, <font color=red>bar</font> where foo.a = bar.a;</code> </blockquote> The {@link #columns} method conveniently returns a list of column names in fully qualified format and is useful for this purpose. <p>Note: This method will read the <i>current</i> row from the specified result set and will <b>not</b> move the result set pointer to the next row after the current row has been read. The result set should be appropriately positioned [via <tt>rs.next()</tt>] <i>before</i> calling this method. @return a new {@link mollytest} object populated with the contents of the next row from the result set or <tt> null </tt> if the ResultSet was empty. */ public static mollytest getFromRS(final ResultSet rs) throws SQLException { __getfromrs_called++; Argcheck.notnull(rs, "the specified resultset parameter was null"); boolean hasrow = ! rs.isAfterLast(); if (! hasrow) { return null; } mollytest bean = new mollytest(); bean.set_uid( rs.getInt("mollytest_uid") ); bean.__orig_uid = rs.getInt("mollytest_uid"); /* save original PK */ if (rs.wasNull()) { bean.__isNullInDB_uid = true; } bean.set_name( rs.getString("mollytest_name") ); if (rs.wasNull()) { bean.__isNullInDB_name = true; } bean.set_email( rs.getString("mollytest_email") ); if (rs.wasNull()) { bean.__isNullInDB_email = true; } bean.set_password( rs.getString("mollytest_password") ); if (rs.wasNull()) { bean.__isNullInDB_password = true; } bean.set_created_on( rs.getDate("mollytest_created_on") ); if (rs.wasNull()) { bean.__isNullInDB_created_on = true; } bean.set_is_active( ((Boolean) rs.getObject("mollytest_is_active")) ); if (rs.wasNull()) { bean.__isNullInDB_is_active = true; } /* set to true when instantiated new, false when we populate the bean from a resultset */ bean.setNew(false); /* it's not modified, just loaded from the database */ bean.resetModified(); return bean; } /** Creates and returns a new <tt>mollytest</tt> object that represents a row from the specified ResultSet. The ResultSet is typically obtained via a handwritten query/PreparedStatement. The resulting ResultSet should contain all of the column names of table, prefixed with the specified <i>prefix</i> argument. a select statement that specified all fields or used a <tt>select <b>*</b>..</tt> clause. <p> In the select clause, we could also be selecting multiple tables. To disambiguate between the same field names that may exist in multiple tables, this method also requires that the query should use a <font color=blue>prefix</font> (some arbitrary prefix) before column names, such as: <blockquote><pre> <font color=blue>foo</font>_column1 <font color=blue>foo</font>_column2 ...etc. </pre></blockquote> This prefix will typically be the same as the table abbreviation chosen via the <b>AS</b> clause. If the AS clause is not used, then it is simpler to use the {@link getFromRS(ResultSet)} method instead <p><b>Note:</b> the "." will automatically be appended between the prefix and column name so the prefix should not end with a "." or "_", etc<p> <p> For example: <blockquote> <code>select <font color=blue>XXX</font>.a <b>AS</b> <font color=blue>XXX</font>_a, <font color=red>YYY</font>.a <b>AS</b> <font color=red>YYY</font>_a from <font color=blue>foo as XXX</font>, <font color=red>bar as YYY</font> where foo.a = bar.a;</code> </blockquote> The {@link #columns} method conveniently returns a list of column names in fully qualified format and is useful for this purpose. <p>Note: This method will read the <i>current</i> row from the specified result set and will <b>not</b> move the result set pointer to the next row after the current row has been read. The result set should be appropriately positioned [via <tt>rs.next()</tt>] <i>before</i> calling this method. @return a new {@link mollytest} object populated with the contents of the next row from the result set or <tt> null </tt> if the ResultSet was empty. */ public static mollytest getFromRS(final ResultSet rs, String prefix) throws SQLException { __getfromrs_called++; Argcheck.notnull(rs, "the specified resultset parameter was null"); boolean hasrow = ! rs.isAfterLast(); if (! hasrow) { return null; } mollytest bean = new mollytest(); bean.set_uid( rs.getInt(prefix+"_uid") ); bean.__orig_uid = rs.getInt(prefix+"_uid"); /* save original PK */ if (rs.wasNull()) { bean.__isNullInDB_uid = true; } bean.set_name( rs.getString(prefix+"_name") ); if (rs.wasNull()) { bean.__isNullInDB_name = true; } bean.set_email( rs.getString(prefix+"_email") ); if (rs.wasNull()) { bean.__isNullInDB_email = true; } bean.set_password( rs.getString(prefix+"_password") ); if (rs.wasNull()) { bean.__isNullInDB_password = true; } bean.set_created_on( rs.getDate(prefix+"_created_on") ); if (rs.wasNull()) { bean.__isNullInDB_created_on = true; } bean.set_is_active( ((Boolean) rs.getObject(prefix+"_is_active")) ); if (rs.wasNull()) { bean.__isNullInDB_is_active = true; } /* set to true when instantiated new, false when we populate the bean from a resultset */ bean.setNew(false); /* it's not modified, just loaded from the database */ bean.resetModified(); return bean; } /** Creates and returns a new <tt>mollytest</tt> object that represents a row from the specified ResultSet. For this method to work properly, the specified ResultSet should contain <b>all</b> (typically via <b>select * </b>) of the column names of table.<tt>mollytest</tt>. <p> This method does not prepend the table name to columns when reading data from the result set. It is useful when writing a JDBC query by hand that uses a single table (no joins) and then converting the returned result set into objects of this class. For example: <p> <code>select a, b, c, c*2 from foo where a = 1;</code> <p> This method will expect columns to be called <code><i>a, b, c</i></code> (no column aliases) in the returned result set. In this example, there is only one table <code>foo</code> so qualifying the column names, like <code>foo.a as foo_a</code> is not necessary). Also note, for this method to work properly, the column list<blockquote><code>select <i>a, b, c </i></code> ...</blockquote> should be complete, i.e., contain <i>at least</i> all the columns of this table (<i>additional</i> expressions like c*2 are fine). It is slightly less efficient to retrieve all columns especially for large tables but to construct a row into an object, we need all the fields. To be safe, use <blockquote><tt>select * ....</tt></blockquote> <p> Of course, if one needs a subset of columns, one can use the ResultSet directly and forego trying to convert a ResultSet row into an corresponding object <p> See {@link getFromRS(ResultSet)} which is more useful when writing a JDBC query that uses multiple table joins. <p>Note: This method will read the <i>current</i> row from the specified result set and will <b>not</b> move the result set pointer to the next row after the current row has been read. The result set should be appropriately positioned [via <tt>rs.next()</tt>] <i>before</i> calling this method. @return a new {@link mollytest} object populated with the contents of the next row from the result set or <tt> null </tt> if the ResultSet was empty. */ public static mollytest getFromRS1Table(final ResultSet rs) throws SQLException { __getfromrs_called++; Argcheck.notnull(rs, "the specified resultset parameter was null"); boolean hasrow = ! rs.isAfterLast(); if (! hasrow) { return null; } mollytest bean = new mollytest(); bean.set_uid( rs.getInt(1) ); bean.__orig_uid = rs.getInt(1); /* save original PK */ if (rs.wasNull()) { bean.__isNullInDB_uid = true; } bean.set_name( rs.getString(2) ); if (rs.wasNull()) { bean.__isNullInDB_name = true; } bean.set_email( rs.getString(3) ); if (rs.wasNull()) { bean.__isNullInDB_email = true; } bean.set_password( rs.getString(4) ); if (rs.wasNull()) { bean.__isNullInDB_password = true; } bean.set_created_on( rs.getDate(5) ); if (rs.wasNull()) { bean.__isNullInDB_created_on = true; } bean.set_is_active( ((Boolean) rs.getObject(6)) ); if (rs.wasNull()) { bean.__isNullInDB_is_active = true; } /* set to true when instantiated but this should be false whenever we populate the bean from a result set */ bean.setNew(false); //it's not modified, just loaded from the database bean.resetModified(); return bean; } private static mollytest decodeFromRS(final ResultSet rs) throws SQLException { Argcheck.notnull(rs, "the specified resultset parameter was null"); boolean hasrow = rs.next(); if (! hasrow) { return null; } mollytest bean = new mollytest(); bean.set_uid( rs.getInt(1) ); bean.__orig_uid = rs.getInt(1); /* save original PK */ if (rs.wasNull()) { bean.__isNullInDB_uid = true; } bean.set_name( rs.getString(2) ); if (rs.wasNull()) { bean.__isNullInDB_name = true; } bean.set_email( rs.getString(3) ); if (rs.wasNull()) { bean.__isNullInDB_email = true; } bean.set_password( rs.getString(4) ); if (rs.wasNull()) { bean.__isNullInDB_password = true; } bean.set_created_on( rs.getDate(5) ); if (rs.wasNull()) { bean.__isNullInDB_created_on = true; } bean.set_is_active( ((Boolean) rs.getObject(6)) ); if (rs.wasNull()) { bean.__isNullInDB_is_active = true; } /* set to true when newly instantiated but this should be false whenever we populate the bean from a result set */ bean.setNew(false); //it's not modified, just loaded from the database bean.resetModified(); return bean; } /** Saves the specified object into the database. If the specified object was newly created, then it is <span style="font-variant: small-caps">insert</span>'ed into the database, else (if it was retrieved earlier from the database) it is <span style="font-variant: small-caps">update</span>'ed. (this can be overriden by the {@link #update update} method). If the object is inserted as a new row, then after insertion, the values of serial/auto-incremented columns will be automatically available via the appropriate getXXX() methods on that object. <p> <b>NOTE 1:</b> When saving an object, only modified fields are saved. Do not rely on default field values (such as null) of newly created objects; instead explicitly set the value (including to null if needed) of any field that should be saved to the database. <p> <b>NOTE 2:</b> Once an object is successfully saved, it is discarded and cannot be saved again and any attempt to save it again will result in a runtime exception. Objects that need to be modified again must be re-instantiated or re-populated from the database before they can be saved again. (the serial/auto-increment data will still be available, discarding only affects the ability to save the object again). <p> <b>Note 3:</b> <font color='red'>For various reasons/flexiblity, default database values for columns <i>other</i> than serial columns are <b>not</b> available in the saved object. To get these values, retrieve the saved object again. (this is what we would have to do internally anyway). This is relevant, for example, when a column has a default value of a now() timestamp, and we need to get that timestamp after the object has been saved</font> @return the number of rows inserted or updated (typically useful to see if an update succeeded) @throws ValidateException on a validation error @throws SQLException on some SQL/Database error @throws IOException by the available() method if/when setting a stream for longvar/text types */ public static int save(final Connection con, final mollytest bean) throws ValidateException, SQLException, IOException { __save_called++; Argcheck.notnull(bean, "the specified bean parameter was null"); checkDiscarded(bean); if (! bean.isModified()) { log.warn("bean=", bean, " not modified, IGNORING SAVE\n====DEBUG STACK TRACE====\n", IOUtil.throwableToString(new Exception())); return 0; } PreparedStatement ps = null; boolean inserting_a_row = false; if (bean.isNew() && ! bean.__force_update) { //insert new row validateBeforeSaveNew(bean); int count = 0; inserting_a_row = true; final StringBuilder buf = new StringBuilder(512); buf.append("INSERT into mollytest ("); if (bean.isModified_name()) { buf.append("name").append(", "); count++; } if (bean.isModified_email()) { buf.append("email").append(", "); count++; } if (bean.isModified_password()) { buf.append("password").append(", "); count++; } if (bean.isModified_created_on()) { buf.append("created_on").append(", "); count++; } if (bean.isModified_is_active()) { buf.append("is_active").append(", "); count++; } if (count == 0) { throw new ValidateException("Cannot save this bean because no column has been modified. Use JDBC directly as needed.\n"); } buf.setLength(buf.length() - 2); buf.append(") values ("); for (int n = 0; n < count; n++) { buf.append("?"); if ((n+1) < count) buf.append(", "); } buf.append(")"); final String insertByPKStmt = buf.toString(); ps = prepareStatement(con, insertByPKStmt); /* Insert any changed values into our prepared statement */ int pos = 0; if (bean.isModified_name()) { pos++; String name = bean.get_name(); ps.setString(pos, name); } if (bean.isModified_email()) { pos++; String email = bean.get_email(); ps.setString(pos, email); } if (bean.isModified_password()) { pos++; String password = bean.get_password(); ps.setString(pos, password); } if (bean.isModified_created_on()) { pos++; java.sql.Date created_on = bean.get_created_on(); ps.setDate(pos, created_on); } if (bean.isModified_is_active()) { pos++; Boolean is_active = bean.get_is_active(); ps.setObject(pos, is_active); } } else //update existing row { validateBeforeSaveUpdate(bean); int count = 0; final StringBuilder buf = new StringBuilder(512); buf.append("UPDATE "); buf.append("mollytest"); buf.append(" SET "); if (bean.isModified_name()) { buf.append("name=?, "); count++; } if (bean.isModified_email()) { buf.append("email=?, "); count++; } if (bean.isModified_password()) { buf.append("password=?, "); count++; } if (bean.isModified_created_on()) { buf.append("created_on=?, "); count++; } if (bean.isModified_is_active()) { buf.append("is_active=?, "); count++; } if (count == 0) { throw new ValidateException("Cannot save this bean because no column has been modified. Use JDBC directly as needed.\n"); } buf.setLength(buf.length() - 2); buf.append(" WHERE "); buf.append("uid=?"); ps = con.prepareStatement(buf.toString()); /* Insert any changed values into our prepared statement */ int pos = 0; if (bean.isModified_name()) { pos++; String name = bean.get_name(); ps.setString(pos, name); } if (bean.isModified_email()) { pos++; String email = bean.get_email(); ps.setString(pos, email); } if (bean.isModified_password()) { pos++; String password = bean.get_password(); ps.setString(pos, password); } if (bean.isModified_created_on()) { pos++; java.sql.Date created_on = bean.get_created_on(); ps.setDate(pos, created_on); } if (bean.isModified_is_active()) { pos++; Boolean is_active = bean.get_is_active(); ps.setObject(pos, is_active); } /* Set primary keys for the WHERE part of our prepared statement */ int uid = (bean.__force_update) ? bean.get_uid() : bean.__orig_uid; ps.setInt(++pos, uid); } //~else update; log.bug("Query to run: ", ps); int result = ps.executeUpdate(); if (inserting_a_row) { //get auto increment info /* Retrieve values from auto-increment columns */ ResultSet rs = null; Statement stmt = null; String query = null; boolean found = false; if (bean.isModified_uid()) { //column: uid //not getting auto increment value for this column //since not using auto increment, a value was specified manually } else{ stmt = con.createStatement(); query = "select currval('mollytest_uid_seq')"; rs = stmt.executeQuery(query); found = rs.next(); if (! found) throw new SQLException("No last inserted id returned"); bean.set_uid( rs.getInt(1)); if (rs.wasNull()) { bean.__isNullInDB_uid = true; } rs.close(); } } //discard after saving/updating for safety bean.discard(); return result; } /** Uses the specified object to update existing data in the database. <p> Note, the {@link #save save} method automatically saves newly created objects as <i>inserts</i> in the database (and prior <i>retrieved</i> objects, when subsequently modified, are saved as <i>updates</i>). <p> However, sometimes it is useful to create a <i>new</i> object and then use its data to <i>update</i> an existing row in the database. This method need <b>only</b> be called to save a <u>newly</u> created object as an <u>update</u> into the database (overriding the default action of saving new objects as inserts in the database). <p> Note, also, a bean can only be updated if the corresponding table it has at least one primary key defined. To update tables with no primary keys, use JDBC directly. <p> This method takes primary key(s) of {@link mollytest} as additional arguments and sets those in the specified bean before updating the database (this way the row to update can be uniquely identified). @see #save @return the number of rows that were updated (typically useful to see if an update succeeded) @throws ValidateException on a validation error @throws SQLException on some SQL/Database error */ public static int update(final Connection con, final mollytest bean, int uid) throws ValidateException, SQLException, IOException { bean.set_uid(uid); if (bean.isNew()) { /* force update (and not insert) for new bean */ bean.__force_update = true; } return save(con, bean); } static private final String deleteStmt = "DELETE from mollytest WHERE uid=?"; /** Deletes this object from the database. <p> <b>NOTE 1:</b> Only objects that were retrieved from the database can be deleted. Newly created objects cannot be deleted since they do not yet exist in the database. Use {@link #deleteByKey deleteByKey} or {@link #deleteWhere deleteWhere} instead for arbitrary deletions. <p><b>NOTE 2:</b> Once an object is successfully deleted, it is discarded and cannot be deleted again and any attempt to delete it again will result in a runtime Exception. */ public static void delete(final Connection con, mollytest bean) throws SQLException { __delete_called++; if (bean.isNew()) { throw new DBOException("Cannot delete new objects using this method. Use deleteByKey() or deleteWhere() instead"); } checkDiscarded(bean); final PreparedStatement ps = prepareStatement(con, deleteStmt); int uid = bean.get_uid(); ps.setInt(1, uid); log.bug("Query to run: ", ps); final int result = ps.executeUpdate(); if (result != 1) { throw new DBOException("The number of deleted rows was: " + result + "; [Should have been 1 row exactly] "); } } static private final String deleteByPKStmt = "DELETE from mollytest WHERE uid=?"; /** Deletes the rows with the specified primary key(s) from the database. <p>This method uses a prepared statement and is safe from SQL injection attacks */ public static void deleteByKey(final Connection con, int uid) throws SQLException { __deletebykey_called++; PreparedStatement ps = prepareStatement(con, deleteByPKStmt); ps.setInt(1, uid); log.bug("Query to run: ", ps); final int result = ps.executeUpdate(); if (result != 1) { throw new DBOException("The number of deleted rows was: " + result + "; [Should have been 1 row exactly] "); } } /** Returns the rows returned by querying the table with the contents of the specified instance of <tt>alltypes</tt> or <tt>null</tt> if no rows were found. As many fields in <tt>alltypes</tt> can be set as needed and the values of all set fields (including fields explicitly set to <tt>null</tt>) are then used to perform the query. <p>Note, however that this method does use any primary key(s). If the primary keys are known then one should use the {@link #deleteByKey deleteByKey} method to delete the data instead. <p>Likewise, to delete a previously fetched row, use the {@link #delete delete} method. This method is really meant to create an new object, set various fields in it, and then use that to delete matching row(s) from the database in a type safe way. <p> This method is often convenient/safer than the {@link #deleteWhere deleteWhere} method (because the <tt>deleteWhere</tt> method takes an arbitrary query string which has to be properly escaped by the user). <p>However, as a middle ground, this method also takes an <tt>clause</tt> parameter which is sent as is to the database. For example, a clause can be: <blockquote><pre> List beans = table_fooMgr.<font color=blue>deleteUsing(proto, <b>"xyx > 5"</b>)</font>; </pre> </blockquote> This clause is optional. Specify <tt>null</tt> to not use it at all. If the clause is specified, do <font size='+1'><b>NOT</b></font> include the word <tt>WHERE</tt>. <p>Essentially, this method is a more convenient way to use a PreparedStatement. Internally, a prepared statement is created and it's parameters are set to fields that are set in this object). Using PreparedStatements directly is also perfectly fine. For example, the following are equivalent. <p> Using a PreparedStatement: <blockquote><pre> String foo = "delete from table_foo where x = ? and y = ?"; PreparedStatement ps = con.prepareStatement(foo); ps.setString(1, "somevalue"); ps.setString(2, "othervalue"); int rows_deleted = ps.executeUpdate(); </pre> </blockquote> Using this method: <blockquote><pre> table_foo proto = new table_foo(); proto.set_x("somevalue"); //compile time safety proto.set_y("othervalue"); //compile time safety int rows_deleted = table_fooMgr.<font color=blue>deleteUsing</font>(proto); </pre></blockquote> @return the number of rows deleted */ public static int deleteUsing(final Connection con, final mollytest bean, final String clause) throws ValidateException, SQLException { __deleteusing_called++; Argcheck.notnull(bean, "the bean parameter was null (and should not be null)"); if (! bean.isModified()) { throw new ValidateException("bean=" + bean + " not modified, ignoring query"); } final StringBuilder buf = new StringBuilder(512); buf.append("DELETE from mollytest WHERE "); int count = 0; if (bean.isModified_name()) { if (bean.get_name() == null) { buf.append("name is NULL and "); } else{ buf.append("name=? and "); count++; } } if (bean.isModified_email()) { if (bean.get_email() == null) { buf.append("email is NULL and "); } else{ buf.append("email=? and "); count++; } } if (bean.isModified_password()) { if (bean.get_password() == null) { buf.append("password is NULL and "); } else{ buf.append("password=? and "); count++; } } if (bean.isModified_created_on()) { if (bean.get_created_on() == null) { buf.append("created_on is NULL and "); } else{ buf.append("created_on=? and "); count++; } } if (bean.isModified_is_active()) { if (bean.get_is_active() == null) { buf.append("is_active is NULL and "); } else{ buf.append("is_active=? and "); count++; } } buf.setLength(buf.length() - 4); if (count == 0) { throw new ValidateException("No non-PrimaryKey column was modified/set in this bean. You must set at least one such column. To delete by the Primary key, use the deleteByKey method instead."); } if (clause != null) { buf.append(" "); buf.append(clause); } final String getUsingPKStmt = buf.toString(); PreparedStatement ps = prepareStatement(con, getUsingPKStmt); int pos = 0; if (bean.isModified_name()) { if (bean.get_name() == null) { /* no value to set here, uses [xxx IS NULL] syntax*/ } else{ pos++; String name = bean.get_name(); ps.setString(pos, name); } } if (bean.isModified_email()) { if (bean.get_email() == null) { /* no value to set here, uses [xxx IS NULL] syntax*/ } else{ pos++; String email = bean.get_email(); ps.setString(pos, email); } } if (bean.isModified_password()) { if (bean.get_password() == null) { /* no value to set here, uses [xxx IS NULL] syntax*/ } else{ pos++; String password = bean.get_password(); ps.setString(pos, password); } } if (bean.isModified_created_on()) { if (bean.get_created_on() == null) { /* no value to set here, uses [xxx IS NULL] syntax*/ } else{ pos++; java.sql.Date created_on = bean.get_created_on(); ps.setDate(pos, created_on); } } if (bean.isModified_is_active()) { if (bean.get_is_active() == null) { /* no value to set here, uses [xxx IS NULL] syntax*/ } else{ pos++; Boolean is_active = bean.get_is_active(); ps.setObject(pos, is_active); } } log.bug("Query to run: ", ps); List list = new ArrayList(); int result = ps.executeUpdate(); return result; } /** Convenience method that invokes {@link getUsing(Connection, mollytest, String) getUsing} with an empty <i><tt>clause</tt></i> parameter.*/ public static int deleteUsing(final Connection con, final mollytest bean) throws ValidateException, SQLException { return deleteUsing(con, bean, null); } /** Deletes the rows with the specified where clause. <p><b>The where clause is sent as-is to the database and SQL injection attacks are possible if it is created as-is from a untrusted source.</b> (note: the string <tt>"WHERE"</tt> does <b>not</b> have to be specified in the clause. It is added automatically by this method). @return the number of rows deleted by the database */ public static int deleteWhere(final Connection con, final String where) throws SQLException { __deletewhere_called++; Argcheck.notnull(where, "the where parameter was null (and should not be null)"); final String stmt_string = "DELETE from mollytest WHERE " + where ; Statement stmt = con.createStatement(); log.bug("Query to run: ", stmt_string); final int result = stmt.executeUpdate(stmt_string); return result; } private final static String countStmt = "SELECT count(*) from mollytest"; /** Returns the count of all rows in the table. <p><b>Note</b>: This may be an expensive operation in MVCC databases like PostgresQL, Oracle and others, where an entire non-optimized table scan <i>may</i> be required -- hence speed will typically be O(n). However, on Postgres (for example), this is still very fast for small values of n (on a mid-level test machine) as of 2004, counting 4k records was about 15 milli-seconds(ms); this scaled almost linearly, so count(*) for 16k records was about 70 ms, 65k records was about 370 ms, 524k records was about 2000 ms and 1 million records was about 4000 ms. Results will vary on your machine and database but the general O(n) principle will remain the same. */ public static int count(final Connection con) throws SQLException { __count_called++; int count = -1; final Statement stmt = con.createStatement(); final ResultSet rs = stmt.executeQuery(countStmt); if (rs.next()) { count = rs.getInt(1); } else { //rs returned no count, which should never happen throw new DBOException("The COUNT query [" + countStmt + "] returned no rows. [Should have returned 1 row exactly] "); } stmt.close(); return count; } /** Returns the count of rows in the table using the specified <tt>where</tt> clause. (note: the string <tt>"WHERE"</tt> does <b>not</b> have to be specified in the clause. It is added automatically by this method). @throws IllegalArgumentException if the where paramater was null */ public static int countWhere(final Connection con, final String where) throws SQLException { __countwhere_called++; Argcheck.notnull(where, "the where parameter was null"); int count = -1; final String countWhereStmt = "SELECT count(*) from mollytest WHERE " + where; Statement stmt = con.createStatement(); log.bug("Query to run: ", stmt, " ", countWhereStmt); ResultSet rs = stmt.executeQuery(countWhereStmt); if (rs.next()) { count = rs.getInt(1); } else { //rs returned no count, which should never happen throw new DBOException("The COUNT query [" + countWhereStmt + "] returned no rows. [Should have returned 1 row exactly] "); } stmt.close(); return count; } /** Returns the rows count by querying the table with the contents of the specified instance of <tt>mollytest</tt> As many fields in <tt>alltypes</tt> can be set as needed and the values of all set fields (including fields explicitly set to <tt>null</tt>) are then used to perform the query. If the primary key(s) are known then one can also use the {@link #exists} method to see if that row exists in the database. <p> This method is often convenient/safer than the {@link #countWhere countWhere} method (because the <tt>countWhere</tt> method takes an arbitrary query string which has to be properly escaped by the user). <p>Essentially, this method is a more convenient way to use a PreparedStatement (with parameters set to fields that are set in this object). Using PreparedStatements directly is also perfectly fine. For example, the following two are equivalent. <p> Using a PreparedStatement: <blockquote><pre> String foo = "select <i>count(*)</i> from table_foo where x = ? and y = ?"; PreparedStatement ps = con.prepareStatement(foo); ps.setString(1, "somevalue"); ps.setString(2, "othervalue"); ResultSet rs = ps.executeUpdate(); rs.next(); int count = rs.getInt(1); </pre> </blockquote> Using this method: <blockquote><pre> table_foo proto = new table_foo(); proto.set_x("somevalue"); //compile time safety proto.set_y("othervalue"); //compile time safety int count = table_fooMgr.<font color=blue>countUsing</font>(proto); </pre> </blockquote> The clause is optional. Specify <tt>null</tt> to not use it at all. If the clause is specified, do <font size='+1'><b>NOT</b></font> include the word <tt>WHERE</tt>. */ public static int countUsing(final Connection con, final mollytest bean, final String clause) throws ValidateException, SQLException { __countusing_called++; Argcheck.notnull(bean, "the bean parameter was null (and should not be null)"); if (! bean.isModified()) { throw new ValidateException("bean=" + bean + " not modified, ignoring query"); } int count = 0; final StringBuilder buf = new StringBuilder(512); buf.append("SELECT count(*) from mollytest WHERE "); if (bean.isModified_uid()) { buf.append("uid=? and "); count++; } if (bean.isModified_name()) { if (bean.get_name() == null) { buf.append("name is NULL and "); } else{ buf.append("name=? and "); count++; } } if (bean.isModified_email()) { if (bean.get_email() == null) { buf.append("email is NULL and "); } else{ buf.append("email=? and "); count++; } } if (bean.isModified_password()) { if (bean.get_password() == null) { buf.append("password is NULL and "); } else{ buf.append("password=? and "); count++; } } if (bean.isModified_created_on()) { if (bean.get_created_on() == null) { buf.append("created_on is NULL and "); } else{ buf.append("created_on=? and "); count++; } } if (bean.isModified_is_active()) { if (bean.get_is_active() == null) { buf.append("is_active is NULL and "); } else{ buf.append("is_active=? and "); count++; } } buf.setLength(buf.length() - 4); if (clause != null) { buf.append(" "); buf.append(clause); } final String countUsingStmt = buf.toString(); PreparedStatement ps = prepareStatement(con, countUsingStmt); int pos = 0; if (bean.isModified_uid()) { pos++; int uid = bean.get_uid(); ps.setInt(pos, uid); } if (bean.isModified_name()) { if (bean.get_name() == null) { /* no value to set here, uses [xxx IS NULL] syntax*/ } else{ pos++; String name = bean.get_name(); ps.setString(pos, name); } } if (bean.isModified_email()) { if (bean.get_email() == null) { /* no value to set here, uses [xxx IS NULL] syntax*/ } else{ pos++; String email = bean.get_email(); ps.setString(pos, email); } } if (bean.isModified_password()) { if (bean.get_password() == null) { /* no value to set here, uses [xxx IS NULL] syntax*/ } else{ pos++; String password = bean.get_password(); ps.setString(pos, password); } } if (bean.isModified_created_on()) { if (bean.get_created_on() == null) { /* no value to set here, uses [xxx IS NULL] syntax*/ } else{ pos++; java.sql.Date created_on = bean.get_created_on(); ps.setDate(pos, created_on); } } if (bean.isModified_is_active()) { if (bean.get_is_active() == null) { /* no value to set here, uses [xxx IS NULL] syntax*/ } else{ pos++; Boolean is_active = bean.get_is_active(); ps.setObject(pos, is_active); } } log.bug("Query to run: ", ps); ResultSet rs = ps.executeQuery(); if (! rs.next()) { throw new DBOException("The COUNT query [" + countUsingStmt + "] returned no rows. [Should have returned 1 row exactly] "); } int rows = rs.getInt(1); rs.close(); return rows; } /** Convenience method that invokes {@link getUsing(Connection, mollytest, String) getUsing} with an empty <i><tt>clause</tt></i> parameter.*/ public static int countUsing(final Connection con, final mollytest bean) throws ValidateException, SQLException { return countUsing(con, bean, null); } static private final String existsStmt = "SELECT count(*) from mollytest WHERE uid=?"; /** Returns <tt>true</tt> if a row with the specified primary keys exists, <tt>false</tt> otherwise. <p>This method uses a prepared statement and is safe from SQL injection attacks */ public static boolean exists(final Connection con, int uid) throws SQLException { __exists_called++; PreparedStatement ps = prepareStatement(con, existsStmt); ps.setInt(1, uid); log.bug("Query to run: ", ps); ResultSet rs = ps.executeQuery(); int count = -1; if (rs.next()) { count = rs.getInt(1); } else { //rs returned no count, which should never happen throw new DBOException("The COUNT query [" + existsStmt + "] returned no rows. [Should have returned 1 row exactly] "); } rs.close(); return (count > 0); //exists if count > 0 } /** A thin wrapper around {@link getUsing(Connection,mollytest) getUsing} that returns <tt>false</tt> if no rows are returned, <tt>true</tt> otherwise. */ public static boolean existsUsing(final Connection con, final mollytest bean) throws ValidateException, SQLException { final List list = getUsing(con, bean, null); return (list.size() > 0); } /** Returns a prepared statement given it's variable name. Essentially speeds up the creation of prepared statements perhaps using a per connection cache -- which makes sense for pooled connections since they are not closed but returned to the pool and hence don't need to "prepare" statements every time (the prepareStatement call is seperate from actually filling in the placeholders in a already created prepared statement -- which does need to be done every time). <p> Prepared statements are unique per connection, so multiple threads using different connections won't stomp over each other's prepared statements. Multiple threads using the SAME connection will cause bizarre errors but multiple threads won't get the same connection from the connection manager -- ever :-), so that should never happen. */ private static final PreparedStatement prepareStatement ( final Connection con, final String sql) throws SQLException { if (! (con instanceof fc.jdbc.PooledConnection) ) { return con.prepareStatement(sql); } final PooledConnection pc = (PooledConnection) con; return pc.getCachedPreparedStatement(sql); } private static final void checkDiscarded(final DBO bean) throws DBOException { if (bean.isDiscarded()) { throw new DBOException("===== Attempt to save a discarded object === " + bean); } } private static final java.util.Date __loadDate = new java.util.Date(); /** Returns usage statistics for this class */ public static String stats() { //locally created _numberFormat for thread safety final java.text.NumberFormat _numberFormat = java.text.NumberFormat.getInstance(); final String nl = fc.io.IOUtil.LINE_SEP; StringBuffer buf = new StringBuffer(256); buf.append("Class Name: [mollytestMgr]; Class loaded on: "); buf.append(__loadDate); buf.append(nl); buf.append("---- Start Usage Statistics ----").append(nl); ByteArrayOutputStream out = new ByteArrayOutputStream(512); TablePrinter.PrintConfig config = new TablePrinter.PrintConfig(); config.setPrintBorders(false); config.setCellSpacing(1); config.setCellPadding(0); config.setAutoFit(true); TablePrinter p = new TablePrinter(2, new PrintStream(out), config); p.startTable(); p.startRow(); p.printCell("Method"); p.printCell("# times called"); p.endRow(); p.startRow(); p.printCell("getAll()"); p.printCell(_numberFormat.format(__getall_called)); p.endRow(); p.startRow(); p.printCell("getLimited()"); p.printCell(_numberFormat.format(__getlimited_called)); p.endRow(); p.startRow(); p.printCell("getByKey()"); p.printCell(_numberFormat.format(__getbykey_called)); p.endRow(); p.startRow(); p.printCell("getWhere()"); p.printCell(_numberFormat.format(__getwhere_called)); p.endRow(); p.startRow(); p.printCell("getUsing()"); p.printCell(_numberFormat.format(__getusing_called)); p.endRow(); p.startRow(); p.printCell("getUsing(prepared_stmt)"); p.printCell(_numberFormat.format(__getusing_ps_called)); p.endRow(); p.startRow(); p.printCell("getFromRS()"); p.printCell(_numberFormat.format(__getfromrs_called)); p.endRow(); p.startRow(); p.printCell("save()"); p.printCell(_numberFormat.format(__save_called)); p.endRow(); p.startRow(); p.printCell("delete()"); p.printCell(_numberFormat.format(__delete_called)); p.endRow(); p.startRow(); p.printCell("deleteByKey()"); p.printCell(_numberFormat.format(__deletebykey_called)); p.endRow(); p.startRow(); p.printCell("deleteWhere()"); p.printCell(_numberFormat.format(__deletewhere_called)); p.endRow(); p.startRow(); p.printCell("deleteUsing()"); p.printCell(_numberFormat.format(__deleteusing_called)); p.endRow(); p.startRow(); p.printCell("count()"); p.printCell(_numberFormat.format(__count_called)); p.endRow(); p.startRow(); p.printCell("countWhere()"); p.printCell(_numberFormat.format(__countwhere_called)); p.endRow(); p.startRow(); p.printCell("countUsing()"); p.printCell(_numberFormat.format(__countusing_called)); p.endRow(); p.startRow(); p.printCell("exists()"); p.printCell(_numberFormat.format(__exists_called)); p.endRow(); p.endTable(); buf.append(out.toString()); return buf.toString(); } public String toString() { return getClass().getName() + " [call stats() for more info]"; } // ================ Validation ==================== /** Creates and attaches validators for all the fields in the specified {@link fc.web.forms.Form}. These fields should <i>have the same name</i> in the form as in {@link mollytest}. If this is not the case, then the then the differences can be specifed as follows. <p> <dl> <dt>with a prefix</dt> <dd><tt>(prefix + mollytest column)</tt> should equal <tt>form fieldname</tt></dd> <dt>with a suffix</dt> <dd><tt>(mollytest column + suffix)</tt> should equal <tt>form fieldname</tt></dd> <dt>with both a prefix/suffix</dt> <dd><tt>(prefix + mollytest + suffix)</tt> should equal <tt>form fieldname</tt></dd> <dt>with a arbitrary map</dt> <dd>[key] <tt>mollytest column</tt> -> [value] <tt>form fieldname</tt> <u>If a map is specified, then the prefix/suffix are not used.</u> </dd> </dl> <p>These validators are for database constraints such as <i>nullability</i> & <i>column length</i>. These validators save a lot of grunt-work in adding such schema constraints to the front-end {@link fc.web.forms.Form}. <p><b>However, <i>business and other validation constraints</i> still need to be manually added to the application code/front-end forms as/when needed</b>. <p> The following table shows the kind of validators added by this method <table border=1 width=90%> <tr bgcolor='#CCCCCC'> <td>Database SQL Type</td> <td><b>Nullable</b>validator</td> <td><b>Length</b> validator</td> <td><b>Digits only</b> input validator ({@link VText#allowIntegersOnly})</td> </tr> <tr> <td><tt>CHAR</tt>, <tt>VARCHAR</tt></td> <td>Yes (maximum length constraint).<br><font size='-1' color=red>This only applies to form fields that are subclasses of {@link fc.web.forms.MaxSizable} </font></td> <td>-NO-</td> </tr> <tr> <td><tt>TINYINT, MEDIUMINT, INT, BIGINT (integral types)</tt></td> <td>Yes</td> <td>-NO-</td> <td>Yes to integer columns displayed using form fields that are subclasses of {@link fc.web.forms.AbstractText}<br> Note: <b>not</b> added non-<i>integral</i> number types such as <tt>FLOAT, REAL, DOUBLE, NUMERIC/DECIMAL</tt></td> </tr> <tr> <td>All other SQL types</td> <td>Yes</td> <td>-NO-</td> </tr> </table> <p>Automatic validators are very useful but can be very tricky to understand. It is suggested to invoke this method, print the form using it's <tt>toString</tt> method and then examine the output to see what validators were added If those automatic validators are too little, too many or too hard to understand, <u>then simply enoough, do NOT invoke this method and simply add validators by hand</u>. In particular, do <i>not</i> add automatic validators for <b>tables</b> in which a row is optional but <i>if</i> some column is filled in the front end form, <i>then</i> all columns must be filled. @param form the form containing fields (some or all) representing this and possible other tables. These field objects must have been added to the form prior to calling this method @param prefix an optional (null allowed) prefix to this table's column name with which the corresponding column was added to the form. A <tt>*</tt> specifies all possible prefixes @param suffix an optional suffix (null allowed) to this table's column name with which the corresponding column was added to the form. A <tt>*</tt> specifies all possible suffixes @param map an optional map (null allowed) that maps this table's column name with which the corresponding column was added to the form. [key] <tt>table's column_name</tt> -> [value] <tt>form's fieldname</tt> */ public static void addValidators(final fc.web.forms.Form form, final String prefix, final String suffix, final Map map) { addValidators(form, prefix, suffix, map, false); } private static void addValidators(fc.web.forms.Form form, String prefix, String suffix, final Map map, final boolean onlyOnFilled) { List list = null; Argcheck.notnull(form, "How can I add validators to the form when the form parameter was null ?"); Field field = null; FieldValidator fv = null; String colname_in_form = null; //fields can be null if they are not being used in the html form /** serial (INTEGER); PK=yes; Nullable=false; AutoInc=true; MaxSize=10 */ list = getFieldFromForm(form, "uid", prefix, suffix, map, false); if (list.size() > 0) { //add applicable automatic validators, empty if n/a for (int n = 0; n < list.size(); n++) { field = (Field) list.get(n); /* field is non-nullable but has a default value [nextval('mollytest_uid_seq'::regclass)], skipping non-nullability validation */ /* database type for this field is integral */ if (field instanceof AbstractText) { fv = new VText((AbstractText)field, "Error: Please enter only numbers in this field") .allowIntegersOnly(); } } } /** varchar (VARCHAR); Nullable=true; AutoInc=false; MaxSize=99 */ list = getFieldFromForm(form, "name", prefix, suffix, map, true); if (list.size() > 0) { //add applicable automatic validators, empty if n/a for (int n = 0; n < list.size(); n++) { field = (Field) list.get(n); /* field is nullable, skipping non-nullability validation */ if (! (field instanceof MaxSizable)) { log.warn("Skipping maximum length validator for field '" + field.getName() + "'; [database type='VARCHAR', field.type='" + field.getType() + "' is not MaxSizable]"); } else{ VText vt = new VText((MaxSizable) field, "Not enough or too many characters"); vt.setMaxSize(99); } } } /** varchar (VARCHAR); Nullable=true; AutoInc=false; MaxSize=99 */ list = getFieldFromForm(form, "email", prefix, suffix, map, true); if (list.size() > 0) { //add applicable automatic validators, empty if n/a for (int n = 0; n < list.size(); n++) { field = (Field) list.get(n); /* field is nullable, skipping non-nullability validation */ if (! (field instanceof MaxSizable)) { log.warn("Skipping maximum length validator for field '" + field.getName() + "'; [database type='VARCHAR', field.type='" + field.getType() + "' is not MaxSizable]"); } else{ VText vt = new VText((MaxSizable) field, "Not enough or too many characters"); vt.setMaxSize(99); } } } /** varchar (VARCHAR); Nullable=true; AutoInc=false; MaxSize=99 */ list = getFieldFromForm(form, "password", prefix, suffix, map, true); if (list.size() > 0) { //add applicable automatic validators, empty if n/a for (int n = 0; n < list.size(); n++) { field = (Field) list.get(n); /* field is nullable, skipping non-nullability validation */ if (! (field instanceof MaxSizable)) { log.warn("Skipping maximum length validator for field '" + field.getName() + "'; [database type='VARCHAR', field.type='" + field.getType() + "' is not MaxSizable]"); } else{ VText vt = new VText((MaxSizable) field, "Not enough or too many characters"); vt.setMaxSize(99); } } } /** date (DATE); Nullable=true; AutoInc=false; MaxSize=13 */ list = getFieldFromForm(form, "created_on", prefix, suffix, map, true); if (list.size() > 0) { //add applicable automatic validators, empty if n/a for (int n = 0; n < list.size(); n++) { field = (Field) list.get(n); /* field is nullable, skipping non-nullability validation */ } } /** bool (BIT); Nullable=true; AutoInc=false; MaxSize=1 */ list = getFieldFromForm(form, "is_active", prefix, suffix, map, true); if (list.size() > 0) { //add applicable automatic validators, empty if n/a for (int n = 0; n < list.size(); n++) { field = (Field) list.get(n); /* field is nullable, skipping non-nullability validation */ } } } /** Convenience method that calls {@link #addValidators(Form, String, String, Map)} with a <tt>null</tt> prefix/suffix and the specified map */ public static void addValidators(fc.web.forms.Form form, Map map) { addValidators(form, null, null, map); } /** Convenience method that calls {@link #addValidators(Form, String, String, Map)} with a <tt>null</tt> prefix/suffix/map */ public static void addValidators(fc.web.forms.Form form) { addValidators(form, null, null, null); } /** Convenience method that calls {@link #addValidators(Form, String, String, map)} with the specified prefix and a <tt>null</tt> suffix/map */ public static void addValidators(fc.web.forms.Form form, String prefix) { addValidators(form, prefix, null, null); } /** Validates a form field <i>if</i> it is filled by the user. Leaves empty fields alone. This is very useful for fields that are optional but must have the correct value when filled by the user */ public static void addIfFilledValidators(Form form, String prefix) { addValidators(form, prefix, null, null, true); } /** implementation helper method -- not for public use */ static List getFieldFromForm(Form form, String colname, String prefix, String suffix, Map map, boolean warn) { Field field = null; List list = Form.empty_list; boolean getwhere = false; getwhere = false; String colname_in_form = colname; if (map != null) { String str = (String) map.get(colname); if (str != null) { prefix = null; /*ignored when there is a mapping*/ suffix = null; /*ignored when there is a mapping*/ colname_in_form = str; /* else if not in map, colname remains as-is*/ } } if (prefix != null) { if (prefix.equals("*")) { getwhere = true; } else{ colname_in_form = prefix + colname_in_form; } } if (suffix != null) { if (suffix.equals("*")) { getwhere = true; } else{ colname_in_form = colname_in_form + suffix; } } if (getwhere) { list = form.getContaining(colname_in_form); if (list.size() == 0 && warn) warn(form, colname_in_form, suffix, prefix, map); return list; } else{ //containsField() check prevents an un-necessary warning with form.get() if (! form.containsField(colname_in_form)) { if (warn) warn(form, colname_in_form, suffix, prefix, map); return list; } field = form.get(colname_in_form); list = new ArrayList(); list.add(field); } return list; } private static final void warn(Form form, String name, String suffix, String prefix, Map map) { log.warn(form.getName(),": No automatic validators will be added for Field [",name,"]. This field does not exist in the front-end form. (this could be normal). suffix=["+suffix+"] prefix=["+prefix+"] map=", map); } /** Validates the bean before saving it to the database. This method is called internally by the {@link save()} method before saving a new bean (i.e., inserting a new row) to the database. <p> The validation is somewhat basic and there can exist many constraints and conditions on the database that might results in a insert/update error anyway. But by doing some basic validation against some known constraints, we save a needless trip to the database. We check to see that: <ol> <li><i>non-nullable and non auto-increment</i> columns [and with no default column value in the database] are modified (via a set method) since these columns must have a explicitly set value.</li> <li>for non-nullable columns that hold non-primitive (i.e., Object) java types, the modified value for non-nullable columns is a non-null object. </li> </ol> */ protected static void validateBeforeSaveNew(mollytest bean) throws ValidateException { boolean error = false; final StringBuffer buf = new StringBuffer("The following validation errors were found").append(IOUtil.LINE_SEP); //[uid]=>is not nullable but has a default column value [nextval('mollytest_uid_seq'::regclass)], no modification check necessary, skipping... //[name]=>nullable, no modification check necessary, skipping... //[email]=>nullable, no modification check necessary, skipping... //[password]=>nullable, no modification check necessary, skipping... //[created_on]=>nullable, no modification check necessary, skipping... //[is_active]=>nullable, no modification check necessary, skipping... if (error) { throw new ValidateException(buf.toString()); } } /** Validates the bean before saving it to the database. This method is called internally by the {@link save()} method before updating an existing bean (i.e., updating a row) in the database. <p> For <i>each modified column</i>, if that column is non-nullable in the database, then it must have a non-null value in the bean before it is saved. This check is only done for fields of <i>non</i>-primitive (Object) java types. [There is no way to ensure a non-null value for <i>primitive</i> types since all values [including 0] are non-null for those types]. */ protected static void validateBeforeSaveUpdate(mollytest bean) throws ValidateException { boolean error = false; final StringBuffer buf = new StringBuffer("The following validation errors were found").append(IOUtil.LINE_SEP); //[uid]=>auto-increment, no modification check necessary, skipping... //[name]=>nullable, no modification check necessary, skipping... //[email]=>nullable, no modification check necessary, skipping... //[password]=>nullable, no modification check necessary, skipping... //[created_on]=>nullable, no modification check necessary, skipping... //[is_active]=>nullable, no modification check necessary, skipping... if (error) { throw new ValidateException(buf.toString()); } } }