Big Data/Analytics Zone is brought to you in partnership with:

Simon Martinelli is founder of simas GmbH and works as an independent Java EE consultant, architect, developer and trainer in Switzerland. Additionally he is a lecturer for Java EE architecture and design and Java Persistence API at Berne University of Applied Sciences. He is expert group member of JSR 352 Batch Applications for the Java Platform and JSR 354 Money and Currency API. Furthermore he is member of the board of Java User Group Switzerland and helps organizing events. Simon has posted 5 posts at DZone. You can read more from them at their website. View Full User Profile

New Open Source Project SQL Result Mapper

03.19.2013
| 1374 views |
  • submit to reddit

I'm using JPA, Hibernate and JDBC in my daily work and I always irritated that there i nothing like JPQL constructor expression in native queries. Therefore I decided to implement a small framework filling the gap!

I called the framework "SQL Result Mapper" because it is able to map any SQL query result into transfer objects.

The usage is quit forward one must be aware that the constructor of the transfer object must have the same number of arguments and types as the result of the SQL query (like in JPQL constructor expression).

Some examples:

JPA native queries

Query q = em.createNativeQuery("SELECT ID, NAME FROM EMPLOYEE");
List<EmployeeTO> list = JpaSqlResultMapper.list(q, EmployeeTO.class);

Query q = em.createNativeQuery("SELECT ID, NAME FROM EMPLOYEE WHERE ID = 1");
EmployeeTO to = JpaSqlResultMapper.uniqueResult(q, EmployeeTO.class);

JPQL

Query q = em.createQuery("SELECT e.id, e.name FROM Employee e");
List<EmployeeTO> list = JpaSqlResultMapper.list(q, EmployeeTO.class);

Query q = em.createNativeQuery("SELECT e.id, e.name FROM Employee e WHERE e.id = 1");
EmployeeTO to = JpaSqlResultMapper.uniqueResult(q, EmployeeTO.class);

JDBC

stmt.execute("SELECT ID, NAME FROM EMPLOYEE");
List<EmployeeTO> list = JdbcSqlResultMapper.list(stmt.getResultSet(), EmployeeTO.class);

stmt.execute("SELECT ID, NAME FROM EMPLOYEE WHERE ID = 1");
EmployeeTO to = JdbcSqlResultMapper.uniqueResult(stmt.getResultSet(), EmployeeTO.class);

Code Generation

Now that I was able to map the results to transfer objects the question was where the transfer objects come from. Coding by hand can be cumbersome and that's why there is a pre generator as well:

The first parameter is the path where the source file should be generated to. The second is the package name, third a suffix. With the forth parameter you can define if the fields should be public or if the generator must generate getters. Then a database connection must be passed. And the last parameter is a var args where you can passe one or multiple table names.

ClassGenerator.generateFromTables("src/test/java/", "ch.simas.sqlresultmapper.to", "TO", false, con, "EMPLOYEE");

Next Steps

In many cases the generation of transfer objects from tables doesn't make sense because projection is often used in the queries. So the next feature of the generator will support generation of transfer objects from SELECT statements.

Source pre

The whole source pre including some test cases is available on GitHub: https://github.com/simasch/sqlresultmapper SQL Result Mapper is open source and free software under Apache License, Version 2.

References

The SQL Result Mapper is inspired by EclipseLink and Hibernate:
http://onpersistence.blogspot.ch/2010/07/eclipselink-jpa-native-constructor.html
http://docs.jboss.org/hibernate/orm/3.3/reference/en/html/querysql.html#d0e13904
Published at DZone with permission of its author, Simon Martinelli. (source)

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)