From 5ef424120ed694b1f69daeafaa1454455d13dee2 Mon Sep 17 00:00:00 2001 From: Vitaly Takmazov Date: Wed, 31 Jan 2024 16:50:01 +0300 Subject: CI: enable SQLite * SQLite JDBC driver does not support generated keys feature. So we implement `returningId` helper * only one connection should be used from connection pooler * changes made to prevent connection locking --- .../java/com/juick/service/BaseJdbcService.java | 14 +++++++ .../com/juick/service/MessagesServiceImpl.java | 46 ++++++++++++++-------- src/main/java/com/juick/service/TagService.java | 2 - .../java/com/juick/service/TagServiceImpl.java | 22 +++++------ .../java/com/juick/service/UserServiceImpl.java | 22 +++++++---- 5 files changed, 68 insertions(+), 38 deletions(-) (limited to 'src/main/java/com/juick/service') diff --git a/src/main/java/com/juick/service/BaseJdbcService.java b/src/main/java/com/juick/service/BaseJdbcService.java index d16dadd2..b51cbdc7 100644 --- a/src/main/java/com/juick/service/BaseJdbcService.java +++ b/src/main/java/com/juick/service/BaseJdbcService.java @@ -52,6 +52,11 @@ public class BaseJdbcService { @Value("#{new Boolean('${spring.sql.init.platform}' == 'mysql')}") private boolean haveNoOffsetDateTime; + @Value("#{new Boolean('${spring.sql.init.platform}' == 'sqlserver')}") + private boolean haveOutput; + @Value("#{new Boolean('${spring.sql.init.platform}' == 'h2')}") + private boolean haveDeltaTables; + public NamedParameterJdbcTemplate getNamedParameterJdbcTemplate() { return namedParameterJdbcTemplate; } @@ -124,4 +129,13 @@ public class BaseJdbcService { } return Types.TIMESTAMP_WITH_TIMEZONE; } + public String returningId(String insertClause, String valuesClause, String idColumn) { + if (haveOutput) { + return insertClause + " OUTPUT inserted."+ idColumn + " " + valuesClause; + } + if (haveDeltaTables) { + return "SELECT " + idColumn + " FROM FINAL TABLE (" + insertClause + " " + valuesClause + ")"; + } + return insertClause + " " + valuesClause + " RETURNING " + idColumn; + } } diff --git a/src/main/java/com/juick/service/MessagesServiceImpl.java b/src/main/java/com/juick/service/MessagesServiceImpl.java index 6874c7bc..1c504a2d 100644 --- a/src/main/java/com/juick/service/MessagesServiceImpl.java +++ b/src/main/java/com/juick/service/MessagesServiceImpl.java @@ -37,6 +37,7 @@ import org.springframework.jdbc.core.RowMapper; import org.springframework.jdbc.core.namedparam.MapSqlParameterSource; import org.springframework.jdbc.core.namedparam.SqlParameterSource; import org.springframework.jdbc.core.simple.SimpleJdbcInsert; +import org.springframework.jdbc.support.GeneratedKeyHolder; import org.springframework.lang.NonNull; import org.springframework.stereotype.Repository; import org.springframework.transaction.annotation.Transactional; @@ -133,22 +134,29 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ @Transactional @Override public int createMessage(final int uid, final String txt, final String attachment, @NonNull final Set tags) { - SimpleJdbcInsert simpleJdbcInsert = new SimpleJdbcInsert(getJdbcTemplate()).withTableName("messages") - .usingColumns("user_id", "attach", "ts", "readonly", "updated", "updated_at", "txt") - .usingGeneratedKeyColumns("message_id"); - var insertMap = new MapSqlParameterSource(); - insertMap.addValue("user_id", uid); var now = Instant.now(); - insertMap.addValue("ts", toDateTime(now.atOffset(ZoneOffset.UTC)), dateTimeType()); - insertMap.addValue("updated", toDateTime(now.atOffset(ZoneOffset.UTC)), dateTimeType()); - insertMap.addValue("updated_at", toDateTime(now.atOffset(ZoneOffset.UTC)), dateTimeType()); - insertMap.addValue("txt", StringUtils.defaultString(txt)); - if (StringUtils.isNotEmpty(attachment)) { - insertMap.addValue("attach", attachment); - } - insertMap.addValue("readonly", TagUtils.hasTag(tags, "readonly")); - int mid = simpleJdbcInsert.executeAndReturnKey(insertMap).intValue(); - if (mid > 0) { + var query = returningId(""" + INSERT INTO messages(user_id, attach, ts, readonly, updated, txt, updated_at)""", + "VALUES(?, ?, ?, ?, ?, ?, ?)", "message_id"); + Integer mid = getJdbcTemplate().execute((ConnectionCallback) con -> { + var nowObject = toDateTime(now.atOffset(ZoneOffset.UTC)); + var stmt = con.prepareStatement(query); + stmt.setInt(1, uid); + stmt.setString(2, attachment); + stmt.setObject(3, nowObject, dateTimeType()); + stmt.setBoolean(4, TagUtils.hasTag(tags, "readonly")); + stmt.setObject(5, nowObject, dateTimeType()); + stmt.setObject(6, StringUtils.defaultString(txt)); + stmt.setObject(7, nowObject, dateTimeType()); + + try (var resultSet = stmt.executeQuery()) { + if (resultSet.next()) { + return resultSet.getInt(1); + } + return 0; + } + }); + if (mid != null && mid > 0) { if (CollectionUtils.isNotEmpty(tags)) { var newTags = new ArrayList<>(tags); getJdbcTemplate().batchUpdate("INSERT INTO messages_tags(message_id, tag_id) VALUES (?, ?)", @@ -819,8 +827,12 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ new MapSqlParameterSource("ids", mids) .addValue("uid", uid), messageMapper); - msgs.forEach(m -> m.setTags(tagService.getMessageTags(m.getMid()).stream() - .map(TagStats::getTag).collect(Collectors.toSet()))); + + msgs.forEach(m -> { + var tags = tagService.getMessageTags(m.getMid()).stream() + .map(TagStats::getTag).collect(Collectors.toSet()); + m.setTags(tags); + }); Map> likes = updateReactionsFor(mids); msgs.forEach(i -> i.setReactions(likes.get(i.getMid()))); msgs.sort(Comparator.comparing(item -> mids.indexOf(item.getMid()))); diff --git a/src/main/java/com/juick/service/TagService.java b/src/main/java/com/juick/service/TagService.java index ef937460..dcdd3a26 100644 --- a/src/main/java/com/juick/service/TagService.java +++ b/src/main/java/com/juick/service/TagService.java @@ -51,12 +51,10 @@ public interface TagService { List getTagStats(); - @CacheEvict(value = "message_tags", key = "#mid") Set updateTags(int mid, Collection newTags); Pair> fromString(String txt); - @Cacheable(value = "message_tags") List getMessageTags(int mid); boolean blacklistTag(User user, Tag tag); diff --git a/src/main/java/com/juick/service/TagServiceImpl.java b/src/main/java/com/juick/service/TagServiceImpl.java index 90d0fc83..9f5c9356 100644 --- a/src/main/java/com/juick/service/TagServiceImpl.java +++ b/src/main/java/com/juick/service/TagServiceImpl.java @@ -24,6 +24,7 @@ import org.apache.commons.collections4.CollectionUtils; import org.apache.commons.lang3.StringUtils; import org.apache.commons.lang3.tuple.Pair; import org.springframework.jdbc.core.BatchPreparedStatementSetter; +import org.springframework.jdbc.core.PreparedStatementCallback; import org.springframework.jdbc.core.RowMapper; import org.springframework.jdbc.core.namedparam.MapSqlParameterSource; import org.springframework.jdbc.support.GeneratedKeyHolder; @@ -132,19 +133,16 @@ public class TagServiceImpl extends BaseJdbcService implements TagService { @Transactional @Override public int createTag(final String name) { - KeyHolder holder = new GeneratedKeyHolder(); - getJdbcTemplate().update( - con -> { - PreparedStatement stmt = con.prepareStatement( - "INSERT INTO tags(name) VALUES (?)", - Statement.RETURN_GENERATED_KEYS); + return getJdbcTemplate().execute(returningId("INSERT INTO tags(name)", "VALUES (?)", "tag_id"), + (PreparedStatementCallback) stmt -> { stmt.setString(1, name); - return stmt; - }, - holder); - - return holder.getKeys().size() > 1 ? ((Number) holder.getKeys().get("tag_id")).intValue() - : holder.getKey().intValue(); + try (var resultSet = stmt.executeQuery()) { + if (resultSet.next()) { + return resultSet.getInt(1); + } + return 0; + } + }); } private class TagStatsMapper implements RowMapper { diff --git a/src/main/java/com/juick/service/UserServiceImpl.java b/src/main/java/com/juick/service/UserServiceImpl.java index 812a4e8b..0bbeea91 100644 --- a/src/main/java/com/juick/service/UserServiceImpl.java +++ b/src/main/java/com/juick/service/UserServiceImpl.java @@ -27,12 +27,16 @@ import org.apache.commons.lang3.tuple.Pair; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.beans.factory.annotation.Value; +import org.springframework.dao.DataAccessException; import org.springframework.dao.DataIntegrityViolationException; import org.springframework.dao.EmptyResultDataAccessException; import org.springframework.jdbc.UncategorizedSQLException; +import org.springframework.jdbc.core.PreparedStatementCallback; import org.springframework.jdbc.core.RowMapper; import org.springframework.jdbc.core.namedparam.MapSqlParameterSource; import org.springframework.jdbc.core.simple.SimpleJdbcInsert; +import org.springframework.jdbc.support.GeneratedKeyHolder; +import org.springframework.jdbc.support.KeyHolder; import org.springframework.stereotype.Repository; import org.springframework.transaction.annotation.Transactional; @@ -93,21 +97,25 @@ public class UserServiceImpl extends BaseJdbcService implements UserService { @Transactional @Override public Optional createUser(final String username, final String password) throws UsernameTakenException { - var userInsert = new SimpleJdbcInsert(getJdbcTemplate()) - .withTableName("users") - .usingColumns("nick", "passw") - .usingGeneratedKeyColumns("id"); var params = new MapSqlParameterSource(); params.addValue("nick", username); params.addValue("passw", password); try { - var uid = userInsert.executeAndReturnKey(params).intValue(); + Integer uid = getNamedParameterJdbcTemplate().execute(returningId("INSERT INTO users(nick, passw)", "VALUES (:nick, :passw)", "id"), + params, ps -> { + try (var resultSet = ps.executeQuery()) { + if (resultSet.next()) { + return resultSet.getInt(1); + } + return 0; + } + }); if (uid > 0) { getJdbcTemplate().update("INSERT INTO subscr_users(user_id, suser_id) VALUES (2, ?)", uid); return getUserByUID(uid); } - } catch (DataIntegrityViolationException | UncategorizedSQLException e) { - throw new UsernameTakenException(); + } catch (Exception e) { + return Optional.empty(); } return Optional.empty(); } -- cgit v1.2.3