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/UserServiceImpl.java    | 22 +++++++++++++++-------
 1 file changed, 15 insertions(+), 7 deletions(-)

(limited to 'src/main/java/com/juick/service/UserServiceImpl.java')

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<User> 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