drop database if exists shop; 
/*创建数据库，并设置编码*/ 
create database shop default character set utf8; 
 
use shop; 
/*删除管理员表*/ 
drop table if exists account; 
/*删除商品类别表*/ 
drop table if exists category; 
 
/*============================*/ 
/*   Table：管理员表结构            */ 
/*============================*/ 
create table account 
( 
  /* 管理员编号，自动增长 */ 
  id int primary key not null auto_increment, 
  /* 管理员登录名 */ 
  login varchar(20), 
  /* 管理员姓名 */ 
  name varchar(20), 
  /* 管理员密码 */ 
  pass varchar(20) 
); 
 
/*============================*/ 
/*   Table：商品类别表结构           */ 
/*============================*/ 
create table category 
( 
  /* 类别编号，自动增长 */ 
  id int primary key not null auto_increment, 
  /* 类别名称 */ 
  type varchar(20), 
  /* 类别是否为热点类别，热点类别才有可能显示在首页*/ 
  hot bool default false, 
  /* 外键，此类别由哪位管理员管理 */ 
  account_id int, 
  constraint aid_FK foreign key(account_id) references account(id) 
); 

public interface CategoryService extends BaseService<Category> { 
  //查询类别信息，级联管理员 
  public List<Category> queryJoinAccount(String type); //使用类别的名称查询 
} 

@Service("categoryService") 
public class CategoryServiceImpl extends BaseServiceImpl<Category> implements CategoryService { 
 
  @Override 
  public List<Category> queryJoinAccount(String type) { 
    String hql = "from Category c where c.type like :type"; 
    return getSession().createQuery(hql) 
        .setString("type", "%" + type + "%").list(); 
  } 
} 

//Category类中 
@ManyToOne(fetch = FetchType.EAGER) 
@JoinColumn(name = "account_id") 
public Account getAccount() { 
  return this.account; 
} 
//Account类中 
@OneToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY, mappedBy = "account") 
public Set<Category> getCategories() { 
  return this.categories; 
} 

@RunWith(SpringJUnit4ClassRunner.class) 
@ContextConfiguration(locations="classpath:beans.xml") 
public class CategoryServiceImplTest { 
 
  @Resource 
  private CategoryService categoryService; 
   
  @Test 
   public void testQueryJoinAccount() { 
    for(Category c : categoryService.queryJoinAccount("")) { 
       System.out.println(c); 
       System.out.println(c.getAccount()); 
    } 
  } 
} 

@Service("categoryService") 
public class CategoryServiceImpl extends BaseServiceImpl<Category> implements CategoryService { 
 
  @Override 
  public List<Category> queryJoinAccount(String type) { 
    String hql = "from Category c left join fetch c.account where c.type like :type"; 
    return getSession().createQuery(hql) 
        .setString("type", "%" + type + "%").list(); 
  } 
} 

//CategoryService 
public interface CategoryService extends BaseService<Category> { 
  //查询类别信息，级联管理员 
  public List<Category> queryJoinAccount(String type, int page, int size); //并实现分页 
} 
 
//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().createQuery(hql) 
        .setString("type", "%" + type + "%") 
        .setFirstResult((page-1) * size) //从第几个开始显示 
        .setMaxResults(size) //显示几个 
        .list(); 
  } 
} 

@RunWith(SpringJUnit4ClassRunner.class) 
@ContextConfiguration(locations="classpath:beans.xml") 
public class CategoryServiceImplTest { 
 
  @Resource 
  private CategoryService categoryService; 
 
  @Test 
  public void testQueryJoinAccount() { 
    for(Category c : categoryService.queryJoinAccount("",1,2)) { //显示第一页，每页2条数据 
      System.out.println(c + "," + c.getAccount()); 
    } 
  } 
}