databasemetadatatest.java

第三方的SQL Server and Sybase的jdbc dirver,速度更快

        rsmd.getColumnCount();
        assertTrue(0 != rsmd.getColumnDisplaySize(1));
        assertNotNull(rsmd.getColumnLabel(1));
        assertNotNull(rsmd.getColumnName(1));
        rsmd.getColumnType(1);
        assertNotNull(rsmd.getColumnTypeName(1));
        rsmd.getPrecision(1);
        rsmd.getScale(1);
        assertNotNull(rsmd.getSchemaName(1));
        assertNotNull(rsmd.getTableName(1));
        rsmd.isAutoIncrement(1);
        rsmd.isCaseSensitive(1);
        rsmd.isCurrency(1);
        rsmd.isDefinitelyWritable(1);
        rsmd.isNullable(1);
        rsmd.isReadOnly(1);
        rsmd.isSearchable(1);
        rsmd.isSigned(1);
        rsmd.isWritable(1);

        rs.close();
    }

    /**
     * Test for bug [1023984] Protocol error processing table meta data.
     * <p>
     * Test to demonstrate failure to process the TDS table name token
     * correctly. Must be run with TDS=8.0.
     * @throws Exception
     */
    public void testTableMetaData() throws Exception {
        // This test is supposed to select from a different database, in order to
        // force the server to return a fully qualified table name. Do not alter.
        Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        ResultSet rs = stmt.executeQuery("SELECT * FROM master.dbo.sysdatabases");

        assertNotNull(rs);
        ResultSetMetaData rsmd = rs.getMetaData();

        assertEquals("master", rsmd.getCatalogName(1));
        assertEquals("dbo", rsmd.getSchemaName(1));
        assertEquals("sysdatabases", rsmd.getTableName(1));

        stmt.close();
        rs.close();
    }

    public void testColumnClassName() throws SQLException {
        byte[] bytes = new byte[] {1, 2, 3};
        String uid = "colGuid char(38)";
        if (con.getMetaData().getDatabaseProductName().startsWith("Microsoft")) {
            uid = "colGuid UNIQUEIDENTIFIER";
        }
        // Create a table w/ pretty much all the possible types
        String tabdef = "CREATE TABLE #testColumnClassName("
                + "colByte TINYINT,"
                + "colShort SMALLINT,"
                + "colInt INTEGER,"
                + "colBigint DECIMAL(29,0),"
                + "colFloat REAL,"
                + "colDouble FLOAT,"
                + "colDecimal DECIMAL(29,10),"
                + "colBit BIT,"
                + "colByteArray VARBINARY(255),"
                + "colTimestamp DATETIME,"
                + "colBlob IMAGE,"
                + "colClob TEXT,"
                + "colString VARCHAR(255),"
                + uid
                + ")";
        Statement stmt = con.createStatement();
        stmt.executeUpdate(tabdef);

        // Insert a row into the table
        PreparedStatement pstmt = con.prepareStatement(
                "INSERT INTO #testColumnClassName ("
                + "colByte,colShort,colInt,colBigint,colFloat,colDouble,"
                + "colDecimal,colBit,colByteArray,colTimestamp,colBlob,colClob,"
                + "colString,colGuid) "
                + "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
        pstmt.setByte(1, (byte) 1);
        pstmt.setShort(2, (short) 2222);
        pstmt.setInt(3, 123456);
        pstmt.setInt(4, 123456);
        pstmt.setFloat(5, 0.111f);
        pstmt.setDouble(6, 0.111);
        pstmt.setDouble(7, 0.111111);
        pstmt.setBoolean(8, true);
        pstmt.setBytes(9, bytes);
        pstmt.setTimestamp(10, new Timestamp(System.currentTimeMillis()));
        pstmt.setBytes(11, bytes);
        pstmt.setString(12, "Test");
        pstmt.setString(13, "Test");
        pstmt.setString(14, "ebd558a0-0c68-11d9-9669-0800200c9a66");
        assertEquals("No row inserted", 1, pstmt.executeUpdate());
        pstmt.close();

        // Select the row and check that getColumnClassName matches the actual
        // class
        ResultSet rs = stmt.executeQuery("SELECT * FROM #testColumnClassName");
        assertTrue("No rows in ResultSet", rs.next());
        ResultSetMetaData meta = rs.getMetaData();
        for (int i=1; i<=meta.getColumnCount(); i++) {
            Object obj = rs.getObject(i);
            assertNotNull("Expecting non-null value", obj);
            String metaClass = meta.getColumnClassName(i);
            Class c;
            try {
                c = Class.forName(metaClass);
            } catch (ClassNotFoundException ex) {
                fail("Class returned by getColumnClassName() not found: " + metaClass);
                return;
            }
            if (!c.isAssignableFrom(obj.getClass())) {
                fail("getColumnClassName() returned " + metaClass + " but the actual class is "
                        + obj.getClass().getName());
            }
        }
        stmt.close();
    }
    /**
     * Test to check DatabaseMetaData.getColumns and ResultSetMetaData is equivalent.
     * This test also checks for bug [ 1074096 ] Incorrect data type determine on dataset meta data.
     * This is because getColumns will return a typename of timestamp which should now also be
     * returned by the result set meta data as well.
     * @throws Exception if an error condition occurs
     */
    public void testColumnMetaData() throws Exception {
        String sql = "CREATE TABLE jTDSTYPETEST (ti tinyint not null, si smallint, i int, bi bigint, " +
        " f float, r real, d decimal(28,10), n numeric(28,10), sm smallmoney, m money, " +
        "c char(10) not null, vc varchar(255), nc nchar(10) not null, nvc nvarchar(255), " +
        " txt text, ntxt ntext, b binary(8) not null, vb varbinary(8), img image, " +
        " dt datetime, sdt smalldatetime, bt bit not null, ts timestamp, sn sysname, "+
        " ui uniqueidentifier, sv sql_variant)";

        String sql7 = "CREATE TABLE jTDSTYPETEST (ti tinyint not null, si smallint, i int, " +
        " f float, r real, d decimal(28,10), n numeric(28,10), sm smallmoney, m money, " +
        "c char(10) not null, vc varchar(255), nc nchar(10) not null, nvc nvarchar(255), " +
        " txt text, ntxt ntext, b binary(8) not null, vb varbinary(8), img image, " +
        " dt datetime, sdt smalldatetime, bt bit not null, ts timestamp, sn sysname, "+
        " ui uniqueidentifier)";

        String sql65 = "CREATE TABLE jTDSTYPETEST (ti tinyint not null, si smallint, i int, " +
        " f float, r real, d decimal(28,10), n numeric(28,10), sm smallmoney, m money, " +
        "c char(10) not null, vc varchar(255), " +
        " txt text, b binary(8) not null, vb varbinary(8), img image, " +
        " dt datetime, sdt smalldatetime, bt bit not null, ts timestamp, sn sysname)";

        String sql125 = "CREATE TABLE jTDSTYPETEST (ti tinyint not null, si smallint, i int, " +
        " f float, r real, d decimal(28,10), n numeric(28,10), sm smallmoney, m money, " +
        "c char(10) not null, vc varchar(255), nc nchar(10) not null, nvc nvarchar(255), " +
        " txt text, b binary(8) not null, vb varbinary(8), img image, " +
        " dt datetime, sdt smalldatetime, bt bit not null, ts timestamp, sn sysname, "+
        " uc unichar(10), vuc univarchar(255), sydt date, syt time)";

        try {
            dropTable("jTDSTYPETEST");
            Statement stmt = con.createStatement();
            DatabaseMetaData dbmd = con.getMetaData();
            if (dbmd.getDatabaseProductName().startsWith("Microsoft")) {
                if (dbmd.getDatabaseProductVersion().startsWith("6.5"))
                    stmt.execute(sql65);
                else if (dbmd.getDatabaseProductVersion().startsWith("7"))
                    stmt.execute(sql7);
                else
                    stmt.execute(sql);
            } else {
                if (dbmd.getDatabaseProductVersion().startsWith("12"))
                    stmt.execute(sql125);
                else
                    stmt.execute(sql65);
            }
            ResultSetMetaData rsmd = stmt.executeQuery("SELECT * FROM jTDSTYPETEST").getMetaData();
            ResultSet rs = dbmd.getColumns(null, null, "jTDSTYPETEST", "%");
//            ResultSetMetaData rsmd2 = rs.getMetaData();
//            System.out.println();
            while (rs.next()) {
                String cn = rs.getString("COLUMN_NAME");
                int ord = rs.getInt("ORDINAL_POSITION");
                assertEquals(cn+" typename", rs.getString("TYPE_NAME"), rsmd.getColumnTypeName(ord));
                assertEquals(cn+" datatype", rs.getInt("DATA_TYPE"), rsmd.getColumnType(ord));
                if (rs.getInt("DATA_TYPE") != Types.REAL && rs.getInt("DATA_TYPE") != Types.DOUBLE) {
                    // Seems to be genuine disagreement between getColumns and metadata on float data!
                    assertEquals(cn+" precision", rs.getInt("COLUMN_SIZE"), rsmd.getPrecision(ord));
                }
                assertEquals(cn+" scale", rs.getInt("DECIMAL_DIGITS"), rsmd.getScale(ord));
                assertEquals(cn+" nullable", rs.getInt("NULLABLE"), rsmd.isNullable(ord));
            }
        } finally {
            dropTable("jTDSTYPETEST");
        }
    }

    /**
     * Test for bug [1184376] Sybase getProcedureColumns bug
     */
    public void testProcedureColumns() throws Exception {
        try {
            dropProcedure("jtds_testparam");
            Statement stmt = con.createStatement();
            stmt.execute("CREATE PROC jtds_testparam @p1 int, @p2 int output as\r\n" +
                    "BEGIN\r\n" +
                    "  SELECT @p2 = @p1\r\n" +
                    "END");
            DatabaseMetaData dbmd = con.getMetaData();
            ResultSet rs = dbmd.getProcedureColumns(null, null, "jtds_testparam", "%");
            assertTrue(rs.next());
            assertEquals("@RETURN_VALUE", rs.getString("COLUMN_NAME"));
            assertEquals(DatabaseMetaData.procedureColumnReturn, rs.getInt("COLUMN_TYPE"));
            assertTrue(rs.next());
            assertEquals("@p1", rs.getString("COLUMN_NAME"));
            assertEquals(DatabaseMetaData.procedureColumnIn, rs.getInt("COLUMN_TYPE"));
            assertTrue(rs.next());
            assertEquals("@p2", rs.getString("COLUMN_NAME"));
            assertEquals(DatabaseMetaData.procedureColumnInOut, rs.getInt("COLUMN_TYPE"));
            stmt.close();
        } finally {
            dropProcedure("jtds_testparam");
        }
    }

    public static void main(String[] args) {
        junit.textui.TestRunner.run(DatabaseMetaDataTest.class);
    }
}