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));
}
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.