aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorGravatar Vitaly Takmazov2016-01-14 17:01:28 +0300
committerGravatar Vitaly Takmazov2016-01-14 17:01:28 +0300
commit7248c94dfd3fecda1c854e09731592a03a79ca31 (patch)
tree8a9f6794c430d1dec61a4646843fae7ffe7ffe9e
parentc37ed56f884661666b964e47efd23a36be9b24d4 (diff)
spring-jdbc WIP
-rw-r--r--build.gradle8
-rw-r--r--src/main/java/com/juick/server/UserQueries.java274
2 files changed, 89 insertions, 193 deletions
diff --git a/build.gradle b/build.gradle
index 405a81af..b82c3d5c 100644
--- a/build.gradle
+++ b/build.gradle
@@ -1 +1,9 @@
apply plugin: "java"
+
+repositories {
+ mavenCentral()
+}
+
+dependencies {
+ compile "org.springframework:spring-jdbc:4.2.4.RELEASE"
+} \ No newline at end of file
diff --git a/src/main/java/com/juick/server/UserQueries.java b/src/main/java/com/juick/server/UserQueries.java
index 98573bfb..bf56e974 100644
--- a/src/main/java/com/juick/server/UserQueries.java
+++ b/src/main/java/com/juick/server/UserQueries.java
@@ -18,6 +18,11 @@
package com.juick.server;
import com.juick.User;
+import org.springframework.jdbc.core.JdbcTemplate;
+import org.springframework.jdbc.core.PreparedStatementCreator;
+import org.springframework.jdbc.core.RowMapper;
+import org.springframework.jdbc.support.GeneratedKeyHolder;
+import org.springframework.jdbc.support.KeyHolder;
import java.sql.Connection;
import java.sql.PreparedStatement;
@@ -41,167 +46,73 @@ public class UserQueries {
private static Logger logger = Logger.getLogger(UserQueries.class.getName());
- public static String getSignUpHashByJID(Connection sql, String jid) {
- String hash = SQLHelpers.getString(sql, "SELECT loginhash FROM jids WHERE jid=? AND user_id IS NULL", jid);
+ static class UserMapper implements RowMapper<User> {
+ @Override
+ public User mapRow(ResultSet rs, int rowNum) throws SQLException {
+ User user = new User();
+ user.UID = rs.getInt("id");
+ user.UName = rs.getString("nick");
+ user.Banned = rs.getBoolean("banned");
+ return user;
+ }
+ }
+ public static String getSignUpHashByJID(JdbcTemplate sql, String jid) {
+ String hash = sql.queryForObject("SELECT loginhash FROM jids WHERE jid=? AND user_id IS NULL",
+ String.class, jid);
if (hash == null) {
hash = UUID.randomUUID().toString();
- PreparedStatement stmt = null;
- try {
- stmt = sql.prepareStatement("INSERT INTO jids(jid,loginhash) VALUES (?,?)");
- stmt.setString(1, jid);
- stmt.setString(2, hash);
- stmt.executeUpdate();
- } catch (SQLException e) {
- logger.log(Level.SEVERE, "sql exception", e);
- } finally {
- Utils.finishSQL(null, stmt);
- }
+ sql.update("INSERT INTO jids(jid,loginhash) VALUES (?,?)", jid, hash);
}
-
return hash;
}
- public static int createUser(Connection sql, String username, String password) {
- int uid = 0;
+ public static int createUser(JdbcTemplate sql, String username, String password) {
+ KeyHolder holder = new GeneratedKeyHolder();
+ sql.update(con -> {
+ PreparedStatement stmt = con.prepareStatement("INSERT INTO users(nick,passw) VALUES (?,?)",
+ Statement.RETURN_GENERATED_KEYS);
+ stmt.setString(1, username);
+ stmt.setString(2, password);
+ return stmt;
+ }, holder);
- PreparedStatement stmt = null;
- ResultSet rs = null;
- try {
- stmt = sql.prepareStatement("INSERT INTO users(nick,passw) VALUES (?,?)", Statement.RETURN_GENERATED_KEYS);
- stmt.setString(1, username);
- stmt.setString(2, password);
- stmt.executeUpdate();
- rs = stmt.getGeneratedKeys();
- if (rs.first()) {
- uid = rs.getInt(1);
- }
- } catch (SQLException e) {
- logger.log(Level.SEVERE, "sql exception", e);
- } finally {
- Utils.finishSQL(rs, stmt);
- }
+ int uid = holder.getKey().intValue();
- SQLHelpers.executeInt(sql, "INSERT INTO useroptions(user_id) VALUES (?)", uid);
- SQLHelpers.executeInt(sql, "INSERT INTO subscr_users(user_id,suser_id) VALUES (2,?)", uid);
+ sql.update("INSERT INTO useroptions(user_id) VALUES (?)", uid);
+ sql.update("INSERT INTO subscr_users(user_id,suser_id) VALUES (2,?)", uid);
return uid;
}
- public static com.juick.User getUserByUID(Connection sql, int uid) {
- com.juick.User user = null;
-
- PreparedStatement stmt = null;
- ResultSet rs = null;
- try {
- stmt = sql.prepareStatement("SELECT nick,banned FROM users WHERE id=?");
- stmt.setInt(1, uid);
- rs = stmt.executeQuery();
- if (rs.first()) {
- user = new com.juick.User();
- user.UID = uid;
- user.UName = rs.getString(1);
- user.Banned = rs.getBoolean(2);
- }
- } catch (SQLException e) {
- logger.log(Level.SEVERE, "sql exception", e);
- } finally {
- Utils.finishSQL(rs, stmt);
- }
- return user;
+ public static User getUserByUID(JdbcTemplate sql, int uid) {
+ return sql.queryForObject("SELECT id, nick,banned FROM users WHERE id=?",
+ new Object[] {uid},
+ new UserMapper());
}
- public static com.juick.User getUserByName(Connection sql, String username) {
- com.juick.User user = null;
-
- PreparedStatement stmt = null;
- ResultSet rs = null;
- try {
- stmt = sql.prepareStatement("SELECT id,nick,banned FROM users WHERE nick=?");
- stmt.setString(1, username);
- rs = stmt.executeQuery();
- if (rs.first()) {
- user = new com.juick.User();
- user.UID = rs.getInt(1);
- user.UName = rs.getString(2);
- user.Banned = rs.getBoolean(3);
- }
- } catch (SQLException e) {
- logger.log(Level.SEVERE, "sql exception", e);
- } finally {
- Utils.finishSQL(rs, stmt);
- }
- return user;
+ public static User getUserByName(JdbcTemplate sql, String username) {
+ return sql.queryForObject("SELECT id,nick,banned FROM users WHERE nick=?",
+ new Object[] {username},
+ new UserMapper());
}
- public static com.juick.User getUserByJID(Connection sql, String jid) {
- com.juick.User user = null;
-
- PreparedStatement stmt = null;
- ResultSet rs = null;
- try {
- stmt = sql.prepareStatement("SELECT id,nick FROM users WHERE id=(SELECT user_id FROM jids WHERE jid=?)");
- stmt.setString(1, jid);
- rs = stmt.executeQuery();
- if (rs.first()) {
- user = new com.juick.User();
- user.UID = rs.getInt(1);
- user.UName = rs.getString(2);
- user.JID = jid;
- }
- } catch (SQLException e) {
- logger.log(Level.SEVERE, "sql exception", e);
- } finally {
- Utils.finishSQL(rs, stmt);
- }
- return user;
+ public static User getUserByJID(JdbcTemplate sql, String jid) {
+ return sql.queryForObject("SELECT id,nick,banned FROM users WHERE id=(SELECT user_id FROM jids WHERE jid=?)",
+ new Object[] {jid},
+ new UserMapper());
}
- public static List<com.juick.User> getUsersByName(Connection sql, List<String> unames) {
- List<com.juick.User> users = new ArrayList<>();
-
- PreparedStatement stmt = null;
- ResultSet rs = null;
- try {
- stmt = sql.prepareStatement("SELECT id,nick FROM users WHERE nick IN (" + Utils.convertArrayString2String(unames) + ")");
- rs = stmt.executeQuery();
- rs.beforeFirst();
- while (rs.next()) {
- com.juick.User user = new com.juick.User();
- user.UID = rs.getInt(1);
- user.UName = rs.getString(2);
- users.add(user);
- }
- } catch (SQLException e) {
- logger.log(Level.SEVERE, "sql exception", e);
- } finally {
- Utils.finishSQL(rs, stmt);
- }
- return users;
+ public static List<User> getUsersByName(JdbcTemplate sql, List<String> unames) {
+ return sql.query("SELECT id,nick,banned FROM users WHERE id=(SELECT user_id FROM jids WHERE jid=?)",
+ new UserMapper(),
+ unames.toArray());
}
- public static List<com.juick.User> getUsersByID(Connection sql, List<Integer> uids) {
- List<com.juick.User> users = new ArrayList<>();
-
- PreparedStatement stmt = null;
- ResultSet rs = null;
- try {
- stmt = sql.prepareStatement("SELECT id,nick FROM users WHERE id IN (" + Utils.convertArrayInt2String(uids) + ")");
- rs = stmt.executeQuery();
- rs.beforeFirst();
- while (rs.next()) {
- com.juick.User u = new com.juick.User();
- u.UID = rs.getInt(1);
- u.UName = rs.getString(2);
- users.add(u);
- }
- } catch (SQLException e) {
- logger.log(Level.SEVERE, "sql exception", e);
- } finally {
- Utils.finishSQL(rs, stmt);
- }
-
- return users;
+ public static List<User> getUsersByID(JdbcTemplate sql, List<Integer> uids) {
+ return sql.query("SELECT id,nick,banned FROM users WHERE id IN (" + Utils.convertArrayInt2String(uids) + ")",
+ new UserMapper(),
+ uids.toArray());
}
public static boolean fillUsersByID(Connection sql, List<com.juick.User> users) {
@@ -246,7 +157,9 @@ public class UserQueries {
PreparedStatement stmt = null;
ResultSet rs = null;
try {
- stmt = sql.prepareStatement("SELECT users.id,users.nick,jids.jid FROM users INNER JOIN jids ON jids.user_id=users.id WHERE jids.jid IN (" + Utils.convertArrayString2String(jids) + ")");
+ stmt = sql.prepareStatement("SELECT users.id,users.nick,jids.jid FROM users " +
+ "INNER JOIN jids ON jids.user_id=users.id " +
+ "WHERE jids.jid IN (" + Utils.convertArrayString2String(jids) + ")");
rs = stmt.executeQuery();
rs.beforeFirst();
while (rs.next()) {
@@ -264,64 +177,43 @@ public class UserQueries {
return users;
}
- public static String getJIDbyUID(Connection sql, int uid) {
- return SQLHelpers.getString(sql, "SELECT jid FROM jids WHERE user_id=? AND active=1", uid);
+ public static String getJIDbyUID(JdbcTemplate sql, int uid) {
+ return sql.queryForObject("SELECT jid FROM jids WHERE user_id=? AND active=1", String.class, uid);
}
- public static int getUIDbyJID(Connection sql, String jid) {
- return SQLHelpers.getInt(sql, "SELECT user_id FROM jids WHERE jid=?", jid, 0);
+ public static int getUIDbyJID(JdbcTemplate sql, String jid) {
+ return sql.queryForObject("SELECT user_id FROM jids WHERE jid=?", Integer.class, jid);
}
- public static int getUIDbyName(Connection sql, String uname) {
- return SQLHelpers.getInt(sql, "SELECT id FROM users WHERE nick=?", uname, 0);
+ public static int getUIDbyName(JdbcTemplate sql, String uname) {
+ return sql.queryForObject("SELECT id FROM users WHERE nick=?", Integer.class, uname);
}
- public static int getUIDbyHash(Connection sql, String hash) {
- return SQLHelpers.getInt(sql, "SELECT user_id FROM logins WHERE hash=?", hash, 0);
+ public static int getUIDbyHash(JdbcTemplate sql, String hash) {
+ return sql.queryForObject("SELECT user_id FROM logins WHERE hash=?", Integer.class, hash);
}
- public static com.juick.User getUserByHash(Connection sql, String hash) {
- com.juick.User user = null;
-
- PreparedStatement stmt = null;
- ResultSet rs = null;
- try {
- stmt = sql.prepareStatement("SELECT logins.user_id,users.nick, users.banned FROM logins INNER JOIN users ON logins.user_id=users.id WHERE logins.hash=?");
- stmt.setString(1, hash);
- rs = stmt.executeQuery();
- if (rs.first()) {
- user = new com.juick.User();
- user.UID = rs.getInt(1);
- user.UName = rs.getString(2);
- user.Banned = rs.getBoolean(3);
- user.AuthHash = hash;
- }
- } catch (SQLException e) {
- logger.log(Level.SEVERE, "sql exception", e);
- } finally {
- Utils.finishSQL(rs, stmt);
- }
+ public static com.juick.User getUserByHash(JdbcTemplate sql, String hash) {
+ User user = sql.queryForObject("SELECT logins.user_id,users.nick, users.banned FROM logins " +
+ "INNER JOIN users ON logins.user_id=users.id WHERE logins.hash=?",
+ new UserMapper(), hash);
+ user.AuthHash = hash;
return user;
}
- public static String getHashByUID(Connection sql, int uid) {
- String hash = SQLHelpers.getString(sql, "SELECT hash FROM logins WHERE user_id=?", uid);
+ public static String getHashByUID(JdbcTemplate sql, int uid) {
+ String hash = sql.queryForObject("SELECT hash FROM logins WHERE user_id=?", String.class, uid);
if (hash == null) {
hash = generateHash(16);
- PreparedStatement stmt = null;
- try {
- stmt = sql.prepareStatement("INSERT INTO logins(user_id,hash) VALUES (?,?)");
+ final String finalHash = hash;
+ sql.update(con -> {
+ PreparedStatement stmt = con.prepareStatement("INSERT INTO logins(user_id,hash) VALUES (?,?)");
stmt.setInt(1, uid);
- stmt.setString(2, hash);
- stmt.executeUpdate();
- } catch (SQLException e) {
- logger.log(Level.SEVERE, "sql exception", e);
- } finally {
- Utils.finishSQL(null, stmt);
- }
+ stmt.setString(2, finalHash);
+ return stmt;
+ });
}
-
return hash;
}
@@ -630,23 +522,19 @@ public class UserQueries {
Active
}
- public static boolean setActiveStatusForJID(Connection sql, String JID, ActiveStatus jidStatus) {
+ public static boolean setActiveStatusForJID(JdbcTemplate sql, String JID, ActiveStatus jidStatus) {
User user = getUserByJID(sql, JID);
if (user != null) {
- PreparedStatement preparedStatement = null;
- try {
- preparedStatement = sql.prepareStatement(
+ return sql.update(con -> {
+ PreparedStatement preparedStatement = con.prepareStatement(
"UPDATE jids SET active=? WHERE user_id=? AND jid=?");
int newStatus = jidStatus == ActiveStatus.Active ? 1 : 0;
preparedStatement.setInt(1, newStatus);
preparedStatement.setInt(2, user.UID);
preparedStatement.setString(3, JID);
- return preparedStatement.executeUpdate() >= 0;
- } catch (SQLException e) {
- logger.log(Level.SEVERE, "sql exception", e);
- } finally {
- Utils.finishSQL(null, preparedStatement);
- }
+ return preparedStatement;
+
+ }) >= 0;
}
return false;
}