@Override public void insert(User user) throws DBException, NotUniqueUserLoginException, NotUniqueUserEmailException { Connection conn = getConnection(); PreparedStatement ps = null; try { conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE); conn.setAutoCommit(false); if (existWithLogin0(conn, user.getLogin())) { throw new NotUniqueUserLoginException("Login '" + user.getLogin() + "' doubled"); } if (existWithEmail0(conn, user.getEmail())) { throw new NotUniqueUserEmailException("Email '" + user.getEmail() + "' doubled"); } ps = conn.prepareStatement(INSERT_SQL); ps.setString(1, user.getLogin()); ps.setString(2, user.getEmail()); ps.executeUpdate(); conn.commit(); } catch (SQLException e) { JdbcUtils.rollbackQuietly(conn); e.printStackTrace(); throw new DBException("Can't execute SQL = '" + INSERT_SQL + "'", e); } finally { JdbcUtils.closeQuietly(ps); JdbcUtils.closeQuietly(conn); } }
@Override public List<User> selectAll() throws DBException { Connection conn = getConnection(); Statement statement = null; ResultSet rs = null; try { conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE); conn.setAutoCommit(false); statement = conn.createStatement(); rs = statement.executeQuery(SELECT_ALL_SQL); List<User> result = new ArrayList<User>(); while (rs.next()) { int id = rs.getInt("id"); String login = rs.getString("login"); String email = rs.getString("email"); User user = new User(id); user.setLogin(login); user.setEmail(email); result.add(user); } conn.commit(); return result; } catch (SQLException e) { JdbcUtils.rollbackQuietly(conn); throw new DBException("Can't execute SQL = '" + SELECT_ALL_SQL + "'", e); } finally { JdbcUtils.closeQuietly(rs); JdbcUtils.closeQuietly(statement); JdbcUtils.closeQuietly(conn); } }
@Override public int deleteById(int id) throws DBException { Connection conn = getConnection(); PreparedStatement ps = null; try { conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE); conn.setAutoCommit(false); ps = conn.prepareStatement(DELETE_BY_ID_SQL); ps.setInt(1, id); int result = ps.executeUpdate(); conn.commit(); return result; } catch (SQLException e) { JdbcUtils.rollbackQuietly(conn); throw new DBException("Can't execute SQL = '" + DELETE_BY_ID_SQL + "'", e); } finally { JdbcUtils.closeQuietly(ps); JdbcUtils.closeQuietly(conn); } }