命名查询的sql语句的问题

最新推荐文章于 2024-11-26 15:25:03 发布

原创最新推荐文章于 2024-11-26 15:25:03 发布 · 1.7k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#sql #hibernate #class #events #query #generator

hibernate 专栏收录该内容

37 篇文章

订阅专栏

本文探讨了在 Hibernate 中使用 SQLQuery 的常见问题及解决方案，包括如何正确构建 SQL 查询语句以避免异常，并确保数据能够被正确映射到 Java 对象。

import java.util.HashSet;

import java.util.Set;

public class Person {

private Long id;

private int age;

private String firstName;

private String lastName;

private Set events = new HashSet();

public Set getEvents() {

return events;

}

public void setEvents(Set events) {

this.events = events;

}

public int getAge() {

return age;

}

public void setAge(Integer age) {

this.age = age;

}

public String getFirstName() {

return firstName;

}

public void setFirstName(String firstName) {

this.firstName = firstName;

}

public Long getId() {

return id;

}

public void setId(Long id) {

this.id = id;

}

public String getLastName() {

return lastName;

}

public void setLastName(String lastName) {

this.lastName = lastName;

}

<?xml version="1.0"?>

<!DOCTYPE hibernate-mapping PUBLIC

"-//Hibernate/Hibernate Mapping DTD 3.0//EN"

"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping package="com.namequery.bean">

</id>

</property>

<many-to-many column="EVENT_ID"

class="com.namequery.bean.Event" />

</set>

</class>

<![CDATA[

from Person

]]>

</query>

<sql-query name="mySqlQuery">

SELECT {p.*} FROM HI_PERSON p WHERE p.age=22

</sql-query>

</hibernate-mapping>

tx = session.beginTransaction();

List people = session.getNamedQuery("mySqlQuery").list();

问题是本人开始写了

  <sql-query name="mySqlQuery">
        <return alias="p" class="Person" />
        SELECT * FROM HI_PERSON p  WHERE p.age=22
    </sql-query>

报错误如下：

Hibernate: SELECT * FROM HI_PERSON p WHERE p.age=26

org.hibernate.exception.GenericJDBCException: could not execute query

at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:82)

at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:70)

at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)

at org.hibernate.loader.Loader.doList(Loader.java:1565)

at org.hibernate.loader.Loader.list(Loader.java:1545)

at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:103)

at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1406)

at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:151)

at com.namequery.NameQueryTest.main(NameQueryTest.java:30)

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: 列名 PERSON1_0_ 无效。

at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source)

at com.microsoft.sqlserver.jdbc.SQLServerResultSet.findColumn(Unknown Source)

at com.microsoft.sqlserver.jdbc.SQLServerResultSet.getLong(Unknown Source)

若写成  <sql-query name="mySqlQuery">
        <return alias="person" class="Person" />
        SELECT {p.* } FROM HI_PERSON p  WHERE p.age=22
    </sql-query>

报

Hibernate: SELECT {p.*} FROM HI_PERSON p WHERE p.age=26

org.hibernate.exception.SQLGrammarException: could not execute query

at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:59)

at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)

at org.hibernate.loader.Loader.doList(Loader.java:1565)

at org.hibernate.loader.Loader.list(Loader.java:1545)

at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:103)

at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1406)

at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:151)

at com.namequery.NameQueryTest.main(NameQueryTest.java:30)

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: '.' 附近有语法错误。

at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Unknown Source)

at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(Unknown Source)

<sql-query name="mySqlQuery">
  <return alias="p" class="Person" />

  SELECT p.AGE as {p.age} FROM HI_PERSON p WHERE p.age=26
</sql-query>

Hibernate: SELECT p.AGE as AGE5_0_ FROM HI_PERSON p WHERE p.age=26

org.hibernate.exception.GenericJDBCException: could not execute query

at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:82)

at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:70)

at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)

at org.hibernate.loader.Loader.doList(Loader.java:1565)

at org.hibernate.loader.Loader.list(Loader.java:1545)

at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:103)

at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1406)

at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:151)

at com.namequery.NameQueryTest.main(NameQueryTest.java:30)

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: 列名 PERSON1_0_ 无效。

at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source)

at com.microsoft.sqlserver.jdbc.SQLServerResultSet.findColumn(Unknown Source)

只有改成

<sql-query name="mySqlQuery">
  <return alias="p" class="Person" />

  SELECT {p.*} FROM HI_PERSON p WHERE p.age=26
</sql-query>

才正确。

听别人说

<sql-query name="mySqlQuery">
  <return alias="p" class="Person" />

  SELECT p.AGE as {p.age} FROM HI_PERSON p WHERE p.age=26
</sql-query> 把所有的对应列写全了。也可以正确。

但本人没有去尝试