Hibernate @SqlInsert annotation getting nulls from bean and not values

Question

I am trying to insert into a mysql table using jpa + hibernate and @SQLInsert annotation. (I was trying a more elaborate insert query until I realized the basic one isn't working). The bean is below, what is happening in on entityManager.persist (or entityManager.merge), even though I set the 3 values on the bean, and log them hibernate complains that CKEY is NULL

the bean:

import java.io.Serializable;
import java.util.Calendar;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;
import javax.xml.bind.annotation.XmlElement;
import javax.xml.bind.annotation.XmlRootElement;
import org.hibernate.annotations.SQLInsert;

@Entity ( )
@Table ( name = "cachedb" )
@SQLInsert( sql="insert into cachedb ( ckey , cvalue , expiry ) VALUES ( ? , ? , ?  )")
public class CacheDb implements Serializable
{

    private static final long serialVersionUID = 1L;

    @Id ( )
    @Column ( name = "ckey" )
    private String key;

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

    @Column ( name = "expiry" )
    private Calendar expiry;

    @SuppressWarnings ( "unused" )
    private CacheDb()
    {
    }

    public CacheDb( final String _key , final String _value )
    {
        this.key = _key;
        this.value = _value;
    }

    public CacheDb( final String _key , final String _value , final int expirtyMinutes )
    {
        this.key = _key;
        this.value = _value;
        final Calendar cal = Calendar.getInstance();
        cal.add( Calendar.MINUTE , expirtyMinutes );
        this.expiry = cal;
    }

    public Calendar getExpiry()
    {
        return this.expiry;
    }

    public void setExpiry( final Calendar _expiry )
    {
        this.expiry = _expiry;
    }

    public static long getSerialversionuid()
    {
        return serialVersionUID;
    }

    public void setKey( final String _key )
    {
        this.key = _key;
    }

    public String getKey()
    {
        return this.key;
    }

    public void setIKey( final String _key )
    {
        this.key = _key;
    }

    public String getValue()
    {
        return this.value;
    }

    public void setValue( final String _value )
    {
        this.value = _value;
    }

    @Override
    public String toString()
    {
        return "CacheDb [key=" + this.key + ", value=" + this.value + ", expiry=" + this.expiry + "]";
    }
}

some sample code I use to test inserts:

import java.util.List;
import javax.persistence.Query;
import com.database.jpa.EntityUtils;

public class TestInsert
{
    public static void main(String[] args) throws Exception 
    {      
        javax.persistence.EntityManager em = null;
        String key = "KEY.TEST.08082017";
        try        
        {
            em = EntityUtils.getEntityManagerWithOutTransaction( "RLENTYMGR" );
            em.getTransaction().begin();
            final Query q = em.createQuery("select p from CacheDb p where key = ?1" );
            q.setParameter( 1 , key );
            final List<CacheDb> resultsList = q.getResultList();
            if (resultsList.size()==0)
            {
                CacheDb newRecord = new CacheDb();
                newRecord.setKey( key ); // only required column varchar(100)
                newRecord.setValue( "TESTB" ); //varchar(1000)   
                //newRecord.setExpiry(null); not needed default is null                 
                em.persist( newRecord );
                //newRecord = em.merge( newRecord );
            }
            em.getTransaction().commit();
        }
        catch(final Exception e)
        {
            e.printStackTrace();
            if (em!=null) 
            {
                em.getTransaction().rollback();
            }
        }
        finally
        {
            if (em!=null) {em.close();}
        }
    }




}

the exception:

Caused by: java.sql.BatchUpdateException: Column 'CKEY' cannot be null
    at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:2055)
    at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1467)
    at org.hibernate.engine.jdbc.batch.internal.BatchingBatch.performExecution(BatchingBatch.java:123)

Brad Turek · Accepted Answer · 2018-07-14 05:18:11Z

3

It would seem that hibernate doesn't look at the order of columns you use in @SQLInsert.

It only uses its own order—which you have to find out first by letting Hibernate generate an insert statment for you and then mimicking it in your custom @SQLInsert.

edited Jul 14, 2018 at 5:18

Brad Turek

3,0404 gold badges36 silver badges60 bronze badges

answered Aug 8, 2017 at 20:39

user1889665

Sign up to request clarification or add additional context in comments.

Comments

Pang · Accepted Answer · 2024-02-16 07:48:59Z

As @user1889665 stated, hibernate uses its own ordering of columns, as stated in the docs:

The parameter order is important and is defined by the order Hibernate handles properties. You can see the expected order by enabling debug logging, so Hibernate can print out the static SQL that is used to create, update, delete entities.

To see the expected sequence, remember to not include your custom SQL through annotations or mapping files as that will override the Hibernate generated static SQL.

Basically, you need to do this:

Remove @SQLInsert
Enable debug logging
```
logging.level.org.hibernate=DEBUG
```
Insert entity normally
```
myCrudRepository.save(myEntity)
```

Check logs to see the insert statement generated

org.hibernate.SQL : insert into MY_TABLE (notMyFirstColumn, myLastColumn, myFirstColumn) values (?, ?, ?)

Use the order from insert statement printed in logs in @SQLInsert

@SQLInsert(sql = "INSERT INTO MY_TABLE(notMyFirstColumn, myLastColumn, myFirstColumn) values (?, ?, ?)")

Collectives™ on Stack Overflow

Hibernate @SqlInsert annotation getting nulls from bean and not values

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related