org.less4j
Class SQL

java.lang.Object
  org.less4j.SQL

public class SQL
extends java.lang.Object

Conveniences to query and update an SQL database with a simple object relational interface to map between JDBC result sets and six JSON patterns: table, relations, collection, dictionary, one or many objects.

Synopsis

This class is a library of static functions applied by less4j's Actor methods sql*, so unless you want to apply it somewhere else, you should use those higher level methods instead.

Also, note that this implementation depends on less4j's JSON and makes little sense out of a web controller.

About ORMs

The benefit of an ORM in a J2EE controller is mainly to paliate the complications of JDBC and a brain-dead synchronous share-everything design. Good ORMs can provide developers a convenient API without leaks that allows controllers to release SQL connections asap.

However, I doubt there is any benefit for J2EE applications in trying to map instances of arbitrary Java objects in an SQL database. First because in most case, data is allready available in a schema that just does not map to the application's object model. Second because when data is not an SQL legacy, there is an opportunity not to use a relations as object properties.

So, you won't find anything like Hibernate here. I expect less4j's applications to store JSON objects as they are first, then latter update one or more database with indexes and statistics about those objects.

This class bundles a simple ORM interface and singletons for each of its six protected implementations, one per patterns supported. Most applications of less4j don't need more and you may as well use the appropriate methods of Actor or Controller.

Version:

0.30

Author:

Laurent Szyster

Nested Class Summary

static interface

SQL.ORM The simplest ORM interface possible for JDBC ResultSet.

Field Summary

static SQL.ORM	collection An ORM singleton to map the first column of a JDBC ResultSet into an JSON.Array.
static SQL.ORM	dictionary An ORM singleton to map a JDBC ResultSet into an JSON.Object, using the first column as key and the following(s) as value.
static SQL.ORM	object An ORM singleton to map the first row of a JDBC ResultSet into a single JSON.Object, using column names as keys.
static SQL.ORM	objects An ORM singleton to map a JDBC ResultSet into a JSON.Array of JSON.Object, using column names as keys.
static SQL.ORM	relations An ORM singleton to map a JDBC ResultSet into a JSON.Array of JSON.Array, without metadata.
static SQL.ORM	table An ORM singleton to map a JDBC ResultSet with metadata into a single JSON.Object with the obvious "columns" and "rows" members.

Constructor Summary

SQL()

Method Summary

static java.lang.Integer	batch(java.sql.Connection sql, java.lang.String statement, java.util.Iterator params)
static java.lang.Object	query(java.sql.Connection sql, java.lang.String statement, java.util.Iterator args, int fetch, SQL.ORM collector) Try to query the sql JDBC connection with an SQL statement and an argument iterator, use an ORM to return a JSON.Array, a JSON.Object or null if the result set was empty.
static java.lang.Integer	update(java.sql.Connection sql, java.lang.String statement) Try to execute and UPDATE, INSERT, DELETE or DDL statement, close the JDBC/DataSource statement, return the number of rows updated.
static java.lang.Integer	update(java.sql.Connection sql, java.lang.String statement, java.util.Iterator args) Try to execute a prepared UPDATE, INSERT, DELETE or DDL statement with many arguments iterator, close the JDBC/DataSource statement and returns the number of rows updated.

Methods inherited from class java.lang.Object

equals, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Field Detail

collection

public static SQL.ORM collection

An ORM singleton to map the first column of a JDBC ResultSet into an JSON.Array.

dictionary

public static SQL.ORM dictionary

An ORM singleton to map a JDBC ResultSet into an JSON.Object, using the first column as key and the following(s) as value.

object

public static SQL.ORM object

An ORM singleton to map the first row of a JDBC ResultSet into a single JSON.Object, using column names as keys.

objects

public static SQL.ORM objects

An ORM singleton to map a JDBC ResultSet into a JSON.Array of JSON.Object, using column names as keys.

relations

public static SQL.ORM relations

An ORM singleton to map a JDBC ResultSet into a JSON.Array of JSON.Array, without metadata.

table

public static SQL.ORM table

An ORM singleton to map a JDBC ResultSet with metadata into a single JSON.Object with the obvious "columns" and "rows" members.

Constructor Detail

SQL

public SQL()

Method Detail

batch

public static java.lang.Integer batch(java.sql.Connection sql,
                                      java.lang.String statement,
                                      java.util.Iterator params)
                               throws java.sql.SQLException

Parameters:

sql -

statement -

params -

Returns:

Throws:

java.sql.SQLException

query

public static java.lang.Object query(java.sql.Connection sql,
                                     java.lang.String statement,
                                     java.util.Iterator args,
                                     int fetch,
                                     SQL.ORM collector)
                              throws java.sql.SQLException

Try to query the sql JDBC connection with an SQL statement and an argument iterator, use an ORM to return a JSON.Array, a JSON.Object or null if the result set was empty.

Synopsis

try {
    JSON.Array relations = (JSON.Array) SQL.query(
        "select * from TABLE wher COLUMN=?", 
        Simple.iterator (new String[]{"criteria"}),
        100, SQL.relations
        );
} catch (SQLException e) {
    e.println(System.err);
}

Parameters:

sql - the Connection to query

statement - to prepare and execute as a query

args - an iterator through arguments

fetch - the number of rows to fetch

collector - the ORM used to map the result set

Returns:

a JSON.Array, a JSON.Object or null

Throws:

java.sql.SQLException

update

public static java.lang.Integer update(java.sql.Connection sql,
                                       java.lang.String statement)
                                throws java.sql.SQLException

Try to execute and UPDATE, INSERT, DELETE or DDL statement, close the JDBC/DataSource statement, return the number of rows updated.

Parameters:

sql - the Connection to update

statement - the SQL statement to execute

Returns:

-1 if the statement failed, 0 if no row update took place, or the numbers of rows updated, deleted or inserted.

Throws:

java.sql.SQLException

update

public static java.lang.Integer update(java.sql.Connection sql,
                                       java.lang.String statement,
                                       java.util.Iterator args)
                                throws java.sql.SQLException

Try to execute a prepared UPDATE, INSERT, DELETE or DDL statement with many arguments iterator, close the JDBC/DataSource statement and returns the number of rows updated.

Parameters:

sql - the Connection to update

statement - the SQL statement to execute

args - an Iterator of JSON.Arrays

Returns:

an Integer

Throws:

SQLException

java.sql.SQLException