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.
