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

[Irliyanti Rahmadhani Lubis]

so, I'm making TableView in my Database Oracle and I mapped it in Hibernate Model class.
This is my Query View:
SELECT DISTINCT SUBMITINFO.ZIP_FILE_NAME, SUBMITINFO.LTKL_COUNT, SUBMITINFO.LTKL_COUNT_PROCESS,submitInfoDet.STATUS, SUBMITINFO.SENT_DATE, SUBMITINFO.RECEIVE_DATE, tKupuInTrans.D_TRANSAKSI FROM T_LTKL_SUBMIT_INFO submitInfo INNER JOIN T_LTKL_SUBMIT_INFO_DET submitInfoDet ON submitInfoDet.ID_SUBMIT_INFO = submitInfo.ID INNER JOIN T_LTKL tLtkl ON tLtkl.ZIP_FILE_NAME = submitInfo.ZIP_FILE_NAME INNER JOIN T_KUPU_IN tKupuIn ON tKupuIn.ID_KUPU_INCOMING = tLtkl.ID_KUPU_INCOMING INNER JOIN T_KUPU_IN_TRANSAKSI tKupuInTrans ON tKupuInTrans.ID_KUPU_INCOMING = tKupuIn.ID_KUPU_INCOMING INNER JOIN T_KUPU_OUT tKupuOut ON tKupuOut.ID_KUPU_OUTGOING = tLtkl.ID_KUPU_OUTGOING INNER JOIN T_KUPU_OUT_TRANSAKSI tKupuOutTrans ON tKupuOutTrans.ID_KUPU_OUTGOING = tKupuOut.ID_KUPU_OUTGOING INNER JOIN T_SWIFT_IN tSwiftIn ON tLtkl.ID_SWIFT_INCOMING = tSwiftIn.ID_SWIFT_INCOMING INNER JOIN T_SWIFT_IN_TRANSAKSI tSwiftInTrans ON tSwiftInTrans.ID_SWIFT_INCOMING = tSwiftIn.ID_SWIFT_INCOMING INNER JOIN T_SWIFT_OUT tSwitOut ON tSwitOut.ID_SWIFT_OUTGOING = tLtkl.ID_SWIFT_OUTGOING INNER JOIN T_SWIFT_OUT_TRANSAKSI tSwiftOutTrans ON tSwiftOutTrans.ID_SWIFT_OUTGOING = tSwitOut.ID_SWIFT_OUTGOING INNER JOIN TN_SWIFT_IN tNSwiftIn ON tNSwiftIn.ID_NON_SWIFT_INCOMING = tLtkl.ID_NON_SWIFT_INCOMING INNER JOIN TN_SWIFT_IN_TRANSAKSI tNSwiftInTrans ON tNSwiftInTrans.ID_NON_SWIFT_INCOMING = tNSwiftIn.ID_NON_SWIFT_INCOMING INNER JOIN TN_SWIFT_OUT tNSwiftOut ON tLtkl.ID_NON_SWIFT_OUTGOING = tNSwiftOut.ID_NON_SWIFT_OUTGOING INNER JOIN TN_SWIFT_OUT_TRANSAKSI tNSwiftOutTrans ON tNSwiftOutTrans.ID_NON_SWIFT_OUTGOING = tNSwiftOut.ID_NON_SWIFT_OUTGOING

My code in Class Model VLtklSubmitInfo

@Entity
 @Table(name = "vLtklSubmitInfo")
 @NamedNativeQuery(name ="vLtklSubmitInfo" ,
     query = "SELECT a FROM VLtklSubmitInfo a where dTransaksi = :dTransaksi", resultClass = VLtklSubmitInfo.class)
public class VLtklSubmitInfo implements Serializable{
   @Id
   @Column(name = "ZIP_FILE_NAME")
   private String zipFileName;
   
   @Column(name = "LTKL_COUNT")
   private Long ltklCount;
   
   @Column(name = "LTKL_COUNT_PROCESS")
   private Long ltklCountProcess;
   
   @Column(name = "STATUS")
   private Long status;
   
   @Column(name = "SENT_DATE")
   private Timestamp sendDate;
   
   @Column(name = "RECEIVE_DATE")
   private Timestamp receiveDate;
   
   @Column(name = "D_TRANSAKSI")
   private Date dTransaksi;

    public Long getLtklCount() {
        return ltklCount;
    }

    public void setLtklCount(Long ltklCount) {
        this.ltklCount = ltklCount;
    }

    public Long getLtklCountProcess() {
        return ltklCountProcess;
    }

    public void setLtklCountProcess(Long ltklCountProcess) {
        this.ltklCountProcess = ltklCountProcess;
    }

    public Long getStatus() {
        return status;
    }

    public void setStatus(Long status) {
        this.status = status;
    }

    public Timestamp getSendDate() {
        return sendDate;
    }

    public void setSendDate(Timestamp sendDate) {
        this.sendDate = sendDate;
    }

    public Timestamp getReceiveDate() {
        return receiveDate;
    }

    public void setReceiveDate(Timestamp receiveDate) {
        this.receiveDate = receiveDate;
    }

    public Date getdTransaksi() {
        return dTransaksi;
    }

    public void setdTransaksi(Date dTransaksi) {
        this.dTransaksi = dTransaksi;
    }

    public String getZipFileName() {
        return zipFileName;
    }

    public void setZipFileName(String zipFileName) {
        this.zipFileName = zipFileName;
    }

This is my Dao Impl:

  String Coba = "SELECT a FROM vLtklSubmitInfo a WHERE dTransaksi = :dTransaksi";
        Criteria HQL = getSession().createCriteria(VLtklSubmitInfo.class, "HQL");
        HQL.addOrder(Order.desc("dTransaksi"));
        HQL.add(Restrictions.between("dTransaksi", AwalDTransaksi, AkhirDTransaksi));
        
        return HQL.list();

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]

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

@Entity
@Table(name = "vLtklSubmitInfo")
public class VLtklSubmitInfo implements Serializable {
}

Why 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]

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

@Entity
@Table(name = "vLtklSubmitInfo")
public class VLtklSubmitInfo implements Serializable {
}

Why 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]

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

@Entity
@Table(name = "vLtklSubmitInfo")
public class VLtklSubmitInfo implements Serializable {
}

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:

String Coba = "SELECT a FROM v_ltkl_submit_info_list a WHERE dTransaksi = :dTransaksi";
        Criteria HQL = getSession().createCriteria(VLtklSubmitInfo.class, "HQL");
        HQL.addOrder(Order.desc("dTransaksi"));
        HQL.add(Restrictions.between("dTransaksi", AwalDTransaksi, AkhirDTransaksi));
        
        return HQL.list();

[Roel De Nijs]

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

TypedQuery<VLtklSubmitInfo> query =
  em.createQuery("SELECT a FROM vLtklSubmitInfo a", VLtklSubmitInfo.class);
List<VLtklSubmitInfo> results = query.getResultList();

[Roel De Nijs]

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]

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 :

List<VLtklSubmitInfo> vLtklSubmitInfo = getSession()
      .createQuery("SELECT a FROM VLtklSubmitInfo a WHERE dTransaksi = :dTransaksi")
      .setDate("dTransaksi", AwalDTransaksi).list();
System.out.println("print out of DAO IMPL --->"+ vLtklSubmitInfo);
return vLtklSubmitInfo;

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:

String tdate = request.getParameter("tdate");//in format date : 16/10/2015
                System.out.println("get Date from URL  -->" + tdate);
                Date firstDate = null;
                Date tanggalTransaksiAkhir = null;
                
                if (tdate != null){
                    if (!tdate.equals("")){
                        firstDate = StringHelper.toDate(tdate);
                        System.out.println("out of Date" + firstDate);
                        
                        List<VLtklSubmitInfo> ltklSubmitInfo = this.ltklSubmitInfoService.loadDTransaksi(firstDate);
                        System.out.println("result--> " + ltklSubmitInfo);
                        
                        mav.addObject("tdate", ltklSubmitInfo);
                        return mav;
                    }
                    
                }

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]

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]

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]

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 tdate

mav.addObject("tdate", ltklSubmitInfo);

That'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 use

mav.addObject("list", ltklSubmitInfo);

you would expect to see some code like this in the appropriate view (jsp file)<ul> <c:forEach var="info" items="${list}"> <li>${info.zipFileName}</li> </c:forEach> </ul>Now you should see the zipFileName for every result.

Hope it helps!
Kind regards,
Roel

[Irliyanti Rahmadhani Lubis]

Roel De Nijs wrote:

mav.addObject("list", ltklSubmitInfo);

you would expect to see some code like this in the appropriate view (jsp file)<ul> <c:forEach var="info" items="${list}"> <li>${info.zipFileName}</li> </c:forEach> </ul>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 :
<c:forEach items="${data.ltklSubmitInfoList}" var="ltklSubmit" varStatus="idx"> <tr> <td><c:out value="${ltklSubmit.zipFileName}" /> </td> <td><c:out value="${ltklSubmit.ltklCount}" /><c:set var="total" value="${total+ltklSubmit.ltklCount}" /></td> <td><c:out value="${ltklSubmit.ltklCountProcess}" /><c:set var="sum" value="${sum+ltklSubmit.ltklCountProcess}"/></td> <td><c:out value="${ltklSubmit.ltklCountSuccess}" /><c:set var="count" value="${count+ltklSubmit.ltklCountSuccess}"/></td> <td><fmt:formatDate value="${ltklSubmit.sentDate}" pattern='dd/MM/yyyy HH:mm:ss'/></td> <td><fmt:formatDate value="${ltklSubmit.receiveDate}" pattern='dd/MM/yyyy HH:mm:ss'/></td> <td></td> <td> <a href="<c:url value="/page/historyLtklView.htm?mode=search&zn=${ltklSubmit.zipFileName}<kl=&np=&tgl=&tgls=" />" style="border:0" ><img /></a> <a href="<c:url value="/page/cekSubmitJsonZip.htm?zip=${ltklSubmit.zipFileName}" />" style="border:0"><img /></a> <a href="<c:url value="/page/cekSubmitFile.htm?zip=${ltklSubmit.zipFileName}" />" style="border:0"><img /></a> </td> </tr> </c:forEach>
and for "Search" by date :
<label style="display: inline-block; width:100px;">Tanggal Terima</label> <lable style="display: inline-block; width:10px;">:</lable> <input type="text" size="10" name="rdate" id="dateValue2" placeholder="dd/MM/yyyy" value="<fmt:formatDate value="${data.filterCriteria.receiveDate}" pattern='dd/MM/yyyy'/>" /> <img id='datepick2' />

So, if I add it in controller

mav.addObject("ltklSubmitInfoList", ltklSubmitInfo);
return mav;

and in JSP <label style="display: inline-block; width:100px;">Tanggal Transaksi</label> <lable style="display: inline-block; width:10px;">:</lable> <input type="text" size="10" name="tdate" id="dateValue3" placeholder="dd/MM/yyyy" value="<fmt:formatDate value="${ltklSubmitInfoList}" pattern='dd/MM/yyyy'/>" /> <img id='datepick3' />
(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:
<input type="text" size="10" name="tdate" id="dateValue3" placeholder="dd/MM/yyyy" value="<fmt:formatDate value="${ltklSubmitInfo}" pattern='dd/MM/yyyy'/>" />

<c:forEach items="${ltklSubmitInfo}" var="ltklSubmit" varStatus="idx">

there's no data anything

[Irliyanti Rahmadhani Lubis]

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]

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 :

List<VLtklSubmitInfo> vLtklSubmitInfo = getSession()
    .createQuery("FROM VLtklSubmitInfo WHERE dTransaksi = :dTransaksi")
    .setDate("dTransaksi", AwalDTransaksi)
    .list();
System.out.println("print out of DAO IMPL --->"+ vLtklSubmitInfo);
return vLtklSubmitInfo;

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.

[Roel De Nijs]

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]

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 :

SELECT a FROM VLtklSubmitInfo a WHERE a.dTransaksiEfektif = :dTransaksiEfektif

but in database I used, SQL query:

SELECT * FROM V_LTKL_SUBMIT_INFO_LIST WHERE TO_CHAR(D_TRANSAKSI_EFEKTIF,'dd/mm/yyyy' )= '16/10/2015'

this is the screen shoot when I debug my query in database :

[Roel De Nijs]

Irliyanti Rahmadhani Lubis wrote:In hibernate I used HQL, like this :

SELECT a FROM VLtklSubmitInfo a WHERE a.dTransaksiEfektif = :dTransaksiEfektif

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]

Roel De Nijs wrote:

Irliyanti Rahmadhani Lubis wrote:In hibernate I used HQL, like this :

SELECT a FROM VLtklSubmitInfo a WHERE a.dTransaksiEfektif = :dTransaksiEfektif

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 :

SELECT DISTINCT SUBMITINFO.ID,SUBMITINFO.ZIP_FILE_NAME, SUBMITINFO.LTKL_COUNT, SUBMITINFO.LTKL_COUNT_PROCESS,submitInfoDet.STATUS, SUBMITINFO.SENT_DATE, SUBMITINFO.RECEIVE_DATE, tKupuInTrans.D_TRANSAKSI, tKupuInTrans.D_TRANSAKSI_EFEKTIF
FROM T_LTKL_SUBMIT_INFO submitInfo
INNER JOIN T_LTKL_SUBMIT_INFO_DET submitInfoDet
ON submitInfoDet.ID_SUBMIT_INFO = submitInfo."ID"
INNER JOIN T_LTKL tLtkl
ON tLtkl.ZIP_FILE_NAME = submitInfo.ZIP_FILE_NAME
LEFT JOIN T_KUPU_IN tKupuIn
ON tKupuIn.ID_KUPU_INCOMING = tLtkl.ID_KUPU_INCOMING
INNER JOIN T_KUPU_IN_TRANSAKSI tKupuInTrans
ON tKupuInTrans.ID_KUPU_INCOMING = tKupuIn.ID_KUPU_INCOMING
LEFT JOIN T_KUPU_OUT tKupuOut
ON tKupuOut.ID_KUPU_OUTGOING = tLtkl.ID_KUPU_OUTGOING
INNER JOIN T_KUPU_OUT_TRANSAKSI tKupuOutTrans
ON tKupuOutTrans.ID_KUPU_OUTGOING = tKupuOut.ID_KUPU_OUTGOING
LEFT JOIN T_SWIFT_IN tSwiftIn
ON tLtkl.ID_SWIFT_INCOMING = tSwiftIn.ID_SWIFT_INCOMING
INNER JOIN T_SWIFT_IN_TRANSAKSI tSwiftInTrans
ON tSwiftInTrans.ID_SWIFT_INCOMING = tSwiftIn.ID_SWIFT_INCOMING
LEFT JOIN T_SWIFT_OUT tSwitOut
ON tSwitOut.ID_SWIFT_OUTGOING = tLtkl.ID_SWIFT_OUTGOING
INNER JOIN T_SWIFT_OUT_TRANSAKSI tSwiftOutTrans
ON tSwiftOutTrans.ID_SWIFT_OUTGOING = tSwitOut.ID_SWIFT_OUTGOING
LEFT JOIN TN_SWIFT_IN tNSwiftIn
ON tNSwiftIn.ID_NON_SWIFT_INCOMING = tLtkl.ID_NON_SWIFT_INCOMING
INNER JOIN TN_SWIFT_IN_TRANSAKSI tNSwiftInTrans
ON tNSwiftInTrans.ID_NON_SWIFT_INCOMING = tNSwiftIn.ID_NON_SWIFT_INCOMING
LEFT JOIN TN_SWIFT_OUT tNSwiftOut
ON tLtkl.ID_NON_SWIFT_OUTGOING = tNSwiftOut.ID_NON_SWIFT_OUTGOING
INNER JOIN TN_SWIFT_OUT_TRANSAKSI tNSwiftOutTrans
ON tNSwiftOutTrans.ID_NON_SWIFT_OUTGOING = tNSwiftOut.ID_NON_SWIFT_OUTGOING

I used DISTINCT, because, without it, there's is double data in di View. But, it not select all Data in my View

[Roel De Nijs]

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]

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 :

SELECT a.ZIP_FILE_NAME, a.LTKL_COUNT, a.LTKL_COUNT_PROCESS, b.STATUS, a.SENT_DATE ,a.RECEIVE_DATE
FROM T_LTKL_SUBMIT_INFO a, T_LTKL_SUBMIT_INFO_DET b, t_ltkl c, T_KUPU_IN d, T_KUPU_IN_TRANSAKSI e
WHERE a."ID" = b.ID_SUBMIT_INFO
AND a.ZIP_FILE_NAME = c.ZIP_FILE_NAME
AND c.ID_KUPU_INCOMING = d.ID_KUPU_INCOMING
AND d.ID_KUPU_INCOMING = e.ID_KUPU_INCOMING

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

[Roel De Nijs]

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]

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.