Using JDBC
While Jmix primarily uses JPA for data access, there are several scenarios where direct JDBC access might be necessary, such as executing complex SQL queries, performing batch operations, or invoking stored procedures.
We recommend using DataManager and fall back to JDBC only when required. |
You can use JdbcTemplate
or JdbcClient
classes provided by Spring to execute requests on the JDBC level.
Both JdbcTemplate
and JdbcClient
automatically participate in Spring-managed transactions, so you can use them alongside JPA operations within the same transaction.
Using JdbcTemplate
JdbcTemplate
is a classic Spring Framework class that simplifies JDBC operations by handling resource management and exception translation.
To use JdbcTemplate
for accessing the main data store, simply inject it into your bean:
@Autowired
private JdbcTemplate jdbcTemplate;
public Map<String, BigDecimal> getCustomerAmounts(CustomerGrade grade) {
return jdbcTemplate.query(
"""
select c.NAME, sum(o.AMOUNT)
from CUSTOMER c join ORDER_ o on c.ID = o.CUSTOMER_ID
where c.GRADE = ?
group by c.NAME
""",
(ResultSet rs) -> {
Map<String, BigDecimal> result = new HashMap<>();
while (rs.next()) {
result.put(rs.getString(1), rs.getBigDecimal(2));
}
return result;
},
grade.getId()
);
}
If you need to access an additional data store, inject the corresponding javax.sql.DataSource
and create a new instance of JdbcTemplate
. In the following example, JdbcTemplate
is created for the db1
data store:
@Autowired
@Qualifier("db1DataSource")
private DataSource db1DataSource;
public List<String> loadFooNames() {
JdbcTemplate jdbcTemplate = new JdbcTemplate(db1DataSource);
return jdbcTemplate.queryForList("select NAME from SAMPLE_FOO", String.class);
}
Using JdbcClient
JdbcClient
, introduced in Spring Framework 6.1, provides a more modern and fluent API for JDBC operations.
To use JdbcClient
for accessing the main data store, simply inject it into your bean:
@Autowired
private JdbcClient jdbcClient;
public Map<String, BigDecimal> getCustomerAmountsByJdbcClient(CustomerGrade grade) {
return jdbcClient.sql("""
select c.NAME, sum(o.AMOUNT)
from CUSTOMER c join ORDER_ o on c.ID = o.CUSTOMER_ID
where c.GRADE = :grade
group by c.NAME
""")
.param("grade", grade.getId())
.query((ResultSet rs) -> {
Map<String, BigDecimal> result = new HashMap<>();
while (rs.next()) {
result.put(rs.getString(1), rs.getBigDecimal(2));
}
return result;
});
}
If you need to access an additional data store, inject the corresponding javax.sql.DataSource
and create a new instance of JdbcClient
. In the following example, JdbcClient
is created for the db1
data store:
@Autowired
@Qualifier("db1DataSource")
private DataSource db1DataSource;
public List<String> loadFooNamesByJdbcClient() {
JdbcClient jdbcClient = JdbcClient.create(db1DataSource);
return jdbcClient.sql("select NAME from SAMPLE_FOO").query(String.class).list();
}
Calling Stored Procedures
You can use the Spring’s SimpleJdbcCall
class to execute stored procedures. It provides better parameter handling and database metadata support than JdbcTemplate
.
The following example shows how to call a PostgresSQL stored procedure located in the main data store.
CREATE OR REPLACE FUNCTION get_customer_stats(
p_customer_id UUID,
OUT total_orders INTEGER,
OUT total_amount DECIMAL(19,2)
)
LANGUAGE plpgsql
AS $$
BEGIN
SELECT
COUNT(*),
COALESCE(SUM(AMOUNT), 0)
INTO
total_orders,
total_amount
FROM ORDER_
WHERE CUSTOMER_ID = p_customer_id;
END;
$$;
@Autowired
private JdbcTemplate jdbcTemplate;
public record CustomerStats(Integer totalOrders, BigDecimal totalAmount) {
}
public CustomerStats callStoredProcedure(UUID customerId) {
// Using SimpleJdbcCall for stored procedure
SimpleJdbcCall jdbcCall = new SimpleJdbcCall(jdbcTemplate)
.withFunctionName("get_customer_stats")
.withoutProcedureColumnMetaDataAccess()
.declareParameters(
new SqlParameter("p_customer_id", Types.OTHER), // UUID type
new SqlOutParameter("total_orders", Types.INTEGER),
new SqlOutParameter("total_amount", Types.DECIMAL)
);
// Execute the stored procedure
Map<String, Object> result = jdbcCall.execute(customerId);
// Extract results
Integer totalOrders = (Integer) result.get("total_orders");
BigDecimal totalAmount = (BigDecimal) result.get("total_amount");
return new CustomerStats(totalOrders, totalAmount);
}
If you need to call a procedure from an additional data store, create the JdbcTemplate
instance for the corresponding javax.sql.DataSource
as described above.