Code Examples of Processing json Data with esProc

esProc can process json data. Here we'll introduce some of the applications through examples. 1. Analyzing and generating json data with esProc; 2. Data-interchange between esProc and application program through json; 3.Reading json file data in esProc.

A. Analyzing and generating json data with esProc

Generally speaking, json is a format used by webpage js program and Java's server-side program (such as servlet)to interchange data. While data access between Java's server-side program and the databases adopts SQL result set format. esProc can act as an intermediary in the data computation and data-interchange between the two formats.

In this example, we use esProc to query detailed information of a group of designated employees. Both data input and output will adopt json format. Table employee of database demo contains all information of the employees:

esProc receives an EID list of json format and returns corresponding detailed information of employees in json format. The code is as follows:

1. esProc program test.dfx receives a parameter: jsonEID.

2. esProc completes json analysis, data processing and generates results in json format:

A1: Connect to database demo.

A2: Retrieve data from table employee.

A3: Use import@j function to parse the inputting jsonEID parameter (EID list in json format) and generate a table sequence containing only one field EID.

A4: Use align function to get from users data the employee information designated by A3.

A5: Convert employee information into json strings. 

A6: Return employee information of json format.

B.Interchanging json data between esProc and Java application

In the above example, esProc program is saved as test.dfx file to be called by Java application. Steps for calling the file are as follows:

1.Deploy esProc in Java application.
See esProc Tutorial for detail.

2.Call test.dfx in Java application.

Code example is as follows:

public void testDataServer(){

                   Connection con = null;

                   com.esproc.jdbc.InternalCStatementst;

                   try{

                            //Usersidlist injson format can be transmitted from browser-side to the program and converted into strings for use. Here process of receiving json data is omitted and value is assigned directly

                            String jsonEid="[{EID:8},{EID:32},{EID:44}]";

                            //Create a connection

                            Class.forName("com.esproc.jdbc.InternalDriver");

                            con= DriverManager.getConnection("jdbc:esproc:local://");

                            //Call stored procedure. test is the file name of dfx

                            st =(com.esproc.jdbc.InternalCStatement)con.prepareCall("call test(?)");

                            //Set parameters

                            st.setObject(1,jsonEid);

                            //Execute stored procedure

                            st.execute();

                            //Get result set

                            ResultSet set = st.getResultSet();

                            String jsonEmployee=null;

                            if (set.next()) jsonEmployee=set.getString(1);

                            //After getting detailed user information injson format, convert it into json objects and return them to browser-side. How to usejsonEmployee is omitted here

                   }

                   catch(Exception e){

                            System.out.println(e);

                   }

                   finally{

                            //Close the connection

                            if (con!=null) {

                                     try {

                                               con.close();

                                     }

                                     catch(Exception e) {

                                               System.out.println(e);

                                     }

                            }

                   }

}

C. Reading and processing jsonfile data inesProc

JSON file test.json contains information including class, serial number, names, subjects,scores, etc. Format is as follows:

[

    {

        "class": "Class one",

        "id": 1,

        "name": "Emily",

        "subject": "English",

        "score": 84

    },

    {

        "class": "Class one",

        "id": 1,

        "name": "Emily",

        "subject": "Math",

        "score": 77

    },

    ......

    {

        "class": "Class one",

        "id": 7,

        "name": "Nicholas",

        "subject": "PE",

        "score": 60

    }

]

It is convenient for esProc to perform the reading and computation of JSON data. After that the result is submitted to Java application in the format of JDBC result set. Steps are as follows:

1. Developing esProc script

Use esProc editor to develop script (fromJSON.dfx), read the jsonfile, analyze it and complete the computation:

A1: Use read() to read json file in string format;

A2: Use import@j() function to parse the json file into a table sequence;

A3: Group students ID and summarize the total scores in A4;

A5: Sort by total scores in descending order and return result set through result in A7

2. Java application callsfromJSON.dfx to present result.

Steps are omitted here for they are almost the same as those in the above example.

A Handy Method of Accessing json Data in Java

It is possible that json data is needed to be processed in Java projects. Here we'll introduce a handy method of accessing data of json files through an example. Ajson file, test.json, contains information of class, serial numbers, names, subjects and scores,etc. In this example, data will be imported, sorted by scores in descending order and written to the file test_result.json. The format of test.json is as follows:
[
    {
        "class": "Class one",
        "id": 7,
        "name": "Nicholas",
        "subject": "PE",
        "score": 60
    },
{
        "class": "Class one",
        "id": 1,
        "name": "Emily",
        "subject": "English",
        "score": 84
},
    ......

    {
        "class": "Class one",
        "id": 1,
        "name": "Emily",
        "subject": "Math",
        "score": 77
}
]

The open source project json-lib is needed to be imported into Java. To do this, the necessary packages include:
json-lib-2.4-jdk15.jar
ezmorph-1.0.6.jar
commons-lang.jar
commons-beanutils.jar
commons-logging.jar
commons-collections.jar

With the json-lib available, way of coding will be like this:
1.Import data from the file, parse them into jsonArr, the json object, using json-lib's JSONArray. 

2.Sort the values of “score” of jObject, representing all members of jsonArr object, in descending order using comparison sort algorithm. 

3.Write the sorted jsonArr to the file. 

The code is as follows: 
public static void myJson() throws Exception{
//then import data from the file
File file = new File("D:/file/test.json"); 
FileInputStreamfis = null; 
fis = new FileInputStream(file); 
InputStreamReader input = new InputStreamReader(fis); 
BufferedReader reader = new BufferedReader(input); 
String laststr = ""; 
   String tempString = null;
while ((tempString = reader.readLine()) != null) {
laststr = laststr+ tempString;
   }
reader.close();
   //then parse the imported data into json object
JSONArrayjsonArr = JSONArray.fromObject(laststr ); 
   //then sort the json data (in descending order)
JSONObjectjObject = null;
for(inti = 0;i<jsonArr.size();i++){
long l = Long.parseLong(jsonArr.getJSONObject(i).get("score").toString());
for(int j = i+1; j<jsonArr.size();j++){
longnl = Long.parseLong(jsonArr.getJSONObject(j).get("score").toString());
if(l<nl){
jObject = jsonArr.getJSONObject(j);
jsonArr.set(j, jsonArr.getJSONObject(i));
jsonArr.set(i, jObject);
}
}
}
//then write the result to the file
FileOutputStream out = new FileOutputStream("D:/file/test_result.json"); 
out.write(jsonArr.toString().getBytes()); 
out.close(); 
}

my Json function has been able to access and sort json data, but it lacks some universality. When sorting in ascending order or by several fields is required, program has to be modified. But if we want to make the function perform more universal and flexible sorting as SQL does, analysis of dynamic expressions will be required, which will result in quite complicated code. 

esProc can help Java with accessing and processing json data. It has the advantage of creating dynamic sorting expressions using simple code. It can deal with tasks about json data, like importing, accessing, computing and writing to the file, conveniently. In order to perform dynamic sorting, a sorting expression can be transferred to esProc as a parameter as follows: 

The value of parameter sortBy is score:-1. Only 5 lines of code are needed to develop a program of sorting json data in esProc. Please see below: 

A1: Use read() to load data from the json file in the format of strings. (esProc's developing tool can display the computed result visually, as shown in the left part of the above figure).

A2: Parse json data into a table sequence using the import@j() method.

A3: Sort the data; then esProc will first compute the parameter sortBy in macro ${sortBy}. After that, the statement to be executed is A2.sort(score:-1), meaning sorting by score in descending order.

A4: Parse the sorting result into strings of json format.

A5: Output the stings to the file.

If the fields for and way of sorting are changed, what we need is to modify parameter sortBy instead of the program. For instance, sort by id in descending order and by score in ascending order. We may just modify sortBy into id:-1,score:1. The statement of sorting we finally execute is A2.sort(id:-1,score:1), and the result is as follows: 

This piece of esProc code can be called conveniently in Java using jdbc provided by esProc. To save the above esProc program as test.dfx file, Java will call the following code:

//create a connection between esProc and jdbc

Class.forName("com.esproc.jdbc.InternalDriver");

con= DriverManager.getConnection("jdbc:esproc:local://");

//call esProc program (the stored procedure), in which test is the file name of dfx

com.esproc.jdbc.InternalCStatementst;

st =(com.esproc.jdbc.InternalCStatement)con.prepareCall("call test(?)");

// set parameters

st.setObject(1,"id:-1,score:1");//esProc’s input parameters

// execute the esProc stored procedure

st.execute();

Here the relatively simple esProc code can be called directly in Java, so it is unnecessary to write esProc script file (like the above-mentioned test.dfx). See the following for detailed code:

st=(com. esproc.jdbc.InternalCStatement)con.createStatement();

st.executeQuery(">file(\"d:/file/test_result.json\").write(export@j(file(\"d:/file/test.json\").read().import@j().sort(score:-1)))");

The above Java code calls directly a line of esProc code, that is, import data from the text file, sort them according to specified fields and write the result to the file.