CODEWITHSUNDEEP
javasqldatabase
ubanerjea
ubanerjea

Reputation: 474

Creating Java objects from Database SQL query

So far the way I make simple POJOs from database queries is as follows: 

CallableStatementCallback<Integer> csc = new CallableStatementCallback<Integer>() {
        @Override
        public Integer doInCallableStatement(CallableStatement cs) 
                       throws SQLException, DataAccessException {
            cs.execute();

            ResultSet rs = cs.getResultSet();
            if (rs != null && rs.next()) {
                SomePojoClass spc = new SomePojoClass();

                spc.setFirstValue(rs.getLong("first")); //column names from table
                spc.setSecondValue(rs.getString("second"));
                spc.setThirdValue(rs.getString("third"));

                spcList.add(spc);
              return 1;
            }
          return 0;
        }
    };

Obviously this maps each row to a new POJO. But I have a SQL query that returns rows like this:

 [parent row 1] - [child row 1] (returned as one row obviously)
 [parent row 1] - [child row 2]
 [parent row 2] - [child row 1]
 [parent row 2] - [child row 2]
 [parent row 2] - [child row 3]
 [parent row 3] - [child row 1]

etc. This is pretty common way of getting a SQL result set because of one to many relations... and doing redundant queries for a fairly simple structure seems unnecessary (and potentially inefficient). Is there an established easy way of parsing this into java objects? i.e. instead of iterating over a ResultSet class and creating and setting a new object for each row, I want to iterate over the set and create a new object for each parent row and a new object for each child row.

For example one idea could be to order the query by parent primary key, then generate a new parent-object only when the parent id changed, but still make a new child-object for each row, and tie the parents and children together whenever the parent id of the next row would change. But I don't know if this is the right way to do it.

Upvotes: 2

Views: 4628

Answers (1)

gknicker
gknicker

Reputation: 5568

This is pretty common way of getting a SQL result set because of one to many relations... and doing redundant queries for a fairly simple structure seems unnecessary (and potentially inefficient).

I agree. In this case you would have to execute n additional queries, one for each row in the parent query results, to get the child records. Or use an in clause with the parent IDs to select all of the child records at once, then somehow map them back to the parent records. It's ugly no matter which way you slice it.

Is there an established easy way of parsing this into java objects?

There are object-relational mapping frameworks that can lazy-load the child objects for you. This helps because the additional queries don't execute unless the collection of child objects is specifically requested from the parent object. However, it doesn't fully overcome the inefficiencies described above. Especially when you know the child objects will be used from every parent object.

Since you're rolling your own object-relational mapping using JDBC directly, there's no established standard for how to accomplish your goal.

For example one idea could be to order the query by parent primary key, then generate a new parent-object only when the parent id changed, but still make a new child-object for each row, and tie the parents and children together whenever the parent id of the next row would change. But I don't know if this is the right way to do it.

The solution you describe is the best way I know. I've used it several times and found it to work well.

Taking your code example above as the template, you would have to populate all parent and child attributes in the POJO, then detect at a higher level in the code whether it's the same parent as the prior one and act accordingly. That doesn't seem very elegant to me, but it could work.

Hopefully you can figure out a straightforward way to modify your design to better support this algorithm. If you like, I can share my home grown framework with you so you can see how I've done it.

Upvotes: 2

Related Questions