Ask HN: Java & SQL advice | Hacker News Reader

Ask HN: Java & SQL advice

3 pointsjacquesm11 comments

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.

The issue is 'select' calls to the (SQL) database.

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.

A nice sample:

        Map<Integer,Questionnaire> questionnaires = new HashMap<Integer,Questionnaire>();
        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;

(yes, I know, it's pretty ugly).

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.

You pass it a class and it figures out the rest.

That works.

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'.

Is there an elegant way around this ?

How do others do this kind of stuff ?

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...

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.

> 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...

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.

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.

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.

OK, that was it, exactly. I was using getFields before, I completely missed the 'Declared' option.

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.

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).

I've had success using Spring's JDBCTemplate http://static.springsource.org/spring/docs/2.5.6/api/org/spr... and their various RowMappers http://static.springsource.org/spring/docs/2.5.6/api/org/spr...

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

Ok, I will do that. Thanks!

pivo, thanks, I did get to read it.

Reply on news.ycombinator.com