testresultset.java

非常棒的java数据库

        rs.next();
        check(rs.getDate("Value") == null && rs.wasNull());
        check(rs.getTime("vALUe") == null && rs.wasNull());
        check(rs.getTimestamp(2) == null && rs.wasNull());
        check(!rs.next());

        rs = stat
                .executeQuery("SELECT DATE '2001-02-03' D, TIME '14:15:16', TIMESTAMP '2007-08-09 10:11:12.141516171' TS FROM TEST");
        rs.next();
        date = (Date) rs.getObject(1);
        time = (Time) rs.getObject(2);
        ts = (Timestamp) rs.getObject(3);
        check(date.toString(), "2001-02-03");
        check(time.toString(), "14:15:16");
        check(ts.toString(), "2007-08-09 10:11:12.141516171");

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

    void testDatetimeWithCalendar() throws Exception {
        trace("Test DATETIME with Calendar");
        ResultSet rs;

        stat.execute("CREATE TABLE TEST(ID INT PRIMARY KEY, D DATE, T TIME, TS TIMESTAMP)");
        PreparedStatement prep = conn.prepareStatement("INSERT INTO TEST VALUES(?, ?, ?, ?)");
        Calendar regular = Calendar.getInstance();
        Calendar other = null;
        String[] timezones = TimeZone.getAvailableIDs();
        // search a locale that has a _different_ raw offset
        for (int i = 0; i < timezones.length; i++) {
            TimeZone zone = TimeZone.getTimeZone(timezones[i]);
            if (regular.getTimeZone().getRawOffset() != zone.getRawOffset()) {
                other = Calendar.getInstance(zone);
                break;
            }
        }
        trace("regular offset = " + regular.getTimeZone().getRawOffset() + " other = "
                + other.getTimeZone().getRawOffset());

        prep.setInt(1, 0);
        prep.setDate(2, null, regular);
        prep.setTime(3, null, regular);
        prep.setTimestamp(4, null, regular);
        prep.execute();

        prep.setInt(1, 1);
        prep.setDate(2, null, other);
        prep.setTime(3, null, other);
        prep.setTimestamp(4, null, other);
        prep.execute();

        prep.setInt(1, 2);
        prep.setDate(2, java.sql.Date.valueOf("2001-02-03"), regular);
        prep.setTime(3, java.sql.Time.valueOf("04:05:06"), regular);
        prep.setTimestamp(4, java.sql.Timestamp.valueOf("2007-08-09 10:11:12.131415"), regular);
        prep.execute();

        prep.setInt(1, 3);
        prep.setDate(2, java.sql.Date.valueOf("2101-02-03"), other);
        prep.setTime(3, java.sql.Time.valueOf("14:05:06"), other);
        prep.setTimestamp(4, java.sql.Timestamp.valueOf("2107-08-09 10:11:12.131415"), other);
        prep.execute();

        prep.setInt(1, 4);
        prep.setDate(2, java.sql.Date.valueOf("2101-02-03"));
        prep.setTime(3, java.sql.Time.valueOf("14:05:06"));
        prep.setTimestamp(4, java.sql.Timestamp.valueOf("2107-08-09 10:11:12.131415"));
        prep.execute();

        rs = stat.executeQuery("SELECT * FROM TEST ORDER BY ID");
        testResultSetMeta(rs, 4, new String[] { "ID", "D", "T", "TS" }, new int[] { Types.INTEGER, Types.DATE,
                Types.TIME, Types.TIMESTAMP }, new int[] { 10, 8, 6, 23 }, new int[] { 0, 0, 0, 10 });

        rs.next();
        check(rs.getInt(1), 0);
        check(rs.getDate(2, regular) == null && rs.wasNull());
        check(rs.getTime(3, regular) == null && rs.wasNull());
        check(rs.getTimestamp(3, regular) == null && rs.wasNull());

        rs.next();
        check(rs.getInt(1), 1);
        check(rs.getDate(2, other) == null && rs.wasNull());
        check(rs.getTime(3, other) == null && rs.wasNull());
        check(rs.getTimestamp(3, other) == null && rs.wasNull());

        rs.next();
        check(rs.getInt(1), 2);
        check(rs.getDate(2, regular).toString(), "2001-02-03");
        check(rs.getTime(3, regular).toString(), "04:05:06");
        checkFalse(rs.getTime(3, other).toString(), "04:05:06");
        check(rs.getTimestamp(4, regular).toString(), "2007-08-09 10:11:12.131415");
        checkFalse(rs.getTimestamp(4, other).toString(), "2007-08-09 10:11:12.131415");

        rs.next();
        check(rs.getInt("ID"), 3);
        checkFalse(rs.getTimestamp("TS", regular).toString(), "2107-08-09 10:11:12.131415");
        check(rs.getTimestamp("TS", other).toString(), "2107-08-09 10:11:12.131415");
        checkFalse(rs.getTime("T", regular).toString(), "14:05:06");
        check(rs.getTime("T", other).toString(), "14:05:06");
        // checkFalse(rs.getDate(2, regular).toString(), "2101-02-03");
        // check(rs.getDate("D", other).toString(), "2101-02-03");

        rs.next();
        check(rs.getInt("ID"), 4);
        check(rs.getTimestamp("TS").toString(), "2107-08-09 10:11:12.131415");
        check(rs.getTime("T").toString(), "14:05:06");
        check(rs.getDate("D").toString(), "2101-02-03");

        checkFalse(rs.next());
        stat.execute("DROP TABLE TEST");
    }

    void testBlob() throws Exception {
        trace("Test BLOB");
        ResultSet rs;

        stat.execute("CREATE TABLE TEST(ID INT PRIMARY KEY,VALUE BLOB)");
        stat.execute("INSERT INTO TEST VALUES(1,X'01010101')");
        stat.execute("INSERT INTO TEST VALUES(2,X'02020202')");
        stat.execute("INSERT INTO TEST VALUES(3,X'00')");
        stat.execute("INSERT INTO TEST VALUES(4,X'ffffff')");
        stat.execute("INSERT INTO TEST VALUES(5,X'0bcec1')");
        stat.execute("INSERT INTO TEST VALUES(6,NULL)");
        rs = stat.executeQuery("SELECT * FROM TEST ORDER BY ID");
        testResultSetMeta(rs, 2, new String[] { "ID", "VALUE" }, new int[] { Types.INTEGER, Types.BLOB }, new int[] {
                10, Integer.MAX_VALUE }, new int[] { 0, 0 });
        rs.next();
        checkBytes(rs.getBytes(2), new byte[] { (byte) 0x01, (byte) 0x01, (byte) 0x01, (byte) 0x01 });
        check(!rs.wasNull());
        rs.next();
        checkBytes(rs.getBytes("value"), new byte[] { (byte) 0x02, (byte) 0x02, (byte) 0x02, (byte) 0x02 });
        check(!rs.wasNull());
        rs.next();
        checkBytes(readAllBytes(rs.getBinaryStream(2)), new byte[] { (byte) 0x00 });
        check(!rs.wasNull());
        rs.next();
        checkBytes(readAllBytes(rs.getBinaryStream("VaLuE")), new byte[] { (byte) 0xff, (byte) 0xff, (byte) 0xff });
        check(!rs.wasNull());
        rs.next();
        InputStream in = rs.getBinaryStream("value");
        byte[] b = readAllBytes(in);
        checkBytes(b, new byte[] { (byte) 0x0b, (byte) 0xce, (byte) 0xc1 });
        check(!rs.wasNull());
        rs.next();
        checkBytes(readAllBytes(rs.getBinaryStream("VaLuE")), null);
        check(rs.wasNull());
        check(!rs.next());
        stat.execute("DROP TABLE TEST");
    }

    void testClob() throws Exception {
        trace("Test CLOB");
        ResultSet rs;
        String string;
        stat.execute("CREATE TABLE TEST(ID INT PRIMARY KEY,VALUE CLOB)");
        stat.execute("INSERT INTO TEST VALUES(1,'Test')");
        stat.execute("INSERT INTO TEST VALUES(2,'Hello')");
        stat.execute("INSERT INTO TEST VALUES(3,'World!')");
        stat.execute("INSERT INTO TEST VALUES(4,'Hallo')");
        stat.execute("INSERT INTO TEST VALUES(5,'Welt!')");
        stat.execute("INSERT INTO TEST VALUES(6,NULL)");
        stat.execute("INSERT INTO TEST VALUES(7,NULL)");
        rs = stat.executeQuery("SELECT * FROM TEST ORDER BY ID");
        testResultSetMeta(rs, 2, new String[] { "ID", "VALUE" }, new int[] { Types.INTEGER, Types.CLOB }, new int[] {
                10, Integer.MAX_VALUE }, new int[] { 0, 0 });
        rs.next();
        string = rs.getString(2);
        check(string != null && string.equals("Test"));
        check(!rs.wasNull());
        rs.next();
        InputStreamReader reader = null;
        try {
            reader = new InputStreamReader(rs.getAsciiStream(2), "ISO-8859-1");
        } catch (Exception e) {
            check(false);
        }
        string = readString(reader);
        check(!rs.wasNull());
        trace(string);
        check(string != null && string.equals("Hello"));
        rs.next();
        try {
            reader = new InputStreamReader(rs.getAsciiStream("value"), "ISO-8859-1");
        } catch (Exception e) {
            check(false);
        }
        string = readString(reader);
        check(!rs.wasNull());
        trace(string);
        check(string != null && string.equals("World!"));
        rs.next();
        string = readString(rs.getCharacterStream(2));
        check(!rs.wasNull());
        trace(string);
        check(string != null && string.equals("Hallo"));
        rs.next();
        string = readString(rs.getCharacterStream("value"));
        check(!rs.wasNull());
        trace(string);
        check(string != null && string.equals("Welt!"));
        rs.next();
        check(rs.getCharacterStream(2) == null);
        check(rs.wasNull());
        rs.next();
        check(rs.getAsciiStream("Value") == null);
        check(rs.wasNull());

        check(rs.getStatement() == stat);
        check(rs.getWarnings() == null);
        rs.clearWarnings();
        check(rs.getWarnings() == null);
        check(rs.getFetchDirection(), ResultSet.FETCH_FORWARD);
        check(rs.getConcurrency(), ResultSet.CONCUR_UPDATABLE);
        rs.next();
        stat.execute("DROP TABLE TEST");
    }

    void testArray() throws Exception {
        trace("Test ARRAY");
        ResultSet rs;
        stat.execute("CREATE TABLE TEST(ID INT PRIMARY KEY, VALUE ARRAY)");
        PreparedStatement prep = conn.prepareStatement("INSERT INTO TEST VALUES(?, ?)");
        prep.setInt(1, 1);
        prep.setObject(2, new Object[] { new Integer(1), new Integer(2) });
        prep.execute();
        prep.setInt(1, 2);
        prep.setObject(2, new Object[] { new Integer(11), new Integer(12) });
        prep.execute();
        rs = stat.executeQuery("SELECT * FROM TEST ORDER BY ID");
        rs.next();
        check(rs.getInt(1), 1);
        Object[] list = (Object[]) rs.getObject(2);
        check(((Integer) list[0]).intValue(), 1);
        check(((Integer) list[1]).intValue(), 2);
        Array array = rs.getArray(2);
        Object[] list2 = (Object[]) array.getArray();
        check(((Integer) list2[0]).intValue(), 1);
        check(((Integer) list2[1]).intValue(), 2);
        list2 = (Object[]) array.getArray(2, 1);
        check(((Integer) list2[0]).intValue(), 2);
        rs.next();
        check(rs.getInt(1), 2);
        list = (Object[]) rs.getObject(2);
        check(((Integer) list[0]).intValue(), 11);
        check(((Integer) list[1]).intValue(), 12);
        array = rs.getArray(2);
        list2 = (Object[]) array.getArray();
        check(((Integer) list2[0]).intValue(), 11);
        check(((Integer) list2[1]).intValue(), 12);
        list2 = (Object[]) array.getArray(2, 1);
        check(((Integer) list2[0]).intValue(), 12);
        checkFalse(rs.next());
        stat.execute("DROP TABLE TEST");
    }

    byte[] readAllBytes(InputStream in) throws Exception {
        if (in == null) {
            return null;
        }
        ByteArrayOutputStream out = new ByteArrayOutputStream();
        try {
            while (true) {
                int b = in.read();
                if (b == -1) {
                    break;
                }
                out.write(b);
            }
            return out.toByteArray();
        } catch (IOException e) {
            check(false);
            return null;
        }
    }

    void checkBytes(byte[] test, byte[] good) throws Exception {
        if (test == null || good == null) {
            check(test == null && good == null);
        } else {
            trace("test.length=" + test.length + " good.length=" + good.length);
            check(test.length, good.length);
            for (int i = 0; i < good.length; i++) {
                check(test[i] == good[i]);
            }
        }
    }

    void checkColumnBigDecimal(ResultSet rs, int column, int i, String bd) throws Exception {
        BigDecimal bd1 = rs.getBigDecimal(column);
        int i1 = rs.getInt(column);
        if (bd == null) {
            trace("should be: null");
            check(rs.wasNull());
        } else {
            trace("BigDecimal i=" + i + " bd=" + bd + " ; i1=" + i1 + " bd1=" + bd1);
            check(!rs.wasNull());
            check(i1 == i);
            check(bd1.compareTo(new BigDecimal(bd)) == 0);
        }
    }

}