How to use

DataSource

in

javax.sql

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