警告:SQL错误:1064,SQLState: 42000

I have a problem with hibernate query.

我对hibernate查询有问题。

UPDATE: When I use this query it works like a charm:

更新:当我使用这个查询时，它的作用就像一个符咒:

public static List<Korisnik> UzmiSveKorisnike()
    {
        EntityManager em = Konekcija.getEmf().createEntityManager();

        TypedQuery<Korisnik> q = em.createQuery("select k from Korisnik k where k.isActive = 1", Korisnik.class); 
        List<Korisnik> resultList = q.getResultList();

        em.close();
        return resultList;
    }

But when I use one listed below, to select single result (like for login)...

但是当我使用下面列出的一个时，选择单个结果(比如登录)……

I get this error: WARN: SQL Error: 1064, SQLState: 42000

我得到这个错误:WARN: SQL error: 1064, SQLState: 42000

I have tried variety of ways to implement this query, but without success. I will list them at the end of the post.

我尝试了各种方法来实现这个查询，但是没有成功。我会在文章的最后列出它们。

This is the code:

这是代码:

public static Korisnik ProvjeraKorisnickihPodataka(String kIme, String kLozinka)
    {
        EntityManager em = Konekcija.getEmf().createEntityManager();

    {
        TypedQuery<Korisnik> q = em.createQuery("select k from Korisnik k where k.isActive = 1 AND k.korisnickoIme=:ime AND k.lozinka =:lozinka", Korisnik.class); 
        q.setParameter("ime", kIme);
        q.setParameter("lozinka", kLozinka);
        Korisnik provjeren = q.getSingleResult();

        em.close();

        return provjeren;

Even if I replace :ime and :lozinka with actual data like "k.korisnickoIme='msbtest' and k.lozinka = 'msbtest'" I get the same error.

即使我用“k”之类的实际数据替换:ime和:lozinka。korisnickoIme = msbtest和k。我得到了同样的错误。

Your help is appreciated.

我是感激你的帮助。

Thank you!

谢谢你！

Complete stack:

完整的堆栈:

Hibernate: select TOP(?) korisnik0_.KorisnikID as KorisnikID11_, korisnik0_.agencija_AgencijaID as agencija8_11_, korisnik0_.ime as ime11_, korisnik0_.isActive as isActive11_, korisnik0_.kontakt as kontakt11_, korisnik0_.korisnickoIme as korisnic5_11_, korisnik0_.lozinka as lozinka11_, korisnik0_.prezime as prezime11_, korisnik0_.rola_RolaID as rola9_11_ from Korisnik korisnik0_ where korisnik0_.isActive=1 and korisnik0_.korisnickoIme=? and korisnik0_.lozinka=?
Jul 05, 2013 11:45:22 AM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
WARN: SQL Error: 1064, SQLState: 42000
Jul 05, 2013 11:45:22 AM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
ERROR: 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 '.KorisnikID as KorisnikID11_, korisnik0_.agencija_AgencijaID as agencija8_11_, k' at line 1
javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: 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 '.KorisnikID as KorisnikID11_, korisnik0_.agencija_AgencijaID as agencija8_11_, k' at line 1
    at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1377)
    at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1300)
    at org.hibernate.ejb.QueryImpl.getSingleResult(QueryImpl.java:318)
    at Servis.LoginServis.ProvjeraKorisnickihPodataka(LoginServis.java:35)
    at UI.LogInFrm.do_btnPrijava_widgetSelected(LogInFrm.java:173)
    at UI.LogInFrm$1.widgetSelected(LogInFrm.java:121)
    at org.eclipse.swt.widgets.TypedListener.handleEvent(Unknown Source)
    at org.eclipse.swt.widgets.EventTable.sendEvent(Unknown Source)
    at org.eclipse.swt.widgets.Widget.sendEvent(Unknown Source)
    at org.eclipse.swt.widgets.Display.runDeferredEvents(Unknown Source)
    at org.eclipse.swt.widgets.Display.readAndDispatch(Unknown Source)
    at UI.LogInFrm.main(LogInFrm.java:44)
Caused by: org.hibernate.exception.SQLGrammarException: 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 '.KorisnikID as KorisnikID11_, korisnik0_.agencija_AgencijaID as agencija8_11_, k' at line 1
    at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:82)
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:110)
    at org.hibernate.engine.jdbc.internal.proxy.AbstractStatementProxyHandler.continueInvocation(AbstractStatementProxyHandler.java:129)
    at org.hibernate.engine.jdbc.internal.proxy.AbstractProxyHandler.invoke(AbstractProxyHandler.java:81)
    at com.sun.proxy.$Proxy10.executeQuery(Unknown Source)
    at org.hibernate.loader.Loader.getResultSet(Loader.java:1926)
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1727)
    at org.hibernate.loader.Loader.doQuery(Loader.java:852)
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:293)
    at org.hibernate.loader.Loader.doList(Loader.java:2411)
    at org.hibernate.loader.Loader.doList(Loader.java:2397)
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2227)
    at org.hibernate.loader.Loader.list(Loader.java:2222)
    at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:470)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:355)
    at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:195)
    at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1247)
    at org.hibernate.internal.QueryImpl.list(QueryImpl.java:101)
    at org.hibernate.ejb.QueryImpl.getSingleResult(QueryImpl.java:285)
    ... 9 more
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 '.KorisnikID as KorisnikID11_, korisnik0_.agencija_AgencijaID as agencija8_11_, k' at line 1
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
    at java.lang.reflect.Constructor.newInstance(Unknown Source)
    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:1053)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4096)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4028)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2490)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2651)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2734)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155)
    at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2322)
    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.hibernate.engine.jdbc.internal.proxy.AbstractStatementProxyHandler.continueInvocation(AbstractStatementProxyHandler.java:122)
    ... 25 more

mapping goes like this:

映射是这样的:

/**
 ** Class Korisnik
 **/
    @Entity
public class Korisnik implements Serializable { 
  //Fields
    @Id
    @GeneratedValue
  private Integer KorisnikID;
  private String ime;
  private String prezime;
  private String kontakt;
  private String korisnickoIme;
  private String lozinka;
  private Boolean isActive;
    @JoinColumn(nullable = false)
    @ManyToOne
  private Agencija agencija;
    @JoinColumn(nullable = false)
    @ManyToOne
  private Rola rola;

and getters and setters go...

getter和setter会…

OK, my persistence.xml file looks like this:

好的,我的持久性。xml文件如下:

<?xml version="1.0" encoding="UTF-8"?>
<persistence xmlns="http://java.sun.com/xml/ns/persistence"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" version="2.0"
    xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd">
    <persistence-unit name="MojaOznaka1" transaction-type="RESOURCE_LOCAL">
        <exclude-unlisted-classes>false</exclude-unlisted-classes>
        <properties>
            <!-- Podaci o bazi podataka -->
            <property name="javax.persistence.jdbc.url" value="jdbc:mysql://xxxxxxxxx.xxxxxxxxx.xxx/xxxxxxxxx/" />
            <property name="javax.persistence.jdbc.user" value="xxxxxx" />
            <property name="javax.persistence.jdbc.password" value="xxxxxx" />
            <property name="javax.persistence.jdbc.driver" value="com.mysql.jdbc.Driver" />


            <!-- hiberante dialect -->
            <property name="hibernate.dialect" value="org.hibernate.dialect.SQLServer2008Dialect" />
            <!-- JDBC connection pool (use the built-in) -->
            <property name="connection.pool_size" value="1" />
            <!-- Ispis SQL koda u konzolu -->
            <property name="hibernate.show_sql" value="true" />
            <!-- formatiranje SQL koda ispisanog u konzolni prozor -->
            <property name="hibernate.format_sql" value="false" />
            <!-- Vrijednost "update" kreira tabele ako nepostoje -->
                    <!-- <property name="hibernate.hbm2ddl.auto" value="update" /> -->
            <!-- Enable Hibernate's automatic session context management -->
            <property name="current_session_context_class" value="thread" />
            <!-- Disable the second-level cache -->
            <property name="cache.provider_class" value="org.hibernate.cache.NoCacheProvider" />
            <!-- automatsko prepoznavanje JPA klasa -->
            <property name="hibernate.archive.autodetection" value="class, hbm" />
        </properties>
    </persistence-unit>
</persistence>

OK, now what???

好了,现在什么? ? ?

2 个解决方案

#1

The query output you give suggests you told Hibernate to generate SQL for a MS-SQL Server (using TOP(1) to fetch the topmost row).

您给出的查询输出建议Hibernate为MS-SQL服务器生成SQL(使用TOP(1)获取最顶层的行)。

Since you're using a mysql database as a backend, that won't work. Try adding

由于使用mysql数据库作为后端，这是行不通的。尝试添加

<property name="hibernate.dialect" value="org.hibernate.dialect.MySQLDialect"/>

to your persistence.xml or wherever you configure JPA.

你的坚持不懈。xml或您配置JPA的任何地方。

As @Secko mentions in the comment, omitting the dialect altogether might even select a better (more specific) one.

正如@Secko在评论中提到的，完全省略方言甚至可以选择更好的(更具体的)方言。

#2

This error usually indicates some mapping error. Are all fields used in the where are actually fields of the "Korisnik" class? HQL uses the field names and not the column names from the database.

这个错误通常表示一些映射错误。是否所有的字段都用于“Korisnik”类的实际字段?HQL使用字段名，而不是来自数据库的列名。

#1