Hibernate uses from (select ......) Subquery Method

Today, the apprentice used a complex query statement .... An error is reported when the result is executed, but there is no problem in SQL Execution, so I am asking for help...

The statement hql/SQL format is as follows:

Select count (1), cxltype, sum (dzsje), sum (iperson) from (select XL. cxltype, G. iperson, (select sum (Y. dzsje) from ysklist as y where Y. cykpid = G. cregno) as dzsje from guestreg as G, XL as XL where G. xluuid = XL. UUID) as t where ...... Group by T. cxltype

An error occurred while executing the result. In the end, hql could not support the subquery method after from. I checked n pieces of information online and found that there were quite a few people who encountered this problem, however, no satisfactory answers are provided to all related questions, and most of them are not followed ....

 

A chill in the dark of hibernate, why is it so weak in support? Although this statement can be done in another way (such as creating a view or directly using SQL), it is always unwilling to do so, so I started to check the information in various places and finally found the idea, I think that since hql does not support this, we can only encapsulate this seed query as an object. Therefore, we must configure this temporary subquery object for HBM, as a result, we started to look up the HBM configuration materials. All the objects configured in HBM are class labels, and began to narrow down the scope. We started to look up the attribute materials of the class labels of hBM, several possible attributes are found. Where, Subselect, mutable, and entity-bean may all be related to temporary objects...

 

So I tried again and continued to look up the information.

FinallyHibernate reference 3.2.0 ga official Chinese Reference ManualFind some reliable information:

5.1.3. Class

You can useclassElement to define a persistence class:

<class        name="ClassName"        table="tableName"        discriminator-value="discriminator_value"        mutable="true|false"        schema="owner"        catalog="catalog"        proxy="ProxyInterface"        dynamic-update="true|false"        dynamic-insert="true|false"        select-before-update="true|false"        polymorphism="implicit|explicit"        where="arbitrary sql where condition"        persister="PersisterClass"        batch-size="N"        optimistic-lock="none|version|dirty|all"        lazy="true|false"        entity-name="EntityName"        check="arbitrary sql check condition"        rowid="rowid"        subselect="SQL expression"        abstract="true|false"        node="element-name"/>

	name(Optional): Java full-qualified names of persistence classes (or interfaces. If this attribute does not exist, Hibernate assumes that this is a non-pojo object ing.
	table(Optional) the default is the non-fully qualified name of the class.): name of the corresponding database table.
	discriminator-value(Optional) The default value is the same as the class name.) A value is used to distinguish different subclasses and is used for polymorphism. The acceptable values includenullAndnot null.
	mutable(Optional) The default value istrue): Indicates that the instance of this class is variable or immutable.
	schema(Optional): overwrite at the rootThe schema name specified in the element.
	catalog(Optional): overwrite at the rootSpecifies the catalog name in the element.
	proxy(Optional): Specifies an interface that is used as a proxy During Delayed loading. You can use the class name here.
	dynamic-update(Optional) The default value isfalse): UsedUPDATEWill be dynamically generated at runtime, and only those changed fields will be updated.
	dynamic-insert(Optional) The default value isfalse): UsedINSERTThe SQL statement will be dynamically generated at runtime and only contain non-null fields.
	select-before-update(Optional) The default value isfalse): Specify hibernate unless it is determined that the object is actually modified (if this value is true-), otherwiseNoExecute SQLUPDATEOperation. In specific scenarios (in fact, it only takes effect in the update () executed when a transient object (transient object) is associated with a new session), which indicates that hibernate willUPDATEExecute an additional SQL statement beforeSELECTOperation to determine whether to executeUPDATE.
	Polymorphism (polymorphism)(Optional) The default value isImplicit (implicit)): Whether to use implicit or explicit multi-state queries (this is only used in hibernate's specific table inheritance policy ).
	where(Optional) specify an additional SQLWHERECondition. This condition is always added when the object of this class is crawled.
	persister(Optional): specify a customClassPersister.
	batch-size(Optional) The default value is1Specifies the "batch size" (number of batch captures) used to capture instances based on identifier ).
	Optimistic-lock (Optimistic Locking)(Optional) The default value isversion): Determines the Optimistic Locking strategy.
(16)	lazy(Optional): Setlazy="false"All lazy fetching functions will be disabled (disabled ).
(17)	entity-name(Optional, the default is the class name): hibernate3 allows a class to be mapped multiple times (provided that it is mapped to different tables ), in addition, maps or XML can be used to replace Java-level object ing (that is, to implement dynamic domain models without writing persistence class-annotation ). For more information, seeSection 4.4 "dynamic model (dynamic models )"AndChapter 2XML ing.
(18)	check(Optional): This is an SQL expression used to add multi-row constraints to the automatically generated schema.Check.
(19)	rowid(Optional): hibernate can use the so-called rowids supported by the database, for example, Oracle database. If you set this optionalrowid, Hibernate can use additional fieldsrowidFast update. Rowid is the focus of this function. It represents the physical location of a tuple.
(20)	subselect(Optional): It maps an immutable and read-only entity to a subquery of a database. This is useful when you want to replace a basic table with a view, but it is best not to do so. For more information, see the following.
(21)	abstract(Optional): Used in<union-subclass>In hierarchies.

Pay attention to the red font, which is the key. Next I found the relevant content:

 

For hibernate ing, views and tables are no different because they are transparent at the data layer (Note: some databases do not support view attributes, especially when updating ). Sometimes you want to use a view, but you cannot create it in the database (for example, in the legacy schema ). In this way, you can map an immutable and read-only entity to a given SQL subquery expression:

<class name="Summary">    <subselect>        select item.name, max(bid.amount), count(*)        from item        join bid on bid.item_id = item.id        group by item.name    </subselect>    <synchronize table="item"/>    <synchronize table="bid"/>    <id name="name"/>    ...</class>

Define the tables used by this object as synchronize to ensure that auto-flush is executed correctly and no expired data is returned for queries dependent on the original object.<subselect>Both the attribute element and a nested ing element are visible.

 

Obviously, this is what I have been looking for. hibernate supports self-built views without relying on databases .. Although this statement is used to replace the view, it is actually the SQL statement of the query. Let's look at our final configuration results.

Temporary subquery view bean [the second non-default constructor is indispensable, otherwise the object cannot be created]:

1. Public class testbean {
2. Private integer ID;
3. Private string cxltype;
4. Private integer iperson;
5. Private double dzsje;
6. Public testbean (){}
8. Public testbean (string cxltype, integer iperson, double dzsje ){
9. Super ();
10. This. cxltype = cxltype;
11. This. iperson = iperson;
12. This. dzsje = dzsje;
13. }
14. Public String getcxltype (){
15. Return cxltype;
16. }
17. Public void setcxltype (string cxltype ){
18. This. cxltype = cxltype;
19. }
20. Public integer getiperson (){
21. Return iperson;
22. }
23. Public void setiperson (integer iperson ){
24. This. iperson = iperson;
25. }
26. Public double getdzsje (){
27. Return dzsje;
28. }
29. Public void setdzsje (double dzsje ){
30. This. dzsje = dzsje;
31. }
33. Public integer GETID (){
34. Return ID;
35. }
37. Public void setid (integer ID ){
38. This. ID = ID;
39. }
40. }

HBM configuration of testbean:

1. <Hibernate-mapping>
2. <Class name = "testbean" mutable = "false">
3. <Subselect>
4. Select XL. cxltype, G. iperson, (select sum (Y. dzsje) from ysklist as y where Y. cykpid = G. cregno) as dzsje
5. From guestreg as G, XL as XL
6. Where G. xluuid = XL. UUID
7. </Subselect>
8. <Synchronize table = "guestreg"/>
9. <Synchronize table = "XL"/>
10. <ID name = "ID" type = "integer">
11. <Column name = "ID"/>
12. <Generator class = "Identity"/>
13. </ID>
14. <Property name = "cxltype" type = "string">
15. <Column name = "cxltype"> </column>
16. </Property>
17. <Property name = "iperson" type = "integer">
18. <Column name = "iperson"> </column>
19. </Property>
20. <Property name = "dzsje" type = "double">
21. <Column name = "dzsje"> </column>
22. </Property>
23. </Class>
24. </Hibernate-mapping>

Hql statement:

Select T. cxltype, sum (T. dzsje), sum (T. iperson) from testbean as t where ...... Group by T. cxltype

SQL statement generated by hibernate:

Hibernate: Select testbean0 _. cxltype as col_0_0 _, sum (testbean0 _. dzsje) as col_1_0 _, sum (testbean0 _. iperson) as col_2_0 _ from (
Select XL. cxltype, G. iperson, (select sum (Y. dzsje) from ysklist as y where Y. cykpid = G. cregno) as dzsje
From guestreg as G, XL as XL
Where G. xluuid = XL. UUID
) Testbean0 _ where 1 = 1 group by testbean0 _. cxltype
This is the SQL subquery statement to be completed at the beginning of the article.

It took me half an afternoon to get it done...

 

This problem is also a long time ago I began to doubt the hql flexibility of hibernate, and I have always felt that hibernate is a weakness in Multi-table queries, however, I have never been looking for time to study it. After this problem, I have learned more about hibernate .. You can use it more securely in the future.