Version : 1.0Beta

Author : Konstantine Kougios

Known bugs : None so far.

 

 

Included in org.aris.OODB

The purpose of hldb is to make database coding of fast database access easier. hldb uses stored procedures to access the database, thus providing the fastest possible access. hldb provides a pool of CallableStatement's which use a pool of Connection's making calls to database an average 80% faster. Depending on the query and the database setup, the calls can be even much faster.

hldb separates the code from the queries by using either external .sql files. Creating queries in the code is of course possible too.

Currently, hldb supports sql server only, but additional database support can be added by an "parser" class, which generates stored procedure code from the .sql files.

The package includes the library along with usage examples. You will need the mssql drivers which can be found at microsoft's site. There are also some free sql servers drivers you can use, like the jtds drivers.

To use hldb typically you need 1 or more .sql files, and some initialization of the library. Suppose we have the queries.sql file which contains the queries, and we use sql server 2000. Our classpath shall contain the hldb package along of course with the mssql driver jars.

First we have to create the stored procedures from the .sql file. This can be done in the code, or externally by using an ant script. The example 1 creates the stored procedures too.

// imports

import org.aris.hldb.CS;
import org.aris.hldb.Common;
import org.aris.hldb.ConnectionPoolProvider;
import org.aris.hldb.Sql;
import org.aris.hldb.servers.SqlServer;

// initialization code

Common.setCreateSPs(true); // set this to false if stored procedures are created externally, i.e. with an ant script

// get a connection pool with maximum of 10 connections to the database

ConnectionPoolProvider cpp=new ConnectionPoolProvider("jdbc:microsoft:sqlserver://localhost:1433;DatabaseName="+dbName+";User=USER;Password=******",10);

// get a CallableStatement pool by using this connection pool
CS cs=new CS(cpp);

// prepare a .sql file parser, using SqlServer parsing. CallableStatements are
// stored in cs.
Sql sql=new Sql(cs,SqlServer.class);

When the code executes, the hldb will create 1 stored procedure for each query in the queries.sql file.

Now lets see what the queries.sql contains:

 

# ========================================================================
# COMMENTS begin with # char.
# Northwind.Categories: Queries.
# SELECT_CategoriesBase is the name of this query. Later we use that
# name to gain access to it.
# ========================================================================

[SELECT_CategoriesBase]
{
SELECT * FROM [Categories] WHERE [CategoryID]=?
}
int

# Inserting into categories requires 3 parameters. These can be marked with the ? symbol.
# Note that we also provide the datatypes (bold characters) at the end of the declaration.
# One datatype per "?". These datatypes are exactly the same as defined in our tables.

[INSERT_CategoriesBase]
{
    -- we will use a transaction in order for identity to be correctly read
    begin transaction
    INSERT INTO [Categories]([CategoryName],[Description],[Picture])
        VALUES(?,?,?)

    SELECT @@IDENTITY
    commit transaction
}
nvarchar(15)
ntext
image

As you can see, even transactions can be coded in the queries. Because these queries are converted to stored procedures, any stored procedure code could be placed in the braces.

An example of how to call one of the queries:

CallableStatement cc=null;
try
{
    cc=cs.allocCS("SELECT_CategoriesBase");

    cc.setInt(1,CategoryID);

    ResultSet rs=cc.executeQuery();
    rs.next();

    ... read the data

    rs.close();
} finally
{
    cs.freeCS(cc); // this checks for null too.
}
 

In the case you want to use the same value twice, you can use a different syntax for providing parameters. Here is an example.

[DIFF_SYNTAX_SQL]
{

    SELECT * FROM MyTable WHERE myId=|MyIdParam,int| AND myLogin=|myLoginParam,nvarchar(20)|

    SELECT * FROM SecondTable WHERE myId=|MyIdParam,int| AND userName=|myLoginParam,nvarchar(20)|

}

Here we declared 2 parameters. The MyIdParam and the MyLoginParam. We defined those parameter names. Also, we defined the types of them : int and nvarchar(20). We couldn't use the "?" symbol, cause it creates one parameter per usage and here we needed the same parameters for both queries.

An ant script can be used to create the stored procedures. Here is an example:

<!--

      This file demonstrates how to create an ant task to automatically compile the sql files to stored procedures.

-->

 

<project name="hldbCompiler" default="compileSqls">

     

      <target name="compileSqls">

            <!--

                  In order for the tasks to be configured correctly, the classpath should contain

                  mssqlserver.jar;msutil.jar;msbase.jar;arislib.jar

            -->

            <taskdef name="hldbCompiler" classname="org.aris.hldb.compilers.QueryCompiler"/>

           

            <!--

                  Compile the .sql file to generate the stored procedures

            -->

            <echo>Creating stored procedures from .sql files.</echo>

            <hldbCompiler connection="jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=YOUR_DATABASE;User=YOUR_USER_NAME;Password=********"

                  queryFile="YOUR_SQL_FILE.sql"/>

                 

            <!--

                  Now our database contains the stored procedures, and we are ready to run our program, or

                  distribute the database.

                 

                  Please check the stored procedures section of the database to verify that the

                  stored procedures have been created.

            -->

      </target>

</project>