Way to store,update and reuse database query result in Java/MySQL

Question

Currently, I have a scenario to generate a report where I need to apply multiple but optional filters and conditions either separately or at once while retrieving data from particular table. Currently, I am checking each filter and applying it by creating separate SQL statement for each scenario as below:

For instance: Reporting requirements: sort by column x, filter where column y = "foo", date between a and b.

Scenario 1: All filter applied.
SELECT * FROM REPORT_TABLE WHERE y='foo' AND date>a AND date<b ORDER BY x ASC

Scenario 2: Sort and date filter applied.
SELECT * FROM REPORT_TABLE WHERE date>a AND date<b ORDER BY x ASC

Scenario 3: Sort and 'foo' type filter applied.
SELECT * FROM REPORT_TABLE WHERE y='foo' ORDER BY x ASC

Scenario 4: Only sort applied
SELECT * FROM REPORT_TABLE ORDER BY x ASC

//So on...

The problem with above method is that with increasing filters there might be many scenario in order to query table. Therefore, Is there a way to accomplish above operation by simply reusing the query result.

For example, Even If I have single or multiple, check each filter and apply consequently.

Scenario: provide all required filter as list

    //first get all the rows and store it variable.
    result = SELECT * FROM report_table;

    //Now loop through each filter from given filter list and apply it.
        //and store the updated query result
        result = SELECT * FROM result WHERE //apply filter 1.
    //loop end.
    //finally all filter applied, return the result.

Why don't you just dynamicylly build a query based on the filters, i.e. start with select * from REPORT_TABLE and add the rest based on the filters? Btw, ...WHERE ORDER BY ... doesn't work, you'd need to remove the WHERE here. — Thomas
– Thomas, Commented Aug 3, 2016 at 10:46
Agreed with Thomas. Don't try to work around having to setup the proper questions to ask the SQL database, it will always be boring code to have to write and test - such is life. But in the end you want the database to do the resultset limiting for you, that's what it is there for. And this is infinitely more true where reports are concerned as they tend to involve quite a bit of data. — Gimby
– Gimby, Commented Aug 3, 2016 at 11:05

DwB · Accepted Answer · 2016-08-03 11:18:39Z

1

Take a look at myBatis dynamic sql. It was created specifically to solve the problem you are trying to solve.

answered Aug 3, 2016 at 11:18

DwB

38.6k11 gold badges62 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ra Ka Over a year ago

This looks like what I wanted. Thank you.

npinti · Accepted Answer · 2016-08-03 10:51:21Z

1

One thing you can do, is that you have an ORM such as Hibernate which can cache query results, as shown here.

This would allow you to execute queries through Hibernate and then let it decide whether the database should be hit or not.

That being said, the above approach might be a little bit overkill if you have just 1 query. What you could thus, on the other hand, would be to retrieve all the records and hold them in memory. You would then periodically update the copy of records you have in memory to ensure that you do not have stale information.

The filters would then be done through code, as opposed to queries. That being said, however, although simpler than the first recommended approach the second approach will be slower, thus I would recommend it if for instance you have a very slow connection to the database or else the connections are extremely limited, and not readily available.

Databases are good at storing data and filtering through it. If you hit millions of rows of data, pulling everything into your application and doing the filtering yourself might make your application unstable.

answered Aug 3, 2016 at 10:51

npinti

52.2k5 gold badges74 silver badges98 bronze badges

1 Comment

Ra Ka Over a year ago

Currently, I'm not using any ORMs but your answer is very helpful to better understand solution. Thank you!!!

Aditya · Accepted Answer · 2016-08-03 11:04:16Z

1

one of the approaches is by using CSVJDBC.

http://octopus.ow2.org/doc/3.2-2/extras/Csvjdbc_readme.html

After firing 'SELECT * FROM report_table;' , save the resultset in a csv and then use CSVJDBC to query the file with filter queries

answered Aug 3, 2016 at 11:04

Aditya

2,41520 silver badges25 bronze badges

1 Comment

Ra Ka Over a year ago

Okay, this seems to be good option. BTW, couldn't there be processing overheads while storing data externally in CSV in case of millions data?

sixtytrees · Accepted Answer · 2016-08-03 15:17:48Z

1

You will have to save the intermediate result in some format and then perform the query on the selected set. One common solution is csv format. In this case you can use http://csvjdbc.sourceforge.net/. You can find both free and proprietary version of this library.

Another way to go is to group your filters by function (where group, ordered by group, etc). Then check which ones are selected and parse in the right order. Pseudocode:

StringBuilder sb = new StringBuilder();
if (several `where` are selected){
    sb.append("where ");
    for (i=0; i<whereTerms.size()-1; i++){        
           sb.append(whereTerms.get(i) + " and ");
    }
    sb.append(whereTerms.get(whereTerms.size()-1) + " ");

edited Aug 3, 2016 at 15:17

answered Aug 3, 2016 at 15:07

sixtytrees

1,2331 gold badge13 silver badges32 bronze badges

Collectives™ on Stack Overflow

Way to store,update and reuse database query result in Java/MySQL

4 Answers 4

1 Comment

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related