10
I’m trying to perform a mass update to update all prices of products registered in the system, adding the value passed by the user to the unit price of the registered product, according to the company logged in the system and the product category. I’m getting the following error.
Object Product:
@NotNull(message = "é Obrigatório!")
@ManyToOne
@JoinColumn(name = "categoria_id", nullable = false)
private Categoria categoria;
//Campo criado para identificar a quem pertence os dados gravados no DB
@NotNull(message = "é Obrigatório!")
@ManyToOne
@JoinColumn(name = "empresa_id")
private Empresa empresa_id;
Object Object-related category Product;
@Entity
@Table(name = "categoria")
public class Categoria implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@GeneratedValue
private Long id;
@NotEmpty(message = "é Obrigatório!")
@Column(nullable = false, length = 60)
private String descricao;
@ManyToOne
@JoinColumn(name = "categoria_pai_id")
private Categoria categoriaPai;
@OneToMany(mappedBy = "categoriaPai", cascade = CascadeType.ALL)
private List<Categoria> subcategorias = new ArrayList<>();
Método UPDATE:
//Alterar todos os preços dos produtos somando VALOR REAL ao valor cadastrado por CATEGORIA
@Transactional
public void alterarPrecosValorRealPorCategoria(ProdutoFiltro filtro){
try {
Query query = manager.createQuery("UPDATE Produto p SET p.valorUnitario = "
+ "(valorUnitario + :valorReal) WHERE p.empresa_id = :empresaLogada and "
+ "p.categoria.categoriaPai = :categoriaSelecionada");
query.setParameter("valorReal", filtro.getPrecoReal());
query.setParameter("empresaLogada", filtro.getEmpresaLogada());
query.setParameter("categoriaSelecionada", filtro.getCategoria());
query.executeUpdate();
} catch (Exception ex) {
ex.printStackTrace();
}
}
Values companiesLog, alloyReal and categorieSelected, are passed from the view through the productFilter. I’m days trying to solve!
According to the friend @Caffé who has tried to help me in this question follows more detail of the problem.
Query generated by Hibernate:
Query:
Hibernate: update produto cross join set valor_unitario=valor_unitario+? where empresa_id=? and categoria_pai_id=?
I couldn’t understand why he generates this "cross Join"
Complete error generated:
Error:
2016-02-18 13:51:18,701 WARN [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] SQL Error: 1064, SQLState: 42000
2016-02-18 13:51:18,701 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'set valor_unitario=valor_unitario+19 where empresa_id=2 and categoria_pai_id=16' at line 1
javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute statement
at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1692)
at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1602)
at org.hibernate.jpa.spi.AbstractEntityManagerImpl.throwPersistenceException(AbstractEntityManagerImpl.java:1700)
at org.hibernate.jpa.spi.AbstractQueryImpl.executeUpdate(AbstractQueryImpl.java:70)
at br.com.networkst.exatocomercial.repositorio.Produtos.alterarPrecosValorRealPorCategoria(Produtos.java:199)
at br.com.networkst.exatocomercial.repositorio.Produtos$Proxy$_$$_WeldSubclass.alterarPrecosValorRealPorCategoria(Produtos$Proxy$_$$_WeldSubclass.java)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.jboss.weld.interceptor.proxy.SimpleInterceptionChain.invokeNextInterceptor(SimpleInterceptionChain.java:85)
at org.jboss.weld.interceptor.proxy.InterceptorInvocationContext.proceed(InterceptorInvocationContext.java:127)
at br.com.networkst.exatocomercial.util.jpa.TransactionInterceptor.invoke(TransactionInterceptor.java:38)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.jboss.weld.interceptor.proxy.SimpleMethodInvocation.invoke(SimpleMethodInvocation.java:30)
at org.jboss.weld.interceptor.proxy.SimpleInterceptionChain.invokeNextInterceptor(SimpleInterceptionChain.java:69)
at org.jboss.weld.interceptor.proxy.InterceptorMethodHandler.executeInterception(InterceptorMethodHandler.java:112)
at org.jboss.weld.interceptor.proxy.InterceptorMethodHandler.invoke(InterceptorMethodHandler.java:88)
at org.jboss.weld.bean.proxy.CombinedInterceptorAndDecoratorStackMethodHandler.invoke(CombinedInterceptorAndDecoratorStackMethodHandler.java:55)
at br.com.networkst.exatocomercial.repositorio.Produtos$Proxy$_$$_WeldSubclass.alterarPrecosValorRealPorCategoria(Produtos$Proxy$_$$_WeldSubclass.java)
at br.com.networkst.exatocomercial.controle.CadastroProdutoBean.alterarPrecosTodosProdutos(CadastroProdutoBean.java:278)
at br.com.networkst.exatocomercial.controle.CadastroProdutoBean$Proxy$_$$_WeldClientProxy.alterarPrecosTodosProdutos(CadastroProdutoBean$Proxy$_$$_WeldClientProxy.java)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.apache.el.parser.AstValue.invoke(AstValue.java:278)
at org.apache.el.MethodExpressionImpl.invoke(MethodExpressionImpl.java:274)
at org.jboss.weld.util.el.ForwardingMethodExpression.invoke(ForwardingMethodExpression.java:39)
at org.jboss.weld.el.WeldMethodExpression.invoke(WeldMethodExpression.java:50)
at com.sun.faces.facelets.el.TagMethodExpression.invoke(TagMethodExpression.java:105)
at javax.faces.component.MethodBindingMethodExpressionAdapter.invoke(MethodBindingMethodExpressionAdapter.java:87)
at com.sun.faces.application.ActionListenerImpl.processAction(ActionListenerImpl.java:102)
at javax.faces.component.UICommand.broadcast(UICommand.java:315)
at javax.faces.component.UIViewRoot.broadcastEvents(UIViewRoot.java:790)
at javax.faces.component.UIViewRoot.processApplication(UIViewRoot.java:1282)
at com.sun.faces.lifecycle.InvokeApplicationPhase.execute(InvokeApplicationPhase.java:81)
at com.sun.faces.lifecycle.Phase.doPhase(Phase.java:101)
at com.sun.faces.lifecycle.LifecycleImpl.execute(LifecycleImpl.java:198)
at javax.faces.webapp.FacesServlet.service(FacesServlet.java:658)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:51)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:330)
at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:118)
at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:84)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:113)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:113)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:103)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:113)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:154)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:45)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:199)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:110)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at org.springframework.security.web.context.request.async.WebAsyncManagerIntegrationFilter.doFilterInternal(WebAsyncManagerIntegrationFilter.java:50)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:87)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:192)
at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:160)
at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:343)
at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:260)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:100)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:953)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1041)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:603)
at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:310)
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)
Caused by: org.hibernate.exception.SQLGrammarException: could not execute statement
at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:63)
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:109)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:95)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:207)
at org.hibernate.hql.internal.ast.exec.BasicExecutor.doExecute(BasicExecutor.java:91)
at org.hibernate.hql.internal.ast.exec.BasicExecutor.execute(BasicExecutor.java:60)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.executeUpdate(QueryTranslatorImpl.java:429)
at org.hibernate.engine.query.spi.HQLQueryPlan.performExecuteUpdate(HQLQueryPlan.java:364)
at org.hibernate.internal.SessionImpl.executeUpdate(SessionImpl.java:1290)
at org.hibernate.internal.QueryImpl.executeUpdate(QueryImpl.java:102)
at org.hibernate.jpa.internal.QueryImpl.internalExecuteUpdate(QueryImpl.java:358)
at org.hibernate.jpa.spi.AbstractQueryImpl.executeUpdate(AbstractQueryImpl.java:61)
... 89 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'set valor_unitario=valor_unitario+19 where empresa_id=2 and categoria_pai_id=16' at line 1
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:404)
at com.mysql.jdbc.Util.getInstance(Util.java:387)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:939)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3878)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3814)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2478)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2625)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2551)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861)
at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2073)
at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2009)
at com.mysql.jdbc.PreparedStatement.executeLargeUpdate(PreparedStatement.java:5094)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1994)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:204)
... 97 more
Fev 18, 2016 1:51:18 PM com.sun.faces.lifecycle.InvokeApplicationPhase execute
ADVERTÊNCIA: #{cadastroProdutoBean.alterarPrecosTodosProdutos}: javax.persistence.RollbackException: Transaction marked as rollbackOnly
javax.faces.FacesException: #{cadastroProdutoBean.alterarPrecosTodosProdutos}: javax.persistence.RollbackException: Transaction marked as rollbackOnly
at com.sun.faces.application.ActionListenerImpl.processAction(ActionListenerImpl.java:118)
at javax.faces.component.UICommand.broadcast(UICommand.java:315)
at javax.faces.component.UIViewRoot.broadcastEvents(UIViewRoot.java:790)
at javax.faces.component.UIViewRoot.processApplication(UIViewRoot.java:1282)
at com.sun.faces.lifecycle.InvokeApplicationPhase.execute(InvokeApplicationPhase.java:81)
at com.sun.faces.lifecycle.Phase.doPhase(Phase.java:101)
at com.sun.faces.lifecycle.LifecycleImpl.execute(LifecycleImpl.java:198)
at javax.faces.webapp.FacesServlet.service(FacesServlet.java:658)
Troubleshooting: NOTE: Read the comments to understand why of changing from createQuery to Sqlquery:
@Transactional
public void alterarPrecosValorRealPorCategoria(ProdutoFiltro filtro){
try {
Session session = HibernateUtil.getSessionFactory().openSession();
SQLQuery query = session.createSQLQuery("UPDATE produto p JOIN categoria c ON"
+ " p.categoria_id = c.id SET p.valor_unitario = (valor_unitario + :valorReal)"
+ " WHERE p.empresa_id = :empresaLogada AND c.categoria_pai_id = :categoriaSelecionada");
query.setParameter("valorReal", filtro.getPrecoReal());
query.setParameter("empresaLogada", filtro.getEmpresaLogada().getId());
query.setParameter("categoriaSelecionada", filtro.getCategoria().getId());
int quantidadeProdutosPrecosAtualizados = query.executeUpdate();
FacesUtil.addInfoMessage("Total de " + quantidadeProdutosPrecosAtualizados
+ " dados afetados!");
} catch (Exception ex) {
ex.printStackTrace();
}
Set the log to "debug" in order to display the generated full SQL command or, more simply, in the settings (properties of Entity manager Factory or persistence.xml) seven the property Hibernate.show_sql for true. In possession of the full query, test it separately from the program (using a Mysql IDE, for example) and see if it collects more clues. You can also update the question by pasting the query here.
– Caffé
Thank you, I will make these test and post for more details, I would also inform that I researched this error a lot, and even with the tips to change the Mysql drive I did not solve.
– CDaniel
A question: why don’t you use Hibernate to do this update? Ex.:
session.saveOrUpdate(objetoMapeado);– Rodrigo
@Rodrigo Because he is "tetando execute a mass update", which is different from getting each entity, updating in memory and then persisting. For a large volume of data, the cost of rescuing-upgrading-persisting may be unfeasible.
– Caffé
@Rodrigo, the reason is precisely the one mentioned by friend Caffé, my concern is with performance in large volumes.
– CDaniel
Good @Caffé, with his tip I found that the syntax error is caused by the excerpt + "p.categoria.categoriaPai = :categoriaSelected"); of my query, because p.categoria is an attribute within the product, but categoriaPai is an attribute within my Object Category. I just don’t know how to solve it. I’m sorry if I haven’t been able to explain it clearly. If you don’t understand I try to give more details.
– CDaniel
@Cdaniel Zou p.categoria.categoriaPai is the type Category while the parameter categorieSelected past is a Java type (a long indicating an Id, for example)? It would be good to update the question with the generated query and the details of how you have reached this conclusion so far. It may also be useful to show more of the statement and relationship between entities.
– Caffé
@Caffé, As for the value passed in categorieLogada already made sure that the same and the same type Category. I will update the question as recommended.
– CDaniel
The SQL generated by Hibernate is wrong and apparently it will not be able to generate this command correctly because it does not allow JOIN in update (navigation p.categoria.categoriaPai causes a Join): http://docs.jboss.org/hibernate/core4.2/manual/en-US/html_single/#batch-direct. The documentation recommends using subquery for these cases, and in subquery you can use JOIN if you need to. If you can’t do subquery for any other limitation (Hibernate or Mysql) the option is to use Nativequery in this update.
– Caffé
@Caffé, thank you so much for helping me so much, really you were correct, I learned a lot studying to solve this mistake. I used Sqlquery creating a new SQL and functional perfect.
– CDaniel
@Cdaniel Legal, I’m glad it worked out.
– Caffé
Don’t forget to document the answer and close the question...
– lpacheco