CODEWITHSUNDEEP
javahibernatehibernate-native-query
Marcelo
Marcelo

Reputation: 321

How to avoid executing the query multiple times?

Say I have this nativeQuery in my DAO:

SELECT a, b, c FROM table

which returns all the values I need.

The problem is I have too many results and I need the query to run only once instead of running for every single row found.

I'm retrieving the query result and setting all the values into a class (value object) called Class like this:

public List<Class> listClass() {

    List<Class> listaObjs;

    String nativeQuery = "SELECT a, b, c FROM table";
    SQLQuery q = getSession().createSQLQuery(nativeQuery);

    int totalRecords = q.list().size();
    System.out.println("Total records: " + totalRecords);

    listaObjs = q.list();

    // For every ROW in query Result
    for (int i = 0; i < totalRecords; i++) {
        Object[] objs = (Object[]) q.list().get(i);
        Class item = new Class();

        // For every COLUMN in that ROW
        for (Object obj : objs) {
            item.setValueA((String) objs[0]);
            item.setValueB(((String) objs[1]));
            item.setValueC(((String) objs[2]));
            listaObjs.add(item);
        }
    }
    return listaObjs;
}

I'm a bit stuck here because I've never treated this Object[] to Class casting before.

Upvotes: 0

Views: 1982

Answers (5)

Vivek Singh
Vivek Singh

Reputation: 2073

Change the below lines

// For every ROW in query Result
for (int i = 0; i < totalRecords; i++) {
    Object[] objs = (Object[]) q.list().get(i);

Use 

List<Object[]> objArr = q.list();
// For every ROW in query Result
for (int i = 0; i < totalRecords; i++) {
    Object[] objs = (Object[]) objArr.get(i);

Upvotes: 2

Roman Pustylnikov
Roman Pustylnikov

Reputation: 1932

This code will rerun the query on each iteration:

 Object[] objs = (Object[]) q.list().get(i);

You got the list already with listaObjs = q.list();, so work on listaObjs inside the loop:

  for (int i = 0; i < totalRecords; i++) {
        Object[] objs = (Object[])listaObjs.get(i);

Upvotes: 1

Siva Kumar
Siva Kumar

Reputation: 2006

Your code has lot of performance and programmatic issue . Please try below.

public List<Class> listClass() {

        List<Class> listaObjs = new ArrayList<Class>();

        String nativeQuery = "SELECT a, b, c FROM table";
        SQLQuery q = getSession().createSQLQuery(nativeQuery);

        List<Object[]> totalRecords = q.list();
        System.out.println("Total records: " + totalRecords.size());


        for (Object[] objects : totalRecords) {
            Class item = new Class();
            item.setValueA((String) objs[0]);
            item.setValueB(((String) objs[1]));
            item.setValueC(((String) objs[2]));
            listaObjs.add(item);
        }

        return listaObjs;
    }

Upvotes: 2

Ivan Nemeth
Ivan Nemeth

Reputation: 106

Don't call q.list every time, just once and store the result in a List variable. (q.list() executes the sql, but you don't need it every time)

List resultList = q.list();
int totalRecords = resultList.size();

and so on...

You may consider using iterate instead of a for loop, maybe SqlQuery has an iterate method, if not, iteratr over the list.

Upvotes: 0

peter.petrov
peter.petrov

Reputation: 39477

You should call q.list() outside of the loop. Then you should iterate through the ResultSet returned, that should be your loop. Read up how to properly iterate through a ResultSet (or maybe through the List returned by the API you're using).

Upvotes: 0

Related Questions