I am a beginner in Java EE and spring. Actually I program an application and I spent several hours on an error that I can't correct. I want specify that I look others topic that deals with this exception and I still can't solve it.
I am trying to get a list of Account 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 the table account which have a foreign key on the table client and this table has 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 doesn't work and return this exception on 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 try to compare a character varying with integer, but I verified and in my bean as in my database this two columns are integer so I don't understand why it doesn't work. I tried to cast this parameter in integer like this :
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 completly lost and I hope that someone can help me to fix this problem. Sorry if my english isn't very good and thanks for the people that take the time to read this topic.
EDITED
thanks for your answers, here the create query of my table client :
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 verified for the table account it's an integer too. I tried to write the query completely whithout use the setparameter but it's still the same problem.