Examples of java.sql.Connection

java.sql.Connection

A Connection represents a session with a specific database. Within the context of a Connection, SQL statements are executed and results are returned.
A Connection's database is able to provide information describing its tables, its supported SQL grammar, its stored procedures, the capabilities of this connection, etc. This information is obtained with the getMetaData method.
Note: By default the Connection automatically commits changes after executing each statement. If auto commit has been disabled, an explicit commit must be done or database changes will not be saved. @see DriverManager#getConnection @see Statement @see ResultSet @see DatabaseMetaData

    public static void testDefaultConn() throws SQLException {
        DriverManager.getConnection("jdbc:default:connection");
    }


    private void testFunctionInSchema() throws SQLException {
        Connection conn = getConnection("functions");
        Statement stat = conn.createStatement();


        stat.execute("create schema schema2");
        stat.execute("create alias schema2.func as 'int x() { return 1; }'");
        stat.execute("create view test as select schema2.func()");
        ResultSet rs;
        rs = stat.executeQuery("select * from information_schema.views");
        rs.next();
        assertTrue(rs.getString("VIEW_DEFINITION").indexOf("SCHEMA2.FUNC") >= 0);


        stat.execute("drop view test");
        stat.execute("drop schema schema2");


        conn.close();
    }

View Full Code Here


        conn.close();
    }


    private void testGreatest() throws SQLException {
        Connection conn = getConnection("functions");
        Statement stat = conn.createStatement();


        String createSQL = "CREATE TABLE testGreatest (id BIGINT);";
        stat.execute(createSQL);
        stat.execute("insert into testGreatest values (1)");


        String query = "SELECT GREATEST(id, " + ((long) Integer.MAX_VALUE) + ") FROM testGreatest";
        ResultSet rs = stat.executeQuery(query);
        rs.next();
        Object o = rs.getObject(1);
        assertEquals(Long.class.getName(), o.getClass().getName());


        String query2 = "SELECT GREATEST(id, " + ((long) Integer.MAX_VALUE + 1) + ") FROM testGreatest";
        ResultSet rs2 = stat.executeQuery(query2);
        rs2.next();
        Object o2 = rs2.getObject(1);
        assertEquals(Long.class.getName(), o2.getClass().getName());


        conn.close();
    }

View Full Code Here

        testThreeThreads();
        deleteDb("multiConn");
    }


    private void testConcurrentShutdownQuery() throws Exception {
        Connection conn1 = getConnection("multiConn");
        Connection conn2 = getConnection("multiConn");
        final Statement stat1 = conn1.createStatement();
        stat1.execute("CREATE ALIAS SLEEP FOR \"java.lang.Thread.sleep(long)\"");
        final Statement stat2 = conn2.createStatement();
        stat1.execute("SET THROTTLE 100");
        Task t = new Task() {
            public void call() throws Exception {
                stat2.executeQuery("CALL SLEEP(100)");
                Thread.sleep(10);
                stat2.executeQuery("CALL SLEEP(100)");
            }
        };
        t.execute();
        Thread.sleep(50);
        stat1.execute("SHUTDOWN");
        conn1.close();
        try {
            conn2.close();
        } catch (SQLException e) {
            // ignore
        }
        try {
            t.get();

View Full Code Here

        }
    }


    private void testThreeThreads() throws Exception {
        deleteDb("multiConn");
        final Connection conn1 = getConnection("multiConn");
        final Connection conn2 = getConnection("multiConn");
        final Connection conn3 = getConnection("multiConn");
        conn1.setAutoCommit(false);
        conn2.setAutoCommit(false);
        conn3.setAutoCommit(false);
        final Statement s1 = conn1.createStatement();
        final Statement s2 = conn2.createStatement();
        final Statement s3 = conn3.createStatement();
        s1.execute("CREATE TABLE TEST1(ID INT)");
        s2.execute("CREATE TABLE TEST2(ID INT)");
        s3.execute("CREATE TABLE TEST3(ID INT)");
        s1.execute("INSERT INTO TEST1 VALUES(1)");
        s2.execute("INSERT INTO TEST2 VALUES(2)");
        s3.execute("INSERT INTO TEST3 VALUES(3)");
        s1.execute("SET LOCK_TIMEOUT 1000");
        s2.execute("SET LOCK_TIMEOUT 1000");
        s3.execute("SET LOCK_TIMEOUT 1000");
        Thread t1 = new Thread(new Runnable() {
            public void run() {
                try {
                    s3.execute("INSERT INTO TEST2 VALUES(4)");
                    conn3.commit();
                } catch (SQLException e) {
                    TestBase.logError("insert", e);
                }
            }
        });
        t1.start();
        Thread.sleep(20);
        Thread t2 = new Thread(new Runnable() {
            public void run() {
                try {
                    s2.execute("INSERT INTO TEST1 VALUES(5)");
                    conn2.commit();
                } catch (SQLException e) {
                    TestBase.logError("insert", e);
                }
            }
        });
        t2.start();
        Thread.sleep(20);
        conn1.commit();
        t2.join(1000);
        t1.join(1000);
        ResultSet rs = s1.executeQuery("SELECT * FROM TEST1 ORDER BY ID");
        rs.next();
        assertEquals(1, rs.getInt(1));
        rs.next();
        assertEquals(5, rs.getInt(1));
        assertFalse(rs.next());
        conn1.close();
        conn2.close();
        conn3.close();
    }

View Full Code Here

    private void testConcurrentOpen() throws Exception {
        if (config.memory || config.googleAppEngine) {
            return;
        }
        deleteDb("multiConn");
        Connection conn = getConnection("multiConn");
        conn.createStatement().execute("CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR)");
        conn.createStatement().execute("INSERT INTO TEST VALUES(0, 'Hello'), (1, 'World')");
        conn.createStatement().execute("SHUTDOWN");
        conn.close();
        final String listener = getClass().getName();
        Runnable r = new Runnable() {
            public void run() {
                try {
                    Connection c1 = getConnection("multiConn;DATABASE_EVENT_LISTENER='" + listener
                            + "';file_lock=socket");
                    c1.close();
                } catch (Exception e) {
                    TestBase.logError("connect", e);
                }
            }
        };
        Thread thread = new Thread(r);
        thread.start();
        Thread.sleep(10);
        Connection c2 = getConnection("multiConn;file_lock=socket");
        c2.close();
        thread.join();
    }

View Full Code Here

        // do nothing
    }


    private void testCommitRollback() throws SQLException {
        deleteDb("multiConn");
        Connection c1 = getConnection("multiConn");
        Connection c2 = getConnection("multiConn");
        c1.setAutoCommit(false);
        c2.setAutoCommit(false);
        Statement s1 = c1.createStatement();
        s1.execute("DROP TABLE IF EXISTS MULTI_A");
        s1.execute("CREATE TABLE MULTI_A(ID INT, NAME VARCHAR(255))");
        s1.execute("INSERT INTO MULTI_A VALUES(0, '0-insert-A')");
        Statement s2 = c2.createStatement();
        s1.execute("DROP TABLE IF EXISTS MULTI_B");
        s1.execute("CREATE TABLE MULTI_B(ID INT, NAME VARCHAR(255))");
        s2.execute("INSERT INTO MULTI_B VALUES(0, '1-insert-B')");
        c1.commit();
        c2.rollback();
        s1.execute("INSERT INTO MULTI_A VALUES(1, '0-insert-C')");
        s2.execute("INSERT INTO MULTI_B VALUES(1, '1-insert-D')");
        c1.rollback();
        c2.commit();
        c1.close();
        c2.close();


        if (!config.memory) {
            Connection conn = getConnection("multiConn");
            ResultSet rs;
            rs = conn.createStatement().executeQuery("SELECT * FROM MULTI_A ORDER BY ID");
            rs.next();
            assertEquals("0-insert-A", rs.getString("NAME"));
            assertFalse(rs.next());
            rs = conn.createStatement().executeQuery("SELECT * FROM MULTI_B ORDER BY ID");
            rs.next();
            assertEquals("1-insert-D", rs.getString("NAME"));
            assertFalse(rs.next());
            conn.close();
        }


    }

View Full Code Here

        testLogMode(1);
        testLogMode(2);
    }


    private void testLogMode(int logMode) throws SQLException {
        Connection conn;
        Statement stat;
        ResultSet rs;
        conn = getConnection("transaction");
        stat = conn.createStatement();
        stat.execute("create table test(id int primary key) as select 1");
        stat.execute("set write_delay 0");
        stat.execute("set log " + logMode);
        rs = stat.executeQuery("select value from information_schema.settings where name = 'LOG'");
        rs.next();
        assertEquals(logMode, rs.getInt(1));
        stat.execute("insert into test values(2)");
        stat.execute("shutdown immediately");
        try {
            conn.close();
        } catch (SQLException e) {
            // expected
        }
        conn = getConnection("transaction");
        stat = conn.createStatement();
        rs = stat.executeQuery("select * from test order by id");
        assertTrue(rs.next());
        if (logMode != 0) {
            assertTrue(rs.next());
        }
        assertFalse(rs.next());
        stat.execute("drop table test");
        conn.close();
    }

View Full Code Here

        conn.close();
    }


    private void testConcurrentSelectForUpdate() throws SQLException {
        deleteDb("transaction");
        Connection conn = getConnection("transaction");
        conn.setAutoCommit(false);
        Statement stat = conn.createStatement();
        stat.execute("create table test(id int primary key, name varchar)");
        stat.execute("insert into test values(1, 'Hello'), (2, 'World')");
        conn.commit();
        PreparedStatement prep = conn.prepareStatement("select * from test for update");
        prep.execute();
        Connection conn2 = getConnection("transaction");
        conn2.setAutoCommit(false);
        assertThrows(ErrorCode.LOCK_TIMEOUT_1, conn2.createStatement()).
                execute("select * from test for update");
        conn2.close();
        conn.close();
    }

View Full Code Here

        conn.close();
    }


    private void testForUpdate() throws SQLException {
        deleteDb("transaction");
        Connection conn = getConnection("transaction");
        conn.setAutoCommit(false);
        Statement stat = conn.createStatement();
        stat.execute("create table test(id int primary key, name varchar)");
        stat.execute("insert into test values(1, 'Hello'), (2, 'World')");
        conn.commit();
        PreparedStatement prep = conn.prepareStatement("select * from test where id = 1 for update");
        prep.execute();
        // releases the lock
        conn.commit();
        prep.execute();
        Connection conn2 = getConnection("transaction");
        conn2.setAutoCommit(false);
        Statement stat2 = conn2.createStatement();
        if (config.mvcc && Constants.VERSION_MINOR >= 3) {
            stat2.execute("update test set name = 'Welt' where id = 2");
        }
        assertThrows(ErrorCode.LOCK_TIMEOUT_1, stat2).
                execute("update test set name = 'Hallo' where id = 1");
        conn2.close();
        conn.close();
    }

View Full Code Here

        conn.close();
    }


    private void testRollback() throws SQLException {
        deleteDb("transaction");
        Connection conn = getConnection("transaction");
        Statement stat = conn.createStatement();
        stat.execute("create table test(id int)");
        stat.execute("create index idx_id on test(id)");
        stat.execute("insert into test values(1), (1), (1)");
        if (!config.memory) {
            conn.close();
            conn = getConnection("transaction");
            stat = conn.createStatement();
        }
        conn.setAutoCommit(false);
        stat.execute("delete from test");
        conn.rollback();
        ResultSet rs;
        rs = stat.executeQuery("select * from test");
        assertResultRowCount(3, rs);
        rs = stat.executeQuery("select * from test where id = 1");
        assertResultRowCount(3, rs);
        conn.close();


        conn = getConnection("transaction");
        stat = conn.createStatement();
        stat.execute("create table master(id int) as select 1");
        stat.execute("create table child1(id int references master(id) on delete cascade)");
        stat.execute("insert into child1 values(1), (1), (1)");
        stat.execute("create table child2(id int references master(id)) as select 1");
        if (!config.memory) {
            conn.close();
            conn = getConnection("transaction");
        }
        stat = conn.createStatement();
        assertThrows(ErrorCode.REFERENTIAL_INTEGRITY_VIOLATED_CHILD_EXISTS_1, stat).
                execute("delete from master");
        conn.rollback();
        rs = stat.executeQuery("select * from master where id=1");
        assertResultRowCount(1, rs);
        rs = stat.executeQuery("select * from child1");
        assertResultRowCount(3, rs);
        rs = stat.executeQuery("select * from child1 where id=1");
        assertResultRowCount(3, rs);
        conn.close();
    }

View Full Code Here

0 1 2 3 4 5 6 7 8 9

TOP

Related Classes of java.sql.Connection

co.nubetech.hiho.hive.HiveUtility

co.nubetech.hiho.job.TestExportToDB

co.nubetech.hiho.job.TestExportToMySQLDB

com.acme.JNDITest

com.alibaba.wasp.jdbc.TestJdbcConnectionPool

com.alibaba.wasp.jdbcx.JdbcXAConnection

com.alimama.mdrill.adhoc.MysqlCallback

com.alimama.mdrill.adhoc.MysqlInfo

com.alimama.mdrill.ui.service.UserJson

com.alimama.web.TableJoin

All source code are property of their respective owners. Java is a trademark of Sun Microsystems, Inc and owned by ORACLE Inc. Contact coftware#gmail.com.