canonical example by Tabnine
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();
DriverManager.registerDriver(new oracle.jdbc.OracleDriver()); final Connection c = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE", "system", "manager"); String plsql = "" + " declare " + " end;"; CallableStatement cs = c.prepareCall(plsql); cs.setString(1, "12345"); cs.registerOutParameter(2, Types.VARCHAR); cs.registerOutParameter(3, OracleTypes.CURSOR); cs.execute(); while (cursorResultSet.next ()) System.out.println (cursorResultSet.getInt(1) + " " + cursorResultSet.getString(2)); cs.close(); c.close();
/** * Override this to link with your connection pooling system. * * By default this creates a single connection which is held open * until the object is garbage collected. */ protected Connection getConnection() throws SQLException { if (!DriverManager.getDrivers().hasMoreElements()) setDriver("sun.jdbc.odbc.JdbcOdbcDriver"); if (connection == null) { connection = DriverManager.getConnection(databaseURL, databaseUser, databasePassword); } return connection; }
// This manually deregisters JDBC driver, which prevents Tomcat 7 from complaining about memory leaks wrto this class Enumeration<Driver> drivers = DriverManager.getDrivers(); while (drivers.hasMoreElements()) { Driver driver = drivers.nextElement(); try { DriverManager.deregisterDriver(driver); LOG.log(Level.INFO, String.format("deregistering jdbc driver: %s", driver)); } catch (SQLException e) { LOG.log(Level.SEVERE, String.format("Error deregistering driver %s", driver), e); } }
private static void createDatabase(File dbHome, String user, String password) throws SQLException { String url = format("jdbc:h2:%s/sonar;USER=%s;PASSWORD=%s", dbHome.getAbsolutePath(), user, password); DriverManager.registerDriver(new Driver()); DriverManager.getConnection(url).close(); } }
/** * @throws Exception If failed. */ @Test public void testCache1() throws Exception { Properties cfg = new Properties(); cfg.setProperty(PROP_NODE_ID, grid(0).localNode().id().toString()); Connection conn = null; try { conn = DriverManager.getConnection(URL, cfg); ResultSet rs = conn.createStatement().executeQuery("select _val from Integer order by _val"); int cnt = 0; while (rs.next()) assertEquals(++cnt, rs.getInt(1)); assertEquals(2, cnt); } finally { if (conn != null) conn.close(); } }
public DriverDataSource(String jdbcUrl, String driverClassName, Properties properties, String username, String password) this.driverProperties = new Properties(); for (Entry<Object, Object> entry : properties.entrySet()) { driverProperties.setProperty(entry.getKey().toString(), entry.getValue().toString()); Enumeration<Driver> drivers = DriverManager.getDrivers(); while (drivers.hasMoreElements()) { Driver d = drivers.nextElement(); try { if (driver == null) { driver = DriverManager.getDriver(jdbcUrl); LOGGER.debug("Loaded driver with class name {} for jdbcUrl={}", driver.getClass().getName(), sanitizedUrl);
@Test public void testUpsertOnSCNSetMutTableWithoutIdx() throws Exception { helpTestUpserWithSCNIT(false, false, true, false, false); prep.executeUpdate(); props = new Properties(); Connection conn = DriverManager.getConnection(getUrl(),props); ResultSet rs = conn.createStatement().executeQuery("SELECT * FROM "+tableName); assertTrue(rs.next()); assertEquals("abc", rs.getString(1)); assertEquals("This is the first comment!", rs.getString(2)); assertFalse(rs.next()); }
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); } }
/** * Verifies that <code>select count(*)</code> behaves correctly in * {@link org.apache.ignite.cache.CacheMode#LOCAL} mode. * * @throws Exception If failed. */ @Test public void testCountAll() throws Exception { Properties cfg = new Properties(); cfg.setProperty(PROP_NODE_ID, grid(0).localNode().id().toString()); Connection conn = null; try { conn = DriverManager.getConnection(BASE_URL, cfg); ResultSet rs = conn.createStatement().executeQuery("select count(*) from Integer"); assertTrue(rs.next()); assertEquals(2L, rs.getLong(1)); } finally { if (conn != null) conn.close(); } }
/** * @param allowOverwrite Allow overwriting of existing keys. * @return Connection to use for the test. * @throws Exception if failed. */ protected Connection createConnection(boolean allowOverwrite) throws Exception { Properties props = new Properties(); props.setProperty(IgniteJdbcDriver.PROP_STREAMING, "true"); props.setProperty(IgniteJdbcDriver.PROP_STREAMING_FLUSH_FREQ, "500"); if (allowOverwrite) props.setProperty(IgniteJdbcDriver.PROP_STREAMING_ALLOW_OVERWRITE, "true"); return DriverManager.getConnection(BASE_URL, props); }
private void testAvaticaAuthFailure(String url) throws Exception { LOG.info("URL: " + url); try { Properties connectionProperties = new Properties(); connectionProperties.put("user", "admin"); connectionProperties.put("password", "wrongpassword"); Connection connection = DriverManager.getConnection(url, connectionProperties); Statement statement = connection.createStatement(); statement.setMaxRows(450); String query = "SELECT * FROM INFORMATION_SCHEMA.COLUMNS"; statement.executeQuery(query); } catch (AvaticaSqlException ase) { Assert.assertEquals( ase.getErrorMessage(), "Error while executing SQL \"SELECT * FROM INFORMATION_SCHEMA.COLUMNS\": Remote driver error: ForbiddenException: Authentication failed." ); return; } Assert.fail("Test failed, did not get AvaticaSqlException."); }
@BeforeClass public static void setUpConnection() throws SQLException { Driver.load(); Properties properties = new Properties(); // Increased prepared statement creation timeout so test doesn't timeout on my laptop properties.setProperty(ExecConstants.bootDefaultFor(ExecConstants.CREATE_PREPARE_STATEMENT_TIMEOUT_MILLIS), "30000"); connection = DriverManager.getConnection( "jdbc:drill:zk=local", properties); try(Statement stmt = connection.createStatement()) { stmt.execute(String.format("alter session set `%s` = true", PlannerSettings.ENABLE_DECIMAL_DATA_TYPE_KEY)); } }
@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(); } }
private Connection createConnection(Map<String, String> additionalProperties) throws SQLException { String url = format("jdbc:presto://localhost:%s", server.getHttpsAddress().getPort()); Properties properties = new Properties(); properties.setProperty("user", "test"); properties.setProperty("SSL", "true"); properties.setProperty("SSLTrustStorePath", getResource("localhost.truststore").getPath()); properties.setProperty("SSLTrustStorePassword", "changeit"); properties.putAll(additionalProperties); return DriverManager.getConnection(url, properties); } }
@Override public boolean open() { if (this.loadDriver()) { Properties props = new Properties(); props.put("user", jdbcTransactionStoreConfig.getJdbcUser()); props.put("password", jdbcTransactionStoreConfig.getJdbcPassword()); try { this.connection = DriverManager.getConnection(this.jdbcTransactionStoreConfig.getJdbcURL(), props); this.connection.setAutoCommit(false); if (!this.computeTotalRecords()) { return this.createDB(); } return true; } catch (SQLException e) { log.info("Create JDBC Connection Exception", e); } } return false; }
/** * @param allowOverwrite Allow overwriting of existing keys. * @param flushTimeout Stream flush timeout. * @return Connection to use for the test. * @throws Exception if failed. */ protected Connection createStreamedConnection(boolean allowOverwrite, long flushTimeout) throws Exception { Properties props = new Properties(); props.setProperty(IgniteJdbcDriver.PROP_STREAMING, "true"); props.setProperty(IgniteJdbcDriver.PROP_STREAMING_FLUSH_FREQ, String.valueOf(flushTimeout)); if (allowOverwrite) props.setProperty(IgniteJdbcDriver.PROP_STREAMING_ALLOW_OVERWRITE, "true"); Connection res = DriverManager.getConnection(STREAMING_URL, props); res.setSchema(QueryUtils.DFLT_SCHEMA); return res; }
@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)); } }
public static void putFolderToDb(final File pDestinationFile, final File pFolderToPut) throws ClassNotFoundException, SQLException, FileNotFoundException, IOException { pDestinationFile.delete(); Class.forName("org.sqlite.JDBC"); final Connection conn = DriverManager.getConnection("jdbc:sqlite:" + pDestinationFile); final Statement stat = conn.createStatement(); stat.execute("CREATE TABLE tiles (key INTEGER PRIMARY KEY, provider TEXT, tile BLOB)"); stat.close(); final PreparedStatement prep = conn.prepareStatement("insert into tiles values (?, ?, ?);"); File[] listFiles = pFolderToPut.listFiles(); if (listFiles != null) { final long y = Long.parseLong(s[s.length - 1].split(".png")[0]); final long index = (((z << z) + x) << z) + y; prep.setLong(1, index); final String provider = s[s.length - 4]; prep.setString(2, provider); final byte[] image = new byte[(int) listFiles2[yf].length()]; final FileInputStream str = new FileInputStream(listFiles2[yf]); str.read(image); str.close(); prep.setBytes(3, image); prep.executeUpdate(); conn.setAutoCommit(false); prep.executeBatch(); conn.setAutoCommit(true);