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

        conn.close();
    }


    private void testSchemaSearchPath() throws SQLException {
        deleteDb("functions");
        Connection conn = getConnection("functions");
        Statement stat = conn.createStatement();
        ResultSet rs;
        stat.execute("CREATE SCHEMA TEST");
        stat.execute("SET SCHEMA TEST");
        stat.execute("CREATE ALIAS PARSE_INT2 FOR \"java.lang.Integer.parseInt(java.lang.String, int)\";");
        rs = stat.executeQuery("SELECT ALIAS_NAME FROM INFORMATION_SCHEMA.FUNCTION_ALIASES WHERE ALIAS_SCHEMA ='TEST'");
        rs.next();
        assertEquals("PARSE_INT2", rs.getString(1));
        stat.execute("DROP ALIAS PARSE_INT2");


        stat.execute("SET SCHEMA PUBLIC");
        stat.execute("CREATE ALIAS TEST.PARSE_INT2 FOR \"java.lang.Integer.parseInt(java.lang.String, int)\";");
        stat.execute("SET SCHEMA_SEARCH_PATH PUBLIC, TEST");


        rs = stat.executeQuery("CALL PARSE_INT2('-FF', 16)");
        rs.next();
        assertEquals(-255, rs.getInt(1));
        rs = stat.executeQuery("SELECT ALIAS_NAME FROM INFORMATION_SCHEMA.FUNCTION_ALIASES WHERE ALIAS_SCHEMA ='TEST'");
        rs.next();
        assertEquals("PARSE_INT2", rs.getString(1));
        rs = stat.executeQuery("CALL TEST.PARSE_INT2('-2147483648', 10)");
        rs.next();
        assertEquals(-2147483648, rs.getInt(1));
        rs = stat.executeQuery("CALL FUNCTIONS.TEST.PARSE_INT2('-2147483648', 10)");
        rs.next();
        assertEquals(-2147483648, rs.getInt(1));
        conn.close();
    }

View Full Code Here

        conn.close();
    }


    private void testArrayParameters() throws SQLException {
        deleteDb("functions");
        Connection conn = getConnection("functions");
        Statement stat = conn.createStatement();
        ResultSet rs;
        stat.execute("create alias array_test AS "
                + "$$ Integer[] array_test(Integer[] in_array) "
                + "{ return in_array; } $$;");


        PreparedStatement stmt = conn.prepareStatement("select array_test(?) from dual");
        stmt.setObject(1, new Integer[] { 1, 2 });
        rs = stmt.executeQuery();
        rs.next();
        assertEquals(Integer[].class.getName(), rs.getObject(1).getClass().getName());


        CallableStatement call = conn.prepareCall("{ ? = call array_test(?) }");
        call.setObject(2, new Integer[] { 2, 1 });
        call.registerOutParameter(1, Types.ARRAY);
        call.execute();
        assertEquals(Integer[].class.getName(), call.getArray(1).getArray().getClass().getName());
        assertEquals(new Integer[] { 2, 1 }, (Integer[]) call.getObject(1));


        stat.execute("drop alias array_test");


        conn.close();
    }

View Full Code Here

        }
        deleteDb("test");
    }


    private void testPgClient() throws SQLException {
        Connection conn = DriverManager.getConnection("jdbc:postgresql://localhost:5535/test", "sa", "sa");
        Statement stat = conn.createStatement();
        assertThrows(SQLException.class, stat).
                execute("select ***");
        stat.execute("create user test password 'test'");
        stat.execute("create table test(id int primary key, name varchar)");
        stat.execute("create index idx_test_name on test(name, id)");
        stat.execute("grant all on test to test");
        conn.close();


        conn = DriverManager.getConnection("jdbc:postgresql://localhost:5535/test", "test", "test");
        stat = conn.createStatement();
        ResultSet rs;


        stat.execute("prepare test(int, int) as select ?1*?2");
        rs = stat.executeQuery("execute test(3, 2)");
        rs.next();
        assertEquals(6, rs.getInt(1));
        stat.execute("deallocate test");


        PreparedStatement prep = conn.prepareStatement("insert into test values(?, ?)");
        ParameterMetaData meta = prep.getParameterMetaData();
        assertEquals(2, meta.getParameterCount());
        prep.setInt(1, 1);
        prep.setString(2, "Hello");
        prep.execute();
        rs = stat.executeQuery("select * from test");
        rs.next();


        ResultSetMetaData rsMeta = rs.getMetaData();
        assertEquals(Types.INTEGER, rsMeta.getColumnType(1));
        assertEquals(Types.VARCHAR, rsMeta.getColumnType(2));


        prep.close();
        assertEquals(1, rs.getInt(1));
        assertEquals("Hello", rs.getString(2));
        assertFalse(rs.next());
        prep = conn.prepareStatement("select * from test where id = ? and name = ?");
        prep.setInt(1, 1);
        prep.setString(2, "Hello");
        rs = prep.executeQuery();
        rs.next();
        assertEquals(1, rs.getInt(1));
        assertEquals("Hello", rs.getString(2));
        assertFalse(rs.next());
        rs.close();
        DatabaseMetaData dbMeta = conn.getMetaData();
        rs = dbMeta.getTables(null, null, "TEST", null);
        rs.next();
        assertEquals("TEST", rs.getString("TABLE_NAME"));
        assertFalse(rs.next());
        rs = dbMeta.getColumns(null, null, "TEST", null);
        rs.next();
        assertEquals("ID", rs.getString("COLUMN_NAME"));
        rs.next();
        assertEquals("NAME", rs.getString("COLUMN_NAME"));
        assertFalse(rs.next());
        rs = dbMeta.getIndexInfo(null, null, "TEST", false, false);
        // index info is currently disabled
        // rs.next();
        // assertEquals("TEST", rs.getString("TABLE_NAME"));
        // rs.next();
        // assertEquals("TEST", rs.getString("TABLE_NAME"));
        assertFalse(rs.next());
        rs = stat.executeQuery("select version(), pg_postmaster_start_time(), current_schema()");
        rs.next();
        String s = rs.getString(1);
        assertTrue(s.indexOf("H2") >= 0);
        assertTrue(s.indexOf("PostgreSQL") >= 0);
        s = rs.getString(2);
        s = rs.getString(3);
        assertEquals(s, "PUBLIC");
        assertFalse(rs.next());


        conn.setAutoCommit(false);
        stat.execute("delete from test");
        conn.rollback();
        stat.execute("update test set name = 'Hallo'");
        conn.commit();
        rs = stat.executeQuery("select * from test order by id");
        rs.next();
        assertEquals(1, rs.getInt(1));
        assertEquals("Hallo", rs.getString(2));
        assertFalse(rs.next());


        rs = stat.executeQuery("select id, name, pg_get_userbyid(id) from information_schema.users order by id");
        rs.next();
        assertEquals(rs.getString(2), rs.getString(3));
        assertFalse(rs.next());
        rs.close();


        rs = stat.executeQuery("select currTid2('x', 1)");
        rs.next();
        assertEquals(1, rs.getInt(1));


        rs = stat.executeQuery("select has_table_privilege('TEST', 'READ')");
        rs.next();
        assertTrue(rs.getBoolean(1));


        rs = stat.executeQuery("select has_database_privilege(1, 'READ')");
        rs.next();
        assertTrue(rs.getBoolean(1));




        rs = stat.executeQuery("select pg_get_userbyid(-1)");
        rs.next();
        assertEquals(null, rs.getString(1));


        rs = stat.executeQuery("select pg_encoding_to_char(0)");
        rs.next();
        assertEquals("SQL_ASCII", rs.getString(1));


        rs = stat.executeQuery("select pg_encoding_to_char(6)");
        rs.next();
        assertEquals("UTF8", rs.getString(1));


        rs = stat.executeQuery("select pg_encoding_to_char(8)");
        rs.next();
        assertEquals("LATIN1", rs.getString(1));


        rs = stat.executeQuery("select pg_encoding_to_char(20)");
        rs.next();
        assertEquals("UTF8", rs.getString(1));


        rs = stat.executeQuery("select pg_encoding_to_char(40)");
        rs.next();
        assertEquals("", rs.getString(1));


        rs = stat.executeQuery("select pg_get_oid('\"WRONG\"')");
        rs.next();
        assertEquals(0, rs.getInt(1));


        rs = stat.executeQuery("select pg_get_oid('TEST')");
        rs.next();
        assertTrue(rs.getInt(1) > 0);


        rs = stat.executeQuery("select pg_get_indexdef(0, 0, false)");
        rs.next();
        assertEquals("", rs.getString(1));


        rs = stat.executeQuery("select id from information_schema.indexes where index_name='IDX_TEST_NAME'");
        rs.next();
        int indexId = rs.getInt(1);


        rs = stat.executeQuery("select pg_get_indexdef("+indexId+", 0, false)");
        rs.next();
        assertEquals("CREATE INDEX PUBLIC.IDX_TEST_NAME ON PUBLIC.TEST(NAME, ID)", rs.getString(1));
        rs = stat.executeQuery("select pg_get_indexdef("+indexId+", null, false)");
        rs.next();
        assertEquals("CREATE INDEX PUBLIC.IDX_TEST_NAME ON PUBLIC.TEST(NAME, ID)", rs.getString(1));
        rs = stat.executeQuery("select pg_get_indexdef("+indexId+", 1, false)");
        rs.next();
        assertEquals("NAME", rs.getString(1));
        rs = stat.executeQuery("select pg_get_indexdef("+indexId+", 2, false)");
        rs.next();
        assertEquals("ID", rs.getString(1));


        conn.close();
    }

View Full Code Here

  }
    
  public Connection getConnection() throws SQLException{
        ConnectionImpl conn = getConnectionImpl();
    
    Connection result = (Connection)Proxy.newProxyInstance(this.getClass().getClassLoader(), new Class[] {Connection.class}, new CloseInterceptor(conn));
    
    return result;
  }

View Full Code Here

        conn.close();
        deleteDb("compatibility");
    }


    private void testCaseSensitiveIdentifiers() throws SQLException {
        Connection c = getConnection("compatibility;DATABASE_TO_UPPER=FALSE");
        Statement stat = c.createStatement();
        stat.execute("create table test(id int primary key, name varchar) as select 1, 'hello'");
        ResultSet rs;
        rs = stat.executeQuery("select * from test");
        assertEquals("id", rs.getMetaData().getColumnLabel(1));
        assertEquals("name", rs.getMetaData().getColumnLabel(2));
        assertThrows(ErrorCode.TABLE_OR_VIEW_NOT_FOUND_1, stat).
                execute("select * from TEST");
        stat.execute("select COUNT(*), count(*), Count(*), Sum(id) from test");
        stat.execute("select LENGTH(name), length(name), Length(name) from test");
        stat.execute("drop table test");
        c.close();
    }

View Full Code Here

    }


    public void test() throws SQLException {


        deleteDb("speed");
        Connection conn;


        conn = getConnection("speed");


        // conn =
        // getConnection("speed;ASSERT=0;MAX_MEMORY_ROWS=1000000;MAX_LOG_SIZE=1000");


        // Class.forName("org.hsqldb.jdbcDriver");
        // conn = DriverManager.getConnection("jdbc:hsqldb:speed");


        Statement stat = conn.createStatement();
        stat.execute("DROP TABLE IF EXISTS TEST");
        stat.execute("CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR(255))");
        int len = getSize(1, 10000);
        for (int i = 0; i < len; i++) {
            stat.execute("SELECT ID, NAME FROM TEST ORDER BY ID");
        }


        // drop table if exists test;
        // CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR(255));
        // @LOOP 100000 INSERT INTO TEST VALUES(?, 'Hello');
        // @LOOP 100000 SELECT * FROM TEST WHERE ID = ?;


        // stat.execute("CREATE TABLE TEST(ID INT PRIMARY KEY, NAME
        // VARCHAR(255))");
        // for(int i=0; i<1000; i++) {
        // stat.execute("INSERT INTO TEST VALUES("+i+", 'Hello')");
        // }
        // stat.execute("CREATE TABLE TEST_A(ID INT PRIMARY KEY, NAME
        // VARCHAR(255))");
        // stat.execute("INSERT INTO TEST_A VALUES(0, 'Hello')");
        long time = System.currentTimeMillis();
        // for(int i=1; i<8000; i*=2) {
        // stat.execute("INSERT INTO TEST_A SELECT ID+"+i+", NAME FROM TEST_A");
        //
        // // stat.execute("INSERT INTO TEST_A VALUES("+i+", 'Hello')");
        // }
        // for(int i=0; i<4; i++) {
        // ResultSet rs = stat.executeQuery("SELECT * FROM TEST_A");
        // while(rs.next()) {
        // rs.getInt(1);
        // rs.getString(2);
        // }
        // }
        // System.out.println(System.currentTimeMillis()-time);


        //
        // stat.execute("CREATE TABLE TEST_B(ID INT PRIMARY KEY, NAME
        // VARCHAR(255))");
        // for(int i=0; i<80000; i++) {
        // stat.execute("INSERT INTO TEST_B VALUES("+i+", 'Hello')");
        // }


        // conn.close();
        // System.exit(0);
        // int testParser;
        // java -Xrunhprof:cpu=samples,depth=8 -cp . org.h2.test.TestAll
        //
        // stat.execute("CREATE TABLE TEST(ID INT)");
        // stat.execute("INSERT INTO TEST VALUES(1)");
        // ResultSet rs = stat.executeQuery("SELECT ID OTHER_ID FROM TEST");
        // rs.next();
        // rs.getString("ID");
        // stat.execute("DROP TABLE TEST");


        // long time = System.currentTimeMillis();


        stat.execute("DROP TABLE IF EXISTS TEST");
        stat.execute("CREATE CACHED TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR(255))");
        PreparedStatement prep = conn.prepareStatement("INSERT INTO TEST VALUES(?, ?)");


        int max = getSize(1, 10000);
        for (int i = 0; i < max; i++) {
            prep.setInt(1, i);
            prep.setString(2,
                    "abchelloasdfaldsjflajdflajdslfoajlskdfkjasdfadsfasdfadsfadfsalksdjflasjflajsdlkfjaksdjflkskd" + i);
            prep.execute();
        }


        // System.exit(0);
        // System.out.println("END "+Value.cacheHit+" "+Value.cacheMiss);


        time = System.currentTimeMillis() - time;
        trace(time + " insert");


        // if(true) return;


        // if(config.log) {
        // System.gc();
        // System.gc();
        // log("mem="+(Runtime.getRuntime().totalMemory() -
        //     Runtime.getRuntime().freeMemory())/1024);
        // }


        // conn.close();


        time = System.currentTimeMillis();


        prep = conn.prepareStatement("UPDATE TEST SET NAME='Another data row which is long' WHERE ID=?");
        for (int i = 0; i < max; i++) {
            prep.setInt(1, i);
            prep.execute();


            // System.out.println("updated "+i);
            // stat.execute("UPDATE TEST SET NAME='Another data row which is
            // long' WHERE ID="+i);
            // ResultSet rs = stat.executeQuery("SELECT * FROM TEST WHERE
            // ID="+i);
            // if(!rs.next()) {
            // throw new AssertionError("hey! i="+i);
            // }
            // if(rs.next()) {
            // throw new AssertionError("hey! i="+i);
            // }
        }
        // for(int i=0; i<max; i++) {
        // stat.execute("DELETE FROM TEST WHERE ID="+i);
        // ResultSet rs = stat.executeQuery("SELECT * FROM TEST WHERE ID="+i);
        // if(rs.next()) {
        // throw new AssertionError("hey!");
        // }
        // }


        time = System.currentTimeMillis() - time;
        trace(time + " update");


        conn.close();
        time = System.currentTimeMillis() - time;
        trace(time + " close");
        deleteDb("speed");
    }

View Full Code Here

        }
        if (!SysProperties.STORE_LOCAL_TIME) {
            return;
        }
        String db = "date;LOG=0;FILE_LOCK=NO";
        Connection conn = getConnection(db);
        Statement stat;
        stat = conn.createStatement();
        stat.execute("create table date_list(tz varchar, t varchar, ts timestamp)");
        conn.close();
        TimeZone defaultTimeZone = TimeZone.getDefault();
        ArrayList<TimeZone> distinct = TestDate.getDistinctTimeZones();
        try {
            for (TimeZone tz : distinct) {
                println("insert using " + tz.getID());
                TimeZone.setDefault(tz);
                DateTimeUtils.resetCalendar();
                conn = getConnection(db);
                PreparedStatement prep = conn.prepareStatement("insert into date_list values(?, ?, ?)");
                prep.setString(1, tz.getID());
                for (int m = 1; m < 10; m++) {
                    String s = "2000-0" + m + "-01 15:00:00";
                    prep.setString(2, s);
                    prep.setTimestamp(3, Timestamp.valueOf(s));
                    prep.execute();
                }
                conn.close();
            }
            printTime("inserted");
            for (TimeZone target : distinct) {
                println("select from " + target.getID());
                if ("Pacific/Kiritimati".equals(target)) {
                    // there is a problem with this time zone, but it seems
                    // unrelated to this database (possibly wrong timezone
                    // information?)
                    continue;
                }
                TimeZone.setDefault(target);
                DateTimeUtils.resetCalendar();
                conn = getConnection(db);
                stat = conn.createStatement();
                ResultSet rs = stat.executeQuery("select * from date_list order by t");
                while (rs.next()) {
                    String source = rs.getString(1);
                    String a = rs.getString(2);
                    String b = rs.getString(3);
                    b = b.substring(0, a.length());
                    if (!a.equals(b)) {
                        assertEquals(source + ">" + target, a, b);
                    }
                }
                conn.close();
            }
        } finally {
            TimeZone.setDefault(defaultTimeZone);
            DateTimeUtils.resetCalendar();
        }
        printTime("done");
        conn = getConnection(db);
        stat = conn.createStatement();
        stat.execute("drop table date_list");
        conn.close();
    }

View Full Code Here

    private static void test(int year, int month, int day, int hour) {
        ValueTimestamp.parse(year + "-" + month + "-" + day + " " + hour + ":00:00");
    }


    private void testAllTimeZones() throws SQLException {
        Connection conn = getConnection("date");
        TimeZone defaultTimeZone = TimeZone.getDefault();
        PreparedStatement prep = conn.prepareStatement("CALL CAST(? AS DATE)");
        try {
            ArrayList<TimeZone> distinct = TestDate.getDistinctTimeZones();
            for (TimeZone tz : distinct) {
                println(tz.getID());
                TimeZone.setDefault(tz);
                DateTimeUtils.resetCalendar();
                for (int d = 101; d < 129; d++) {
                    test(prep, d);
                }
            }
        } finally {
            TimeZone.setDefault(defaultTimeZone);
            DateTimeUtils.resetCalendar();
        }
        conn.close();
        deleteDb("date");
    }

View Full Code Here

    private static int getNewsCount() {
        return newsCount;
    }


    void first() throws SQLException {
        Connection c = base.getConnection();
        c.createStatement().execute("create table news(id identity, state int default 0, text varchar default '')");
        PreparedStatement prep = c.prepareStatement("insert into news() values()");
        for (int i = 0; i < newsCount; i++) {
            prep.executeUpdate();
        }
        c.createStatement().execute("update news set text = 'Text' || id");
        c.close();
    }

View Full Code Here

    }


    public void run() {
        try {
            org.h2.Driver.load();
            Connection conn = DriverManager.getConnection(url + ";MULTI_THREADED=1;LOCK_MODE=3;WRITE_DELAY=0", user, password);
            conn.createStatement().execute(
                    "CREATE TABLE TEST" + id + "(COL1 BIGINT AUTO_INCREMENT PRIMARY KEY, COL2 BIGINT)");
            PreparedStatement prep = conn.prepareStatement("insert into TEST" + id + "(col2) values (?)");
            for (int i = 0; !master.stop; i++) {
                prep.setLong(1, i);
                prep.execute();
            }
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

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.