Version 4.10.0

Package hirondelle.web4j.database

Interaction with the database(s).

See:
          Description

Interface Summary
ConnectionSource Return a Connection to a database, using a policy defined by the application.
ConvertColumn Convert ResultSet column values into common 'building block' objects.
Tx Execute a database transaction.
 

Class Summary
ConvertColumnImpl Default implementation of ConvertColumn, suitable for most applications.
Db Utility class for the most common database tasks.
DbTx Version of Db for use in a transaction.
DynamicSql Dynamic SQL statement created in code.
Report Utility for producing reports quickly from a ResultSet.
SqlId Identifier of an SQL statement block in an .sql file. (Such identifiers must be unique.)
StoredProcedureTemplate Template for using CallableStatements.
TxSimple Perform the simplest kinds of transactions.
TxTemplate Template for executing a local, non-distributed transaction versus a single database, using a single connection.
 

Enum Summary
TxIsolationLevel Type-safe enumeration for transaction isolation levels.
 

Exception Summary
DAOException The only checked exception (excluding subclasses of this class) emitted by the data layer.
DuplicateException Thrown when a uniqueness problem occurs in the datastore during an ADD or CHANGE operation.
ForeignKeyException Thrown when a violation of a foreign key constraint occurs in the datastore during an ADD, CHANGE, or DELETE operation.
 

Package hirondelle.web4j.database Description

Interaction with the database(s).

The WEB4J data layer assumes the datastore is one or more relational databases. If some other means of persistence is required, then this package will not help the application programmer to implement persistence.

An application is not required to use this package to implement persistence. Other persistence tools can be used instead, if desired.

Please see the Data Access Object (DAOs) of the example application for effective illustration of how to use the services of this package.

Many DAO methods can be implemented using only two classes :

The WEB4J data layer :

This package does not currently use distributed transactions internally. However, since a ConnectionSource can return a Connection for any database, the caller can still implement operations against multiple databases, including distributed transactions, if desired.

Ordering Convention

When creating Model Objects from a ResultSet, WEB4J is unusual since it does not use a naming convention to map columns. Instead, it uses a much more effective ordering convention. This turns out to be a significant advantage for the caller, since trivial column mapping is no longer required.

(Ordering conventions seem to be very effective for human understanding. For example, would you rather do arithmetic with roman numerals, which use a naming convention to define magnitudes, or hindu-arabic numerals, which use an ordering convention to define place value?)

The convention is that the N columns of the ResultSet must map, in order, to the N arguments passed to a constructor of the Model Object. This is not as restrictive as it first sounds, since the order of the ResultSet columns is controlled by the application's SQL statement, and not by the structure of the underlying table.

More specifically : when building a Model Object from a ResultSet, the number of columns in the ResultSet is used to find the appropriate public constructor of the target Model Object. (Usually, a Model Object will have only a single constructor, one that takes all fields associated with the object.) Then, the columns are mapped in order, one-to-one, to the constructor arguments. Using the configured implementation of ConvertColumn, the columns are translated into Integer, Date, and so on, and passed to the Model Object constructor.

Many authors recommended that the SELECT * FROM X style be avoided : it does not explicitly name what columns are returned, so it is unclear to the reader. In addition, the order of the returned columns is not specified, and may change if the definition of the underlying table is altered. In WEB4J, following this recommendation is doubly important, because of the ordering convention mentioned above.

The .sql Files

Basic Idea
Benefits Of This Design
File Name And Location
Startup Processing
Portability
Passing Parameters
Detailed Syntax For .sql Files
Stored Procedures

Basic Idea

The WEB4J data layer uses text .sql files. SQL statements are not placed directly in classes. Instead, they are placed in regular text files (of a certain format), and referenced from code using SqlId objects.

Here is a quick example.

An entry in an .sql file :

MEMBER_FETCH {
  SELECT Id, Name, IsActive FROM Member WHERE Id=?
}
This SQL statement is referenced in code using an SqlId object, created using a corresponding String identifier "MEMBER_FETCH". Each SqlId must be defined as a public static final field:
public static final SqlId MEMBER_FETCH = new SqlId("MEMBER_FETCH");
...
public Member fetch(Id aMemberId) throws DAOException {
  return Db.fetch(Member.class, MEMBER_FETCH, aMemberId);
}
That is the basic idea.

(When this technique is used, most implementations of DAO methods are compact - usually just one or two lines, as shown above.)

Benefits Of This Design

This design has many benefits :

File Name And Location

All files under the WEB-INF directory whose name matches the regular expression "(?:.)*\\.sql" will be treated as .sql files, and will be read in upon startup.

Examples of valid *.sql file names include :

Any other files under WEB-INF will be ignored by this mechanism. Note that a file named something.SQL (upper case .SQL), will not be read by WEB4J upon startup. (This is a quick way of disabling a given file.)

It is recommended to use more than one *.sql file, to allow :

If you use one .sql file per directory/feature, then it's recommended to consider using a fixed, conventional name such as statements.sql.

Startup Processing Of .sql Files

Upon startup, WEB4J will read in all .sql files under the WEB-INF directory, and will match entries to SqlId objects - more info.

In addition, WEB4J can perform a 'test precompile' of each SQL statement, to see if it is syntactically correct. (This is not supported by all databases/drivers.) There is a setting in web.xml called IsSQLPrecompilationAttempted which turns this behavior on and off.

Portability

To maximize portability, SQL statements should be validated using a tool such as the Mimer SQL-92 validator.

Passing Parameters

There are constraints on how an application passes parameters to SQL statements. They are listed in Db.

Detailed Syntax For .sql Files

Here is an example of the syntax expected by WEB4J for .sql files.
-- This is a comment 
 ADD_MESSAGE   {
 INSERT INTO MyMessage -- another comment
  (LoginName, Body, CreationDate)
  -- another comment
  VALUES (?,?,?)
 }

-- Any number of 'constants' blocks can be defined, anywhere 
-- in the file. Such constants must be defined before being
-- referenced later in a SQL statement, however.
constants {
  num_messages_to_view = 5
}

-- Example of referring to a constant defined above.
FETCH_RECENT_MESSAGES {
 SELECT 
 LoginName, Body, CreationDate 
 FROM MyMessage 
 ORDER BY Id DESC LIMIT ${num_messages_to_view}
}

FETCH_NUM_MSGS_FOR_USER {
 SELECT COUNT(Id) FROM MyMessage WHERE LoginName=?
}

DELETE_USER_MESSAGES {
 DELETE FROM MyMessage WHERE LoginName=?
}

constants {
  -- Each constant must appear on a *single line* only, which
  -- is not the best for long SQL statements.
  -- Typically, if a previously defined *sub-query* is needed, 
  -- then a SQL statment may simply refer directly to that previously 
  -- defined SQL statement. 
  base_query = SELECT Id, LoginName, Body, CreationDate FROM MyMessage 
}

BROWSE_MESSAGES {
 ${base_query}
 ORDER BY 1
}

-- Some simple stored procedures may be referenced as well
UPDATE_NUM_NEURONS_STORED_PROC {
 {call update_num_neurons}
}

To describe the syntax more precisely, the following terminology is used here :

The format details are as follows :

Stored Procedures

For calling stored procedures in general, please see StoredProcedureTemplate.

If the stored procedure is particularly simple in nature, it may also be referenced in an .sql file. In short, if the stored procedure can be treated as a PreparedStatement, without using methods specific to CallableStatement, then it may appear in an .sql file, and be treated by WEB4J as any other SQL statement.

More specifically, such stored procedures have no OUT parameters of any kind, either OUT, INOUT, or an explicit return value (which must be registered as an OUT). On the other hand, they must have a single implicit return value. Here, 'implicit return value' refers to the return values of executeQuery and executeUpdate (either a ResultSet or an int count, respectively).


Version 4.10.0

Copyright Hirondelle Systems. Published October 19, 2013 - User Guide - All Docs.