java - 将 Java byte[] 对象插入到 H2 表中，然后再次检索它

Question

我试图将 Java byte[] 插入到 H2 数据库表中，然后再次检索它，但我没有取得成功。根据this page , BINARY 数据类型直接映射到 byte[]。所以我的理解是我可以将 byte[] 变量直接写入列，但是当我尝试再次检索它时会导致异常。

这是一个SSCCE这说明了我的问题。我在这里做错了什么？

PS:你需要有一个 H2 database安装以运行此代码。

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 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)

错误绝对正确，因为 [B@11210ee 确实包含非十六进制字符。 [B@ 特别看起来不对。我只是觉得我不太了解 byte[] 的工作原理。任何帮助将不胜感激。

Answer 1

最好的方法是使用准备好的语句:

byte[] object = ...;
PreparedStatement prep = conn.prepareStatement(
    "insert into TEST (OBJECT) values (?)");
prep.setBytes(1, object);

另一种方法是将字节数组转换为十六进制值，但这更复杂。