Hibernate 4.1 with HSQLDB gives 'data exception: string data, right truncation'

spring hibernate jpa hsqldb c3p0
29,039

Solution 1

Try HSQLDB 2.2.9 or the latest sanpshot as the stack trace looks like an issue that was fixed after 2.2.8.

Maven repository for both snapshots and releases is here:

http://www.hsqldb.org/repos/org/hsqldb/hsqldb/

Also see the answer below. It seems the use of "org.hibernate.type.MaterializedClobType" is common with PostgreSQL. But your field is byte[] and can be represented better as BLOB with HSQLDB.

Retrieve image from blob via Hibernate (not JDBC)

Solution 2

The problem is with HSQLDialect implementation, which registers sql types in its constructor:

public HSQLDialect() {
    super();

    ...
    registerColumnType( Types.BLOB, "blob($l)" );
    registerColumnType( Types.CLOB, "clob($l)" );
    ...
}

Where $l is taken from column length attribute. But if column length attribute is not set explicitly then it has default value of 255. In such case HSQLDialect creates column as CLOB(255) which is funny for clobs...

1)

One solution is to set column length explicitly with sufficient value.

For example:

@Lob
@Column(length = 10000)
private String text;

But if you want to avoid such artificial constructs, you can modify dialect.

2)

Second solution is to modify HSQLDialect:

package com.extensions.dialect;

import java.sql.Types;
import org.hibernate.dialect.HSQLDialect;

public class MyHsqlDialect extends HSQLDialect {

    public MppHsqlDialect() {
        super();
        registerColumnType(Types.CLOB, "clob");
    }
}

and in hibernate configuration setup new dialect:

hibernate.dialect=com.extensions.dialect.MyHsqlDialect

I use this solution in my integration tests.

Some additional info may be found here:

https://hibernate.atlassian.net/browse/HHH-7541

Solution 3

The correct answer should be: add length to your @Column annotation, like this:

@Lob
@Column(length = 10000, name = "style")
private String style;

Solution 4

I've had similar problem. There was plenty of errors: "data exception: string data, right truncation". The resolution was to specify size of big fields. Without that fields will be limited to default size. But that is not all. HSQL doesn't automatically updates database schema. So changes will not apply util you recreate database.

@Column(length = 5 * 1024 * 1024) // five megabytes
private byte[] bytesPreview;
Share:
29,039
Kronis
Author by

Kronis

Updated on February 17, 2020

Comments

  • Kronis
    Kronis over 4 years

    I have a very strange problem, got it when I upgraded som deps to the project.

    I'm now using following versions:

    • Spring: 3.1.0.RELEASE
    • Hibernate: 4.1.7.Final
    • Hsqldb: 2.2.8 (org.hsqldb)

    I THINK the problem has to do something with the file field. (Dbfile.content)

    Stacktrace:

    javax.persistence.PersistenceException: org.hibernate.exception.DataException: could not insert: [org.project.model.Cv]
    at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1387)
    at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1315)
    at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1321)
    at org.hibernate.ejb.AbstractEntityManagerImpl.persist(AbstractEntityManagerImpl.java:843)
    at sun.reflect.GeneratedMethodAccessor11.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    at org.springframework.orm.jpa.SharedEntityManagerCreator$SharedEntityManagerInvocationHandler.invoke(SharedEntityManagerCreator.java:240)
    at $Proxy36.persist(Unknown Source)
    at org.project.dao.jpa.GenericDaoJpa.save(GenericDaoJpa.java:49)
    at sun.reflect.GeneratedMethodAccessor10.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:318)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
    at $Proxy40.save(Unknown Source)
    at org.project.dao.CvDaoTest.updateTest(CvDaoTest.java:77)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:44)
    at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:15)
    at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:41)
    at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:20)
    at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:74)
    at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:83)
    at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:72)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:231)
    at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:49)
    at org.junit.runners.ParentRunner$3.run(ParentRunner.java:193)
    at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:52)
    at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:191)
    at org.junit.runners.ParentRunner.access$000(ParentRunner.java:42)
    at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:184)
    at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
    at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:71)
    at org.junit.runners.ParentRunner.run(ParentRunner.java:236)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:174)
    at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:50)
    at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:467)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:683)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:390)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:197)
Caused by: org.hibernate.exception.DataException: could not insert: [org.project.model.Cv]
    at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:102)
    at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
    at org.hibernate.id.insert.AbstractReturningDelegate.performInsert(AbstractReturningDelegate.java:64)
    at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2345)
    at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2852)
    at org.hibernate.action.EntityIdentityInsertAction.execute(EntityIdentityInsertAction.java:71)
    at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:273)
    at org.hibernate.event.def.AbstractSaveEventListener.performSaveOrReplicate(AbstractSaveEventListener.java:320)
    at org.hibernate.event.def.AbstractSaveEventListener.performSave(AbstractSaveEventListener.java:203)
    at org.hibernate.event.def.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:129)
    at org.hibernate.ejb.event.EJB3PersistEventListener.saveWithGeneratedId(EJB3PersistEventListener.java:69)
    at org.hibernate.event.def.DefaultPersistEventListener.entityIsTransient(DefaultPersistEventListener.java:179)
    at org.hibernate.event.def.DefaultPersistEventListener.onPersist(DefaultPersistEventListener.java:135)
    at org.hibernate.event.def.DefaultPersistEventListener.onPersist(DefaultPersistEventListener.java:61)
    at org.hibernate.impl.SessionImpl.firePersist(SessionImpl.java:808)
    at org.hibernate.impl.SessionImpl.persist(SessionImpl.java:782)
    at org.hibernate.impl.SessionImpl.persist(SessionImpl.java:786)
    at org.hibernate.ejb.AbstractEntityManagerImpl.persist(AbstractEntityManagerImpl.java:837)
    ... 45 more
Caused by: java.sql.SQLDataException: data exception: string data, right truncation
    at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
    at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCPreparedStatement.fetchResult(Unknown Source)
    at org.hsqldb.jdbc.JDBCPreparedStatement.executeUpdate(Unknown Source)
    at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:105)
    at org.hibernate.id.IdentityGenerator$GetGeneratedKeysDelegate.executeAndExtract(IdentityGenerator.java:94)
    at org.hibernate.id.insert.AbstractReturningDelegate.performInsert(AbstractReturningDelegate.java:57)
    ... 60 more
Caused by: org.hsqldb.HsqlException: data exception: string data, right truncation
    at org.hsqldb.error.Error.error(Unknown Source)
    at org.hsqldb.error.Error.error(Unknown Source)
    at org.hsqldb.types.BinaryType.castOrConvertToType(Unknown Source)
    at org.hsqldb.types.BinaryType.convertToType(Unknown Source)
    at org.hsqldb.StatementDML.getInsertData(Unknown Source)
    at org.hsqldb.StatementInsert.getInsertValuesNavigator(Unknown Source)
    at org.hsqldb.StatementInsert.getResult(Unknown Source)
    at org.hsqldb.StatementDMQL.execute(Unknown Source)
    at org.hsqldb.Session.executeCompiledStatement(Unknown Source)
    at org.hsqldb.Session.execute(Unknown Source)
    ... 65 more

    Console:

    INFO : org.hibernate.engine.jdbc.JdbcSupportLoader - Disabling contextual LOB creation as createClob() method threw error : java.lang.reflect.InvocationTargetException
Hibernate: insert into Nationality (id, countryCode) values (default, ?)
Hibernate: insert into Language (id, countryCode, languageCode) values (default, ?, ?)
Hibernate: insert into Person (id, birthdate, created, email, enabled, firstName, image, lastName, lastUpdate, nationality_id, password, role, salt) values (default, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Hibernate: insert into Nationality (id, countryCode) values (default, ?)
Hibernate: insert into Person (id, birthdate, created, email, enabled, firstName, image, lastName, lastUpdate, nationality_id, password, role, salt) values (default, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Hibernate: insert into Company (id, name) values (default, ?)
Hibernate: insert into Language (id, countryCode, languageCode) values (default, ?, ?)
Hibernate: insert into Company (id, name) values (default, ?)
Hibernate: insert into Skill (id, company_id, created, isCategory, lastUpdate, skill_fk) values (default, ?, ?, ?, ?, ?)
Hibernate: insert into Company (id, name) values (default, ?)
Hibernate: insert into Skill (id, company_id, created, isCategory, lastUpdate, skill_fk) values (default, ?, ?, ?, ?, ?)
Hibernate: insert into Nationality (id, countryCode) values (default, ?)
Hibernate: insert into Person (id, birthdate, created, email, enabled, firstName, image, lastName, lastUpdate, nationality_id, password, role, salt) values (default, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Hibernate: insert into CourseExperience (created, lastUpdate, lastUpdatePerson_id, person_fk, endDate, startDate, id) values (?, ?, ?, ?, ?, ?, ?)
Hibernate: insert into Nationality (id, countryCode) values (default, ?)
Hibernate: insert into Person (id, birthdate, created, email, enabled, firstName, image, lastName, lastUpdate, nationality_id, password, role, salt) values (default, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Hibernate: insert into CourseExperience (created, lastUpdate, lastUpdatePerson_id, person_fk, endDate, startDate, id) values (?, ?, ?, ?, ?, ?, ?)
Hibernate: insert into Cv (id, company, created, language, lastUpdate, lastUpdatePerson_id, name, person_fk, summary_id) values (default, ?, ?, ?, ?, ?, ?, ?, ?)
WARN : org.hibernate.util.JDBCExceptionReporter - SQL Error: -3401, SQLState: 22001
ERROR: org.hibernate.util.JDBCExceptionReporter - data exception: string data, right truncation

    Cv.java:

    package org.project.model;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToMany;
import javax.persistence.ManyToOne;
import javax.persistence.OneToMany;
import javax.persistence.OrderBy;
import javax.persistence.OrderColumn;
import javax.persistence.PrePersist;
import javax.persistence.PreUpdate;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;

import org.project.model.interfaces.DomainObject;

@Entity
public class Cv implements DomainObject {

    private static final long serialVersionUID = -9111485038728016755L;

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

    @Column(nullable = false)
    private String name;

    @ManyToOne
    @JoinColumn(name = "person_fk")
    private Person person;

    @ManyToMany
    private List<Skill> skills = new ArrayList<Skill>();

    @ManyToOne
    private Summary summary;

    @ManyToMany(targetEntity = Experience.class)
    @OrderColumn(name = "index")
    private List<Experience> experiences = new ArrayList<Experience>();

    @Temporal(TemporalType.TIMESTAMP)
    @Column(updatable = false, insertable = true)
    private Date created;

    @Temporal(TemporalType.TIMESTAMP)
    private Date lastUpdate;

    @ManyToOne(optional = false)
    private Person lastUpdatePerson;

    @Column(nullable = false)
    private Language language;

    @Column(nullable = false)
    private Company company;

    @OneToMany(orphanRemoval = true, cascade = CascadeType.ALL, targetEntity = DbFile.class)
    @OrderBy("created DESC")
    private List<DbFile> files = new ArrayList<DbFile>();

    @PrePersist
    @PreUpdate
    protected void updateDates() {
        Date now = new Date();
        if (this.created == null) {
            this.created = new Date(now.getTime());
        }
        this.lastUpdate = now;
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Person getPerson() {
        return person;
    }

    public void setPerson(Person person) {
        this.person = person;
    }

    public List<Skill> getSkills() {
        return skills;
    }

    public void setSkills(List<Skill> skills) {
        this.skills = skills;
    }

    public List<Experience> getExperiences() {
        return experiences;
    }

    public void setExperiences(List<Experience> experiences) {
        this.experiences = experiences;
    }

    public Date getCreated() {
        return created;
    }

    public void setCreated(Date created) {
        this.created = created;
    }

    public Date getLastUpdate() {
        return lastUpdate;
    }

    public void setLastUpdate(Date lastUpdate) {
        this.lastUpdate = lastUpdate;
    }

    public Person getLastUpdatePerson() {
        return lastUpdatePerson;
    }

    public void setLastUpdatePerson(Person lastUpdatePerson) {
        this.lastUpdatePerson = lastUpdatePerson;
    }

    public void addSkill(Skill skill) {
        this.skills.add(skill);
    }

    public void addExperience(Experience experience) {
        this.experiences.add(experience);
    }

    public Summary getSummary() {
        return summary;
    }

    public void setSummary(Summary summary) {
        this.summary = summary;
    }

    public List<DbFile> getFiles() {
        return files;
    }

    public void setFiles(List<DbFile> files) {
        this.files = files;
    }

    public void addFile(DbFile file) {
        this.files.add(file);
    }

    public Company getCompany() {
        return company;
    }

    public void setCompany(Company company) {
        this.company = company;
    }

    public Language getLanguage() {
        return language;
    }

    public void setLanguage(Language language) {
        this.language = language;
    }

    @Override
    public int hashCode() {
        final int prime = 31;
        int result = 1;
        result = prime * result + ((id == null) ? 0 : id.hashCode());
        return result;
    }

    @Override
    public boolean equals(Object obj) {
        if (this == obj)
            return true;
        if (obj == null)
            return false;
        if (getClass() != obj.getClass())
            return false;
        Cv other = (Cv) obj;
        if (id == null) {
            if (other.id != null)
                return false;
        } else if (!id.equals(other.id))
            return false;
        return true;
    }
}

    DbFile.java:

    package org.project.model;

import java.util.Date;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Lob;
import javax.persistence.PrePersist;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;

import org.hibernate.annotations.Type;

import org.project.model.interfaces.DomainObject;

@Entity
public class DbFile implements DomainObject {

    private static final long serialVersionUID = 1974800702358176016L;

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

    private String filename;

    @Column(nullable = false)
    @Lob
    @Type(type = "org.hibernate.type.MaterializedClobType")
    private byte[] content;

    private String contentType;

    @Temporal(TemporalType.TIMESTAMP)
    @Column(updatable = false)
    private Date created;

    @PrePersist
    protected void updateDates() {
        if (this.created == null) {
            this.created = new Date();
        }
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getFilename() {
        return filename;
    }

    public void setFilename(String filename) {
        this.filename = filename;
    }

    public byte[] getContent() {
        return content;
    }

    public void setContent(byte[] content) {
        this.content = content;
    }

    public String getContentType() {
        return contentType;
    }

    public void setContentType(String contentType) {
        this.contentType = contentType;
    }

    public Date getCreated() {
        return created;
    }

    @Override
    public int hashCode() {
        final int prime = 31;
        int result = 1;
        result = prime * result + ((id == null) ? 0 : id.hashCode());
        return result;
    }

    @Override
    public boolean equals(Object obj) {
        if (this == obj)
            return true;
        if (obj == null)
            return false;
        if (getClass() != obj.getClass())
            return false;
        DbFile other = (DbFile) obj;
        if (id == null) {
            if (other.id != null)
                return false;
        } else if (!id.equals(other.id))
            return false;
        return true;
    }
}

    Test:

    @Transactional
@RunWith(SpringJUnit4ClassRunner.class)
...
@TransactionConfiguration(transactionManager = "transactionManager", defaultRollback = true)
public class CvDaoTest extends AbstractTest {

    @Autowired
    private CvDao cvDao;

    @Test
    public void updateTest() {
        Cv cv = new Cv();
        cv.setName("test");

        ...
        cv.setLastUpdatePerson(lastUpdatePerson);

        ...
        cv.setPerson(person);
        cv.setCreated(new Date());
        cv.setLastUpdate(new Date());

        cv.setCompany(getCompany());
        cv.setLanguage(getLanguage());

        ...
        cv.addSkill(skill1);
        cv.addSkill(skill2);

        ...
        cv.addExperience(experience1);
        cv.addExperience(experience2);

        cvDao.save(cv).getId();
    }
}

    GenericDaoJpa.java

    public class GenericDaoJpa<T extends DomainObject> implements GenericDao<T> {

    private Class<T> type;
//  @PersistenceContext
    protected EntityManager entityManager;

    @PersistenceContext
    public void setEntityManager(EntityManager entityManager) {
        this.entityManager = entityManager;
    }

    public GenericDaoJpa(Class<T> type) {
        super();
        this.type = type;
    }

    @Transactional(readOnly = true)
    public T get(Long id) {
        if (id == null) {
            return null;
        } else {
            return entityManager.find(type, id);
        }
    }

    @SuppressWarnings("unchecked")
    @Transactional(readOnly = true)
    public List<T> getAll() {
        return entityManager.createQuery("select o from " + type.getName() + " o").getResultList();
    }

    @Transactional(readOnly = false)
    public T save(T object) {
        if (object.getId() != null) {
            return entityManager.merge(object);
        } else {
            entityManager.persist(object);
        }
        entityManager.flush();
        return object;
    }

    public T delete(T object) {
        entityManager.remove(object);
        return object;
    }
}

    It fails on: 'cvDao.save(cv).getId();'

    Datasource using class 'com.mchange.v2.c3p0.ComboPooledDataSource' with driver 'org.hsqldb.jdbcDriver' at url 'jdbc:hsqldb:mem:data'

    What have I done wrong?

  • Chris
    Chris over 9 years
    Petar Tahchiev provided a better answer. worked for me on HSQL 2.3.2
  • Markus Malkusch
    Markus Malkusch about 8 years
    How could you store files larger than 2G?
  • C12Z
    C12Z almost 5 years
    Thank you very much - that did it. For the record: The schema has to be recreated after the dialect change (took me a while to figure that)

Recents

Why Is PNG file with Drop Shadow in Flutter Web App Grainy?
How to troubleshoot crashes detected by Google Play Store for Flutter app
Cupertino DateTime picker interfering with scroll behaviour
Why does awk -F work for most letters, but not for the letter "t"?
Flutter change focus color and icon color but not works
How to print and connect to printer using flutter desktop via usb?
Critical issues have been reported with the following SDK versions: com.google.android.gms:play-services-safetynet:17.0.0
Flutter Dart - get localized country name from country code
navigatorState is null when using pushNamed Navigation onGenerateRoutes of GetMaterialPage
Android Sdk manager not found- Flutter doctor error
Flutter Laravel Push Notification without using any third party like(firebase,onesignal..etc)
How to change the color of ElevatedButton when entering text in TextField

Related

Configuring C3P0 in Persistence.xml with JPA and Hibernate
'hibernate.dialect' must be set when no Connection available
Store byte array in MySQL with Hibernate
@embeddable vs @entity for a mapping a collection
How to update entity using hibernate
JPA provider vs. dialect vs. vendor in the Spring contaniner configuration
Calling flush() in @Transactional method in Spring Boot application
Integrate Hibernate 5.2 with Spring framework 4.x
Cannot configure Spring Data JPA: Specified class is an interface
Configuring Spring + Hibernate JPA Transaction Manager through JTA