How to Call an SPL Script in Java
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.
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.
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.
esProc JDBC has two basic jars, which are all situated in [installation directory]\esProc\lib. With a web application project, these jars can be put in WEB-INF\lib.
esproc-bin-xxxx.jar //esProc computing engine and JDBC driver
icu4j-60.3.jar //Handle internationalizationBesides, 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 and xmlbeans-4.0.0.jar are needed.
To use the graphic function in esProc JDBC, jars for SVG-typed image processing are required, including batik*.jar, js-1.0.jar, pdf-transcoder-1.0.jar, xalan-2.7.0.jar, xercesImpl-2.12.1.jar, xml-apis-xerces-2.7.1.jar and xml-apis-ext-2.5.0.jar.
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.
The following explains how to call a SPL script from Java.
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:
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.
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.2.8.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:
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.