How to solve this error: ERROR: operator does not exist: character variableing = integer
I am new to Java EE and spring. Actually I am programming an application and I spent several hours on a bug that I cannot fix. I want to point out that I am looking at another thread that deals with this exception and I still cannot solve it.
I am trying to get a list of accounts with this query:
@Override
public List<Account> getAccountByClient2(Client client){
LOGGER.log(Level.INFO,"customer id : " + client.getId() );
List<Account> accounts = sessionFactory.getCurrentSession()
.createQuery("from Account a where a.client = :client ")
.setParameter("client", client)
.getResultList();
LOGGER.log(Level.INFO, " size of the list : " +accounts.size());
return accounts;
}
This query is on a table account
that has a foreign key on the table client
and this table has a bean:
Bean Account
@Entity
@Table(name="account")
public class Account {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
@Column(name = "iban")
private String iban;
@JoinColumn(name="id_client")
@ManyToOne
private Client client;
@Column(name="overdraftallowed")
private Double overdraftallowed;
@JoinColumn(name="idtypecodeaccount")
@ManyToOne
private AccountType accountType;
@JoinColumn(name="id_balance")
@ManyToOne
private Balance balance;
//getters and setters
public String toString() {
return "Account [iban=" + iban + ", overdraftAllowed=" +
overdraftallowed + "]";
}
}
Bean Client
@Entity
@Table(name="client")
public class Client {
@Id
@GeneratedValue(strategy=GenerationType.IDENTITY)
@Column(name="id_client")
private Integer id;
@Column(name="gender")
private String gender;
@Column(name="statut")
private String statut;
@Column(name="first_name")
private String first_name;
@Column(name="last_name")
private String last_name;
@Column(name="income")
private float income;
@Column(name="city")
private String city;
@Column(name="street")
private String street;
@Column(name="postal_code")
private String postal_code;
@Column(name="phone_number")
private String phone_number;
@Column(name="mail")
private String mail;
@Column(name="birthday")
private Date birthday;
@Column(name="birthplace_city")
private String birthplace_city;
@Column(name="nationality")
private String nationality;
@JoinColumn(name="id_status")
@ManyToOne
private Status status;
@JoinColumn(name="id_workgroup")
@ManyToOne
private WorkGroup workGroup;
@JoinColumn(name="id_user")
@ManyToOne
private User user;
@Column(name="abnormal_transaction_service")
private boolean abnormalTransactionService;
public Client(String gender, String statut, String first_name, String last_name, float income, String city, String street, String postal_code, String phone_number, String mail, Date birthday, String birthplace_city, String nationality) {
this.gender = gender;
this.statut = statut;
this.first_name = first_name;
this.last_name = last_name;
this.income = income;
this.city = city;
this.street = street;
this.postal_code = postal_code;
this.phone_number = phone_number;
this.mail = mail;
this.birthday = birthday;
this.birthplace_city = birthplace_city;
this.nationality = nationality;
}
//getters and setters
public void setAbnormalTransactionService(boolean abnormalTransactionService) {
this.abnormalTransactionService = abnormalTransactionService;
}
}
And this query fails and returns this exception in the logs:
[2017-05-30T10:18:16.111+0200] [glassfish 4.0] [INFO] []
[edu.sodteam.dao.AccountDaoImpl] [tid: _ThreadID=23 _ThreadName=http-
listener-1(4)] [timeMillis: 1496132296111] [levelValue: 800] [[
customer id : 1]]
[2017-05-30T10:18:16.223+0200] [glassfish 4.0] [WARN] []
[org.hibernate.engine.jdbc.spi.SqlExceptionHelper] [tid: _ThreadID=23
_ThreadName=http-listener-1(4)] [timeMillis: 1496132296223] [levelValue: 900]
[[
SQL Error: 0, SQLState: 42883]]
[2017-05-30T10:18:16.224+0200] [glassfish 4.0] [ERROR] []
[org.hibernate.engine.jdbc.spi.SqlExceptionHelper] [tid: _ThreadID=23
_ThreadName=http-listener-1(4)] [timeMillis: 1496132296224] [levelValue: 1000]
[[
ERROR: operator does not exist: character varying = integer
Indice : No operator matches the given name and argument type(s). You might
need to add explicit type casts.
Position : 176]]
[2017-05-30T10:18:16.227+0200] [glassfish 4.0] [INFO] []
[org.hibernate.event.internal.DefaultLoadEventListener] [tid: _ThreadID=23
_ThreadName=http-listener-1(4)] [timeMillis: 1496132296227] [levelValue: 800]
[[
HHH000327: Error performing load command :
org.hibernate.exception.SQLGrammarException: could not extract ResultSet]]
[2017-05-30T10:18:16.332+0200] [glassfish 4.0] [WARNING] []
[javax.enterprise.web] [tid: _ThreadID=23 _ThreadName=http-listener-1(4)]
[timeMillis: 1496132296332] [levelValue: 900] [[
StandardWrapperValve[servlet-dispatcher]: Servlet.service() for servlet
servlet-dispatcher threw exception
org.postgresql.util.PSQLException: ERROR: operator does not exist: character
varying = integer
Indice : No operator matches the given name and argument type(s). You might
need to add explicit type casts.
Position : 176
at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl
.java:2182)
at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:
1911)
at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:173)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:615)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:465)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:350)
at sun.reflect.GeneratedMethodAccessor136.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.postgresql.ds.jdbc23.AbstractJdbc23PooledConnection$StatementHandler.invoke(AbstractJdbc23PooledConnection.java:453)
at com.sun.proxy.$Proxy246.executeQuery(Unknown Source)
at com.sun.gjc.spi.jdbc40.PreparedStatementWrapper40.executeQuery(PreparedStatementWrapper40.java:642)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:70)
at org.hibernate.loader.plan.exec.internal.AbstractLoadPlanBasedLoader.getResultSet(AbstractLoadPlanBasedLoader.java:434)
at org.hibernate.loader.plan.exec.internal.AbstractLoadPlanBasedLoader.executeQueryStatement(AbstractLoadPlanBasedLoader.java:186)
at org.hibernate.loader.plan.exec.internal.AbstractLoadPlanBasedLoader.executeLoad(AbstractLoadPlanBasedLoader.java:121)
at org.hibernate.loader.plan.exec.internal.AbstractLoadPlanBasedLoader.executeLoad(AbstractLoadPlanBasedLoader.java:86)
at org.hibernate.loader.entity.plan.AbstractLoadPlanBasedEntityLoader.load(AbstractLoadPlanBasedEntityLoader.java:167)
at org.hibernate.persister.entity.AbstractEntityPersister.load(AbstractEntityPersister.java:4019)
at org.hibernate.event.internal.DefaultLoadEventListener.loadFromDatasource(DefaultLoadEventListener.java:508)
at org.hibernate.event.internal.DefaultLoadEventListener.doLoad(DefaultLoadEventListener.java:478)
at org.hibernate.event.internal.DefaultLoadEventListener.load(DefaultLoadEventListener.java:219)
at org.hibernate.event.internal.DefaultLoadEventListener.proxyOrLoad(DefaultLoadEventListener.java:278)
at org.hibernate.event.internal.DefaultLoadEventListener.doOnLoad(DefaultLoadEventListener.java:121)
at org.hibernate.event.internal.DefaultLoadEventListener.onLoad(DefaultLoadEventListener.java:89)
at org.hibernate.internal.SessionImpl.fireLoad(SessionImpl.java:1222)
at org.hibernate.internal.SessionImpl.internalLoad(SessionImpl.java:1105)
at org.hibernate.type.EntityType.resolveIdentifier(EntityType.java:646)
at org.hibernate.type.EntityType.resolve(EntityType.java:431)
at org.hibernate.engine.internal.TwoPhaseLoad.doInitializeEntity(TwoPhaseLoad.java:151)
at org.hibernate.engine.internal.TwoPhaseLoad.initializeEntity(TwoPhaseLoad.java:125)
at org.hibernate.loader.Loader.initializeEntitiesAndCollections(Loader.java:1146)
at org.hibernate.loader.Loader.processResultSet(Loader.java:1005)
at org.hibernate.loader.Loader.doQuery(Loader.java:943)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:349)
at org.hibernate.loader.Loader.doList(Loader.java:2615)
at org.hibernate.loader.Loader.doList(Loader.java:2598)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2430)
at org.hibernate.loader.Loader.list(Loader.java:2425)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:502)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:371)
at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:216)
at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1473)
at org.hibernate.query.internal.AbstractProducedQuery.doList(AbstractProducedQuery.java:1426)
at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1398)
at org.hibernate.Query.getResultList(Query.java:427)
at edu.sodteam.dao.AccountDaoImpl.getAccountByClient2(AccountDaoImpl.java:116)
at edu.sodteam.service.ServiceConsultAccount.getCustomerAccount(ServiceConsultAccount.java:33)
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:498)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:333)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:282)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213)
at com.sun.proxy.$Proxy826.getCustomerAccount(Unknown Source)
at edu.sodteam.controller.ConsultAccountController.displayAccount(ConsultAccountController.java:32)
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:498)
at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:220)
at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:134)
at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:116)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:827)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:738)
at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:963)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:897)
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:970)
at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:861)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:687)
at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)
at org.apache.catalina.core.StandardWrapper.service(StandardWrapper.java:1682)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:344)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:214)
at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:197)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:256)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:214)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:316)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:160)
at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:734)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:673)
at com.sun.enterprise.web.WebPipeline.invoke(WebPipeline.java:99)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:174)
at org.apache.catalina.connector.CoyoteAdapter.doService(CoyoteAdapter.java:357)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:260)
at com.sun.enterprise.v3.services.impl.ContainerMapper.service(ContainerMapper.java:188)
at org.glassfish.grizzly.http.server.HttpHandler.runService(HttpHandler.java:191)
at org.glassfish.grizzly.http.server.HttpHandler.doHandle(HttpHandler.java:168)
at org.glassfish.grizzly.http.server.HttpServerFilter.handleRead(HttpServerFilter.java:189)
at org.glassfish.grizzly.filterchain.ExecutorResolver$9.execute(ExecutorResolver.java:119)
at org.glassfish.grizzly.filterchain.DefaultFilterChain.executeFilter(DefaultFilterChain.java:288)
at org.glassfish.grizzly.filterchain.DefaultFilterChain.executeChainPart(DefaultFilterChain.java:206)
at org.glassfish.grizzly.filterchain.DefaultFilterChain.execute(DefaultFilterChain.java:136)
at org.glassfish.grizzly.filterchain.DefaultFilterChain.process(DefaultFilterChain.java:114)
at org.glassfish.grizzly.ProcessorExecutor.execute(ProcessorExecutor.java:77)
at org.glassfish.grizzly.nio.transport.TCPNIOTransport.fireIOEvent(TCPNIOTransport.java:838)
at org.glassfish.grizzly.strategies.AbstractIOStrategy.fireIOEvent(AbstractIOStrategy.java:113)
at org.glassfish.grizzly.strategies.WorkerThreadIOStrategy.run0(WorkerThreadIOStrategy.java:115)
at org.glassfish.grizzly.strategies.WorkerThreadIOStrategy.access$100(WorkerThreadIOStrategy.java:55)
at org.glassfish.grizzly.strategies.WorkerThreadIOStrategy$WorkerThreadRunnable.run(WorkerThreadIOStrategy.java:135)
at org.glassfish.grizzly.threadpool.AbstractThreadPool$Worker.doWork(AbstractThreadPool.java:564)
at org.glassfish.grizzly.threadpool.AbstractThreadPool$Worker.run(AbstractThreadPool.java:544)
at java.lang.Thread.run(Thread.java:745)
]]
If I understand the error correctly, I am trying to compare the character changing with an integer, but I checked and in my bean as in my database, these two columns are integer, so I don't understand why it doesn't work, I tried to include this parameter to an integer as follows:
List<Account> accounts = sessionFactory.getCurrentSession()
.createQuery("from Account a where cast(a.client.id as integer) = :client")
.setParameter("client", client.getId())
.getResultList();
But it doesn't work, so I am completely lost and I hope someone can help me solve this problem. Sorry if my English is not very good and thanks the people who are in no rush to read this thread.
EDITED
thanks for your answers, create my client table query here:
CREATE TABLE public.client
(
id_client integer NOT NULL DEFAULT nextval('client_id_client_seq'::regclass),
first_name character varying(50) NOT NULL,
last_name character varying(50) NOT NULL,
gender character varying(50) NOT NULL,
statut character varying(50) NOT NULL,
income numeric(25,0),
city character varying(50) NOT NULL,
street character varying(50) NOT NULL,
postal_code character varying(5) NOT NULL,
phone_number character varying(10) NOT NULL,
mail character varying(50) NOT NULL,
birthday date,
birthplace_city character varying(50) NOT NULL,
nationality character varying(50) NOT NULL,
abnormal_transaction_service boolean,
id_user integer NOT NULL,
id_status integer NOT NULL,
id_workgroup integer NOT NULL,
CONSTRAINT prk_constraint_client PRIMARY KEY (id_client),
CONSTRAINT fk_client_id_status FOREIGN KEY (id_status)
REFERENCES public.status (id_status) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT fk_client_id_user FOREIGN KEY (id_user)
REFERENCES public."user" (id_user) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT fk_client_id_workgroup FOREIGN KEY (id_workgroup)
REFERENCES public.workgroup (id_workgroup) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT client_mail_key UNIQUE (mail)
)
As you can see it is an integer and I have verified that for the table account it is also an integer. I tried to write the query completely without using setparameter, but it's still the same problem.
+3
source to share
No one has answered this question yet
Check out similar questions: