@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);
}
}
