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 :
- {@link hirondelle.web4j.database.Db} - common utility methods
- {@link hirondelle.web4j.database.SqlId} - identifiers for underlying SQL statements in .sql files
The WEB4J data layer :
- uses one or more relational databases
- uses simple .sql text files, containing SQL statements
- does not use any .xml files
- does not use any object-relational mapping
- does not use any annotations
- is instructed on how to obtain a Connection through the application's implementation of {@link hirondelle.web4j.database.ConnectionSource}
- uses only PreparedStatements, since they are preferred
- is configured using some items in web.xml
- can precompile SQL statements upon startup (if supported by the driver). This allows syntax errors to be found upon startup.
- allows many DAO methods to be implemented in just one or two lines of code
This package does not currently use distributed transactions internally. However, since a
{@link hirondelle.web4j.database.ConnectionSource} can return a Connection for any database,
the caller can still implement operations against multiple databases, including distributed
transactions, if desired.
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
{@link hirondelle.web4j.database.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
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 {@link hirondelle.web4j.database.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 {@link hirondelle.web4j.database.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.)
This design has many benefits :
- it uses no tedious object-relational mapping, no .xml files, and no annotations.
This greatly reduces the effort needed to implement DAOs.
- SQL statements created and verified in other tools can be easily copied over,
with very few textual edits
- if an application is upgraded to an entirely new implementation, the SQL statements
can be easily reused in the new environment. This is a major benefit when migrating an application.
- programmers or database administrators unfamiliar with the details of an
application - or even unfamiliar with Java itself - can easily find, read,
and edit the SQL statements used by the application. Thus, a larger pool of maintainers
can contribute meaningfully to an application.
- SQL statements are not hard-coded into classes. If desired, SQL statements can be edited easily after
deployment, and these edits are pulled in through a simple restart. (In production, this should be used with
care, or even avoided altogether. For example, it might be used to change something minor, such as sort
order of a SELECT. It might also be used in an emergency.)
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 :
- statements.sql
- config.sql
- REPORTS.sql
- pick-lists.sql
- accounts.payable.sql
- 2005-06-15blah0-_$blah.sql
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 :
- placing .sql files in the same package as the Data Access Object
that uses it (package-by-feature)
- eliminating or reducing developer contention for commonly needed files
- splitting up a large file into various parts of more reasonable size
- swapping files that target different databases
If you use one .sql file per directory/feature, then it's recommended to consider using a
fixed, conventional name such as statements.sql.
Upon startup, WEB4J will read in all .sql files under the WEB-INF directory, and will
match entries to {@link hirondelle.web4j.database.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.
To maximize portability, SQL statements should be validated using a tool such as
the Mimer SQL-92
validator.
There are constraints on how an application passes parameters to SQL statements.
They are listed in Db.
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 :
- Block - a multiline block of text with within braces, with an associated
identifier (similar to a typical Java block)
- Block Name - the identifier of a block, appearing on the first line, before the
opening brace
- Block Body - the text appearing between the opening and closing braces.
The format details are as follows :
- empty lines can appear only outside of blocks
- the '--' character denotes a comment
- there are no multiline comments. (Such comments are easier for the writer,
but are much less clear for the reader.)
- the body of each item is placed in a named block, bounded by
'<name> {' on an initial line, and '}' on an end line.
The name given to the block (ADD_MESSAGE for example) is how
WEB4J identifies each block. The Block Name must correspond
to a public static final SqlId field. Upon startup, this allows verification
that all items defined in the text source have a corresponding item in code.
- '--' comments can appear in the Block Body as well
- if desired, the Block Body may be indented, to make the Block more legible
- the Block Name of 'constants' is reserved. A constants
block defines one or more simple textual substitution constants, as
name = value pairs, one per line, that may be
referenced later on in the file (see example). They are defined only to allow
such substitution to occur later in the file. Any number of constants
blocks can appear in a file.
- Block Names and the names of constants satisfy
{@link hirondelle.web4j.database.SqlId#FORMAT}.
- inside a Block Body, substitutions are denoted by the common
syntax '${blah}', where blah refers to an item appearing
earlier in the file, either the content
of a previously defined Block, or the value of a constant defined in a
constants Block
- an item must be defined before it can be used in a substitution ; that is,
it must appear earlier in the file
- no substitutions are permitted in a constants Block
- Block Names, and the names of constants, should be unique.
For calling stored procedures in general, please see {@link hirondelle.web4j.database.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 {@link java.sql.PreparedStatement},
without using methods specific to {@link java.sql.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).