Packages: org.aris.OODB org.aris.hldb org.aris.cache.UniversalCache
Documentation : Javadoc
org.aris.hldb : A package for fast
database access and ease of development
Version : 1.0Beta
Author : Konstantine
Kougios
Known bugs : None so far.
Downloads
Included in org.aris.OODB
Purpose
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.
Package
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.
Usage
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.
Example 1: Java code for initializing the hldb
// 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);
// and add a .sql file to
the cs CallableStatement
pool. Maximum CallableStatements
// pooled will be 100.
sql.Add(new
InputStreamReader(new FileInputStream("queries.sql")),ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY,100);
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:
Example 2: A query file
#
========================================================================
# 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:
Example 3: Calling a query
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.
Example 4: A query which uses a different
parameter syntax
[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.
Using
an Ant script to create the stored procedures
An ant script can be used to create the stored
procedures. Here is an example:
Example 5: An ant script
which creates the stored procedures.
<!--
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>