Java JDBC vs JPA for database application [closed]

Question

I'd like to preface that I'm somewhat of a novice looking for advice as I'm trying to build good habits.

The application I'm developing right now is a heavily integrated database application. As I develop and explore and implement the requirements for each of my entities, I'm finding that my classes are just exploding with code to run queries in different ways on each of the entities.

While it might not be a bad thing right now, in terms of maintenance, I foresee my application being a nightmare to debug and update.

Do any JDBC experts out there have any suggestions for design patters that would help slim down the boiler-plate type code for handling all of these queries? Or should I stray from that completely and use JPA?

I've tried to implement JPA in the past, but have had trouble with complex entity relationships. Should I just read a JPA book and go from there?

Answer 1

JPA can be a good long-term solution. But if you prefer to stay closer to plain SQL, you can consider other options like Spring Framework's JDBC support.

Note that, you don't need to use other spring framework components link DI, MVC etc to be able to use Spring JDBC. It is quiet easy to use without other parts spring framework. When using spring jdbc, you don't need to do following tasks in your code:

1. Open the connection.
2. Prepare and execute the statement.
3. Set up the loop to iterate through the results (if any).
4. Process any exception.
5. Handle transactions.
6. Close the connection, statement and resultset.

What you need to do is:

1. Define connection parameters. (once)
2. Specify the SQL statement. (for each query)
3. Declare parameters and provide parameter values (when using prepared statements)
4. Do the work for each iteration. (spring do the resultset traversal, you only need to provide logic for acting on a single row)

Another benefit of spring-jdbc is that it replaces JDBC checked exceptions with unchecked exceptions.