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:
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.
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).
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.
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.
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.
INSERT INTO purchase(purchaseTimestamp) VALUES(:purchaseTimestamp);
SELECT LAST_INSERT_ID();
SELECT purchaseId, purchaseTimestamp
FROM purchase
WHERE purchaseId = ?
The client of the DAO class calls insert method and fetchLastId method within a transaction boundary.
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));
}
}
