开发者

insertupdate sql mysql and jpa entity

I am trying to run an mysql sql statement using the code below;

public void createUpdate(Tsaleline entity) {
    //Insert
    String sql = "Insert into tSaleLine (CenterId, ProductId, Price, VATRate) "
            + "Select :centerId, :productId, :price, :vatrate "
            + "wh开发者_StackOverflow社区ere (Select count(*) from tSaleLine where CenterId = :centerId and ProductId = :productId)=0; "
            //Update
            + "Update tSaleLine "
            + "set CenterId = :centerId, "
            + "ProductId = :productId, "
            + "Price = :price, "
            + "VATRate = :vatrate "
            + "where CenterId = :centerId and ProductId = :productId; ";

    Query q = getEntityManager().createNativeQuery(sql);

            q.setParameter("centerId", entity.getCenterId())
            .setParameter("productId", entity.getProductId())
            .setParameter("price", entity.getPrice())
            .setParameter("vatrate", entity.getVATRate())
            .executeUpdate();
}

}

but it rises a error :

exception

org.apache.jasper.JasperException: javax.ejb.EJBException: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute native bulk manipulation query


This will only insert if the record does not exist. Afterwards it will update price and vatrate for the specified record.

public void createUpdate(Tsaleline entity) {
    //Insert
    String sql = "Insert into tSaleLine (CenterId, ProductId, Price, VATRate) "
            + "Select :centerId, :productId, :price, :vatrate "
            + "where not exists (Select * from tSaleLine where CenterId = :centerId and ProductId = :productId); "
            //Update
            + "Update tSaleLine "
            + "set Price = :price, "
            + "VATRate = :vatrate "
            + "where CenterId = :centerId and ProductId = :productId; ";

    Query q = getEntityManager().createNativeQuery(sql);

            q.setParameter("centerId", entity.getCenterId())
            .setParameter("productId", entity.getProductId())
            .setParameter("price", entity.getPrice())
            .setParameter("vatrate", entity.getVATRate())
            .executeUpdate();
}

This is not the ideal solution, because the JPA implementation you are using should be taking care of an INSERT or UPDATE to an entity.


Thanks for your interest Marcelo, I did it kind of lame way.

            entity.Tsaleline saleline = sf.findByProductIdCenterId(Integer.parseInt(productIds[i]), Integer.parseInt(centerIds[i]));
            if (saleline != null)
            {
                saleline.setPrice(Float.parseFloat(price[i]));
                saleline.setVATRate(Float.parseFloat(vat[i]));
                sf.edit(saleline);
            }
            else
            {
                saleline = new entity.Tsaleline(Integer.parseInt(productIds[i]), Integer.parseInt(centerIds[i]), Float.parseFloat(price[i]), Float.parseFloat(vat[i]));
                sf.create(saleline);
            }
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜