MyPage is a personalized page based on your interests.The page is customized to help you to find content that matters you the most.


I'm not curious

esProc Helps Process Structured Texts in Java - Alignment Join

Published on 17 November 14
0
0

The join statements of the database can be used conveniently to perform the operation of alignment join. But sometimes the data is stored in the text files, and to compute it in Java alone we need to write a large number of loop statements. This makes the code cumbersome. Using esProc to help with programming in Java can solve the problem easily and quickly. Let’s look at how this works through an example.

The text file emp.txt contains employee information, except that in which EId is 1. Another text file sOrder.txt contains information of sales orders in which field SellerId corresponds to field EId in emp and from which the information whose SellerId is 2 is excluded. Part of the original data is listed below:

emp.txt:
esProc Helps Process Structured Texts in Java - Alignment Join - Image 1
sOrder.txt:
esProc Helps Process Structured Texts in Java - Alignment Join - Image 2
It is required to join the three fields: Name, Dept and Gender, in emp to sOrder in alignment and output the computed result to a new file. The expected result is as follows:
esProc Helps Process Structured Texts in Java - Alignment Join - Image 3
Code written in esProc:
esProc Helps Process Structured Texts in Java - Alignment Join - Image 4
In cells A1 and A2 respectively, data is imported from the two text files and stored in two variables: emp and sOrder. Here import function uses tab as the column separator by default. Option @t represents the first row will be imported as the field names. Because only some of the fields in emp.txt are needed, the code in A1 uses the names of these desired fields as parameters. After execution, values of emp and sOrder are as follows:
esProc Helps Process Structured Texts in Java - Alignment Join - Image 5
In the code in A3: =join@1(sOrder:s,SellerId;emp:e,EId), join function performs the operation of alignment join and changes the names of the two tables to s and e respectively. Option @1 represents the left join which is in line with the requirement of the example: join emp to sOrder in alignment. The computed result is as follows:
esProc Helps Process Structured Texts in Java - Alignment Join - Image 6
Click the numbers in blue and we can see the detailed information, as shown below:
esProc Helps Process Structured Texts in Java - Alignment Join - Image 7
esProc can also be used to realize the right join which only requires exchanging positions of data in alignment. For example, to align sOrder according to emp, we just need to exchange their positions in the code, that is, =join@1(emp:e,EId;sOrder:s,SellerId). The computed result is as follows:
esProc Helps Process Structured Texts in Java - Alignment Join - Image 8
It is also easy to realize the full join using option @f. The code is join@f(sOrder:s,SellerId;emp:e,EId). The computed result is as follows:
esProc Helps Process Structured Texts in Java - Alignment Join - Image 9
There are altogether four operations of alignment join: left join, right join, full join and inner join. By default, join function is used to execute the inner join, the code is =join(sOrder:s,SellerId;emp:e,EId). The computed result is as follows:
esProc Helps Process Structured Texts in Java - Alignment Join - Image 10
Let’s get back to the example. The code in A4: =A3.new(s.OrderID, s.Client, s.SellerId, s.Amount, s.OrderDate,e.Name, e.Dept, e.Gender), is for getting the desired fields from table and creating a new structured two-dimensional table. The computed result is as follows:
esProc Helps Process Structured Texts in Java - Alignment Join - Image 11
Now the alignment is done and data needs to be exported to a new file. The code for this is =file("E: \\result.txt").export@t(A4). In export function, tab is by default the column separator and option @t represents the field names are exported to the first row. Open result.txt and we can see information as follows:
esProc Helps Process Structured Texts in Java - Alignment Join - Image 12
The script in the above has finished exporting all aligned data to the new file, what we will do next is to call the script in Java.
//create a connection using esProcjdbc
Class.forName("com.esproc.jdbc.InternalDriver");
con= DriverManager.getConnection("jdbc:esproc:local://");
//call esProc script; the name of the script file is test
st =(com.esproc.jdbc.InternalCStatement)con.prepareCall("call test()");
// execute esProc stored procedure
st.execute();

By executing the above Java code, emp will be joined to sOrderin alignment and the result will be output to file result.txt.

If the requirement is changed to this: query data in sOrder according to dynamic periods of time, execute the same operation of alignment join and return the result directly to Java. To complete the task esProc needs to define two parameters: begin and end, to represent starting time and ending time respectively. The esProc code is as follows:
esProc Helps Process Structured Texts in Java - Alignment Join - Image 13
The code in red has been modified.
A2:Filter sOrder again using select function according to the starting and ending time passed from Java, that is, @begin and @end.

A5:Output the computed result in A4 to JDBC interface.

And Java code should be modified too to pass parameters to esProc code and get thefinal result. The modified code is as follows:
Class.forName("com.esproc.jdbc.InternalDriver");
con= DriverManager.getConnection("jdbc:esproc:local://");
st =(com.esproc.jdbc.InternalCStatement)con.prepareCall("call test(?,?)");
st.setObject(1,startTime);
st.setObject(2,endTime);
st.execute();
ResultSet set = st.getResultSet();

This blog is listed under Development & Implementations and Data & Information Management Community

Related Posts:

Java

 

Structured text

 

Cursor

 
Post a Comment

Please notify me the replies via email.

Important:
  • We hope the conversations that take place on MyTechLogy.com will be constructive and thought-provoking.
  • To ensure the quality of the discussion, our moderators may review/edit the comments for clarity and relevance.
  • Comments that are promotional, mean-spirited, or off-topic may be deleted per the moderators' judgment.
You may also be interested in
 
Awards & Accolades for MyTechLogy
Winner of
REDHERRING
Top 100 Asia
Finalist at SiTF Awards 2014 under the category Best Social & Community Product
Finalist at HR Vendor of the Year 2015 Awards under the category Best Learning Management System
Finalist at HR Vendor of the Year 2015 Awards under the category Best Talent Management Software
Hidden Image Url