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