How to store file in postgres database using hibernate?

Question

I am new to Hibernate. I need to store file in postgres, data type of the column is bytea. I looked around on internet for a working demo of this case, however I couldn't find any.

Hibernate mapping :


<property name="fileData" type="binary">
   <column name="fileData" not-null="true" />
</property>

POJO :



    private byte[] fileData;
        public byte[] getFileData() {
            return fileData;
        }
        public void setFileData(byte[] fileData) {
            this.fileData = fileData;
        }

table :


    create table nmonData(id int, buildNumber int, path text, fileName text, fileData bytea);

Error :

org.hibernate.tool.schema.spi.SchemaManagementException: Schema-validation: wrong column type encountered in column [fileData] in table [nmondata]; found [bytea (Types#BINARY)], but expecting [binary(255) (Types#VARBINARY)]
    at org.hibernate.tool.schema.internal.SchemaValidatorImpl.validateColumnType(SchemaValidatorImpl.java:105)
    at org.hibernate.tool.schema.internal.SchemaValidatorImpl.validateTable(SchemaValidatorImpl.java:92)
    at org.hibernate.tool.schema.internal.SchemaValidatorImpl.doValidation(SchemaValidatorImpl.java:50)
    at org.hibernate.tool.hbm2ddl.SchemaValidator.validate(SchemaValidator.java:91)
    at org.hibernate.internal.SessionFactoryImpl.<init>(SessionFactoryImpl.java:473)
    at org.hibernate.boot.internal.SessionFactoryBuilderImpl.build(SessionFactoryBuilderImpl.java:444)
    at com.psl.perf.hibernate.util.HibernateUtil.<clinit>(HibernateUtil.java:34)
    at com.psl.perf.DAO.NmonReportFileDaoImpl.save(NmonReportFileDaoImpl.java:15)
    at com.psl.perf.service.DataService.saveNmonCSV(DataService.java:249)
    at com.psl.perf.main.Main2.main(Main2.java:8)
Jan 22, 2016 12:26:18 PM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl stop
INFO: HHH10001008: Cleaning up connection pool [jdbc:postgresql://x.x.x.x:5432/perf]
Exception in thread "main" java.lang.NullPointerException
    at com.psl.perf.DAO.NmonReportFileDaoImpl.save(NmonReportFileDaoImpl.java:17)
    at com.psl.perf.service.DataService.saveNmonCSV(DataService.java:249)
    at com.psl.perf.main.Main2.main(Main2.java:8)

can someone share working demo or link for it?

This worked for me commenting <property name="hbm2ddl.auto">validate</property> in hibernate config file did the trick for me, however I'm confuse now about what should be the data type of column and member variable in java for storing file.

bytea in PG maps to byte[] in Java. I assume you want to store the file contents and not the reference (location). Declare the object field corresponding to the column as byte[]. Convert your file to a byte[] and set it as the field value. When your save your object the file contents will be mapped to the bytea column. While reading from the database read the byte array and reconstruct the file from the byte[]. — uncaught_exception, Jan 22 '16 at 06:34
@ShireResident I did something similar, however still getting the error. I edited the post added code snippets and error. — Ketu, Jan 22 '16 at 07:03
@DavidSiro NmonReportFileDaoImpl.java is just to get a session and save the object, it does nothing else. Do you still want to see the code? — Ketu, Jan 22 '16 at 07:21
@ketu b why is this throwing a NPE? Exception in thread "main" java.lang.NullPointerException at com.psl.perf.DAO.NmonReportFileDaoImpl.save(NmonReportFileDaoImpl.java:17) Also, what version of Hibernate / PG are you using? — uncaught_exception, Jan 22 '16 at 07:24
What is causing the NullPointerException? It works for me with Hibernate 5.0.6 and the XML configuration. — uncaught_exception, Jan 22 '16 at 07:52
I assume it is because of the validation `validate` in hibernate config. After removing the validation property, exception is gone, and file is stored in database. — Ketu, Jan 22 '16 at 07:57
@ShireResident can you suggest on what should be the postgres datatype and corresponding type in java for `validate` this to work without any issues? — Ketu, Jan 22 '16 at 08:03
@ketu b what dialect have you set? It is working on my side even with the validate. I suspect it might be set to org.hibernate.dialect.H2Dialect for unit testing. — uncaught_exception, Jan 22 '16 at 09:35
@ShireResident `org.hibernate.dialect.PostgreSQLDialect` could it be something due to the hibernate version i.e. 5.0.6 i'm using? — Ketu, Jan 22 '16 at 09:49

score 3 · Answer 1 · answered Jan 22 '16 at 07:06

I assume you need to store file contents into PG bytea column.

PostgreSQL DDL

CREATE TABLE data_object
(
  obj_id bigserial NOT NULL,
  obj_name character(30),
  obj_file bytea,
  CONSTRAINT data_object_pkey PRIMARY KEY (obj_id)
)

Java Object

@Entity
@Table (name = "data_object")
public class DataObject {

    @Id
    @GeneratedValue (strategy = GenerationType.IDENTITY)
    @Column(name = "obj_id")
    private Long id;

    @Column(name = "obj_name")
    private String name;

    @Column(name = "obj_file")
    private byte[] file;

    // getters, setters, hasCode, equals, toString

Driver Code

byte[] data = new byte[FILE_SIZE];
// read file contents into data
DataObject obj = new DataObject();
obj.setFile(data);
// save obj using your hibernate session

I am using Hibernate 4.3.7 and PG 9.4.1

How to store file in postgres database using hibernate?

1 Answers1