org.aris.hldb
Class Sql

java.lang.Object
  extended by org.aris.hldb.Sql

public class Sql
extends java.lang.Object

This class adds to an hlCS pool queries from a hl query text file (.query). It is also responsible for the conversion of queries so that they are compatible with all supported databases.
First you have to create one or more .query files with sql commands. Example of sql.query file for sql server :


# ==============================================================================
# SELECT QUERY. key="aselect".
# This query has 2 parameters, one NVARCHAR(100) and one INTEGER.
# The code to call this would be:
# CallableStatement cs=allocCS("aselect");
# cs.setString(1, "sometext");
# cs.setInt(2,someNumber);
# ResultSet rs=cs.executeQuery();
# ==============================================================================
[aselect]
SELECT * FROM TABLE_NAME WHERE text=? AND id=?
NVARCHAR(100)
INTEGER
# ==============================================================================
# AN UPDATE SQL .Query's key="b_update" (code to allocate: allocCS("c_update"))
# ==============================================================================
[b_update]
UPDATE TABLE_NAME SET text=? WHERE id=?
NVARCHAR(50)
INT
# ==============================================================================
# An INSERT. key="c_insert". Also this will return a resultset with the new
# id of the inserted item.
# ==============================================================================
[c_insert]
{
INSERT INTO TABLE_NAME(text,other) VALUES (?,?)
SELECT @@IDENTITY
}
NVARCHAR(50)
NVARCHAR(50)
# ==============================================================================
# A simple example with |paramname,paramtype| parameter.
# ==============================================================================
[SIMPLE]
SELECT * FROM Users WHERE login=|my_param_name,nvarchar(50)|

# ==============================================================================
# More complex: insert an entry to the A and B tables, identity fields included.
# As you can see, we can add stored procedure code too, along with multiple sql
# commands. Also, check the |paramname,paramtype| parameter passing format,
# mixed with ? parameters.
# ==============================================================================
[iAB]
{
SET IDENTITY_INSERT A ON
INSERT INTO A(ID,Path) VALUES(|ID,int|,?)
INSERT INTO B(ID, thedate, shortDescr,type) VALUES (|ID,int|, GETDATE(), ?, 'text/html')
SELECT @@IDENTITY
}
NVARCHAR(1024)

Save this file as sql.query. You can load it when constructing this object. When this object loads the query file, and is on debugging mode (during development) it automatically creates stored procedures for each query found. If the program is in deployment mode (Common.debug=Common.createSPs=false), it uses the stored procedures already created by debug runs.
The hlSql object creates a CS pool for each key in the .query. The CS pool is saved in the hlCS pool associated with it.

Version:
1.0
Author:
Konstantine Kougios

Constructor Summary
Sql(CS hlcs, java.lang.Class sqlSPParser)
          Construct the object and define that this hlSql will write to a specific hlCS pool.
 
Method Summary
 void Add(java.io.Reader rdr, int resultSetType, int resultSetConcurency, int maxInPool)
          Reads the query from a Reader and creates the stored procedures for each sql command.
 void Add(java.lang.String filename, int maxInPool)
          Reads the .query file and creates the stored procedures for each sql command.
 void Add(java.lang.String filename, int resultSetType, int resultSetConcurency, int maxInPool)
          Reads the .query file and creates the stored procedures for each sql command.
 void AddSql(java.lang.String key, java.lang.String sql, int maxInPool)
          Parses and adds an sql to the CS pool.
 void AddSql(java.lang.String key, java.lang.String sql, int resultSetType, int resultSetConcurency, int maxInPool)
          Parses and adds an sql to the CS pool.
 
Methods inherited from class java.lang.Object
equals, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait
 

Constructor Detail

Sql

public Sql(CS hlcs,
           java.lang.Class sqlSPParser)
    throws java.lang.InstantiationException,
           java.lang.IllegalAccessException
Construct the object and define that this hlSql will write to a specific hlCS pool.

Parameters:
sqlSPParser - This defines the class that will be used for parsing sql's and creating Sp's. I.e. SqlServer for microsoft's sql server database.
hlcs - The hlCS instance where new queries will be appended.
Throws:
java.lang.InstantiationException - -
java.lang.IllegalAccessException - -
Method Detail

Add

public void Add(java.lang.String filename,
                int maxInPool)
         throws java.io.FileNotFoundException,
                java.io.IOException,
                java.lang.Exception
Reads the .query file and creates the stored procedures for each sql command. It then creates and associates those SP's with the linked CS pool. The Sp's resultsets will have the ResultSet.TYPE_FORWARD_ONLY and ResultSet.CONCUR_READ_ONLY flags.

Parameters:
filename - The file name of the file containing the hl query definitions.
maxInPool - The pool tries to keep maximum CallableStatements below this figure, but if more CallableStatements are needed, they will be allocated
Throws:
java.io.FileNotFoundException - If the .query file is not found.
java.io.IOException - If some error occurs during the parsing of the .query file.
java.lang.Exception - If the structure of the .query file is incorrect, an exception will be thrown with the line containing the error.

AddSql

public void AddSql(java.lang.String key,
                   java.lang.String sql,
                   int resultSetType,
                   int resultSetConcurency,
                   int maxInPool)
            throws java.io.FileNotFoundException,
                   java.io.IOException,
                   java.lang.Exception
Parses and adds an sql to the CS pool. The sql string should also contain the correct parameter format (newlines '\n' included). I.e.
 
 UPDATE tmp SET text=? WHERE id=? NVARCHAR(50) INT
 
 
.

Parameters:
key - The key of this CS. I.e. "myquery"
sql - The sql command along with the parameters (\n included)
maxInPool - The pool tries to keep maximum CallableStatements below this figure, but if more CallableStatements are needed, they will be allocated
Throws:
java.io.FileNotFoundException - -
java.io.IOException - -
java.lang.Exception - -

AddSql

public void AddSql(java.lang.String key,
                   java.lang.String sql,
                   int maxInPool)
            throws java.io.FileNotFoundException,
                   java.io.IOException,
                   java.lang.Exception
Parses and adds an sql to the CS pool. The sql string should also contain the correct parameter format (newlines '\n' included). I.e.
 
 UPDATE tmp SET text=? WHERE id=? NVARCHAR(50) INT
 
 

Parameters:
key - The key of this CS. I.e. "myquery"
sql - The sql command along with the parameters (\n included)
maxInPool - The pool tries to keep maximum CallableStatements below this figure, but if more CallableStatements are needed, they will be allocated
Throws:
java.io.FileNotFoundException - -
java.io.IOException - -
java.lang.Exception - -

Add

public void Add(java.lang.String filename,
                int resultSetType,
                int resultSetConcurency,
                int maxInPool)
         throws java.io.FileNotFoundException,
                java.io.IOException,
                java.lang.Exception
Reads the .query file and creates the stored procedures for each sql command. It then creates and associates those SP's with a CS pool.
This function is used if you want to specify the resultSetType and Concurency of the resultSets returned from the queries in the .query file.

Parameters:
filename - The file name of the file containing the hl query definitions.
resultSetType - Defines the resultSet type for the resultsets returned when executing the queries defined in the .query file.
resultSetConcurency - Defines the resultSet concurency for the resultsets returned when executing the queries defined in the .query file.
maxInPool - The pool tries to keep maximum CallableStatements below this figure, but if more CallableStatements are needed, they will be allocated
Throws:
java.io.FileNotFoundException - If the .query file is not found.
java.io.IOException - If some error occurs during the parsing of the .query file.
java.lang.Exception - If the structure of the .query file is incorrect, an exception will be thrown with the line containing the error.

Add

public void Add(java.io.Reader rdr,
                int resultSetType,
                int resultSetConcurency,
                int maxInPool)
         throws java.io.FileNotFoundException,
                java.io.IOException,
                java.lang.Exception
Reads the query from a Reader and creates the stored procedures for each sql command. It then creates and associates those SP's with a CS pool.

Parameters:
rdr - The Reader containing the hl query definitions.
resultSetType - Defines the resultSet type for the resultsets returned when executing the queries defined in the .query file.
resultSetConcurency - Defines the resultSet concurency for the resultsets returned when executing the queries defined in the .query file.
maxInPool - The pool tries to keep maximum CallableStatements below this figure, but if more CallableStatements are needed, they will be allocated
Throws:
java.io.FileNotFoundException - If the .query file is not found.
java.io.IOException - If some error occurs during the parsing of the .query file.
java.lang.Exception - If the structure of the .query file is incorrect, an exception will be thrown with the line containing the error.