上一篇关于mybatis的记录了如何查询user这个实体类的数据。假设这些用户分属于不同的工作部门,今天记录实现查询部门及下属用户的过程。据此,新建一个mysql的 table,名为department_,字段为id,name。代码略。
在com.mybatis.pojo下面建立一个Department.java的实体类。属性包括id,name,users,为其设置setter和getter。
另外,在上一篇记录到的user_的table中,增加字段did,表示某用户所属部门的id。给User.java设置属性Departmet,和对应的setter与getter,代码略。
由于采用注解的方式,所以要建立名为UserMapper和DepartmentMapper的interface。
代码如下
User对department是多对一
package com.mybatis.mapper;
import java.util.List;
import org.apache.ibatis.annotations.One;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import com.mybatis.pojo.User;
public interface UserMapper {
@Insert(" insert into user_ (name,usercode,did) values(#{name},#{usercode},#{did})")
public int add(User user);
@Delete("select * from user_ where id = #{id}")
public void delete(int id);
@Update("update user_ set name=#{name} , usercode=#{usercode} , did = #{did} where id = #{id}")
public int update(User user);
@Select("select * from user_")
@Results({
@Result(property="department",column="did",one=@One(select="com.mybatis.mapper.DepartmentMapper.get"))
})
public List<User> list();
@Select("select * from user_ where id = #{id}")
public User get(int id);
@Select("select * from user_ where did = #{did}")
public List<User> listByDepartment(int did);
}
department对user是一对多
package com.mybatis.mapper;
import java.util.List;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Many;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import com.mybatis.pojo.Department;
public interface DepartmentMapper {
@Insert("insert into department_ (name) values(#{name})")
public int add(Department department);
@Delete("delete from department_ where id = #{id}")
public void delete(int id);
@Select("select * from department_")
@Results({
@Result(property="id",column="id"),
@Result(property="users",javaType=List.class,column="id",many=@Many(select = "com.mybatis.mapper.UserMapper.listByDepartment"))
})
public List<Department> list();
@Select("select * from department_ where id = #{id}")
public Department get(int id);
@Update("update department_ set name = #{name} where id = #{id}")
public int update(Department department);
}
写完两个Mapper,要在mybatis-config.xml里面写上<mapper class="com.mybatis.mapper.DepartmentMapper"/>
最后编写测试类
package com.mybatis;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import com.mybatis.mapper.DepartmentMapper;
import com.mybatis.mapper.UserMapper;
import com.mybatis.pojo.Department;
import com.mybatis.pojo.User;
public class TestMybatisM2M {
public static void main(String[] args) throws IOException{
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession session = sqlSessionFactory.openSession();
DepartmentMapper mapper = session.getMapper(DepartmentMapper.class);
listAll(mapper);
session.commit();
session.close();
}
private static void listAll(DepartmentMapper mapper) {
// TODO Auto-generated method stub
List<Department> ds = mapper.list();
for(Department d:ds){
System.out.println(d.getName());
List<User> us = d.getUsers();
for(User u:us){
System.out.println("\t"+u.getName()+"\t"+u.getUsercode());
}
}
}
}
控制台结果
DEBUG [main] - ==> Preparing: select * from department_
DEBUG [main] - ==> Parameters:
TRACE [main] - <== Columns: id, name
TRACE [main] - <== Row: 1, technique
DEBUG [main] - ====> Preparing: select * from user_ where did = ?
DEBUG [main] - ====> Parameters: 1(Integer)
TRACE [main] - <==== Columns: id, name, usercode, did
TRACE [main] - <==== Row: 1, tom123, 180923001, 1
TRACE [main] - <==== Row: 2, serenity, 180923101, 1
DEBUG [main] - <==== Total: 2
DEBUG [main] - <== Total: 1
technique
tom123 180923001
serenity 180923101