Exploring options to make SQL easier and more reliable in Java

When building web backends in Java one of my biggest grievance was the communincation with the SQL server. Either one has to resort to some kind of ORM like Hibernate or one has to drop back to hand writing SQL queries. I was never a fan of the former mostly since it so typically of Java frameworks from the 00s. It just seems so overengineered with its dependence on annotations and gigantic inheritance structures. The latter is of course much more susceptible to typos which (of course) will only be discovered in production because some obscure code branch was executed.

I was looking for alternatives especially DSLs that create a SQL string that I can use to query the database. During my search I have found 1 or 2 libraries in this area. The first is JOOQ which puts the focus on a type safe DSL. The second is persism (I discovered it recently when scanning the github trending page) which puts the focus on eliminating boilerplate but is much more ORM like, which is stated in the official description:

Persism is a wood simple, auto-discovery, auto-configuration, and convention over configuration ORM (Object Relational Mapping) library for Java.

– https://sproket.github.io/Persism/

//A small persism example
List<Customer> list = session.query(Customer.class,"select * from Customers where name = ?", "Fred");

The two libraries are not direct competitors since persism (which is also still very new and not feature complete) takes a dynamic approach using reflection (which I do not like), but is very approachable and has much less enterprise style documentation than JOOQ (which I like a lot). It also takes the new Java 16 records into consideration. They also seem to work with JOOQ (that is not obvious since records are immutable).

public record EmployeeDTO(String employeeName, String departmentName) {
}

//EMPLOYEE.NAME etc. are generated by JOOQ (for instance by their maven plugin)
List<EmployeeDTO> employees = dsl
   .select(EMPLOYEE.NAME, DEPARTMENT.NAME)
   .from(EMPLOYEE).join(DEPARTMENT).on(EMPLOYEE.DEPARTMENT_ID.eq(DEPARTMENT.ID))
   .fetchInto(EmployeeDTO.class);

Overall I like the focus on type safety that JOOQ takes and I will explore it in one of my next toy projects. JOOQ has an inbuilt code generator that connects to your database and generates constants representing your table and field names. It also generates Record classes (no not the new Java 16 kind). This is certainly helpful, though I still think I would need seperate model classes for my Java backend that can easily be serialized to JSON and sent to my typescript frontend. That would mean multiple model classes (one for database one for the internal Java model) and a translation mechanism between them. Maybe this is not a problem in practice, but it sounds strange (since I am currently examining the SQL result set manually I only need one class).