esProc provides its own JDBC driver to become integration-friendly with a Java application. The method of calling an esProc SPL script is similar to the execution of SQL queries and stored procedures in Java.

Deploying esProc JDBC in a Java application

Simply put, to deploy JDBC in a Java application is to put in place the necessary jars and configuration files for loading esProc when starting the application. esProc JDBC requires JDK 1.8 or a higher version.

1. Load driver jars

esProc JDBC is like a database JDBC driver without physical tables. It can be regarded simply as a database that only supports the stored procedure. It has a built-in computing engine that can perform all calculations independently. A database’s JDBC, however, functions only as an interface and an extra standalone server is needed to perform calculations.
The necessary third-party jars are located in [installation directory]\esProc\lib.
esProc JDBC has two basic jars:

esproc-bin-xxxx.jar  //esProc computing engine and JDBC driver

icu4j*.jar  //Handle internationalization

Besides, there are jars for fulfilling specific functionalities:

To use databases as the data sources in esProc JDBC, their driver jars are required.

To read and write an Office file, poi*.jar ,commons-collections*.jar,commons-compress*.jar,commons-io*.jar and xmlbeans*.jar are needed.

To use the graphic function in esProc JDBC, jars for SVG-typed image processing are required, including batik-all*.jar,xml-apis*.jar,xml-apis-ext*.jar and xmlgraphics-commons*.jar.

2. Configure raqsoftConfig.xml

raqsoftConfig.xml is an important configuration file for deploying esProc JDBC, which is located in [installation directory]\esProc\config. It should be copied and placed in the class path of the application project, and its name must not be changed.

The raqsoftConfig.xml file contains esProc main path, dfx file’s search path, and etc.

Java invocation

The following explains how to call a SPL script from Java.

Execute SPL queries

Create a data table consisting of two fields – baseNum (a natural number less than or equal to 100) and square2 (the number’s square), insert 100 records into it, and return the data as result set.

Java code:

 public void  runSPL() throws ClassNotFoundException, SQLException{
  Connection con = null;
  PreparedStatement st;
  ResultSet set ;
  //Establish a connection 
  Class.forName("com.esproc.jdbc.InternalDriver");
  con= DriverManager.getConnection("jdbc:esproc:local://");
  // Execute the SPL statement and return result set
  st = (PreparedStatement)con.createStatement();
  ResultSet rs = st.executeQuery("=100.new(~:baseNum,~*~:square2)");
  // Output field names and detailed data in the result set 
  ResultSetMetaData rsmd = rs.getMetaData();
  int colCount = rsmd.getColumnCount();
  for( int c = 1; c <= colCount;c++) {
  String title = rsmd.getColumnName(c);
  if( c > 1 ) {
  System.out.print("\t");
  }
  else{
  System.out.print("\n");
  }
  System.out.print(title);
  }
  while(rs.next()) {
  for(int c = 1; c<= colCount; c++) {
  if ( c > 1 ) {
  System.out.print("\t");
  }
  else {
  System.out.print("\n");
  }
  Object o = rs.getObject(c);
  System.out.print(o.toString());
  }
  }
  //Close the connection
  if(con!=null) {
  con.close();
  }

Result set:

Access a local file from SPL

You can access a local file from SPL. esProc supports multiple file types, such as TXT, Excel, JSON, CSV and CTX. Both absolute path and relative path can be used to find the desired file. A relative path is relative to the math directory set in the configuration file raqsoftConfig.xml. First, let’s look at how to configure the main directory.

Add the following node under < Esproc></ Esproc > in raqsoftConfig.xml:

 <!-- esProc main directory, which is an absolute path -->
 <mainPath>D:\mainFile</mainPath>

Put a to-be-called file, like employee.txt, into the main directory. Code for connecting data source connection and outputting result is the same as that in the previous instance. Here’s how to call the SPL statement:

ResultSet rs=st.executeQuery("=file(\"D:\mainFile\employee.txt\").import@t()");

Both an absolute path and a relative path can be used. The back slash \ is an escape character in Java.

Result:

As the calculation is simple, you can also use esProc simple SQL syntax to do it:

ResultSet rs=st.executeQuery("$()select * from employee.txt");

The symbol $() means accessing a local file system. The two phrases get same result.

Call SPL statements that connect to data source

Databases are one of the most important data sources of esProc JDBC. Let’s look at how to call a SPL statement that connects to the database.

First, put corresponding database driver jars into the application project; then set data source information in raqsoftConfig.xml. Suppose the data source name used in an SPL statement is demo and the database type is HSQL, then you can configure it like this:

First, load HSQL database driver jar hsqldb-2.7.3-jdk8.jar into the application project;

Second, set data source information in the node < Runtime>< /Runtime> in raqsoftConfig.xml.

<DBList>
<DB name="demo"> <!—data source name-->
 <property name="url" value="jdbc:hsqldb:hsql://127.0.0.1/demo" /> <!—url connection-->
 <property name="driver" value="org.hsqldb.jdbcDriver" /> <!—database driver-->
 <property name="type" value="13" /> <!—database type-->
 <property name="user" value="sa" /> <!—user name-->
 <property name="password" /> <!--password-->
 <property name="batchSize" value="1000" />
 <property name="autoConnect" value="true" /> <!-- Automatically connect or not. If the value is true, use a SQL statement starting with $ to access the database; if it is false, there will be no automatic connection; establish the connection with connect(db) statement before you can access the database -->
 <property name="useSchema" value="false" />
 <property name="addTilde" value="false" />
 <property name="dbCharset" value="UTF-8" />
 <property name="clientCharset" value="UTF-8" />
 <property name="needTransContent" value="false" />
 <property name="needTransSentence" value="false" />
 <property name="caseSentence" value="false" />
</DB>
</DBList>

Parameters are an important part of a SQL statement. So are they in SPL statements. Now we’ll query SALES table from demo in SPL to find records whose SELLERID is 3 and ORDERDATE is from 2014-11-11 to 2014-12-12:
Here’s part of the code for calling the SPL script:

  PreparedStatement pst = con.prepareStatement("=connect(\"demo\").query(\"select * from SALES  where  SELLERID = ? and  ORDERDATE>? and ORDERDATE<?\")");
 //Set parameters
 pst.setObject(1,"3");
 pst.setObject(2, java.sql.Date.valueOf("2014-11-11"));
 pst.setObject(3, java.sql.Date.valueOf("2014-12-12"));
 //Get the result set
 ResultSet rs = pst.executeQuery();

The question mark (?) represents a parameter, whose value will be assigned by setObject().

Result set:

Execute SPL script

By integrating esProc JDBC, Java can call and execute a single SPL statement as well as the complicated SPL script (whose extension is dfx).

A dfx file:

	A	B	C
1	=demo.query("select NAME as CITY, STATEID as STATE from   CITIES")	[]
2	for A1	=demo.query("select * from STATES where   STATEID=?",A2.STATE)
3		if left(B2.ABBR,1)==arg1	>A2.STATE=B2.NAME
4			>B1=B1|A2
5	return B1

Explanation:

There are two tables – CITIES and STATES. Traverse records in CITIES and find corresponding STATES record by CITIES. STATES, during which if the first letter of STATES.ABBR is equal to parameter arg1, then assign STATES.NAME to CITIES.STATE, and add this record in CITIES to B1. The final result set returned is B1’s value.

The above cellset file receives data from the data source demo and uses parameter arg1:

See the above instances for the data source configuration. The cellset file is saved as city.dfx. A dfx file can be saved in an application project’s class path or in the main directory set in raqsoftConfig.xml. If there are many dfx files, you can put them in the dfx’s search path for easy management and maintenance. Below is how to configure a search path:
Add the following content under < Esproc> <\Esproc> in raqsoftConfig.xml:

<dfxPathList>
 <!-- Configure dfx file’s search path, which is an absolute path; multiple paths are allowed, which are separated by the semicolon -->
 <dfxPath>D:\dfxFile</dfxPath>
 </dfxPathList>

Here’s part of the code for calling the SPL script:

// Call a stored procedure with call method; city is the name of the dfx file, the question mark (?) represents a parameter; multiple parameters are separated by comma
 st =con.prepareCall("call city(?)");
 st.setObject(1, "A");
//Get the result set
  ResultSet rs = st.executeQuery();

Or you can pass the parameter to the dfx file when calling it:

st=con.prepareStatement("call city(\"A\")");
// Get the result set
ResultSet rs = st.executeQuery();

Result set:

For more related information, see our online Tutorial on the Raqsoft website.