org.sormula
Class Table<R>

java.lang.Object
  org.sormula.Table<R>

Type Parameters:

R - type of row objects

All Implemented Interfaces:

TypeTranslatorMap

Direct Known Subclasses:

OperationTable

public class Table<R>
extends java.lang.Object
implements TypeTranslatorMap

A table within a sql database. Contains a RowTranslator for reading/writing data form/to database and contains methods for common input/output operations. The RowTranslator is created based upon fields within the row class of type R and from annotations for the class. All common input/output methods use the primary key defined by Column.primaryKey() annotation on class R.

Example 1 - Get table from database:

 Connection connection = ... // jdbc connection
 Database database = new Database(connection);
 Table<MyRow> table = database.getTable(MyRow.class);
 table.selectAll();
 

Example 2 - Instantiate table:

 Connection connection = ... // jdbc connection
 Database database = new Database(connection);
 Table<MyRow> table = new Table<MyRow>(database, MyRow.class);
 table.selectAll();
 

Example 3 - Instantiate a table subclass:

 public class MyCustomTable extends Table<MyRow>
 {
     public MyCustomTable(Connection connection)
     {
         super(connection, MyRow.class);
     }
 
     ...
 }
 
 Connection connection = ... // jdbc connection
 Database database = new Database(connection);
 MyCustomTable table = new MyCustomTable(database);
 table.selectAll();
 

Since:

1.0

Author:

Jeff Miller

Constructor Summary

Table(Database database, java.lang.Class<R> rowClass)
Constructs for a database and the class that used for row objects.

Method Summary

int	delete(java.lang.Object... parameters) Deletes by primary key.
int	delete(R row) Deletes by primary key.
int	deleteAll() Deletes all rows in table.
int	deleteAll(java.util.Collection<R> rows) Deletes many rows by primary key.
int	deleteAllBatch(java.util.Collection<R> rows) Deletes many rows by primary key in batch mode.
Database	getDatabase() Gets the database supplied in constructor.
java.util.List<java.lang.reflect.Field>	getLazySelectCascadeFields() Gets fields for table record class that are annotated with SelectCascade.lazy() true.
NameTranslator	getNameTranslator() Deprecated.
java.util.List<? extends NameTranslator>	getNameTranslators() Gets the name translators used by translateName(String).
java.lang.String	getQualifiedTableName() Gets table name used in sql statements with optional schema prefix if necessary.
java.lang.Class<R>	getRowClass()
RowTranslator<R>	getRowTranslator() Gets the row translator for converting row values to/from sql parameters.
java.lang.String	getTableName() Gets the table name used in sql statements.
TypeTranslator<?>	getTypeTranslator(java.lang.Class<?> typeClass) Gets the translator to use to convert a value to a prepared statement or to convert from a result set.
protected java.util.List<? extends NameTranslator>	initNameTranslators(Row rowAnnotation) Initialize all name translators annotated on table.
protected RowTranslator<R>	initRowTranslator() Creates a RowTranslator for use by this table.
protected java.lang.String	initTableName(Row rowAnnotation) Initializes table name from row annotation.
protected void	initTypeTranslatorMap() Initializes type translators for table.
int	insert(R row) Inserts one row into table.
int	insertAll(java.util.Collection<R> rows) Inserts collection of rows.
int	insertAllBatch(java.util.Collection<R> rows) Inserts a collection of rows in batch mode.
R	newRow() Creates new instance of row.
void	putTypeTranslator(java.lang.Class<?> typeClass, TypeTranslator<?> typeTranslator) Overrides translator defined in Database for all operations on this table.
void	putTypeTranslator(java.lang.String typeClassName, TypeTranslator<?> typeTranslator) Same as putTypeTranslator(Class, TypeTranslator) but uses class name.
int	save(R row) Uses SaveOperation to update an existing row or insert row if it is not already in database.
int	saveAll(java.util.Collection<R> rows) Uses SaveOperation to update an existing rows or insert rows if they are not already in database.
R	select(java.lang.Object... primaryKeys) Selects one row in table using primary key.
java.util.List<R>	selectAll() Selects all rows in table.
java.util.List<R>	selectAllCustom(java.lang.String customSql, java.lang.Object... parameters) Select list of rows using custom sql.
java.util.List<R>	selectAllWhere(java.lang.String whereConditionName, java.lang.Object... parameters) Selects list of rows for where condition and parameters.
java.util.List<R>	selectAllWhereOrdered(java.lang.String whereConditionName, java.lang.String orderByName, java.lang.Object... parameters) Selects list of rows for where condition and parameters.
<T> T	selectAvg(java.lang.String expression) Selects average value.
<T> T	selectAvg(java.lang.String expression, java.lang.String whereConditionName, java.lang.Object... parameters) Selects average value.
int	selectCount() Gets count of all rows in table.
<T> T	selectCount(java.lang.String expression) Selects count of rows.
int	selectCount(java.lang.String whereConditionName, java.lang.Object... parameters) Selects count for a subset of rows.
<T> T	selectCount(java.lang.String expression, java.lang.String whereConditionName, java.lang.Object... parameters) Selects count of rows.
R	selectCustom(java.lang.String customSql, java.lang.Object... parameters) Selects one row using custom sql.
<T> T	selectMax(java.lang.String expression) Selects maximum value.
<T> T	selectMax(java.lang.String expression, java.lang.String whereConditionName, java.lang.Object... parameters) Selects maximum value.
<T> T	selectMin(java.lang.String expression) Selects minimum value.
<T> T	selectMin(java.lang.String expression, java.lang.String whereConditionName, java.lang.Object... parameters) Selects minimum value.
<T> T	selectSum(java.lang.String expression) Selects sum.
<T> T	selectSum(java.lang.String expression, java.lang.String whereConditionName, java.lang.Object... parameters) Selects sum.
R	selectWhere(java.lang.String whereConditionName, java.lang.Object... parameters) Selects one row for where condition and parameters.
void	setTableName(java.lang.String tableName) Sets the table name to use in sql statements.
java.lang.String	translateName(java.lang.String javaName) Converts a Java class or field name to corresponding SQL name.
int	update(R row) Updates one row in table by primary key.
int	updateAll(java.util.Collection<R> rows) Updates collection of rows using primary key.
int	updateAllBatch(java.util.Collection<R> rows) Updates collection of rows using primary key in batch mode.

Methods inherited from class java.lang.Object

clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Constructor Detail

Table

public Table(Database database,
             java.lang.Class<R> rowClass)
      throws SormulaException

Constructs for a database and the class that used for row objects. Creates a RowTranslator for mapping row objects to/from database.

Parameters:

database - database for this table

rowClass - row objects are of this type

Throws:

SormulaException - if error

Method Detail

initTypeTranslatorMap

protected void initTypeTranslatorMap()
                              throws SormulaException

Initializes type translators for table. Invoked by the constructor. Override to add custom types via putTypeTranslator(Class, TypeTranslator).

Throws:

SormulaException - if error

Since:

1.9.2 and 2.3.2

initNameTranslators

protected java.util.List<? extends NameTranslator> initNameTranslators(Row rowAnnotation)

Initialize all name translators annotated on table. Subclasses of Table may contain NameTranslator annotations instead of annotating row class.

Parameters:

rowAnnotation - the annotation that defines name translator(s)

Returns:

list of name translators; empty list if none

Since:

1.8 and 2.2

initTableName

protected java.lang.String initTableName(Row rowAnnotation)

Initializes table name from row annotation. If rowAnnotation is not null, then Table name is Row.tableName(). If no name is supplied, then table name will be row class simple name, Class.getSimpleName().

Parameters:

rowAnnotation - row annotation on table or null if none

Returns:

name to use in SQL statements for table

initRowTranslator

protected RowTranslator<R> initRowTranslator()
                                      throws TranslatorException

Creates a RowTranslator for use by this table. Invoked by constructor.

Returns:

row translator

Throws:

TranslatorException - if error

getDatabase

public Database getDatabase()

Gets the database supplied in constructor.

Returns:

database for this table

getRowClass

public java.lang.Class<R> getRowClass()

Returns:

row class supplied in constructor

Since:

1.6 and 2.0

getTableName

public java.lang.String getTableName()

Gets the table name used in sql statements.

Returns:

table name (without schema prefix)

setTableName

public void setTableName(java.lang.String tableName)

Sets the table name to use in sql statements. The default is set in the constructor and based upon the row class name and NameTranslator if specified. Use this to override the default.

Parameters:

tableName - table name (without schema prefix)

getQualifiedTableName

public java.lang.String getQualifiedTableName()

Gets table name used in sql statements with optional schema prefix if necessary.

Returns:

schema.tablename if schema is not empty string; otherwise tablename

getNameTranslator

@Deprecated
public NameTranslator getNameTranslator()

Deprecated.

Gets translator defined by Row.nameTranslator(). Use getNameTranslators() instead of this method.

Returns:

translator for converting java names to sql table and column names

getNameTranslators

public java.util.List<? extends NameTranslator> getNameTranslators()

Gets the name translators used by translateName(String).

Returns:

list of name translators; empty list if none

Since:

1.8 and 2.2

translateName

public java.lang.String translateName(java.lang.String javaName)

Converts a Java class or field name to corresponding SQL name. Invokes NameTranslator.translate(String, Class) for all name translators in the order that they are defined.

Returns:

SQL name that corresponds to Java class or field name

Since:

1.8 and 2.2

See Also:

Row.nameTranslators(), Database.addNameTranslatorClass(Class)

getRowTranslator

public RowTranslator<R> getRowTranslator()

Gets the row translator for converting row values to/from sql parameters.

Returns:

row translator for this table

getLazySelectCascadeFields

public java.util.List<java.lang.reflect.Field> getLazySelectCascadeFields()

Gets fields for table record class that are annotated with SelectCascade.lazy() true.

Returns:

list of fields

Since:

1.8 and 2.2

newRow

public R newRow()
         throws SormulaException

Creates new instance of row. Typically used by select operations for each row that is read from result set.

Returns:

new instance of row created with zero-arg constructor

Throws:

SormulaException - if error

Since:

1.7 and 2.1

putTypeTranslator

public void putTypeTranslator(java.lang.Class<?> typeClass,
                              TypeTranslator<?> typeTranslator)

Overrides translator defined in Database for all operations on this table. See Database.putTypeTranslator(Class, TypeTranslator) for an explanation of translators.

Specified by:

putTypeTranslator in interface TypeTranslatorMap

Parameters:

typeClass - class that translator operates upon

typeTranslator - to use for typeClass

Since:

1.6 and 2.0

putTypeTranslator

public void putTypeTranslator(java.lang.String typeClassName,
                              TypeTranslator<?> typeTranslator)

Same as putTypeTranslator(Class, TypeTranslator) but uses class name. Usefull for adding primative types like "int", "boolean", "float", etc.

Specified by:

putTypeTranslator in interface TypeTranslatorMap

Parameters:

typeClassName - class name that translator operates upon

typeTranslator - translator to use for typeClass

Since:

1.9.2 and 2.3.2

getTypeTranslator

public TypeTranslator<?> getTypeTranslator(java.lang.Class<?> typeClass)

Gets the translator to use to convert a value to a prepared statement or to convert from a result set. If none are set for this table, then translator is obtained from Database.getTypeTranslator(Class). See Database.getTypeTranslator(Class) for more details.

Specified by:

getTypeTranslator in interface TypeTranslatorMap

Parameters:

typeClass - class that translator operates upon

Returns:

translator to use for typeClass

Since:

1.6 and 2.0

selectAll

public java.util.List<R> selectAll()
                            throws SormulaException

Selects all rows in table.

Example:

 Database database = ...
 Table<Order> table = database.getTable(Order.class);
 List<Order> orders = table.selectAll();
 

Returns:

list of all rows; empty list if none found

Throws:

SormulaException - if error

select

public R select(java.lang.Object... primaryKeys)
         throws SormulaException

Selects one row in table using primary key. Primary key must be defined by one or more Column.primaryKey() annotations.

Example:

 Database database = ...
 Table<Order> table = database.getTable(Order.class);
 int orderNumber = 123456; // primary key
 Order order = table.select(orderNumber);
 

Parameters:

primaryKeys - primary key values to use for select (must be in same order as primary key columns appear with row class)

Returns:

row or null if none found

Throws:

SormulaException - if error

selectWhere

public R selectWhere(java.lang.String whereConditionName,
                     java.lang.Object... parameters)
              throws SormulaException

Selects one row for where condition and parameters.

Parameters:

whereConditionName - name of where condition to use; empty string to select arbitrary row in table

parameters - parameter values for where condition

Returns:

row for where condition and parameters; null if none found

Throws:

SormulaException - if error

Since:

1.7 and 2.1

selectAllWhere

public java.util.List<R> selectAllWhere(java.lang.String whereConditionName,
                                        java.lang.Object... parameters)
                                 throws SormulaException

Selects list of rows for where condition and parameters.

Parameters:

whereConditionName - name of where condition to use; empty string to select all rows in table

parameters - parameter values for where condition

Returns:

rows for where condition and parameters; empty list if none found

Throws:

SormulaException - if error

Since:

1.7 and 2.1

selectAllWhereOrdered

public java.util.List<R> selectAllWhereOrdered(java.lang.String whereConditionName,
                                               java.lang.String orderByName,
                                               java.lang.Object... parameters)
                                        throws SormulaException

Selects list of rows for where condition and parameters.

Parameters:

whereConditionName - name of where condition to use; empty string to select all rows in table

orderByName - name of order phrase to use as defined in OrderBy.name()

parameters - parameter values for where condition

Returns:

rows for where condition and parameters; empty list if none found

Throws:

SormulaException - if error

Since:

1.7 and 2.1

selectAllCustom

public java.util.List<R> selectAllCustom(java.lang.String customSql,
                                         java.lang.Object... parameters)
                                  throws SormulaException

Select list of rows using custom sql.

Example:

 Database database = ...
 Table<Order> table = database.getTable(Order.class);
 List<Order> orders = table.selectAllCustom("where orderdate >= '2011-01-01'");
 

Parameters:

customSql - custom sql to be appended to base sql (for example, "where somecolumn=?")

parameters - parameter value to be set in customSql

Returns:

list of rows selected; empty list if none found

Throws:

SormulaException - if error

selectCustom

public R selectCustom(java.lang.String customSql,
                      java.lang.Object... parameters)
               throws SormulaException

Selects one row using custom sql.

Example:

 Database database = ...
 Table<Order> table = database.getTable(Order.class);
 Order order = table.selectCustom("where orderdate >= '2011-01-01'");
 

Parameters:

customSql - custom sql to be appended to base sql (for example, "where somecolumn=?")

parameters - parameter value to be set in customSql

Returns:

first row found or null if no rows selected

Throws:

SormulaException - if error

selectCount

public int selectCount()
                throws SormulaException

Gets count of all rows in table. This method is from older versions and is kept for backward compatiblity. It is equivalent to selectCount("*", "").

Returns:

count of all rows in table

Throws:

SormulaException - if error

selectCount

public int selectCount(java.lang.String whereConditionName,
                       java.lang.Object... parameters)
                throws SormulaException

Selects count for a subset of rows. This method is from older versions and is kept for backward compatiblity. It is equivalent to selectCount("*", whereConditionName, parameters).

Example:

 Database database = ...
 Table<Order> table = database.getTable(Order.class);
  
 // quantityExceeds is the name of a Where annotation on Order that filters quantity >= ?
 int bigOrderCount = table.selectCount("quanityExceeds", 100);
 

Parameters:

whereConditionName - name of where condition to use; empty string to count all rows in table

parameters - parameter values for where condition

Returns:

count of all rows in table

Throws:

SormulaException - if error

selectCount

public <T> T selectCount(java.lang.String expression)
              throws SormulaException

Selects count of rows.

The data type returned from database is the same type as the expression. For example, if expression is a column, then the returned type is the same type as column. If expression is "*", then return types are database dependent.

Type Parameters:

T - aggregate result type

Parameters:

expression - expression to use as parameter to function; typically it is the name of a column

Returns:

count of rows for expression

Throws:

SormulaException - if error

selectCount

public <T> T selectCount(java.lang.String expression,
                         java.lang.String whereConditionName,
                         java.lang.Object... parameters)
              throws SormulaException

Selects count of rows.

The data type returned from database is the same type as the expression. For example, if expression is a column, then the returned type is the same type as column. If expression is "*", then return types are database dependent.

Type Parameters:

T - aggregate result type

Parameters:

expression - expression to use as parameter to function; typically it is the name of a column

whereConditionName - name of where condition to use; empty string to count all rows in table

parameters - parameter values for where condition

Returns:

count of rows for expression and where condition

Throws:

SormulaException - if error

selectMin

public <T> T selectMin(java.lang.String expression)
            throws SormulaException

Selects minimum value.

Type Parameters:

T - aggregate result type

Parameters:

expression - expression to use as parameter to function; typically it is the name of a column

Returns:

minimum value for expression

Throws:

SormulaException - if error

selectMin

public <T> T selectMin(java.lang.String expression,
                       java.lang.String whereConditionName,
                       java.lang.Object... parameters)
            throws SormulaException

Selects minimum value.

Type Parameters:

T - aggregate result type

Parameters:

expression - expression to use as parameter to function; typically it is the name of a column

whereConditionName - name of where condition to use; empty string to count all rows in table

parameters - parameter values for where condition

Returns:

minimum value for expression and where condition

Throws:

SormulaException - if error

selectMax

public <T> T selectMax(java.lang.String expression)
            throws SormulaException

Selects maximum value.

Type Parameters:

T - aggregate result type

Parameters:

expression - expression to use as parameter to function; typically it is the name of a column

Returns:

maximum value for expression

Throws:

SormulaException - if error

selectMax

public <T> T selectMax(java.lang.String expression,
                       java.lang.String whereConditionName,
                       java.lang.Object... parameters)
            throws SormulaException

Selects maximum value.

Type Parameters:

T - aggregate result type

Parameters:

expression - expression to use as parameter to function; typically it is the name of a column

whereConditionName - name of where condition to use; empty string to count all rows in table

parameters - parameter values for where condition

Returns:

maximum value for expression and where condition

Throws:

SormulaException - if error

selectAvg

public <T> T selectAvg(java.lang.String expression)
            throws SormulaException

Selects average value.

Type Parameters:

T - aggregate result type

Parameters:

expression - expression to use as parameter to function; typically it is the name of a column

Returns:

average value for expression

Throws:

SormulaException - if error

selectAvg

public <T> T selectAvg(java.lang.String expression,
                       java.lang.String whereConditionName,
                       java.lang.Object... parameters)
            throws SormulaException

Selects average value.

Type Parameters:

T - aggregate result type

Parameters:

expression - expression to use as parameter to function; typically it is the name of a column

whereConditionName - name of where condition to use; empty string to count all rows in table

parameters - parameter values for where condition

Returns:

average value for expression and where condition

Throws:

SormulaException - if error

selectSum

public <T> T selectSum(java.lang.String expression)
            throws SormulaException

Selects sum.

Type Parameters:

T - aggregate result type

Parameters:

expression - expression to use as parameter to function; typically it is the name of a column

Returns:

sum for expression

Throws:

SormulaException - if error

Since:

1.7 and 2.1

selectSum

public <T> T selectSum(java.lang.String expression,
                       java.lang.String whereConditionName,
                       java.lang.Object... parameters)
            throws SormulaException

Selects sum.

Type Parameters:

T - aggregate result type

Parameters:

expression - expression to use as parameter to function; typically it is the name of a column

whereConditionName - name of where condition to use; empty string to count all rows in table

parameters - parameter values for where condition

Returns:

sum for expression and where condition

Throws:

SormulaException - if error

Since:

1.7 and 2.1

insert

public int insert(R row)
           throws SormulaException

Inserts one row into table.

Example:

 Database database = ...
 Table<Student> table = database.getTable(Student.class);
 Student s = new Student();
 s.setId(1234);
 s.setFirstName("Jeff");
 s.setLastName("Miller");
 s.setGraduationDate(new Date(System.currentTimeMillis()));
 table.insert(s);
 

Parameters:

row - row to insert

Returns:

count of rows affected

Throws:

SormulaException - if error

insertAll

public int insertAll(java.util.Collection<R> rows)
              throws SormulaException

Inserts collection of rows.

Example:

 Database database = ...
 Table<Student> table = database.getTable(Student.class);
 ArrayList<Student> list = new ArrayList<Student>();
 list.add(s1);
 list.add(s2);
 list.add(s3);
 table.insertAll(list);
 

Parameters:

rows - rows to insert

Returns:

count of rows affected

Throws:

SormulaException - if error

insertAllBatch

public int insertAllBatch(java.util.Collection<R> rows)
                   throws SormulaException

Inserts a collection of rows in batch mode. See limitations about batch inserts in ModifyOperation.setBatch(boolean).

Parameters:

rows - rows to insert

Returns:

count of rows affected

Throws:

SormulaException - if error

Since:

1.9 and 2.3

update

public int update(R row)
           throws SormulaException

Updates one row in table by primary key. Primary key must be defined by one or more Column.primaryKey() annotations.

Example:

 Database database = ...
 Table<Student> table = database.getTable(Student.class);
 Student s = table.select(id);
 s.setGraduationDate(...);
 table.update(s);
 

Parameters:

row - row to update

Returns:

count of rows affected

Throws:

SormulaException - if error

updateAll

public int updateAll(java.util.Collection<R> rows)
              throws SormulaException

Updates collection of rows using primary key. Primary key must be defined by one or more Column.primaryKey() annotations.

Example:

 Database database = ...
 Table<Student> table = database.getTable(Student.class);
 List<Student> list = table.selectAll();
 for (Student s: list)
     s.setGraduationDate(...);
   
 table.updateAll(list);
 

Parameters:

rows - rows to update

Returns:

count of rows affected

Throws:

SormulaException - if error

updateAllBatch

public int updateAllBatch(java.util.Collection<R> rows)
                   throws SormulaException

Updates collection of rows using primary key in batch mode. Primary key must be defined by one or more Column.primaryKey() annotations. See limitations about batch updates in ModifyOperation.setBatch(boolean).

Parameters:

rows - rows to update

Returns:

count of rows affected

Throws:

SormulaException - if error

Since:

1.9 and 2.3

delete

public int delete(java.lang.Object... parameters)
           throws SormulaException

Deletes by primary key. Primary key must be defined by one or more Column.primaryKey() annotations.

Example:

 Database database = ...
 Table<Student> table = database.getTable(Student.class);
 table.delete(1234); // deletes student with id of 1234
 

Parameters:

parameters - where condition values to use for delete (must be in same order as primary key fields appear with row class)

Returns:

count of rows affected

Throws:

SormulaException - if error

delete

public int delete(R row)
           throws SormulaException

Deletes by primary key. Primary key must be defined by one or more Column.primaryKey() annotations.

Example:

 Database database = ...
 Table<Student> table = database.getTable(Student.class);
 // delete student with id of 1234
 Student s = table.select(1234);
 table.delete(s);
 

Parameters:

row - get primary key values from this row

Returns:

count of rows affected

Throws:

SormulaException - if error

deleteAll

public int deleteAll(java.util.Collection<R> rows)
              throws SormulaException

Deletes many rows by primary key. Primary key must be defined by one or more Column.primaryKey() annotations.

Example:

 Database database = ...
 Table<Student> table = database.getTable(Student.class);
 List<Student> list = getSomeStudents();
 table.deleteAll(list);
 

Parameters:

rows - get primary key values from each row in this collection

Returns:

count of rows affected

Throws:

SormulaException - if error

deleteAllBatch

public int deleteAllBatch(java.util.Collection<R> rows)
                   throws SormulaException

Deletes many rows by primary key in batch mode. Primary key must be defined by one or more Column.primaryKey() annotations. See limitations about batch deletes in ModifyOperation.setBatch(boolean).

Parameters:

rows - get primary key values from each row in this collection

Returns:

count of rows affected

Throws:

SormulaException - if error

Since:

1.9 and 2.3

deleteAll

public int deleteAll()
              throws SormulaException

Deletes all rows in table.

Example:

 Database database = ...
 Table<Student> table = database.getTable(Student.class);
 table.deleteAll();
 

Returns:

count of rows affected

Throws:

SormulaException - if error

save

public int save(R row)
         throws SormulaException

Uses SaveOperation to update an existing row or insert row if it is not already in database.

Parameters:

row - row to save

Returns:

count of rows affected

Throws:

SormulaException - if error

saveAll

public int saveAll(java.util.Collection<R> rows)
            throws SormulaException

Uses SaveOperation to update an existing rows or insert rows if they are not already in database.

Parameters:

rows - collection of new and/or existing rows to save (may be mixture of new and existing)

Returns:

count of rows affected

Throws:

SormulaException - if error