Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

 
Irliyanti Rahmadhani Lubis
Greenhorn
Posts: 27
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
so, I'm making TableView in my Database Oracle and I mapped it in Hibernate Model class.
This is my Query View:


My code in Class Model VLtklSubmitInfo


This is my Dao Impl:


But, why the source code still said "java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist"


org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.hibernate.exception.SQLGrammarException: could not execute query
org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:965)
org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:844)
javax.servlet.http.HttpServlet.service(HttpServlet.java:624)
org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:829)
javax.servlet.http.HttpServlet.service(HttpServlet.java:731)
org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
root cause

org.hibernate.exception.SQLGrammarException: could not execute query
org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
org.hibernate.loader.Loader.doList(Loader.java:2223)
org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)
org.hibernate.loader.Loader.list(Loader.java:2099)
org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:94)
org.hibernate.impl.SessionImpl.list(SessionImpl.java:1569)
org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:283)
gov.siapuppatk.dao.impl.LtklSubmitInfoDaoImpl.loadDataByDTransaksi(LtklSubmitInfoDaoImpl.java:81)
gov.siapuppatk.service.LtklSubmitInfoService.loadDTransaksi(LtklSubmitInfoService.java:120)
gov.siapuppatk.service.LtklSubmitInfoService$$FastClassByCGLIB$$d8bedee7.invoke(<generated>)
org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:701)
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:96)
org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:260)
org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:94)
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:634)
gov.siapuppatk.service.LtklSubmitInfoService$$EnhancerByCGLIB$$927e9113.loadDTransaksi(<generated>)
gov.siapuppatk.controller.CekSubmitController.viewHistoryLtkl(CekSubmitController.java:130)
sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
java.lang.reflect.Method.invoke(Method.java:606)
org.springframework.web.bind.annotation.support.HandlerMethodInvoker.invokeHandlerMethod(HandlerMethodInvoker.java:176)
org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.invokeHandlerMethod(AnnotationMethodHandlerAdapter.java:440)
org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.handle(AnnotationMethodHandlerAdapter.java:428)
org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:925)
org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:856)
org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:953)
org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:844)
javax.servlet.http.HttpServlet.service(HttpServlet.java:624)
org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:829)
javax.servlet.http.HttpServlet.service(HttpServlet.java:731)
org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
root cause

java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:440)
oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:837)
oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:445)
oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:191)
oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:523)
oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207)
oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:863)
oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1153)
oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1275)
oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3576)
oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3620)
oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1491)
org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)
org.hibernate.loader.Loader.getResultSet(Loader.java:1787)
org.hibernate.loader.Loader.doQuery(Loader.java:674)
org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
org.hibernate.loader.Loader.doList(Loader.java:2220)
org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)
org.hibernate.loader.Loader.list(Loader.java:2099)
org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:94)
org.hibernate.impl.SessionImpl.list(SessionImpl.java:1569)
org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:283)
gov.siapuppatk.dao.impl.LtklSubmitInfoDaoImpl.loadDataByDTransaksi(LtklSubmitInfoDaoImpl.java:81)
gov.siapuppatk.service.LtklSubmitInfoService.loadDTransaksi(LtklSubmitInfoService.java:120)
gov.siapuppatk.service.LtklSubmitInfoService$$FastClassByCGLIB$$d8bedee7.invoke(<generated>)
org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:701)
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:96)
org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:260)
org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:94)
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:634)
gov.siapuppatk.service.LtklSubmitInfoService$$EnhancerByCGLIB$$927e9113.loadDTransaksi(<generated>)
gov.siapuppatk.controller.CekSubmitController.viewHistoryLtkl(CekSubmitController.java:130)
sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
java.lang.reflect.Method.invoke(Method.java:606)
org.springframework.web.bind.annotation.support.HandlerMethodInvoker.invokeHandlerMethod(HandlerMethodInvoker.java:176)
org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.invokeHandlerMethod(AnnotationMethodHandlerAdapter.java:440)
org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.handle(AnnotationMethodHandlerAdapter.java:428)
org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:925)
org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:856)
org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:953)
org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:844)
javax.servlet.http.HttpServlet.service(HttpServlet.java:624)
org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:829)
javax.servlet.http.HttpServlet.service(HttpServlet.java:731)
org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I assume you have created a view vLtklSubmitInfo in your database and this view actually exists in the database.

According to the Java Persistence WikiBook you can use/map a view just like it was a table, using the @Table annotation. So that would mean your entity class looks likeWhy do you have a @NamedNativeQuery annotation with the same name as your view (vLtklSubmitInfo). And your so called "native query" is not native SQL, it's JPQL. So remove this @NamedNativeQuery annotation and try again.

Hope it helps!
Kind regards,
Roel
 
Irliyanti Rahmadhani Lubis
Greenhorn
Posts: 27
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Roel De Nijs wrote:I assume you have created a view vLtklSubmitInfo in your database and this view actually exists in the database.

According to the Java Persistence WikiBook you can use/map a view just like it was a table, using the @Table annotation. So that would mean your entity class looks likeWhy do you have a @NamedNativeQuery annotation with the same name as your view (vLtklSubmitInfo). And your so called "native query" is not native SQL, it's JPQL. So remove this @NamedNativeQuery annotation and try again.

Hope it helps!
Kind regards,
Roel


I try it, but the error is same.
If I have join table in my View, is that must be have annotation in class (like ManyToOne, etc)?
I don't know my is JPQL, so I'm using @NamedNativeQuery. I'm a beginner in Java programmer.
 
Irliyanti Rahmadhani Lubis
Greenhorn
Posts: 27
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Roel De Nijs wrote:I assume you have created a view vLtklSubmitInfo in your database and this view actually exists in the database.

According to the Java Persistence WikiBook you can use/map a view just like it was a table, using the @Table annotation. So that would mean your entity class looks like
Roel



Sorry, It's my fault, I forget to change the name View in DAO Impl. It works now. Thank's
But, my question is, if my View have JOIN Table, must I making annotation in my class? and, in my View database, there's have data, but when I running in Hibernate, there's no data, where the error?
My source code in DAO Impl, like this:
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Irliyanti Rahmadhani Lubis wrote:If I have join table in my View, is that must be have annotation in class (like ManyToOne, etc)?

No, the view is just treated like it were a table.

Irliyanti Rahmadhani Lubis wrote:I don't know my is JPQL, so I'm using @NamedNativeQuery. I'm a beginner in Java programmer.

You don't need to add the @NamedNativeQuery annotation on your entity class.

Irliyanti Rahmadhani Lubis wrote:I try it, but the error is same.

And the view is 100% correct? You can execute a query like SELECT * FROM vLtklSubmitInfo using a database client? And can you execute the same query using JDBC? And if both succeed, can you try to execute the same query but using JPQL
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Irliyanti Rahmadhani Lubis wrote:Sorry, It's my fault, I forget to change the name View in DAO Impl. It works now. Thank's

I would expect that you have to change the name of the view in your entity class as well. Because as far as I can see the variable Coba is not used in your DAO

Irliyanti Rahmadhani Lubis wrote:and, in my View database, there's have data, but when I running in Hibernate, there's no data, where the error?

Do you get an error in your application? Or does the query execute without returning results? If it's the first one, you'll have to share that error because I'm not clairvoyant. If it's the latter one, try to execute the query without adding restrictions/conditions and see if results are returned.
 
Irliyanti Rahmadhani Lubis
Greenhorn
Posts: 27
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Roel De Nijs wrote:
Irliyanti Rahmadhani Lubis wrote:and, in my View database, there's have data, but when I running in Hibernate, there's no data, where the error?

Do you get an error in your application? Or does the query execute without returning results? If it's the first one, you'll have to share that error because I'm not clairvoyant. If it's the latter one, try to execute the query without adding restrictions/conditions and see if results are returned.


Oooh. my fault again, I don't Call Coba in my query, I change my DAO Impl to :


But, there's no data. I try to see the output of my query in Log Apache, I got :
get Date from URL -->16/10/2015
out of DateFri Oct 16 00:00:00 ICT 2015
print out of DAO IMPL --->[gov.siapuppatk.model.VLtklSubmitInfo@15dbb09, gov.siapuppatk.model.VLtklSubmitInfo@15dbb09, gov.siapuppatk.model.VLtklSubmitInfo@15dbb09]
result--> [gov.siapuppatk.model.VLtklSubmitInfo@15dbb09, gov.siapuppatk.model.VLtklSubmitInfo@15dbb09, gov.siapuppatk.model.VLtklSubmitInfo@15dbb09]

Is the print out of DAO IMPL and result my data?
my code in controller like:


I trying to Debug my Apps, I got 3 data in my array List, Array List By Date
There's no error in my Log
 
Irliyanti Rahmadhani Lubis
Greenhorn
Posts: 27
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I try to see the output of my query in Log Apache, I got :
get Date from URL -->16/10/2015
out of DateFri Oct 16 00:00:00 ICT 2015
print out of DAO IMPL --->[gov.siapuppatk.model.VLtklSubmitInfo@15dbb09, gov.siapuppatk.model.VLtklSubmitInfo@15dbb09, gov.siapuppatk.model.VLtklSubmitInfo@15dbb09]
result--> [gov.siapuppatk.model.VLtklSubmitInfo@15dbb09, gov.siapuppatk.model.VLtklSubmitInfo@15dbb09, gov.siapuppatk.model.VLtklSubmitInfo@15dbb09]



Hem, I think, on controller I get the data with Array List, but in the return mav; I change it to model data type. is it the problem?
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Irliyanti Rahmadhani Lubis wrote:
get Date from URL -->16/10/2015
out of DateFri Oct 16 00:00:00 ICT 2015
print out of DAO IMPL --->[gov.siapuppatk.model.VLtklSubmitInfo@15dbb09, gov.siapuppatk.model.VLtklSubmitInfo@15dbb09, gov.siapuppatk.model.VLtklSubmitInfo@15dbb09]
result--> [gov.siapuppatk.model.VLtklSubmitInfo@15dbb09, gov.siapuppatk.model.VLtklSubmitInfo@15dbb09, gov.siapuppatk.model.VLtklSubmitInfo@15dbb09]

Based on the output the query returns three objects (see line in bold). So that means your query definitely returns results and there is definitely data!
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Irliyanti Rahmadhani Lubis wrote:Hem, I think, on controller I get the data with Array List, but in the return mav; I change it to model data type. is it the problem?

Based on the code of your controller, you are adding the result of the query to the parameter tdateThat's a little bit wierd/strange, because tdate is the same parameter you use to get a formatted date. You would expect another attribute like list or results or ...
And I don't see anything wrong with your code in the controller. Using the addObject method is the way to go. Assume you useyou would expect to see some code like this in the appropriate view (jsp file)Now you should see the zipFileName for every result.

Hope it helps!
Kind regards,
Roel
 
Irliyanti Rahmadhani Lubis
Greenhorn
Posts: 27
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Roel De Nijs wrote:you would expect to see some code like this in the appropriate view (jsp file)Now you should see the zipFileName for every result.


Hem, Okay I got it.
so, there's how to get data from controller and view in JSP.
In my previously JSP and Controller, there's is a DTO, and to view data from DTO to JSP, they use "data" as DTO.
But, now I want to add new search data by "Tanggal Transaksi" and "Tanggal Transaksi Efektif" without DTO.
this is they source code looping in JSP :

and for "Search" by date :


So, if I add it in controller
and in JSP
(Like you ask to me), there's error
Oct 28, 2015 9:20:30 AM org.apache.catalina.core.StandardWrapperValve invoke
SEVERE: Servlet.service() for servlet [siapuppatk-jsp] in context with path [/SIAPUPPATK-FE-SVN] threw exception [An exception occurred processing JSP page /pages/cekSubmitList.jsp at line 50

47:

48: <label style="display: inline-block; width:100px;">Tanggal Transaksi</label>
49: <lable style="display: inline-block; width:10px;">:</lable>
50: <input type="text" size="10" name="tdate" id="dateValue3" placeholder="dd/MM/yyyy" value="<fmt:formatDate value="${ltklSubmitInfoList}" pattern='dd/MM/yyyy'/>" />
51:
52:

53: <label style="display: inline-block; width:100px;">Tanggal Transaksi Efektif</label>


Stacktrace:] with root cause
javax.el.ELException: Cannot convert [gov.siapuppatk.model.VLtklSubmitInfo@168adcb, gov.siapuppatk.model.VLtklSubmitInfo@168adcb, gov.siapuppatk.model.VLtklSubmitInfo@168adcb] of type class java.util.ArrayList to class java.util.Date
at org.apache.el.lang.ELSupport.coerceToType(ELSupport.java:431)
at org.apache.el.ValueExpressionImpl.getValue(ValueExpressionImpl.java:186)
at org.apache.jasper.runtime.PageContextImpl.proprietaryEvaluate(PageContextImpl.java:944)
at org.apache.jsp.pages.cekSubmitList_jsp._jspx_meth_fmt_005fformatDate_005f3(cekSubmitList_jsp.java:3849)
at org.apache.jsp.pages.cekSubmitList_jsp._jspService(cekSubmitList_jsp.java:1274)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:731)
at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:439)
at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:395)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:339)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:731)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDispatcher.java:748)
at org.apache.catalina.core.ApplicationDispatcher.processRequest(ApplicationDispatcher.java:486)
at org.apache.catalina.core.ApplicationDispatcher.doForward(ApplicationDispatcher.java:411)
at org.apache.catalina.core.ApplicationDispatcher.forward(ApplicationDispatcher.java:338)
at org.springframework.web.servlet.view.InternalResourceView.renderMergedOutputModel(InternalResourceView.java:238)
at org.springframework.web.servlet.view.AbstractView.render(AbstractView.java:263)
at org.springframework.web.servlet.DispatcherServlet.render(DispatcherServlet.java:1208)
at org.springframework.web.servlet.DispatcherServlet.processDispatchResult(DispatcherServlet.java:992)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:939)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:856)
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:953)
at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:844)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:624)
at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:829)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:731)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:220)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:122)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:505)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:170)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:957)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:423)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1079)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:620)
at org.apache.tomcat.util.net.AprEndpoint$SocketProcessor.doRun(AprEndpoint.java:2516)
at org.apache.tomcat.util.net.AprEndpoint$SocketProcessor.run(AprEndpoint.java:2505)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Thread.java:745)



I try to solved it, but it's not work. I changed with this code:


there's no data anything
no data in my list view.PNG
[Thumbnail for no data in my list view.PNG]
 
Irliyanti Rahmadhani Lubis
Greenhorn
Posts: 27
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hem, Okay I got it.
so, there's how to get data from controller and view in JSP.
In my previously JSP and Controller, there's is a DTO, and to view data from DTO to JSP, they use "data" as DTO.
But, now I want to add new search data by "Tanggal Transaksi" and "Tanggal Transaksi Efektif" without DTO.


It works.
I'm making new page for select data by Tanggal Transaksi and Tanggal Transaksi Efektif and without DTO
I'm done with Tanggal Transaksi, next step, I want to solved Tanggal Transaksi Efektif, there's something wrong with query in hibernate, because, when I execute in Oracel, it's work, but not in hibernate.

Thank you, sir Roel De Nijs.
 
Irliyanti Rahmadhani Lubis
Greenhorn
Posts: 27
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I want to ask once again, sir.
I have search data by Tanggal Transaksi and Tanggal Transaksi Efektif.
The first problem, why the result from Tanggal Transaksi is same and looping just 1 row data?
query in DAO Impl :

the results it's look same, but, when I execute query in database, there's 3 different data. (where d_Transaksi = '16/10/2015')

The second problem, I have same code in Tanggal Transaksi Efektif, but when I trace and debug my code, there's no result. Meanwhile, in my database there's 3 data in column d_transaksi_efektif.
D_Transaksi.PNG
[Thumbnail for D_Transaksi.PNG]
Tanggal Transaksi.PNG
[Thumbnail for Tanggal Transaksi.PNG]
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Irliyanti Rahmadhani Lubis wrote:the results it's look same, but, when I execute query in database, there's 3 different data. (where d_Transaksi = '16/10/2015')

According to your entity class zipFileName is the unique identifier for each record, because it's annotated with @Id. But if I look at your records in the results screenshot, I see that this value is equal for every row. So that's why the same object is returned 3 times. For JPA (Hibernate) it's the same object, because each row has the same primary key.

Irliyanti Rahmadhani Lubis wrote:The second problem, I have same code in Tanggal Transaksi Efektif, but when I trace and debug my code, there's no result. Meanwhile, in my database there's 3 data in column d_transaksi_efektif.

And can you execute the query generated by Hibernate on your Oracle database and see which results are returned?
 
Irliyanti Rahmadhani Lubis
Greenhorn
Posts: 27
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Roel De Nijs wrote:
Irliyanti Rahmadhani Lubis wrote:The second problem, I have same code in Tanggal Transaksi Efektif, but when I trace and debug my code, there's no result. Meanwhile, in my database there's 3 data in column d_transaksi_efektif.

And can you execute the query generated by Hibernate on your Oracle database and see which results are returned?


In hibernate I used HQL, like this :

but in database I used, SQL query:


this is the screen shoot when I debug my query in database :
d_transaksi_efektif.PNG
[Thumbnail for d_transaksi_efektif.PNG]
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Irliyanti Rahmadhani Lubis wrote:In hibernate I used HQL, like this :

That's indeed the JPQL (or HQL) query, but that's not what I asked. Hibernate will translate your JPQL/HQL query into an actual SQL query. So this generated query is the one you are looking for. And you should execute this query on your database, not some query you created yourself.
 
Irliyanti Rahmadhani Lubis
Greenhorn
Posts: 27
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Roel De Nijs wrote:
Irliyanti Rahmadhani Lubis wrote:In hibernate I used HQL, like this :

That's indeed the JPQL (or HQL) query, but that's not what I asked. Hibernate will translate your JPQL/HQL query into an actual SQL query. So this generated query is the one you are looking for. And you should execute this query on your database, not some query you created yourself.


Oh God. another mistake again
I'm forgot to change the name of parameter String to Date.
Thank you, sir.

can I ask again? I'm making new data in my tables, there's 2 row , but when I selected it in database, why just 1 row?
this is my query :


I used DISTINCT, because, without it, there's is double data in di View. But, it not select all Data in my View
In Table.PNG
[Thumbnail for In Table.PNG]
In View.PNG
[Thumbnail for In View.PNG]
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Irliyanti Rahmadhani Lubis wrote:I used DISTINCT, because, without it, there's is double data in di View. But, it not select all Data in my View

And if you execute the view without DISTINCT, do you get all rows which you expect to have (besides having some double data)? And is it normal to have duplicate rows in your view or is there something wrong with the conditions in your join clauses?
 
Irliyanti Rahmadhani Lubis
Greenhorn
Posts: 27
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Roel De Nijs wrote:
Irliyanti Rahmadhani Lubis wrote:I used DISTINCT, because, without it, there's is double data in di View. But, it not select all Data in my View

And if you execute the view without DISTINCT, do you get all rows which you expect to have (besides having some double data)? And is it normal to have duplicate rows in your view or is there something wrong with the conditions in your join clauses?


There's something wrong with JOIN CLAUSE,
I have 15 tables to join, for the first step, I'm join 5 table :

but, when I'm adding more table, the result just 1 row, and without DISTINCT they have double row and just view 1 data (not select all data).
Is not that when we want to join table based 1 same column in 2 table (and the content in column must be same with each other)?
5 table.PNG
[Thumbnail for 5 table.PNG]
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Irliyanti Rahmadhani Lubis wrote:but, when I'm adding more table, the result just 1 row, and without DISTINCT they have double row and just view 1 data (not select all data).
Is not that when we want to join table based 1 same column in 2 table (and the content in column must be same with each other)?

You should know your data better than me. So you should know what's normal and which results are correct and which are not.

If you use joins in your queries and some rows don't exist anymore in the results after adding another table with another join condition, that mostly means there is no match in the other table to match with that column value. And therefore that record will be excluded from the results. If you still want that record to be in the final results, you'll need to user a left outer join or a right outer join. I think it's very useful to read about SQL Joins and the different types. HAving a good understanding is probably crucial in creating the appropriate SELECT statement for your view.

Hope it helps!
Kind regards,
Roel
 
Irliyanti Rahmadhani Lubis
Greenhorn
Posts: 27
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Roel De Nijs wrote:
Irliyanti Rahmadhani Lubis wrote:but, when I'm adding more table, the result just 1 row, and without DISTINCT they have double row and just view 1 data (not select all data).
Is not that when we want to join table based 1 same column in 2 table (and the content in column must be same with each other)?

You should know your data better than me. So you should know what's normal and which results are correct and which are not.

If you use joins in your queries and some rows don't exist anymore in the results after adding another table with another join condition, that mostly means there is no match in the other table to match with that column value. And therefore that record will be excluded from the results. If you still want that record to be in the final results, you'll need to user a left outer join or a right outer join. I think it's very useful to read about SQL Joins and the different types. HAving a good understanding is probably crucial in creating the appropriate SELECT statement for your view.

Hope it helps!
Kind regards,
Roel


Okay, sir.
I got it.
Thank you for your helps so far, sir.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic