Error updating the entire JAVA table

Question

Error updating the entire JAVA table

Navigation

#1 by (1 votes)
#2 by (1 votes)

10

I am trying to perform a mass update to update all the prices of products registered in the system, adding the value passed by the user to the unit price of the product registered, according to the company entered in the system and the product category. I'm getting the following error.

Product Object:

@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 Category related to Product Object;

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

CompanyLogada, alorReal, and SelectedItem values are passed from the view through the FilterProfile. I've been trying to solve it for days!

As advised by 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 could not understand why it generates this "cross join"

Full 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)

Problem Solution: NOTE: Read the comments to understand why the change 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();
    }

mysql java sql-update query hibernate

asked by anonymous 18.02.2016 / 14:03

2 answers

Difference between printf and format Connect webservice with SOAP in PHP

score 1 · Answer 1

Hello - I did not understand if you really solved it or had to change the approach. But in your code there is a visible error in updating the table.

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

See that in: p.value_unitario = (community_value +: true_value) you are not setting a new value but a text and by the logs your application complains about it. see what it does:

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 value_unit = value_unit + 19

That is, if you want to update the value you have to do_unit_value =: newValue and pass exactly what value you want in the query.setParameter. One option would be to redeem the old value and add it to the one generated by the application. Set only one value in your query.

Tip: Always look at the generated sql logs to see what query you are trying to apply, and pull it directly into your database

score 1 · Answer 2

Hi. I think I found the problem. It's in your query. Do as follows:

 @Transactional
    public void alterarPrecosValorRealPorCategoria(ProdutoFiltro filtro){            
        try {
            Query query = manager.createQuery("UPDATE Produto p SET p.valorUnitario = "
                + "(p.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();
        }

If you notice, before you had: SET p.valueUnit="+" (unitvalue +: true) Hibernate was trying to treat the word UnitValue as the sql command rather than the Product class attribute.