Package java.sql

Examples of java.sql.Statement


        if (config.memory) {
            return;
        }
        deleteDb("memoryUsage");
        conn = getConnection("memoryUsage");
        Statement stat = conn.createStatement();
        stat.execute("create table test(id int, name varchar(255))");
        PreparedStatement prep = conn.prepareStatement("insert into test values(?, space(200))");
        int len = getSize(10000, 100000);
        for (int i = 0; i < len; i++) {
            if (i % 1000 == 0) {
                // trace("[" + i + "/" + len + "] KB: " + MemoryUtils.getMemoryUsed());
            }
            prep.setInt(1, i);
            prep.executeUpdate();
        }
        int base = Utils.getMemoryUsed();
        stat.execute("create index idx_test_id on test(id)");
        System.gc();
        System.gc();
        int used = Utils.getMemoryUsed();
        if ((used - base) > getSize(7500, 12000)) {
            fail("Used: " + (used - base));
        }
        stat.execute("drop table test");
        conn.close();
    }
View Full Code Here


        printTimeMemory("connect", System.currentTimeMillis() - time);
        conn1.close();
    }

    private void insertUpdateSelectDelete() throws SQLException {
        Statement stat = conn.createStatement();
        long time;
        int len = getSize(1, 2000);

        // insert
        time = System.currentTimeMillis();
        stat.execute("DROP TABLE IF EXISTS TEST");
        trace("drop=" + (System.currentTimeMillis() - time));
        stat.execute("CREATE CACHED TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR(255))");
        PreparedStatement prep = conn.prepareStatement("INSERT INTO TEST VALUES(?, 'Hello World')");
        printTimeMemory("start", 0);
        time = System.currentTimeMillis();
        for (int i = 0; i < len; i++) {
            prep.setInt(1, i);
View Full Code Here

        // test the database event listener
        conn = DriverManager.getConnection(url + ";DATABASE_EVENT_LISTENER='" + getClass().getName() + "'");
        conn.close();

        Statement stat;

        conn = DriverManager.getConnection(url);
        restart();
        stat = conn.createStatement();
        restart();
        stat.execute("create table test(id identity, name varchar)");
        restart();
        PreparedStatement prep = conn.prepareStatement("insert into test values(null, ?)");
        restart();
        prep.setString(1, "Hello");
        restart();
        prep.execute();
        restart();
        prep.setString(1, "World");
        restart();
        prep.execute();
        restart();
        ResultSet rs = stat.executeQuery("select * from test order by id");
        restart();
        assertTrue(rs.next());
        restart();
        assertEquals(1, rs.getInt(1));
        restart();
        assertEquals("Hello", rs.getString(2));
        restart();
        assertTrue(rs.next());
        restart();
        assertEquals(2, rs.getInt(1));
        restart();
        assertEquals("World", rs.getString(2));
        restart();
        assertFalse(rs.next());
        restart();
        stat.execute("SET @TEST 10");
        restart();
        rs = stat.executeQuery("CALL @TEST");
        rs.next();
        assertEquals(10, rs.getInt(1));
        stat.setFetchSize(10);
        restart();
        rs = stat.executeQuery("select * from system_range(1, 20)");
        restart();
        for (int i = 0;; i++) {
            try {
                boolean more = rs.next();
                if (!more) {
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

        IOUtils.deleteRecursive(TEMP_DIR, true);
    }

    private void testFunctionTable() throws SQLException {
        Connection conn = getConnection("functions");
        Statement stat = conn.createStatement();
        stat.execute("create alias simple_function_table for \"" + TestFunctions.class.getName() + ".simpleFunctionTable\"");
        stat.execute("select * from simple_function_table() where a>0 and b in ('x', 'y')");
        conn.close();
    }
View Full Code Here

        return result;
    }

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

        String createSQL = "CREATE TABLE testNvl2(id BIGINT, txt1 varchar, txt2 varchar, num number(9, 0));";
        stat.execute(createSQL);
        stat.execute("insert into testNvl2(id, txt1, txt2, num) values(1, 'test1', 'test2', null)");
        stat.execute("insert into testNvl2(id, txt1, txt2, num) values(2, null, 'test4', null)");
        stat.execute("insert into testNvl2(id, txt1, txt2, num) values(3, 'test5', null, null)");
        stat.execute("insert into testNvl2(id, txt1, txt2, num) values(4, null, null, null)");
        stat.execute("insert into testNvl2(id, txt1, txt2, num) values(5, '2', null, 1)");
        stat.execute("insert into testNvl2(id, txt1, txt2, num) values(6, '2', null, null)");
        stat.execute("insert into testNvl2(id, txt1, txt2, num) values(7, 'test2', null, null)");

        String query = "SELECT NVL2(txt1, txt1, txt2), txt1 FROM testNvl2 order by id asc";
        ResultSet rs = stat.executeQuery(query);
        rs.next();
        String actual = rs.getString(1);
        assertEquals("test1", actual);
        rs.next();
        actual = rs.getString(1);
        assertEquals("test4", actual);
        rs.next();
        actual = rs.getString(1);
        assertEquals("test5", actual);
        rs.next();
        actual = rs.getString(1);
        assertEquals(null, actual);
        assertEquals(rs.getMetaData().getColumnType(2), rs.getMetaData().getColumnType(1));
        rs.close();

        rs = stat.executeQuery("SELECT NVL2(num, num, txt1), num FROM testNvl2 where id in(5, 6) order by id asc");
        rs.next();
        assertEquals(rs.getMetaData().getColumnType(2), rs.getMetaData().getColumnType(1));

        assertThrows(ErrorCode.DATA_CONVERSION_ERROR_1, stat).
                executeQuery("SELECT NVL2(num, num, txt1), num FROM testNvl2 where id = 7 order by id asc");

        // nvl2 should return expr2's datatype, if expr2 is character data.
        rs = stat.executeQuery("SELECT NVL2(1, 'test', 123), 'test' FROM dual");
        rs.next();
        actual = rs.getString(1);
        assertEquals("test", actual);
        assertEquals(rs.getMetaData().getColumnType(2), rs.getMetaData().getColumnType(1));
View Full Code Here

        conn.close();
    }

    private void testValue() throws SQLException {
        Connection conn = getConnection("functions");
        Statement stat = conn.createStatement();
        ResultSet rs;
        stat.execute("create alias TO_CHAR for \"" + getClass().getName() + ".toChar\"");
        rs = stat.executeQuery("call TO_CHAR(TIMESTAMP '2001-02-03 04:05:06', 'format')");
        rs.next();
        assertEquals("2001-02-03 04:05:06.0", rs.getString(1));
        stat.execute("drop alias TO_CHAR");
        conn.close();
    }
View Full Code Here

        return args[0].convertTo(Value.STRING);
    }

    private void testDefaultConnection() throws SQLException {
        Connection conn = getConnection("functions;DEFAULT_CONNECTION=TRUE");
        Statement stat = conn.createStatement();
        stat.execute("create alias test for \""+TestFunctions.class.getName()+".testDefaultConn\"");
        stat.execute("call test()");
        stat.execute("drop alias test");
        conn.close();
    }
View Full Code Here

        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

TOP

Related Classes of java.sql.Statement

Copyright © 2015 www.massapicom. All rights reserved.
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.