FINERACT-1971: use KeyHolder instead of LASTVAL/LAST_INSERT_ID
diff --git a/fineract-core/src/main/java/org/apache/fineract/infrastructure/core/service/database/DatabaseSpecificSQLGenerator.java b/fineract-core/src/main/java/org/apache/fineract/infrastructure/core/service/database/DatabaseSpecificSQLGenerator.java
index 60f7aed..0da46a8 100644
--- a/fineract-core/src/main/java/org/apache/fineract/infrastructure/core/service/database/DatabaseSpecificSQLGenerator.java
+++ b/fineract-core/src/main/java/org/apache/fineract/infrastructure/core/service/database/DatabaseSpecificSQLGenerator.java
@@ -21,6 +21,7 @@
import static java.lang.String.format;
import jakarta.validation.constraints.NotNull;
+import java.math.BigInteger;
import java.util.Collection;
import java.util.List;
import java.util.Map;
@@ -30,6 +31,7 @@
import org.apache.logging.log4j.util.Strings;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Sort;
+import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.stereotype.Component;
@Component
@@ -157,16 +159,6 @@
}
}
- public String lastInsertId() {
- if (databaseTypeResolver.isMySQL()) {
- return "LAST_INSERT_ID()";
- } else if (databaseTypeResolver.isPostgreSQL()) {
- return "LASTVAL()";
- } else {
- throw new IllegalStateException("Database type is not supported for last insert id " + databaseTypeResolver.databaseType());
- }
- }
-
public String castChar(String sql) {
if (databaseTypeResolver.isMySQL()) {
return format("CAST(%s AS CHAR)", sql);
@@ -258,7 +250,7 @@
return "";
}
return "INSERT INTO " + escape(definition) + '(' + fields.stream().map(this::escape).collect(Collectors.joining(", "))
- + ") VALUES (" + fields.stream().map(e -> decoratePlaceHolder(headers, e, "?")).collect(Collectors.joining(", ")) + ')';
+ + ") VALUES (" + fields.stream().map(e -> decoratePlaceHolder(headers, e, "?")).collect(Collectors.joining(", ")) + ")";
}
public String buildUpdate(@NotNull String definition, List<String> fields, Map<String, ResultsetColumnHeaderData> headers) {
@@ -282,4 +274,19 @@
}
return placeHolder;
}
+
+ public Long fetchPK(GeneratedKeyHolder keyHolder) {
+ return switch (getDialect()) {
+ case POSTGRESQL -> (Long) keyHolder.getKeys().get("id");
+ case MYSQL -> {
+ // Mariadb
+ BigInteger generatedKey = (BigInteger) keyHolder.getKeys().get("insert_id");
+ if (generatedKey == null) {
+ // Mysql
+ generatedKey = (BigInteger) keyHolder.getKeys().get("GENERATED_KEY");
+ }
+ yield generatedKey.longValue();
+ }
+ };
+ }
}
diff --git a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/service/ReadWriteNonCoreDataServiceImpl.java b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/service/ReadWriteNonCoreDataServiceImpl.java
index fd1b77f..5cf2275 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/service/ReadWriteNonCoreDataServiceImpl.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/service/ReadWriteNonCoreDataServiceImpl.java
@@ -60,12 +60,15 @@
import jakarta.validation.constraints.NotNull;
import java.lang.reflect.Type;
import java.math.BigDecimal;
+import java.sql.PreparedStatement;
+import java.sql.SQLException;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Locale;
import java.util.Map;
+import java.util.concurrent.atomic.AtomicInteger;
import java.util.stream.Stream;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
@@ -120,6 +123,7 @@
import org.springframework.data.support.PageableExecutionUtils;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
+import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.springframework.orm.jpa.JpaSystemException;
import org.springframework.transaction.annotation.Transactional;
@@ -1322,17 +1326,21 @@
params.add(scoreValue);
}
+ GeneratedKeyHolder keyHolder = new GeneratedKeyHolder();
final String sql = sqlGenerator.buildInsert(dataTableName, insertColumns, headersByName);
try {
- int updated = jdbcTemplate.update(sql, params.toArray(Object[]::new));
+ int updated = jdbcTemplate.update(con -> {
+ PreparedStatement ps = con.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
+ setParameters(params, ps);
+ return ps;
+ }, keyHolder);
if (updated != 1) {
throw new PlatformDataIntegrityException("error.msg.invalid.insert", "Expected one inserted row.");
}
Long resourceId = appTableId;
if (isMultirowDatatable(columnHeaders)) {
- resourceId = jdbcTemplate.queryForObject(DatabaseSpecificSQLGenerator.SELECT_CLAUSE.formatted(sqlGenerator.lastInsertId()),
- Long.class);
+ resourceId = sqlGenerator.fetchPK(keyHolder);
}
return CommandProcessingResult.fromCommandProcessingResult(commandProcessingResult, resourceId);
} catch (final DataAccessException dve) {
@@ -1344,6 +1352,17 @@
}
}
+ private static void setParameters(ArrayList<Object> params, PreparedStatement ps) {
+ AtomicInteger parameterIndex = new AtomicInteger(1);
+ params.forEach(param -> {
+ try {
+ ps.setObject(parameterIndex.getAndIncrement(), param);
+ } catch (SQLException e) {
+ throw new IllegalArgumentException(e);
+ }
+ });
+ }
+
private static boolean isUserInsertable(@NotNull EntityTables entityTable, @NotNull ResultsetColumnHeaderData columnHeader) {
String columnName = columnHeader.getColumnName();
return !columnHeader.getIsColumnPrimaryKey() && !CREATEDAT_FIELD_NAME.equals(columnName) && !UPDATEDAT_FIELD_NAME.equals(columnName)
diff --git a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/jobs/domain/CustomJobParameterRepositoryImpl.java b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/jobs/domain/CustomJobParameterRepositoryImpl.java
index c2333c9..10b2f6f 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/jobs/domain/CustomJobParameterRepositoryImpl.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/jobs/domain/CustomJobParameterRepositoryImpl.java
@@ -33,6 +33,7 @@
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
+import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.stereotype.Component;
@RequiredArgsConstructor
@@ -50,10 +51,9 @@
.formatted(databaseSpecificSQLGenerator.castJson(":jsonString"));
final String jsonString = gson.toJson(customJobParameters);
SqlParameterSource parameters = new MapSqlParameterSource("jsonString", jsonString);
- namedParameterJdbcTemplate.update(insertSQL, parameters);
- final Long customParameterId = namedParameterJdbcTemplate.getJdbcTemplate().queryForObject(
- DatabaseSpecificSQLGenerator.SELECT_CLAUSE.formatted(databaseSpecificSQLGenerator.lastInsertId()), Long.class);
- return customParameterId;
+ GeneratedKeyHolder keyHolder = new GeneratedKeyHolder();
+ namedParameterJdbcTemplate.update(insertSQL, parameters, keyHolder);
+ return databaseSpecificSQLGenerator.fetchPK(keyHolder);
}
@Override
