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 } }
@Override public void execute(String sql) { try (Connection connection = DriverManager.getConnection(jdbcUrl, jdbcProperties); Statement statement = connection.createStatement()) { statement.execute(sql); } catch (SQLException e) { throw new RuntimeException("Error executing sql:\n" + sql, e); } } }
// 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)); }
Class.forName("com.mysql.jdbc.Driver") ; Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/DBNAME", "usrname", "pswd") ; Statement stmt = conn.createStatement() ; String query = "select columnname from tablename ;" ; ResultSet rs = stmt.executeQuery(query) ;
@Test public void testStringToArrayFunctionInWhere2() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); ResultSet rs; rs = conn.createStatement().executeQuery("SELECT region_name FROM " + tableName + " WHERE 'a'=ANY(STRING_TO_ARRAY(string1, delimiter1))"); assertTrue(rs.next()); assertEquals("SF Bay Area", rs.getString(1)); assertFalse(rs.next()); }
@Test public void testRunScript() throws Exception { File tempDir = TestFileUtil.createTempDirectoryInSystemTemp(); File tempDatabaseFile = new File(tempDir, "db"); InputStream inputStream = SqlRunnerTest.class.getResourceAsStream("/org/syncany/database/sql/script.create.all.sql"); String connectionString = "jdbc:hsqldb:file:" + tempDatabaseFile.getAbsolutePath() + ";user=sa;password=;create=true;write_delay=false;hsqldb.write_delay=false;shutdown=true"; Connection connection = DriverManager.getConnection(connectionString); SqlRunner.runScript(connection, inputStream); connection.createStatement().execute("INSERT INTO DATABASEVERSION VALUES(1337,'MASTER',TIMESTAMP_WITH_ZONE(1388589969),'A','(A1)')"); connection.commit(); // Test a few selects assertEquals("1337", TestSqlUtil.runSqlSelect("select id from databaseversion", connection)); assertEquals( "DATABASEVERSION\nCHUNK\nDATABASEVERSION_VECTORCLOCK\nFILECONTENT\nFILECONTENT_CHUNK\nFILEHISTORY\nFILEVERSION\nMULTICHUNK\nMULTICHUNK_CHUNK\nMULTICHUNK_MUDDY\nKNOWN_DATABASES\nGENERAL_SETTINGS\nDATABASEVERSION_MASTER\nFILEVERSION_MASTER\nFILEVERSION_MASTER_MAXVERSION\nFILEVERSION_MASTER_LAST\nFILEHISTORY_FULL\nFILEVERSION_FULL", TestSqlUtil.runSqlSelect("select table_name from information_schema.tables where table_schema='PUBLIC'", connection)); // Test the function (--> different delimiter!) assertEquals("3", TestSqlUtil.runSqlSelect("select distinct substr_count('/a/b/c', '/') from information_schema.system_tables", connection)); connection.createStatement().execute("shutdown"); TestFileUtil.deleteDirectory(tempDir); } }
/** * @throws Exception If failed. */ @Test public void testPrimaryKeyMetadata() throws Exception { try (Connection conn = DriverManager.getConnection(URL); ResultSet rs = conn.getMetaData().getPrimaryKeys(null, "pers", "PERSON")) { int cnt = 0; while (rs.next()) { assert "_KEY".equals(rs.getString("COLUMN_NAME")); cnt++; } assert cnt == 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)); } } }
@Test public void testJavaJdbcRDD() throws Exception { JavaRDD<Integer> rdd = JdbcRDD.create( sc, () -> DriverManager.getConnection("jdbc:derby:target/JavaJdbcRDDSuiteDb"), "SELECT DATA FROM FOO WHERE ? <= ID AND ID <= ?", 1, 100, 1, r -> r.getInt(1) ).cache(); Assert.assertEquals(100, rdd.count()); Assert.assertEquals(Integer.valueOf(10100), rdd.reduce((i1, i2) -> i1 + i2)); } }
/** * @throws Exception If failed. */ @Test public void testDefaults() throws Exception { String url = URL_PREFIX + HOST; assert DriverManager.getConnection(url) != null; assert DriverManager.getConnection(url + "/") != null; }
@Test public void testTooManyConnections() throws Exception { final Connection connection1 = DriverManager.getConnection(url); final Statement statement1 = connection1.createStatement(); final Connection connection2 = DriverManager.getConnection(url); final Statement statement2 = connection2.createStatement(); final Connection connection3 = DriverManager.getConnection(url); final Statement statement3 = connection3.createStatement(); expectedException.expect(AvaticaClientRuntimeException.class); expectedException.expectMessage("Too many connections, limit is[3]"); final Connection connection4 = DriverManager.getConnection(url); }
/** * @throws Exception If failed. */ @Test public void testEmptySchemasMetadata() throws Exception { try (Connection conn = DriverManager.getConnection(URL)) { ResultSet rs = conn.getMetaData().getSchemas(null, "qqq"); assert !rs.next() : "Empty result set is expected"; } }
/** * Checks if the HMS backend db row number is as expected. If they are not, an * {@link AssertionError} is thrown. * @param tableName The table in which we count the rows * @param size The expected row number * @throws SQLException If there is a problem connecting to / querying the backend DB */ private void checkBackendTableSize(String tableName, int size) throws SQLException { String connectionStr = MetastoreConf.getVar(conf, MetastoreConf.ConfVars.CONNECT_URL_KEY); Connection conn = DriverManager.getConnection(connectionStr); Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery("SELECT COUNT(1) FROM " + tableName); rs.next(); Assert.assertEquals(tableName + " table should contain " + size + " rows", size, rs.getLong(1)); }
@Test public void testNotTooManyConnectionsWhenTheyAreEmpty() throws Exception { final Connection connection1 = DriverManager.getConnection(url); connection1.createStatement().close(); final Connection connection2 = DriverManager.getConnection(url); connection2.createStatement().close(); final Connection connection3 = DriverManager.getConnection(url); connection3.createStatement().close(); final Connection connection4 = DriverManager.getConnection(url); Assert.assertTrue(true); }
/** Load driver and make a connection. */ @Test public void testConnect() throws Exception { Class.forName("org.apache.drill.jdbc.Driver"); final Connection connection = DriverManager.getConnection("jdbc:drill:zk=local"); connection.close(); }
/** * @throws Exception If failed. */ @Test public void testCreateStatement() throws Exception { try (Connection conn = DriverManager.getConnection(URL)) { try (Statement stmt = conn.createStatement()) { assertNotNull(stmt); stmt.close(); conn.close(); // Exception when called on closed connection checkConnectionClosed(new RunnableX() { @Override public void run() throws Exception { conn.createStatement(); } }); } } }
import java.sql.Connection; import java.sql.DatabaseMetaData; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; public class JtdsSqlExpressInstanceConnect { public static void main(String[] args) throws SQLException { Connection conn = null; ResultSet rs = null; String url = "jdbc:jtds:sqlserver://127.0.0.1;instance=SQLEXPRESS;DatabaseName=master"; String driver = "net.sourceforge.jtds.jdbc.Driver"; String userName = "user"; String password = "password"; try { Class.forName(driver); conn = DriverManager.getConnection(url, userName, password); System.out.println("Connected to the database!!! Getting table list..."); DatabaseMetaData dbm = conn.getMetaData(); rs = dbm.getTables(null, null, "%", new String[] { "TABLE" }); while (rs.next()) { System.out.println(rs.getString("TABLE_NAME")); } } catch (Exception e) { e.printStackTrace(); } finally { conn.close(); rs.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 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(); } }
@Test public void testArrayFillFunctionInWhere2() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); ResultSet rs; rs = conn.createStatement().executeQuery( "SELECT region_name FROM " + tableName + " WHERE \"varchar\"=ANY(ARRAY_FILL('foo',3))"); assertTrue(rs.next()); assertEquals("SF Bay Area", rs.getString(1)); assertFalse(rs.next()); }