How to use

Connection

in

java.sql

public void runQuery(String url, String sql) {
  try (Connection connection = DriverManager.getConnection(url);
     PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
    // ... add parameters to the SQL query using PreparedStatement methods:
    //     setInt, setString, etc.
    try (ResultSet resultSet = preparedStatement.executeQuery()) {
      while (resultSet.next()) {
        // ... do something with result set
      }
    }
  } catch (SQLException e) {
    // ... handle SQL exception
  }
}

public static void main(String[] args) throws Exception {
  Class.forName("org.sqlite.JDBC");
  Connection conn = DriverManager.getConnection("jdbc:sqlite:test.db");
  Statement stat = conn.createStatement();
  stat.executeUpdate("drop table if exists people;");
  stat.executeUpdate("create table people (name, occupation);");
  PreparedStatement prep = conn.prepareStatement(
    "insert into people values (?, ?);");
  prep.setString(1, "Gandhi");
  prep.setString(2, "politics");
  prep.addBatch();
  prep.setString(1, "Turing");
  prep.setString(2, "computers");
  prep.addBatch();
  conn.setAutoCommit(false);
  prep.executeBatch();
  conn.setAutoCommit(true);
  ResultSet rs = stat.executeQuery("select * from people;");
  while (rs.next()) {
    System.out.println("name = " + rs.getString("name"));
    System.out.println("job = " + rs.getString("occupation"));
  rs.close();
  conn.close();

/**
 * Return whether JDBC 3.0 Savepoints are supported.
 * Caches the flag for the lifetime of this ConnectionHolder.
 * @throws SQLException if thrown by the JDBC driver
 */
public boolean supportsSavepoints() throws SQLException {
  if (this.savepointsSupported == null) {
    this.savepointsSupported = getConnection().getMetaData().supportsSavepoints();
  }
  return this.savepointsSupported;
}

public void commit() throws SQLException {
  getConn().commit();
  if (logger.isTraceEnabled()) {
    logger.trace("Batch executor commit " + idx.get() + " rows");
  }
  idx.set(0);
}

@Override
public PreparedStatement getPreparedStatement(Connection connection, String sql)
    throws SQLException
{
  connection.setAutoCommit(false);
  PreparedStatement statement = connection.prepareStatement(sql);
  statement.setFetchSize(1000);
  return statement;
}

/**
 * Prepare the given Connection before it is exposed.
 * <p>The default implementation applies the auto-commit flag, if necessary.
 * Can be overridden in subclasses.
 * @param con the Connection to prepare
 * @see #setAutoCommit
 */
protected void prepareConnection(Connection con) throws SQLException {
  Boolean autoCommit = getAutoCommitValue();
  if (autoCommit != null && con.getAutoCommit() != autoCommit) {
    con.setAutoCommit(autoCommit);
  }
}

public boolean isValidConnection(final Connection c, String validateQuery, int validationQueryTimeout) throws Exception {
  if (c.isClosed()) {
    return false;
  }
  Statement stmt = null;
  try {
    stmt = c.createStatement();
    if (validationQueryTimeout > 0) {
      stmt.setQueryTimeout(validationQueryTimeout);
    }
    stmt.execute(validateQuery);
    return true;
  } catch (SQLException e) {
    throw e;
  } finally {
    JdbcUtils.close(stmt);
  }
}

@Test
public void testHanaSequenceMaxValueIncrementer() throws SQLException {
  given(dataSource.getConnection()).willReturn(connection);
  given(connection.createStatement()).willReturn(statement);
  given(statement.executeQuery("select myseq.nextval from dummy")).willReturn(resultSet);
  given(resultSet.next()).willReturn(true);
  given(resultSet.getLong(1)).willReturn(10L, 12L);
  HanaSequenceMaxValueIncrementer incrementer = new HanaSequenceMaxValueIncrementer();
  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();
}

@Test
public void testCloseConnectionOnRequest() throws Exception {
  String sql = "SELECT ID, FORENAME FROM CUSTMR WHERE ID < 3";
  given(this.resultSet.next()).willReturn(false);
  given(this.connection.createStatement()).willReturn(this.preparedStatement);
  RowCountCallbackHandler rcch = new RowCountCallbackHandler();
  this.template.query(sql, rcch);
  verify(this.resultSet).close();
  verify(this.preparedStatement).close();
  verify(this.connection).close();
}

  private static void assertConnectionSource(Connection connection, String expectedSource)
      throws SQLException
  {
    String queryId;
    try (Statement statement = connection.createStatement();
        ResultSet rs = statement.executeQuery("SELECT 123")) {
      queryId = rs.unwrap(PrestoResultSet.class).getQueryId();
    }

    try (PreparedStatement statement = connection.prepareStatement(
        "SELECT source FROM system.runtime.queries WHERE query_id = ?")) {
      statement.setString(1, queryId);
      try (ResultSet rs = statement.executeQuery()) {
        assertTrue(rs.next());
        assertThat(rs.getString("source")).isEqualTo(expectedSource);
        assertFalse(rs.next());
      }
    }
  }
}

@Before
public void setUp() throws SQLException {
  given(connection.createStatement()).willReturn(statement);
  given(connection.prepareStatement(anyString())).willReturn(preparedStatement);
  given(statement.executeQuery(anyString())).willReturn(resultSet);
  given(preparedStatement.executeQuery()).willReturn(resultSet);
  given(resultSet.next()).willReturn(true, true, false);
  given(resultSet.getString(1)).willReturn("tb1", "tb2");
  given(resultSet.getInt(2)).willReturn(1, 2);
  template.setDataSource(new SingleConnectionDataSource(connection, false));
  template.setExceptionTranslator(new SQLStateSQLExceptionTranslator());
  template.afterPropertiesSet();
}

 // assumes...
//     import java.sql.*;
Connection conn=DriverManager.getConnection(
    "jdbc:ucanaccess://C:/__tmp/test/zzz.accdb");
Statement s = conn.createStatement();
ResultSet rs = s.executeQuery("SELECT [LastName] FROM [Clients]");
while (rs.next()) {
  System.out.println(rs.getString(1));
}

/**
 * @throws Exception If failed.
 */
@Test
public void testPortRangeConnect() throws Exception {
  try (Connection conn = DriverManager.getConnection(URL_PORT_RANGE)) {
    try (Statement stmt = conn.createStatement()) {
      stmt.execute("SELECT 1");
      ResultSet rs = stmt.getResultSet();
      assertTrue(rs.next());
      assertEquals(1, rs.getInt(1));
    }
  }
}

public static void selectAllEmployments(ResultSet[] resultSets) throws SQLException {
  Connection conn = DriverManager.getConnection( "jdbc:default:connection" );
  PreparedStatement statement = conn.prepareStatement(
      "select EMPLOYEE, EMPLOYER, STARTDATE, ENDDATE," +
          " REGIONCODE, EMPID, 'VALUE', CURRENCY" +
          " FROM EMPLOYMENT"
  );
  resultSets[0] = statement.executeQuery();
  conn.close();
}

@Test
public void testSqlUpdateWithThreadConnection() throws Exception {
  final String sql = "UPDATE NOSUCHTABLE SET DATE_DISPATCHED = SYSDATE WHERE ID = 4";
  int rowsAffected = 33;
  given(this.statement.executeUpdate(sql)).willReturn(rowsAffected);
  given(this.connection.createStatement()).willReturn(this.statement);
  int actualRowsAffected = this.template.update(sql);
  assertTrue("Actual rows affected is correct", actualRowsAffected == rowsAffected);
  verify(this.statement).close();
  verify(this.connection).close();
}

/**
 * {@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();
  }
 }
}

@ExpectWarning("ODR_OPEN_DATABASE_RESOURCE")
public void isReported(String url, String username, String password) throws Exception {
  Connection connection = DriverManager.getConnection(url, username, password);
  PreparedStatement pstmt = connection.prepareStatement("SELECT count(1) from tab");
  ResultSet rs = pstmt.executeQuery();
  while (rs.next()) {
    System.out.println(rs.getString(1));
  }
}

private void testAvaticaQuery(String url)
{
 LOG.info("URL: " + url);
 try {
  Properties connectionProperties = new Properties();
  connectionProperties.put("user", "admin");
  connectionProperties.put("password", "priest");
  Connection connection = DriverManager.getConnection(url, connectionProperties);
  Statement statement = connection.createStatement();
  statement.setMaxRows(450);
  String query = "SELECT * FROM INFORMATION_SCHEMA.COLUMNS";
  ResultSet resultSet = statement.executeQuery(query);
  Assert.assertTrue(resultSet.next());
  statement.close();
  connection.close();
 }
 catch (Exception e) {
  throw new RuntimeException(e);
 }
}

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

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