Closed github-actions[bot] closed 2 years ago
https://github.com/StruckCroissant/Game-DB/blob/ce0447eeb9492536f57bbcbf51c2c9ced1c1fabf/api/main/java/com/StruckCroissant/GameDB/user/UserDAOImpl.java#L119
package com.StruckCroissant.GameDB.user; import com.StruckCroissant.GameDB.user.models.User; import com.StruckCroissant.GameDB.user.models.UserRoleEnum; import org.jetbrains.annotations.NotNull; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.stereotype.Repository; import org.springframework.stereotype.Service; import java.sql.ResultSet; import java.sql.SQLException; import java.util.List; import java.util.Optional; @Service @Repository("db-user") public class UserDAOImpl implements UserDao { private final JdbcTemplate jdbcTemplate; @Autowired public UserDAOImpl(JdbcTemplate jdbcTemplate) { this.jdbcTemplate = jdbcTemplate; } @Override public int insertUser(User user) { final String sql; if(user.getId() == null){ // Might not need due to Auto_inc - including for posterity sql = "INSERT INTO user (uid, username, password, email) VALUES (?, ?, ?, ?)"; return jdbcTemplate.update(sql, user.getId(), user.getUsername(), user.getPassword(), user.getEmail()); } else { sql = "INSERT INTO user (username, password, email) VALUES (?, ?, ?)"; return jdbcTemplate.update(sql, user.getUsername(), user.getPassword(), user.getEmail()); } } @Override public List<User> selectAllUsers() { final String sql = "SELECT u.uid, u.username, u.password, u.email, u.locked, u.enabled, r.role " + "FROM user u, role r where u.uid = r.uid"; return jdbcTemplate.query(sql, (resultSet, i) -> { return getUser(resultSet); }); } @Override public Optional<User> selectUserById(int id) { final String sql = "SELECT u.uid, u.username, u.password, u.email, u.locked, u.enabled, r.role " + "FROM user u, role r WHERE u.uid = ? AND u.uid = r.uid LIMIT 1"; return Optional.ofNullable( jdbcTemplate.query(sql, (resultSet) -> { if (resultSet.next()) { return getUser(resultSet); } else { return null; } }, id) ); } @NotNull private User getUser(ResultSet resultSet) throws SQLException { int uid = resultSet.getInt("uid"); String username = resultSet.getString("username"); String password = resultSet.getString("password"); String email = resultSet.getString("email"); UserRoleEnum role = UserRoleEnum.valueOf(resultSet.getString("role")); Boolean locked = resultSet.getBoolean("locked"); Boolean enabled = resultSet.getBoolean("enabled"); return new User(uid, username, password, email, role, locked, enabled); } @Override public Optional<User> selectUserByUsername(String username) { final String sql = "SELECT u.uid, u.username, u.password, u.email, u.locked, u.enabled, r.role FROM user u, role r WHERE u.username = ? AND u.uid = r.uid LIMIT 1"; return Optional.ofNullable(jdbcTemplate.query(sql, (ResultSet resultSet) -> { if(resultSet.next()){ return getUser(resultSet); } else { return null; } }, username)); } public Optional<Integer> getUidByUsername(String username){ final String sql = "SELECT uid FROM user WHERE username = ? LIMIT 1"; return Optional.ofNullable(jdbcTemplate.query(sql, (resultSet) -> { if(resultSet.next()){ return resultSet.getInt("uid"); } else { return null; } }, username)); } @Override public int deleteUserById(int id) { final String sql = "DELETE FROM user WHERE uid = ?"; return jdbcTemplate.update(sql, id); } @Override public int updateUserById(int id, User user) { final String sql = "UPDATE user SET username = ? WHERE uid = ?"; return jdbcTemplate.update(sql, user.getUsername(), id); } @Override public int updateUser(User user){ final String SQL_INSERT_USER = "INSERT INTO user (username, password, email, locked, enabled) VALUES (?, ?, ?, ?, ?);"; final String SQL_INSERT_ROLE_USER = "INSERT INTO role (uid, role) VALUES (?, ?);"; int query1 = 0; int query2 = 0; // TODO handle duplicate email query1 = jdbcTemplate.update(SQL_INSERT_USER, user.getUsername(), user.getPassword(), user.getEmail(), !user.isAccountNonLocked(), user.isEnabled()); final int UID = getUidByUsername(user.getUsername()).get(); if(query1 == 1){// TODO find a better implementation for swapping this UID query2 = jdbcTemplate.update(SQL_INSERT_ROLE_USER, UID, user.getRole().toString()); } return (query2 == 1) ? 1 : 0; } @Override public boolean registerNewUser(User user) { if(userIsUnique(user)){ insertUser(user); return true; } else { return false; } } @Override public boolean userIsUnique(User user) { final String sql = "SELECT COUNT(*) FROM user WHERE username = ?"; Integer amnt = jdbcTemplate.query(sql, (resultSet) -> { if(resultSet.next()){ return resultSet.getInt("count(*)"); } else { return null; } }, user.getUsername()); assert amnt != null; return amnt == 0; } public boolean loginUser(User user) { final String sql = "SELECT COUNT(*) FROM user WHERE username = ? AND password = ?"; Integer amnt = jdbcTemplate.query(sql, (resultSet) -> { if(resultSet.next()){ return resultSet.getInt("count(*)"); } else { return null; } }, user.getUsername(), user.getPassword()); assert amnt != null; return amnt == 1; } }
Closed in a1b9fb3bf5a64211b67e9873fb21f102166c2734
https://github.com/StruckCroissant/Game-DB/blob/ce0447eeb9492536f57bbcbf51c2c9ced1c1fabf/api/main/java/com/StruckCroissant/GameDB/user/UserDAOImpl.java#L119