Examples of java.sql.Statement

java.sql.Statement
Interface used for executing static SQL statements and returning their results. By default, an object implementing the Statement interface can returns results as ResultSets. For any given Statement object, only one ResultSet can be open at one time. A call to any of the execution methods of Statement will cause any previously created ResultSet object for that Statement to be closed implicitly.
To have multiple ResultSet objects open concurrently, multiple Statement objects must be used.

        conn.close();
    }


    private void testSource() throws SQLException {
        Connection conn = getConnection("functions");
        Statement stat = conn.createStatement();
        ResultSet rs;
        stat.execute("create force alias sayHi as 'String test(String name) {\n" +
                "return \"Hello \" + name;\n}'");
        rs = stat.executeQuery("SELECT ALIAS_NAME FROM INFORMATION_SCHEMA.FUNCTION_ALIASES");
        rs.next();
        assertEquals("SAY" + "HI", rs.getString(1));
        rs = stat.executeQuery("call sayHi('Joe')");
        rs.next();
        assertEquals("Hello Joe", rs.getString(1));
        if (!config.memory) {
            conn.close();
            conn = getConnection("functions");
            stat = conn.createStatement();
            rs = stat.executeQuery("call sayHi('Joe')");
            rs.next();
            assertEquals("Hello Joe", rs.getString(1));
        }
        stat.execute("drop alias sayHi");
        conn.close();
    }

View Full Code Here

        }
        deleteDb("openClose");
        String url = getURL("openClose", true);
        org.h2.Driver.load();
        Connection conn = DriverManager.getConnection(url, "sa", "abc def");
        Statement stat = conn.createStatement();
        stat.execute("CREATE TABLE TEST(C CLOB)");
        stat.execute("INSERT INTO TEST VALUES(SPACE(10000))");
        stat.execute("BACKUP TO '" + getBaseDir() + "/test.zip'");
        conn.close();
        deleteDb("openClose");
        Restore.execute(getBaseDir() + "/test.zip", getBaseDir(), null, true);
        conn = DriverManager.getConnection(url, "sa", "abc def");
        stat = conn.createStatement();
        ResultSet rs = stat.executeQuery("SELECT * FROM TEST");
        rs.next();
        assertEquals(10000, rs.getString(1).length());
        assertFalse(rs.next());
        conn.close();
        IOUtils.delete(getBaseDir() + "/test.zip");

View Full Code Here

        deleteDb("openClose");
        String user = getUser(), password = getPassword();
        String url = getURL("openClose;DATABASE_EVENT_LISTENER='" + TestOpenClose.class.getName()
                + "'", true);
        Connection conn = DriverManager.getConnection(url, user, password);
        Statement stat = conn.createStatement();
        try {
            stat.execute("CREATE TABLE TEST(ID IDENTITY, NAME VARCHAR)");
            stat.execute("SET MAX_MEMORY_UNDO 100000");
            stat.execute("CREATE INDEX IDXNAME ON TEST(NAME)");
            stat.execute("INSERT INTO TEST SELECT X, X || ' Data' FROM SYSTEM_RANGE(1, 1000)");
        } catch (SQLException e) {
            // ok
        }
        stat.close();
        conn.close();
        conn = DriverManager.getConnection(url, user, password);
        stat = conn.createStatement();
        ResultSet rs = stat.executeQuery("SELECT * FROM DUAL");
        if (rs.next()) {
            rs.getString(1);
        }
        rs.close();
        stat.close();
        conn.close();
        conn = DriverManager.getConnection(url, user, password);
        stat = conn.createStatement();
        // stat.execute("SET DB_CLOSE_DELAY 0");
        stat.executeUpdate("SHUTDOWN");
        stat.close();
        conn.close();
    }

View Full Code Here

        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


    private void testDomain() throws SQLException {
        if (config.memory) {
            return;
        }
        Statement stat = conn.createStatement();
        stat.execute("create table test(id int primary key) as select 1");
        assertThrows(ErrorCode.USER_DATA_TYPE_ALREADY_EXISTS_1, stat).
                execute("create domain int as varchar");
        conn.close();
        conn = getConnection("compatibility");
        stat = conn.createStatement();
        stat.execute("insert into test values(2)");
        stat.execute("drop table test");
    }

View Full Code Here

        stat.execute("insert into test values(2)");
        stat.execute("drop table test");
    }


    private void testColumnAlias() throws SQLException {
        Statement stat = conn.createStatement();
        String[] modes = { "PostgreSQL", "MySQL", "HSQLDB", "MSSQLServer", "Derby", "Oracle", "Regular" };
        String columnAlias;
        columnAlias = "MySQL,Regular";
        stat.execute("CREATE TABLE TEST(ID INT)");
        for (String mode : modes) {
            stat.execute("SET MODE " + mode);
            ResultSet rs = stat.executeQuery("SELECT ID I FROM TEST");
            ResultSetMetaData meta = rs.getMetaData();
            String columnName = meta.getColumnName(1);
            String tableName = meta.getTableName(1);
            if ("ID".equals(columnName) && "TEST".equals(tableName)) {
                assertTrue(mode + " mode should not support columnAlias", columnAlias.indexOf(mode) >= 0);
            } else if ("I".equals(columnName) && tableName == null) {
                assertTrue(mode + " mode should support columnAlias", columnAlias.indexOf(mode) < 0);
            } else {
                fail();
            }
        }
        stat.execute("DROP TABLE TEST");
    }

View Full Code Here

        // 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);

View Full Code Here

    @AfterClass public static void tearDown() throws SQLException {
      connection.close();
    }


    @Test public void testSetTableStats() throws Exception {
      Statement s = connection.createStatement();
      ResultSet rs = s.executeQuery("select cardinality from tables where name = 'PARTSSUPPLIER.PARTS'");
      rs.next();
      assertEquals(16, rs.getInt(1));
      s.execute("call setTableStats(tableName=>'partssupplier.partssupplier.parts', cardinality=>32)");
      rs = s.executeQuery("select cardinality from tables where name = 'PARTSSUPPLIER.PARTS'");
      rs.next();
      assertEquals(32, rs.getInt(1));
    }

View Full Code Here

      rs.next();
      assertEquals(32, rs.getInt(1));
    }
    
    @Test public void testSetColumnStats() throws Exception {
      Statement s = connection.createStatement();
      ResultSet rs = s.executeQuery("select MinRange, MaxRange, DistinctCount, NullCount from columns where name = 'PART_ID'");
      rs.next();
      assertEquals(null, rs.getString(1));
      assertEquals(null, rs.getString(2));
      assertEquals(-1, rs.getInt(3));
      assertEquals(-1, rs.getInt(4));
      s.execute("call setColumnStats(tableName=>'partssupplier.partssupplier.parts', columnName=>'PART_ID', max=>32, nullcount=>0)");
      rs = s.executeQuery("select MinRange, MaxRange, DistinctCount, NullCount from columns where name = 'PART_ID'");
      rs.next();
      assertEquals(null, rs.getString(1));
      assertEquals("32", rs.getString(2));
      assertEquals(-1, rs.getInt(3));
      assertEquals(0, rs.getInt(4));

View Full Code Here

      assertEquals(-1, rs.getInt(3));
      assertEquals(0, rs.getInt(4));
    }
    
    @Test(expected=SQLException.class) public void testSetColumnStatsInvalidColumn() throws Exception {
      Statement s = connection.createStatement();
      s.execute("call setColumnStats(tableName=>'partssupplier.partssupplier.parts', columnName=>'foo', max=>32, nullcount=>0)");
    }

View Full Code Here

0 1 2 3 4 5 6 7 8 9

TOP

Related Classes of java.sql.Statement

co.nubetech.hiho.hive.HiveUtility

co.nubetech.hiho.job.TestDBQueryInputJobWithCluster

co.nubetech.hiho.job.TestExportToDB

com.acme.JNDITest

com.alibaba.wasp.jdbc.TestJdbcResultSet

com.alibaba.wasp.jdbc.TestPreparedStatement

com.alibaba.wasp.jdbcx.JdbcXAConnection

com.alimama.mdrill.adhoc.MysqlInfo

com.alimama.web.TableJoin

com.asakusafw.bulkloader.cache.LocalCacheInfoRepositoryTest

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.