JdbcTemplate: access to MySQL VARBINARY field as string

Question

I'm having trouble reading MySQL VARBINARY as a string using JdbcTemplate. We store line abbreviations ("ABC", "XYZ", "LMN", etc.) as VARBINARY (don't ask me why). Oddly enough, when I use the Connection / PreparedStatement route and the plain old ResultSets vs. SqlRowSet, I have no problem reading the string. I.e

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 does not:

 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 are SqlRowSet and ResultSet creating a different string representation for VARBINARY? And how can I get the “correct” view using JdbcTemplate / SqlRowSet?

Thanks!

Decision

Mark Rottweel (below) answered the question. I got this 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)); } 

Answer 1

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

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

Answer 2

If I use it as shown below, this is good.

• org.mybatis 3.2.8
• mysql-connector-java 5.1.38 || MariaDB-socket-Java
• Java Bean, private String myVarbinary;

my_varbinary(64) for SessionID / NameOfEnglis h, so I don't need to consider character_set and encoding / decoding.