Java EE and Entity : best way to get results from a DB

Question

I am currently learning Java EE and how to use JPA.

I have made a great database, which one of its table got more than 20 fields.

I was wondering what is the best way to get the results from the DB.

Currently, I am using a @NamedQuery which takes like 12 or 13 parameters.

I was wondering if I could instead use the findAll() method, then do a loop that verifies with .contains() (for a String of course) if the record is what I am looking for.

From my point of view, this looks less complicated using the second method, maybe because I'm better with Java than JPA (at the moment)

Any piece of advice would be greatly appreciated, thank you very much

Rocío García Luque · Answer 1 · 2015-05-06T07:20:58.103

0

If you get the results with findAll method (from Spring Data JPA -thanks Neil Stockton), you are bringing to memory a huge amount of data. It's usually better to get only the records that you need and use Java only to 'work' with them and not to filter them from DB.

edited May 06 '15 at 07:20

answered May 06 '15 at 07:14

Rocío García Luque

3,597
31
31

But if I just use this data to loop on it ? I could also use a NamedQuery as a first filter, for instance filtering on the user – May 06 '15 at 07:16
1

You mean "Spring Data JPA"'s method findAll(). The JPA API has no such – Neil Stockton May 06 '15 at 07:16
@trichetriche The thing is, do you need all the data? I mean, do yo want to do something with all the records in the table, or just with some of them? – Rocío García Luque May 06 '15 at 07:24
I load all the data, because I use Javascript arrays to do instant research on inputs (with typeaheads). I then store those arrays in session attributes. but once I have done that, I don't use all the data anymore, and for every research, I use my NamedQuery – May 06 '15 at 07:28

score 0 · Answer 2 · answered May 06 '15 at 08:09

First of all, the second solution will result in sending much more data form database to server than you really need. As the table will grow, this could be an issue as far as it comes to time-performance. Also, from my experience, query that result in less data due to where constraint, will be executed quicker in database. At the end, I would not ignore the fact, that database main feature is query optimization, which is designed to optimize query execution and I think it is better to use it instead of handling result data in Java.

But given the fact that I anyway have to load all the data, would it be easier/faster to use Java instead of JPA ? I could do a first filter on 1 or 2 criterias to lessen the amount of records fetched — , May 06 '15 at 08:14

victor.herrera · Answer 3 · 2015-05-07T16:24:11.057

If you want to search data in your table based on your search criteria, you can use a different approach, in JPA you can retrieve blocks of data like this:

boolean existOnSessionArray=findInSessionArray(searchCriteria);
int numMaxResultsPerBlock=50;
int pageNumber=0;
int firstResult=0;
while( !sexistOnSessionArray){
    Query query=entityManager.createNamedQuery("yourQueryName");
    query.setMaxResults(numMaxResultsPerBlock);//you will retrieve only 50 records per block
    query.setFirstResult(firstResult);//your first record to the 49 record
    List fetchedData=query.getResultList();
    //Check if the new fetched list contains your expected result
    boolean existInNewFetchedData=check(fetchedData,searchCriteria);
    updateSessionArrayWithNewFetchedData(fetchedData);
    if(!existInNewFetchedData){
        pageNumber++;
        firstResult=pageNumber*numMaxResultsPerBlock;
    }else{
        break;
    }
}

In this example, you will find your data based on your search criteria (value of your input textfield) searching for blocks of 50 records, if your search criteria doenst exist in those first 50 records, this records will store in your session array, and then you will search if your search criteria exist in the next 50 records (51-100) and so on, until you find your data. In this way you don't have to modify your query name. The only problem that I see is, if you store all your data in a session array when your application runs, what happen if somebody updates (inserts or remove) data from this table? you will need to update your session array too.

Hope it helps...

score 0 · Answer 4 · answered Jun 23 '15 at 08:18

"Best" is relative.

Still, an elegant way to perform the CRUD operations is using the power of the Spring framework. You can learn how to do that from here provided that you have some basic Spring knowledge like creating beans and how the framework works in basic (which you can get pretty easily in the earlier chapters in the last link) and plain MySQL and JDBC knowledge.

Spring takes care of the low-level tasks like creating connections, preparing and executing SQLs etc.

Java EE and Entity : best way to get results from a DB

4 Answers4