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