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 Text in Java–Conditional Filtering

Published on 13 November 14
290
0
0

Following problems will arise if you perform conditional filtering on text files in Java alone:

1. The text file is not a database,so it cannot be accessed by SQL. The code needs to be modified if filtering conditions are changed. Besides, if you want a flexible conditional filtering as that in SQL, you have to self-program the dynamic expression parsing and evaluating, resulting in a great amount of programming work.

2. Stepwise loading is required for the big files that cannot be loaded into the memory all at once. If the performance must be taken into account, you have to deal with some complicated programming like the management of file buffer and line-splitting computing.

But if esProc is used to help with Java programming, these problems can be solved without self-programmed code. The following example will teach you how to do this in detail.

The text file employee.txt has the employee information. You are required to fetch the data and select from them the female employees who were born on and after January 1, 1981.

The text file employee.txt is in a format as follows:

EID NAME SURNAME GENDER STATE BIRTHDAY HIREDATE DEPT SALARY

1 Rebecca Moore F California 1974-11-20 2005-03-11 R&D 7000

2 Ashley Wilson F New York 1980-07-19 2008-03-16 Finance 11000

3 Rachel Johnson F New Mexico 1970-12-17 2010-12-01 Sales 9000

4 Emily Smith F Texas 1985-03-07 2006-08-15 HR 7000

5 Ashley Smith F Texas 1975-05-13 2004-07-30 R&D 16000

6 Matthew Johnson M California 1984-07-07 2005-07-07 Sales 11000

7 Alexis Smith F Illinois 1972-08-16 2002-08-16 Sales 9000

8 Megan Wilson F California 1979-04-19 1984-04-19 Marketing 11000

9 Victoria Davis F Texas 1983-12-07 2009-12-07 HR 3000

10 Ryan Johnson M Pennsylvania 1976-03-12 2006-03-12 R&D 13000

11 Jacob Moore M Texas 1974-12-16 2004-12-16 Sales 12000

12 Jessica Davis F New York 1980-09-11 2008-09-11 Sales 7000

13 Daniel Davis M Florida 1982-05-14 2010-05-14 Finance 10000

â¦

Implementation approach: call esProc script with Java, import and compute the data, then return the result in the form of ResultSet to Java. Because esProc supports dynamic expression parsing and evaluating, it enables Java to perform the conditional filtering as flexibly as SQL does.

For example, it is required to query the information of female employees who were born on and after January 1, 1981. In this case, esProc can use an input parameter âwhereâas the condition, which is shown below:
esProc Helps Process Structured Text in JavaâConditional Filtering - Image 1

âwhereâ is a string, its values is BIRTHDAY>=date(1981,1,1) && GENDER=="F".

The code written in esProc is as follows:
esProc Helps Process Structured Text in JavaâConditional Filtering - Image 2

A1ï¼Define a file object and import the data, with the first row being the title. tab is used as the field separator by default. esProcâs IDE can display the imported data visually, as shown in the right part of the above figure.

A2ï¼Perform the conditional filtering, using macro to realize parsing the expression dynamically. The âwhereâ in this process is an input parameter. In executing, esProc will first compute the expression enclosed by ${â¦}, then replace ${â¦} with the computed result acting as the macro string value and interpret and execute the code. The final code to be executed in this example is =A1.select(BIRTHDAY>=date(1981,1,1) && GENDER=="F").

A3ï¼Return the eligible result set to the external program.

In esProc, when the filtering condition is changed, you just need to modify âwhereââ the parameter. For example, it is required to query the information of female employees who were born on and after January 1, 1981, or employees whose NAME+SURNAME is equivalent to âRebeccaMooreâ. The value of âwhereâ can be written as BIRTHDAY>=date(1981,1,1) && GENDER=="F" || NAME+SURNAME=="RebeccaMoore". After the code is executed, the result set in A2 is as follows:
esProc Helps Process Structured Text in JavaâConditional Filtering - Image 3

Call this piece of code in Java with esProc JDBC and get the result. Detailed code is as follows (save the above program in esProc as test.dfx):

//create a connection withesProc JDBC

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

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

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

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

//set the parameters

st.setObject(1," BIRTHDAY>=date(1981,1,1) && GENDER==\"F\" ||NAME+SURNAME==\"RebeccaMoore\"");//the parameters are the dynamic filtering conditions

//execute the esProcstored procedure

st.execute();

//get the result set, which is the eligible set of employees

ResultSet set = st.getResultSet();

If the script is simple, the code can be written directly into the program in Java that calls the esProc JDBC. It wonât be necessary to write a special script file (test.dfx):

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

ResultSet set=st.executeQuery("=file(\"D:/employee.txt\").import@t().select(BIRTHDAY>=date(1981,1,1)&&GENDER==\"F\" || NAME+SURNAME==\"RebeccaMoore\")");

This piece of code in Java calls a line of code in esProc script directly, that is, get the data from the text file, perform conditional filtering and return the result set to setâ the object of ResultSet.

It is assumed, in the above approach, that the file is small enough to be loaded to the memory all together. In reality, there may be huge files that cannot be loaded all together or the situation where it is believed that it is unnecessary to increase memory usage even if the file is not huge. In these occasions, file cursor can be used to handle the operation, thus the program in esProc can be modified in this way:
esProc Helps Process Structured Text in JavaâConditional Filtering - Image 4
Following problems will arise if you perform conditional filtering on text files in Java alone:

1. The text file is not a database,so it cannot be accessed by SQL. The code needs to be modified if filtering conditions are changed. Besides, if you want a flexible conditional filtering as that in SQL, you have to self-program the dynamic expression parsing and evaluating, resulting in a great amount of programming work.

2. Stepwise loading is required for the big files that cannot be loaded into the memory all at once. If the performance must be taken into account, you have to deal with some complicated programming like the management of file buffer and line-splitting computing.

But if esProc is used to help with Java programming, these problems can be solved without self-programmed code. The following example will teach you how to do this in detail.

The text file employee.txt has the employee information. You are required to fetch the data and select from them the female employees who were born on and after January 1, 1981.

The text file employee.txt is in a format as follows:

EID NAME SURNAME GENDER STATE BIRTHDAY HIREDATE DEPT SALARY

1 Rebecca Moore F California 1974-11-20 2005-03-11 R&D 7000

2 Ashley Wilson F New York 1980-07-19 2008-03-16 Finance 11000

3 Rachel Johnson F New Mexico 1970-12-17 2010-12-01 Sales 9000

4 Emily Smith F Texas 1985-03-07 2006-08-15 HR 7000

5 Ashley Smith F Texas 1975-05-13 2004-07-30 R&D 16000

6 Matthew Johnson M California 1984-07-07 2005-07-07 Sales 11000

7 Alexis Smith F Illinois 1972-08-16 2002-08-16 Sales 9000

8 Megan Wilson F California 1979-04-19 1984-04-19 Marketing 11000

9 Victoria Davis F Texas 1983-12-07 2009-12-07 HR 3000

10 Ryan Johnson M Pennsylvania 1976-03-12 2006-03-12 R&D 13000

11 Jacob Moore M Texas 1974-12-16 2004-12-16 Sales 12000

12 Jessica Davis F New York 1980-09-11 2008-09-11 Sales 7000

13 Daniel Davis M Florida 1982-05-14 2010-05-14 Finance 10000

â¦

Implementation approach: call esProc script with Java, import and compute the data, then return the result in the form of ResultSet to Java. Because esProc supports dynamic expression parsing and evaluating, it enables Java to perform the conditional filtering as flexibly as SQL does.

For example, it is required to query the information of female employees who were born on and after January 1, 1981. In this case, esProc can use an input parameter âwhereâas the condition, which is shown below:

esProc Helps Process Structured Text in Javaâ

âwhereâ is a string, its values is BIRTHDAY>=date(1981,1,1) && GENDER=="F".

The code written in esProc is as follows:

esProc Helps Process Structured Text in Javaâ

A1ï¼Define a file object and import the data, with the first row being the title. tab is used as the field separator by default. esProcâs IDE can display the imported data visually, as shown in the right part of the above figure.

A2ï¼Perform the conditional filtering, using macro to realize parsing the expression dynamically. The âwhereâ in this process is an input parameter. In executing, esProc will first compute the expression enclosed by ${â¦}, then replace ${â¦} with the computed result acting as the macro string value and interpret and execute the code. The final code to be executed in this example is =A1.select(BIRTHDAY>=date(1981,1,1) && GENDER=="F").

A3ï¼Return the eligible result set to the external program.

In esProc, when the filtering condition is changed, you just need to modify âwhereââ the parameter. For example, it is required to query the information of female employees who were born on and after January 1, 1981, or employees whose NAME+SURNAME is equivalent to âRebeccaMooreâ. The value of âwhereâ can be written as BIRTHDAY>=date(1981,1,1) && GENDER=="F" || NAME+SURNAME=="RebeccaMoore". After the code is executed, the result set in A2 is as follows:

esProc Helps Process Structured Text in Javaâ

Call this piece of code in Java with esProc JDBC and get the result. Detailed code is as follows (save the above program in esProc as test.dfx):

//create a connection withesProc JDBC

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

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

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

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

//set the parameters

st.setObject(1," BIRTHDAY>=date(1981,1,1) && GENDER==\"F\" ||NAME+SURNAME==\"RebeccaMoore\"");//the parameters are the dynamic filtering conditions

//execute the esProcstored procedure

st.execute();

//get the result set, which is the eligible set of employees

ResultSet set = st.getResultSet();

If the script is simple, the code can be written directly into the program in Java that calls the esProc JDBC. It wonât be necessary to write a special script file (test.dfx):

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

ResultSet set=st.executeQuery("=file(\"D:/employee.txt\").import@t().select(BIRTHDAY>=date(1981,1,1)&&GENDER==\"F\" || NAME+SURNAME==\"RebeccaMoore\")");

This piece of code in Java calls a line of code in esProc script directly, that is, get the data from the text file, perform conditional filtering and return the result set to setâ the object of ResultSet.

It is assumed, in the above approach, that the file is small enough to be loaded to the memory all together. In reality, there may be huge files that cannot be loaded all together or the situation where it is believed that it is unnecessary to increase memory usage even if the file is not huge. In these occasions, file cursor can be used to handle the operation, thus the program in esProc can be modified in this way:

esProc Helps Process Structured Text in Javaâ

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

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