testsql.java

hsqldb是100％java实现的数据库,是一个开放源代码的JAVA数据库 l 具有标准的SQL语法和JAVA接口 l HSQLDB可以自由使用和分发 l 非常简洁和快速的

    public void testDoubleNaN() {

        double  value    = 0;
        boolean wasEqual = false;
        String  message  = "DB operation completed";
        String ddl1 =
            "DROP TABLE t1 IF EXISTS;"
            + "CREATE TABLE t1 ( d DECIMAL, f DOUBLE, l BIGINT, i INTEGER, s SMALLINT, t TINYINT, "
            + "dt DATE DEFAULT CURRENT_DATE, ti TIME DEFAULT CURRENT_TIME, ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP );";

        try {
            stmnt.execute(ddl1);

            PreparedStatement ps = connection.prepareStatement(
                "INSERT INTO t1 (d,f,l,i,s,t,dt,ti,ts) VALUES (?,?,?,?,?,?,?,?,?)");

            ps.setString(1, "0.2");
            ps.setDouble(2, 0.2);
            ps.setLong(3, java.lang.Long.MAX_VALUE);
            ps.setInt(4, Integer.MAX_VALUE);
            ps.setInt(5, Short.MAX_VALUE);
            ps.setInt(6, 0);
            ps.setDate(7, new java.sql.Date(System.currentTimeMillis()));
            ps.setTime(8, new java.sql.Time(System.currentTimeMillis()));
            ps.setTimestamp(
                9, new java.sql.Timestamp(System.currentTimeMillis()));
            ps.execute();
            ps.setInt(1, 0);
            ps.setDouble(2, java.lang.Double.NaN);
            ps.setLong(3, java.lang.Long.MIN_VALUE);
            ps.setInt(4, Integer.MIN_VALUE);
            ps.setInt(5, Short.MIN_VALUE);
            ps.setInt(6, 0);

            // allowed conversions
            ps.setTimestamp(
                7, new java.sql.Timestamp(System.currentTimeMillis() + 1));
            ps.setTime(8, new java.sql.Time(System.currentTimeMillis() + 1));
            ps.setDate(9, new java.sql.Date(System.currentTimeMillis() + 1));
            ps.execute();

            //
            ps.setInt(1, 0);
            ps.setDouble(2, java.lang.Double.POSITIVE_INFINITY);
            ps.setInt(4, Integer.MIN_VALUE);

            // test conversion
            ps.setObject(5, Boolean.TRUE);
            ps.setBoolean(5, true);
            ps.setObject(5, new Short((short) 2), Types.SMALLINT);
            ps.setObject(6, new Integer(2), Types.TINYINT);

            // allowed conversions
            ps.setObject(7, new java.sql.Date(System.currentTimeMillis()
                                              + 2));
            ps.setObject(8, new java.sql.Time(System.currentTimeMillis()
                                              + 2));
            ps.setObject(9, new java.sql.Timestamp(System.currentTimeMillis()
                                                   + 2));
            ps.execute();
            ps.setObject(1, new Float(0), Types.INTEGER);
            ps.setObject(4, new Float(1), Types.INTEGER);
            ps.setDouble(2, java.lang.Double.NEGATIVE_INFINITY);
            ps.execute();

            ResultSet rs =
                stmnt.executeQuery("SELECT d, f, l, i, s*2, t FROM t1");
            boolean result = rs.next();

            value = rs.getDouble(2);

//            int smallintValue = rs.getShort(3);
            int integerValue = rs.getInt(4);

            if (rs.next()) {
                value        = rs.getDouble(2);
                wasEqual     = Double.isNaN(value);
                integerValue = rs.getInt(4);

                // tests for conversion
                // getInt on DECIMAL
                integerValue = rs.getInt(1);
            }

            if (rs.next()) {
                value    = rs.getDouble(2);
                wasEqual = wasEqual && value == Double.POSITIVE_INFINITY;
            }

            if (rs.next()) {
                value    = rs.getDouble(2);
                wasEqual = wasEqual && value == Double.NEGATIVE_INFINITY;
            }

            rs = stmnt.executeQuery("SELECT MAX(i) FROM t1");

            if (rs.next()) {
                int max = rs.getInt(1);

                System.out.println("Max value for i: " + max);
            }

            {
                stmnt.execute("drop table CDTYPE if exists");

                // test for the value MAX(column) in an empty table
                rs = stmnt.executeQuery(
                    "CREATE TABLE cdType (ID INTEGER NOT NULL, name VARCHAR(50), PRIMARY KEY(ID))");
                rs = stmnt.executeQuery("SELECT MAX(ID) FROM cdType");

                if (rs.next()) {
                    int max = rs.getInt(1);

                    System.out.println("Max value for ID: " + max);
                } else {
                    System.out.println("Max value for ID not returned");
                }

                stmnt.executeUpdate(
                    "INSERT INTO cdType VALUES (10,'Test String');");
                stmnt.executeQuery("CALL IDENTITY();");

                try {
                    stmnt.executeUpdate(
                        "INSERT INTO cdType VALUES (10,'Test String');");
                } catch (SQLException e1) {
                    stmnt.execute("ROLLBACK");
                    connection.rollback();
                }
            }
        } catch (SQLException e) {
            fail(e.getMessage());
        }

        System.out.println("testDoubleNaN complete");

        // assert new behaviour
        assertEquals(true, wasEqual);
    }

    public void testAny() {

        try {
            String ddl =
                "drop table PRICE_RELATE_USER_ORDER_V2 if exists;"
                + "create table PRICE_RELATE_USER_ORDER_V2 "
                + "(ID_ORDER_V2 BIGINT, ID_USER NUMERIC, DATE_CREATE TIMESTAMP)";
            String sql = "insert into PRICE_RELATE_USER_ORDER_V2 "
                         + "(ID_ORDER_V2, ID_USER, DATE_CREATE) " + "values "
                         + "(?, ?, ?)";
            Statement st = connection.createStatement();

            st.execute(ddl);

            PreparedStatement ps = connection.prepareStatement(sql);

            ps.setLong(1, 1);
            ps.setNull(2, Types.NUMERIC);
            ps.setTimestamp(
                3, new java.sql.Timestamp(System.currentTimeMillis()));
            ps.execute();
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("TestSql.testAny() error: " + e.getMessage());
        }

        System.out.println("testAny complete");
    }

    /**
     * Fix for bug #1201135
     */
    public void testBinds() {

        try {
            PreparedStatement pstmt =
                connection.prepareStatement("drop table test if exists");

            pstmt.execute();

            pstmt =
                connection.prepareStatement("create table test (id integer)");

            pstmt.execute();

            pstmt =
                connection.prepareStatement("insert into test values (10)");

            pstmt.execute();

            pstmt =
                connection.prepareStatement("insert into test values (20)");

            pstmt.execute();

            pstmt = connection.prepareStatement(
                "select count(*) from test where ? is null");

            pstmt.setString(1, "hello");

            ResultSet rs = pstmt.executeQuery();

            rs.next();

            int count = rs.getInt(1);

            assertEquals(0, count);

            pstmt =
                connection.prepareStatement("select limit ? 1  id from test");

            pstmt.setInt(1, 0);

            rs = pstmt.executeQuery();

            rs.next();

            count = rs.getInt(1);

            assertEquals(10, count);
            pstmt.setInt(1, 1);

            rs = pstmt.executeQuery();

            rs.next();

            count = rs.getInt(1);

            assertEquals(20, count);
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("TestSql.testBinds() error: "
                               + e.getMessage());
        }
    }

    // miscellaneous tests
    public void testX1() {

        String tableDDL =
            "create table lo_attribute ( "
            + "learningid varchar(15) not null, "
            + "ordering integer not null,"
            + "attribute_value_data varchar(85) null,"
            + "constraint PK_LO_ATTR primary key (learningid, ordering))";

        try {
            Statement stmt = connection.createStatement();

            stmt.execute("drop table lo_attribute if exists");
            stmt.execute(tableDDL);
            stmt.execute(
                "insert into lo_attribute values('abcd', 10, 'cdef')");
            stmt.execute(
                "insert into lo_attribute values('bcde', 20, 'cdef')");
        } catch (SQLException e) {
            assertEquals(0, 1);
        }

        try {
            String prepared =
                "update lo_attribute set "
                + " ordering = (ordering - 1) where ordering > ?";
            PreparedStatement ps = connection.prepareStatement(prepared);

            ps.setInt(1, 10);
            ps.execute();
        } catch (SQLException e) {
            assertEquals(0, 1);
        }

        try {
            connection.setAutoCommit(false);

            java.sql.Savepoint savepoint =
                connection.setSavepoint("savepoint");

            connection.createStatement().executeQuery("CALL true;");
            connection.rollback(savepoint);
        } catch (SQLException e) {
            assertEquals(0, 1);
        }
    }

    /**
     * In 1.8.0.2, this fails in client / server due to column type of the
     * second select for b1 being boolean, while the first select is interpreted
     * as varchar. The rowOutputBase class attempts to cast the Java Boolean
     * into String.
     */
    public void testUnionColumnTypes() {

        try {
            Connection conn = newConnection();
            Statement  stmt = conn.createStatement();

            stmt.execute("DROP TABLE test1 IF EXISTS");
            stmt.execute("DROP TABLE test2 IF EXISTS");
            stmt.execute("CREATE TABLE test1 (id int, b1 boolean)");
            stmt.execute("CREATE TABLE test2 (id int)");
            stmt.execute("INSERT INTO test1 VALUES(1,true)");
            stmt.execute("INSERT INTO test2 VALUES(2)");

            ResultSet rs = stmt.executeQuery(
                "select id,null as b1 from test2 union select id, b1 from test1");
            Boolean[] array = new Boolean[2];

            for (int i = 0; rs.next(); i++) {
                boolean boole = rs.getBoolean(2);

                array[i] = Boolean.valueOf(boole);

                if (rs.wasNull()) {
                    array[i] = null;
                }
            }

            boolean result = (array[0] == null && array[1] == Boolean.TRUE)
                             || (array[0] == Boolean.TRUE
                                 && array[1] == null);

            assertTrue(result);
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("TestSql.testUnionColumnType() error: "
                               + e.getMessage());
        }
    }

    protected void tearDown() {

        try {
            connection.close();
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("TestSql.tearDown() error: " + e.getMessage());
        }
    }

    public static void main(String[] argv) {

        TestResult result = new TestResult();
        TestCase   testA  = new TestSql("testMetaData");
        TestCase   testB  = new TestSql("testDoubleNaN");
        TestCase   testC  = new TestSql("testAny");

        testA.run(result);
        testB.run(result);
        testC.run(result);
        System.out.println("TestSql error count: " + result.failureCount());
    }
}