Time: 2017-1-22-16:09
--Search method
Hibernate provides the following ways to retrieve objects:
* Navigation object Map Retrieval method
Navigates to other objects based on the loaded amount object.
> Customer customer = (customer) session.get (Customer.class, 1)
Customer.getorders (); Get the customer's order
* OID Retrieval method
Objects are retrieved according to the OID of the object.
> Get ()/load ()
* Hql Search method
Use the object-oriented HQL query language.
> Query query = session.createquery (String HQL)
* QBC Search method
Use the QBC API to retrieve objects, which encapsulate query statements based on string literals and provide a more object-oriented query interface.
> Criteria = Session.createcriteria (Customer.class)
* Local SQL Retrieval method
SQL query statement that uses the local database.
> SQLQuery quer = session.createsqlquery (String sql)
--hql
1, HQL (Hibernate query Language) is an object-oriented query language, it is similar to the SQL query language, in the various retrieval methods provided by Hibernate, HQL is the most widely used as a retrieval method, it has the following functions:
* Set various query conditions in the query statement
* Supports projection queries, that is, retrieving only part of an object's properties
* Support Paging Query
* Support Connection (multi-table) query
* Support for group queries, allowing the having and group by keywords to be used
* provides built-in functions such as SUM (), MIN (), MAX ()
* Ability to invoke user-defined SQL functions or standard SQL functions
* Support Sub-query
* Supports dynamic binding parameters
2, Hql Search method includes the following steps
* Create a Query object with the CreateQuery () method of the session, which includes a HQL queries statement that can contain named parameters in HQL query statements.
* Dynamic Binding parameters
* Call query's list () method to execute a query statement that returns a query result of type java.util.List that holds the persisted object for the compound query condition in the list collection.
3. The query interface supports the method call chain programming style, and its setxxx () method returns its own instance rather than the void type
4. The difference between HQL and SQL
1) HQL query statements are object-oriented, hibernate is responsible for parsing HQL query statements, and then according to the mapping information in the object-relational mapping file, the HQL query statements are translated into the corresponding SQL statements, hql the subject in the query statement is the domain model class and class properties.
2) The SQL query statement is bound to a relational database, and the principal in the SQL query statement is the field of the database table and table.
Example code:
1) Use HQL to query all data
Use HQL to query all customer information
Query query = Session.createquery ("from Customer"); list<customer> list = Query.list (); print (list);
2) query using aliases
Using an alias query
list<customer> list = Session.createquery ("From Customer as C"). List (); print (list); Use alias query: Conditional query, as keyword can omit//does not support the syntax of SELECT * FROM table, but can use: Select alias from table as alias query query = session.create Query ("from Customer as C where c.cname =?"); Query.setstring (0, "Zhang San"); In addition to SetString (), can also Setparameter () query.setparameter (0, "Zhang San"); list<customer> list = Query.list (); print (list);
3) Multi-state query
All subclasses of the specified class are queried in the configuration file.
Query query = Session.createquery ("from Java.lang.Object"); Print (Query.list ());
4) Sort the results of the query
list<customer> list = Session.createquery ("from Customer as C order by c.id Desc"). List (); print (list);
5) Paging Query
Use HQL for paging queries query query = Session.createquery ("from Order"); Query.setfirstresult (0); Query.setmaxresults (11); list<order> list = Query.list (); print (list);
6) querying a single record
Querying a single record using hql query query = Session.createquery ("from Customer where cname =?"); Query.setstring (0, "John Doe"); When the result exceeds 1, the Uniqueresult () method will error the customer customer = (customer) query.uniqueresult (); SYSTEM.OUT.PRINTLN (customer);
7) Projection Query
/* * Query only the name of the customer * if you query only one property, the return is a string * If you query multiple properties, an object array is returned *//query query = Session.createquery ("Select C.cid, C.cname from Customer as C"); When querying an attribute, the return string//list<string> = Query.list (); Returns an array when querying multiple properties//list<object[]> List = Query.list (); /* * Encapsulate Query records into objects * Construct method that provides a corresponding parameter for the entity class
* Public Customer (Integer CID, String CNAME) {...} */Query query = Session.createquery ("Select New Customer (CID, CNAME) from customer"); list<customer> list = Query.list (); print (list);
8) binding parameters, making conditional queries
Use the? Mode binding parameter query query = Session.createquery ("from Customer where cname =?"); Query.setparameter (0, "John Doe"); list<customer> list = Query.list (); print (list);
Multiple parameters
Query query = Session.createquery ("from Customer where cname =?") and CID =? "); Query.setparameter (0, "John Doe"); Query.setparameter (1, 2); list<customer> list = Query.list (); print (list);
Use the name method to bind the parameter query query = Session.createquery ("from Customer where cname =: Name and CID =: id"); Query.setparameter ("name", "John Doe"); Query.setparameter ("id", 2); list<customer> list = Query.list (); print (list);
Binding entity Parameters Customer customer = new Customer (); Customer.setcid (1); Query query = Session.createquery ("From Order as O where O.customer =?"); Query.setentity (0, customer); list<order> list = Query.list (); print (list);
9) Fuzzy Query
Use HQL for fuzzy queries query query = Session.createquery ("From Customer where cname is like?"); Query.setparameter (0, "Li%"); list<customer> list = Query.list (); print (list);
10) Aggregation function
Querying query query with an aggregate function = Session.createquery ("SELECT count (*) from Order"); list<order> list = Query.list (); print (list);
--qbc
QBC operator:
Example code:
1) Use QBC to query all data
Use QBC to query all customer information
Criteria = Session.createcriteria (Customer.class); Print (Criteria.list ());
2) Sort the results of the query
Criteria = Session.createcriteria (customer.class) AddOrder (ORG.HIBERNATE.CRITERION.ORDER.DESC ("id")); Print (Criteria.list ());
3) Paging Query
Use QBC for paged query criteria = Session.createcriteria (Order.class); Criteria.setfirstresult (0); Criteria.setmaxresults (11); list<order> list = Criteria.list (); print (list);
4) querying a single record
Use QBC to query a single record criteria = Session.createcriteria (Customer.class); Set the condition Criteria.add (Restrictions.eq ("CNAME", "John Doe")); Query only the first record criteria.setmaxresults (1); Customer customer = (customer) criteria.uniqueresult (); SYSTEM.OUT.PRINTLN (customer);
5) Conditional Query
Bind a parameter criteria = Session.createcriteria (Customer.class); Criteria.add (Restrictions.eq ("CNAME", "John Doe"));
Binding multiple parameters
Criteria.add (Restrictions.eq ("CID", "2")); list<customer> list = Criteria.list (); print (list);
6) Fuzzy Query
Use QBC for fuzzy queries criteria = Session.createcriteria (Customer.class); Criteria.add (Restrictions.like ("CNAME", "Li%")); list<customer> list = Criteria.list (); print (list);
--sql
Example code:
1) Querying all data using SQL
Query all customer information using SQL statements sqlquery query = Session.createsqlquery ("SELECT * from Customer"); list<object[]> list = Query.list (); Printarr (list); 2) Querying all data using SQL and encapsulating the data in the entity object
Query all customer information using SQL and encapsulate the result record into the entity object SQLQuery query = Session.createsqlquery ("SELECT * from Customer"); list<customer> list = query.addentity (customer.class). List (); print (list);
--Multi-table query
1, common multi-table query
1) Cross-connect
SELECT * from a B;
What we get is a Cartesian product.
2) Internal connection
The intersection of two tables is queried
SELECT * from a INNER join B on a. field = B. field;
Implicit Intra-connection:
SELECT * from A, b where a. field = B. field
3) External Connection
Left outer connection
SELECT * from a LEFT outer join B on A. field = B. field
Right outer connection
SELECT * from a right outer join B on A. field = B. field
2. Multi-table query in HQL
1) Cross-connect
2) Internal connection
3) Implicit in-connection
4) Urgent Internal connection
5) Left Outer connection
6) Urgent left outer connection
7) Right outer connection
3. Sample Code
1) Internal connection
/*
* HQL Inside Connection Query * Query is the intersection of two tables */Queries query = Session.createquery ("from Customer as C inner join C.ord ERs "); List List = Query.list (); Printarr (list);
2) Urgent Internal connection
Urgent internal connection, using a keyword fetch
//If you use the from customer as C inner join fetch c.orders, a duplicate Customer will appear //can use the DISTINCT keyword to remove duplicate customer:select distinct c query Query = session.createquery ("SELECT DISTINCT C from Customer as C INNER JOIN Fetch c.orders ") list List = query.list (); print (list);
4, the use of Left outer connection and the urgent left outer connection is the same as the internal connection, the urgent internal connection is the same
5, HQL internal connection and urgent internal connection difference:
Internal connection query:
encapsulates data into list<object[]>.
Urgent connection:
encapsulates data into a single list<customer>, but the urgency of the connection is to have duplicate records, Need to use distinct to weight.
--offline conditional query
now has a form that can perform different query operations based on different query criteria, but the DAO layer cannot get objects from the Web layer, and can be manipulated using offline objects.
Traditional mode:
UserService user = new UserService ();
user.findbycondition (username, sex, edu, tel);
The limitation is that it is impossible to modify the content of the upload, which is not easy to expand.
Offline:
Get an offline criteria and set the criteria for the criteria object at the Web layer.
Criteria.add (Restrictions.eq ("name", "Zhang San"));
Criteria.add (Restrictions.eq ("Sex", "male"));
Then invoke the service method at the Web layer to pass in the criteria object and get the executable offline criteria object at the DAO layer.
Sample code:
/* Offline Condition query */public void Fun15 () {Session session = Hibernateutils.opensession (); Transaction tx = Session.begintransaction (); Create an offline object at the Web tier Detachedcriteria criteria = Detachedcriteria.forclass (Customer.class); Set the parameter Criteria.add (RESTRICTIONS.EQ ("CNAME", "John Doe")); Criteria.add (Restrictions.eq ("CID", 2));
The Criteria criteria2 = Criteria.getexecutablecriteria (session) in the DAO layer for the executable criteria; list<customer> list = Criteria2.list (); print (list); Tx.commit (); Session.close (); }
--Summary
1. Object Navigation mode
* Can be obtained by an object to the associated object.
2, according to the OID search
* GET ()
* Load ();
3, HQL
* Session.createquery (String hql);
* Simple query: from Customer
* Sort: From Customer C order by c.cid Desc;
* Conditional query: Position binding parameter (?), name binding parameter (: Name)
* Paged query: query.setfirstresult (int from); Query.setmaxresult (int max)
* Aggregation function: SELECT COUNT (*) from the Customer group by CID;
* polymorphic query: from Java.lang.Object
* Projection query: Select CID, CNAME from Customer
* Construction method encapsulates query results into objects: Select New Customer (CID, CNAME) from customer
* Multi-table Query
> Cross Connect
> Internal Connection
> Urgent Internal Connection
> Left Outer connection
> Urgent LEFT Outer connection
* Named query
4, QBC
* Session.createcriteria (class Class);
* Simple Query
* Sort Query
* Conditional query: Criteria.add (Restrictions (name, value));
* Paging Query
* Offline Conditional query
5. SQL
* Session.createsqlquery (String sql);
Hibernate's Search method