JdbcTemplate: accessing MySQL VARBINARY field as String

Question

I'm having trouble reading MySQL's VARBINARY field as a String using JdbcTemplate. We're storing string abbreviations ("ABC", "XYZ", "LMN" and the like) as VARBINARYs (don't ask me why). Strangely enough, when I use the Connection class / PreparedStatement route, and plain old ResultSets vs. SqlRowSet, I have no problem reading the String. That is to say,

This code works:

String sql = "select MY_VARBINARY_FIELD from MY_TABLE where KEY1=? and KEY2=?";
PreparedStatement stmt = connectionDev.prepareStatement(sql);
prepStmt1.setInt(1, key1);
prepStmt1.setInt(2, key2);
ResultSet rs = stmt.executeQuery();

while (rs.next()) {
    String s = rs.getString("MY_VARBINARY_FIELD");
    System.out.print(s + " ");
}

**Output:** AHI-1 DKFZp686J1653 FLJ14023 FLJ20069 JBTS3 ORF1 dJ71N10.1 

But this code doesn't:

String sql = "select MY_VARBINARY_FIELD from MY_TABLE where KEY1=? and KEY2=?";
Object[] params = {key1, key2};
SqlRowSet rows = getJdbcTemplate().queryForRowSet(sql, params);

while (rows.next()) {
    String s = rows.getString("MY_VARBINARY_FIELD");
    System.out.print(s + " ");
}

**Output:** [B@3a329572 [B@4ef18d37 [B@546e3e5e [B@11c0b8a0 [B@399197b [B@3857dc15 [B@10320399 

Why do SqlRowSet and ResultSet produce a different String representation for the VARBINARY? And how can I get the "correct" representation using JdbcTemplate/SqlRowSet?

Thanks!

SOLUTION

Mark Rotteveel (below) answered the question. I got it to work with this:

String sql = "select MY_VARBINARY from MY_TABLE where KEY=VALUE";

SqlRowSet rows = getJdbcTemplate().queryForRowSet(sql);

while (rows.next()) {
     byte[] varbinary = (byte[]) rows.getObject("MY_VARBINARY");
     System.out.println(new String(varbinary));
}

Solution

The [B@3a329572 etc is the result of a toString() on a byte array. A VARBINARY is represented using a byte[] in JDBC. Most likely when you access it directly on PreparedStatement, the driver does new String(getBytes(idx)), while the JdbcTemplate probably does a getObject(idx).toString() instead, resulting in output like [B@3a329572.

So to fix this, do a getBytes() on the JdbcTemplate, and convert it to string yourself or (better yet), use the byte array directly or change the column datatype to a VARCHAR instead.

Comments

[artbristol]

+1 good spot. Also, probably a good idea to specify the character encoding to avoid cross-platform weirdness

[Mark Rotteveel]

@artbristol True, but I suspect the driver is also doing it without explicit characterset.

[ktm5124]

+1 This makes a lot of sense and you're probably right. I'll check later today.