testsqlpersistent.java

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

            arrayValueResult = rs.getObject(3);

            // how to check if the last retrieved value was null
            wasNull = rs.wasNull();

            // cast objects to original types - will throw if type is wrong
            String   castStringValue      = (String) stringValueResult;
            Integer  castIntegerValue     = (Integer) integerValueResult;
            Double[] castDoubleArrayValue = (Double[]) arrayValueResult;

            {
                sqlString = "DELETE FROM PREFERENCE WHERE user_id = ?";

                PreparedStatement st =
                    cConnection.prepareStatement(sqlString);

                st.setString(1, "2");

                int ret = st.executeUpdate();

                // here, ret is equal to 1, that is expected
                //conn.commit(); // not needed, as far as AUTO_COMMIT is set to TRUE
                st.close();

                st = cConnection.prepareStatement(
                    "SELECT user_id FROM PREFERENCE WHERE user_id=?");

                st.setString(1, "2");

                rs = st.executeQuery();

                while (rs.next()) {
                    System.out.println(rs.getString(1));
                }
            }
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        } catch (IOException e1) {}

        /*
        boolean success = stringValue.equals(stringValueResult)
                          && integerValue.equals(integerValueResult)
                          && java.util.Arrays.equals((Double[]) arrayValue,
                              (Double[]) arrayValueResult);
        */
        boolean success = true;

        assertEquals(true, success);
    }

    public void testSelectObject() throws IOException {

        String   stringValue        = null;
        Integer  integerValue       = null;
        Double[] arrayValue         = null;
        byte[]   byteArrayValue     = null;
        String   stringValueResult  = null;
        Integer  integerValueResult = null;
        Double[] arrayValueResult   = null;
        boolean  wasNull            = false;
        String   message            = "DB operation completed";

        try {
            String sqlString = "DROP TABLE TESTOBJECT IF EXISTS;"
                               + "CREATE CACHED TABLE TESTOBJECT ("
                               + "ID INTEGER NOT NULL IDENTITY, "
                               + "STOREDOBJECT OTHER, STOREDBIN BINARY )";

            sStatement.execute(sqlString);

            sqlString = "INSERT INTO TESTOBJECT "
                        + "(STOREDOBJECT, STOREDBIN) " + "VALUES (?,?)";

            PreparedStatement ps = cConnection.prepareStatement(sqlString);

            // initialise
            stringValue  = "Test String Value";
            integerValue = new Integer(1000);
            arrayValue   = new Double[] {
                new Double(1), new Double(Double.NaN),
                new Double(Double.NEGATIVE_INFINITY),
                new Double(Double.POSITIVE_INFINITY)
            };
            byteArrayValue = new byte[] {
                1, 2, 3
            };

            // String as Object
// fredt - in order to store Strings in OBJECT columns setObject should
// explicitly be called with a Types.OTHER type
            ps.setObject(1, stringValue, Types.OTHER);
            ps.setBytes(2, byteArrayValue);
            ps.execute();

            // Integer as Object
            ps.setObject(1, integerValue, Types.OTHER);
            ps.setBinaryStream(2, new ByteArrayInputStream(byteArrayValue),
                               byteArrayValue.length);
            ps.execute();

            // Array as object
            ps.setObject(1, arrayValue, Types.OTHER);

            // file as binary - works fine but file path and name has to be modified for test environment
            /*
            int length = (int) new File("c://ft/db.jar").length();
            FileInputStream fis = new FileInputStream("c://ft/db.jar");
            ps.setBinaryStream(2,fis,length);
            */
            ps.execute();

            ResultSet rs =
                sStatement.executeQuery("SELECT * FROM TESTOBJECT");
            boolean result = rs.next();

            // retrieving objects inserted into the third column
            stringValueResult = (String) rs.getObject(2);

            rs.next();

            integerValueResult = (Integer) rs.getObject(2);

            rs.next();

            arrayValueResult = (Double[]) rs.getObject(2);

            // cast objects to original types - will throw if type is wrong
            String   castStringValue      = (String) stringValueResult;
            Integer  castIntegerValue     = (Integer) integerValueResult;
            Double[] castDoubleArrayValue = (Double[]) arrayValueResult;

            for (int i = 0; i < arrayValue.length; i++) {
                if (!arrayValue[i].equals(arrayValueResult[i])) {
                    System.out.println("array mismatch: " + arrayValue[i]
                                       + " : " + arrayValueResult[i]);
                }
            }

            rs.close();
            ps.close();

            sqlString = "SELECT * FROM TESTOBJECT WHERE STOREDOBJECT = ?";
            ps        = cConnection.prepareStatement(sqlString);

            ps.setObject(1, new Integer(1000));

            rs = ps.executeQuery();

            rs.next();

            Object returnVal = rs.getObject(2);

            rs.next();
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }

        boolean success = stringValue.equals(stringValueResult)
                          && integerValue.equals(integerValueResult)
                          && java.util.Arrays.equals((Double[]) arrayValue,
                              (Double[]) arrayValueResult);

        assertEquals(true, success);

        try {
            String            sqlString = "drop table objects if exists";
            PreparedStatement ps = cConnection.prepareStatement(sqlString);

            ps.execute();

            sqlString =
                "create cached table objects (object_id INTEGER IDENTITY,"
                + "object_name VARCHAR(128) NOT NULL,role_name VARCHAR(128) NOT NULL,"
                + "value LONGVARBINARY NOT NULL,description LONGVARCHAR)";
            ps = cConnection.prepareStatement(sqlString);

            ps.execute();

            sqlString =
                "INSERT INTO objects VALUES(1, 'name','role',?,'description')";
            ps = cConnection.prepareStatement(sqlString);

            ps.setBytes(1, new byte[] {
                1, 2, 3, 4, 5
            });
            ps.executeUpdate();

            sqlString =
                "UPDATE objects SET value = ? AND description = ? WHERE "
                + "object_name = ? AND role_name = ?";
            ps = cConnection.prepareStatement(sqlString);

            ps.setBytes(1, new byte[] {
                1, 2, 3, 4, 5
            });
            ps.setString(2, "desc");
            ps.setString(3, "new");
            ps.setString(4, "role");
            ps.executeUpdate();
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
    }

    protected void tearDown() {

        try {
            cConnection.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   testC  = new TestSqlPersistent("testInsertObject");
        TestCase   testD  = new TestSqlPersistent("testSelectObject");

        testC.run(result);
        testD.run(result);
        System.out.println("TestSqlPersistent error count: "
                           + result.failureCount());
    }
}