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.
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.
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{
//Usersid list in json 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 use json Employee
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);
}
}
}
}
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
}
]
1. Developing esProc script
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
Steps are
omitted here for they are almost the same as those in the above example.
没有评论:
发表评论