/**
* Issue multiple SQL updates on a single JDBC Statement using batching. Will fall back to
* separate updates on a single Statement if the JDBC driver does not support batch updates.
*
* @param sqls
* @return
* @throws SQLException
*/
public int[] batchUpdate(final String[] sqls) {
validateSqlParameter(sqls);
Connection conn = null;
Statement stmt = null;
try {
conn = getConnection();
conn.setAutoCommit(false);
applyQueryTimeOut(stmt);
stmt = conn.createStatement();
for (String sql : sqls) {
if (showSql) {
logger.info(getFinalSql(sql));
}
stmt.addBatch(sql);
}
int[] result = stmt.executeBatch();
conn.commit();
return result;
} catch (SQLException e) {
try {
JdbcUtils.rollback(conn);
} catch (Exception unused) {
}
throw new RuntimeException(e);
} finally {
JdbcUtils.closeQuietly(stmt);
JdbcUtils.closeQuietly(conn);
}
}
@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);
}
}
/**
* Issue a single SQL update operation (such as an insert, update or delete statement) via a
* prepared statement, binding the given arguments.
*
* @param sql
* @param args
* @param argTypes
* @return
* @throws SQLException
*/
public int update(final String sql, Object[] args, int[] argTypes) {
validateSqlParameter(sql);
if (showSql) {
logger.info(getFinalSql(sql, args, argTypes));
}
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = getConnection();
pstmt = conn.prepareStatement(sql);
applyQueryTimeOut(pstmt);
if (args != null) {
if (argTypes != null) {
JdbcUtils.setPreparedStatementValue(pstmt, args, argTypes);
} else {
JdbcUtils.setPreparedStatementValue(pstmt, args);
}
}
return pstmt.executeUpdate();
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
JdbcUtils.closeQuietly(pstmt);
JdbcUtils.closeQuietly(conn);
}
}
/**
* Query all records, reflection to a JavaBean List
*
* @param type
* @param sql
* @param args
* @param sqlTypes
* @return
* @throws Exception
*/
public <T> List<T> queryObjectListUseReflection(
Class<T> type, final String sql, Object[] args, int[] sqlTypes) {
if (showSql) {
logger.info(getFinalSql(sql, args, sqlTypes));
}
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
conn = getConnection();
pstmt = conn.prepareStatement(sql);
applyQueryTimeOut(pstmt);
if (args != null) {
if (sqlTypes != null) {
JdbcUtils.setPreparedStatementValue(pstmt, args, sqlTypes);
} else {
JdbcUtils.setPreparedStatementValue(pstmt, args);
}
}
rs = pstmt.executeQuery();
return ResultSetUtils.getObjectListUseReflection(type, rs);
} catch (Exception e) {
throw new RuntimeException(e);
} finally {
JdbcUtils.closeQuietly(conn, pstmt, rs);
}
}
/**
* Query given SQL to create a prepared statement from SQL and a list of arguments to bind to the
* query, mapping each row to a Java object via a RowMapper.
*
* @param sql
* @param args
* @param sqlTypes
* @param rowMapper
* @return
* @throws SQLException
*/
public List query(final String sql, Object[] args, int[] sqlTypes, RowMapper rowMapper) {
if (showSql) {
logger.info(getFinalSql(sql, args, sqlTypes));
}
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
conn = getConnection();
pstmt = conn.prepareStatement(sql);
if (args != null) {
if (sqlTypes == null) {
JdbcUtils.setPreparedStatementValue(pstmt, args);
} else {
JdbcUtils.setPreparedStatementValue(pstmt, args, sqlTypes);
}
}
applyQueryTimeOut(pstmt);
rs = pstmt.executeQuery();
if (rs == null) {
return null;
} else {
List resultList = new ArrayList();
while (rs.next()) {
resultList.add(rowMapper.mapRow(rs, rs.getRow()));
}
return resultList;
}
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
JdbcUtils.closeQuietly(conn, pstmt, rs);
}
}
/**
* Execute SQL query and return RowSet List
*
* @param sql
* @param args
* @param sqlTypes
* @return
* @throws SQLException
*/
public List<Row> queryForRowList(final String sql, Object[] args, int[] sqlTypes) {
if (showSql) {
logger.info(getFinalSql(sql, args, sqlTypes));
}
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
conn = getConnection();
pstmt = conn.prepareStatement(sql);
if (args != null) {
if (sqlTypes == null) {
JdbcUtils.setPreparedStatementValue(pstmt, args);
} else {
JdbcUtils.setPreparedStatementValue(pstmt, args, sqlTypes);
}
}
applyQueryTimeOut(pstmt);
rs = pstmt.executeQuery();
if (rs == null) {
return null;
}
return Row.valueOf(rs);
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
JdbcUtils.closeQuietly(conn, pstmt, rs);
}
}
/**
* Issue a single SQL execute, typically a DDL statement.
*
* @param sql
* @throws SQLException
*/
public void execute(final String sql) {
if (showSql) {
logger.info(getFinalSql(sql));
}
Connection conn = null;
Statement stmt = null;
try {
conn = getConnection();
stmt = conn.createStatement();
applyQueryTimeOut(stmt);
stmt.execute(sql);
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
JdbcUtils.closeQuietly(stmt);
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);
}
}