stringregressiontest.java

MySql Java Connector

            rs = stmt.executeQuery("SELECT * FROM latin1RegressTest");

            rs.next();

            String retrievedString = rs.getString(1);

            System.out.println(latin1String);
            System.out.println(retrievedString);

            if (!retrievedString.equals(latin1String)) {
                int stringLength = Math.min(retrievedString.length(),
                        latin1String.length());

                for (int i = 0; i < stringLength; i++) {
                    char rChar = retrievedString.charAt(i);
                    char origChar = latin1String.charAt(i);

                    if ((rChar != '?') && (rChar != origChar)) {
                        fail("characters differ at position " + i + "'" + rChar
                            + "' retrieved from database, original char was '"
                            + origChar + "'");
                    }
                }
            }
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (Exception ex) {
                    // ignore
                }
            }

            if (pStmt != null) {
                try {
                    pStmt.close();
                } catch (Exception ex) {
                    // ignore
                }
            }

            stmt.executeUpdate("DROP TABLE IF EXISTS latin1RegressTest");
        }
    }

    /**
     * Tests newline being treated correctly.
     *
     * @throws Exception if an error occurs
     */
    public void testNewlines() throws Exception {
        String newlineStr = "Foo\nBar\n\rBaz";

        stmt.executeUpdate("DROP TABLE IF EXISTS newlineRegressTest");
        stmt.executeUpdate(
            "CREATE TABLE newlineRegressTest (field1 MEDIUMTEXT)");

        try {
            stmt.executeUpdate("INSERT INTO newlineRegressTest VALUES ('"
                + newlineStr + "')");
            pstmt = conn.prepareStatement(
                    "INSERT INTO newlineRegressTest VALUES (?)");
            pstmt.setString(1, newlineStr);
            pstmt.executeUpdate();

            rs = stmt.executeQuery("SELECT * FROM newlineRegressTest");

            while (rs.next()) {
                assertTrue(rs.getString(1).equals(newlineStr));
            }
        } finally {
            stmt.executeUpdate("DROP TABLE IF EXISTS newlineRegressTest");
        }
    }

    /**
     * Tests that single-byte character conversion works correctly.
     *
     * @throws Exception if any errors occur
     */
    public void testSingleByteConversion() throws Exception {
        testConversionForString("latin1", "��� ����");
        testConversionForString("latin1", "Kaarle ��nis Ilmari");
        testConversionForString("latin1", "������������������");
    }

    /**
     * Tests that the 0x5c escaping works (we didn't use to have this).
     *
     * @throws Exception if an error occurs.
     */
    public void testSjis5c() throws Exception {
        byte[] origByteStream = new byte[] {
                (byte) 0x95, (byte) 0x5c, (byte) 0x8e, (byte) 0x96
            };

        //
        // Print the hex values of the string
        //
        StringBuffer bytesOut = new StringBuffer();

        for (int i = 0; i < origByteStream.length; i++) {
            bytesOut.append(Integer.toHexString(origByteStream[i] & 255));
            bytesOut.append(" ");
        }

        System.out.println(bytesOut.toString());

        String origString = new String(origByteStream, "SJIS");
        byte[] newByteStream = StringUtils.getBytes(origString, "SJIS", "SJIS",
                false);

        //
        // Print the hex values of the string (should have an extra 0x5c)
        //
        bytesOut = new StringBuffer();

        for (int i = 0; i < newByteStream.length; i++) {
            bytesOut.append(Integer.toHexString(newByteStream[i] & 255));
            bytesOut.append(" ");
        }

        System.out.println(bytesOut.toString());

        //
        // Now, insert and retrieve the value from the database
        //
        Connection sjisConn = null;
        Statement sjisStmt = null;

        try {
            Properties props = new Properties();
            props.put("useUnicode", "true");
            props.put("characterEncoding", "SJIS");
            sjisConn = getConnectionWithProps(props);

            sjisStmt = sjisConn.createStatement();

            this.rs = sjisStmt.executeQuery(
                    "SHOW VARIABLES LIKE 'character_set%'");

            while (rs.next()) {
                System.out.println(rs.getString(1) + " = " + rs.getString(2));
            }

            sjisStmt.executeUpdate("DROP TABLE IF EXISTS sjisTest");

            if (versionMeetsMinimum(4, 1)) {
                sjisStmt.executeUpdate(
                    "CREATE TABLE sjisTest (field1 char(50)) DEFAULT CHARACTER SET SJIS");
            } else {
                sjisStmt.executeUpdate(
                    "CREATE TABLE sjisTest (field1 char(50))");
            }

            pstmt = sjisConn.prepareStatement("INSERT INTO sjisTest VALUES (?)");
            pstmt.setString(1, origString);
            pstmt.executeUpdate();

            rs = sjisStmt.executeQuery("SELECT * FROM sjisTest");

            while (rs.next()) {
                byte[] testValueAsBytes = rs.getBytes(1);

                bytesOut = new StringBuffer();

                for (int i = 0; i < testValueAsBytes.length; i++) {
                    bytesOut.append(Integer.toHexString(testValueAsBytes[i]
                            & 255));
                    bytesOut.append(" ");
                }

                System.out.println("Value retrieved from database: "
                    + bytesOut.toString());

                String testValue = rs.getString(1);

                assertTrue(testValue.equals(origString));
            }
        } finally {
            stmt.executeUpdate("DROP TABLE IF EXISTS sjisTest");
        }
    }

    /**
     * Tests that UTF-8 character conversion works correctly.
     *
     * @throws Exception if any errors occur
     */
    public void testUtf8Encoding() throws Exception {
        Properties props = new Properties();
        props.put("characterEncoding", "UTF8");
        props.put("useUnicode", "true");

        Connection utfConn = DriverManager.getConnection(dbUrl, props);
        testConversionForString("UTF8", utfConn, "\u043c\u0438\u0445\u0438");
    }

    /**
     *
     *
     * @throws Exception ...
     */
    public void testUtf8Encoding2() throws Exception {
        String field1 = "K��sel";
        String field2 = "B�b";
        byte[] field1AsBytes = field1.getBytes("utf-8");
        byte[] field2AsBytes = field2.getBytes("utf-8");

        Properties props = new Properties();
        props.put("characterEncoding", "UTF8");
        props.put("useUnicode", "true");

        Connection utfConn = DriverManager.getConnection(dbUrl, props);
        Statement utfStmt = utfConn.createStatement();

        try {
            utfStmt.executeUpdate("DROP TABLE IF EXISTS testUtf8");
            utfStmt.executeUpdate(
                "CREATE TABLE testUtf8 (field1 varchar(32), field2 varchar(32)) CHARACTER SET UTF8");
            utfStmt.executeUpdate("INSERT INTO testUtf8 VALUES ('" + field1
                + "','" + field2 + "')");

            PreparedStatement pStmt = utfConn.prepareStatement(
                    "INSERT INTO testUtf8 VALUES (?, ?)");
            pStmt.setString(1, field1);
            pStmt.setString(2, field2);
            pStmt.executeUpdate();

            ResultSet rs = utfStmt.executeQuery("SELECT * FROM testUtf8");
            assertTrue(rs.next());

            // Compare results stored using direct statement
            // Compare to original string
            assertTrue(field1.equals(rs.getString(1)));
            assertTrue(field2.equals(rs.getString(2)));

            // Compare byte-for-byte, ignoring encoding
            assertTrue(bytesAreSame(field1AsBytes, rs.getBytes(1)));
            assertTrue(bytesAreSame(field2AsBytes, rs.getBytes(2)));

            assertTrue(rs.next());

            // Compare to original string
            assertTrue(field1.equals(rs.getString(1)));
            assertTrue(field2.equals(rs.getString(2)));

            // Compare byte-for-byte, ignoring encoding
            assertTrue(bytesAreSame(field1AsBytes, rs.getBytes(1)));
            assertTrue(bytesAreSame(field2AsBytes, rs.getBytes(2)));
        } finally {
            utfStmt.executeUpdate("DROP TABLE IF EXISTS testUtf8");
        }
    }

    private boolean bytesAreSame(byte[] byte1, byte[] byte2) {
        if (byte1.length != byte2.length) {
            return false;
        }

        for (int i = 0; i < byte1.length; i++) {
            if (byte1[i] != byte2[i]) {
                return false;
            }
        }

        return true;
    }

    private void testConversionForString(String charsetName,
        Connection convConn, String charsToTest) throws Exception {
        PreparedStatement pStmt = null;

        try {
            System.out.println("String to be tested: " + charsToTest);

            stmt = convConn.createStatement();
            stmt.executeUpdate("DROP TABLE IF EXISTS charConvTest_"
                + charsetName);

            if (!versionMeetsMinimum(4, 1)) {
                stmt.executeUpdate("CREATE TABLE charConvTest_" + charsetName
                    + "(field1 CHAR(50)");
            } else {
                stmt.executeUpdate("CREATE TABLE charConvTest_" + charsetName
                    + "(field1 CHAR(50) CHARACTER SET " + charsetName + ")");
            }

            stmt.executeUpdate("INSERT INTO charConvTest_" + charsetName
                + " VALUES ('" + charsToTest + "')");
            pStmt = convConn.prepareStatement("INSERT INTO charConvTest_"
                    + charsetName + " VALUES (?)");
            pStmt.setString(1, charsToTest);
            pStmt.executeUpdate();
            rs = stmt.executeQuery("SELECT * FROM charConvTest_" + charsetName);

            boolean hadRows = false;

            while (rs.next()) {
                hadRows = true;

                String testValue = rs.getString(1);
                System.out.println(testValue);
                assertTrue("Returned string \"" + testValue
                    + "\" != original string of \"" + charsToTest + "\"",
                    testValue.equals(charsToTest));
            }

            assertTrue(hadRows);
        } finally {
            stmt.executeUpdate("DROP TABLE IF EXISTS charConvTest_"
                + charsetName);
        }
    }

    private void testConversionForString(String charsetName, String charsToTest)
        throws Exception {
        testConversionForString(charsetName, this.conn, charsToTest);
    }
}