Package javatools.db

Database interface classes.

See:
Description

Interface Summary

DbTableUser

It is an interface for all the "things" that use tables.

Class Summary

ConnectionString	A class to manage the building of connection string for JDBC.
DbAbstractTable	A class representing tabular data.
DbAndExpr	An expression of the form A AND B.
DbCachedIterator	A class to represent a "cached" DbIterator, that is an iterator that can be called more than once.
DbCachedTable	It's a "cached" table, that represents a result from SELECT in a cached way, that is it can be accessed more than once.
DbColumn	A class that represents a particular column within a particular DbTable.
DbConnection	A Database connection.
DbConstraint	This abstract class represents a generic set of constraints for a table.
DbCriterion	It is a class that represents an expression of the type "A op B", where "op" is a generic operator.
DbDatabase	A class that represents a particular database.
DbDatabaseAdmin	Class to manage administrator functions.
DbDeleter	A class used to delete records from SQL tables.
DbDynamicConstraint	It is a class representing a "dynamic" constraint, i.e. a constraint that changes references dynamically.
DbExpr	An sql expression class.
DbExprFuncDef	An SQL expression of the form FUNCNAME(parameter....).
DbFalseExpr	An expression that always evaluates to false.
DbFixedAbstractTable	It represents a real database table.
DbInserter	A class used to insert records into SQL tables.
DbIterator	An iterator class for DbTables.
DbJoinedTable	This class represents a "joined table" in an SQL statement.
DbLiteral	Inserts a piece of literal text within the SQL expression.
DbManager	A class to manage all the other DbDatabases.
DbMiscExpr	An SQL expression of the form FUNCNAME(parameter....).
DbOrderBy	A class that represents an ORDER BY clause in a SELECT.
DbOrExpr	An expression of the form A OR B.
DbParenthesis	This expression has the result of putting parenthesis around another expression. i.e.
DbReferencedDeleter	It is just a DbDeleter, except in the fact it takes care of constraints.
DbReferencedInserter	It is just like a DbInserter, except on the fact it takes care of constraints.
DbReferencedTable	It is currently a hybrid.
DbReferencedUpdater	It is just like DbUpdater, except in the fact it takes care of constraints.
DbResult	Currently this class is not used.
DbRow	A row of tabular data.
DbRowSet	It contains only a set of rows.
DbSelector	A class used to select tabular data from an SQL database.
DbSequence	Generates unique values for use as keys.
DbTable	A class representing tabular data.
DbTrueExpr	An expression that always evaluates to true.
DbUpdater	A class used to update records from SQL tables.
DbValueList	This class represents a set of values to be used in an expression.

Exception Summary

DbException

An Exception class for this package.

Package javatools.db Description

Database interface classes.

These classes are designed to provide a nicer, higher level interface to the database than than provided by raw JDBC. Part of the design is somewhat modeled on the Rogue Wave Source Pro DB C++ class library (formally known as DBTools.h++). If you are left wondering "why is it so", reading the online Rogue-Wave Source Pro DB docs may provide philosophical answers.

Of course, everything that you can do with this package can be done with raw JDBC. For some simple examples, you may feel that jdbc provides a simpler solution. The real power of this package however is two-fold. Firstly, it becomes evident when you need to generate SQL on the fly. This is a typical scenario with a search screen where you can search on a number of fields. In this case you can find the generation of the correct SQL to be particularly hazardous. Secondly is in portability. The framework isolates you from the idiosyncracies of the SQL database implementation.

Other areas the framework shines is in general ease of use. While in some ways the plain SQL of jdbc may appear simpler, keeping track of the order of dynamic arguments is error prone, and attempting generation of dynamic SQL is tedious. The DbRow class provides a more useful way of storing a result row since it persists after each read, whereas the JDBC ResultSet only can access the current row.

The framework can provide a portability buffer between different SQL databases, since it hides the underlying SQL code. At the present time, there has not been an enormous amount of effort to make this portabilility work, but some effort has been made to make the package work with both Oracle and Postgresql. Various properties associated with differing SQL vendors are in the dbvendor.properties file.

Each application will generally use a db.properties file to specify parameters. A typical example would be...

myapp.driver  = oracle.jdbc.driver.OracleDriver
myapp.connect = jdbc:oracle:thin:@dbdev01:1521:devu02
myapp.userId = myloginname
myapp.password = mypassword
myapp.vendor = oracle

As of the current point in time, no doubt there is much room for extension and improvement to the framework. But the foundation seems solid and useful. Much more pleasant than raw JDBC, and more portable.

Here is an example of how this package greatly simplifies dynamic queries... Imagine the situation of a Person object with first and last names. A query method is required to query on one or the other or both or neither. With regular JDBC...

public void query(Person p) {
  String sql = "SELECT * FROM PERSON ";
  boolean first = true;
  if (p.getFirstName() != null) {
    sql += "WHERE PERSON.FIRSTNAME = ?";
    first = false;
  }
  if (p.getLastName() != null) {
    if (first) {
      sql += "WHERE ";
    } else {
      sql += " AND ";
    }
    sql += "PERSON.LASTNAME = ?";
    first = false;
  }
  Connection con = ...
  PreparedStatement stmt = con.prepareStatement(sql);
  int count = 1;
  if (p.getFirstName() != null) {
    stmt.setString(count++, p.getFirstName());
  }
  if (p.getLastName() != null) {
    stmt.setString(count++, p.getLastName());
  }
  stmt.execute();
  ...
}

Now compare this to using the framework...

public void query(Person p) {
  DbDatabase db = DbManager.getDatabase("mydb");
  DbTable ptable = db.getTable("PERSON");
  DbSelector select = db.selector();
  selector.addAll(ptable);
  DbExpr expr = db.trueExpr();
  if (p.getFirstName() != null) {
    expr = expr.and(ptable.getColumn("FIRSTNAME").equal(p.getFirstName()));
  }
  if (p.getLastName() != null) {
    expr = expr.and(ptable.getColumn("LASTNAME").equal(p.getLastName()));
  }
  selector.setWhere(expr);
  DbTable result = selector.execute();
  ...
}

Now the framework provides much more readable and maintainable code. Note the use of trueExpr() to allow dynamically generating AND expressions.

If you want to know where to start learning about this package, a good place to start is DbManager. From there continue to DbSelector, DbInserter, DbDeleter and DbUpdater.