Hql join fetch

Source: Internet
Author: User
Join usage, hql method, fetch in hibernate

The following text is taken from join, join2, hql, fetch.

Join usage:

Inner join and outer join:

 

The most common (inner by default ):

Select <field to be selected> from <main data table>

<Join method> <secondary data table> [on <join rule>]

The main spirit of inner join is exclusive. Call it exclusive! That is, materials that do not match the join rule will be excluded. For example, the supplier code of a product (supplierid) in the product is not displayed in the suppliers data table, this record will be excluded.

Outer Join:

Select <field to be queried> from <left table>

<Left | right> [outer] Join <right table> On <join rule>

Outer in syntax can be omitted. For example, you can use left join or right join. In essence, outer join is inclusive! Different from the exclusive nature of inner join, the query results in left Outer Join will contain information about all left tables. If you look back, right outer join queries will contain information about all right tables.

In addition, there are all external links:

Full joinOr full outer join

The Complete External Join Operation returns all rows in the left and right tables. If a row does not match a row in another table, the selection list column of the other table contains a null value. If there are matched rows between tables, the entire result set row contains the data value of the base table.

And,

Cross join

Returns all rows in the left table. Each row in the left table is combined with all rows in the right table. Cross join is also called Cartesian product.

A cross join without a where clause will generate the Cartesian product of the table involved in the join. The number of rows in the first table multiplied by the number of rows in the second table is equal to the size of the Cartesian result set. That is to say, without the WHERE clause, if Table A has three rows of records, table B has six rows of records ::

Select a. *, B. * from Table A cross join Table B

The above statement will return 18 rows of records.

Fetch:

When we query a parent object, only the parent content by default does not contain Childs information. HBM. if lazy = "false" is set in XML, all associated Childs contents are retrieved at the same time.
The problem is that I want both the default performance of Hibernate and temporary flexibility. What should I do? This is the fetch function. We can compare the relationship between fetch and lazy = "true" to the programming and declarative transactions in a transaction. This is not accurate, but it probably means this.
Total value: Fetch is at the code level to give you an initiative to seize the opportunity.

Parent = (parent)hibernatetemplate.exe cute (New hibernatecallback (){
Public object doinhibernate (session) throws hibernateexception, sqlexception {
Query q = session. createquery (
"From parent as parent" +
"Left Outer Join fetch parent. Childs" +
"Where parent. ID =: ID"
);
Q. setparameter ("ID", new long (15 ));
Return (parent) Q. uniqueresult ();
}
});
Assert. asserttrue (parent. getchilds (). Size ()> 0 );

You can remove fetch when lazy = "true", and an exception will be reported. Of course, if lazy = "false", fetch is not required.

Hql features:

InAndBetweenMay be used as follows:

from DomesticCat cat where cat.name between 'A' and 'B'
from DomesticCat cat where cat.name in ( 'Foo', 'Bar', 'Baz' )

And the negated forms may be written

from DomesticCat cat where cat.name not between 'A' and 'B'
from DomesticCat cat where cat.name not in ( 'Foo', 'Bar', 'Baz' )

Likewise,Is nullAndIs not nullMay be used to test for null values.

Booleans may be easily used in expressions by declaring hql query substitutions in hibernate Configuration:

<property name="hibernate.query.substitutions">true 1, false 0</property>

This will replace the keywordsTrueAndFalseWith the literals1And0In the translated SQL from this hql:

from Cat cat where cat.alive = true

You may test the size of a collection with the special propertySize, Or the specialSize ()Function.

from Cat cat where cat.kittens.size > 0
from Cat cat where size(cat.kittens) > 0

For indexed collections, you may refer to the minimum and maximum indices usingMinindexAndMaxindexFunctions. Similarly, you may refer to the minimum and maximum elements of a collection of basic type usingMinelementAndMaxelementFunctions.

from Calendar cal where maxelement(cal.holidays) > current_date
from Order order where maxindex(order.items) > 100
from Order order where minelement(order.items) > 10000

The SQL FunctionsAny, some, all, exists, inAre supported when passed the element or index set of a collection (ElementsAndIndicesFunctions) or the result of a subquery (see below ).

select mother from Cat as mother, Cat as kitwhere kit in elements(foo.kittens)
select p from NameList list, Person pwhere p.name = some elements(list.names)
from Cat cat where exists elements(cat.kittens)
from Player p where 3 > all elements(p.scores)
from Show show where 'fizard' in indices(show.acts)

Note that these constructs-Size,Elements,Indices,Minindex,Maxindex,Minelement,Maxelement-May only be used in the WHERE clause in hibernate3.

Elements of indexed collections (arrays, lists, maps) may be referred to by index (in a where clause only ):

from Order order where order.items[0].id = 1234
select person from Person person, Calendar calendarwhere calendar.holidays['national day'] = person.birthDay    and person.nationality.calendar = calendar
select item from Item item, Order orderwhere order.items[ order.deliveredItemIndices[0] ] = item and order.id = 11
select item from Item item, Order orderwhere order.items[ maxindex(order.items) ] = item and order.id = 11

The expression inside[]May even be an arithmetic expression.

select item from Item item, Order orderwhere order.items[ size(order.items) - 1 ] = item

Hql also provides the built-inIndex ()Function, for elements of a one-to-least association or collection of values.

select item, index(item) from Order order     join order.items itemwhere index(item) < 5

Scalar SQL functions orted by the underlying database may be used

from DomesticCat cat where upper(cat.name) like 'FRI%'

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.