Mybatis
1.简介
1.1 环境说明以及课前预备知识
1.2 什么是MyBatis
1.3 持久化
1.4 持久层
1.5 为什么需要Mybatis
2.MyBatis第一个程序
2.1 导入相关的依赖
创建Maven工程并导入相关依赖:mybatis、mysql、junit.log4j
2.2 创建user数据表
CREATE TABLE USER(
id INT PRIMARY KEY,
username VARCHAR(20),
PASSWORD VARCHAR(20));
INSERT INTO `user`
VALUES (1,'zhangsan','abc'),
(2,'lisi','987'),
(3,'wangwu','123'),
(4,'zhaoliu','5635'),
(5,'tianqi','4454');
2.3 编写实体类
package com.lwb.pojo;
public class User {
private int id;
private String username;
private String password;
public User() {
}
public User(int id, String username, String password) {
this.id = id;
this.username = username;
this.password = password;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", password='" + password + '\'' +
'}';
}
}
2.4 编写UserMapper映射文件
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="UserMapper">
<select id="findAll" resultType="com.lwb.pojo.User">
select * from user
</select>
</mapper>
2.5 编写核心配置文件
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<!--核心配置文件-->
<configuration>
<!--数据源环境-->
<environments default="development">
<environment id="development">
<!--事务管理-->
<transactionManager type="JDBC"/>
<!--数据源-->
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/mybatis?useSSL=true&useUnicode=true&characterEncoding=utf8"/>
<property name="username" value="root"/>
<property name="password" value="lwb"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="com/lwb/mapper/UserMapper.xml"></mapper>
</mappers>
</configuration>
2.6 编写测试代码
@Test
public void test1() throws IOException {
//加载核心配置文件
InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
//得到SqlSession工厂对象
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
//获取SqlSession对象
SqlSession sqlSession = sqlSessionFactory.openSession();
//执行sql语句
List<User> userList = sqlSession.selectList("UserMapper.findAll");
//打印结果
System.out.println(userList);
//结果:[User{id=1, username='zhangsan', password='abc'}, User{id=2, username='lisi', password='987'}, User{id=3, username='wangwu', password='123'}, User{id=4, username='zhaoliu', password='5635'}, User{id=5, username='tianqi', password='4454'}]
//释放资源
sqlSession.close();
}
3.MyBatis的映射文件概述
4.Mybatis的增删改查操作
4.1 插入操作
(1)编写UserMapper映射文件
<!--插入操作-->
<insert id="add" parameterType="com.lwb.pojo.User">
<!--参数为User类的属性名-->
insert into user values(#{id},#{username},#{password})
</insert>
(2) 编写插入实体User的代码
@Test
public void test2() throws IOException {
//创建User类对象作为参数
User user = new User(6,"linba","4567");
//加载核心配置文件
InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
//得到SqlSession工厂对象
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
//获取SqlSession对象
SqlSession sqlSession = sqlSessionFactory.openSession();
//执行sql语句
sqlSession.insert("UserMapper.add",user);
//Mybatis默认事务不自动提交,要提交事务
sqlSession.commit();
//释放资源
sqlSession.close();
}
4.2 插入操作的注意点
4.3 修改操作
(1)编写UserMapper映射文件
<!--修改操作-->
<update id="update" parameterType="com.lwb.pojo.User">
update user set username=#{username},password=#{password} where id=#{id}
</update>
(2)编写修改实体User的代码
//修改
@Test
public void test3() throws IOException {
//创建User类对象作为参数
User user = new User(6,"wangmou","abcd");
//加载核心配置文件
InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
//得到SqlSession工厂对象
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
//获取SqlSession对象
SqlSession sqlSession = sqlSessionFactory.openSession();
//执行sql语句
sqlSession.update("UserMapper.update",user);
//Mybatis默认事务不自动提交,要提交事务
sqlSession.commit();
//释放资源
sqlSession.close();
}
4.4 修改操作的注意点
4.5 删除操作
(1)编写UserMapper映射文件
<!--删除操作-->
<delete id="delete" parameterType="java.lang.Integer">
<!--#里面的id可以是任意的字符串,它只是相当于一个变量名-->
delete from user where id=#{id}
</delete>
(2)编写修改实体User的代码
//删除
@Test
public void test4() throws IOException {
//加载核心配置文件
InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
//得到SqlSession工厂对象
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
//获取SqlSession对象
SqlSession sqlSession = sqlSessionFactory.openSession();
//执行sql语句
sqlSession.update("UserMapper.delete",6);
//Mybatis默认事务不自动提交,要提交事务
sqlSession.commit();
//释放资源
sqlSession.close();
}
4.6 删除操作的注意点
5.核心配置文件
5.1 MyBatis核心配置文件层级关系
5.2 MyBatis常用配置解析
(1) environments标签
(2)mapper标签:常用的就第一种,后面三种用的比较少
(3)properties标签
properties文件
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/mybatis?useSSL=true&useUnicode=true&characterEncoding=utf8
jdbc.username=root
jdbc.password=lwb
核心配置文件
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<!--核心配置文件-->
<configuration>
<!--properties标签来读取外部properties文件-->
<properties resource="jdbc.properties"></properties>
<!--数据源环境-->
<environments default="development">
<environment id="development">
<!--事务管理-->
<transactionManager type="JDBC"/>
<!--数据源-->
<dataSource type="POOLED">
<!--使用$可配置文件的key来读取配置文件-->
<property name="driver" value="${jdbc.driver}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</dataSource>
</environment>
</environments>
<!--加载映射文件-->
<mappers>
<mapper resource="com/lwb/mapper/UserMapper.xml"></mapper>
</mappers>
</configuration>
(4)typeAliases标签
为类设置别名,在核心配置文件中配置类的别名,该标签在properties之后
<!--配置别名,这个标签写在properties标签后面-->
<typeAliases>
<typeAlias type="com.lwb.pojo.User" alias="user"></typeAlias>
</typeAliases>
5.3 核心配置文件小结
6.Mybatis相应的API
6.1 SqlSession工厂构建器SqlSessionFactoryBuilder
6.2 SqlSession工厂对象SqlSessionFactory
6.3 SqlSession会话对象
7.Mybatis的dao层实现
7.1 传统开发模式
(1)结构:
(2)编写UserMapper接口
public interface UserMapper {
public List<User> findAll() throws IOException;
}
(3)编写接口实现类
public class UserMapperImpl implements UserMapper {
public List<User> findAll() throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
List<User> users = sqlSession.selectList("UserMapper.findAll");
return users;
}
}
(4)编写业务层调用Dao层
//创建测试单元代替业务层
public class TestMapper {
public static void main(String[] args) throws IOException {
UserMapperImpl userMapper = new UserMapperImpl();
System.out.println(userMapper.findAll());
}
}
7.2 代理开发模式
7.2.1 介绍
使用xml配置文件代替了实现类
7.2.2 代码实现
(1)结构
(2)编写UserMapper接口
package com.lwb.mapper;
import com.lwb.pojo.User;
import java.util.List;
public interface UserMapper {
//查询所有用户
public List<User> findAll();
//通过id查询用户
public User findById(int id);
}
(3)编写UserMapper.xml实现sql语句的执行
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.lwb.mapper.UserMapper">
<!--查询操作-->
<select id="findAll" resultType="user">
select * from user
</select>
<!--通过id查询操作-->
<select id="findById" resultType="user" parameterType="int">
select * from user where id=#{id}
</select>
</mapper>
(3)编写service层调用Mapper层
//使用测试单元代替了service层
public class TestMapper {
public static void main(String[] args) throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<User> users = mapper.findAll();
System.out.println(users);
System.out.println(mapper.findById(1));
sqlSession.close();
}
}
8.MyBatis映射文件深入
8.1 动态 SQL 之
根据if的判断条件添加sql语句的条件
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.lwb.mapper.UserMapper">
<select id="findByConfigs" resultType="user" parameterType="user">
select * from user
<where>
<if test="id!=0">
and id=#{id}
</if>
<if test="username!=null">
and username=#{username}
</if>
<if test="password!=null">
and password=#{password}
</if>
</where>
</select>
</mapper>
8.2 动态 SQL 之
<select id="findByIds" parameterType="list" resultType="user">
select * from user
<where>
<foreach collection="list" open="id in (" close=")" separator="," item="id">
#{id}
</foreach>
</where>
</select>
接口
public interface UserMapper {
public List<User> findByConfigs(User user);
public List<User> findByIds(List<Integer> list);
}
测试方法
@Test
public void test2() throws IOException {
List<Integer> list = new ArrayList<Integer>();
list.add(1);
list.add(2);
list.add(4);
InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<User> users = mapper.findByIds(list);
System.out.println(users);
sqlSession.close();
}
8.3 sql片段的抽取
9.MyBatis核心配置文件深入
9.1 typeHandlers标签实现数据类型的转换
需求:数据库中生日的数据类型为bigint(毫秒值),java中User类的生日数据类型为Date类型,要实现两者交互时数据类型的转换。
(1)定义转换类继承类BaseTypeHandler并重写四个方法
package com.lwb.handle;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
public class DateHandle extends BaseTypeHandler<Date> {
@Override
//将java数据类型转换为数据库的数据类型
public void setNonNullParameter(PreparedStatement preparedStatement, int i, Date date, JdbcType jdbcType) throws SQLException {
long time = date.getTime();
preparedStatement.setLong(i,time);
}
@Override
//将数据库的数据类型转换为java类型
public Date getNullableResult(ResultSet resultSet, String s) throws SQLException {
long aLong = resultSet.getLong(s);
Date date = new Date(aLong);
return date;
}
@Override
//将数据库的数据类型转换为java类型
public Date getNullableResult(ResultSet resultSet, int i) throws SQLException {
long aLong = resultSet.getLong(i);
Date date = new Date(aLong);
return date;
}
@Override
//将数据库的数据类型转换为java类型
public Date getNullableResult(CallableStatement callableStatement, int i) throws SQLException {
long aLong = callableStatement.getLong(i);
Date date = new Date(aLong);
return date;
}
}
(2)在Mybatis核心配置文件中进行注册
<!--注册自定义类型转换-->
<typeHandlers>
<typeHandler handler="com.lwb.handle.DateHandle"></typeHandler>
</typeHandlers>
(3)UserMapper接口和测试类
UserMapper接口
public interface UserMapper {
public void insert(User user);
public User selectById(int id);
}
测试类
public class MapperTest {
//插入用户
@Test
public void test1() throws IOException {
User user = new User();
user.setId(7);
user.setUsername("tianmou");
user.setPassword("4784");
user.setBirthday(new Date());
InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
mapper.insert(user);
sqlSession.commit();
sqlSession.close();
}
//通过id查询用户
@Test
public void test2() throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
System.out.println(mapper.selectById(1));
}
}
9.2 plugins标签实现分页功能
(1)导入相关依赖
<!--分页助手-->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>3.7.5</version>
</dependency>
<dependency>
<groupId>com.github.jsqlparser</groupId>
<artifactId>jsqlparser</artifactId>
<version>0.9.1</version>
</dependency>
</dependencies>
(2)在mybatis核心配置文件中配置PageHelper插件
<!--配置分页助手-->
<plugins>
<plugin interceptor="com.github.pagehelper.PageHelper">
<property name="dialect" value="mysql"/>
</plugin>
</plugins>
(3)测试分页数据的获取
//测试分页
@Test
public void test3() throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
//设置分页相关的参数 当前的页数和条数
PageHelper.startPage(2,2);
List<User> users = mapper.findAll();
for(User user:users){
System.out.println(user);
}
//获取其他相关参数
PageInfo<User> pageinfo = new PageInfo<User>(users);
System.out.println("总条数:"+pageinfo.getTotal());
System.out.println("总页数:"+pageinfo.getPages());
System.out.println("当前页数:"+pageinfo.getPageNum());
System.out.println("每页显示条数:"+pageinfo.getPageSize());
System.out.println("上一页页数:"+pageinfo.getPrePage());
System.out.println("下一页页数:"+pageinfo.getNextPage());
System.out.println("是否是第一页:"+pageinfo.isIsFirstPage());
System.out.println("是否是最后一页:"+pageinfo.isIsLastPage());
sqlSession.close();
}
10.多表查询
10.1 一对一查询
(1)创建订单表和用户表和查询语句如下
SELECT *,user.id uid,orders.id oid FROM USER,orders WHERE user.id=orders.uid
(2)创建对应的bean对象
(3)在mapper层创建对应的mapper接口
package com.lwb.mapper;
import com.lwb.pojo.User;
import java.util.List;
public interface UserMapper {
}
package com.lwb.mapper;
import com.lwb.pojo.Orders;
import java.util.List;
public interface OrdersMapper {
public List<Orders> findAll();
}
(3)一对一查询的OrderMapper.xml如下
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--OrderMapper接口的映射-->
<mapper namespace="com.lwb.mapper.OrdersMapper">
<!--
手动指定字体与属性类的映射关系
column表示字段名称
property表示实体的属性名称
-->
<resultMap id="orderMap" type="orders">
<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>
</resultMap>
<select id="findAll" resultMap="orderMap">
SELECT *,user.id uid,orders.id oid FROM USER,orders WHERE user.id=orders.uid
</select>
</mapper>
(4)在核心配置文件中配置加载OrderMapper.xml和UserMapper.xml
<!--添加映射-->
<mappers>
<mapper resource="com/lwb/mapper/UserMapper.xml"></mapper>
<mapper resource="com/lwb/mapper/OrderMapper.xml"></mapper>
</mappers>
(5)编写测试类
//测试一对一多表查询
@Test
public void test3() throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
OrdersMapper mapper = sqlSession.getMapper(OrdersMapper.class);
List<Orders> orders = mapper.findAll();
for(Orders orders1:orders){
System.out.println(orders1);
}
sqlSession.close();
}
运行结果
10.2 OrderMapper.xml另外一种配置方式
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--OrderMapper接口的映射-->
<mapper namespace="com.lwb.mapper.OrdersMapper">
<!--
手动指定字体与属性类的映射关系
column表示字段名称
property表示实体的属性名称
-->
<resultMap id="orderMap" type="orders">
<id column="oid" property="id"></id>
<result column="ordertime" property="ordertime"></result>
<result column="total" property="total"></result>
<!--第一个user表示order的属性,第二user是User类的别名-->
<association property="user" javaType="user">
<id column="uid" property="id"></id>
<result column="username" property="username"></result>
<result column="password" property="password"></result>
</association>
</resultMap>
<select id="findAll" resultMap="orderMap">
SELECT *,user.id uid,orders.id oid FROM orders,users WHERE orders.uid=user.id
</select>
</mapper>
10.3 一对多查询
(1)在user类中新建一个集合存放订单
(2)UserMapper接口中定义方法
public interface UserMapper {
public List<User> findAll();
}
(3)映射配置
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.lwb.mapper.UserMapper">
<!--自定义结果的映射-->
<resultMap id="userMap" type="user">
<id column="id" property="id"></id>
<result column="username" property="username"></result>
<result column="password" property="password"></result>
<collection property="ordersList" ofType="orders">
<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 *,orders.id oid from user,orders where user.id=orders.uid
</select>
</mapper>
(4)测试方法
//测试一对多多表查询
@Test
public void test4() throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<User> users = mapper.findAll();
for(User user:users){
System.out.println(user);
}
sqlSession.close();
}
测试结果(截图太长截不完)
10.4 多对多查询
三个表如图
user表
role表
user_role表
(1)在User类中添加集合存放角色
(2)userMapper接口中定义方法
public interface UserMapper {
public List<User> findUserAndRole();
}
(3)映射
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.lwb.mapper.UserMapper">
<!--多对多结果映射-->
<resultMap id="userAndRoleMap" type="user">
<id column="userid" property="id"></id>
<result column="username" property="username"></result>
<result column="password" property="password"></result>
<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="findUserAndRole" resultMap="userAndRoleMap">
select * from user u,sys_role r,sys_user_role ur where u.id=ur.userid and r.id=ur.roleid
</select>
</mapper>
(4)测试方法
//测试多对多多表查询
@Test
public void test5() throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<User> users = mapper.findUserAndRole();
for(User user:users){
System.out.println(user);
}
sqlSession.close();
}
测试结果
10.5 多表查询总结
11.mabatis注解开发
11.1 常用注解
11.2 mybatis的增删查改
(1)在mapper接口中使用注解完成增删改查的方法
package com.lwb.mapper;
import com.lwb.pojo.User;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import java.util.List;
public interface UserMapper {
@Insert("insert into user values(#{id},#{username},#{password})")
public void insert(User user);
@Update("update user set username=#{username},password=#{password} where id=#{id}")
public void update(User user);
@Delete("delete from user where id=#{id}")
public void delete(int id);
@Select("select * from user where id = #{id}")
public User findById(int id);
@Select("select * from user")
public List<User> findAll();
}
(2)在核心配置文件中添加映射关系
<!--添加映射-->
<mappers>
<package name="com.lwb.mapper"/>
</mappers>
11.3 一对一查询注解开发
查询orders表以及对应订单的用户信息:通过两种注解的方式(详细看以下代码)
package com.lwb.mapper;
import com.lwb.pojo.Orders;
import com.lwb.pojo.User;
import org.apache.ibatis.annotations.One;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import java.util.List;
public interface OrdersMapper {
//一对一查询方式1
@Select("select *,o.id oid from orders o,user u where o.uid=u.id")
@Results(value = {
@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<Orders> findAll1();
//一对一查询方式2
@Select("select * from orders")
@Results(value = {
@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.lwb.mapper.UserMapper.findById")
)
})
public List<Orders> findAll2();
}
11.4 一对多查询注解开发
查询user表以及用户的订单信息
(1)在OrdersMapper接口中定义通过id查询订单的方法
//查询根据用户id订单
@Select("select * from orders where uid=#{uid}")
public List<Orders> findByUid(int uid);
(2)在UserMapper接口中定义查询用户以及对应订单的方法
//查询用户信息和订单信息(一对多查询)
@Select("select * from user")
@Results({
@Result(column = "id",property = "id"),
@Result(column = "username",property = "username"),
@Result(column = "password",property = "password"),
@Result(column = "id",property = "ordersList",
javaType = List.class,
many=@Many(select = "com.lwb.mapper.OrdersMapper.findByUid"))
})
public List<User> findUserAndOrders();
}
11.5 多对多查询
查询user表以及每个用户对应的所有角色
(1)多对多模型展示
(2)创建RoleMapper接口以及通过id查询
package com.lwb.mapper;
import com.lwb.pojo.Role;
import org.apache.ibatis.annotations.Select;
public interface RoleMapper {
//根据id查询角色
@Select("select * from sys_user_role ur,sys_role r where ur.roleId=r.id")
public Role findById(int id);
}
(3)在UserMapper中完成多对多查询
//查询用户及其角色
@Select("select * from user")
@Results({
@Result(column = "id",property = "id"),
@Result(column = "username",property = "username"),
@Result(column = "password",property = "password"),
@Result(
column = "id",property = "roleList",javaType = List.class,
many = @Many(select = "com.lwb.mapper.RoleMapper.findById")
)
})
public List<User> findUserAndRoleAll();
}