How to use

SQLException

in

java.sql

private String getOriginalTaskId(final String taskId) {
  String sql = String.format("SELECT original_task_id FROM %s WHERE task_id = '%s' and state='%s' LIMIT 1", TABLE_JOB_STATUS_TRACE_LOG, taskId, State.TASK_STAGING);
  String result = "";
  try (
      Connection conn = dataSource.getConnection();
      PreparedStatement preparedStatement = conn.prepareStatement(sql);
      ResultSet resultSet = preparedStatement.executeQuery()
  ) {
    if (resultSet.next()) {
      return resultSet.getString("original_task_id");
    }
  } catch (final SQLException ex) {
    // TODO 记录失败直接输出日志,未来可考虑配置化
    log.error(ex.getMessage());
  }
  return result;
}

 public void create(User user) throws SQLException {
  try (
    Connection connection = dataSource.getConnection();
    PreparedStatement statement = connection.prepareStatement(SQL_INSERT,
                   Statement.RETURN_GENERATED_KEYS);
  ) {
    statement.setString(1, user.getName());
    statement.setString(2, user.getPassword());
    statement.setString(3, user.getEmail());
    // ...

    int affectedRows = statement.executeUpdate();

    if (affectedRows == 0) {
      throw new SQLException("Creating user failed, no rows affected.");
    }

    try (ResultSet generatedKeys = statement.getGeneratedKeys()) {
      if (generatedKeys.next()) {
        user.setId(generatedKeys.getLong(1));
      }
      else {
        throw new SQLException("Creating user failed, no ID obtained.");
      }
    }
  }
}

public void release() {
  try {
    if (null != rs) {
      rs.close();
    }
    if (null != pstmt) {
      pstmt.close();
    }
    if (null != conn) {
      conn.close();
    }
  } catch (SQLException e) {
    e.printStackTrace();
  }
  System.out.println("释放数据库连接");
}

/**
 * Constructor for UncategorizedSQLException.
 * @param task name of current task
 * @param sql the offending SQL statement
 * @param ex the root cause
 */
public UncategorizedSQLException(String task, @Nullable String sql, SQLException ex) {
  super(task + "; uncategorized SQLException" + (sql != null ? " for SQL [" + sql + "]" : "") +
      "; SQL state [" + ex.getSQLState() + "]; error code [" + ex.getErrorCode() + "]; " +
      ex.getMessage(), ex);
  this.sql = sql;
}

/**
 * Gets the SQL state code from the supplied {@link SQLException exception}.
 * <p>Some JDBC drivers nest the actual exception from a batched update, so we
 * might need to dig down into the nested exception.
 * @param ex the exception from which the {@link SQLException#getSQLState() SQL state}
 * is to be extracted
 * @return the SQL state code
 */
@Nullable
private String getSqlState(SQLException ex) {
  String sqlState = ex.getSQLState();
  if (sqlState == null) {
    SQLException nestedEx = ex.getNextException();
    if (nestedEx != null) {
      sqlState = nestedEx.getSQLState();
    }
  }
  return sqlState;
}

/**
 * For the given SQLException, locates the vendor-specific error code.
 *
 * @param sqlException The exception from which to extract the SQLState
 * @return The error code.
 */
public static int extractErrorCode(SQLException sqlException) {
  int errorCode = sqlException.getErrorCode();
  SQLException nested = sqlException.getNextException();
  while ( errorCode == 0 && nested != null ) {
    errorCode = nested.getErrorCode();
    nested = nested.getNextException();
  }
  return errorCode;
}

@Before
public void setUp() throws ClassNotFoundException, SQLException {
 sc = new JavaSparkContext("local", "JavaAPISuite");
 Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
 Connection connection =
  DriverManager.getConnection("jdbc:derby:target/JavaJdbcRDDSuiteDb;create=true");
 try {
  Statement create = connection.createStatement();
  create.execute(
   "CREATE TABLE FOO(" +
   "ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1)," +
   "DATA INTEGER)");
  create.close();
  PreparedStatement insert = connection.prepareStatement("INSERT INTO FOO(DATA) VALUES(?)");
  for (int i = 1; i <= 100; i++) {
   insert.setInt(1, i * 2);
   insert.executeUpdate();
  }
  insert.close();
 } catch (SQLException e) {
  // If table doesn't exist...
  if (e.getSQLState().compareTo("X0Y32") != 0) {
   throw e;
  }
 } finally {
  connection.close();
 }
}

@Test
public void testSQLErrorCodeTranslationWithSpecifiedDbName() throws Exception {
  final SQLException sqlException = new SQLException("I have a known problem", "99999", 1054);
  final String sql = "SELECT ID FROM CUSTOMER";
  given(this.resultSet.next()).willReturn(true);
  given(this.connection.createStatement()).willReturn(this.preparedStatement);
  JdbcTemplate template = new JdbcTemplate();
  template.setDataSource(this.dataSource);
  template.setDatabaseProductName("MySQL");
  template.afterPropertiesSet();
  this.thrown.expect(BadSqlGrammarException.class);
  this.thrown.expect(exceptionCause(sameInstance(sqlException)));
  try {
    template.query(sql, (RowCallbackHandler) rs -> {
      throw sqlException;
    });
  }
  finally {
    verify(this.resultSet).close();
    verify(this.preparedStatement).close();
    verify(this.connection).close();
  }
}

public void delete(String email) {
  try (Connection con = ConnectionUtil.getConnection()) {
    String deleteQuery = "DELETE FROM users WHERE email=?";
    PreparedStatement pstmt = con.prepareStatement(deleteQuery);
    pstmt.setString(1, email);
    pstmt.executeUpdate();
  } catch (SQLException exc) {
    logger.error(exc.getMessage());
  }
}

@Test
public void testFactoryMethodConfig() throws Exception {
  try (final Connection connection = jdbcRule.getConnectionSource().getConnection()) {
    final SQLException exception = new SQLException("Some other error message!");
    final ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
    try (final PrintWriter writer = new PrintWriter(outputStream)) {
      exception.printStackTrace(writer);
    logger.debug("Factory logged message 01.");
    logger.error("Error from factory 02.", exception);
    try (final Statement statement = connection.createStatement();
        final ResultSet resultSet = statement.executeQuery("SELECT * FROM fmLogEntry ORDER BY id")) {
      assertTrue("There should be at least one row.", resultSet.next());
      long date = resultSet.getTimestamp("eventDate").getTime();
      long anotherDate = resultSet.getTimestamp("anotherDate").getTime();
      assertEquals(date, anotherDate);
      assertTrue("The date should be later than pre-logging (1).", date >= millis);
          resultSet.getString("literalColumn"));
      assertEquals("The level column is not correct (1).", "DEBUG", resultSet.getNString("level"));
      assertEquals("The logger column is not correct (1).", logger.getName(), resultSet.getNString("logger"));
      assertEquals("The message column is not correct (1).", "Factory logged message 01.",
          resultSet.getString("message"));

/**
 * {@inheritDoc}
 */
@Override
public Optional<Customer> getById(int id) throws Exception {
 ResultSet resultSet = null;
 try (Connection connection = getConnection();
   PreparedStatement statement = 
     connection.prepareStatement("SELECT * FROM CUSTOMERS WHERE ID = ?")) {
  statement.setInt(1, id);
  resultSet = statement.executeQuery();
  if (resultSet.next()) {
   return Optional.of(createCustomer(resultSet));
  } else {
   return Optional.empty();
  }
 } catch (SQLException ex) {
  throw new CustomException(ex.getMessage(), ex);
 } finally {
  if (resultSet != null) {
   resultSet.close();
  }
 }
}

  @Override
  public Connection getConnection() throws SQLException {
    Connection connection = super.getConnection();
    try(Statement statement = connection.createStatement()) {
      if ( dbName == null ) {
        try(ResultSet rs = statement.executeQuery( "SELECT DB_NAME()" )) {
          rs.next();
          dbName = rs.getString( 1 );
        }
      }
      statement.executeUpdate(String.format( RCS, dbName, "ON" ));
      statement.executeUpdate(String.format( SI, dbName, "ON" ));
    }
    catch (SQLException se) {
      fail( se.getMessage());
    }
    return connection;
  }
}

@Test
public void testCouldNotClose() throws Exception {
  SQLException sqlException = new SQLException("bar");
  given(this.connection.createStatement()).willReturn(this.statement);
  given(this.resultSet.next()).willReturn(false);
  willThrow(sqlException).given(this.resultSet).close();
  willThrow(sqlException).given(this.statement).close();
  willThrow(sqlException).given(this.connection).close();
  RowCountCallbackHandler rcch = new RowCountCallbackHandler();
  this.template.query("SELECT ID, FORENAME FROM CUSTMR WHERE ID < 3", rcch);
  verify(this.connection).close();
}

try {
  conn = ds.getConnection();
  stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
  stmt.setFetchSize(Integer.MIN_VALUE);
  rs = stmt.executeQuery(sql);
  return fun.apply(rs);
} finally {
  if (rs != null) {
    try {
      rs.close();
    } catch (SQLException e) {
      logger.error(e.getMessage(), e);
      stmt.close();
    } catch (SQLException e) {
      logger.error(e.getMessage(), e);
      conn.close();
    } catch (SQLException e) {
      logger.error(e.getMessage(), e);

private boolean isReadOnly(final Connection conn) throws SQLException {
 final Statement stmt = conn.createStatement();
 final ResultSet rs = stmt.executeQuery("SELECT @@global.read_only");
 if (rs.next()) {
  final int value = rs.getInt(1);
  return value != 0;
 }
 throw new SQLException("can not fetch read only value from DB");
}

 /**
  * {@inheritDoc}
  */
 @Override
 public boolean delete(Customer customer) throws Exception {
  try (Connection connection = getConnection();
    PreparedStatement statement = 
      connection.prepareStatement("DELETE FROM CUSTOMERS WHERE ID = ?")) {
   statement.setInt(1, customer.getId());
   return statement.executeUpdate() > 0;
  } catch (SQLException ex) {
   throw new CustomException(ex.getMessage(), ex);
  }
 }
}

public String getSchema() throws SQLException {
 if (isClosed) {
  throw new SQLException("Connection is closed");
 }
 try (Statement stmt = createStatement();
    ResultSet res = stmt.executeQuery("SELECT current_database()")) {
  if (!res.next()) {
   throw new SQLException("Failed to get schema information");
  }
  return res.getString(1);
 }
}

/** Return rank of 1 gram in google ngeams if it is less than 20k. Otherwise -1. */
public static int get1GramRank(String str) {
 String query = null;
 try{
  connect();
  str = str.trim();
  if(str.contains("'")){
   str = StringUtils.escapeString(str, new char[]{'\''},'\'');
  }
  int ngram = str.split("\\s+").length;
  if(ngram > 1)
   return -1;
  String table =  "googlengrams_1_ranked20k";
  if(!existsTable(table))
   return -1;
  String phrase = escapeString(str);
  query = "select rank from " + table + " where phrase='" + phrase+"';";
  Statement stmt = connection.createStatement();
  ResultSet result = stmt.executeQuery(query);
  if(result.next()){
   return result.getInt("rank");
  }else
   return -1;
 }catch(SQLException e){
  log.info("Error getting count for " + str+ ". The query was " + query);
  e.printStackTrace();
  throw new RuntimeException(e);
 }
}

public void validateConnection(Connection conn) throws SQLException {
  String query = getValidationQuery();
  if (conn.isClosed()) {
    throw new SQLException("validateConnection: connection closed");
        new SQLException("validateConnection false", error) //
        : new SQLException("validateConnection false");
      throw sqlError;
    ResultSet rs = null;
    try {
      stmt = conn.createStatement();
      if (getValidationQueryTimeout() > 0) {
        stmt.setQueryTimeout(getValidationQueryTimeout());
      rs = stmt.executeQuery(query);
      if (!rs.next()) {
        throw new SQLException("validationQuery didn't return a row");

ResultSet rs = null;
try {
  stmt = conn.createStatement();
  rs = stmt.executeQuery(sql);
  consumer.accept(rs);
} catch (SQLException e) {
  logger.error(e.getMessage(), e);
} finally {
  if (rs != null) {
    try {
      rs.close();
    } catch (SQLException e) {
      logger.error(e.getMessage(), e);
      stmt.close();
    } catch (SQLException e) {
      logger.error(e.getMessage(), e);