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/martinellich/qlrm/blob/main/ConstructorResult.md
QLRM is available in Maven Central
Important Notices
ch.simas.qlrm to org.qlrm<dependency>
<groupId>org.qlrm</groupId>
<artifactId>qlrm</artifactId>
<version>4.2.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:
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:
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");
RecordGenerator generates Java records instead of classes.
The parameters are the path, the package name, a suffix and the schema (may be null), followed by the database connection
and one or multiple table names.
RecordGenerator recordGenerator = new RecordGenerator();
recordGenerator.generateFromTables("src/main/java/", "org.example.to", "TO", null, con, "EMPLOYEE");
Records can also be generated from the result of an arbitrary query:
ResultSet rs = stmt.executeQuery("SELECT ID, NAME FROM EMPLOYEE");
recordGenerator.generateFromResultSet("src/main/java/", "org.example.to", "EmployeeWithName", rs);
jakarta.persistence) for Spring Boot 3 and Hibernate 6 https://github.com/martinellich/qlrm/issues/30Special thanks for their contribution to
QLRM is open source and free software under Apache License, Version 2:
http://www.apache.org/licenses/LICENSE-2.0.html