bad value for type long: - Postgresql, Hibernate, Spring

Question

I wanna store an entity(a String + an image) in PostgresQL using Spring MVC and Hibernate Here is my table. The image is supposed to be the type of oid.

CREATE TABLE document
(
  name character varying(200),
  id serial NOT NULL,
  content oid,   // that should be the image
  CONSTRAINT document_pkey PRIMARY KEY (id )
)
WITH (
  OIDS=FALSE
);

Here is the entity that I want to store.

    @Entity
    @Table(name = "document")
    public class Document {

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

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

        @Column(name="content")
            private Blob content;  //this is the image
//getters- setters

You can see the variable "name" is a String, not Long. Still when I submit the form with a value which is not numeric it throws org.postgresql.util.PSQLException: Bad value for type long : x

here is the form:

<form:form method="post" action="save.html" commandName="document" enctype="multipart/form-data">
    <form:errors path="*" cssClass="error"/>
    <table>
    <tr>
        <td><form:label path="name">Name</form:label></td>
        <td><form:input path="name" /></td> 
    </tr>

     <tr>
        <td><form:label path="content">Document</form:label></td>
        <td><input type="file" name="file" id="file"></input></td>
    </tr>
    <tr>
        <td colspan="2">
            <input type="submit" value="Add Document"/>
        </td>
    </tr>
</table>  
</form:form>

If I enter a numeric value and submit it, OK. But any non-numeric value triggers the above mentioned exception...I read that it might be caused by that I do not use OID properly but I do not know what should I do to eliminate this exception. Actually I do not understand the name of the excpetion either. It says "bad value for type long" . but who wants type long? the variable "name" is type String!!!!

Finally, here is the Controller

@RequestMapping(value = "/save", method = RequestMethod.POST)
public String save(@ModelAttribute("document") Document document, @RequestParam("file") MultipartFile file) {

    try {
        Blob blob = Hibernate.createBlob(file.getInputStream());
        document.setContent(blob);
        documentDao.save(document);
    } catch (Exception e) {
        e.printStackTrace();
    }


    return "redirect:/index.html";
}

Any advice is appriciated.

Solution 1

when I created the table the column "name" happened to be the first. That's not good. Id must be the first column. If I change the order of columns it works fine...

Solution 2

I had a similiar problem but it was not related to the order of ID field in the database.

After some searching I found this pointing to the fact that Lobs in Hibernate are treated as OIDs unless otherwise specified.

That means Hibernate will try put a Lob into a Long a hence produce that exception PSQLException: Bad value for type long

The way to specify that the Lob is a to be treated as text is by annotating the field

@Lob
@Type(type = "org.hibernate.type.TextType")

Solution 3

At first I tried to set

@Column(columnDefinition = "text")

instead of @Lob annotation, as already mentioned here. It worked for me on PostgreSQL, but with error org.hibernate.tool.schema.spi.SchemaManagementException: Schema-validation: wrong column type encountered in column [htmlText] in table [Question]; found [clob (Types#CLOB)], but expecting [text (Types#VARCHAR)]

in my unit tests (on HSQLDB).

Then it tried

@Column(columnDefinition = "clob")

And it works well on both PostgreSQL and HSQLDB!

Solution 4

Problem exist on Postgres and Hibernate. Column text can't be mapped to String. You need to add : @Type(type="org.hibernate.type.TextType")

Example :

@Lob
@Type(type = "org.hibernate.type.TextType")
@Column
private String contentJson;

If column contains binary type, use : @Type(type="org.hibernate.type.BinaryType")

Solution founded on page : https://github.com/jhipster/generator-jhipster/issues/5995

View more solutions

Comments

[user3145373 ツ]

@Regenbogenfisch - how to change the order or column ?? I am also facing this problem. I have tried it in pgAdmin III but can't, so will you help me ?

[Sanyifejű]

simply drop table and recreate it.

[membersound]

You could as well use @Column(columnDefinition = "text") instead of lob+type.

[dtrunk]

Using columnDefinition didn't worked for me. But using @Type did.

[marciowb]

I confirmed the problem with Hibernate 3.6.8 and PostgreSQL 9.4. After apply the Tšeliso Molukanele's solution it worked fine.

[tonyfarney]

It saved my day! The strange thing about the error I was getting is that the field pointed in the logs was not the TEXT one.