目录
一、Mybatis多表查询
1.1 一对一查询
1. 一对多查询的模型
?2. 一对多查询的语句
?3. 修改User实体
public class User { private int id; private String username; private String password; private Date birthday; } public class Order { private int id; private Date ordertime; private double total; //代表当前订单从属于哪一个客户 private User user; }4. 创建OrderMapper接口
public interface OrderMapper { List<Order> findAll(); }5. 配置OrderMapper.xml
<mapper namespace="com.learn.mapper.OrderMapper"> <resultMap id="orderMap" type="order"> <!--手动指定字段与实体属性的映射关系 column：数据表的字段名称 property：实体的属性名称 --> <id column="oid" property="id"></id> <result column="ordertime" property="ordertime"></result> <result column="total" property="total"></result> <!-- <result column="uid" property="user.id"></result> <result column="username" property="user.username"></result> <result column="password" property="user.password"></result> <result column="birthday" property="user.birthday"></result>--> <!--还可以这样配置，如下： property：当前实体(order)中的属性名称(private User user) javaType：当前实体(order)中的属性的类型(User) --> <association property="user" javaType="user"> <id column="uid" property="id"></id> <result column="username" property="username"></result> <result column="password" property="password"></result> <result column="birthday" property="birthday"></result> </association> </resultMap> <select id="findAll" resultMap="orderMap"> SELECT *,o.id oid FROM orders o,USER u WHERE o.uid=u.id </select> </mapper>6. 测试
@Test public void test1() throws IOException { //获得核心配置文件 InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapperConfig.xml"); //获得session工厂对象 SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream); //获得session会话对象 SqlSession sqlSession = sqlSessionFactory.openSession(); //获得MyBatis框架生成的UserMapper接口的实现类 OrderMapper mapper = sqlSession.getMapper(OrderMapper.class); List<Order> orderList = mapper.findAll(); for (Order order : orderList) { System.out.println(order); } sqlSession.close(); }
1.2 一对多查询
1. 一对多查询的模型
?2. 一对多查询的语句
?3. 修改User实体
public class User { private int id; private String username; private String password; private Date birthday; //代表当前用户具备哪些订单 private List<Order> orderList; } public class Order { private int id; private Date ordertime; private double total; //代表当前订单从属于哪一个客户 private User user; }4. 创建UserMapper接口
public interface UserMapper { List<User> findAll(); }5. 配置UserMapper.xml
<mapper namespace="com.learn.mapper.UserMapper"> <resultMap id="userMap" type="user"> <id column="uid" property="id"></id> <result column="username" property="username"></result> <result column="password" property="password"></result> <result column="birthday" property="birthday"></result> <!--配置集合信息 property:集合名称 ofType:当前集合中的数据类型 --> <collection property="orderList" ofType="order"> <!--封装order的数据--> <id column="oid" property="id"></id> <result column="ordertime" property="ordertime"></result> <result column="total" property="total"></result> </collection> </resultMap> <select id="findAll" resultMap="userMap"> SELECT *,o.id oid FROM USER u,orders o WHERE u.id=o.uid </select> </mapper>6. 测试
@Test public void test2() throws IOException { //获得核心配置文件 InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapperConfig.xml"); //获得session工厂对象 SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream); //获得session会话对象 SqlSession sqlSession = sqlSessionFactory.openSession(); //获得MyBatis框架生成的UserMapper接口的实现类 UserMapper mapper = sqlSession.getMapper(UserMapper.class); List<User> userList = mapper.findAll(); for (User user : userList) { System.out.println(user); } sqlSession.close(); }
1.3 多对多查询
?1. 多对多查询的模型
?2. 多对多查询的语句
?3. 创建Role实体，修改User实体
public class User { private int id; private String username; private String password; private Date birthday; //代表当前用户具备哪些订单 private List<Order> orderList; //代表当前用户具备哪些角色 private List<Role> roleList; } public class Role { private int id; private String rolename; }4. 添加UserMapper接口方法
public interface UserMapper { public List<User> findUserAndRoleAll(); }5. 配置UserMapper.xml
<mapper namespace="com.learn.mapper.UserMapper"> <resultMap id="userRoleMap" type="user"> <!--封装user的信息--> <id column="userId" property="id"></id> <result column="username" property="username"></result> <result column="password" property="password"></result> <result column="birthday" property="birthday"></result> <!--user内部的roleList信息--> <collection property="roleList" ofType="role"> <id column="roleId" property="id"></id> <result column="roleName" property="roleName"></result> <result column="roleDesc" property="roleDesc"></result> </collection> </resultMap> <select id="findUserAndRoleAll" resultMap="userRoleMap"> SELECT * FROM USER u,sys_user_role ur,sys_role r WHERE u.id=ur.userId AND ur.roleId=r.id </select> </mapper>6. 测试
@Test public void test3() throws IOException { //获得核心配置文件 InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapperConfig.xml"); //获得session工厂对象 SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream); //获得session会话对象 SqlSession sqlSession = sqlSessionFactory.openSession(); //获得MyBatis框架生成的UserMapper接口的实现类 UserMapper mapper = sqlSession.getMapper(UserMapper.class); List<User> userAndRoleAll = mapper.findUserAndRoleAll(); for (User user : userAndRoleAll) { System.out.println(user); } sqlSession.close(); }
1.4 知识小结
二、Mybatis注解开发
2.1 MyBatis的常用注解
Mybatis可以使用注解开发方式来减少编写Mapper 映射文件。先围绕一些基本的CRUD来学习，再学习复杂映射多表操作
@Insert：实现新增
@Update：实现更新
@Delete：实现删除
@Select：实现查询
@Result：实现结果集封装
@Results：可以与@Result 一起使用，封装多个结果集
@One：实现一对一结果集封装
@Many：实现一对多结果集封装
2.2 MyBatis的增删改查
public class MybatisTest { private UserMapper mapper; @Before public void before() throws IOException { InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapperConfig.xml"); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream); SqlSession sqlSession = sqlSessionFactory.openSession(true); mapper = sqlSession.getMapper(UserMapper.class); } @Test public void testSave(){ User user = new User(); user.setUsername("tom"); user.setPassword("233"); mapper.save(user); } @Test public void testUpdate(){ User user = new User(); user.setId(9); user.setUsername("lucy"); user.setPassword("789"); mapper.update(user); } @Test public void testDelete(){ User user = new User(); mapper.delete(7); } @Test public void testFindById(){ User user = mapper.findById(2); System.out.println(user); } @Test public void testFindAll(){ List<User> all = mapper.findAll(); for (User user : all) { System.out.println(user); } } }修改MyBatis的核心配置文件，使用了注解替代的映射文件，所以只需要加载使用了注解的Mapper接口即可
<mappers> <!--扫描使用注解的类--> <mapper class="com.learn.mapper.UserMapper"></mapper> </mappers>或者指定扫描包含映射关系的接口所在的包也可以
<!--加载映射关系--> <mappers> <!--指定接口所在的包--> <package name="com.learn.mapper"/> </mappers>
2.3?MyBatis的注解实现复杂映射开发
实现复杂关系映射之前我们可以在映射文件中通过配置来实现，使用注解开发后，我们可以使用@Results注解 ，@Result注解，@One注解，@Many注解组合完成复杂关系的配置
| 注解 | 说明 |
| @Results | 代替的是标签该注解中可以使用单个@Result注解，也可以使用@Result集 合。使用格式：@Results（{@Result（），@Result（）}）或@Results（@Result（）） |
| @Result | 代替了标签和标签 @Result中属性介绍： column：数据库的列名 property：需要装配的属性名 one：需要使用的@One 注解（@Result（one=@One）（））） many：需要使用的@Many 注解（@Result（many=@many）（））） |
2.4?一对一查询
1. 一对一查询的模型
?2. 一对一查询的语句
?3. 创建Order和User实体
public class Order { private int id; private Date ordertime; private double total; //代表当前订单从属于哪一个客户 private User user; } public class User { private int id; private String username; private String password; private Date birthday; }4. 创建OrderMapper接口
public interface OrderMapper { List<Order> findAll(); }5. 使用注解配置Mapper
?
public interface OrderMapper { /* @Select("select *,o.id oid from orders o,user u where o.uid=u.id") @Results({ @Result(column = "oid",property = "id"), @Result(column = "ordertime",property = "ordertime"), @Result(column = "total",property = "total"), @Result(column = "uid",property = "user.id"), @Result(column = "username",property = "user.username"), @Result(column = "password",property = "user.password") }) public List<Order> findAll(); */ @Select("select *,o.id oid from orders o,user u where o.uid=u.id") @Results({ @Result(column = "oid",property = "id"), @Result(column = "ordertime",property = "ordertime"), @Result(column = "total",property = "total"), @Result( property = "user", //要封装的属性名称 column = "uid", //根据哪个字段去查询user表的数据 javaType = User.class, //要封装的实体类型 //select属性 代表查询哪个接口的方法来获得数据 one = @One(select = "com.learn.mapper.UserMapper.findById") ) }) public List<Order> findAll(); @Select("select * from orders where uid=#{uid}") public List<Order> findByUid(int uid); }public interface UserMapper { @Select("select * from user where id=#{id}") public User findById(int id); }6. 测试
public class MybatisTest2 { private OrderMapper mapper; @Before public void before() throws IOException { InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapperConfig.xml"); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream); SqlSession sqlSession = sqlSessionFactory.openSession(true); mapper = sqlSession.getMapper(OrderMapper.class); } @Test public void testSave(){ List<Order> all = mapper.findAll(); for (Order order : all) { System.out.println(order); } } }
2.5 一对多查询
1. 一对一查询的模型
?2. 一对一查询的语句
?3. 修改User实体
public class Order { private int id; private Date ordertime; private double total; //代表当前订单从属于哪一个客户 private User user; } public class User { private int id; private String username; private String password; private Date birthday; //代表当前用户具备哪些订单 private List<Order> orderList; }4. 创建UserMapper接口
List<User> findAllUserAndOrder();5. 使用注解配置Mapper
public interface UserMapper { @Select("select * from user") @Results({ @Result(id=true,column = "id",property = "id"), @Result(column = "username",property = "username"), @Result(column = "password",property = "password"), @Result( property = "orderList", column = "id", javaType = List.class, many = @Many(select = "com.learn.mapper.OrderMapper.findByUid") ) }) public List<User> findUserAndOrderAll(); }public interface OrderMapper { @Select("select * from orders where uid=#{uid}") public List<Order> findByUid(int uid); }?6. 测试
public class MybatisTest3 { private UserMapper mapper; @Before public void before() throws IOException { InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapperConfig.xml"); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream); SqlSession sqlSession = sqlSessionFactory.openSession(true); mapper = sqlSession.getMapper(UserMapper.class); } @Test public void testSave(){ List<User> userAndOrderAll = mapper.findUserAndOrderAll(); for (User user : userAndOrderAll) { System.out.println(user); } } }
2.6 多对多查询
1. 一对一查询的模型
?2. 一对一查询的语句
?3. 创建Role实体，修改User实体
?
public class User { private int id; private String username; private String password; private Date birthday; //代表当前用户具备哪些订单 private List<Order> orderList; //代表当前用户具备哪些角色 private List<Role> roleList; } public class Role { private int id; private String rolename; }4. 添加UserMapper接口方法
List<User> findAllUserAndRole();5. 使用注解配置Mapper
?
public interface UserMapper { @Select("select * from user") @Results({ @Result(id=true,column = "id",property = "id"), @Result(column = "username",property = "username"), @Result(column = "password",property = "password"), @Result( property = "roleList", column = "id", javaType = List.class, many = @Many(select = "com.learn.mapper.RoleMapper.findByUid") ) }) public List<User> findUserAndRoleAll(); }public interface RoleMapper { @Select("select * from sys_user_role ur,sys_role r where ur.roleId=r.id and ur.userId=#{uid}") public List<Role> findByUid(int uid); }6. 测试
public class MybatisTest4 { private UserMapper mapper; @Before public void before() throws IOException { InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapperConfig.xml"); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream); SqlSession sqlSession = sqlSessionFactory.openSession(true); mapper = sqlSession.getMapper(UserMapper.class); } @Test public void testSave(){ List<User> userAndRoleAll = mapper.findUserAndRoleAll(); for (User user : userAndRoleAll) { System.out.println(user); } } }
