Implementing database access using Spring or separate library / pattern?

Question

I'm working on an app which uses hard coded sql statements to retrieve data from a database and then populate this data into pojo's. Spring jdbc template is being used so dont need to worry about opening/closing connections. Using hard-coded sql statements seems wrong ?

Is there a design pattern or library I can use to abstact the sql statements ?

Answer 1

Look at MyBatis (formly iBatis).

• It let's you extract hardcoded SQLs into XML files (or even annotations),
• It integrates with Spring container, and can use Spring Transaction.

and many more.

Answer 2

Here's a good previous discussion of some of the issues surrounding the choice between using raw SQL or an ORM tool:

Hibernate, iBatis, Java EE or other Java ORM tool

Answer 3

Using JdbcTemplate, your application code still has the responsibility to provide sql and the JdbcTemplate can then execute SQL query or updates, iterate over ResultSets and catch JDBC exceptions. If you want to get away with writing hard-coded sql statements, you need to look into an ORM tools like Hibernate, iBatisetc

Answer 4

There is nothing inherently wrong with having SQL inside Java classes (although it's a bit controversial). If you want to externalize SQL queries you can:

• use MyBatis which is very mature
• put SQL in your applicationContext.xml Spring configuration file and inject it to POJOs (poor man's MyBatis)
• hide SQL behind DAO pattern (interface SomeDao and class SqlSomeDao having SQL encapsulated)
• ...going for full ORM like JPA if you already have SQLs is not the best idea

^{Also check out Spring Data JDBC generic DAO - small DAO implementation on top of JdbcTemplate and RowMapper<T>. Disclaimer: I'm an author of this library.}