Issue with uploading pdf into Postgres

Question

I am trying to save a pdf doc into my postgres 10.1 database using Hibernate. Below is my entity class.

@Entity
@Table(name = "FILEUPLOADS", schema="SomeSchema")
public class FileUploads{
//skipping other columns

@Lob
@Column(name="DOCDATA")
private byte[] docData;

}

This column is op type bytea in the postgres database.

As suggested by Authur here : similar issue, I created my custom implementation of postgres dialect like below.

@Configuration
public class TestDialect extends PostgreSQLDialect{
    public TestDialect() {
        super();

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

}

Then inside my persistence.xml file , I am using this custom dialect

<property name="hibernate.dialect" value="com.digital.repository.TestDialect" />

Even after doing all this, I am getting below error

ERROR: column "DOCDATA" is of type bytea but expression is of type bigint
  Hint: You will need to rewrite or cast the expression.
  Position: 251
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2468)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2211)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:309)
        at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:446)
        at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:370)
        at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:149)
        at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:124)
        at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
        at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:606)
        at org.hibernate.engine.jdbc.internal.proxy.AbstractStatementProxyHandler.continueInvocation(AbstractStatementProxyHandler.java:122)
        ... 250 more

What else do I need to do in order to resolve this issue ?

Just don't. Save your big binary files using a database that was designed for that purpose: *the filesystem*. Then save filenames in your DB. — Jonathon Reinhart, Jun 28 '19 at 11:37
@Simon No need. Ensure the file exists on disk, and then insert into the db. Reverse the order on deletion. If queries always start at the db, you'll never see a record without a backing file. — Jonathon Reinhart, Jun 28 '19 at 21:41

score 1 · Answer 1 · answered Jun 28 '19 at 12:22

1

Try to change the Hibernate type:

@Entity
@Table(name = "FILEUPLOADS", schema="SomeSchema")
public class FileUploads{
//skipping other columns

@Type(type = "org.hibernate.type.BinaryType")
@Column(name="DOCDATA")
private byte[] docData;

}

answered Jun 28 '19 at 12:22

Simon Martinelli

34,053
5
48
82

Oracle??? You said PostgreSQL! Oracle has BLOB data type and there you have to use the @LOB annotation – Simon Martinelli Jun 28 '19 at 14:54
Once again, I have to maintain single code base for both oracle and postgres :-) I will try this one out anyways. Thanks ! – Naxi Jun 28 '19 at 15:03

score 0 · Accepted Answer · answered Jun 28 '19 at 17:51

Below configuration worked for me and I am able to save the doc/pdf into db now.

@Configuration
public class CustomPostgresDialect extends PostgreSQL82Dialect{

    @Override
    public SqlTypeDescriptor remapSqlTypeDescriptor(SqlTypeDescriptor sqlTypeDescriptor) {
    if (sqlTypeDescriptor.getSqlType() == java.sql.Types.BLOB) {
      return BinaryTypeDescriptor.INSTANCE;
    }
    return super.remapSqlTypeDescriptor(sqlTypeDescriptor);
  }
}

Hibernate 4.1.10

Java 1.7

Issue with uploading pdf into Postgres

2 Answers2