将H2数据过渡到PostgreSQL [英] Transition H2 data to PostgreSQL

问题描述

我正在从H2数据库过渡到PostgreSQL。正如我发现的那样-在H2上执行SCRIPT TO命令时创建的SQL转储包含几种不适当的构造：

I am doing a transition from H2 database to PostgreSQL. As I found out - the SQL dump which is created when doing SCRIPT TO command on H2 contains several inappropriate constructions:

• Unicode数据被包装为函数STRINGDECODE（'unicode-data'）


• 二进制& Blob数据以X'binary-data'（可能是十六进制格式）的形式转义（用于将图像和文件直接存储在数据库表中

我设法更改了导出的SQL，使其与Postgres兼容，如下所示：

I managed to change my exported SQL so it is compatible with Postgres as follows:

• Unicode数据用E'unicode-data'


• 二进制和Blob数据转义为E'binxbinary-data'-应导入Postgres的'bytea'列中

按需导入数据。

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

I configured Hibernate to use PostgreSQL dialect, where default binary & blob type of Postgres is changed to 'bytea':

public class PostgreDialectFix extends PostgreSQLDialect {

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

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

我还向我的persistense.xml添加了以下内容：

I also added the following to my persistense.xml:

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

所有内容都会编译并开始，但是当我尝试打开一个包含图像的页面时，它应该从新的PostgreSQL数据库加载时，会发生以下错误：

So everything compiles and starts, but when I try to open a page, containing an image and it should be loaded from new PostgreSQL database, the following error occurs:

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数据列有关。

So my question is: What is going wrong in this transition? I think it is something related to import of data or configuring of Hibernate to read correctly Postgres data columns ...

预先感谢！

附加信息：

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

Here is Postgres database table definition for FILEDATA:

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函数解码（ filedata-in-hex ，'hex'）。以下是导入示例：

FINALLY FOUND THE SOLUTION: In order to import filedata from H2 database into PostgreSQL database you need to use PostgreSQL function decode(filedata-in-hex, 'hex'). Here is a sample import:

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插入到数据库中，即可正确加载！

After that import is correctly insert into database as bytea and you can load it correctly!

推荐答案

使用PostgreSQL的函数decode（）解析。答案和示例代码可以在上面的问题中找到：）

Resolved using PostgreSQL's function decode(). Answer and sample code can be found in question above :)

这篇关于将H2数据过渡到PostgreSQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！