Package java.sql

Examples of java.sql.Statement


    private void testReferential() throws SQLException {
        deleteDb("transaction");
        Connection c1 = getConnection("transaction");
        c1.setAutoCommit(false);
        Statement s1 = c1.createStatement();
        s1.execute("drop table if exists a");
        s1.execute("drop table if exists b");
        s1.execute("create table a (id integer identity not null, " +
                "code varchar(10) not null, primary key(id))");
        s1.execute("create table b (name varchar(100) not null, a integer, " +
                "primary key(name), foreign key(a) references a(id))");
        Connection c2 = getConnection("transaction");
        c2.setAutoCommit(false);
        s1.executeUpdate("insert into A(code) values('one')");
        Statement s2 = c2.createStatement();
        if (config.mvcc) {
            assertThrows(ErrorCode.REFERENTIAL_INTEGRITY_VIOLATED_PARENT_MISSING_1, s2).
                    executeUpdate("insert into B values('two', 1)");
        } else {
            assertThrows(ErrorCode.LOCK_TIMEOUT_1, s2).
View Full Code Here


    }

    private void testSavepoint() throws SQLException {
        deleteDb("transaction");
        Connection conn = getConnection("transaction");
        Statement stat = conn.createStatement();
        stat.execute("CREATE TABLE TEST0(ID IDENTITY, NAME VARCHAR)");
        stat.execute("CREATE TABLE TEST1(NAME VARCHAR, ID IDENTITY, X TIMESTAMP DEFAULT CURRENT_TIMESTAMP)");
        conn.setAutoCommit(false);
        int[] count = new int[2];
        int[] countCommitted = new int[2];
        int[] countSave = new int[2];
        int len = getSize(2000, 10000);
        Random random = new Random(10);
        Savepoint sp = null;
        for (int i = 0; i < len; i++) {
            int tableId = random.nextInt(2);
            String table = "TEST" + tableId;
            int op = random.nextInt(6);
            switch (op) {
            case 0:
                stat.execute("INSERT INTO " + table + "(NAME) VALUES('op" + i + "')");
                count[tableId]++;
                break;
            case 1:
                if (count[tableId] > 0) {
                    stat.execute("DELETE FROM " + table + " WHERE ID=SELECT MIN(ID) FROM " + table);
                    count[tableId]--;
                }
                break;
            case 2:
                sp = conn.setSavepoint();
View Full Code Here

    private void testPerformance2d() throws SQLException {
        deleteDb("multiDimension");
        Connection conn;
        conn = getConnection("multiDimension");
        Statement stat = conn.createStatement();
        stat.execute("CREATE ALIAS MAP FOR \"" + getClass().getName() + ".interleave\"");
        stat.execute("CREATE TABLE TEST(X INT NOT NULL, Y INT NOT NULL, " +
                "XY BIGINT AS MAP(X, Y), DATA VARCHAR)");
        stat.execute("CREATE INDEX IDX_X ON TEST(X, Y)");
        stat.execute("CREATE INDEX IDX_XY ON TEST(XY)");
        PreparedStatement prep = conn.prepareStatement(
                "INSERT INTO TEST(X, Y, DATA) VALUES(?, ?, ?)");
        // the MultiDimension tool is faster for 4225 (65^2) points
        // the more the bigger the difference
        int max = getSize(30, 65);
        long time = System.currentTimeMillis();
        for (int x = 0; x < max; x++) {
            for (int y = 0; y < max; y++) {
                long t2 = System.currentTimeMillis();
                if (t2 - time > 1000) {
                    int percent = (int) (100.0 * ((double) x * max + y) / ((double) max * max));
                    trace(percent + "%");
                    time = t2;
                }
                prep.setInt(1, x);
                prep.setInt(2, y);
                prep.setString(3, "Test data");
                prep.execute();
            }
        }
        stat.execute("ANALYZE SAMPLE_SIZE 10000");
        PreparedStatement prepRegular = conn.prepareStatement(
                "SELECT * FROM TEST WHERE X BETWEEN ? AND ? " +
                "AND Y BETWEEN ? AND ? ORDER BY X, Y");
        MultiDimension multi = MultiDimension.getInstance();
        String sql = multi.generatePreparedQuery("TEST", "XY", new String[] { "X", "Y" });
View Full Code Here

    private void testPerformance3d() throws SQLException {
        deleteDb("multiDimension");
        Connection conn;
        conn = getConnection("multiDimension");
        Statement stat = conn.createStatement();
        stat.execute("CREATE ALIAS MAP FOR \"" + getClass().getName() + ".interleave\"");
        stat.execute("CREATE TABLE TEST(X INT NOT NULL, Y INT NOT NULL, Z INT NOT NULL, " +
                "XYZ BIGINT AS MAP(X, Y, Z), DATA VARCHAR)");
        stat.execute("CREATE INDEX IDX_X ON TEST(X, Y, Z)");
        stat.execute("CREATE INDEX IDX_XYZ ON TEST(XYZ)");
        PreparedStatement prep = conn.prepareStatement(
                "INSERT INTO TEST(X, Y, Z, DATA) VALUES(?, ?, ?, ?)");
        // the MultiDimension tool is faster for 8000 (20^3) points
        // the more the bigger the difference
        int max = getSize(10, 20);
        long time = System.currentTimeMillis();
        for (int x = 0; x < max; x++) {
            for (int y = 0; y < max; y++) {
                for (int z = 0; z < max; z++) {
                    long t2 = System.currentTimeMillis();
                    if (t2 - time > 1000) {
                        int percent = (int) (100.0 * ((double) x * max + y) / ((double) max * max));
                        trace(percent + "%");
                        time = t2;
                    }
                    prep.setInt(1, x);
                    prep.setInt(2, y);
                    prep.setInt(3, z);
                    prep.setString(4, "Test data");
                    prep.execute();
                }
            }
        }
        stat.execute("ANALYZE SAMPLE_SIZE 10000");
        PreparedStatement prepRegular = conn.prepareStatement(
                "SELECT * FROM TEST WHERE X BETWEEN ? AND ? " +
                "AND Y BETWEEN ? AND ? AND Z BETWEEN ? AND ? ORDER BY X, Y, Z");
        MultiDimension multi = MultiDimension.getInstance();
        String sql = multi.generatePreparedQuery("TEST", "XYZ", new String[] { "X", "Y", "Z" });
View Full Code Here

 
  /**
   * Under the covers this still executes a prepared statement due to the driver handling
   */
  @Test public void testSelect() throws Exception {
    Statement s = conn.createStatement();
    assertTrue(s.execute("select * from tables order by name"));
    TestMMDatabaseMetaData.compareResultSet(s.getResultSet());
  }
View Full Code Here

    public static int[] batch(Connection conn, String... sqls) throws SQLException {
        final boolean autoCommit = conn.getAutoCommit();
        if(autoCommit) {
            conn.setAutoCommit(false);
        }
        Statement stmt = null;
        int[] rows = null;
        try {
            stmt = conn.createStatement();
            for(int i = 0; i < sqls.length; i++) {
                verboseQuery(sqls[i], (Object[]) null);
                stmt.addBatch(sqls[i]);
            }
            rows = stmt.executeBatch();
            conn.commit();
        } catch (SQLException e) {
            conn.rollback();
            rethrow(e, sqls);
        } finally {
View Full Code Here

     * @return The object represents ResultSet.
     */
    public static ResultSet fetch(Connection conn, String sql) throws SQLException {
        ResultSet rs = null;
        try {
            Statement stmt = conn.createStatement();
            verboseQuery(sql, (Object[]) null);
            rs = stmt.executeQuery(sql);
        } catch (SQLException e) {
            rethrow(e, sql, (Object[]) null);
        }
        return rs;
    }
View Full Code Here

     * @param conn The connection to use to run the query.
     * @param sql The SQL to execute.
     * @return The number of rows updated.
     */
    public static int update(Connection conn, String sql) throws SQLException {
        Statement stmt = null;
        int rows = 0;
        try {
            stmt = conn.createStatement();
            verboseQuery(sql, (Object[]) null);
            rows = stmt.executeUpdate(sql);
        } catch (SQLException e) {
            rethrow(e, sql, (Object[]) null);
        } finally {
            close(stmt);
        }
View Full Code Here

    /**
     * Close a <code>ResultSet</code>, avoid closing if null.
     */
    public static void closeAll(ResultSet rs) throws SQLException {
        if(rs != null) {
            Statement stmt = rs.getStatement();
            if(stmt != null) {
                Connection conn = stmt.getConnection();
                stmt.close();
                closeQuietly(conn);
            } else {
                rs.close();
            }
        }
View Full Code Here

        }
        stat.execute("DROP TABLE TEST");
    }

    private void testUniqueIndexSingleNull() throws SQLException {
        Statement stat = conn.createStatement();
        String[] modes = { "PostgreSQL", "MySQL", "HSQLDB", "MSSQLServer", "Derby", "Oracle", "Regular" };
        String multiNull = "PostgreSQL,MySQL,Oracle,Regular";
        for (String mode : modes) {
            stat.execute("SET MODE " + mode);
            stat.execute("CREATE TABLE TEST(ID INT)");
            stat.execute("CREATE UNIQUE INDEX IDX_ID_U ON TEST(ID)");
            try {
                stat.execute("INSERT INTO TEST VALUES(1), (2), (NULL), (NULL)");
                assertTrue(mode + " mode should not support multiple NULL", multiNull.indexOf(mode) >= 0);
            } catch (SQLException e) {
                assertTrue(mode + " mode should support multiple NULL", multiNull.indexOf(mode) < 0);
            }
            stat.execute("DROP TABLE TEST");
        }
    }
View Full Code Here

TOP

Related Classes of java.sql.Statement

Copyright © 2018 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.