将H2数据转换成PostgreSQL

Question

我正在将数据库从H2转换到PostgreSQL。当我通过执行SCRIPT TO命令在H2上创建SQL转储时，我发现其中包含几个不恰当的构造：

- Unicode数据用函数STRINGDECODE('unicode-data')包装。 - 二进制和Blob数据以X'binary-data'的形式进行转义，可能是十六进制格式（用于将图像和文件直接存储在数据库表中）。

我成功地修改了导出的SQL，使其与Postgres兼容，如下所示：

- Unicode数据用E'unicode-data'包装。 - 二进制和Blob数据以E'\\xbinary-data'的形式进行转义，应该在Postgres的'bytea'列中导入。

数据的导入顺利完成。

我配置了Hibernate使用PostgreSQL方言，其中默认的二进制和Blob类型被更改为'bytea'：

public class PostgreDialectFix extends PostgreSQLDialect {

    /**
     * Creates a new instance.
     */
    public PostgreDialectFix() {
        super();

        registerColumnType(Types.BLOB, "bytea");                    
        registerColumnType(Types.LONGVARBINARY, "bytea");           
    }
}

我还在我的persistense.xml文件中添加了以下内容：

<!-- PostgreSQL binary data usage ('false' for oid, 'true' for bytea) -->
<property name="hibernate.jdbc.use_streams_for_binary" value="true"/>

所以一切都编译并启动了，但当我尝试打开一个包含图像的页面，并且应该从新的PostgreSQL数据库中加载时，就会出现以下错误：

Caused by: org.postgresql.util.PSQLException: Bad value of type long : \xffd8ffe000104a46494600010200000100010000ffdb004300...
at org.postgresql.jdbc2.AbstractJdbc2ResultSet.toLong(AbstractJdbc2ResultSet.java:2971)
at org.postgresql.jdbc2.AbstractJdbc2ResultSet.getLong(AbstractJdbc2ResultSet.java:2163)
at org.postgresql.jdbc2.AbstractJdbc2ResultSet.getBlob(AbstractJdbc2ResultSet.java:378)
at org.postgresql.jdbc2.AbstractJdbc2ResultSet.getBlob(AbstractJdbc2ResultSet.java:366)
at org.jboss.resource.adapter.jdbc.WrappedResultSet.getBlob(WrappedResultSet.java:386)
at org.hibernate.type.BlobType.get(BlobType.java:80)
at org.hibernate.type.BlobType.nullSafeGet(BlobType.java:134)
at org.hibernate.type.AbstractType.hydrate(AbstractType.java:105)
at org.hibernate.persister.entity.AbstractEntityPersister.hydrate(AbstractEntityPersister.java:2124)
at org.hibernate.loader.Loader.loadFromResultSet(Loader.java:1404)
at org.hibernate.loader.Loader.instanceNotYetLoaded(Loader.java:1332)
at org.hibernate.loader.Loader.getRow(Loader.java:1230)
at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:603)
at org.hibernate.loader.Loader.doQuery(Loader.java:724)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259)
at org.hibernate.loader.Loader.loadCollectionBatch(Loader.java:2053)
... 99 more

我的问题是：这个转换出了什么问题？我认为这与数据的导入或配置Hibernate以正确读取Postgres数据列有关...

提前致谢！

额外信息：

以下是FILEDATA的Postgres数据库表定义：

CREATE TABLE filedata
(
  id bigint NOT NULL,
  version integer,
  created timestamp with time zone,
  createdbymemberid bigint,
  modified timestamp with time zone,
  modifiedbymemberid bigint,
  filedata bytea,
  file_id bigint,
)
WITH (
  OIDS=FALSE
);       

终于找到了解决方案：要将H2数据库中的文件数据导入到PostgreSQL数据库中，您需要使用PostgreSQL函数decode(十六进制的文件数据，'hex')。以下是一个样例导入：

INSERT INTO PUBLIC.FILEDATA(ID, VERSION, CREATED, CREATEDBYMEMBERID, MODIFIED, MODIFIEDBYMEMBERID, FILEDATA, FILE_ID) VALUES
(174849, 0, TIMESTAMP '2013-02-11 14:47:57.743', 174833, NULL, NULL, decode('89504e470d0a1a0a0000000d494844520000007b00000050080600000039ac0a8a00000c1a4944415478daed5d095494d7151e136bb3b659baa5494c62dbc4d85a4dba9ee6a49bd6b4f5246dd29ec6a5ae803126261aad5b5ca24489bb6ca2800a0ac822288280ca22b8a251510165515059040541d661bbbddf9b19986146f9c1f90707df77ce3bc30c33ef9f79dfbbefdefbdebdf7d790c47d038d1c0249b684245b42922d21c99690644b48b22524d912926c0949b684245be2be203bb9289b3cd213e9d3c3c1f49ffd...', 'hex'), 174848);

之后，将导入正确插入到数据库作为bytea，并且您能够正确加载它！

- MykoB

---

你能在脚本中搜索这个值，然后发布表定义和失败的插入语句吗？这样更容易看出问题所在。 - Thomas Mueller

顺便提一下，有一些工具可以帮助迁移数据，例如SQuirreL DB Copy Plugin - 实际上还有更多类似的工具。 - Thomas Mueller

谢谢Thomas！我用所请求信息的示例更新了帖子。顺便说一句，我尝试使用SQuirrel DB Copy插件，但是由于需要复制的数据量太大而没有成功。所以唯一的障碍就是那些二进制列及其读取问题。 :) - MykoB

你确定已经配置了Hibernate使用你的特殊方言吗？ - Andrew Lazarus

Answer 1

使用PostgreSQL的decode()函数解决。答案和样例代码可以在上面的问题中找到 :)

- MykoB