

Ask HN: Java & SQL advice - jacquesm

I've inherited this large chunk of java code. For half a year now slowly it's been getting cleaner but there is still one major stumbling block.<p>The issue is 'select' calls to the (SQL) database.<p>These are all implemented as endless chunks of copy and paste data that do nothing but move fields from queries into class members. It's butt ugly and I want to get rid of it.<p>A nice sample:<p><pre><code>        Map&#60;Integer,Questionnaire&#62; questionnaires = new HashMap&#60;Integer,Questionnaire&#62;();
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet rs = null;

        try {
        	connection = DBStore.getReadConnection();
        	String SQL = "SELECT questionnaire_id,order_id,label FROM questionnaires ORDER BY order_id";
            statement = connection.prepareStatement(SQL);
            rs = statement.executeQuery();
            while (rs.next()){
                Questionnaire questionnaire = new Questionnaire(rs.getInt(1), rs.getInt(2), rs.getString(3));
                questionnaires.put(questionnaire.getQuestionnaireId(), questionnaire);
            }
        } catch (SQLException e) {
            logger.error(e.toString(), e);
        } finally {
        	DBStore.closeAll(connection, statement, rs);
        }
        return questionnaires;
</code></pre>
(yes, I know, it's pretty ugly).<p>So, I came up with a solution, which essentially does the mapping of database fields to class members based on the field names as returned by the database during the query.<p>You pass it a class and it figures out the rest.<p>That works.<p>But it has another problem. Because the classes that are being stored in the SQL database and the class that takes care of the storing and retrieving of the data are not in the same package or derived from each other I'd have to make all the fields that I wish to store in all the classes 'public'.<p>Is there an elegant way around this ?<p>How do others do this kind of stuff ?<p>I can't believe that this is the way it is really done, so if any of you has any clue or pointer then please help me out before I throw my monitor out of the window...
======
justinsb
Reflection (which you'll be using anyway) can access private fields. See
Class.getDeclaredFields.

Check out JPA or Hibernate & Hibernate Annotations for the 'standard' way to
do this.

I would just use JPA/Hibernate, rather than writing your own mini-ORM. But you
could equally borrow the JPA Annotations and use them for your mini-ORM, and
then adopt Hibernate when you're ready.

~~~
jacquesm
> Reflection (which you'll be using anyway) can access private fields.

Yes, I am using 'reflection'. That seems to be the way to get to the 'guts' of
a class.

Hey, that's interesting!

Protected is less strict than private, if I can't see protected fields then I
should be able to see private fields?

I'm definitely going to see if that works, I never though that it would be
able to since it couldn't read the protected ones.

This is where I got that from:

[http://java.sun.com/docs/books/tutorial/java/javaOO/accessco...](http://java.sun.com/docs/books/tutorial/java/javaOO/accesscontrol.html)

Maybe I messed up in a test somewhere.

Or maybe I can fiddle with setAccessible from within the class the does the
storing/retrieving ?

Good tip on using the interface of an established library and then making the
switch. Like that I can take it one step at the time.

~~~
justinsb
Just checked in our code base: getDeclaredFields does return private fields,
you then call field.setAccessible(true) to bypass security so you can then
get/set the field.

And this definitely works across packages - we use it for some evil tricks
like peeking into private fields in the Java runtime library.

~~~
jacquesm
Sweet! Ok, so that solves my short term problem, longer term I'll have to put
in a more solid ORM than this home built thing.

At least it will cut down on the cruft enough that I can start to see the wood
for the trees.

thank you very much.

------
icey
Hibernate is a fairly popular way of dealing with OR mapping in Java:

<https://www.hibernate.org/>

Unfortunately, you'll end up trading off code cleanliness with XML
configuration files, but I believe there are tools out there that help with
the generation and maintenance of those files.

I feel that I may have missed some of what you're looking for though, so if
Hibernate is the wrong solution for you let me know and maybe I can help you
get nearer to a workable solution.

~~~
jacquesm
I found a way to use the 'reflect' class to figure out the mappings, and by
caching them after the first query it is reasonably fast and doesn't require
any further mapping information, as long as you make sure that the field names
in the classes match the field names in the tables it works.

But I'm stuck on not being allowed to access the fields in the classes from a
'foreign' class, it simply doesn't allow it.

So I'm forced to make all those fields public, and that isn't the way things
are meant to work.

Java doesn't seem to have a way to say 'this class is a friend' other than
putting the classes in the same package, or by deriving the class that has
access from the class that you wish to modify (and that isn't an option
because that would mean the storage class would have to derive from every
object that you wish to store).

------
mthomas
I've had success using Spring's JDBCTemplate
[http://static.springsource.org/spring/docs/2.5.6/api/org/spr...](http://static.springsource.org/spring/docs/2.5.6/api/org/springframework/jdbc/core/JdbcTemplate.html#query%28java.lang.String,%20org.springframework.jdbc.core.RowMapper%29)
and their various RowMappers
[http://static.springsource.org/spring/docs/2.5.6/api/org/spr...](http://static.springsource.org/spring/docs/2.5.6/api/org/springframework/jdbc/core/BeanPropertyRowMapper.html)

------
mavelikara
Check out iBatis - it might be the solution you are looking for.

~~~
jacquesm
Ok, I will do that. Thanks!

------
jacquesm
pivo, thanks, I did get to read it.

