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 - The Class Library of Structured File Computing for Java

Published on 11 June 15
673
0
1

In some cases, data must be stored in the file system, rather than in a database. That requires handling file-based data computing manually. Since Java lacks the related class library, you have to hardcode the structured file computing, resulting in complicated and unreadable code. Many are asking on the internet about the Java class library for file computing. Some of their problems are:

http://www.coderanch.com/t/561180/java/java/read-text-file-perform-operation

http://stackoverflow.com/questions/9614112/how-to-sort-and-arrange-a-text-file-in-java-to-group-lines

http://stackoverflow.com/questions/26418282/get-text-file-through-sql-java

http://www.linkedin.com/groups/I-would-like-desing-DAO-70526.S.5927393719350235139?trk=groups_search_item_list-0-b-ttl&goback=%2Egna_70526

https://plus.google.com/112275790033275716955/posts/4p75NJYzMcv

http://stackoverflow.com/questions/28969897/file-based-datastructure-for-java

Yet, you can make up for this deficiency with esProc for free. esProc encapsulates plenty of functions for processing structured files and provides the JDBC interface. A Java application will treat an esProc script as a database stored procedure, execute it after passing parameter and get the result set via JDBC.

The structure of esProc script being integrated into Java application is:
esProc - The Class Library of Structured File Computing for Java - Image 1

Through the example of conditional query on a text file, weâll see the process of Javaâs handling of structured file computing by calling the esProc script. Below is the source data:

esProc - The Class Library of Structured File Computing for Java - Image 2

Conditional filtering: To find orders in a specified period.

esProc code:
esProc - The Class Library of Structured File Computing for Java - Image 3

Explanation:

A1: Import the file with tab being the default separator. @t means importing the first row as column headers.

A2: Execute the conditional filtering. startDate and endDate are input parameters, like a period from 2010-01-01 to 2010-12-31.

Result:
esProc - The Class Library of Structured File Computing for Java - Image 4

Using JDBC, The main Java program can call an esProc script with the following code:

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

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

// Call esProc script (similar to the stored procedure); orderQuery is the name of the dfx file.

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

st.setObject(1,"2010-01-01");

st.setObject(2,"2010-12-31");

// Execute the script

st.execute();

// Get the result set

ResultSet rs = st.getResultSet();

â¦â¦

The returned result is a ResultSet object in accordance with JDBC standards. The method of calling an esProc script is the same as that of accessing a database. Programmers can master it fast as long as they are familiar with JDBC.

If the script is as simple as the above, you can write it right into the JDBC calling program, with lines of statements separated by \n. This is analogous to executing a complex SQL statement. It can save you the trouble of saving a script file.

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

ResultSet rs1 = st.executeQuery("=file(\"D:\\sOrder.txt\").import@t()\n" + "=A1.select(OrderDate>=date(\"2010-01-01\") && OrderDate<=date(\"2010-12-31\"))");

esProc will return the value of the last expression.

For more details about deploying esProc JDBC and calling script through it, see esProc Integration & Application: Java Invocation .

As a specialized class library for structured computing, esProc can handle more than this. Examples will show its capabilities.

Sorting: To sort records by client numbers in descending order and by year and month in ascending order.

esProc code: =A1.sort(-Client,year(OrderDate),month(OrderDate))

Explanation: Use â-â to sort data in descending order. You need to do some calculation to get the year and the month.

Related information: To perform sorting based on query, you can use =A2.sort(â¦), or =A1.select(â¦).sort(â¦)

Result:
esProc - The Class Library of Structured File Computing for Java - Image 5

Grouping and aggregation: To calculate each sellerâs sales amount and number of orders every year.

esProc code: =A1.groups(SellerId,year(OrderDate);sum(Amount),count(~))

Explanation: group function can perform aggregate while grouping data. ~ represents each group or the current group. count(~) is equivalent to count(OrderID).

Result:
esProc - The Class Library of Structured File Computing for Java - Image 6

Getting distinct value: To make a client list.

esProc code: =A1.id(Client)

Result:
esProc - The Class Library of Structured File Computing for Java - Image 7

Removing overlapped values: To get the first record for each client and for each seller.

esPrco code: =A1.group@1(Client,SellerId)

Explanation: group function is used to group records (allowing no aggregation). @1 means getting the first record from each group.

Result:
esProc - The Class Library of Structured File Computing for Java - Image 8

TopN: To get the 3 orders with the greatest sales amount for each seller.

esProc code: =A1.group(SellerId;~.top(3,-Amount):t).conj(t)

Explanation: top function filter records for TopN, in which â-â means reversed order. conj function is used for concatenation.

Related information: To get the order with the greatest sales amount, you can use maxp function.

Result:
esProc - The Class Library of Structured File Computing for Java - Image 9

Related computing: Align Name, Dept and Gender field in emp.txt with sOrder.txt.

esProc code:
esProc - The Class Library of Structured File Computing for Java - Image 10

Explanation:

A3: join function performs a join and changes names of the two tables into s and e respectively. @1 means left join. The result is:
esProc - The Class Library of Structured File Computing for Java - Image 11

Related information: @1 means left join; @f means full join. By default, join function performs inner join.

A4: Get desired fields from the joined table to create a new two-dimensional structured table.

Result:
esProc - The Class Library of Structured File Computing for Java - Image 12

All the above examples assume that the size of the file is relatively small. If the file is too big to be entirely loaded into memory, you can use the esProc cursor to handle it. See related documents for detailed information.

One point worth noting is that all these esProc functionalities are free of charge, so programmers can embed esProc engine into their applications at no cost.
In some cases, data must be stored in the file system, rather than in a database. That requires handling file-based data computing manually. Since Java lacks the related class library, you have to hardcode the structured file computing, resulting in complicated and unreadable code. Many are asking on the internet about the Java class library for file computing. Some of their problems are:

http://www.coderanch.com/t/561180/java/java/read-text-file-perform-operation

http://stackoverflow.com/questions/9614112/how-to-sort-and-arrange-a-text-file-in-java-to-group-lines

http://stackoverflow.com/questions/26418282/get-text-file-through-sql-java

http://www.linkedin.com/groups/I-would-like-desing-DAO-70526.S.5927393719350235139?trk=groups_search_item_list-0-b-ttl&goback=%2Egna_70526

https://plus.google.com/112275790033275716955/posts/4p75NJYzMcv

http://stackoverflow.com/questions/28969897/file-based-datastructure-for-java

Yet, you can make up for this deficiency with esProc for free. esProc encapsulates plenty of functions for processing structured files and provides the JDBC interface. A Java application will treat an esProc script as a database stored procedure, execute it after passing parameter and get the result set via JDBC.

The structure of esProc script being integrated into Java application is:

esProc - The Class Library of Structured File Computing for Java - Image 1

Through the example of conditional query on a text file, weâll see the process of Javaâs handling of structured file computing by calling the esProc script. Below is the source data:

esProc - The Class Library of Structured File Computing for Java - Image 2

Conditional filtering: To find orders in a specified period.



esProc code:

esProc - The Class Library of Structured File Computing for Java - Image 3

Explanation:

A1: Import the file with tab being the default separator. @t means importing the first row as column headers.

A2: Execute the conditional filtering. startDate and endDate are input parameters, like a period from 2010-01-01 to 2010-12-31.

Result:

esProc - The Class Library of Structured File Computing for Java - Image 4

Using JDBC, The main Java program can call an esProc script with the following code:

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

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

// Call esProc script (similar to the stored procedure); orderQuery is the name of the dfx file.

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

st.setObject(1,"2010-01-01");

st.setObject(2,"2010-12-31");

// Execute the script

st.execute();

// Get the result set

ResultSet rs = st.getResultSet();

â¦â¦

The returned result is a ResultSet object in accordance with JDBC standards. The method of calling an esProc script is the same as that of accessing a database. Programmers can master it fast as long as they are familiar with JDBC.

If the script is as simple as the above, you can write it right into the JDBC calling program, with lines of statements separated by \n. This is analogous to executing a complex SQL statement. It can save you the trouble of saving a script file.

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

ResultSet rs1 = st.executeQuery("=file(\"D:\\sOrder.txt\").import@t()\n" + "=A1.select(OrderDate>=date(\"2010-01-01\") && OrderDate

esProc will return the value of the last expression.

For more details about deploying esProc JDBC and calling script through it, see esProc Integration & Application: Java Invocation .

As a specialized class library for structured computing, esProc can handle more than this. Examples will show its capabilities.

Sorting: To sort records by client numbers in descending order and by year and month in ascending order.



esProc code: =A1.sort(-Client,year(OrderDate),month(OrderDate))

Explanation: Use â-â to sort data in descending order. You need to do some calculation to get the year and the month.

Related information: To perform sorting based on query, you can use =A2.sort(â¦), or =A1.select(â¦).sort(â¦)

Result:

esProc - The Class Library of Structured File Computing for Java - Image 5

Grouping and aggregation: To calculate each sellerâs sales amount and number of orders every year.



esProc code: =A1.groups(SellerId,year(OrderDate);sum(Amount),count(~))

Explanation: group function can perform aggregate while grouping data. ~ represents each group or the current group. count(~) is equivalent to count(OrderID).

Result:

esProc - The Class Library of Structured File Computing for Java - Image 6

Getting distinct value: To make a client list.



esProc code: =A1.id(Client)

Result:

esProc - The Class Library of Structured File Computing for Java - Image 7

Removing overlapped values: To get the first record for each client and for each seller.



esPrco code: =A1.group@1(Client,SellerId)

Explanation: group function is used to group records (allowing no aggregation). @1 means getting the first record from each group.

Result:

esProc - The Class Library of Structured File Computing for Java - Image 8

TopN: To get the 3 orders with the greatest sales amount for each seller.



esProc code: =A1.group(SellerId;~.top(3,-Amount):t).conj(t)

Explanation: top function filter records for TopN, in which â-â means reversed order. conj function is used for concatenation.

Related information: To get the order with the greatest sales amount, you can use maxp function.

Result:

esProc - The Class Library of Structured File Computing for Java - Image 9

Related computing: Align Name, Dept and Gender field in emp.txt with sOrder.txt.



esProc code:

esProc - The Class Library of Structured File Computing for Java - Image 10

Explanation:

A3: join function performs a join and changes names of the two tables into s and e respectively. @1 means left join. The result is:

esProc - The Class Library of Structured File Computing for Java - Image 11

Related information: @1 means left join; @f means full join. By default, join function performs inner join.

A4: Get desired fields from the joined table to create a new two-dimensional structured table.

Result:

esProc - The Class Library of Structured File Computing for Java - Image 12

All the above examples assume that the size of the file is relatively small. If the file is too big to be entirely loaded into memory, you can use the esProc cursor to handle it. See related documents for detailed information.

One point worth noting is that all these esProc functionalities are free of charge, so programmers can embed esProc engine into their applications at no cost.

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

Related Posts:
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

Back to Top