java Hibernate@SqlInsert注释从bean中获取空值而不是值

Question

我正在尝试使用jpa+hibernate和@SQLInsert注释插入mysql表。（我尝试了一个更复杂的insert查询，直到我意识到基本的查询不起作用）。下面是在entityManager上发生的bean。persist（或entityManager.merge），即使我在bean上设置了3个值，并记录它们，hibernate仍会抱怨CKEY为空

豆子：

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 + "]";
    }
}

我用来测试插入的一些示例代码：

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();}
        }
    }




}

例外情况：

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)

Answer 1

hibernate似乎没有查看@SQLInsert中使用的列的顺序

它只使用自己的顺序，您必须先让Hibernate为您生成一个insert语句，然后在您的自定义@SQLInsert中模仿它，才能找到它

Answer 2

作为@user1889665stated，hibernate使用自己的列顺序，如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.

基本上你需要这样做：

• 删除@SQLInsert
• 启用调试日志记录

  logging.level.org.hibernate=DEBUG
  

• 正常插入实体

  myCrudRepository.save(myEntity)
  

• 检查日志以查看生成的insert语句

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

• 使用日志中打印的order from insert语句@SQLInsert

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