This is an automated email from the ASF dual-hosted git repository.
adamsaghy pushed a commit to branch develop
in repository https://gitbox.apache.org/repos/asf/fineract.git
The following commit(s) were added to refs/heads/develop by this push:
new 749d6511e FINERACT-1971: use KeyHolder instead of
LASTVAL/LAST_INSERT_ID
749d6511e is described below
commit 749d6511efe8f89189b133449828ece9133ce3f3
Author: Adam Saghy <[email protected]>
AuthorDate: Thu Mar 28 14:58:13 2024 +0100
FINERACT-1971: use KeyHolder instead of LASTVAL/LAST_INSERT_ID
---
.../database/DatabaseSpecificSQLGenerator.java | 29 ++++++++++++++--------
.../service/ReadWriteNonCoreDataServiceImpl.java | 25 ++++++++++++++++---
.../domain/CustomJobParameterRepositoryImpl.java | 8 +++---
3 files changed, 44 insertions(+), 18 deletions(-)
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 60f7aed1a..0da46a80b 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 @@ package
org.apache.fineract.infrastructure.core.service.database;
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.fineract.infrastructure.dataqueries.data.ResultsetColumnHeader
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 class DatabaseSpecificSQLGenerator {
}
}
- 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 @@ public class DatabaseSpecificSQLGenerator {
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 @@ public class DatabaseSpecificSQLGenerator {
}
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 fd1b77fd3..5cf2275a5 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.persistence.PersistenceException;
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.domain.Sort;
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 @@ public class ReadWriteNonCoreDataServiceImpl implements
ReadWriteNonCoreDataServ
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 @@ public class ReadWriteNonCoreDataServiceImpl implements
ReadWriteNonCoreDataServ
}
}
+ 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 c2333c983..10b2f6fb8 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.ResultSetExtractor;
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 @@ public class CustomJobParameterRepositoryImpl implements
CustomJobParameterRepos
.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
