java - HIbernate 查询语言错误

Question

我对基于 Hibernate 的应用程序开发还很陌生。最近我在 Eclipse Juno IDE 中运行相同的代码(如下所示)。效果很好。但是当进入 Netbeans 时它不起作用。并且它显示错误为

SEVERE: 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 '.users users0_ where users0_.uname='username'' at line 1

这里我使用的是Mysql 5.5，IDE-NetBeans-7.2.1。

这里我正在执行的代码是，

Hibernate.cfg.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
  <session-factory>
    <property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>
    <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
    <property name="hibernate.connection.url">jdbc:mysql://localhost:3306/office manager?zeroDateTimeBehavior=convertToNull</property>
    <property name="hibernate.connection.username">root</property>
    <property name="hibernate.connection.password">root</property>
    <mapping resource="hibernatefiles/Users.hbm.xml"/>
  </session-factory>
</hibernate-configuration>

hibernate 映射文件:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!-- Generated Nov 6, 2012 12:42:42 PM by Hibernate Tools 3.2.1.GA -->
<hibernate-mapping>
    <class name="hibernatefiles.Users" table="users" catalog="OFFICE MANAGER">
        <id name="uid" type="java.lang.Integer">
            <column name="Uid" />
            <generator class="identity" />
        </id>
        <property name="uname" type="string">
            <column name="uname" length="50" />
        </property>
        <property name="upass" type="string">
            <column name="upass" length="30" />
        </property>
        <property name="ename" type="string">
            <column name="ename" length="100" />
        </property>
        <property name="edesg" type="string">
            <column name="edesg" length="100" />
        </property>
        <property name="contactnumber" type="java.lang.Integer">
            <column name="contactnumber" />
        </property>
    </class>
</hibernate-mapping>

Users.java(POJO文件)

package hibernatefiles;
public class Users  implements java.io.Serializable {


     private Integer uid;
     private String uname;
     private String upass;
     private String ename;
     private String edesg;
     private Integer contactnumber;

    public Users() {
    }

    public Users(String uname, String upass, String ename, String edesg, Integer contactnumber) {
       this.uname = uname;
       this.upass = upass;
       this.ename = ename;
       this.edesg = edesg;
       this.contactnumber = contactnumber;
    }

    public Integer getUid() {
        return this.uid;
    }

    public void setUid(Integer uid) {
        this.uid = uid;
    }
    public String getUname() {
        return this.uname;
    }

    public void setUname(String uname) {
        this.uname = uname;
    }
    public String getUpass() {
        return this.upass;
    }

    public void setUpass(String upass) {
        this.upass = upass;
    }
    public String getEname() {
        return this.ename;
    }

    public void setEname(String ename) {
        this.ename = ename;
    }
    public String getEdesg() {
        return this.edesg;
    }

    public void setEdesg(String edesg) {
        this.edesg = edesg;
    }
    public Integer getContactnumber() {
        return this.contactnumber;
    }

    public void setContactnumber(Integer contactnumber) {
        this.contactnumber = contactnumber;
    }

}

我正在使用查询类执行下面的 JSP 文件中的 HQL。

<%@page import="hibernatefiles.Users"%>
<%@page import="java.util.Iterator"%>
<%@page import="org.hibernate.Query"%>
<%@page import="org.hibernate.Session"%>
<%@page import="org.hibernate.Transaction"%>
<%
    String user_id = request.getParameter("uid");
    String u_pass = request.getParameter("upass");
    out.println("got it dude we get the details..."+user_id+"\n"+u_pass);

    //writing the hibernate code

    Transaction transaction;

    Session ses = hibernatefiles.NewHibernateUtil.getSessionFactory().openSession();
    try{
             transaction = ses.beginTransaction();
             String HQL = "FROM Users u WHERE u.uname='"+user_id+"'";
             Query query = ses.createQuery(HQL);
            java.util.List li = query.list();
            Iterator itera = li.iterator();
            while(itera.hasNext()){
        //getting record
                Users u= (Users) itera.next();
        out.println("user name is:"+u.getUname());
                out.println("user password is :"+u.getUpass());         

            }

    }
    catch(Exception error){
        error.printStackTrace();

    }

%>

这里是 NetBeans 日志

Nov 06, 2012 2:31:27 PM org.apache.catalina.core.StandardContext reload
INFO: Reloading Context with name [/Office_Manager] is completed
Nov 06, 2012 2:31:45 PM org.hibernate.cfg.annotations.Version <clinit>
INFO: Hibernate Annotations 3.3.1.GA
Nov 06, 2012 2:31:45 PM org.hibernate.cfg.Environment <clinit>
INFO: Hibernate 3.2.5
Nov 06, 2012 2:31:45 PM org.hibernate.cfg.Environment <clinit>
INFO: hibernate.properties not found
Nov 06, 2012 2:31:45 PM org.hibernate.cfg.Environment buildBytecodeProvider
INFO: Bytecode provider name : cglib
Nov 06, 2012 2:31:45 PM org.hibernate.cfg.Environment <clinit>
INFO: using JDK 1.4 java.sql.Timestamp handling
Nov 06, 2012 2:31:45 PM org.hibernate.cfg.Configuration configure
INFO: configuring from resource: /hibernate.cfg.xml
Nov 06, 2012 2:31:45 PM org.hibernate.cfg.Configuration getConfigurationInputStream
INFO: Configuration resource: /hibernate.cfg.xml
Nov 06, 2012 2:31:45 PM org.hibernate.cfg.Configuration addResource
INFO: Reading mappings from resource : hibernatefiles/Users.hbm.xml
Nov 06, 2012 2:31:46 PM org.hibernate.cfg.Configuration doConfigure
INFO: Configured SessionFactory: null
Nov 06, 2012 2:31:46 PM org.hibernate.cfg.HbmBinder bindRootPersistentClassCommonValues
INFO: Mapping class: hibernatefiles.Users -> users
Nov 06, 2012 2:31:46 PM org.hibernate.cfg.AnnotationConfiguration secondPassCompile
INFO: Hibernate Validator not found: ignoring
Nov 06, 2012 2:31:46 PM org.hibernate.connection.DriverManagerConnectionProvider configure
INFO: Using Hibernate built-in connection pool (not for production use!)
Nov 06, 2012 2:31:46 PM org.hibernate.connection.DriverManagerConnectionProvider configure
INFO: Hibernate connection pool size: 20
Nov 06, 2012 2:31:46 PM org.hibernate.connection.DriverManagerConnectionProvider configure
INFO: autocommit mode: false
Nov 06, 2012 2:31:46 PM org.hibernate.connection.DriverManagerConnectionProvider configure
INFO: using driver: com.mysql.jdbc.Driver at URL: jdbc:mysql://localhost:3306/office manager?zeroDateTimeBehavior=convertToNull
Nov 06, 2012 2:31:46 PM org.hibernate.connection.DriverManagerConnectionProvider configure
INFO: connection properties: {user=root, password=****}
Nov 06, 2012 2:31:47 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: RDBMS: MySQL, version: 5.5.25a
Nov 06, 2012 2:31:47 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: JDBC driver: MySQL-AB JDBC Driver, version: mysql-connector-java-5.1.18 ( Revision: tonci.grgin@oracle.com-20110930151701-jfj14ddfq48ifkfq )
Nov 06, 2012 2:31:47 PM org.hibernate.dialect.Dialect <init>
INFO: Using dialect: org.hibernate.dialect.MySQLDialect
Nov 06, 2012 2:31:47 PM org.hibernate.transaction.TransactionFactoryFactory buildTransactionFactory
INFO: Using default transaction strategy (direct JDBC transactions)
Nov 06, 2012 2:31:47 PM org.hibernate.transaction.TransactionManagerLookupFactory getTransactionManagerLookup
INFO: No TransactionManagerLookup configured (in JTA environment, use of read-write or transactional second-level cache is not recommended)
Nov 06, 2012 2:31:47 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Automatic flush during beforeCompletion(): disabled
Nov 06, 2012 2:31:47 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Automatic session close at end of transaction: disabled
Nov 06, 2012 2:31:47 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: JDBC batch size: 15
Nov 06, 2012 2:31:47 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: JDBC batch updates for versioned data: disabled
Nov 06, 2012 2:31:47 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Scrollable result sets: enabled
Nov 06, 2012 2:31:47 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: JDBC3 getGeneratedKeys(): enabled
Nov 06, 2012 2:31:47 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Connection release mode: auto
Nov 06, 2012 2:31:47 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Maximum outer join fetch depth: 2
Nov 06, 2012 2:31:47 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Default batch fetch size: 1
Nov 06, 2012 2:31:47 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Generate SQL with comments: disabled
Nov 06, 2012 2:31:47 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Order SQL updates by primary key: disabled
Nov 06, 2012 2:31:47 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Order SQL inserts for batching: disabled
Nov 06, 2012 2:31:47 PM org.hibernate.cfg.SettingsFactory createQueryTranslatorFactory
INFO: Query translator: org.hibernate.hql.ast.ASTQueryTranslatorFactory
Nov 06, 2012 2:31:47 PM org.hibernate.hql.ast.ASTQueryTranslatorFactory <init>
INFO: Using ASTQueryTranslatorFactory
Nov 06, 2012 2:31:47 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Query language substitutions: {}
Nov 06, 2012 2:31:47 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: JPA-QL strict compliance: disabled
Nov 06, 2012 2:31:47 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Second-level cache: enabled
Nov 06, 2012 2:31:47 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Query cache: disabled
Nov 06, 2012 2:31:47 PM org.hibernate.cfg.SettingsFactory createCacheProvider
INFO: Cache provider: org.hibernate.cache.NoCacheProvider
Nov 06, 2012 2:31:47 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Optimize cache for minimal puts: disabled
Nov 06, 2012 2:31:47 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Structured second-level cache entries: disabled
Nov 06, 2012 2:31:47 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Echoing all SQL to stdout
Nov 06, 2012 2:31:47 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Statistics: disabled
Nov 06, 2012 2:31:47 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Deleted entity synthetic identifier rollback: disabled
Nov 06, 2012 2:31:47 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Default entity-mode: pojo
Nov 06, 2012 2:31:47 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Named query checking : enabled
Nov 06, 2012 2:31:47 PM org.hibernate.impl.SessionFactoryImpl <init>
INFO: building session factory
Nov 06, 2012 2:31:48 PM org.hibernate.impl.SessionFactoryObjectFactory addInstance
INFO: Not binding factory to JNDI, no JNDI name configured
Nov 06, 2012 2:31:48 PM org.hibernate.util.JDBCExceptionReporter logExceptions
WARNING: SQL Error: 1064, SQLState: 42000
Nov 06, 2012 2:31:48 PM org.hibernate.util.JDBCExceptionReporter logExceptions
SEVERE: 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 '.users users0_ where users0_.uname='manikanta'' at line 1
org.hibernate.exception.SQLGrammarException: could not execute query
Hibernate: 
    select
        users0_.Uid as Uid0_,
        users0_.uname as uname0_,
        users0_.upass as upass0_,
        users0_.ename as ename0_,
        users0_.edesg as edesg0_,
        users0_.contactnumber as contactn6_0_ 
    from
        OFFICE MANAGER.users users0_ 
    where
        users0_.uname='manikanta'
    at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
    at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
    at org.hibernate.loader.Loader.doList(Loader.java:2223)
    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.apache.jsp.Employers.checking_jsp._jspService(checking_jsp.java:88)
    at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:722)
    at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:432)
    at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:390)
    at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:334)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:722)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
    at org.netbeans.modules.web.monitor.server.MonitorFilter.doFilter(MonitorFilter.java:393)
    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:225)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:169)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:168)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:98)
    at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:927)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:407)
    at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:999)
    at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:565)
    at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:309)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1110)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:603)
    at java.lang.Thread.run(Thread.java:722)
Caused by: com.mysql.jdbc.exceptions.jdbc4.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 '.users users0_ where users0_.uname='manikanta'' at line 1
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:525)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
    at com.mysql.jdbc.Util.getInstance(Util.java:386)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1052)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3609)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3541)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2002)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2163)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2624)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2127)
    at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2293)
    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)
    ... 33 more

Answer 1

问题似乎出在模式“OFFICE MANAGER”中。它有空格，所以需要加引号。 “办公室经理”真的是正确的名字吗？如果是正确的，那么你必须更改你的 hibernate 映射文件，以引用目录属性:

<class name="hibernatefiles.Users" table="users" catalog="`OFFICE MANAGER`">

但是，我不知道为什么它在 Eclipse 中有效。如果你好奇，你可以尝试在 eclipse 中再次运行它 <property name="hibernate.show_sql">true</property> <property name="hibernate.format_sql">true</property>这就是显示的查询。