nz.co.gregs.dbvolution

Class DBTable<E extends DBRow>

java.lang.Object

nz.co.gregs.dbvolution.DBTable<E>

Type Parameters:

E - DBRow type

public class DBTable<E extends DBRow>
extends Object

DBTable provides features for making simple queries on the database.

If your query only references one table, DBTable makes it easy to get the rows from that table.

Use getDBTable from DBDatabase to retrieve an instance for particular DBRow subclass.

DBTable and DBQuery are very similar but there are important differences. In particular DBTable uses a simple List<<E extends DBRow>> rather than List<DBQueryRow>. Additionally DBTable results are always fresh: the internal query is rerun each time a get* method is called.

DBTable is a quick and easy API for targeted data retrieval; for more complex needs, use DBQuery.

Support DBvolution at Patreon

Author:

Gregory Graham

Constructor Summary

Constructors

Modifier	Constructor and Description
protected	DBTable(DBDatabase database, E exampleRow) Default constructor for DBTable, used by DBDatabase to create instances.

Method Summary

Methods

Modifier and Type	Method and Description
DBTable<E>	clearRowLimit() Removes the limit set with setRowLimit(int).
DBTable<E>	clearSortOrder() Removes the sort order add with setSortOrder(nz.co.gregs.dbvolution.columns.ColumnProvider...).
DBTable<E>	clearTimeout()
void	compare(DBTable<E> secondTable) Compares 2 tables, presumably from different criteria or databases prints the differences to System.out Should be updated to return the varying rows somehow
Long	count() Count the rows on the database without retrieving the rows.
DBActionList	delete(Collection<E> oldRows) Deletes the rows from the database permanently.
DBActionList	delete(E... oldRows) Deletes the rows from the database permanently.
List<E>	getAllRows() Gets All Rows of the table from the database
<A> List<A>	getDistinctValuesOfColumn(A fieldOfProvidedRow) Returns the unique values for the column in the database.
E	getFirstRow() Returns the first row of the table
static <E extends DBRow> DBTable<E>	getInstance(DBDatabase database, E example) Factory method to create a DBTable.
E	getOnlyRow() Returns the first row and only row of the table.
E	getOnlyRowByExample(E example) Sets the exemplar to the given example and retrieves the only appropriate record.
List<Long>	getPrimaryKeysAsLong() Retrieves the rows for this table and returns the primary keys of the rows as Longs.
List<String>	getPrimaryKeysAsString() Retrieves the rows for this table and returns the primary keys of the rows as Strings.
List<E>	getRowsByExample(E example) Sets the example and retrieves all the appropriate records.
List<E>	getRowsByExample(E example, long expectedNumberOfRows) This method retrieves all the appropriate records, and throws an exception if the number of records differs from the required number.
List<E>	getRowsByPrimaryKey(Date pkValue) Retrieves the row (or rows in a bad database) that has the specified primary key.
List<E>	getRowsByPrimaryKey(Number pkValue) Retrieves the row (or rows in a bad database) that has the specified primary key.
List<E>	getRowsByPrimaryKey(String pkValue) Retrieves the row (or rows in a bad database) that has the specified primary key.
List<E>	getRowsForPage(Integer pageNumber) Retrieves that DBRows for the page supplied.
String	getSQLForCount() Returns the SQL query that will used to count the rows
String	getSQLForQuery() Generates and returns the actual SQL that will be used by getAllRows() now.
String	getSQLForQuery(DBRow exemplar) Generates and returns the actual SQL that will be used by getRowsByExample(nz.co.gregs.dbvolution.DBRow) now.
DBActionList	insert(Collection<E> newRows) Inserts DBRows into the database
DBActionList	insert(E... newRows) Inserts DBRows into the database.
void	print() Convenience method to print all the rows in the current collection Equivalent to: print(System.out)
void	print(PrintStream stream) the same as print() but allows you to specify the PrintStream required myTable.printAllRows(System.err);
void	printSQLForQuery()
DBTable<E>	setBlankQueryAllowed(boolean allow) Change the Default Setting of Disallowing Blank Queries
DBTable<E>	setQueryTimeout(int i)
DBTable<E>	setRawSQL(String rawQuery) Adds the specified raw SQL to the DBTable query.
DBTable<E>	setRowLimit(int rowLimit) Limit the query to only returning a certain number of rows
DBTable<E>	setSortOrder(ColumnProvider... sortColumns) Sets the sort order of properties (field and/or method) by the given property object references.
void	setToMatchAllConditions() Set the query to only return rows that match all conditions
void	setToMatchAnyCondition() Set the query to return rows that match any conditions
List<E>	toList() Synonym for getAllRows() Support DBvolution at Patreon
DBActionList	update(Collection<E> oldRows) Updates Lists of DBRows on the database
DBActionList	update(E oldRow) Updates the DBRow on the database.

Methods inherited from class java.lang.Object

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

Constructor Detail

DBTable

protected DBTable(DBDatabase database,
       E exampleRow)

Default constructor for DBTable, used by DBDatabase to create instances.

Parameters:

database - the database this DBTable instance is applicable too.

exampleRow - The row that this table is applicable too.

Method Detail

getInstance

public static <E extends DBRow> DBTable<E> getInstance(DBDatabase database,
                                       E example)

Factory method to create a DBTable.

The example will be copied to avoid unexpected changes of the results.

DBDatabase.getDBTable(nz.co.gregs.dbvolution.DBRow) is probably a better option.

Type Parameters:

E - DBRow type

Parameters:

database - database

example - example

Support DBvolution at Patreon

Returns:

an instance of the supplied example

getAllRows

public List<E> getAllRows()
                                 throws SQLException

Gets All Rows of the table from the database

Retrieves all rows that match the example set during creation or by subsequent getRowsByExample(nz.co.gregs.dbvolution.DBRow) and similar methods.

If the example has no criteria specified and there is no raw SQL set then all rows of the table will be returned.

Throws AccidentalBlankQueryException if you haven't specifically allowed blank queries with setBlankQueryAllowed(boolean)

Support DBvolution at Patreon

Returns:

all the appropriate rows of the table from the database;

Throws:

SQLException - database exceptions

toList

public List<E> toList()
                             throws SQLException

Synonym for getAllRows()

Support DBvolution at Patreon

Returns:

all the appropriate rows 1 Database exceptions may be thrown

Throws:

SQLException - java.sql.SQLException

getRowsByExample

public List<E> getRowsByExample(E example)
                                       throws SQLException

Sets the example and retrieves all the appropriate records.

The example is stored as the new exemplar and the query is rerun

The following will retrieve all records from the table where the Language column contains JAVA:
DBTableOLD<MyRow> myTable = database.getDBTableOLD(new MyRow());
MyRow myExample = new MyRow();
myExample.getLanguage.useLikeComparison("%JAVA%");
myTable.getByExample(myExample);
List<MyRow> myRows = myTable.toList();

Parameters:

example - example

Support DBvolution at Patreon

Returns:

All the rows that match the example 1 Database exceptions may be thrown

Throws:

SQLException - java.sql.SQLException

See Also:

QueryableDatatype, DBRow

getFirstRow

public E getFirstRow()
                            throws SQLException

Returns the first row of the table

Particularly helpful when you know there is only one row

Functionally equivalent to getAllRows().get(0).

Support DBvolution at Patreon

Returns:

the first appropriate row in this DBTable

Throws:

SQLException - java.sql.SQLException

getOnlyRow

public E getOnlyRow()
                           throws SQLException,
                                  UnexpectedNumberOfRowsException

Returns the first row and only row of the table.

Similar to getFirstRow() but throws an UnexpectedNumberOfRowsException if there is more than 1 row available

getAllRows() with the initial exemplar will be run.

Support DBvolution at Patreon

Returns:

the first row in this DBTableOLD instance

Throws:

SQLException - java.sql.SQLException

UnexpectedNumberOfRowsException - nz.co.gregs.dbvolution.exceptions.UnexpectedNumberOfRowsException

getOnlyRowByExample

public E getOnlyRowByExample(E example)
                                    throws SQLException,
                                           UnexpectedNumberOfRowsException,
                                           AccidentalBlankQueryException

Sets the exemplar to the given example and retrieves the only appropriate record.

Throws an exception if there are no appropriate records, or several appropriate records.

The following will return the only record from the table where the Language column contains JAVA:
MyTableRow myExample = new MyTableRow();
myExample.getLanguage.useLikeComparison("%JAVA%");
MyRow myRow = (new DBTable<MyTableRow>()).getOnlyRowByExample(myExample);

Parameters:

example - example

Support DBvolution at Patreon

Returns:

A list containing the rows that match the example 1 Database exceptions may be thrown

Throws:

SQLException - java.sql.SQLException

UnexpectedNumberOfRowsException - nz.co.gregs.dbvolution.exceptions.UnexpectedNumberOfRowsException

AccidentalBlankQueryException

See Also:

QueryableDatatype, DBRow

getRowsByExample

public List<E> getRowsByExample(E example,
                       long expectedNumberOfRows)
                                       throws SQLException,
                                              UnexpectedNumberOfRowsException,
                                              AccidentalBlankQueryException

This method retrieves all the appropriate records, and throws an exception if the number of records differs from the required number.

The following will retrieve all 10 records from the table where the Language column contains JAVA, and throw an exception if anything other than 10 rows is returned.
MyTableRow myExample = new MyTableRow();
myExample.getLanguage.useLikeComparison("%JAVA%");
List<MyTableRow> rows = (new DBTable<MyTableRow>()).getRowsByExample(myExample, 10L);

Parameters:

example - example

expectedNumberOfRows - expectedNumberOfRows

Support DBvolution at Patreon

Returns:

a DBTableOLD instance containing the rows that match the example 1 Database exceptions may be thrown

Throws:

SQLException - java.sql.SQLException

UnexpectedNumberOfRowsException - nz.co.gregs.dbvolution.exceptions.UnexpectedNumberOfRowsException

AccidentalBlankQueryException

See Also:

QueryableDatatype, DBRow

getRowsForPage

public List<E> getRowsForPage(Integer pageNumber)
                                     throws SQLException

Retrieves that DBRows for the page supplied.

DBvolution supports paging through this method. Use setRowLimit(int) to set the page size and then call this method with the desired page number.

This method is zero-based so the first page is getAllRows(0).

Parameters:

pageNumber - pageNumber

Support DBvolution at Patreon

Returns:

a list of the DBRows for the selected page. 1 Database exceptions may be thrown

Throws:

SQLException - java.sql.SQLException

getRowsByPrimaryKey

public List<E> getRowsByPrimaryKey(Number pkValue)
                                          throws SQLException,
                                                 ClassNotFoundException

Retrieves the row (or rows in a bad database) that has the specified primary key.

The primary key column is identified by the @DBPrimaryKey annotation in the TableRow subclass.

Parameters:

pkValue - pkValue

Support DBvolution at Patreon

Returns:

a List containing the row(s) for the primary key 1 Database exceptions may be thrown

Throws:

SQLException - java.sql.SQLException

ClassNotFoundException - java.lang.ClassNotFoundException

getRowsByPrimaryKey

public List<E> getRowsByPrimaryKey(String pkValue)
                                          throws SQLException,
                                                 ClassNotFoundException

Retrieves the row (or rows in a bad database) that has the specified primary key.

The primary key column is identified by the @DBPrimaryKey annotation in the TableRow subclass.

Parameters:

pkValue - pkValue

Support DBvolution at Patreon

Returns:

a List containing the row(s) for the primary key 1 Database exceptions may be thrown

Throws:

SQLException - java.sql.SQLException

ClassNotFoundException - java.lang.ClassNotFoundException

getRowsByPrimaryKey

public List<E> getRowsByPrimaryKey(Date pkValue)
                                          throws SQLException,
                                                 ClassNotFoundException

Retrieves the row (or rows in a bad database) that has the specified primary key.

The primary key column is identified by the @DBPrimaryKey annotation in the TableRow subclass.

Parameters:

pkValue - pkValue

Support DBvolution at Patreon

Returns:

a List containing the row(s) for the primary key 1 Database exceptions may be thrown

Throws:

SQLException - java.sql.SQLException

ClassNotFoundException - java.lang.ClassNotFoundException

getSQLForQuery

public String getSQLForQuery()

Generates and returns the actual SQL that will be used by getAllRows() now.

Good for debugging and great for DBAs, this is how you find out what DBvolution is really doing.

Generates the SQL query for retrieving the objects but does not execute the SQL. Use the get* methods to retrieve the rows.

See also getSQLForCount

Support DBvolution at Patreon

Returns:

a String of the SQL that will be used by getAllRows(). 1 Database exceptions may be thrown

getSQLForQuery

public String getSQLForQuery(DBRow exemplar)
                      throws SQLException

Generates and returns the actual SQL that will be used by getRowsByExample(nz.co.gregs.dbvolution.DBRow) now.

Good for debugging and great for DBAs, this is how you find out what DBvolution is really doing.

Generates the SQL query for retrieving the objects but does not execute the SQL. Use the get* methods to retrieve the rows.

See also getSQLForCount and getSQLForQuery()

Parameters:

exemplar -

Support DBvolution at Patreon

Returns:

a String of the SQL that will be used by getRowsByExample(nz.co.gregs.dbvolution.DBRow). 1 Database exceptions may be thrown

Throws:

SQLException - java.sql.SQLException

getSQLForCount

public String getSQLForCount()
                      throws SQLException

Returns the SQL query that will used to count the rows

Use this method to check the SQL that will be executed during the count() method

Support DBvolution at Patreon

Returns:

a String of the SQL query that will be used to count the rows returned by this query 1 Database exceptions may be thrown

Throws:

SQLException - java.sql.SQLException

count

public Long count()
           throws SQLException

Count the rows on the database without retrieving the rows.

Either: counts the results already retrieved, or creates a count query for this instance and retrieves the number of rows that would have been returned had getAllRows() been called.

Support DBvolution at Patreon

Returns:

the number of rows that have or will be retrieved. 1 Database exceptions may be thrown

Throws:

SQLException - java.sql.SQLException

print

public void print()
           throws SQLException

Convenience method to print all the rows in the current collection Equivalent to: print(System.out)

Throws:

SQLException - SQLException

print

public void print(PrintStream stream)
           throws SQLException

the same as print() but allows you to specify the PrintStream required myTable.printAllRows(System.err);

Parameters:

stream - stream

Throws:

SQLException - java.sql.SQLException

insert

@SafeVarargs
public final DBActionList insert(E... newRows)
                          throws SQLException

Inserts DBRows into the database.

Parameters:

newRows - newRows

Support DBvolution at Patreon

Returns:

a DBActionList of all the actions performed 1 Database exceptions may be thrown

Throws:

SQLException - java.sql.SQLException

insert

public DBActionList insert(Collection<E> newRows)
                    throws SQLException

Inserts DBRows into the database

Parameters:

newRows - newRows

Support DBvolution at Patreon

Returns:

a DBActionList of all the actions performed 1 Database exceptions may be thrown

Throws:

SQLException - java.sql.SQLException

delete

@SafeVarargs
public final DBActionList delete(E... oldRows)
                          throws SQLException

Deletes the rows from the database permanently.

Parameters:

oldRows - oldRows

Support DBvolution at Patreon

Returns:

a DBActionList of the delete actions. 1 Database exceptions may be thrown

Throws:

SQLException - java.sql.SQLException

delete

public DBActionList delete(Collection<E> oldRows)
                    throws SQLException

Deletes the rows from the database permanently.

Parameters:

oldRows - oldRows

Support DBvolution at Patreon

Returns:

a DBActionList of the delete actions. 1 Database exceptions may be thrown

Throws:

SQLException - java.sql.SQLException

update

public DBActionList update(E oldRow)
                    throws SQLException

Updates the DBRow on the database. The row will be changed so that future updates will not include the current changes.

Parameters:

oldRow - oldRow

Support DBvolution at Patreon

Returns:

a DBActionList of the actions performed on the database 1 Database exceptions may be thrown

Throws:

SQLException - java.sql.SQLException

update

public DBActionList update(Collection<E> oldRows)
                    throws SQLException

Updates Lists of DBRows on the database

Parameters:

oldRows - oldRows

Support DBvolution at Patreon

Returns:

a DBActionList of the actions performed on the database 1 Database exceptions may be thrown

Throws:

SQLException - java.sql.SQLException

getPrimaryKeysAsLong

public List<Long> getPrimaryKeysAsLong()
                                throws SQLException

Retrieves the rows for this table and returns the primary keys of the rows as Longs.

Requires the primary key field to be a DBNumber of DBInteger

Support DBvolution at Patreon

Returns:

a List of primary keys as Longs. 1 Database exceptions may be thrown

Throws:

SQLException - java.sql.SQLException

See Also:

getPrimaryKeysAsString(), getAllRows()

getPrimaryKeysAsString

public List<String> getPrimaryKeysAsString()
                                    throws SQLException

Retrieves the rows for this table and returns the primary keys of the rows as Strings.

Support DBvolution at Patreon

Returns:

a List of primary keys as Longs. 1 Database exceptions may be thrown

Throws:

SQLException - java.sql.SQLException

See Also:

getPrimaryKeysAsString(), getAllRows()

compare

public void compare(DBTable<E> secondTable)
             throws SQLException

Compares 2 tables, presumably from different criteria or databases prints the differences to System.out Should be updated to return the varying rows somehow

Parameters:

secondTable - : a comparable table

Throws:

SQLException - java.sql.SQLException

setRowLimit

public DBTable<E> setRowLimit(int rowLimit)

Limit the query to only returning a certain number of rows

Implements support of the LIMIT and TOP operators of many databases.

Only the specified number of rows will be returned from the database and DBvolution.

Parameters:

rowLimit - rowLimit

Support DBvolution at Patreon

Returns:

this DBTable instance

clearRowLimit

public DBTable<E> clearRowLimit()

Removes the limit set with setRowLimit(int).

Al the rows will be returned from the database and DBvolution.

Support DBvolution at Patreon

Returns:

this DBTable instance

setSortOrder

public DBTable<E> setSortOrder(ColumnProvider... sortColumns)

Sets the sort order of properties (field and/or method) by the given property object references.

For example the following code snippet will sort by just the name column:

 Customer customer = ...;
 customer.setSortOrder(customer, customer.name);
 

Requires that all orderColumns be from the baseRow instance to work.

Parameters:

sortColumns - sortColumns

Support DBvolution at Patreon

Returns:

this

clearSortOrder

public DBTable<E> clearSortOrder()

Removes the sort order add with setSortOrder(nz.co.gregs.dbvolution.columns.ColumnProvider...).

Support DBvolution at Patreon

Returns:

this DBTable instance

setBlankQueryAllowed

public DBTable<E> setBlankQueryAllowed(boolean allow)

Change the Default Setting of Disallowing Blank Queries

A common mistake is creating a query without supplying criteria and accidently retrieving a huge number of rows.

DBvolution detects this situation and, by default, throws a AccidentalBlankQueryException when it happens.

To change this behaviour, and allow blank queries, call setBlankQueriesAllowed(true).

Parameters:

allow - - TRUE to allow blank queries, FALSE to return it to the default setting.

Support DBvolution at Patreon

Returns:

this DBTable instance

setToMatchAnyCondition

public void setToMatchAnyCondition()

Set the query to return rows that match any conditions

This means that all permitted*, excluded*, and comparisons are optional for any rows and rows will be returned if they match any of the conditions.

The conditions will be connected by OR in the SQL.

setToMatchAllConditions

public void setToMatchAllConditions()

Set the query to only return rows that match all conditions

This is the default state

This means that all permitted*, excluded*, and comparisons are required for any rows and the conditions will be connected by AND.

setRawSQL

public DBTable<E> setRawSQL(String rawQuery)
                                   throws SQLException

Adds the specified raw SQL to the DBTable query.

This method is for adding conditions that can not be created using the Expressions framework or the preferred/excluded methods of QueryableDatatype.

The raw SQL will be added as a condition to the where clause. It should and SQL excerpt that starts with AND (or if you are using Match Any Condition).

For instance marque.name.permittedValues('peugeot','hummer') could be implemented, rather more awkwardly, as table.setRawSQL("and lower(name) in ('peugeot','hummer')").

Parameters:

rawQuery - rawQuery

Support DBvolution at Patreon

Returns:

this DBtable instance. 1 Database exceptions may be thrown

Throws:

SQLException - java.sql.SQLException

getDistinctValuesOfColumn

public <A> List<A> getDistinctValuesOfColumn(A fieldOfProvidedRow)
                                  throws AccidentalBlankQueryException,
                                         IncorrectRowProviderInstanceSuppliedException,
                                         SQLException

Returns the unique values for the column in the database.

Creates a query that finds the distinct values that are used in the field/column supplied.

Some tables use repeated values instead of foreign keys or do not use all of the possible values of a foreign key. This method makes it easy to find the distinct or unique values that are used.

Type Parameters:

A - DBRow type

Parameters:

fieldOfProvidedRow - - the field/column that you need data for. Must be from the exemplar

Support DBvolution at Patreon

Returns:

a list of distinct values used in the column. 1 Database exceptions may be thrown

Throws:

SQLException - java.sql.SQLException

AccidentalBlankQueryException

IncorrectRowProviderInstanceSuppliedException

printSQLForQuery

public void printSQLForQuery()

setQueryTimeout

public DBTable<E> setQueryTimeout(int i)

clearTimeout

public DBTable<E> clearTimeout()