SQL function with mysql

Asked

Viewed 62 times

1

I am implementing a function for a vendor search field and my sql function is in trouble, would anyone know what problem? This was a function of when using postgree, but now I’m using mysql.

 package br.com.project.bean.geral;

import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.List;

import javax.faces.model.SelectItem;

import org.hibernate.Query;
import org.springframework.stereotype.Component;

import br.com.framework.interfac.crud.InterfaceCrud;
import br.com.project.annotation.IdentificaCampoPesquisa;
import br.com.project.enums.CondicaoPesquisa;
import br.com.project.report.util.BeanReportView;
import br.com.project.report.util.UtilitariaRegex;

@Component
public abstract class BeanManagerViewAbstract extends BeanReportView{

    private static final long serialVersionUID = 1L;

    protected abstract Class<?> getClassImplement();

    protected abstract InterfaceCrud<?> getController();

    public ObjetoCampoConsulta campoPesquisaSelecionado;

    public List<SelectItem> listaCampoPesquisa;

    public abstract String condicaoAndParaPesquisa() throws Exception;

    public CondicaoPesquisa condicaoPesquisaSelecionado;

    public String valorPesquisa;


    public List<SelectItem> listaCondicaoPesquisa;

    protected String getSqlLazyQuery() throws Exception {
        StringBuilder sql = new StringBuilder();
        sql.append(" select entity from ");
        sql.append(getQueryConsulta());
        sql.append(" order by entity.");
        sql.append(campoPesquisaSelecionado.getCampoBanco());
        return sql.toString();
    }

    protected int totalRegistroConsulta() throws Exception {
        Query query = getController().obterQuery(
                "select count(entity) from " + getQueryConsulta());
        Number result = (Number) query.uniqueResult();
        return result.intValue();
    }


    private StringBuilder getQueryConsulta() throws Exception {
        valorPesquisa = new  UtilitariaRegex().retiraAcentos(valorPesquisa);
        StringBuilder sql = new StringBuilder();
        sql.append(getClassImplement().getSimpleName());
        sql.append(" entity where ");

        sql.append(" retira_acentos(upper(cast(entity.");
        sql.append(campoPesquisaSelecionado.getCampoBanco());
        sql.append(" as text))) ");

        if (condicaoPesquisaSelecionado.name().equals(
                CondicaoPesquisa.IGUAL_A.name())) {
            sql.append(" = retira_acentos(upper('");
            sql.append(valorPesquisa);
            sql.append("'))");
        } else if (condicaoPesquisaSelecionado.name().equals(
                CondicaoPesquisa.CONTEM.name())) {
            sql.append(" like retira_acentos(upper('%");
            sql.append(valorPesquisa);
            sql.append("%'))");
        } else if (condicaoPesquisaSelecionado.name().equals(
                CondicaoPesquisa.INICIA_COM.name())) {
            sql.append(" like retira_acentos(upper('");
            sql.append(valorPesquisa);
            sql.append("%'))");
        } else if (condicaoPesquisaSelecionado.name().equals(
                CondicaoPesquisa.TERMINA_COM.name())) {
            sql.append(" like retira_acentos(upper('%");
            sql.append(valorPesquisa);
            sql.append("'))");
        }
        sql.append(" ");
        sql.append(condicaoAndParaPesquisa());
        return sql;
    }


    public List<SelectItem> getListaCampoPesquisa() {
        listaCampoPesquisa = new ArrayList<SelectItem>();
        List<ObjetoCampoConsulta> listTemp = new ArrayList<ObjetoCampoConsulta>();

        for (Field field : getClassImplement().getDeclaredFields()) {

            if (field.isAnnotationPresent(IdentificaCampoPesquisa.class)) {
                String descricaoCampo = field.getAnnotation(
                        IdentificaCampoPesquisa.class).descricaoCampo();
                String descricaoCampoConsulta = field.getAnnotation(
                        IdentificaCampoPesquisa.class).campoConsulta();
                int isPrincipal = field.getAnnotation(
                        IdentificaCampoPesquisa.class).principal();

                ObjetoCampoConsulta objetoCampoConsulta = new ObjetoCampoConsulta();
                objetoCampoConsulta.setDescricao(descricaoCampo);
                objetoCampoConsulta.setCampoBanco(descricaoCampoConsulta);
                objetoCampoConsulta.setTipoClass(field.getType().getCanonicalName());
                objetoCampoConsulta.setPrincipal(isPrincipal);

                listTemp.add(objetoCampoConsulta);

            }
        }

        ordernarReverse(listTemp);

        for (ObjetoCampoConsulta objetoCampoConsulta : listTemp) {
            listaCampoPesquisa.add(new SelectItem(objetoCampoConsulta));
        }

        return listaCampoPesquisa;
    }   

    private void ordernarReverse(List<ObjetoCampoConsulta> listTemp){
        Collections.sort(listTemp, new Comparator<ObjetoCampoConsulta>() {

            @Override
            public int compare(ObjetoCampoConsulta obj_1, ObjetoCampoConsulta obj_2) {

                return obj_1.isPrincipal().compareTo(obj_2.isPrincipal());
            }
        });
    }


    public ObjetoCampoConsulta getCampoPesquisaSelecionado() {
        return campoPesquisaSelecionado;
    }

    public void setCampoPesquisaSelecionado(
            ObjetoCampoConsulta campoPesquisaSelecionado) {
        if (campoPesquisaSelecionado != null) {
            for (Field field : getClassImplement().getDeclaredFields()) {
                if (field.isAnnotationPresent(IdentificaCampoPesquisa.class)) {
                    if (campoPesquisaSelecionado.getCampoBanco().equalsIgnoreCase(field.getName())) {
                        String descricaoCampo = field.getAnnotation(IdentificaCampoPesquisa.class).descricaoCampo();
                        campoPesquisaSelecionado.setDescricao(descricaoCampo);
                        campoPesquisaSelecionado.setTipoClass(field.getType().getCanonicalName());
                        campoPesquisaSelecionado.setPrincipal(field.getAnnotation(IdentificaCampoPesquisa.class).principal());
                        break;
                    }

                }
            }
        }
        this.campoPesquisaSelecionado = campoPesquisaSelecionado;
    }


    public List<SelectItem> getListaCondicaoPesquisa() {
        listaCondicaoPesquisa = new ArrayList<SelectItem>();
        for (CondicaoPesquisa enumCp : CondicaoPesquisa.values()) {
            listaCondicaoPesquisa
                    .add(new SelectItem(enumCp, enumCp.toString()));
        }
        return listaCondicaoPesquisa; 
    }


    public void setCondicaoPesquisaSelecionado(CondicaoPesquisa condicaoPesquisaSelecionado) {
        this.condicaoPesquisaSelecionado = condicaoPesquisaSelecionado;
    }

    public CondicaoPesquisa getCondicaoPesquisaSelecionado() {
        return condicaoPesquisaSelecionado;
    }

    public void setValorPesquisa(String valorPesquisa) {
        this.valorPesquisa = valorPesquisa;
    }

    public String getValorPesquisa() {
        return valorPesquisa != null ? new  UtilitariaRegex().retiraAcentos(valorPesquisa.trim() ): "";
    }


}

error:

Why this mistake

    fornecedor0_.nome as longtext

Class uses String normal name.

    Hibernate: 
    select
        count(fornecedor0_.id) as col_0_0_ 
    from
        fornecedor fornecedor0_ 
    where
        retira_acentos(upper(cast(fornecedor0_.nome as longtext))) like retira_acentos(upper('rw%'))
65420 [http-nio-8085-exec-13] WARN org.hibernate.util.JDBCExceptionReporter - SQL Error: 1064, SQLState: 42000
65420 [http-nio-8085-exec-13] ERROR org.hibernate.util.JDBCExceptionReporter - 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 'longtext))) like retira_acentos(upper('rw%'))' at line 1
jun 15, 2018 12:10:09 AM com.sun.faces.lifecycle.InvokeApplicationPhase execute
ADVERTÊNCIA: org.hibernate.exception.SQLGrammarException: could not execute query
javax.el.ELException: org.hibernate.exception.SQLGrammarException: could not execute query
    at com.sun.el.parser.AstValue.invoke(AstValue.java:238)
    at com.sun.el.MethodExpressionImpl.invoke(MethodExpressionImpl.java:297)
    at javax.faces.event.MethodExpressionActionListener.processAction(MethodExpressionActionListener.java:149)
    at javax.faces.event.ActionEvent.processListener(ActionEvent.java:88)
    at javax.faces.component.UIComponentBase.broadcast(UIComponentBase.java:813)
    at javax.faces.component.UICommand.broadcast(UICommand.java:300)
    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:646)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:292)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
    at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
    at org.primefaces.webapp.filter.FileUploadFilter.doFilter(FileUploadFilter.java:70)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
    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.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.authentication.rememberme.RememberMeAuthenticationFilter.doFilter(RememberMeAuthenticationFilter.java:146)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
    at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:54)
    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.www.BasicAuthenticationFilter.doFilter(BasicAuthenticationFilter.java:150)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
    at org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:183)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
    at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:105)
    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.session.ConcurrentSessionFilter.doFilter(ConcurrentSessionFilter.java:125)
    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:237)
    at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:167)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
    at br.com.project.filter.FilterOpenSessionInView.doFilter(FilterOpenSessionInView.java:72)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
    at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88)
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:212)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:94)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:141)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79)
    at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:620)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:502)
    at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1132)
    at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:684)
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1539)
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1495)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
    at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
    at java.lang.Thread.run(Thread.java:745)
Caused by: org.hibernate.exception.SQLGrammarException: could not execute query
    at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:92)
    at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
    at org.hibernate.loader.Loader.doList(Loader.java:2536)
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2276)
    at org.hibernate.loader.Loader.list(Loader.java:2271)
    at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:452)
    at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:363)
    at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:196)
    at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1268)
    at org.hibernate.impl.QueryImpl.list(QueryImpl.java:102)
    at org.hibernate.impl.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:890)
    at br.com.project.bean.geral.BeanManagerViewAbstract.totalRegistroConsulta(BeanManagerViewAbstract.java:60)
    at br.com.project.bean.view.FornecedorBeanView.consultarEntidades(FornecedorBeanView.java:141)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:497)
    at com.sun.el.parser.AstValue.invoke(AstValue.java:234)
    ... 73 more
Caused by: java.sql.SQLSyntaxErrorException: 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 'longtext))) like retira_acentos(upper('rw%'))' at line 1
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:686)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:663)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:653)
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:115)
    at com.mysql.cj.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2041)
    at com.mysql.cj.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1827)
    at com.mysql.cj.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1930)
    at org.apache.tomcat.dbcp.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:82)
    at org.apache.tomcat.dbcp.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:82)
    at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
    at org.hibernate.loader.Loader.getResultSet(Loader.java:1953)
    at org.hibernate.loader.Loader.doQuery(Loader.java:802)
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:274)
    at org.hibernate.loader.Loader.doList(Loader.java:2533)
    ... 88 more

javax.faces.FacesException: org.hibernate.exception.SQLGrammarException: could not execute query
    at com.sun.faces.lifecycle.InvokeApplicationPhase.execute(InvokeApplicationPhase.java:89)
    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:646)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:292)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
    at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
    at org.primefaces.webapp.filter.FileUploadFilter.doFilter(FileUploadFilter.java:70)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
    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.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.authentication.rememberme.RememberMeAuthenticationFilter.doFilter(RememberMeAuthenticationFilter.java:146)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
    at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:54)
    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.www.BasicAuthenticationFilter.doFilter(BasicAuthenticationFilter.java:150)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
    at org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:183)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
    at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:105)
    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.session.ConcurrentSessionFilter.doFilter(ConcurrentSessionFilter.java:125)
    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:237)
    at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:167)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
    at br.com.project.filter.FilterOpenSessionInView.doFilter(FilterOpenSessionInView.java:72)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
    at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88)
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:212)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:94)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:141)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79)
    at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:620)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:502)
    at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1132)
    at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:684)
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1539)
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1495)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
    at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
    at java.lang.Thread.run(Thread.java:745)
Caused by: javax.el.ELException: org.hibernate.exception.SQLGrammarException: could not execute query
    at com.sun.el.parser.AstValue.invoke(AstValue.java:238)
    at com.sun.el.MethodExpressionImpl.invoke(MethodExpressionImpl.java:297)
    at javax.faces.event.MethodExpressionActionListener.processAction(MethodExpressionActionListener.java:149)
    at javax.faces.event.ActionEvent.processListener(ActionEvent.java:88)
    at javax.faces.component.UIComponentBase.broadcast(UIComponentBase.java:813)
    at javax.faces.component.UICommand.broadcast(UICommand.java:300)
    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)
    ... 65 more
Caused by: org.hibernate.exception.SQLGrammarException: could not execute query
    at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:92)
    at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
    at org.hibernate.loader.Loader.doList(Loader.java:2536)
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2276)
    at org.hibernate.loader.Loader.list(Loader.java:2271)
    at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:452)
    at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:363)
    at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:196)
    at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1268)
    at org.hibernate.impl.QueryImpl.list(QueryImpl.java:102)
    at org.hibernate.impl.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:890)
    at br.com.project.bean.geral.BeanManagerViewAbstract.totalRegistroConsulta(BeanManagerViewAbstract.java:60)
    at br.com.project.bean.view.FornecedorBeanView.consultarEntidades(FornecedorBeanView.java:141)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:497)
    at com.sun.el.parser.AstValue.invoke(AstValue.java:234)
    ... 73 more
Caused by: java.sql.SQLSyntaxErrorException: 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 'longtext))) like retira_acentos(upper('rw%'))' at line 1
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:686)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:663)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:653)
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:115)
    at com.mysql.cj.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2041)
    at com.mysql.cj.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1827)
    at com.mysql.cj.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1930)
    at org.apache.tomcat.dbcp.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:82)
    at org.apache.tomcat.dbcp.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:82)
    at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
    at org.hibernate.loader.Loader.getResultSet(Loader.java:1953)
    at org.hibernate.loader.Loader.doQuery(Loader.java:802)
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:274)
    at org.hibernate.loader.Loader.doList(Loader.java:2533)
    ... 88 more
  • Hello, @Dherik... can help with this problem tmb

  • 1

    Apparently condicaoAndParaPesquisa() is returning null. See this sql that was built: (...) near line 1, column 163 [select count(entity) from br.com.project.model.classes.Fornecedor entity where retira_acentos(upper(cast(entity.id as text))) like retira_acentos(upper('%1%')) null] <<-- that null here is making the query invalid

  • Hello, Diego I will check and post for you to see... I thank you from now Brow

  • @Rafaelblum, can only quote me if I am active in the question or answer. I found this question by chance rs, I was not warned of it.

  • @Rafaelblum, I suggest you open a new question to this by reversing the question to the previous problem. So it gets more organized and you will have better chances to solve your new problem :)

1 answer

1

Apparently this method:

condicaoAndParaPesquisa()

Of the following excerpt:

} else if (condicaoPesquisaSelecionado.name().equals(
        CondicaoPesquisa.TERMINA_COM.name())) {
    sql.append(" like retira_acentos(upper('%");
    sql.append(valorPesquisa);
    sql.append("'))");
}
sql.append(" ");
sql.append(condicaoAndParaPesquisa());
return sql;

Is returning null, generating an invalid JPQL:

like retira_acentos(upper('%1%')) null

Browser other questions tagged

You are not signed in. Login or sign up in order to post.