Overview  Package   Class  Use  Deprecated  Index
Home >> All >> javatools >> [ db overview ]	PREV CLASS  NEXT CLASS
SUMMARY:  JAVADOC |  SOURCE |  DOWNLOAD | NESTED | FIELD | CONSTR | METHOD	DETAIL: FIELD | CONSTR | METHOD

---

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




Version:
  
0.0.2













Field Summary



(package private)  java.util.Map
asMap



          Maps columns to "AS" arguments.



(package private)  java.util.List
columnList



          Contains the columns used in this operation.



(package private)  boolean
distinct



          true: clause DISTINCT will be put;
 false: clause DISTINCT will not be put.



(package private)  java.util.List
groupBy



          Contains the columns for grouping.



(package private)  java.util.List
joinedTables



          Contains the list of joined tables.



(package private)  DbExpr
limit



          Limits the search to a certain amount of rows.



(package private)  DbExpr
offset



          Puts an offset.



(package private)  java.util.List
orderBy



          Contains the columns for sorting.



(package private)  DbTable
result



          The result of the SELECT operation.



(package private)  java.sql.ResultSet
resultSet



          Contains the real result set.



(package private)  java.sql.PreparedStatement
stmt



          It's the prepared statement to execute.



(package private)  DbExpr
where



          The where clause for the SELECT.


 


Fields inherited from class javatools.db.DbExpr


db


 






Constructor Summary



(package private)
DbSelector(DbDatabase db)



          Create a new DbSelector.


 






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.



(package private)  java.lang.String
groupByClause(java.util.List groupBy)



          Generates the GROUP Bye clause.



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



          Generate the order by clause.



private  void
purgeTables(java.util.Set c)



           



 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.



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



          Converts this selector in a string.


 


Methods inherited from class javatools.db.DbExpr


and, containsAllStrings, containsAllStrings, count, dateTrunc, equal, getString, greaterThan, greaterThanOrEqual, in, in, isNotNull, isNull, lessThan, lessThanOrEqual, like, lower, max, min, notEqual, notIn, 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

The result of the SELECT operation.







asMap
java.util.Map asMap

Maps columns to "AS" arguments.







columnList
java.util.List columnList

Contains the columns used in this operation.







where
DbExpr where

The where clause for the SELECT.







orderBy
java.util.List orderBy

Contains the columns for sorting.







groupBy
java.util.List groupBy

Contains the columns for grouping.







joinedTables
java.util.List joinedTables

Contains the list of joined tables.







resultSet
java.sql.ResultSet resultSet

Contains the real result set.







stmt
java.sql.PreparedStatement stmt

It's the prepared statement to execute.







limit
DbExpr limit

Limits the search to a certain amount of rows.







offset
DbExpr offset

Puts an offset.







distinct
boolean distinct

true: clause DISTINCT will be put;
 false: clause DISTINCT will not be put.











Constructor Detail




DbSelector
DbSelector(DbDatabase db)
     throws DbException

Create a new DbSelector.









Method Detail




setWhere
public void setWhere(DbExpr where)

Set the where condition for this query.








getWhere
public DbExpr getWhere()

Returns the where condition.








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

Set the entire orderby list in one go.








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








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.








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.








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

Get the query string represented by this query.


Specified by:
getQueryString in class DbExpr








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

Add the given object to the select column list.








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.








getColumn
public DbColumn getColumn(int index)

Gets a column in the column list.








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.








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.








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.








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








addGroupBy
public void addGroupBy(DbExpr column)

Adds a GROUP BY column to this select.








setDistinct
public void setDistinct(boolean pDistinct)

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








addJoinedTable
public void addJoinedTable(DbJoinedTable joinedTable)

Adds a joined table.








executeToResultSet
public void executeToResultSet(DbConnection dbcon)
                        throws DbException

Execute and get a JDBC ResultSet.








execute
public DbTable execute(DbConnection dbcon)
                throws DbException

Execute and return a DbTable.








execute
public DbTable execute()
                throws DbException

Execute and return a DbTable with the default DbConnection.








toString
public java.lang.String toString()

Converts this selector in a string.








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

Creates a SELECT instruction with given tables.








purgeTables
private void purgeTables(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.








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

Generate the order by clause.








groupByClause
java.lang.String groupByClause(java.util.List groupBy)
                         throws DbException

Generates the GROUP Bye clause.














  

  
    Overview 
  
    Package 
  
  Class 
  
    Use 
  
    Deprecated 
  
    Index 
  









Home >> All >> javatools  >> [ db overview ]

 PREV CLASS  NEXT CLASS



  SUMMARY:  JAVADOC |  SOURCE |  DOWNLOAD | NESTED | FIELD | CONSTR | METHOD

DETAIL: FIELD | CONSTR | METHOD