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 `PreparedStatement`s, 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.

## 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 {@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

### 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 {@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.)

### Benefits Of This Design

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

### 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 :

- `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`.

### Startup Processing Of `.sql` Files

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.

### 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 :

- 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.

### Stored Procedures

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