Class alltypesMgr

java.lang.Object
fc.jdbc.dbo.DBOMgr
fc.jdbc.dbo.generated.alltypesMgr
public final class alltypesMgr extends DBOMgr
Manages various operations on the alltypes table.

Most methods of this class take a 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 IllegalArgumentException if the specified connection object is null.

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.

  • Method Details

    • getAll

      public static List getAll(Connection con, String clause) throws SQLException
      Returns all rows in the table. Use with care for large tables since this method can result in VM out of memory errors.

      This method also takes an optional (can be null) clause parameter which is sent as is to the database. For example, a clause can be: 

      order by some_column_name
      Returns:
      a list containing alltypes objects or an empty list if there are no rows in the database
      Throws:
      SQLException

    • getAll

      public static List getAll(Connection con) throws ValidateException, SQLException
      Convenience method that invokes
      invalid reference 
      getAll
      with an empty additional clause.
      Throws:
      ValidateException
      SQLException

    • getLimited

      public static List getLimited(Connection con, String order_clause, int limit, int offset) throws SQLException
      Returns all rows in the table starting from some row number and limited by a certain number of rows after that starting row.

      This method takes a required (non-null) order_clause, since when using a limit clause, rows must be ordered for the limit to make sense. The clause should be of the form order by ...

      The limit 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).

      The offset 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.

      The sql-query generated by this method is database specific but will (typically) look like: 

      select <column_list> from <table> order by <clause> limit 5 offset 10
      Returns:
      a list containing alltypes objects or an empty list if there are no rows in the database
      Throws:
      SQLException

    • getByKey

      public static alltypes getByKey(Connection con, int id) throws SQLException
      Returns the row corresponding to the specified primary key(s) of this table or null if no row was found.

      This method uses a prepared statement and is safe from SQL injection attacks

      Throws:
      SQLException

    • getWhere

      public static List getWhere(Connection con, String where) throws SQLException
      Returns the rows returned by querying the table with the specified WHERE clause or an empty list if no rows were found. (note: the string "WHERE" does not have to be specified in the clause. It is added automatically by this method).

      Queries can use database functions such as: lower(), upper(), LIKE etc. For example: 

      alltypesMgr.getWhere("lower(col_a) = 'foo'")
//compares the lower case value of col_a with the string 'foo'

      The "where" clause is sent as-is to the database. SQL injection attacks are possible if it is created as-is from a untrusted source.

      Throws:
      IllegalArgumentException - if the specified where parameter is null
      SQLException

    • getUsing

      public static List getUsing(Connection con, alltypes bean, String clause) throws ValidateException, SQLException
      Returns the rows returned by querying the table with the value of the specified alltypes object or an empty list if no rows were found. As many fields in alltypes can be set as needed and the values of all set fields (including fields explicitly set to null) are then used to perform the query.

      This method is often convenient/safer than the getWhere method (because the getWhere method takes an arbitrary query string which has to be properly escaped by the user).

      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.

      Using a PreparedStatement: 

      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);
    }
      Using this method: 
      table_foo proto = new table_foo();
proto.set_x("somevalue"); //compile time safety
proto.set_y("othervalue");  //compile time safety
List beans = table_fooMgr.getUsing(proto);

      This method also takes an clause parameter which is sent as is to the database. For example, a clause can be: 

      List beans = table_fooMgr.getUsing(proto, "order by some_column_name");
      This clause is optional. Specify null to not use it at all. If the clause is specified, do NOT include the word WHERE. 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 WHERE. However, you may have to specify AND to add to the internal WHERE clause, if you have set any fields in the proto object. For example 
      List beans = table_fooMgr.getUsing(proto, "and bar = 5");

      Note: For a very 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...

      Throws:
      ValidateException
      SQLException

    • getUsing

      public static List getUsing(Connection con, alltypes bean) throws ValidateException, SQLException
      Convenience method that invokes getUsing with an empty clause parameter.
      Throws:
      ValidateException
      SQLException

    • getUsing

      public static List getUsing(Connection con, PreparedStatement ps) throws ValidateException, SQLException
      This is a convenience method that runs the specified prepared statement to perform an arbitrary query. For example: 
      PreparedStatement ps = con.prepareStatement(
    "select * from some_table where some_column = ?");
ps.setString(1, "foo");
List list = fooMgr.getUsing(con, ps);
for (int n = 0; n invalid input: '<' list.size(); n++) {
  sometable t = (sometable) list.get(n);
  //do something
  }
      The effect of the above is equivalent to the following (larger) block of code: 
      PreparedStatement ps = con.prepareStatement(
  "select * from sometable where some_column = ?"
  );
ps.setString(1, "foo");
ResultSet rs = ps.executeQuery();
List list = new ArrayList();
while (rs.next()) {
  list.add(sometableMgr.getFromRS(rs));
  }

for (int n = 0; n invalid input: '<' list.size(); n++) {
  sometable t = (sometable) list.get(n);
  //do something
  }
      Note: Just as with other getXXX methods, for large amounts of rows (say many thousands), it may be more efficient use and iterate through a JDBC result set directly.
      Throws:
      ValidateException
      SQLException

    • getUsing

      public static List getUsing(Connection con, NamedParamStatement ps) throws ValidateException, SQLException
      This is a convenience method that runs the specified
      invalid reference 
      NamedParamStatement
      to perform an arbitrary query. For example: 
      NamedParamStatement ps = queryReadeer.getQuery("somequery");
ps.setString("some_placeholder", "foo");
List list = fooMgr.getUsing(con, ps);
for (int n = 0; n invalid input: '<' list.size(); n++) {
  sometable t = (sometable) list.get(n);
  //do something
  }
      Note: Just as with other getXXX methods, for large amounts of rows (say many thousands), it may be more efficient use and iterate through a JDBC result set directly.
      Throws:
      ValidateException
      SQLException

    • columns

      public static String columns() throws SQLException
      Returns a comma delimited list of all columns in alltypes. These column names are fully qualified, i.e., they contain table name as a prefix to the column name. For example: 
      tablename.column1 AS tablename_column1, tablename.column2 AS tablename_column2 ...

      This list is suitable for placing in the column(s) clause of a select query, such as:

      Single table: select [column_list_A] from table_A
      Join: select [column_list_A], [column_list_B] from table_A, table_B
      The ResultSet returned by the query can be used directly or can be passed to the getFromRS method to convert it into a list of alltypes objects. If the query is a join across multiple tables, then the 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.
      Throws:
      SQLException

    • columns

      public static String columns(String prefix) throws SQLException
      Returns a comma delimited list of all columns in alltypes. These column names are prefix with the specified prefix, which corresponds to the table abbreviation used in the "AS" clause. For example: 
      xyz.column1 AS xyz_column1, xyz.column2 AS xyz_column2 ...

      This list is suitable for placing in the column(s) clause of a select query, such as:

      Note: the "." will automatically be appended between the prefix and column name so the prefix should not end with a "." or "_", etc

      Single table: select [column_list_A] from table_A AS xyz
      Join: select [column_list_A], [column_list_B] from table_A AS xyz, table_B AS zzz

      The ResultSet returned by the query can be used directly or can be passed to the getFromRS(String) method to convert it into a list of alltypes objects. If the query is a join across multiple tables, then the 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.
      Throws:
      SQLException

    • getFromRS

      public static alltypes getFromRS(ResultSet rs) throws SQLException
      Creates and returns a new alltypes 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 select *.. clause.

      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 fully qualified (prefixed with the table name) column names, such as: 

      tablename_column1
tablename_column2
...etc.

      For example:

      select foo.a AS foo_a, bar.a AS bar_a from foo, bar where foo.a = bar.a;
      The columns() method conveniently returns a list of column names in fully qualified format and is useful for this purpose.

      Note: This method will read the current row from the specified result set and will not move the result set pointer to the next row after the current row has been read. The result set should be appropriately positioned [via rs.next()] before calling this method.

      Returns:
      a new alltypes object populated with the contents of the next row from the result set or null if the ResultSet was empty.
      Throws:
      SQLException

    • getFromRS

      public static alltypes getFromRS(ResultSet rs, String prefix) throws SQLException
      Creates and returns a new alltypes 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 prefix argument. a select statement that specified all fields or used a select *.. clause.

      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 prefix (some arbitrary prefix) before column names, such as: 

      foo_column1
foo_column2
...etc.
      This prefix will typically be the same as the table abbreviation chosen via the AS clause. If the AS clause is not used, then it is simpler to use the getFromRS(java.sql.ResultSet) method instead

      Note: the "." will automatically be appended between the prefix and column name so the prefix should not end with a "." or "_", etc

      For example:

      select XXX.a AS XXX_a, YYY.a AS YYY_a from foo as XXX, bar as YYY where foo.a = bar.a;
      The columns() method conveniently returns a list of column names in fully qualified format and is useful for this purpose.

      Note: This method will read the current row from the specified result set and will not move the result set pointer to the next row after the current row has been read. The result set should be appropriately positioned [via rs.next()] before calling this method.

      Returns:
      a new alltypes object populated with the contents of the next row from the result set or null if the ResultSet was empty.
      Throws:
      SQLException

    • getFromRS1Table

      public static alltypes getFromRS1Table(ResultSet rs) throws SQLException
      Creates and returns a new alltypes object that represents a row from the specified ResultSet. For this method to work properly, the specified ResultSet should contain all (typically via select * ) of the column names of table.alltypes.

      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:

      select a, b, c, c*2 from foo where a = 1;

      This method will expect columns to be called a, b, c (no column aliases) in the returned result set. In this example, there is only one table foo so qualifying the column names, like foo.a as foo_a is not necessary). Also note, for this method to work properly, the column list

      select a, b, c ...
      should be complete, i.e., contain at least all the columns of this table (additional 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
      select * ....

      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

      See getFromRS(java.sql.ResultSet) which is more useful when writing a JDBC query that uses multiple table joins.

      Note: This method will read the current row from the specified result set and will not move the result set pointer to the next row after the current row has been read. The result set should be appropriately positioned [via rs.next()] before calling this method.

      Returns:
      a new alltypes object populated with the contents of the next row from the result set or null if the ResultSet was empty.
      Throws:
      SQLException

    • save

      public static int save(Connection con, alltypes bean) throws ValidateException, SQLException, IOException
      Saves the specified object into the database. If the specified object was newly created, then it is insert'ed into the database, else (if it was retrieved earlier from the database) it is update'ed. (this can be overriden by the 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.

      NOTE 1: 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.

      NOTE 2: 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).

      Note 3: For various reasons/flexiblity, default database values for columns other than serial columns are not 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

      Returns:
      the number of rows inserted or updated (typically useful to see if an update succeeded)
      Throws:
      ValidateException - on a validation error
      SQLException - on some SQL/Database error
      IOException - by the available() method if/when setting a stream for longvar/text types

    • update

      public static int update(Connection con, alltypes bean, int id) throws ValidateException, SQLException, IOException
      Uses the specified object to update existing data in the database.

      Note, the save method automatically saves newly created objects as inserts in the database (and prior retrieved objects, when subsequently modified, are saved as updates).

      However, sometimes it is useful to create a new object and then use its data to update an existing row in the database. This method need only be called to save a newly created object as an update into the database (overriding the default action of saving new objects as inserts in the database).

      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.

      This method takes primary key(s) of alltypes as additional arguments and sets those in the specified bean before updating the database (this way the row to update can be uniquely identified).

      Returns:
      the number of rows that were updated (typically useful to see if an update succeeded)
      Throws:
      ValidateException - on a validation error
      SQLException - on some SQL/Database error
      IOException
      See Also:

    • delete

      public static void delete(Connection con, alltypes bean) throws SQLException
      Deletes this object from the database.

      NOTE 1: 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 deleteByKey or deleteWhere instead for arbitrary deletions.

      NOTE 2: 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.

      Throws:
      SQLException

    • deleteByKey

      public static void deleteByKey(Connection con, int id) throws SQLException
      Deletes the rows with the specified primary key(s) from the database.

      This method uses a prepared statement and is safe from SQL injection attacks

      Throws:
      SQLException

    • deleteUsing

      public static int deleteUsing(Connection con, alltypes bean, String clause) throws ValidateException, SQLException
      Returns the rows returned by querying the table with the contents of the specified instance of alltypes or null if no rows were found. As many fields in alltypes can be set as needed and the values of all set fields (including fields explicitly set to null) are then used to perform the query.

      Note, however that this method does use any primary key(s). If the primary keys are known then one should use the deleteByKey method to delete the data instead.

      Likewise, to delete a previously fetched row, use the 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.

      This method is often convenient/safer than the deleteWhere method (because the deleteWhere method takes an arbitrary query string which has to be properly escaped by the user).

      However, as a middle ground, this method also takes an clause parameter which is sent as is to the database. For example, a clause can be: 

      List beans = table_fooMgr.deleteUsing(proto, "xyx > 5");
      This clause is optional. Specify null to not use it at all. If the clause is specified, do NOT include the word WHERE.

      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.

      Using a PreparedStatement: 

      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();
      Using this method: 
      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.deleteUsing(proto);
      Returns:
      the number of rows deleted
      Throws:
      ValidateException
      SQLException

    • deleteUsing

      public static int deleteUsing(Connection con, alltypes bean) throws ValidateException, SQLException
      Convenience method that invokes getUsing with an empty clause parameter.
      Throws:
      ValidateException
      SQLException

    • deleteWhere

      public static int deleteWhere(Connection con, String where) throws SQLException
      Deletes the rows with the specified where clause.

      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. (note: the string "WHERE" does not have to be specified in the clause. It is added automatically by this method).

      Returns:
      the number of rows deleted by the database
      Throws:
      SQLException

    • count

      public static int count(Connection con) throws SQLException
      Returns the count of all rows in the table.

      Note: This may be an expensive operation in MVCC databases like PostgresQL, Oracle and others, where an entire non-optimized table scan may 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.

      Throws:
      SQLException

    • countWhere

      public static int countWhere(Connection con, String where) throws SQLException
      Returns the count of rows in the table using the specified where clause. (note: the string "WHERE" does not have to be specified in the clause. It is added automatically by this method).
      Throws:
      IllegalArgumentException - if the where paramater was null
      SQLException

    • countUsing

      public static int countUsing(Connection con, alltypes bean, String clause) throws ValidateException, SQLException
      Returns the rows count by querying the table with the contents of the specified instance of alltypes As many fields in alltypes can be set as needed and the values of all set fields (including fields explicitly set to null) are then used to perform the query. If the primary key(s) are known then one can also use the exists(Connection, int) method to see if that row exists in the database.

      This method is often convenient/safer than the countWhere method (because the countWhere method takes an arbitrary query string which has to be properly escaped by the user).

      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.

      Using a PreparedStatement: 

      String foo = "select count(*) 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);
      Using this method: 
      table_foo proto = new table_foo();
proto.set_x("somevalue"); //compile time safety
proto.set_y("othervalue");  //compile time safety
int count = table_fooMgr.countUsing(proto);
      The clause is optional. Specify null to not use it at all. If the clause is specified, do NOT include the word WHERE.
      Throws:
      ValidateException
      SQLException

    • countUsing

      public static int countUsing(Connection con, alltypes bean) throws ValidateException, SQLException
      Convenience method that invokes getUsing with an empty clause parameter.
      Throws:
      ValidateException
      SQLException

    • exists

      public static boolean exists(Connection con, int id) throws SQLException
      Returns true if a row with the specified primary keys exists, false otherwise.

      This method uses a prepared statement and is safe from SQL injection attacks

      Throws:
      SQLException

    • existsUsing

      public static boolean existsUsing(Connection con, alltypes bean) throws ValidateException, SQLException
      A thin wrapper around getUsing that returns false if no rows are returned, true otherwise.
      Throws:
      ValidateException
      SQLException

    • stats

      public static String stats()
      Returns usage statistics for this class

    • toString

      public String toString()
      Overrides:
      toString in class Object

    • addValidators

      public static void addValidators(Form form, String prefix, String suffix, Map map)
      Creates and attaches validators for all the fields in the specified Form. These fields should have the same name in the form as in alltypes. If this is not the case, then the then the differences can be specifed as follows.

      with a prefix
      (prefix + alltypes column) should equal form fieldname
      with a suffix
      (alltypes column + suffix) should equal form fieldname
      with both a prefix/suffix
      (prefix + alltypes + suffix) should equal form fieldname
      with a arbitrary map
      [key] alltypes column -> [value] form fieldname If a map is specified, then the prefix/suffix are not used.

      These validators are for database constraints such as nullability invalid input: '&' column length. These validators save a lot of grunt-work in adding such schema constraints to the front-end Form.

      However, business and other validation constraints still need to be manually added to the application code/front-end forms as/when needed.

      The following table shows the kind of validators added by this method

      Database SQL Type Nullablevalidator Length validator Digits only input validator (VText.allowIntegersOnly())
      CHAR, VARCHAR Yes (maximum length constraint).
      This only applies to form fields that are subclasses of MaxSizable       		-NO-
      TINYINT, MEDIUMINT, INT, BIGINT (integral types) Yes -NO- Yes to integer columns displayed using form fields that are subclasses of AbstractText
      Note: not added non-integral number types such as FLOAT, REAL, DOUBLE, NUMERIC/DECIMAL
      All other SQL types Yes -NO-

      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 toString 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, then simply enoough, do NOT invoke this method and simply add validators by hand. In particular, do not add automatic validators for tables in which a row is optional but if some column is filled in the front end form, then all columns must be filled.

      Parameters:
      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
      prefix - an optional (null allowed) prefix to this table's column name with which the corresponding column was added to the form. A * specifies all possible prefixes
      suffix - an optional suffix (null allowed) to this table's column name with which the corresponding column was added to the form. A * specifies all possible suffixes
      map - an optional map (null allowed) that maps this table's column name with which the corresponding column was added to the form. [key] table's column_name -> [value] form's fieldname

    • addValidators

      public static void addValidators(Form form, Map map)
      Convenience method that calls addValidators(Form, String, String, Map) with a null prefix/suffix and the specified map

    • addValidators

      public static void addValidators(Form form)
      Convenience method that calls addValidators(Form, String, String, Map) with a null prefix/suffix/map

    • addValidators

      public static void addValidators(Form form, String prefix)
      Convenience method that calls addValidators(Form, String, String, map) with the specified prefix and a null suffix/map

    • addIfFilledValidators

      public static void addIfFilledValidators(Form form, String prefix)
      Validates a form field if 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