Loading and saving data from/to a DBMS
Using hash-indexes for increasing the performance
Add the file stels_engine.jar to your classpath or extract the jar file in the directory of the application.
The class name of the driver is jstels.jdbc.engine.EngineDriver
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 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 |
An SQL query must meet the following conditions:
It must corespond to the SQL syntax supported by the driver. For detailed information about supported SQL syntax see specification here.
À column using an SQL reserved word as a name or containing spaces and other delimiters must be quoted in double quotes in a query. For example, SELECT "Date", "My integer-column" FROM test
To use single quotes (') and backslashes (\) in a string constant you should use an escape character (\). For example: 'abcd\'ef\\g'
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 cityINSERT 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.' |
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 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)");
|
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); |
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.
Along with the standard data types, StelsEngine also supports processing arbitrary Java objects (classes).
For this purpose, you should observe the following conditions:
Specify the fully-qualified class name as the type of data in the column while creating a table (this class must have the public modifier)
Use the setObject(int parameterIndex, Object x) method of the PreparedStatement class for inserting an object into the table
Use the getObject() methods of the ResultSet class for fetching the object from the table
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).
For example:
//create the hash-index CREATE HASH INDEX indx_empl ON employess(id) |
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) |
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:
The method must have the public modifier
The number of arguments in the method is arbitrary, but they must belong to one of the java classes specified in the data types table
It is advisable to process the null values for arguments in the method code
The method can return the null value as well
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 ) {
if (d ==
null || format == null)
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" ); |