testpreparedstatement.java

来自「非常棒的java数据库」· Java 代码 · 共 887 行 · 第 1/3 页

/*
 * Copyright 2004-2008 H2 Group. Licensed under the H2 License, Version 1.0
 * (license2)
 * Initial Developer: H2 Group
 */
package org.h2.test.jdbc;

import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.io.StringReader;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.ParameterMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.sql.Types;

import org.h2.test.TestBase;

/**
 * Tests for the PreparedStatement implementation.
 */
public class TestPreparedStatement extends TestBase {

    static final int LOB_SIZE = 4000, LOB_SIZE_BIG = 512 * 1024;

    public void test() throws Exception {

        deleteDb("preparedStatement");
        Connection conn = getConnection("preparedStatement");
        testExecuteErrorTwice(conn);
        testTempView(conn);
        testInsertFunction(conn);
        testPrepareRecompile(conn);
        testMaxRowsChange(conn);
        testUnknownDataType(conn);
        testCancelReuse(conn);
        testCoalesce(conn);
        testPreparedStatementMetaData(conn);
        testDate(conn);
        testArray(conn);
        testUUIDGeneratedKeys(conn);
        testSetObject(conn);
        testPreparedSubquery(conn);
        testLikeIndex(conn);
        testCasewhen(conn);
        testSubquery(conn);
        testObject(conn);
        if (config.jdk14) {
            testIdentity(conn);
        }
        testDataTypes(conn);
        testBlob(conn);
        testClob(conn);
        testParameterMetaData(conn);
        conn.close();
    }

    private void testExecuteErrorTwice(Connection conn) throws Exception {
        PreparedStatement prep = conn.prepareStatement("CREATE TABLE BAD AS SELECT A");
        try {
            prep.execute();
            error();
        } catch (SQLException e) {
            checkNotGeneralException(e);
        }
        try {
            prep.execute();
            error();
        } catch (SQLException e) {
            checkNotGeneralException(e);
        }
    }


    private void testTempView(Connection conn) throws Exception {
        Statement stat = conn.createStatement();
        PreparedStatement prep;
        stat.execute("CREATE TABLE TEST(FIELD INT PRIMARY KEY)");
        stat.execute("INSERT INTO TEST VALUES(1)");
        stat.execute("INSERT INTO TEST VALUES(2)");
        prep = conn.prepareStatement("select FIELD FROM "
                + "(select FIELD FROM (SELECT FIELD  FROM TEST WHERE FIELD = ?) AS T2 "
                + "WHERE T2.FIELD = ?) AS T3 WHERE T3.FIELD = ?");
        prep.setInt(1, 1);
        prep.setInt(2, 1);
        prep.setInt(3, 1);
        ResultSet rs = prep.executeQuery();
        rs.next();
        check(1, rs.getInt(1));
        prep.setInt(1, 2);
        prep.setInt(2, 2);
        prep.setInt(3, 2);
        rs = prep.executeQuery();
        rs.next();
        check(2, rs.getInt(1));
        stat.execute("DROP TABLE TEST");
    }

    private void testInsertFunction(Connection conn) throws Exception {
        Statement stat = conn.createStatement();
        PreparedStatement prep;
        ResultSet rs;

        stat.execute("CREATE TABLE TEST(ID INT, H BINARY)");
        prep = conn.prepareStatement("INSERT INTO TEST VALUES(?, HASH('SHA256', STRINGTOUTF8(?), 5))");
        prep.setInt(1, 1);
        prep.setString(2, "One");
        prep.execute();
        prep.setInt(1, 2);
        prep.setString(2, "Two");
        prep.execute();
        rs = stat.executeQuery("SELECT COUNT(DISTINCT H) FROM TEST");
        rs.next();
        check(rs.getInt(1), 2);

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

    private void testPrepareRecompile(Connection conn) throws Exception {
        Statement stat = conn.createStatement();
        PreparedStatement prep;
        ResultSet rs;

        prep = conn.prepareStatement("SELECT COUNT(*) FROM DUAL WHERE ? IS NULL");
        prep.setString(1, null);
        prep.executeQuery();
        stat.execute("CREATE TABLE TEST(ID INT)");
        stat.execute("DROP TABLE TEST");
        prep.setString(1, null);
        prep.executeQuery();
        prep.setString(1, "X");
        rs = prep.executeQuery();
        rs.next();
        check(rs.getInt(1), 0);

        stat.execute("CREATE TABLE t1 (c1 INT, c2 VARCHAR(10))");
        stat.execute("INSERT INTO t1 SELECT X, CONCAT('Test', X)  FROM SYSTEM_RANGE(1, 5);");
        prep = conn.prepareStatement("SELECT c1, c2 FROM t1 WHERE c1 = ?");
        prep.setInt(1, 1);
        prep.executeQuery();
        stat.execute("CREATE TABLE t2 (x int PRIMARY KEY)");
        prep.setInt(1, 2);
        rs = prep.executeQuery();
        rs.next();
        check(rs.getInt(1), 2);
        prep.setInt(1, 3);
        rs = prep.executeQuery();
        rs.next();
        check(rs.getInt(1), 3);
        stat.execute("DROP TABLE t1, t2");

    }

    private void testMaxRowsChange(Connection conn) throws Exception {
        PreparedStatement prep = conn.prepareStatement("SELECT * FROM SYSTEM_RANGE(1, 100)");
        ResultSet rs;
        for (int j = 1; j < 20; j++) {
            prep.setMaxRows(j);
            rs = prep.executeQuery();
            for (int i = 0; i < j; i++) {
                check(rs.next());
            }
            checkFalse(rs.next());
        }
    }

    private void testUnknownDataType(Connection conn) throws Exception {
        try {
            PreparedStatement prep = conn.prepareStatement(
            "SELECT * FROM (SELECT ? FROM DUAL)");
            prep.setInt(1, 1);
            prep.execute();
            error();
        } catch (SQLException e) {
            checkNotGeneralException(e);
        }
        PreparedStatement prep = conn.prepareStatement("SELECT -?");
        prep.setInt(1, 1);
        prep.execute();
        prep = conn.prepareStatement("SELECT ?-?");
        prep.setInt(1, 1);
        prep.setInt(2, 2);
        prep.execute();
    }

    private void testCancelReuse(Connection conn) throws Exception {
        conn.createStatement().execute("CREATE ALIAS YIELD FOR \"java.lang.Thread.yield\"");
        final PreparedStatement prep = conn.prepareStatement("SELECT YIELD() FROM SYSTEM_RANGE(1, 1000000) LIMIT ?");
        prep.setInt(1, 100000000);
        Thread t = new Thread() {
            public void run() {
                try {
                    prep.execute();
                } catch (SQLException e) {
                    // ignore
                }
            }
        };
        t.start();
        Thread.sleep(10);
        try {
            prep.cancel();
        } catch (SQLException e) {
            this.checkNotGeneralException(e);
        }
        prep.setInt(1, 1);
        ResultSet rs = prep.executeQuery();
        check(rs.next());
        check(rs.getInt(1), 0);
        checkFalse(rs.next());
    }

    private void testCoalesce(Connection conn) throws Exception {
        Statement stat = conn.createStatement();
        stat.executeUpdate("create table test(tm timestamp)");
        stat.executeUpdate("insert into test values(current_timestamp)");
        PreparedStatement prep = conn.prepareStatement("update test set tm = coalesce(?,tm)");
        prep.setTimestamp(1, new java.sql.Timestamp(System.currentTimeMillis()));
        prep.executeUpdate();
        stat.executeUpdate("drop table test");
    }

    private void testPreparedStatementMetaData(Connection conn) throws Exception {
        PreparedStatement prep = conn.prepareStatement("select * from table(x int = ?, name varchar = ?)");
        ResultSetMetaData meta = prep.getMetaData();
        check(meta.getColumnCount(), 2);
        check(meta.getColumnTypeName(1), "INTEGER");
        check(meta.getColumnTypeName(2), "VARCHAR");
        prep = conn.prepareStatement("call 1");
        meta = prep.getMetaData();
        check(meta.getColumnCount(), 1);
        check(meta.getColumnTypeName(1), "INTEGER");
    }

    private void testArray(Connection conn) throws Exception {
        PreparedStatement prep = conn.prepareStatement("select * from table(x int = ?) order by x");
        prep.setObject(1, new Object[] { new BigDecimal("1"), "2" });
        ResultSet rs = prep.executeQuery();
        rs.next();
        check(rs.getString(1), "1");
        rs.next();
        check(rs.getString(1), "2");
        checkFalse(rs.next());
    }

    private void testUUIDGeneratedKeys(Connection conn) throws Exception {
        Statement stat = conn.createStatement();
        stat.execute("CREATE TABLE TEST_UUID(id UUID DEFAULT random_UUID() PRIMARY KEY)");
        stat.execute("INSERT INTO TEST_UUID() VALUES()");
        ResultSet rs = stat.getGeneratedKeys();
        rs.next();
        byte[] data = rs.getBytes(1);
        check(data.length, 16);
        stat.execute("DROP TABLE TEST_UUID");
    }

    private void testSetObject(Connection conn) throws Exception {
        Statement stat = conn.createStatement();
        stat.execute("CREATE TABLE TEST(ID INT, DATA BINARY, JAVA OTHER)");
        PreparedStatement prep = conn.prepareStatement("INSERT INTO TEST VALUES(?, ?, ?)");
        prep.setInt(1, 1);
        prep.setObject(2, new Integer(11));
        prep.setObject(3, null);
        prep.execute();
        prep.setInt(1, 2);
        prep.setObject(2, new Integer(101), Types.OTHER);
        prep.setObject(3, new Integer(103), Types.OTHER);
        prep.execute();
        PreparedStatement p2 = conn.prepareStatement("SELECT * FROM TEST ORDER BY ID");
        ResultSet rs = p2.executeQuery();
        rs.next();
        Object o = rs.getObject(2);
        check(o instanceof byte[]);
        check(rs.getObject(3) == null);
        rs.next();
        o = rs.getObject(2);
        check(o instanceof byte[]);
        o = rs.getObject(3);
        check(o instanceof Integer);
        check(((Integer) o).intValue(), 103);
        checkFalse(rs.next());
        stat.execute("DROP TABLE TEST");
    }

    private void testDate(Connection conn) throws Exception {
        PreparedStatement prep = conn.prepareStatement("SELECT ?");
        Timestamp ts = Timestamp.valueOf("2001-02-03 04:05:06");
        prep.setObject(1, new java.util.Date(ts.getTime()));
        ResultSet rs = prep.executeQuery();
        rs.next();
        Timestamp ts2 = rs.getTimestamp(1);