Where to keep large SQL queries when usuing Spring's JdbcTemplate classes

Question

I'm developing a DAO using Spring JdbcDaoSupport and would like to know if anyone can suggest best practice for externalizing the SQL from the Java code.

I'm used to using Hibernate and iBatis for this kind of project and like the way that the queries are not held in the Java code. Due to the complexity of the data access (accessing different DBs on different servers) I cannot use a framework like Hibernate/JPA/iBatis in this project and feel that JDBC is a much better fit anyway.

I've considered injecting the queries but this would lead to horrible Spring config files and my Unit test configs would become a burden to manage. I don't really want to write a XML parser just for this, and property files get a bit messy when values are split across multiple lines.

Any suggestions?

Answer 1

You can use native SQL queries with Hibernate. See here

Otherwise, you can store your queries in .properties file(s), and load them in a application-wide Map which you can inject into your beans.

You can also use some XML format of your choice, and parse it easily with commons-configuration.

Answer 2

How about using JPA annotations to store the (more complicated) queries as named queries on the domain objects?

@Entity
@Indexed
@NamedQueries(
    @NamedQuery(name = "MyDomainObject.myNamedQuery", query = "select id from    MyDomainObject where myProperty.id = :propertyId")
)
public class MyDomainObject {
   ...
}