javatools.db
Class DbSelector

java.lang.Object
  |
  +--javatools.db.DbExpr
        |
        +--javatools.db.DbSelector

public class DbSelector

extends DbExpr

A class used to select tabular data from an SQL database. The constructor is not public. To obtain a DbSelector call DbDatabase.selector(); Example: To select FRED's record from the people table...

 DbDatabase db = ...;
 DbTable people = db.getTable("PEOPLE");
 DbSelector selector = db.selector();
 selector.addColumn(people.getColumn("NAME"));
 selector.addColumn(people.getColumn("AGE"));
 selector.setWhere(people.getColumn("NAME").equal("FRED"));
 DbTable result = selector.execute();
 DbIterator it = result.iterator();
 while (it.hasNextRow()) {
     DbRow row = it.nextRow();
     System.out.println(row.getValue("NAME") + " " + row.getValue("AGE"));
 }
 

This is equivilent to...

 SELECT NAME, AGE FROM PEOPLE WHERE PEOPLE.NAME='FRED';
 

To get more fancy we can join the people table with the team table to find the captain of the person's favourite team. Then we can also order by the person's name, while igoring upper/lower case distinctions...

 DbDatabase db = ...;
 DbSelector selector = db.selector();
 DbTable people = db.getTable("PEOPLE");
 DbTable team = db.getTable("TEAM");
 DbSelector selector = db.selector();
 selector.addColumn(people.getColumn("NAME"));
 selector.addColumn(team.getColumn("CAPTAIN"));
 selector.setWhere(team.getColumn("NAME").equal(people.getColumn("FAVOURITE_TEAM"));
 selector.addOrderBy(people.getColumn("NAME").lower(), false) // Order by NAME ignoring case.
 DbTable result = selector.execute();
 DbIterator it = result.iterator();
 while (it.hasNextRow()) {
     DbRow row = it.nextRow();
     System.out.println(row.getValue("NAME") + " " + row.getValue("CAPTAIN"));
 }
 

This is equivilent to...

 SELECT PEOPLE.NAME, TEAM.CAPTAIN FROM PEOPLE, TEAM WHERE TEAM.NAME = PEOPLE.FAVOURITE_TEAM
   ORDER BY LOWER(PEOPLE.NAME)
 

To get fancier still, we can make use of sub-selects. To find all the people who happen to be captains of teams...

 DbDatabase db = ...;
 DbTable people = db.getTable("PEOPLE");
 DbTable team = db.getTable("TEAM");
 DbSelector subselector = db.selector();
 subselector.addColumn(team.getColumn("CAPTAIN"));
 DbSelector selector = db.selector();
 selector.addAll(people);
 selector.setWhere(people.getColumn("NAME").in(subselector));
 DbTable result = selector.execute();
 DbIterator it = result.iterator();
 while (it.hasNextRow()) {
     DbRow row = it.nextRow();
     System.out.println(row.toString());
 }
 

This is equivilent to...

 SELECT * from PEOPLE WHERE PEOPLE.NAME IN (SELECT CAPTAIN FROM TEAM);












Field Summary




(package private)  java.util.Map
asMap



           



(package private)  java.util.List
columnList



           



(package private)  DbExpr
limit



           



(package private)  DbExpr
offset



           



(package private)  java.util.List
orderBy



           



(package private)  DbTable
result



           



(package private)  java.sql.ResultSet
resultSet



           



(package private)  DbExpr
where



           


 
Fields inherited from class javatools.db.DbExpr
db
 



Constructor Summary




(package private)
DbSelector(DbDatabase db)



           



(package private)
DbSelector(DbDatabase db,
           DbTable result)



           


 



Method Summary




 void
addAll(DbTable table)



          Add all the columns from the given table to the select list.



 void
addAllExcept(DbTable table,
             DbColumn o)



          Add all the columns from the given table to the select list.



 void
addAllExcept(DbTable table,
             java.util.Set set)



          Add all the columns from the given table to the select list.



 DbColumn
addColumn(java.lang.Object col)



          Add the given object to the select column list.



 DbColumn
addColumn(java.lang.Object col,
          java.lang.String as)



          Add the given object to the select column list with an "AS" alias.



 void
addOrderBy(DbExpr column,
           boolean desc)



          Add an ORDER BY clause to this select.



 DbTable
execute()



          Execute and return a DbTable with the default DbConnection.



 DbTable
execute(DbConnection dbcon)



          Execute and return a DbTable.



 java.sql.PreparedStatement
executeToResultSet(DbConnection dbcon)



          Execute and get a JDBC ResultSet.



 java.lang.String
getQueryString()



          Get the query string represented by this query.



(package private)  java.lang.String
orderByClause(java.util.List orderBy)



          Generate the order by clause.



 void
selectTables(java.util.Set c)



           



 void
setLimit(int n)



          Don't get the whole result set, get only a limited number of rows.



 void
setOffset(int n)



          Don't get the first results, but skip n result rows.



 void
setOrderBy(java.util.List l)



          Set the entire orderby list in one go.



 int
setSqlValues(java.sql.PreparedStatement stmt,
             int i)



          Any DbExpr needs to be able to substitute any parameters as per JDBC "?"
  substitutions.



(package private)  int
setSqlValues(java.sql.PreparedStatement stmt,
             int i,
             java.util.List intoList)



          Substitute the literal values in the Prepared Statement.



 void
setWhere(DbExpr where)



          Set the where condition for this query.



 java.lang.String
toString()



           


 
Methods inherited from class javatools.db.DbExpr
and, dateTrunc, equal, getString, greaterThan, greaterThanOrEqual, in, isNotNull, isNull, lessThan, lessThanOrEqual, like, lower, max, min, notEqual, notIn, or, setSqlValue, upper, usesTables, usesTables
 
Methods inherited from class java.lang.Object
clone, equals, finalize, getClass, hashCode, notify, notifyAll, wait, wait, wait
 





Field Detail



result
DbTable result







asMap
java.util.Map asMap







columnList
java.util.List columnList







where
DbExpr where







orderBy
java.util.List orderBy







resultSet
java.sql.ResultSet resultSet







limit
DbExpr limit







offset
DbExpr offset








Constructor Detail



DbSelector
DbSelector(DbDatabase db)
     throws DbException





DbSelector
DbSelector(DbDatabase db,
           DbTable result)
     throws DbException






Method Detail



setWhere
public void setWhere(DbExpr where)

Set the where condition for this query.


Parameters:
where - The new where value





setOrderBy
public void setOrderBy(java.util.List l)

Set the entire orderby list in one go.


Parameters:
l - The new orderBy value





setSqlValues
public int setSqlValues(java.sql.PreparedStatement stmt,
                        int i)
                 throws DbException,
                        java.sql.SQLException

Description copied from class: DbExpr
Any DbExpr needs to be able to substitute any parameters as per JDBC "?"
  substitutions.


Specified by:
setSqlValues in class DbExpr


Parameters:
stmt - The new sqlValues value
i - The new sqlValues value
Returns:
Description of the Returned Value
Throws:
DbException - Description of Exception
java.sql.SQLException - Description of Exception





setLimit
public void setLimit(int n)
              throws DbException

Don't get the whole result set, get only a limited number of rows. Should
  be used in conjunction with ORDER BY in order to make the returned rows
  deterministic.


Parameters:
n - The new limit value
Throws:
DbException - Description of Exception





setOffset
public void setOffset(int n)
               throws DbException

Don't get the first results, but skip n result rows. Should be used in
  conjunction with ORDER BY in order to make the returned rows deterministic.


Parameters:
n - The new offset value
Throws:
DbException - Description of Exception





getQueryString
public java.lang.String getQueryString()
                                throws DbException

Get the query string represented by this query.


Specified by:
getQueryString in class DbExpr



Returns:
The queryString value
Throws:
DbException - Description of Exception





addColumn
public DbColumn addColumn(java.lang.Object col)
                   throws DbException

Add the given object to the select column list.


Parameters:
col - A DbColumn, DbExpr or literal value
Returns:
Description of the Returned Value
Throws:
DbException - Description of Exception





addColumn
public DbColumn addColumn(java.lang.Object col,
                          java.lang.String as)
                   throws DbException

Add the given object to the select column list with an "AS" alias.


Parameters:
col - a DbColumn, DbExpr or literal value
as - a column alias
Returns:
Description of the Returned Value
Throws:
DbException - Description of Exception





addAll
public void addAll(DbTable table)
            throws DbException

Add all the columns from the given table to the select list. A bit like
  SELECT * from table.


Parameters:
table - the table whose columns we wish to add
Throws:
DbException - Description of Exception





addAllExcept
public void addAllExcept(DbTable table,
                         DbColumn o)
                  throws DbException

Add all the columns from the given table to the select list. A bit like
  SELECT * from table.


Parameters:
table - the table whose columns we wish to add
o - The feature to be added to the AllExcept attribute
Throws:
DbException - Description of Exception





addAllExcept
public void addAllExcept(DbTable table,
                         java.util.Set set)
                  throws DbException

Add all the columns from the given table to the select list. A bit like
  SELECT * from table.


Parameters:
table - the table whose columns we wish to add
set - The feature to be added to the AllExcept attribute
Throws:
DbException - Description of Exception





addOrderBy
public void addOrderBy(DbExpr column,
                       boolean desc)

Add an ORDER BY clause to this select. The column actually need not be a
  plain column. It could be a column with a function applied. e.g.
  addOrderBy(table.getColumn("NAME").upper, false);


Parameters:
column - the column to order by
desc - whether to sort in descending order





executeToResultSet
public java.sql.PreparedStatement executeToResultSet(DbConnection dbcon)
                                              throws DbException

Execute and get a JDBC ResultSet.


Parameters:
dbcon - Description of Parameter
Throws:
DbException - Description of Exception





execute
public DbTable execute(DbConnection dbcon)
                throws DbException

Execute and return a DbTable.


Parameters:
dbcon - Description of Parameter
Returns:
Description of the Returned Value
Throws:
DbException - Description of Exception





execute
public DbTable execute()
                throws DbException

Execute and return a DbTable with the default DbConnection.



Returns:
Description of the Returned Value
Throws:
DbException - Description of Exception





toString
public java.lang.String toString()


Overrides:
toString in class java.lang.Object








selectTables
public void selectTables(java.util.Set c)








setSqlValues
int setSqlValues(java.sql.PreparedStatement stmt,
                 int i,
                 java.util.List intoList)
           throws DbException,
                  java.sql.SQLException

Substitute the literal values in the Prepared Statement.


Parameters:
stmt - the PreparedStatement
i - the parameter number we are up to
intoList - The new sqlValues value
Returns:
Description of the Returned Value
Throws:
DbException - Description of Exception
java.sql.SQLException - Description of Exception





orderByClause
java.lang.String orderByClause(java.util.List orderBy)
                         throws DbException

Generate the order by clause.


Parameters:
orderBy - Description of Parameter
Returns:
Description of the Returned Value
Throws:
DbException - Description of Exception