SQL mappers and builders (MyBatis, jOOQ)

Q: What are the most common mistakes with SQL mappers and builders (MyBatis, jOOQ)?

Using ${} with user input in MyBatis. Select star mapped to partial objects. Hand-writing update statements that forget to set the audit columns. Mixing two mappers and an ORM on the same table without a plan.

MyBatis and jOOQ sit between raw JDBC and a full ORM: you control the SQL, and the library handles parameters, result mapping and (in jOOQ) type-safe query building.

🔎 Unravel this example Prerequisites (1)

Learn these first

JDBC and prepared statements

At a glance

Mental model

JPA is a driver who picks the route for you; MyBatis and jOOQ are a map and a steering wheel.

What happens, in order

1MyBatis maps an interface to SQL: @Mapper interface OrderMapper { @Select("select * from orders where id = #{id}") Order find(long id); }#{id} is a safe bound parameter; ${...} pastes text and is an injection risk.
2Larger queries live in XML mapper files with resultMap definitions that map columns to fields and nested objects
3jOOQ generates Java classes from your schema and lets you write SQL in Java with compile-time checks: dsl.select(ORDERS.ID).from(ORDERS).where(ORDERS.STATUS.eq("NEW")).fetch()
4Both integrate with Spring transactions, so @Transactional works as with JdbcTemplate

Remember

You get full SQL power (window functions, CTEs, hints) and predictable queries
In MyBatis, treat ${} as dangerous (table names or order-by columns only, from an allow-list)
MyBatis creates a dynamic proxy for each mapper interface; a method call looks up the SQL by its fully qualified name, binds parameters with a PreparedStatement, executes it and maps rows using the result map or auto-mapping by column name

Watch out

Using ${} with user input in MyBatisSelect star mapped to partial objectsHand-writing update statements that forget to set the audit columnsMixing two mappers and an ORM on the same table without a plan

Example (highlighted lines explain it)

@Mapperpublic interface OrderMapper {    @Select("select id, status, total from orders where customer_id = #{customerId} order by id desc")    List<Order> forCustomer(@Param("customerId") long customerId);    @Update("update orders set status = #{status} where id = #{id}")    int updateStatus(@Param("id") long id, @Param("status") String status);}

line 3 uses Select, the core of SQL mappers and builders (MyBatis, jOOQ)
line 6 uses Update, the core of SQL mappers and builders (MyBatis, jOOQ)

Read the full explanation (about 2 min)

MyBatis and jOOQ sit between raw JDBC and a full ORM: you control the SQL, and the library handles parameters, result mapping and (in jOOQ) type-safe query building.

A mental model

JPA is a driver who picks the route for you; MyBatis and jOOQ are a map and a steering wheel. You choose the route (the SQL) and the car handles the mechanics.

How it works

MyBatis maps an interface to SQL: @Mapper interface OrderMapper { @Select("select * from orders where id = #{id}") Order find(long id); }. #{id} is a safe bound parameter; ${...} pastes text and is an injection risk.

Larger queries live in XML mapper files with resultMap definitions that map columns to fields and nested objects.

jOOQ generates Java classes from your schema and lets you write SQL in Java with compile-time checks: dsl.select(ORDERS.ID).from(ORDERS).where(ORDERS.STATUS.eq("NEW")).fetch().

Both integrate with Spring transactions, so @Transactional works as with JdbcTemplate.

Going deeper

You get full SQL power (window functions, CTEs, hints) and predictable queries. In exchange there is no dirty checking, no lazy loading and no cache coherence; you write the update statements yourself.

In MyBatis, treat ${} as dangerous (table names or order-by columns only, from an allow-list). In jOOQ, plain SQL strings carry the same risk if you concatenate input.

A schema change breaks jOOQ code generation at compile time. That is a feature: schema drift is caught in the build rather than in production.

Under the hood

MyBatis creates a dynamic proxy for each mapper interface; a method call looks up the SQL by its fully qualified name, binds parameters with a PreparedStatement, executes it and maps rows using the result map or auto-mapping by column name. jOOQ builds a query object tree and renders it to the dialect of your database before execution.

Common mistakes

Using ${} with user input in MyBatis.
Select star mapped to partial objects.
Hand-writing update statements that forget to set the audit columns.
Mixing two mappers and an ORM on the same table without a plan.

How Unravel recognises it

When you paste code, Unravel looks for these signs of sql mappers and builders (mybatis, jooq). Spotting them yourself is a good habit when you read unfamiliar code:

Annotations: @MapperScan @Select @Insert @Update @Delete @Results @Result @Options @SelectProvider
Types: DSLContext SqlSessionFactory SqlSession SqlSessionTemplate SqlSessionFactoryBean DSL TableField Jdbi Sql2o

Part of a learning path

Data stores beyond SQL · previous: DynamoDB access · next: JDBC transactions, batching and JdbcTemplate mapping

Related concepts

JDBC and prepared statementsPersistence JPA entitiesPersistence

Frequently asked questions

What is SQL mappers and builders (MyBatis, jOOQ)?

MyBatis and jOOQ sit between raw JDBC and a full ORM: you control the SQL, and the library handles parameters, result mapping and (in jOOQ) type-safe query building.

What are the most common mistakes with SQL mappers and builders (MyBatis, jOOQ)?

Using ${} with user input in MyBatis. Select star mapped to partial objects. Hand-writing update statements that forget to set the audit columns. Mixing two mappers and an ORM on the same table without a plan.

How can I see SQL mappers and builders (MyBatis, jOOQ) in real code?

Paste your own Java into Unravel and it will point out where sql mappers and builders (mybatis, jooq) appears, explain how it works at the detail level you choose, and draw the flow of the code around it.