New Open Source Project SQL Result Mapper
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).
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);
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);
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);
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 StepsIn 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 preThe 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.
ReferencesThe SQL Result Mapper is inspired by EclipseLink and Hibernate:
(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)