how mybatis insert/delete blob, large object, bytea columns in postgresql

[Emi Lu]

Hello,

Is there an online doc about how mybatis deal with(add, update, remove)
large object (postgresql8.3)?

<insert id="" parameterType="?" >
...
</insert>

How mybatis deal with blob, large object, bytea columns in postgresql8.3?

Thanks a lot!
Emi

[Xin Li]

Hi,

I am having a hard time figuring out how Mybatis save and retrieve large object in PostgreSQL.

Any help would be greatly appreciated.

I am using:

MyBatis 3.1.1

PostgreSQL 9.2

Here is my code after reading this http://jira.codehaus.org/browse/ACT-187:

PostgreSQL code:

CREATE TABLE file_blob

(

  id serial NOT NULL,

  data_blob oid NOT NULL

)

Java code:

public class FileEntity{

  private int id;

  private byte[] fileBytes;

  //getters and setters

}

//Also plus DAO and interface mapper

Mapper XML:

<resultMap id="FileEntityResultMap" type="FileEntity">

        <result property="id" column="id" />

        <!--pretty sure this is wrong, but do not know what it should be-->

        <result property="fileBytes" column="data_blob" jdbcType="BINARY" />  

</resultMap>

<select id="find" resultMap="FileEntityResultMap">

        select id, name, data_blob

        from file_blob

        where id = #{id};

</select>

<insert id="insert" parameterType="FileEntity" useGeneratedKeys="true" keyProperty="id" keyColumn="id">

        insert into file_blob(data_blob)

        values( #{fileBytes,jdbcType=BINARY});

</insert>

Error messages:

org.apache.ibatis.exceptions.PersistenceException: 

### Error updating database.  Cause: org.postgresql.util.PSQLException: ERROR: column "data_blob" is of type oid but expression is of type bytea

  ヒント: You will need to rewrite or cast the expression.

  ポジション: 120

### The error may involve databasetest.mapper.FileEntityMapper.insert-Inline

### The error occurred while setting parameters

### SQL: insert into file_blob( data_blob)         values( ? );

### Cause: org.postgresql.util.PSQLException: ERROR: column "data_blob" is of type oid but expression is of type bytea

  ヒント: You will need to rewrite or cast the expression.

 ポジション: 120

at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:23)

at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:147)

at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:134)

at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:79)

at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:40)

....

[Guy Rouillier]

On 6/4/2013 3:33 AM, Xin Li wrote:
> CREATE TABLE file_blob
> (
> id serial NOT NULL,
> data_blob oid NOT NULL
> )

See http://jdbc.postgresql.org/documentation/head/binary-data.html. You
should be declaring your column bitea, not oid.

> Mapper XML:
> <resultMap id="FileEntityResultMap" type="FileEntity">
> <result property="id" column="id" />
> <!--pretty sure this is wrong, but do not know what it should be-->
> <result property="fileBytes" column="data_blob"
> jdbcType="BINARY" />

See the MyBatis User's Guide. Set jdbcType to BLOB.

--
Guy Rouillier

[Xin Li]

On Tuesday, June 4, 2013 5:20:45 PM UTC+9, Guy Rouillier wrote:

On 6/4/2013 3:33 AM, Xin Li wrote:
> CREATE TABLE file_blob
> (
>    id serial NOT NULL,
> data_blob oid NOT NULL
> )

See http://jdbc.postgresql.org/documentation/head/binary-data.html.  You
should be declaring your column bitea, not oid.

Sorry, I did not make myself clear at the first pace. 

I want to use LargeObject instead of bitea in my case. Is there a way to do it with MyBatis? Do I have to create my own type handler for it? 

Thanks,

Xin