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.