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.

You may want to try annotating your Blob declaration with the hibernate @Lob annotation. Also, it may help to turn on hibernate's query output so you can see the sql that is being generated and see if it gives you a hint as to what is being sent to the database. — jcern, Sep 28 '12 at 22:01
An OID can't hold a BLOB. ["The oid type is currently implemented as an unsigned four-byte integer."](http://www.postgresql.org/docs/9.0/static/datatype-oid.html). I suppose that's where the error comes from. — madth3, Sep 28 '12 at 22:33
@madth3 `oid` is used to hold a reference to a large object in the `pg_largeobject` table. — Craig Ringer, Sep 28 '12 at 23:40
Of course, sorry for that. Did you check [this question](http://stackoverflow.com/q/1333596/422353)? What version of PostgreSQL are you using? — madth3, Sep 28 '12 at 23:46

score 96 · Answer 1 · answered Feb 04 '14 at 07:57

96

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")

answered Feb 04 '14 at 07:57

Tšeliso Molukanele

1,630
12
18

6

You could as well use `@Column(columnDefinition = "text")` instead of lob+type. – membersound May 05 '15 at 09:27
1

Using `columnDefinition` didn't worked for me. But using `@Type` did. – dtrunk May 09 '17 at 19:22
1

I confirmed the problem with Hibernate 3.6.8 and PostgreSQL 9.4. After apply the Tšeliso Molukanele's solution it worked fine. – marciowb Aug 21 '17 at 16:03
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. – tonyfarney Apr 16 '21 at 22:33

score 10 · Accepted Answer · answered Sep 29 '12 at 19:52

10

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...

answered Sep 29 '12 at 19:52

Sanyifejű

2,610
10
46
73

@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 ? – user3145373 ツ Apr 04 '14 at 11:07
1

simply drop table and recreate it. – Sanyifejű Apr 04 '14 at 11:16

score 6 · Answer 3 · answered Jul 26 '18 at 14:02

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!

score 2 · Answer 4 · answered Dec 30 '21 at 11:56

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

score 1 · Answer 5 · answered May 12 '22 at 13:55

Use the @Column and columnDefinition to stay compliant with the JPA spec

@Column(columnDefinition = "CLOB")

or Hibernate annotation @Type with JPA @Lob annotation if its okay for you to use Hibernate annotations

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

score -1 · Answer 6 · answered Jul 14 '15 at 23:41

-1

I faced smiler error and the reason was that I was having some characters other than integer in integer data type filed

e.g.- ,111 and 144- etc

answered Jul 14 '15 at 23:41

Shubham

434
6
12

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

6 Answers6

Linked