Insert Java byte[] object into an H2 table and then retrieve it again
binary, byte, h2, java
Solution
The best way is to use a prepared statement:
byte[] object = ...;
PreparedStatement prep = conn.prepareStatement(
"insert into TEST (OBJECT) values (?)");
prep.setBytes(1, object);
An alternative is to convert the byte array to a hex value, but that's more complicated.
Problem
I'm trying to insert a Java byte[] into an H2 database table and then retrieve it again, but i am not achieving success. According to this page, the BINARY data type maps directly to a byte[]. So my understanding is i can write the byte[] variable directly to the column, but it is resulting in an exception when i try to retrieve it again. Here is a SSCCE that illustrates my problem. What am i doing wrong here? PS: You need to have an H2 database installed to run this code. ``` package coza.modh.fxplatform.examples.bytearray; import javax.swing.*; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; /** * Created by Willie van Zyl on 2014/07/04. */ public class ByteArray { public static void main(String[] args) { ByteArray byteArray = new ByteArray(); byteArray.createByteArray(); } private void createByteArray() { String object = "This is a string object"; byte[] bArray = null; System.out.println(object); try { java.io.ByteArrayOutputStream baos = new java.io.ByteArrayOutputStream(); java.io.ObjectOutputStream objOstream = new java.io.ObjectOutputStream(baos); objOstream.writeObject(object); bArray = baos.toByteArray(); System.out.println(bArray + " is the byte[] representation of the string object"); baos.close(); objOstream.close(); } catch (Exception e) { System.out.println(e.getMessage()); } System.out.println("byte [] created successfully"); if (writeToDB(bArray)) { System.out.println("byte[] successfully written to database"); } else { System.out.println("error writing byte[] to database"); System.exit(-1); } bArray = readFromDB(); java.io.ByteArrayInputStream bis = new java.io.ByteArrayInputStream(bArray); java.io.ObjectInput in; try { in = new java.io.ObjectInputStream(bis); object = in.readObject().toString(); System.out.println(object); } catch (Exception e) { System.out.println(e.getMessage()); } System.out.println("string successfully created"); } private boolean writeToDB(byte[] object) { boolean result = false; try { String dbUrl = "C:\\Users\\User\\db\\test"; String input = JOptionPane.showInputDialog(null, "DB String: (" + dbUrl +")"); if (!input.equals("")) dbUrl = "jdbc:h2:" + input; else dbUrl = "jdbc:h2:" + dbUrl; String dbDriverClass = "org.h2.Driver"; String userName = "sa"; String password = "sa"; Class.forName(dbDriverClass); Connection connection = DriverManager.getConnection(dbUrl, userName, password); Statement statement = connection.createStatement(); statement.execute("drop table if exists TEST"); statement.execute("create table TEST(OBJECT BINARY)"); statement.execute("insert into TEST (OBJECT) values ('" + object + "')"); connection.close(); statement.close(); result = true; } catch (Exception except) { except.printStackTrace(); } return result; } private byte[] readFromDB() { byte[] bArray = null; try { String dbUrl = "C:\\Users\\User\\db\\test"; String input = JOptionPane.showInputDialog(null, "DB String: (" + dbUrl +")"); if (!input.equals("")) dbUrl = "jdbc:h2:" + input; else dbUrl = "jdbc:h2:" + dbUrl; String dbDriverClass = "org.h2.Driver"; String userName = "sa"; String password = "sa"; Class.forName(dbDriverClass); Connection connection = DriverManager.getConnection(dbUrl, userName, password); Statement statement = connection.createStatement(); ResultSet results = statement.executeQuery("select * from TEST"); while (results.next()) { bArray = results.getBytes("OBJECT"); } connection.close(); statement.close(); } catch (Exception except) { except.printStackTrace(); } return bArray; } } ``` This is the output i am getting: ``` This is a string object [B@11210ee is the byte[] representation of the string object byte [] created successfully org.h2.jdbc.JdbcSQLException: Hexadecimal string contains non-hex character: "[B@11210ee"; SQL statement: insert into TEST (OBJECT) values ('[B@11210ee') -- ('[B@11210ee') [90004-178] at org.h2.message.DbException.getJdbcSQLException(DbException.java:344) at org.h2.message.DbException.get(DbException.java:178) at org.h2.message.DbException.get(DbException.java:154) at org.h2.util.StringUtils.convertHexToBytes(StringUtils.java:966) at org.h2.value.Value.convertTo(Value.java:864) at org.h2.table.Column.convert(Column.java:151) at org.h2.command.dml.Insert.insertRows(Insert.java:144) at org.h2.command.dml.Insert.update(Insert.java:115) at org.h2.command.CommandContainer.update(CommandContainer.java:79) at org.h2.command.Command.executeUpdate(Command.java:254) at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:186) at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:160) at coza.modh.fxplatform.examples.bytearray.ByteArray.writeToDB(ByteArray.java:88) at coza.modh.fxplatform.examples.bytearray.ByteArray.createByteArray(ByteArray.java:43) at coza.modh.fxplatform.examples.bytearray.ByteArray.main(ByteArray.java:16) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at >sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:483) at com.intellij.rt.execution.application.AppMain.main(AppMain.java:134) ``` The error is definitely correct as [B@11210ee does contain non hex characters. The [B@ specifically doesn't look right. I just think i don't have a good handle on how byte[] work. Any help would be appreciated.