上一节我们完成了EasyUI菜单的实现。这一节我们主要来写一下CategoryServiceImpl实现类,完成数据库的级联查询。一般项目从后往前做,先做service(我们没有抽取Dao,最后再抽取),做完了再做上面层。

  在写之前,先看一下数据库中的表的情况:

SQL代码
  1. drop?database?if?exists?shop;????
  2. /*创建数据库,并设置编码*/????
  3. create?database?shop?default?character?set?utf8;????
  4. ????
  5. use?shop;????
  6. /*删除管理员表*/????
  7. drop?table?if?exists?account;????
  8. /*删除商品类别表*/????
  9. drop?table?if?exists?category;????
  10. ????
  11. /*============================*/????
  12. /*??????Table:管理员表结构???????????????????????*/????
  13. /*============================*/????
  14. create?table?account????
  15. (????
  16. ????/*?管理员编号,自动增长?*/????
  17. ????id?int?primary?key?not?null?auto_increment,????
  18. ????/*?管理员登录名?*/????
  19. ????login?varchar(20),????
  20. ????/*?管理员姓名?*/????
  21. ????name?varchar(20),????
  22. ????/*?管理员密码?*/????
  23. ????pass?varchar(20)????
  24. );????
  25. ????
  26. /*============================*/????
  27. /*?????Table:商品类别表结构??????????????????????*/????
  28. /*============================*/????
  29. create?table?category????
  30. (????
  31. ???/*?类别编号,自动增长?*/????
  32. ???id??int?primary?key?not?null?auto_increment,????
  33. ???/*?类别名称?*/????
  34. ???type?varchar(20),????
  35. ???/*?类别是否为热点类别,热点类别才有可能显示在首页*/????
  36. ???hot??bool?default?false,????
  37. ???/*?外键,此类别由哪位管理员管理?*/????
  38. ???account_id?int,????
  39. ???constraint?aid_FK?foreign?key(account_id)?references?account(id)????
  40. );????

  主要有两张表,商品类别表和管理员表,并且商品类别表中提供了一个外键关联管理员表。也就是商品和管理员是多对一的关系。现在我们开始编写查询商品的类别信息,需要级联管理员。

  1. 实现级联查询方法

  首先在CategoryService接口中定义该方法:

Java代码
  1. public?interface?CategoryService?extends?BaseService?{????
  2. ????//查询类别信息,级联管理员????
  3. ????public?List?queryJoinAccount(String?type);?//使用类别的名称查询????
  4. }???

  然后我们在CategoryService的实现类CategoryServiceImpl中实现这个方法:

Java代码
  1. @Service("categoryService")????
  2. public?class?CategoryServiceImpl?extends?BaseServiceImpl?implements?CategoryService?{????
  3. ????
  4. ????@Override????
  5. ????public?List?queryJoinAccount(String?type)?{????
  6. ????????String?hql?=?"from?Category?c?where?c.type?like?:type";????
  7. ????????return?getSession().createQuery(hql)????
  8. ????????????????.setString("type",?"%"?+?type?+?"%").list();????
  9. ????}????
  10. }????

  在两个Model中我们配一下关联注解:

Java代码
  1. //Category类中????
  2. @ManyToOne(fetch?=?FetchType.EAGER)????
  3. @JoinColumn(name?=?"account_id")????
  4. public?Account?getAccount()?{????
  5. ????return?this.account;????
  6. }????
  7. //Account类中????
  8. @OneToMany(cascade?=?CascadeType.ALL,?fetch?=?FetchType.LAZY,?mappedBy?=?"account")????
  9. public?Set?getCategories()?{????
  10. ????return?this.categories;????
  11. }????

  然后我们在测试类中测试一下:

Java代码
  1. @RunWith(SpringJUnit4ClassRunner.class)????
  2. @ContextConfiguration(locations="classpath:beans.xml")????
  3. public?class?CategoryServiceImplTest?{????
  4. ????
  5. ????@Resource????
  6. ????private?CategoryService?categoryService;????
  7. ????????
  8. ????@Test????
  9. ?????public?void?testQueryJoinAccount()?{????
  10. ????????for(Category?c?:?categoryService.queryJoinAccount(""))?{????
  11. ?????????????System.out.println(c);????
  12. ?????????????System.out.println(c.getAccount());????
  13. ????????}????
  14. ????}????
  15. }????

  2. 级联查询存在的问题

  我们看一下控制台的输出可以看出,它发了不止一条SQL语句,但是我们明明只查询了一次,为什么会发这么多语句呢?这就是常见的1+N问题。所谓的1+N问题,就是首先发出一条语句查询当前对象,然后发出N条语句查询关联对象,因此效率变得很低。这里就两个对象,如果有更多的对象,那效率就会大打折扣了,我们该如何解决这个问题呢?

  可能大家会想到将fetch设置生FetchType.LAZY就不会发多条语句了,但是这肯定不行,因为设置成LAZY后,我们就拿不到Account对象了,比较好的解决方法是我们自己写hql语句,使用join fetch。具体看修改后的CategoryServiceImpl实现类:

Java代码
  1. @Service("categoryService")????
  2. public?class?CategoryServiceImpl?extends?BaseServiceImpl?implements?CategoryService?{????
  3. ????
  4. ????@Override????
  5. ????public?List?queryJoinAccount(String?type)?{????
  6. ????????String?hql?=?"from?Category?c?left?join?fetch?c.account?where?c.type?like?:type";????
  7. ????????return?getSession().createQuery(hql)????
  8. ????????????????.setString("type",?"%"?+?type?+?"%").list();????
  9. ????}????
  10. }????

  left join表示关联Account一起查询,fetch表示将Account对象加到Category中去,这样就只会发一条SQL语句了,并且返回的Category中也包含了Account对象了。

  3. 完成分页功能

  Hibernate中的分页很简单,只需要调用两个方法setFirstResult和setMaxResults即可:我们修改一下CategoryService接口和它的实现类CategoryServiceImpl:

Java代码
  1. //CategoryService????
  2. public?interface?CategoryService?extends?BaseService?{????
  3. ????//查询类别信息,级联管理员????
  4. ????public?List?queryJoinAccount(String?type,?int?page,?int?size);?//并实现分页????
  5. }????
  6. ????
  7. //CategoryServiceImpl????
  8. @Service("categoryService")????
  9. public?class?CategoryServiceImpl?extends?BaseServiceImpl?implements?CategoryService?{????
  10. ????
  11. ????@Override????
  12. ????public?List?queryJoinAccount(String?type,?int?page,?int?size)?{????
  13. ????????String?hql?=?"from?Category?c?left?join?fetch?c.account?where?c.type?like?:type";????
  14. ????????return?getSession().createQuery(hql)????
  15. ????????????????.setString("type",?"%"?+?type?+?"%")????
  16. ????????????????.setFirstResult((page-1)?*?size)?//从第几个开始显示????
  17. ????????????????.setMaxResults(size)?//显示几个????
  18. ????????????????.list();????
  19. ????}????
  20. }????

  我们在测试类中测试一下:

Java代码
  1. @RunWith(SpringJUnit4ClassRunner.class)????
  2. @ContextConfiguration(locations="classpath:beans.xml")????
  3. public?class?CategoryServiceImplTest?{????
  4. ????
  5. ????@Resource????
  6. ????private?CategoryService?categoryService;????
  7. ????
  8. ????@Test????
  9. ????public?void?testQueryJoinAccount()?{????
  10. ????????for(Category?c?:?categoryService.queryJoinAccount("",1,2))?{?//显示第一页,每页2条数据????
  11. ????????????System.out.println(c?+?","?+?c.getAccount());????
  12. ????????}????
  13. ????}????
  14. }????

  为此,我们写完了Service的方法了,完成了对商品类别的级联查询和分页功能。

除非特别注明,鸡皇冠现金官网hg6388|首页文章均为原创
转载请标明本文地址:http://www.jizhuomi.com/software/764.html
2017年9月13日
作者:鸡皇冠现金官网hg6388|首页 分类:软件开发 浏览: 评论:0