testresultset.java

非常棒的java数据库

/*
 * 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.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.math.BigDecimal;
import java.sql.Array;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Time;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.Calendar;
import java.util.TimeZone;

import org.h2.test.TestBase;

/**
 * Tests for the ResultSet implementation.
 */
public class TestResultSet extends TestBase {
    Connection conn;
    Statement stat;

    public void test() throws Exception {
        deleteDb("resultSet");
        conn = getConnection("resultSet");

        stat = conn.createStatement();

        testFindColumn();

        testColumnLength();
        testArray();
        testLimitMaxRows();

        trace("max rows=" + stat.getMaxRows());
        stat.setMaxRows(6);
        trace("max rows after set to 6=" + stat.getMaxRows());
        check(stat.getMaxRows() == 6);

        testInt();
        testVarchar();
        testDecimal();
        testDoubleFloat();
        testDatetime();
        testDatetimeWithCalendar();
        testBlob();
        testClob();
        testAutoIncrement();

        conn.close();

    }

    private void testFindColumn() throws Exception {
        trace("testFindColumn");
        ResultSet rs;
        stat.execute("CREATE TABLE TEST(ID INT, NAME VARCHAR)");
        rs = stat.executeQuery("SELECT * FROM TEST");
        check(rs.findColumn("ID"), 1);
        check(rs.findColumn("NAME"), 2);
        check(rs.findColumn("id"), 1);
        check(rs.findColumn("name"), 2);
        check(rs.findColumn("Id"), 1);
        check(rs.findColumn("Name"), 2);
        check(rs.findColumn("TEST.ID"), 1);
        check(rs.findColumn("TEST.NAME"), 2);
        check(rs.findColumn("Test.Id"), 1);
        check(rs.findColumn("Test.Name"), 2);
        stat.execute("DROP TABLE TEST");

        stat.execute("CREATE TABLE TEST(ID INT, NAME VARCHAR, DATA VARCHAR)");
        rs = stat.executeQuery("SELECT * FROM TEST");
        check(rs.findColumn("ID"), 1);
        check(rs.findColumn("NAME"), 2);
        check(rs.findColumn("DATA"), 3);
        check(rs.findColumn("id"), 1);
        check(rs.findColumn("name"), 2);
        check(rs.findColumn("data"), 3);
        check(rs.findColumn("Id"), 1);
        check(rs.findColumn("Name"), 2);
        check(rs.findColumn("Data"), 3);
        check(rs.findColumn("TEST.ID"), 1);
        check(rs.findColumn("TEST.NAME"), 2);
        check(rs.findColumn("TEST.DATA"), 3);
        check(rs.findColumn("Test.Id"), 1);
        check(rs.findColumn("Test.Name"), 2);
        check(rs.findColumn("Test.Data"), 3);
        stat.execute("DROP TABLE TEST");

    }

    private void testColumnLength() throws Exception {
        trace("testColumnDisplayLength");
        ResultSet rs;
        ResultSetMetaData meta;

        stat.execute("CREATE TABLE one (ID INT, NAME VARCHAR(255))");
        rs = stat.executeQuery("select * from one");
        meta = rs.getMetaData();
        check("ID", meta.getColumnLabel(1));
        check(11, meta.getColumnDisplaySize(1));
        check("NAME", meta.getColumnLabel(2));
        check(255, meta.getColumnDisplaySize(2));
        stat.execute("DROP TABLE one");

        rs = stat.executeQuery("select 1, 'Hello' union select 2, 'Hello World!'");
        meta = rs.getMetaData();
        check(11, meta.getColumnDisplaySize(1));
        check(12, meta.getColumnDisplaySize(2));

        rs = stat.executeQuery("explain select * from dual");
        meta = rs.getMetaData();
        check(Integer.MAX_VALUE, meta.getColumnDisplaySize(1));
        check(Integer.MAX_VALUE, meta.getPrecision(1));

        rs = stat.executeQuery("script");
        meta = rs.getMetaData();
        check(Integer.MAX_VALUE, meta.getColumnDisplaySize(1));
        check(Integer.MAX_VALUE, meta.getPrecision(1));

        rs = stat.executeQuery("select group_concat(table_name) from information_schema.tables");
        rs.next();
        meta = rs.getMetaData();
        check(Integer.MAX_VALUE, meta.getColumnDisplaySize(1));
        check(Integer.MAX_VALUE, meta.getPrecision(1));

    }

    private void testLimitMaxRows() throws Exception {
        trace("Test LimitMaxRows");
        ResultSet rs;
        stat.execute("CREATE TABLE one (C CHARACTER(10))");
        rs = stat.executeQuery("SELECT C || C FROM one;");
        ResultSetMetaData md = rs.getMetaData();
        check(20, md.getPrecision(1));
        ResultSet rs2 = stat.executeQuery("SELECT UPPER (C)  FROM one;");
        ResultSetMetaData md2 = rs2.getMetaData();
        check(10, md2.getPrecision(1));
        rs = stat.executeQuery("SELECT UPPER (C), CHAR(10), CONCAT(C,C,C), HEXTORAW(C), RAWTOHEX(C) FROM one");
        ResultSetMetaData meta = rs.getMetaData();
        check(10, meta.getPrecision(1));
        check(1, meta.getPrecision(2));
        check(30, meta.getPrecision(3));
        check(3, meta.getPrecision(4));
        check(40, meta.getPrecision(5));
        stat.execute("DROP TABLE one");
    }

    void testAutoIncrement() throws Exception {
        trace("Test AutoIncrement");
        stat.execute("DROP TABLE IF EXISTS TEST");
        ResultSet rs;
        stat.execute("CREATE TABLE TEST(ID IDENTITY NOT NULL, NAME VARCHAR NULL)");

        stat.execute("INSERT INTO TEST(NAME) VALUES('Hello')");
        rs = stat.getGeneratedKeys();
        check(rs.next());
        check(rs.getInt(1), 1);

        stat.execute("INSERT INTO TEST(NAME) VALUES('World')");
        rs = stat.getGeneratedKeys();
        check(rs.next());
        check(rs.getInt(1), 2);

        rs = stat.executeQuery("SELECT ID AS I, NAME AS N, ID+1 AS IP1 FROM TEST");
        ResultSetMetaData meta = rs.getMetaData();
        check(meta.isAutoIncrement(1));
        checkFalse(meta.isAutoIncrement(2));
        checkFalse(meta.isAutoIncrement(3));
        check(meta.isNullable(1), ResultSetMetaData.columnNoNulls);
        check(meta.isNullable(2), ResultSetMetaData.columnNullable);
        check(meta.isNullable(3), ResultSetMetaData.columnNullableUnknown);
        check(rs.next());
        check(rs.next());
        checkFalse(rs.next());

    }

    void testInt() throws Exception {
        trace("Test INT");
        ResultSet rs;
        Object o;

        stat.execute("CREATE TABLE TEST(ID INT PRIMARY KEY,VALUE INT)");
        stat.execute("INSERT INTO TEST VALUES(1,-1)");
        stat.execute("INSERT INTO TEST VALUES(2,0)");
        stat.execute("INSERT INTO TEST VALUES(3,1)");
        stat.execute("INSERT INTO TEST VALUES(4," + Integer.MAX_VALUE + ")");
        stat.execute("INSERT INTO TEST VALUES(5," + Integer.MIN_VALUE + ")");
        stat.execute("INSERT INTO TEST VALUES(6,NULL)");
        // this should not be read - maxrows=6
        stat.execute("INSERT INTO TEST VALUES(7,NULL)");

        // MySQL compatibility (is this required?)
        // rs=stat.executeQuery("SELECT * FROM TEST T ORDER BY ID");
        // check(rs.findColumn("T.ID"), 1);
        // check(rs.findColumn("T.NAME"), 2);

        rs = stat.executeQuery("SELECT *, NULL AS N FROM TEST ORDER BY ID");

        // MySQL compatibility
        check(rs.findColumn("TEST.ID"), 1);
        check(rs.findColumn("TEST.VALUE"), 2);

        ResultSetMetaData meta = rs.getMetaData();
        check(meta.getColumnCount(), 3);
        check(meta.getCatalogName(1), "resultSet".toUpperCase());
        check("PUBLIC".equals(meta.getSchemaName(2)));
        check("TEST".equals(meta.getTableName(1)));
        check("ID".equals(meta.getColumnName(1)));
        check("VALUE".equals(meta.getColumnName(2)));
        check(!meta.isAutoIncrement(1));
        check(meta.isCaseSensitive(1));
        check(meta.isSearchable(1));
        checkFalse(meta.isCurrency(1));
        check(meta.getColumnDisplaySize(1) > 0);
        check(meta.isSigned(1));
        check(meta.isSearchable(2));
        check(meta.isNullable(1), ResultSetMetaData.columnNoNulls);
        checkFalse(meta.isReadOnly(1));
        check(meta.isWritable(1));
        checkFalse(meta.isDefinitelyWritable(1));
        check(meta.getColumnDisplaySize(1) > 0);
        check(meta.getColumnDisplaySize(2) > 0);
        check(meta.getColumnClassName(3), null);

        check(rs.getRow() == 0);
        testResultSetMeta(rs, 3, new String[] { "ID", "VALUE", "N" }, new int[] { Types.INTEGER, Types.INTEGER,
                Types.NULL }, new int[] { 10, 10, 1 }, new int[] { 0, 0, 0 });
        rs.next();
        check(rs.getConcurrency(), ResultSet.CONCUR_READ_ONLY);
        check(rs.getFetchDirection(), ResultSet.FETCH_FORWARD);
        trace("default fetch size=" + rs.getFetchSize());
        // 0 should be an allowed value (but it's not defined what is actually
        // means)
        rs.setFetchSize(0);
        trace("after set to 0, fetch size=" + rs.getFetchSize());
        // this should break
        try {
            rs.setFetchSize(-1);
            error("fetch size -1 is not allowed");
        } catch (SQLException e) {
            checkNotGeneralException(e);
            trace(e.toString());
        }
        trace("after try to set to -1, fetch size=" + rs.getFetchSize());
        try {
            rs.setFetchSize(100);
            error("fetch size 100 is bigger than maxrows - not allowed");
        } catch (SQLException e) {
            checkNotGeneralException(e);
            trace(e.toString());
        }
        trace("after try set to 100, fetch size=" + rs.getFetchSize());
        rs.setFetchSize(6);

        check(rs.getRow() == 1);
        check(rs.findColumn("VALUE"), 2);
        check(rs.findColumn("value"), 2);
        check(rs.findColumn("Value"), 2);
        check(rs.findColumn("Value"), 2);
        check(rs.findColumn("ID"), 1);
        check(rs.findColumn("id"), 1);
        check(rs.findColumn("Id"), 1);
        check(rs.findColumn("iD"), 1);
        check(rs.getInt(2) == -1 && !rs.wasNull());
        check(rs.getInt("VALUE") == -1 && !rs.wasNull());
        check(rs.getInt("value") == -1 && !rs.wasNull());
        check(rs.getInt("Value") == -1 && !rs.wasNull());
        check(rs.getString("Value").equals("-1") && !rs.wasNull());

        o = rs.getObject("value");
        trace(o.getClass().getName());
        check(o instanceof Integer);
        check(((Integer) o).intValue() == -1);
        o = rs.getObject(2);
        trace(o.getClass().getName());
        check(o instanceof Integer);
        check(((Integer) o).intValue() == -1);
        check(rs.getBoolean("Value"));
        check(rs.getByte("Value") == (byte) -1);
        check(rs.getShort("Value") == (short) -1);
        check(rs.getLong("Value") == -1);
        check(rs.getFloat("Value") == -1.0);
        check(rs.getDouble("Value") == -1.0);

        check(rs.getString("Value").equals("-1") && !rs.wasNull());
        check(rs.getInt("ID") == 1 && !rs.wasNull());
        check(rs.getInt("id") == 1 && !rs.wasNull());
        check(rs.getInt("Id") == 1 && !rs.wasNull());
        check(rs.getInt(1) == 1 && !rs.wasNull());
        rs.next();