Colle-SQL is relational database persistence layer. It uses JDBC and schema definitions written in XML to create databases and generated Java classes. The generated classes simplify common JDBC application development while providing strong typing between the relational database and Java.

The calling application has full control and can mix low-level JDBC calls with high-level Colle-SQL objects. To further express this, there are several things that Colle-SQL does not do:

1. Colle-SQL does not manage JDBC connections (see java.sql.Connection ) or any type of connection pooling. In most cases, your application code is responsible for providing a database connection to Colle-SQL. If you need connection pooling check with your JDBC driver provider or look at an EJB application server.
2. Colle-SQL does not manage transactions. It is assumed this will be handled either in the application code or by a middleware system such as an EJB server.
3. Colle-SQL is not a object-to-relational mapper. It does handle content of relational databases in an object-oriented maner but existing Java objects that are to be persisted through Colle-SQL must persist their content with the generated classes.

Database Schema Definition

To use Colle-SQL you must first define your database schema. This is done by placing table and view definition files in the same package in a Java source folder. The file format is XML based but Java code is generated in the same package by the build process (see the Quick Start Guide).

Your Java code can access several schemas at once, each in their own seperate namespace and package. Schema definition files that reference each other (i.e.: views referencing tables) must currently reside in the same package.

The schema definition is used to create the database (see net.sf.colle.schema.TableBuilder ) and the generated code used to access that database. TableBuilder issues CREATE TABLE and CREATE INDEX statements and can even load data from comma-delimited text files (.CSV files).

The Row Object

In the generated class for each table and view, there is an inner Row interface and RowImpl class. An instance of Row is used in most operations with the table or view.

Row classes in views are special in that they may implement the interface of the row class of one or more of the tables they join. This allows a row object of a view to be used as if it were the row object of table, thereby simplifying application code.

<crs:view name="UserGroups" ...>
    ...
    <crs:table name="Users" view-derived-from="true"/>
    <crs:table name="Groups" view-derived-from="true"/>
    ...
</crs:view>

Standard Methods

The generated code for tables have several "standard methods" for working with row objects. With these methods, you can insert, update, delete and read row objects by primary key.

WHERE Clauses

Colle-SQL doesn't attempt to completely eliminate SQL in the calling application, but it does greatly reduce the amount of SQL you need to write. As a side note, Colle-SQL also attempts to keep all relevant SQL you write in one location--all custom SQL is located in the table or view definition file on which it operates.

One type of SQL that is found frequently in table and view definitions is WHERE clauses. WHERE clauses can be used in custom lookup definitions to pull specific records or in custom delete definitions to delete specific records. WHERE clauses are also used in view definitions to specify which records are included in the view.

The following XML fragment can be used to define a custom lookup that will return the number of active users with a specific first and last name:

<crs:lookup name="getActiveUsers">
    <crs:where-clause>
        status = 'A' and first = ? and last = ?
    </crs:where-clause>
    <crs:parameter name="firstName" type="varchar"/>
    <crs:parameter name="lastName" type="varchar"/>
</crs:lookup>

Colle-SQL takes this fragment and creates a Java method. The method creates a java.sql.PreparedStatement with a SELECT statement, executes, retrieves a java.sql.ResultSet and returns a java.util.List of row objects. All of the details have been taken care of. The resulting Java method will have the following signature:

static java.util.List getActiveUsers(
    String firstName,
    String lastName,
    java.sql.Connection connection);

Stored Procedures

Table and view definitions can also define stored procedures. It has always been the opinion of the Colle team that stored procedures are bad, but on many projects they have become a necessary evil. Using stored procedure causes a project's business logic to become unnecessarily split. In a perfect world, projects would be written in one language and that language would be Java.

Since it would be practically impossible to write a stored procedure that runs on all relational databases, there is a mechanism to specify for which relational databases a stored procedure was written. This prevents TableBuilder from attempting to load the stored procedure on a system for which it was not intended. This mechanism could also be used to have seperate stored procedures for each supported platform.