Handling auto-generated keys on Bootiful SQL Template

Java

About the library

Bootiful SQL Template is an O/R mapper library which acts as a thin wrapper to Spring JdbcTemplate. The library adds following functionalities.

  • SQL files
  • Moderate facade methods such as forObject, forList and update
  • Limited support for Date and Time API
  • etc.

I think this library is a good option for Spring applications, especially if you are already familiar with JdbcTemplate.

Handling auto-generated keys

To handle auto-generated keys on Bootiful, you can use DBMS specific functions, such as LAST_INSERT_ID() on MySQL.

Spring provides a generic way to fetch generated keys via GeneratedKeyHolder, but Bootiful lacks the support of it. The library author @cero_t told me that the lack is intentional. Because handling of generated keys is inherently DBMS specific, there is no use in abstraction.

Here I exhibit how to handle generated keys on MySQL with Bootiful SQL Template. View the full source code on the BitBucket repository.

The example program uses the purchase table and the purchaseDetail table. The purchase table has a primary key purchaseId which is declared as AUTO_INCREMENT. The DDL is provided as below:

-- src/main/resources/db/migration/V1__create_purchase.sql

CREATE TABLE purchase (
  purchaseId INTEGER NOT NULL AUTO_INCREMENT,
  purchaseTimestamp TIMESTAMP NOT NULL,
  PRIMARY KEY (purchaseId)
)
CHARACTER SET utf8mb4
COLLATE utf8mb4_bin;

CREATE TABLE purchaseDetail (
  purchaseId INTEGER NOT NULL,
  purchaseDetailNumber INTEGER NOT NULL,
  itemName VARCHAR(100) NOT NULL,
  PRIMARY KEY (purchaseId, purchaseDetailNumber),

  CONSTRAINT fk_purchaseDetail_purchase
  FOREIGN KEY (purchaseId) REFERENCES purchase(purchaseId)
)
CHARACTER SET utf8mb4
COLLATE utf8mb4_bin;

The entry point of Bootiful is SqlTemplate class, which can be instantiated with Spring JdbcTemplate and NamedParameterJdbcTemplate.

// src/main/java/org/kink_lang/sqltemplate/autokeys_example/AppConfig.java

import org.springframework.context.annotation.Bean;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import ninja.cero.sqltemplate.core.SqlTemplate;

...

@Bean
public SqlTemplate sqlTemplate(
        JdbcTemplate jdbcTemplate,
        NamedParameterJdbcTemplate namedJdbcTemplate) {
    return new SqlTemplate(jdbcTemplate, namedJdbcTemplate);
}

...

Entity classes can be defined as plain Java Beans. No annotations are needed (@Nullable annotation in the list does not affect Bootiful).

// src/main/java/org/kink_lang/sqltemplate/autokeys_example/entity/Purchase.java

package org.kink_lang.sqltemplate.autokeys_example.entity;

import java.util.Arrays;
import java.util.Date;
import java.util.List;
import javax.annotation.Nullable;

public class Purchase {

    @Nullable private Integer purchaseId;

    private Date purchaseTimestamp;

    public Purchase() {
        this(null, new Date(0));
    }

    public Purchase(@Nullable Integer purchaseId, Date purchaseTimestamp) {
        this.purchaseId = purchaseId;
        this.purchaseTimestamp = new Date(purchaseTimestamp.getTime());
    }

    @Nullable
    public Integer getPurchaseId() {
        return this.purchaseId;
    }

    public void setPurchaseId(@Nullable Integer purchaseId) {
        this.purchaseId = purchaseId;
    }

    public Date getPurchaseTimestamp() {
        return new Date(this.purchaseTimestamp.getTime());
    }

    public void setPurchaseTimestamp(Date purchaseTimestamp) {
        this.purchaseTimestamp = new Date(purchaseTimestamp.getTime());
    }

    ...
}

The DAO class for Purchase entities is defined as below. It provides distinct fetchLastId method to execute a query to fetch the last generated key.

// src/main/java/org/kink_lang/sqltemplate/autokeys_example/dao/PurchaseDao.java

package org.kink_lang.sqltemplate.autokeys_example.dao;

import java.util.Optional;
import org.springframework.stereotype.Service;
import ninja.cero.sqltemplate.core.SqlTemplate;
import org.kink_lang.sqltemplate.autokeys_example.entity.Purchase;

@Service
public class PurchaseDao {

    private final SqlTemplate sqlTemplate;

    public PurchaseDao(SqlTemplate sqlTemplate) {
        this.sqlTemplate = sqlTemplate;
    }

    /**
     * Inserts the entity to the table.
     * If purchaseId is null, the column value is auto-generated.
     */
    public void insert(Purchase purchase) {
        sqlTemplate.update("db/sqltemplate/PurchaseDao/insert.sql", purchase);
    }

    /** Fetches the last purchaseId which is auto-generated in the current session. */
    public int fetchLastId() {
        return sqlTemplate.forObject(
                "db/sqltemplate/PurchaseDao/fetchLastId.sql", Integer.class);
    }

    /** Returns the entity of the purchaseId if any. */
    public Optional<Purchase> find(int purchaseId) {
        Purchase purchase = sqlTemplate.forObject(
                "db/sqltemplate/PurchaseDao/find.sql", Purchase.class, purchaseId);
        return Optional.ofNullable(purchase);
    }
}

The queries are placed in distinct SQL files.

-- src/main/resources/db/sqltemplate/PurchaseDao/insert.sql
INSERT INTO purchase(purchaseTimestamp) VALUES(:purchaseTimestamp);

-- src/main/resources/db/sqltemplate/PurchaseDao/fetchLastId.sql 
SELECT LAST_INSERT_ID();

-- src/main/resources/db/sqltemplate/PurchaseDao/find.sql
SELECT purchaseId, purchaseTimestamp
FROM purchase
WHERE purchaseId = ?

The client of the DAO class calls insert method and fetchLastId method within a transaction boundary.

// src/main/java/org/kink_lang/sqltemplate/autokeys_example/service/PurchaserService.java

package org.kink_lang.sqltemplate.autokeys_example.service;

import java.time.Clock;
import java.util.Arrays;
import java.util.Date;
import java.util.List;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.kink_lang.sqltemplate.autokeys_example.dao.PurchaseDao;
import org.kink_lang.sqltemplate.autokeys_example.dao.PurchaseDetailDao;
import org.kink_lang.sqltemplate.autokeys_example.entity.Purchase;
import org.kink_lang.sqltemplate.autokeys_example.entity.PurchaseDetail;

@Service
public class PurchaserService {

    private final Clock clock;
    private final PurchaseDao purchaseDao;
    private final PurchaseDetailDao purchaseDetailDao;

    public PurchaserService(
            Clock clock,
            PurchaseDao purchaseDao,
            PurchaseDetailDao purchaseDetailDao) {
        this.clock = clock;
        this.purchaseDao = purchaseDao;
        this.purchaseDetailDao = purchaseDetailDao;
    }

    @Transactional
    public void purchase() {
        Purchase purchase = new Purchase(null, Date.from(clock.instant()));
        purchaseDao.insert(purchase);
        int purchaseId = purchaseDao.fetchLastId();

        List<PurchaseDetail> details = Arrays.asList(
                new PurchaseDetail(purchaseId, 1, "アジ"),
                new PurchaseDetail(purchaseId, 2, "大根"),
                new PurchaseDetail(purchaseId, 3, "醤油"));
        details.forEach(detail -> purchaseDetailDao.insert(detail));
    }
}

Conclusion

Bootiful SQL Template enables direct mapping between SQLs and true POJOs, with minimal boilerplate code. This minimalist design seems to work well in most cases.