In JPA there is no Constructor Expression for native SQL queries. QLRM fills the gap!
And because the implementation was quite easy there is an implementation for JDBC resultsets as well.
Read more: https://github.com/simasch/qlrm/blob/master/ConstructorResult.md
QLRM is available in Maven Central
Important Notices
- The groupId has changed from
ch.simas.qlrmtoorg.qlrm - QLRM from version 4.x.x require Jakarta Persistence
<dependency>
<groupId>org.qlrm</groupId>
<artifactId>qlrm</artifactId>
<version>4.0.1</version>
</dependency>Sometimes it's cleaner to move the SQL statements to a file but how to execute these statements?
QueryExecutors to the rescue!
There is an implemenation that takes a JDBC connection and one using the JPA EntityManager.
JdbcQueryExecutor queryExecutor = new JdbcQueryExecutor();
List<EmployeeTO> list = queryExecutor.executeSelect(con, EmployeeTO.class, "select_with_one_param.sql", 1);JpaQueryExecutor queryExecutor = new JpaQueryExecutor();
List<EmployeeTO> list = queryExecutor.executeSelect(em, EmployeeTO.class, "select_with_one_param.sql", 1);The usage is quite forward but be aware of:
- The Constructor must have the same number of arguments as the result of the SQL query
- The result types must match the constructor arguments types
JpaResultMapper jpaResultMapper = new JpaResultMapper();
Query q = em.createNativeQuery("SELECT ID, NAME FROM EMPLOYEE");
List<EmployeeTO> list = jpaResultMapper.list(q, EmployeeTO.class);Query q = em.createNativeQuery("SELECT ID, NAME FROM EMPLOYEE WHERE ID = 1");
EmployeeTO to = jpaResultMapper.uniqueResult(q, EmployeeTO.class);Query q = em.createQuery("SELECT e.id, e.name FROM Employee e");
List<EmployeeTO> list = jpaResultMapper.list(q, EmployeeTO.class);Query q = em.createNativeQuery("SELECT e.id, e.name FROM Employee e WHERE e.id = 1");
EmployeeTO to = jpaResultMapper.uniqueResult(q, EmployeeTO.class);JdbcResultMapper jdbcResultMapper = new JdbcResultMapper();
stmt.execute("SELECT ID, NAME FROM EMPLOYEE");
List<EmployeeTO> list = jdbcResultMapper.list(stmt.getResultSet(), EmployeeTO.class);boolean ok = stmt.execute("SELECT ID, NAME FROM EMPLOYEE WHERE ID = 1");
EmployeeTO to = jdbcResultMapper.uniqueResult(stmt.getResultSet(), EmployeeTO.class);The QL 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
ClassGenerator is a simple utility to generate transfer objects from database tables.
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 vargargs where you can pass one or multiple table names.
ClassGenerator classGenerator = new ClassGenerator();
classGenerator.generateFromTables("src/test/java/", "ch.simas.sqlresultmapper.to", "TO", false, con, "EMPLOYEE");- New method on JdbcQueryExecutor to execute a SQL string. Thank you Severin! #17
- JpaQueryExecutor now supports paging
- Fixed #16
- Added JpaQueryExecutor and JdbcQueryExecutor
- BigInteger Support for class generator by Nicola Mazarese: #15
- Switched Java version back to 1.7. Sorry for any inconvenience!
- Fixes from Nicola Mazarese pull request #14
- Contains fixes from Jan Mosigs pull request #12
- #9 thanks to Jan Mosig for the fix
- Support for multiple constructors
- Definition of schema for code generation
Special thanks for their contribution to
- Jan Mosig https://github.com/JanMosigItemis
- Stefan Heimberg https://github.com/StefanHeimberg
- Matthias Begert https://github.com/begert
- Nicola Mazarese https://github.com/nicolaMaza
QLRM is open source and free software under Apache License, Version 2: