1

I am not seeing anymore where i am doing my mistake. Working with Spring 3 and Hibernate and Mysql so far worked perfectly and was ok, but on this one it just doesn't work.

This is my class:

@Entity
@Table(name = "order")
@XmlRootElement(name = "order")
public class Order implements java.io.Serializable {

    /**
     * 
     */
    private static final long serialVersionUID = 1L;

    @Id
    @Column(name = "idOrder", length = 11)
    private int id;

    @Column(name = "latitude", nullable = true, length = 45)
    private String latitude;

    @Column(name = "longitude", nullable = true, length = 45)
    private String longitude;

    @Column(name = "orderDate", length = 30)
    @DateTimeFormat
    private Date orderDate;

    @Column(name = "city", nullable = true, length = 45)
    private String city;

    @Column(name = "country", nullable = true, length = 45)
    private String country;

    @Column(name = "street", nullable = true, length = 45)
    private String street;

    @Column(name = "streetNo", nullable = true, length = 45)
    private String streetNo;

    @Column(name = "created", length = 30)
    @DateTimeFormat
    private Date created;

    @JoinColumn(name = "idTaxi")
    @JoinTable(name= "taxi")
    @Column(name = "idTaxi", length = 11)
    private Integer idTaxi;

    @Column(name = "orderstatus", nullable = false, length = 45)
    private String orderstatus;

    @Column(name = "clientname", nullable = true, length = 45)
    private String clientName;

    //public getters and setters

}

In db, my table name is "order". I have checked all fields for typo. In persistence xml config file:

[...]
 <property name="annotatedClasses">
     <list>
           <value>test.bean.Taxi</value> 
           <value>test.bean.Order</value>
     </list>
</property>
<property name="hibernateProperties">
    <props>
        <prop key="hibernate.dialect">
            org.hibernate.dialect.MySQLDialect
        </prop>
        <prop key="hibernate.hbm2ddl.auto">create</prop>
        <prop key="lazy">true</prop>
        <prop key="hibernate.show_sql">true</prop>
        <!-- Show and print nice SQL on stdout -->  
        <prop key="hibernate.format_sql">true</prop>
        <prop key="hibernate.archive.autodetection">class</prop>
    </props>
</property>
</bean>
[...]

In DAO class:

public List<Order> showAllOpenOrders() {

    List<Order> history = new ArrayList<Order>();
    history = getHibernateTemplate().find("FROM Order o where o.orderstatus ='open'");

    return history;
}

My Error:

SEVERE: Servlet.service() for servlet [] in context with path [/] threw exception [Request processing failed; nested exception is org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute query; SQL [select order0_.idOrder as idOrder1_, order0_.city as city1_, order0_.clientname as clientname1_, order0_.country as country1_, order0_.created as created1_, order0_.idTaxi as idTaxi1_, order0_.latitude as latitude1_, order0_.longitude as longitude1_, order0_.orderDate as orderDate1_, order0_.orderstatus as orderst10_1_, order0_.street as street1_, order0_.streetNo as streetNo1_ from order order0_ where order0_.orderstatus='open']; nested exception is org.hibernate.exception.SQLGrammarException: could not execute query] with root cause
com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'order order0_ where order0_.orderstatus='open'' at line 1
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2985)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1631)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1723)
at com.mysql.jdbc.Connection.execSQL(Connection.java:3256)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1313)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1448)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1787)
at org.hibernate.loader.Loader.doQuery(Loader.java:674)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
at org.hibernate.loader.Loader.doList(Loader.java:2220)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)
at org.hibernate.loader.Loader.list(Loader.java:2099)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:378)
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:338)
at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:172)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1121)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)
at org.springframework.orm.hibernate3.HibernateTemplate$30.doInHibernate(HibernateTemplate.java:921)
at org.springframework.orm.hibernate3.HibernateTemplate$30.doInHibernate(HibernateTemplate.java:1)
at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:406)
at org.springframework.orm.hibernate3.HibernateTemplate.executeWithNativeSession(HibernateTemplate.java:374)
at org.springframework.orm.hibernate3.HibernateTemplate.find(HibernateTemplate.java:912)
at org.springframework.orm.hibernate3.HibernateTemplate.find(HibernateTemplate.java:904)
at test.rest.persistence.OrderDAOImpl.showAllOpenOrders(OrderDAOImpl.java:154)
at test.rest.services.OrderServiceImpl.showAllOpenOrders(OrderServiceImpl.java:109)
at test.rest.controller.OrderController.showAllOrders(OrderController.java:97)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.springframework.web.bind.annotation.support.HandlerMethodInvoker.invokeHandlerMethod(HandlerMethodInvoker.java:176)
at org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.invokeHandlerMethod(AnnotationMethodHandlerAdapter.java:436)
at org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.handle(AnnotationMethodHandlerAdapter.java:424)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:923)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:852)
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:882)
at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:778)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:621)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:722)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:304)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at org.springframework.orm.hibernate3.support.OpenSessionInViewFilter.doFilterInternal(OpenSessionInViewFilter.java:198)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:240)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:164)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:462)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:164)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:100)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:562)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:395)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:250)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:188)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:166)
at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:302)
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)

I have successfully mapped another class(Taxi), but this one..just doesn't work.I don't get why... Do you see something i don't? So my db table is lowecase "order" and in my Class Order i am mapping that table. But if I try to access all the records from database with status set on "open" like this:

public List<Order> showAllOpenOrders() {
    ;
    List<Order> history = new ArrayList<Order>();
    history = getHibernateTemplate().find("FROM Order o where o.orderstatus ='" + TaxiStatus.OPEN + "'");

    return history;
}

I receive an error that there is an SQL Exception.

Thank you for your time.

Improve this question

1 Answer 1

Reset to default
2

order - is reserved keyword in MySQL, and must be backquoted if it used not as part of order by

rewrite you query as:

history = getHibernateTemplate().find("FROM `Order` o where o.orderstatus ='" + TaxiStatus.OPEN + "'");
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

thank you. such a stupid mistake, i didn't see the problem with the name, even if i knew that order is keyword. Thank you again. it's works.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.