[Abstract]

The set solver provides JDBC driver, which is easy to embed into Java program calls. The methods are similar to executing SQL and stored procedures in Java.

The structure diagram is as follows:

First, deploy JDBC to Java application project. In short, put the jar package and configuration file required to load the aggregator when starting Java application into the project. It should be noted that the required version of JDBC for the aggregator should not be less than One point six .

1. Load the driver jar

JDBC is similar to a database jdbc driver without physical tables. It can be simply regarded as a database with only stored procedures. In addition, JDBC is a fully embedded computing engine, which has completed all operations in JDBC. Unlike the database, JDBC is only an interface, and the actual operations are completed in an independent database server.

If you are in a web application project, you can put these jar packages in the WEB-INF/lib directory. There are three basic jar packages required for JDBC, which can be found in the [installation directory] \ esProc\lib Directory:

dm.jar  //Computing engine and JDBC driver package
icu4j_3_4_5.jar  //Deal with internationalization
jdom.jar  //Resolve profile dm.jar / / collector computing engine and JDBC driver package icu4j_3_4_5.jar / / handle internationalization jdom.jar / / parse configuration file

In addition to the above required jars, there are also some jar packages to complete specific functions:
For example, if the database is used as the data source, the corresponding database driver jar package is also needed;
To read and write Office files, you need to add poi*.jar and xmlbeans.jar ;
To use the drawing graphics function, you need to add the jar package related to SVG graphics processing, including batik*.jar js.jar ,pdf-transcoder.jar ,xalan-2.6.0.jar , xercesImpl.jar ,xml-apis.jar ,xml-apis-ext.jar .

2. Deployment raqsoftConfig.xml

The collector also has an important configuration file raqsoftConfig.xml , which can be found in [installation directory] \ esProc\config. It needs to be copied and placed in the class path of the application project. The name of the configuration file cannot be changed.
At raqsoftConfig.xml In the file, the authorization information, the main path of the concentrator, and the addressing path of the dfx file are configured. Let's take a look at the most basic configuration, that is, the authorization file configuration of the totalizer:

<?xml version="1.0" encoding=" UTF-8"?>
< Config Version="2">
 <Runtime>
 <Esproc>
<! -- the authorization file configuration of the totalizer can be absolute path or relative path. When relative path is used, it is relative to class path -- >
    <license>esproc.xml</license>
<! -- the trial authorization file can be downloaded from the official website of Runqian company -- >
 </Esproc>
 </Runtime>
</Config><?xml version="1.0" encoding=" UTF-8"?>< Config Version="2">
 <Runtime>
 <Esproc>
<! -- the authorization file configuration of the totalizer can be absolute path or relative path. When relative path is used, it is relative to class path -- >
    <license>esproc.xml</license>
<! -- the trial authorization file can be downloaded from the official website of Runqian company -- >
 </Esproc>
 </Runtime></Config>

Java call

In the java program, calling SPL script is similar to calling SQL and stored procedure in java.

Execute SPL statement

For example, create a data table, add two fields baseNum and square 2, respectively, and insert 100 records consisting of natural numbers within 100 and their square values into the data table, and finally return the data in the table as the result set.

The Java code is as follows:

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://");
    //Directly execute SPL statement and return result set
    st = (PreparedStatement)con.createStatement();
    ResultSet rs = st.executeQuery("=100.new(~:baseNum,~*~:square2)");
    //Simply process the result set and output the field name and 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 connection
   if (con!=null) {
        con.close();
   } 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://"); / / directly execute the SPL statement and return the result set
    st = (PreparedStatement)con.createStatement();
    ResultSet rs = st.executeQuery("=100.new(~:baseNum,~*~:square2)");    //Simply process the result set and output the field name and 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 connection
   if (con!=null) {
        con.close();
   }

Execution result:

Accessing local files in SPL

Through SPL, you can also access local files, including Txt, Excel, Json, Csv, Ctx and other types of files. When you access them, you can check the file location through the absolute path or through the relative path. When you use the relative path, it is relative to the home directory in the configuration file. Therefore, first, we will configure the following home directory:
At raqsoftConfig.xml Add the following nodes to the file's node < esproc > < esproc >

<! -- the main path of the collector, which is a single absolute path -- >
< mainpath > D: \ mainfile < / mainpath > <! -- the main path of the collector, which is a single absolute path -- >
 <mainPath>D:\mainFile</mainPath>

We will call the file employee.txt When you put it in the main directory and call it in JAVA, the code that sets up connections, output results and other parts is exactly the same as the previous example. Calling SPL statements is as follows:

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

Here, we support the use of absolute path and relative path. In Java, we use backslash \ to indicate escape character.

Execution result:

For this simple operation, you can also use simple SQL syntax:

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

Where $() indicates access to the local file system, and the result sets of the two writing methods are the same.

SPL statement with parameters

Parameters are an important part of SQL statements. Similarly, the use of parameters is also supported in SPL statements. For example, in the example above, to query employee.txt Data in the file, but only records with wages between 12000 and 20000 are required to be queried and sorted in ascending order according to wages:

Call part of the code as follows:

PreparedStatement pst = con.prepareStatement("$()select * from employee.txt where SALARY > ? and SALARY< ? order by SALARY");
 //Set parameters
 pst.setObject(1,12000);
 pst.setObject(2,20000);
 ResultSet rs = pst.executeQuery(); PreparedStatement pst = con.prepareStatement("$()select * from employee.txt where SALARY > ? and SALARY< ? order by SALARY"); //Set parameters
 pst.setObject(1,12000);
 pst.setObject(2,20000);
 ResultSet rs = pst.executeQuery();

In which? Represents parameters, and then assigns values to the above parameters one by one through setObject().

Execution result:

SPL statement with data source

Since JDBC is a data computing engine, one of the important ways of data source is database. How to call SPL statements with data source in JAVA? Look down:
Before JAVA calls SPL statement with data source, you need to add corresponding database driver in the application project, and then in the configuration file raqsoftConfig.xml Configure data source information in.
For example, if the data source name used in the SPL statement is demo and the database type is HSQL, the configuration is as follows:
First, drive the data set of HSQL hsqldb.jar Load into the application project;
Second, in raqsoftConfig.Xml Configure data source information in the < runtime > < runtime > node of:

<DBList>
<DB name="demo"> <!--Data source name-->
<property name="url" value="jdbc:hsqldb:hsql://127.0.0.1/demo ">
<property name="driver" value="org.hsqldb.jdbcDriver" ></property> <!--Database driven-->
<property name="type" value="13" ></property> <!--Database type-->
<property name="user" value="sa" ></property> <!--user name-->
<property name="password" value=""></property> <!--password-->
<property name="batchSize" value="1000" ></property>
<property name="autoConnect" value="true" ></property><!--Whether to connect automatically, if set to true，You can use the $initial  SQL Statement to access the database if false，It will not be automatically connected and must be used before use connect(db)Statement to create a database connection-->
<property name="useSchema" value="false" ></property>
<property name="addTilde" value="false" ></property>
<property name="dbCharset" value="UTF-8" ></property>
<property name="clientCharset" value="UTF-8" ></property>
<property name="needTransContent" value="false" ></property>
<property name="needTransSentence" value="false" ></property>
<property name="caseSentence" value="false" ></property>
</DB>
</DBList><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. Jdbc driver "/ >! -- database driver -- > < property name="type "value="13 "/ > <! -- database type -- > < property name="user "value="sa "/ > <! -- user name -- > < property name="password "value =" "/ > name="autoConnect" value="true" / > <! -- whether to connect automatically. If it is set to true, you can directly access the database with SQL statements beginning with $. If it is false, it will not connect automatically. Before using, you must use the connect(db) statement to create a database connection -- < 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>

Now we use SPL to query the SALES table from the demo data source, and filter out all the order information of employees with sellrid 3 from November 11, 2014 to December 12, 2014:

Call part of the code as follows:

PreparedStatement pst = con.prepareStatement("$(demo)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 result set
 ResultSet rs = pst.executeQuery();  PreparedStatement pst = con.prepareStatement("$(demo)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 result set
 ResultSet rs = pst.executeQuery();

The result set output is as follows:

Execute SPL script

After JAVA integrates JDBC, it can not only directly execute single sentence SPL statements, but also call more complex SPL scripts (files with suffix of dfx).
For example, the following dfx file:

SPL script idea:
Cycle through the records in the CITIES table, filter the STATES table through the CITIES. STATES. If the initial of the STATES. ABBR is arg1, assign the NAME value in the STATES table to the CITIES. STATE, splice the records in the CITIES table into the B1 cell, and finally return the result set of the B1 cell.
In this grid file, you need to get data from the data source demo and use the parameter arg1:

The data source configuration method can refer to the above example. The grid file is saved as city.dfx, and the DFX file can be stored in the application project classpath or the main directory configured in raqsoftConfig.xml. When there are many DFX files, we can also put the DFX file in the DFX addressing path for unified maintenance and management. The addressing path is configured as follows:
In the < esproc > < esproc > node of the raqsoftConfig.xml file, add the following:

<dfxPathList>
<! -- configure the addressing path of dfx file, which is an absolute path. You can set mu lt iple paths separated by ";" -- >
   <dfxPath>D:\dfxFile</dfxPath>
</dfxPathList><dfxPathList>
<! -- configure the addressing path of dfx file, which is an absolute path. You can set mu lt iple paths separated by ";" -- >
   <dfxPath>D:\dfxFile</dfxPath></dfxPathList>

Call part of the code as follows:

//Call the stored procedure through call, where city is the file name of dfx, where "? Represents the parameter, and multiple parameters are separated by commas
  st =con.prepareCall("call city(?)");
  st.setObject(1, "A");
  //Get result set
  ResultSet rs = st.executeQuery();  //Call the stored procedure through call, where city is the file name of dfx, where "? Represents the parameter, and multiple parameters are separated by commas
  st =con.prepareCall("call city(?)");
  st.setObject(1, "A");  //Get result set
  ResultSet rs = st.executeQuery();

In addition to the above writing method, the dfx file can be called through call, and parameters can also be passed directly when calling dfx, as follows:

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

Execution result:

When calling dfx files in Java, you can also omit call and use dfx directly (... )There are also two ways to write this usage, as follows:

//dfx names are separated from parameters by spaces, and multiple parameters are separated by commas
  st =con.prepareCall("city ?");
  //Set parameters
  st.setObject(1,"A");  //dfx names are separated from parameters by spaces, and multiple parameters are separated by commas
  st =con.prepareCall("city ?");  //Set parameters
  st.setObject(1,"A");

Or:

st =con.prepareCall("city \"A\""); st =con.prepareCall("city \"A\"");

The result sets obtained by the above methods are the same.
The above content is a common way for Java to call SPL script, and for other programs to call SPL:

Birt calls SPL script
JasperReport calls SPL script
How to call SPL script
How to call SPL script remotely in Java
HTTP call SPL

If you want to know more about how to use it, you can go to the official website Online tutorial View in