public List<MonitorApp> listApp(String domain) throws SQLException { List<MonitorApp> list = new ArrayList<MonitorApp>(); String sql = "select id, domain, app from druid_app " // + " where domain = ?"; Connection conn = null; PreparedStatement stmt = null; ResultSet rs = null; try { conn = dataSource.getConnection(); stmt = conn.prepareStatement(sql); stmt.setString(1, domain); rs = stmt.executeQuery(); if (rs.next()) { list.add(readApp(rs)); } return list; } finally { JdbcUtils.close(rs); JdbcUtils.close(stmt); JdbcUtils.close(conn); } }
@Override public void setLogWriter(PrintWriter out) throws SQLException { this.dataSource.setLogWriter(out); }
@Override public PrintWriter getLogWriter() throws SQLException { return this.dataSource.getLogWriter(); }
@Test public void testOracleSequenceMaxValueIncrementer() throws SQLException { given(dataSource.getConnection()).willReturn(connection); given(connection.createStatement()).willReturn(statement); given(statement.executeQuery("select myseq.nextval from dual")).willReturn(resultSet); given(resultSet.next()).willReturn(true); given(resultSet.getLong(1)).willReturn(10L, 12L); OracleSequenceMaxValueIncrementer incrementer = new OracleSequenceMaxValueIncrementer(); incrementer.setDataSource(dataSource); incrementer.setIncrementerName("myseq"); incrementer.setPaddingLength(2); incrementer.afterPropertiesSet(); assertEquals(10, incrementer.nextLongValue()); assertEquals("12", incrementer.nextStringValue()); verify(resultSet, times(2)).close(); verify(statement, times(2)).close(); verify(connection, times(2)).close(); }
int countAfter = 0; InitialContext initialContext = new InitialContext(ht); DataSource ds = (DataSource) initialContext.lookup("jdbc/EdecDataSourceNonXA"); Connection con = null; PreparedStatement statement = null; ResultSet rs = null; try { con = ds.getConnection(); .prepareStatement("select count(*) as rowcount from edec_deklarationen_mgt where dko_spediteur_dekl_nr like ? and dko_spediteur_nr = ? "); statement.setString(1, spediDeklNr); statement.setString(2, spediNr); rs = statement.executeQuery(); rs.next(); countBefore = rs.getInt("rowcount"); rs.close(); .prepareStatement("DELETE FROM edec_deklarationen_mgt where dko_spediteur_dekl_nr like ? and dko_spediteur_nr = ?"); .prepareStatement("DELETE FROM edec_deklarationen WHERE dek_id IN (SELECT d.dek_id FROM edec_deklarationen d " e.printStackTrace(); e.printStackTrace(); e.printStackTrace();
public static void listTableData(String tableName) throws NamingException, SQLException { Context ctx = cache.getJNDIContext(); DataSource ds = (DataSource) ctx.lookup("java:/SimpleDataSource"); String sql = "select * from " + tableName; Connection conn = ds.getConnection(); Statement sm = conn.createStatement(); ResultSet rs = sm.executeQuery(sql); while (rs.next()) { System.out.println("id " + rs.getString(1) + " name " + rs.getString(2)); } rs.close(); conn.close(); }
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; }
private boolean insertJobExecutionEvent(final JobExecutionEvent jobExecutionEvent) { boolean result = false; String sql = "INSERT INTO `" + TABLE_JOB_EXECUTION_LOG + "` (`id`, `job_name`, `task_id`, `hostname`, `ip`, `sharding_item`, `execution_source`, `is_success`, `start_time`) " + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?);"; try ( Connection conn = dataSource.getConnection(); PreparedStatement preparedStatement = conn.prepareStatement(sql)) { preparedStatement.setString(1, jobExecutionEvent.getId()); preparedStatement.setString(2, jobExecutionEvent.getJobName()); preparedStatement.setString(3, jobExecutionEvent.getTaskId()); preparedStatement.setString(4, jobExecutionEvent.getHostname()); preparedStatement.setString(5, jobExecutionEvent.getIp()); preparedStatement.setInt(6, jobExecutionEvent.getShardingItem()); preparedStatement.setString(7, jobExecutionEvent.getSource().toString()); preparedStatement.setBoolean(8, jobExecutionEvent.isSuccess()); preparedStatement.setTimestamp(9, new Timestamp(jobExecutionEvent.getStartTime().getTime())); preparedStatement.execute(); result = true; } catch (final SQLException ex) { if (!isDuplicateRecord(ex)) { // TODO 记录失败直接输出日志,未来可考虑配置化 log.error(ex.getMessage()); } } return result; }
protected String getConstValueFromDb(String domain, String app, String type, Long hash) { String sql = "select value from druid_const where domain = ? AND app = ? and type = ? and hash = ?"; Connection conn = null; PreparedStatement stmt = null; ResultSet rs = null; try { conn = dataSource.getConnection(); stmt = conn.prepareStatement(sql); stmt.setString(1, domain); stmt.setString(2, app); stmt.setString(3, type); stmt.setLong(4, hash); rs = stmt.executeQuery(); if (rs.next()) { return rs.getString(1); } } catch (SQLException ex) { LOG.error("save const error error", ex); } finally { JdbcUtils.close(rs); JdbcUtils.close(stmt); JdbcUtils.close(conn); } return null; }
ResultSet rs = null; 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);
try { if (this.useNewConnection) { con = getDataSource().getConnection(); if (con.getAutoCommit()) { mustRestoreAutoCommit = true; con.setAutoCommit(false); con = DataSourceUtils.getConnection(getDataSource()); stmt = con.createStatement(); if (!this.useNewConnection) { DataSourceUtils.applyTransactionTimeout(stmt, getDataSource()); stmt.executeUpdate("update " + getIncrementerName() + " set " + columnName + " = last_insert_id(" + columnName + " + " + getCacheSize() + ")"); ResultSet rs = stmt.executeQuery(VALUE_SQL); try { if (!rs.next()) { throw new DataAccessResourceFailureException("last_insert_id() failed after executing an update"); this.maxId = rs.getLong(1);
@Test public void testValid() throws Exception { String sql = "SELECT NAME AS NAME, PROPERTY AS PROPERTY, VALUE AS VALUE FROM T"; Connection connection = mock(Connection.class); DataSource dataSource = mock(DataSource.class); given(dataSource.getConnection()).willReturn(connection); ResultSet resultSet = mock(ResultSet.class); given(resultSet.next()).willReturn(true, true, false); given(resultSet.getString(1)).willReturn("one", "one"); given(resultSet.getString(2)).willReturn("(class)", "age"); given(resultSet.getString(3)).willReturn("org.springframework.tests.sample.beans.TestBean", "53"); Statement statement = mock(Statement.class); given(statement.executeQuery(sql)).willReturn(resultSet); given(connection.createStatement()).willReturn(statement); DefaultListableBeanFactory bf = new DefaultListableBeanFactory(); JdbcBeanDefinitionReader reader = new JdbcBeanDefinitionReader(bf); reader.setDataSource(dataSource); reader.loadBeanDefinitions(sql); assertEquals("Incorrect number of bean definitions", 1, bf.getBeanDefinitionCount()); TestBean tb = (TestBean) bf.getBean("one"); assertEquals("Age in TestBean was wrong.", 53, tb.getAge()); verify(resultSet).close(); verify(statement).close(); } }
@Test public void testLeaveConnectionOpenOnRequest() throws Exception { String sql = "SELECT ID, FORENAME FROM CUSTMR WHERE ID < 3"; given(this.resultSet.next()).willReturn(false); given(this.connection.isClosed()).willReturn(false); given(this.connection.createStatement()).willReturn(this.preparedStatement); // if close is called entire test will fail willThrow(new RuntimeException()).given(this.connection).close(); SingleConnectionDataSource scf = new SingleConnectionDataSource(this.dataSource.getConnection(), false); this.template = new JdbcTemplate(scf, false); RowCountCallbackHandler rcch = new RowCountCallbackHandler(); this.template.query(sql, rcch); verify(this.resultSet).close(); verify(this.preparedStatement).close(); }
private boolean updateJobExecutionEventWhenSuccess(final JobExecutionEvent jobExecutionEvent) { boolean result = false; String sql = "UPDATE `" + TABLE_JOB_EXECUTION_LOG + "` SET `is_success` = ?, `complete_time` = ? WHERE id = ?"; try ( Connection conn = dataSource.getConnection(); PreparedStatement preparedStatement = conn.prepareStatement(sql)) { preparedStatement.setBoolean(1, jobExecutionEvent.isSuccess()); preparedStatement.setTimestamp(2, new Timestamp(jobExecutionEvent.getCompleteTime().getTime())); preparedStatement.setString(3, jobExecutionEvent.getId()); if (0 == preparedStatement.executeUpdate()) { return insertJobExecutionEventWhenSuccess(jobExecutionEvent); } result = true; } catch (final SQLException ex) { // TODO 记录失败直接输出日志,未来可考虑配置化 log.error(ex.getMessage()); } return result; }
public static <T> void executeQuery(DataSource dataSource , ResultSetConsumer<T> consumer , String sql , Object... parameters) throws SQLException { Connection conn = null; PreparedStatement stmt = null; ResultSet rs = null; try { conn = dataSource.getConnection(); stmt = conn.prepareStatement(sql); for (int i = 0; i < parameters.length; ++i) { stmt.setObject(i + 1, parameters[i]); } rs = stmt.executeQuery(); while (rs.next()) { if (consumer != null) { T object = consumer.apply(rs); consumer.accept(object); } } } finally { close(rs); close(stmt); close(conn); } }
@Before public void setUp() throws Exception { this.connection = mock(Connection.class); this.dataSource = mock(DataSource.class); this.statement = mock(Statement.class); this.preparedStatement = mock(PreparedStatement.class); this.resultSet = mock(ResultSet.class); this.resultSetMetaData = mock(ResultSetMetaData.class); this.template = new JdbcTemplate(this.dataSource); given(this.dataSource.getConnection()).willReturn(this.connection); given(this.resultSet.getMetaData()).willReturn(this.resultSetMetaData); given(this.resultSetMetaData.getColumnCount()).willReturn(1); given(this.resultSetMetaData.getColumnLabel(1)).willReturn("age"); given(this.connection.createStatement()).willReturn(this.statement); given(this.connection.prepareStatement(anyString())).willReturn(this.preparedStatement); given(this.preparedStatement.executeQuery()).willReturn(this.resultSet); given(this.statement.executeQuery(anyString())).willReturn(this.resultSet); }
try InitialContext ctx = new InitialContext(); this.src = (DataSource)ctx.lookup("jndi/MYSQL"); //The string should be the same name you're giving to your JNDI in Glassfish. this.con = src.getConnection(); this.con.close();
private int getEventCount(final String tableName, final Collection<String> tableFields, final Condition condition) { int result = 0; try ( Connection conn = dataSource.getConnection(); PreparedStatement preparedStatement = createCountPreparedStatement(conn, tableName, tableFields, condition); ResultSet resultSet = preparedStatement.executeQuery() ) { resultSet.next(); result = resultSet.getInt(1); } catch (final SQLException ex) { // TODO 记录失败直接输出日志,未来可考虑配置化 log.error("Fetch EventCount from DB error:", ex); } return result; }
@Override public void shutdown(DataSource dataSource, String databaseName) { Connection con = null; try { con = dataSource.getConnection(); if (con != null) { con.createStatement().execute("SHUTDOWN"); } } catch (SQLException ex) { logger.info("Could not shut down embedded database", ex); } finally { if (con != null) { try { con.close(); } catch (Throwable ex) { logger.debug("Could not close JDBC Connection on shutdown", ex); } } } }
private int executeUpdate(final String sql, final Object... params) { Connection connection = null; PreparedStatement ps = null; try { connection = dataSource.getConnection(); ps = connection.prepareStatement(sql); if (params != null) { for (int i = 0; i < params.length; i++) { ps.setObject(i + 1, convertDataTypeToDB(params[i])); } } return ps.executeUpdate(); } catch (SQLException e) { LOGGER.error("executeUpdate-> " + e.getMessage()); return FAIL_ROWS; } finally { close(connection, ps, null); } }