"SSH Project Combat 05" Complete the database Cascade query and paging

In the previous section we completed the implementation of the Easyui menu. This section we mainly write about the Categoryserviceimpl implementation class, complete the database Cascade query. General project from the back to do, first do the service (we did not draw DAO, and then extract), finished and then do the upper layer.

Before you write, look at the tables in the database:

drop database if exists shop;/* create databases and set encoding */create the default character set UTF8 ; Use shop;/* Delete Administrator table */drop table if exists account;/* Delete product Category Table */drop table if exists category;/*=========================== =*//* Table: Administrator table Structure *//*============================*/create table account (/* Administrator number, autogrow */id in T primary key NOT NULL auto_increment,/* administrator login */login varchar (20),/* Administrator name */name varchar (20),/* Administrator password */pass varchar (20 ));/*============================*//* table: Commodity category table Structure *//*============================*/create tabl E Category (/* class number, autogrow */ID int primary key NOT NULL auto_increment,/* Category name */type varchar (20),/* category is hotspot Category, the hotspot category is only possible to display on the homepage */hot bool Default FALSE,/* foreign key, which administrator manages this category */account_id int, constraint AID_FK foreign KEY (ACC ount_id) references account (ID)); 

There are mainly two tables, a commodity category table and an administrator table, and a foreign key associated administrator table is provided in the Commodity category table. That is, goods and administrators are many-to-one relationship. Now we are writing to query the category information of the product, need the Cascade administrator.

1. Implementing a cascading Query method

The method is defined first in the Categoryservice interface:

Public interface Categoryservice extends baseservice<category> {//query category information, Cascade Admin public list<category> Queryjoinaccount (String type); Use the category's name query}

We then implement this method in the implementation class Categoryserviceimpl of Categoryservice:

@Service ("Categoryservice") public class Categoryserviceimpl extends baseserviceimpl<category> implements Categoryservice {@Overridepublic list<category> queryjoinaccount (String type) {string hql = "from Category C where c . Type Like:type "; return GetSession (). CreateQuery (HQL). setString (" type ","% "+ type +"% "). List ();}}

In the two model we match the associated annotations:

@manytoone (fetch = Fetchtype.eager) @JoinColumn in category class (name = "account_id") Public account Getaccount () {return This.account;} @onetomany (cascade = cascadetype.all, fetch = fetchtype.lazy, Mappedby = "account") in account class public set<category > GetCategories () {return this.categories;}

Then we test it in the test class:

@RunWith (Springjunit4classrunner.class) @ContextConfiguration (locations= "Classpath:beans.xml") public class categoryserviceimpltest {@Resourceprivate categoryservice categoryservice; @Test public void Testqueryjoinaccount () { For (Category C:categoryservice.queryjoinaccount ("")) {System.out.println (c); System.out.println (C.getaccount ()); } }}

2. Problems with cascading queries

Let's look at the output of the console and see that it sends more than one SQL statement, but we've only queried it once, why are there so many statements? This is a common 1+n problem. The so-called 1+n problem is that it starts by issuing a statement to query the current object and then issuing N statements to query the associated object, so the efficiency becomes very low. Here are two objects, if there are more objects, then the efficiency will be greatly reduced, how can we solve this problem?

Perhaps everyone will think of the fetch set raw fetchtype.lazy will not send many statements, but this certainly not, because after set to lazy, we will not get the account object, the better solution is we write the HQL statement, using join Fetch. specifically see the modified Categoryserviceimpl implementation class:

@Service ("Categoryservice") public class Categoryserviceimpl extends baseserviceimpl<category> implements Categoryservice {@Overridepublic list<category> queryjoinaccount (String type) {string hql = "from Category C left Jo In fetch c.account where C.type like:type "; return GetSession (). CreateQuery (HQL). setString (" type ","% "+ type +"% "). List ( );}}

The left join indicates that the associated account is queried together, and fetch indicates that the account object is added to the category so that only one SQL statement is sent and the account object is included in the returned category.

3. Complete the paging function

The paging in hibernate is simple, just call two methods Setfirstresult and Setmaxresults: We'll modify the Categoryservice interface and its implementation class Categoryserviceimpl:

Categoryservicepublic interface Categoryservice extends baseservice<category> {//query category information, Cascade Admin public list< Category> queryjoinaccount (String type, int page, int size); and realize paging}//categoryserviceimpl@service ("Categoryservice") public class Categoryserviceimpl extends Baseserviceimpl <Category> implements Categoryservice {     @Override     public list<category > Queryjoinaccount (String type, int page, int size) {        string hql = "from Category C LEFT join fetch C.account where C.type like:type ";        return getsession (). Createquer Y (hql)                 .setstring ("type", "%" + type + "%") & nbsp;               .setfirstresult ((page-1) * size)// From the beginning of the first display                 .setmaxresults (size)//show several  &nbsP              .list ();    }}

Let's test it in the test class:

@RunWith (Springjunit4classrunner.class) @ContextConfiguration (locations= "Classpath:beans.xml") public class categoryserviceimpltest {@Resourceprivate categoryservice categoryservice; @Testpublic void Testqueryjoinaccount () { For (Category c:categoryservice.queryjoinaccount (",")) {//Show first page, 2 data per page System.out.println (c + "," + C.getaccount ()) ;}}}

To this end, we finished the service method, completed the product category cascading query and paging function.

(note: In the end I will apply for this project blog column, and provide the entire project source code download!) Welcome to our collection or attention)

___________________________________________________________________________________________________________ __________________________________________

-----willing to share and progress together!

-----More articles please see: http://blog.csdn.net/eson_15

"SSH Project Combat 05" Complete the database Cascade query and paging