hbm.xml 文件中的 SQL 查询如何用源代码编写?
我正在尝试在 Hibernate 中运行 SQL 查询。我正在使用它的文档。 由于一些未知的值,我试图在源代码中执行此操作。
请参阅下面的 SQL 查询配置。如何在源代码本身中重写它! 我尝试了这个
personList = session.createSQLQuery("SELECT person.NAME, person.AGE, person.SEX, address.STREET, address.CITY, address.STATE, address.ZIP FROM person JOIN address WHERE person.ID = address.PERSON_ID").addEntity(Person.class).addEntity(Address.class).list();
我想要对上面的查询做的是,它应该映射 person.ID & PERSON_ID。如果匹配,则使用 PERSON 表列 [NAME、AGE、SEX] 获取 ADDRESS[STREET、CITY、STATE、ZIP] 表中的其他列。
在 JDBC 中,上述查询的结果集列看起来像
NAME、AGE、SEX、STREET、CITY、STATE、ZIP
但它不起作用,它说,查询中的地址列未找到。我的查询版本是否有任何语法错误!
查询映射文件中的声明;
<sql-query name="personsWith">
<return alias="person" class="eg.Person"/>
<return-join alias="address" property="person.mailingAddress"/>
SELECT person.NAME AS {person.name},
person.AGE AS {person.age},
person.SEX AS {person.sex},
address.STREET AS {address.street},
address.CITY AS {address.city},
address.STATE AS {address.state},
address.ZIP AS {address.zip}
FROM PERSON person
JOIN ADDRESS address
ON person.ID = address.PERSON_ID
</sql-query>
谢谢
I am trying to run a SQL query in Hibernate. I am using its documentation.
Because of some unknown values i am trying to do inside the Source code.
See this below SQL-Query configuration. How to re-write it in source code itself!!
I tried this
personList = session.createSQLQuery("SELECT person.NAME, person.AGE, person.SEX, address.STREET, address.CITY, address.STATE, address.ZIP FROM person JOIN address WHERE person.ID = address.PERSON_ID").addEntity(Person.class).addEntity(Address.class).list();
What i am trying to do with the above query is, it should map person.ID & PERSON_ID. If it matches, then fetch the other columns in the ADDRESS[STREET, CITY, STATE, ZIP] table with the PERSON table columns[NAME, AGE, SEX].
In JDBC the columns of resultset, for the above query looks like
NAME, AGE, SEX, STREET, CITY, STATE, ZIP
But its not working it says, address columns in the query were not found. Is there any syntax error in my version of query!!
Query Declaration in mapping file;
<sql-query name="personsWith">
<return alias="person" class="eg.Person"/>
<return-join alias="address" property="person.mailingAddress"/>
SELECT person.NAME AS {person.name},
person.AGE AS {person.age},
person.SEX AS {person.sex},
address.STREET AS {address.street},
address.CITY AS {address.city},
address.STATE AS {address.state},
address.ZIP AS {address.zip}
FROM PERSON person
JOIN ADDRESS address
ON person.ID = address.PERSON_ID
</sql-query>
Thanks
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
它不起作用,因为您误用了
AddEntity
:这段代码:
addEntity
确实向查询添加了一个实体类型的参数。例如,您可以这样做:我不确定您是否可以在代码中执行相同的实体映射。我必须阅读文档。
编辑:
您可以轻松找到具有如下地址的所有人员:
或者,如果人员上没有
地址
属性:假设您可以在相同的地址。
It does not work because of your misuse of
AddEntity
:This piece of code:
addEntity
does add a parameter to the query, of an entity type. For instance, you could do this:I'm not sure if you could do the same entity mapping in code. I had to read the documentation.
Edit:
You could easily find all the persons with addresses like this:
or, if there isn't an
adress
property on person:assumed that you could have several persons on the same address.
找不到
address
列的两个可能原因:首先,在 SQL 中,您必须编写表的名称,而不是实体名称。
其次,您的 JOIN 语句在 SQL 中可能无效。有几种方法可以实现连接。我将采用直接方法(从两个表中选择并在 where 子句中声明连接)。
假设
person
实体映射一个名为“TABLE_PERSON
”的表,address
映射表“TABLE_ADDRESS
”,有效的查询如下所示:还有一点。检查hibernate文档,我发现了这个例子:
所以,也许问题不在于查询本身,而在于您正在使用的resultSet映射,以及引用字段的方式。
让我们假设这个映射(来自 Hibernate 文档):
然后,如示例所述,您的查询应该定义大括号 (
{}
) 之间的列,并使用您在映射中定义的别名:请告诉我这些示例是否有效。
Two posible causes for not finding the
address
columns:First, in SQL you have to write the name of the table, not the entity name.
Second, your JOIN sentence could be invalid at SQL. There are a few ways to implement a join. I'll take the direct approach (select from both table and stating the join at the where clause).
Suposing that the
person
entity maps a table called "TABLE_PERSON
", andaddress
maps table "TABLE_ADDRESS
", a valid query would look as follows:One more point. Checking the hibernate documentation, I have found this example:
So, maybe the problem is not at the query itself, but on the resultSet Mapping you are using, and the way to reference the fields.
Let's suppose this mapping (from the Hibernate doc):
Then, as the exaple states, your query should define the columns between curly brackets (
{}
), and using the alias you have defined at the mapping:Please, tell me if any of this examples works.