Hibernate Study Notes -------- 4. query, hibernate -------- 4

Hibernate Study Notes -------- 4. query, hibernate -------- 4

1. Get/Load

The Get method is instant retrieval, while the load method is delayed retrieval. They all query based on the primary key. In the <class> label, if the lazy attribute is changed to false, the load method will be retrieved immediately. The lazy attribute in the class is only valid for the load method. When load is used, ObjectNotFoundException may occur when no data is found in the database. An exception occurs.

 

1 public void LazyTest () {2 // set lazy to false or true to check the SQL printing time. 3 Tb_User u = session. load (Tb_User.class, "1"); 4 // System. out. println (u. getName (); 5}

 

2. 1-n or multiple-to-many queries

2.1 The lazy attribute of the set tag can be set to true, false, and extra.

　　select        count(ID)     from        TB_ORDER     where        userid =?

 

2.2 set the batch-size attribute of the tag to determine the number of elements in the set initialization in batches

2.3 The fetch attribute of the set tag. The default value is select. determine the method for initializing the set.

1. select, initialize set in Normal Mode

2. subselect: initializes all set sets through subqueries. The lazty attribute is valid and the batch-size attribute is invalid. In this case, the SQL statement

Hibernate:     select        orders0_.userid as userid6_0_1_,        orders0_.ID as ID1_0_1_,        orders0_.ID as ID1_0_0_,        orders0_.NAME as NAME2_0_0_,        orders0_.MONEY as MONEY3_0_0_,        orders0_.COUNT as COUNT4_0_0_,        orders0_.CREATETIME as CREATETIME5_0_0_,        orders0_.userid as userid6_0_0_     from        TB_ORDER orders0_     where        orders0_.userid in (            select                tb_user0_.ID             from                TB_USER tb_user0_        )

2.4 join, lazy will be invalid, use left outer join query. If the hql query is used, the fetch = join attribute is ignored.

Iii. n-1 Query

1. The lazy attribute of allow-to-one has three values: proxy delayed retrieval, no-proxy, and false instant retrieval.

2. The fetch attribute of parallel-to-one has two values: select and join, which have the same effect as above.

3. the batch-size attribute is added to the class label of the 1st party. It is not added to the class-to-one!

4. Use hql to query

1. steps:

-Use the createQuery method of the Session to create a Query object. The parameter is an HQL statement.

-Dynamic binding parameters (by parameter name: parameter name, by parameter location ?)

1 // bind the parameter value 2 @ Test 3 public void test1 () {4 Query query = session. createQuery ("FROM Tb_User WHERE name =? AND id =? "); 5 query. setParameter (0, "1"); 6 query. setParameter (1, "1"); 7 List <Tb_User> list = query. getResultList (); 8 System. out. println (list. size (); 9} 10 11 // bind the parameter value 12 @ Test13 public void test2 () {14 Query query = session. createQuery ("FROM Tb_User WHERE name =: name AND id =: id"); 15 query. setParameter ("name", "1"); 16 query. setParameter ("id", "1"); 17 List <Tb_User> list = query. getResultList (); 18 System. out. println (list. size (); 19}

 

-Call the query method to execute the query statement.

2. Two Methods for querying by PAGE: setFirsetResult | setMaxResult

1 // paging Test 2 @ Test 3 public void pageTest () {4 Query query = session. createQuery ("SELECT id FROM Tb_User"); 5 int pageNo = 2; // the current page number is 6 int pageSize = 4; // page size 7 8 List <String> list = query 9. setFirstResult (pageNo-1) * pageSize) 10. setMaxResults (4 ). getResultList (); 11 for (String u: list) {12 System. out. println (u); 13} 14}

3. getNamedQuery

Naming query allows us to define hql statements in xml and add them outside the class label in the ing file. Here we use CDATA because if hql contains symbols such as greater than or less than <, conflicts can be avoided.

<query name = "findUserByName">    <![CDATA[FROM Tb_User a WHERE a.name = :name]]></query>

Execute the statement using the name attribute.

　　@Test    public void nameQuery(){        Query query = session.getNamedQuery("findUserByName");        List<Tb_User> list = query.setParameter("name", "1").getResultList();        System.out.println(list.size());    }

4. query certain fields in the projection query table

Two fields are selected here, so each data query is an object array. Because there are multiple data entries, the List <Object []> is used to save the result, if only one field is queried, you can use List <T> to save the result.

    @Test    public void filedQuery(){        String hql = "SELECT a.id ,a.name FROM Tb_User a";        Query query = session.createQuery(hql);        List<Object[]> result = query.getResultList();                for(Object[] objs: result){            System.out.println(Arrays.asList(objs));            //System.out.println(objs[0]);        }    }

Of course, you can also initialize this class at the same time of the query, as shown below. Note that you need to add a constructor with only id and name in Tb_User.

    @Test    public void filedQuery2(){        String hql = "SELECT new Tb_User( a.id ,a.name ) FROM Tb_User a";        Query query = session.createQuery(hql);        List<Tb_User> result = query.getResultList();                for(Tb_User u: result){            System.out.println(u);        }    }

5. REPORT query. hql can use the group by and HAVING keywords to call the aggregate function.

-Count ()

-Min ()

-Max ()

-Sum ()

-Avg ()

// Query the maximum amount and minimum amount of each user @ Test public void GroupQuery () {String hql = "SELECT min (. money), max (. money) FROM Tb_Order a "+" group by. user "; Query query = session. createQuery (hql); List <Object []> result = query. getResultList (); for (Object [] objs: result) {System. out. println (Arrays. asList (objs); // System. out. println (objs [0]);}

6. left Outer Join

There are two keywords for LEFT Outer JOIN: left join and left join fetch.

The left join fetch method is to find out and initialize the set immediately. The set may contain duplicate elements and need to be de-duplicated. Deduplicated hql distinct or hashset Sets

@ Test public void leftJoinFetch () {String hql = "From Tb_User a left join fetch. orders "; // String hql =" select distinct a From Tb_User a left join fetch. orders "; // hql removes the repeated field Query = session. createQuery (hql); List <Tb_User> list = query. getResultList (); // list = new ArrayList <Tb_User> (new javashashset <Tb_User> (list); // remove duplicate fields from hashset for (Tb_User u: list) {System. out. println (u. getName () + "-" + u. getOrders (). size ());}}

 

Left join mode. By default, each piece of data is an array of objects. Each object array contains Tb_User and Tb_Order, but it does not have an initial set, the initialization of the set requires more configuration of the ing file. There may be duplicates, but only the distinct keyword of hql can be used to remove duplicates.

    @Test    public void leftJoin() {        String hql = "From Tb_User a LEFT JOIN a.orders";        Query query = session.createQuery(hql);        List<Object[]> list = query.getResultList();                for(Object[] objs : list){            System.out.println(Arrays.asList(objs));        }    }

In this case, the user will query the database based on the configuration of the user ing file to obtain the number of his order

    @Test    public void leftJoin2() {        String hql = "SELECT DISTINCT a From Tb_User a LEFT JOIN a.orders";        Query query = session.createQuery(hql);        List<Tb_User> list = query.getResultList();                for(Tb_User u : list){            System.out.println(u.getName() +"-"+ u.getOrders().size());        }    }

The difference between inner join fetch and inner join is that it does not query records that do not meet the conditions in the left table. In this case, no user with the order 0 is returned.

V. QBC (Query By Criteria) Object-Oriented Query

Use the Criteria interface to implement

@ Test public void qbcTest () {// 1. create a Criteria object Criteria criteria = session. createCriteria (Tb_User.class); // 2. add the query condition criteria. add (Restrictions. eq ("id", "1"); // 3. execute the query Tb_User u = (Tb_User) criteria. uniqueResult (); System. out. println (u );}

Their and or query method

@ Test public void qbcTest2 () {Criteria criteria = session. createCriteria (Tb_User.class); // AND: Use Conjunction to indicate Conjunction conjunction = Restrictions. conjunction (); conjunction. add (Restrictions. like ("name", "1", MatchMode. ANYWHERE); conjunction. add (Restrictions. eq ("id", "1"); criteria. add (conjunction); System. out. println (criteria. list (); // or Criteria criteria1 = session. createCriteria (Tb_Order.class); Disjunction disjunction = Restrictions. disjunction (); disjunction. add (Restrictions. ge ("money", 2.0); disjunction. add (Restrictions. eq ("name", "aa"); criteria1.add (disjunction); System. out. println (criteria1.list (). size ());}

Criteria statistics Query

@ Test public void qbcTest3 () {Criteria criteria = session. createCriteria (Tb_Order.class); // statistical query: Use prijection to represent criteria. setProjection (Projections. max ("money"); // Add sort // criteria. addOrder (Order. asc ("id"); System. out. println (criteria. uniqueResult ());}

The paging method is the same as the query. getResultList method.

6. Local SQL query

If you write an SQL statement and query it as an object array, it feels strange to use it.

    @Test    public void nativeSql(){        String sql = "select * from Tb_User where name = :name";        Query query = session.createSQLQuery(sql);        query.setParameter("name", "1");        List<Object[]> list = query.getResultList();        for(Object[] u : list){            System.out.println(Arrays.asList(u));        }    }

To execute other SQL statements

    @Test    public void nativeSql(){        UUID u = UUID.randomUUID();        String sql = "insert into Tb_User(id,name) values (:id,:name)";        Query query = session.createSQLQuery(sql);        query.setParameter("id", u.toString());        query.setParameter("name", u.toString());        query.executeUpdate();    }

Demo address: http://pan.baidu.com/s/1kUT9H91