Exemple #1
0
 /**
  * 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);
   }
 }
Exemple #4
0
 /**
  * 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);
   }
 }
Exemple #5
0
 /**
  * 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);
   }
 }
Exemple #6
0
 /**
  * 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);
   }
 }
Exemple #7
0
 /**
  * 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);
   }
 }
Exemple #8
0
 /**
  * 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);
   }
 }