javatools.db
Class DbSelector

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

All Implemented Interfaces:

DbTableUser

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);


















Method Summary




 void
addAll(DbAbstractTable table)



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



 void
addAllExcept(DbAbstractTable table,
             DbColumn o)



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



 void
addAllExcept(DbAbstractTable 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
addGroupBy(DbExpr column)



          Adds a GROUP BY column to this select.



 void
addJoinedTable(DbJoinedTable joinedTable)



          Adds a joined table.



 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.



 void
executeToResultSet(DbConnection dbcon)



          Execute and get a JDBC ResultSet.



 DbColumn
getColumn(int index)



          Gets a column in the column list.



 java.lang.String
getQueryString()



          Get the query string represented by this query.



 DbExpr
getWhere()



          Returns the where condition.



 void
selectTables(java.util.Set c)



          Creates a SELECT instruction with given tables.



 void
setDistinct(boolean pDistinct)



          Sets the value to control whether to put the DISTINCT clause or not.



 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)



          Puts data into statement.



 void
setWhere(DbExpr where)



          Set the where condition for this query.



 java.lang.String
toString()



          Converts this selector in a string.


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











Method Detail



setWhere
public void setWhere(DbExpr where)

Set the where condition for this query.


Parameters:
where - The new where value





getWhere
public DbExpr getWhere()

Returns the where condition.



Returns:
The stored where condition.





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

Puts data into statement.


Specified by:
setSqlValues in class DbExpr


Parameters:
stmt - The statement to use.
i - An index (obscure).
Returns:
An index (obscure).
Throws:
DbException - If something goes wrong.
java.sql.SQLException - If something goes wrong.





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 - If something goes wrong.





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 - If something goes wrong.





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 - If something goes wrong.





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:
The column itself.
Throws:
DbException - If something goes wrong.





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:
The column itself.
Throws:
DbException - If something goes wrong.





getColumn
public DbColumn getColumn(int index)

Gets a column in the column list.


Parameters:
index - The column to be got.
Returns:
The requested column.





addAll
public void addAll(DbAbstractTable 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 - If something goes wrong.





addAllExcept
public void addAllExcept(DbAbstractTable 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 - If something goes wrong.





addAllExcept
public void addAllExcept(DbAbstractTable 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 - If something goes wrong.





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





addGroupBy
public void addGroupBy(DbExpr column)

Adds a GROUP BY column to this select.


Parameters:
column - The column to add in the GROUP BY clause.





setDistinct
public void setDistinct(boolean pDistinct)

Sets the value to control whether to put the DISTINCT clause or not.


Parameters:
pDistinct - true: clause DISTINCT will be put;
 false: clause DISTINCT will not be put.





addJoinedTable
public void addJoinedTable(DbJoinedTable joinedTable)

Adds a joined table.


Parameters:
joinedTable - The joined table to add.





executeToResultSet
public void executeToResultSet(DbConnection dbcon)
                        throws DbException

Execute and get a JDBC ResultSet.


Parameters:
dbcon - The connection to use.
Throws:
DbException - If something goes wrong.





execute
public DbTable execute(DbConnection dbcon)
                throws DbException

Execute and return a DbTable.


Parameters:
dbcon - The connection to use.
Returns:
The result, in a DbTable.
Throws:
DbException - If something goes wrong.





execute
public DbTable execute()
                throws DbException

Execute and return a DbTable with the default DbConnection.



Returns:
The result, in a DbTable
Throws:
DbException - If something goes wrong.





toString
public java.lang.String toString()

Converts this selector in a string.


Overrides:
toString in class java.lang.Object



Returns:
The requested string.





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

Creates a SELECT instruction with given tables.


Parameters:
c - A set of tables.