StelsEngine JDBC Driver v2.0 Documentation

 

 

Contents 

 

Installation

Driver Name

URL Syntax

Connection Example

Data types

Supported SQL Syntax

Driver Modes

Loading and saving data from/to a DBMS

Using a StelsEngine listener

Using Java objects

Using hash-indexes for increasing the performance

User-defined SQL functions

 

Installation

Add the file stels_engine.jar to your classpath or extract the jar file in the directory of the application.

 

Driver Name

The class name of the driver is jstels.jdbc.engine.EngineDriver

 

URL Syntax

The connection URL is jdbc:jstels:engine

 
Connection Example
This example code shows how the driver is used. You can download it here.
import java.sql.*;
 
public class EngineTest {

  public static void main(String[] args)

  {

    try

    {

      // load the driver into memory

      Class.forName("jstels.jdbc.engine.EngineDriver");

 

      // create a connection

      Connection conn = DriverManager.getConnection("jdbc:jstels:engine");

 

      // create a Statement object to execute the query with

      Statement stmt = conn.createStatement();

 

      // create a data table in memory

      stmt.execute("CREATE TABLE employee(id int, age int, name string(20), hiredate datetime)");

 

      // insert the records into the table

      stmt.execute("INSERT INTO employee(id, age, name, hiredate) VALUES (1,32,'Bill Adams',to_date('21-05-1992','dd-MM-yyyy'))");

      stmt.execute("INSERT INTO employee(id, age, name, hiredate) VALUES (1,25,'Mary Jones',to_date('12-06-1995','dd-MM-yyyy'))");

      stmt.execute("INSERT INTO employee(id, age, name, hiredate) VALUES (1,35,'Sue Smith',to_date('03-04-1992','dd-MM-yyyy'))");

      stmt.execute("INSERT INTO employee(id, age, name, hiredate) VALUES (1,24,'Dan Roberts',to_date('20-10-1999','dd-MM-yyyy'))");

      stmt.execute("INSERT INTO employee(id, age, name, hiredate) VALUES (1,29,'Paul Cruz',to_date('08-12-2000','dd-MM-yyyy'))");

 

      // execute a query

      ResultSet rs = stmt.executeQuery("SELECT * FROM employee");

 

      // read the data and put it to the console

      for (int j = 1; j <= rs.getMetaData().getColumnCount(); j++) {

        System.out.print(rs.getMetaData().getColumnName(j) + "\t");

      }

      System.out.println();

 

      while (rs.next())

      {

            for(int j=1; j <= rs.getMetaData().getColumnCount(); j++){

                   System.out.print(rs.getObject(j)+ "\t");

            }

            System.out.println();

      }

 

      // close the objects

      rs.close();

      stmt.close();

      conn.close();

    }

    catch(Exception e)

    {

      e.printStackTrace();

    }

  }

}

 
 
 

Data types

StelsEngine supports the following column data types:

Data Type

Java Class

JDBC Returned Type (java.sql.Types.*)

Integer

java.lang.Integer

java.sql.Types.INTEGER

Long

java.lang.Long

java.sql.Types.BIGINT

Float

java.lang.Float

java.sql.Types.FLOAT

Double

java.lang.Double

java.sql.Types.DOUBLE

String

java.lang.String

java.sql.Types.VARCHAR

Datetime

java.util.Date

java.sql.Types.TIMESTAMP

Boolean

java.lang.Boolean

java.sql.Types.BOOLEAN

Object

arbitrary java class

java.sql.Types.JAVA_OBJECT

 

 

Supported SQL Syntax

StelsEngine supports the most part of ANSI 92 grammar like SELECT, INSERT, UPDATE, DELETE and CREATE statements.

 

An SQL query must meet the following conditions:

Query examples:

SELECT SUM(a) AS col1, MAX(b) / MAX(c) AS col2 FROM test GROUP BY a  HAVING AVG(a) > 30

SELECT name FROM salesreps WHERE ( rep_office IN ( 22, 11, 12 ) )  OR ( manager IS NULL AND hire_date >= to_date ( '01-05-2002','dd-MM-yyyy' ) OR ( sales > quota AND NOT sales > 600000.0 )

SELECT city, target, sales FROM offices WHERE region = 'Eastern' AND sales > target ORDER BY city

INSERT INTO salesreps (name, age, empl_num, sales, title) VALUES ('Henry Smith', 35, 111, NULL, 'Sales Mgr')

DELETE FROM salesreps WHERE NAME LIKE 'Henry%' 

UPDATE customers SET credit_limit = 50000.00 WHERE company = 'Acme Mfg.'

 

 

Driver Modes

There are two main modes StelsEngine can run in: the mode with data caching and the mode with data swapping. Running in the first mode (default mode), the driver stores all tables in the RAM making it possible to achieve maximum performance. Keep in mind that Java Virtual Machine must have enough free memory allotted for storing large tables (use -Xms and -Xmx JVM options).

 

The second mode can be used to process large tables which cannot be fully stored in the RAM. To use this mode, set the driver property caching to false. There are also some properties for configuring this mode:

tempPath - directory where temporary files will be created (by default it is a OS temporary directory, specified by JVM environment variable "java.io.tmpdir").

maxRecordsInMemoryPerTable is used to define how many records should be saved in the RAM for each table (by default - 5000 records).
maxSwapStringSize - default size for string columns in swap files (by default - 50 characters).

 

Example:

Properties props = new Properties();

props.setProperty("caching", "false");         // switch to the swapping mode
props.setProperty("tempPath", "c:/temp");     
props.setProperty("maxRecordsInMemoryPerTable", "1000");
props.setProperty("maxSwapStringSize", "60");

Connection conn = DriverManager.getConnection("jdbc:jstels:engine", props);

 

To reduce the swap size and increase the overall performance in the swapping mode, we recommend to specify a column size for each string column while creating table. If this attribute is not specified, the column size by default is equals to the value of the maxSwapStringSize property.

Example:

stmt.execute("CREATE TABLE employee(id int, age int, name string(20), hiredate datetime)");

 

 

Loading and saving data from/to a DBMS

StelsEngine allows you to load and save data from/to various relational DBMS. To load data from a DB, you can use the following methods of the jstels.jdbc.engine.EngineConnection class: loadTableFromDB (Connection conn, String sqlQuery, String tableName) or loadTableFromDB (ResultSet rs, String tableName), where the parameter conn is the JDBC connection to the DB, sqlQuery is a query to the DB, rs is a variable of the ResultSet type, tableName is the name of the table the resulting data will be loaded to.

For example:

// connection to StelsEngine

Connection conn = DriverManager.getConnection("jdbc:jstels:engine");

// connection to a MySQL DBMS

Connection conn2 = DriverManager.getConnection("jdbc:mysql://localhost/mydatabase?user=username;password=password");

 

// load data from a DB table to a StelsEngine table by using an SQL query.

 ( (jstels.jdbc.engine.EngineConnection) conn).loadTableFromDB(conn2,"select * from test", "dbtest");

// load data from a DB by using a ResultSet object

 ( (jstels.jdbc.engine.EngineConnection) conn).loadTableFromDB(rs, "dbtest2");

 

To save data to the DB, use the method saveTableToDB (String sqlQuery, Connection conn, String dbCatalogName, String destTableName, boolean createTable), where the parameter sqlQuery is a SQL query to StelsEngine, conn - JDBC connection to the DB, dbCatalogName - catalog name in the DB (may be set to null), destTableName - the name of a destination table in the DB and createTable is used to create the destination table, if it does not exist in the DB:

// connection to StelsEngine

Connection conn = DriverManager.getConnection("jdbc:jstels:engine");

// connection to MySQL DBMS

Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/mydatabase?user=username;password=password");

 

// save data from a StelsEngine table to a MySQL table

 ( (jstels.jdbc.engine.EngineConnection) conn).saveTableToDB( "select * from stels_engine_test", conn2, null, "my_sql_test", false);

 

 

Using a StelsEngine listener

You can also add record listeners for each table in StelsEngine. It may be useful to synchronize data between StelsEngine and external data sources.

 

To add the listener to the table, you need to do the following:

1) Extend the jstels.jdbc.engine.EngineListener class

2) Call the method addTableListener (String tableName, jstels.jdbc.engine.EngineListener listener) of the jstels.jdbc.engine.EngineConnection class, where the tableName parameter is the name of the table the listener will be added for, listener - instance of the EngineListener class.

 

Example of a simple listener:

public class MyEngineListener

    extends EngineListener {

  public MyEngineListener () {

  }

 

  // ##### methods that you need to override #####

 

  public void recordInserted(Object[] newRecordObjects) {

    System.out.println("##### Record is inserted. New objects: " +

             objects2String(newRecordObjects));

  }

 

  public void recordUpdated(int id, Object[] oldRecordObjects,

                            Object[] newRecordObjects) {

    System.out.println("##### Record is updated. ID=" + id + ". Old objects: " +

               objects2String(oldRecordObjects) + ". New objects: " +

               objects2String(newRecordObjects));

  }

 

  public void recordDeleted(int id, Object[] oldRecordObjects) {

    System.out.println("##### Record is deleted. ID=" + id + " Old objects:" +

               objects2String(oldRecordObjects));

  }

 

  // ##########

 

 

  private String objects2String(Object[] objs) {

    StringBuffer sb = new StringBuffer();

    for (int i = 0; i < objs.length; i++) {

      sb.append(objs[i]);

      if (i < objs.length - 1)

        sb.append(", ");

    }

    return sb.toString();

  }

}

 

To remove a listener, call the method removeTableListener(String tableName, EngineListener listener) of the EngineConnection class.

 

 

Using Java objects

Along with the standard data types, StelsEngine also supports processing arbitrary Java objects (classes).

For this purpose, you should observe the following conditions:

For example:

package testpack;

 

// user object (class) 

public class Car {

  public int maxSpeed;

  public String name;

  public Car(String name, int speed) {

    this.name = name;

    this.maxSpeed = speed;

  }

 

  public int getSpeed() {

    return maxSpeed;

  }

 

  public String getName() {

    return name;

  }

}

 

import java.sql.*;

import testpack;

 

public class ObjectsTest {

  public static void main(String[] args) {

    try {

      // load the driver into memory

      Class.forName("jstels.jdbc.engine.EngineDriver");

 

      // create a connection

      Connection conn = DriverManager.getConnection("jdbc:jstels:engine");

 

      // create a Statement instance to execute the query with

      Statement stmt = conn.createStatement();

 

      // create a table that contains object data type

      stmt.execute("CREATE TABLE cars (id INTEGER, owner STRING, car \"testpack.Car\")");

 

      // create a PreparedStatement instance to insert values

      PreparedStatement pst = conn.prepareStatement(

          "INSERT INTO cars(id,owner,car) values(?,?,?)");

      pst.setInt(1, 1);

      pst.setObject(2, "John Smith");

      pst.setObject(3, new Car("Dodge Viper", 177));

      pst.execute();

 

      pst.setInt(1, 2);

      pst.setObject(2, "Mary Jones");

      pst.setObject(3, new Car("Porsche 964", 162));

      pst.execute();

 

      pst.setInt(1, 3);

      pst.setObject(2, "Dan Roberts");

      pst.setObject(3, new Car("Ferrari F430", 196));

      pst.execute();

 

      // execute a query

      ResultSet rs = stmt.executeQuery(

          "SELECT id, owner, car, car->getName(),car->maxSpeed FROM cars");

 

      while (rs.next()) {

        for (int j = 1; j <= rs.getMetaData().getColumnCount(); j++) {

          System.out.print(rs.getObject(j) + "\t");

        }

        System.out.println();

      }

 

    }

    catch (Exception e) {

      e.printStackTrace();

    }

  }

}

 

Note1: Using the "->" operator, you can call the methods of the object or read the values of its fields. This methods and fields of the object must have the public modifier

For example:

ResultSet rs = stmt.executeQuery(

          "SELECT car->name, car->maxSpeed, car->getName(),car->getSpeed() FROM cars");

 

Note2: For comparing values in the WHERE clause the class of the object must implement java.lang.Comparable interface.

For example:

// user object (class) 

public class Car implement java.lang.Comparable{

 

//previous methods

...

 

//implement the compareTo method

  public int compareTo(Object o) {

    Car car = (Car)o;

    return car.getName().compareTo(this.getName());

  }

}

 

Note3: You can also use your own SQL-functions for processing objects (see user-defined SQL functions).
 

 

Using hash-indexes for increasing the performance

You can use a hash-index to provide quick access to table strings based on the values of one or several columns (similar to DBMS indexes).

To create a hash-index use the following command:

CREATE HASH INDEX index_name ON table_name ( column1, column2, etc)

For example:

//create the hash-index

CREATE HASH INDEX indx_empl ON employess(id)

Notes:

To choose the data you need, you should specify the name of the hash-index in the expression FROM and also enumerate the values for the columns being indexed:

For example:

SELECT * FROM indx_empl(12345)

 

 

User-defined SQL functions

You can use your own SQL functions in the driver. To use this feature, you should do the following: 

1) Create a static method that will act as an SQL function
    Mind that:

For example:

package my_pack;

public class MyFuncs{

// user-defined SQL function that formats the given argument into a date/time string with specified format

public static String format_date( java.util.Date d, String format ) {
    // process the null values

    if (d == null || format == null)
    return null;
    java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat(format);
    // return a function result with java.lang.String type

    return sdf.format(d);
}
}

 

2) Register the function using the static method jstels.jdbc.engine.EngineDriver.registerFunction (String funcName, String handler). Where funcName argument is the name by which this method will be called in SQL queries and handler argument is the fully-qualified method name.

For example:

jstels.jdbc.engine.EngineDriver.registerFunction ( "format_date",  "my_pack.MyFuncs.format_date" )

 

Also, you can use the driver property function:<my_func>.

For example:

Properties props = new java.util.Properties();
props.put("function:formate_date","my_pack.MyFuncs.format_date");
...  
Connection conn = DriverManager.getConnection("jdbc:jstels:engine", props);
// or append this property to the URL
Connection conn2 = DriverManager.getConnection("jdbc:jstels:engine" + 
"?function:formate_date=my_pack.MyFuncs.format_date");

 

3) Call the function in an SQL query

For example:

Statement st = connection.createStatement();

st.execute( "select format_date( date_column , 'yyyy-MM-dd' ) from test" );

 

 

[HOME]   [TOP]