Mybatis 也可以使用注解开发方式,这样我们就可以减少编写 Mapper映射文件了。下面介绍用户的增删改查。
3.1 修改全局配置文件
在mybatis 全局文件中 通过package标签或者mapper标签的class属性加载mapper接口即可。
<mappers>
<mapper class="com.xa92.mapper.UserMapper"></mapper>
</mappers>
3.2 使用注解实现复杂关系映射开发
实现复杂关系映射之前我们可以在映射文件中通过配置来实现,但通过后我们发现并没 有@ResultMap 这个注解。我们可以使用
@Results 注解,@Result 注解,@One 注解,@Many 注解。
-
@Insert:实现新增
-
@Update:实现更新
-
@Delete:实现删除
-
@Select:实现查询
-
@Result:实现结果集封装
@Result 中 属性介绍:
column 数据库的列名
Property 需要装配的属性名
one 需要使用的@One 注解(@Result(one=@One)()))
many 需要使用的@Many 注解(@Result(many=@many)()))
-
@Results:可以与@Result 一起使用,封装多个结果集
@Results 注解代替的是标签
该注解中可以使用单个@Result 注解,也可以使用@Result 集合
@Results({@Result(),@Result()})或@Results(@Result())
-
@One:实现一对一结果集封装
代替了标签,是多表查询的关键,在注解中用来指定子查询返回单一对象。
@One 注解属性介绍:
select 指定用来多表查询的 s qlmapper
fetchType 会覆盖全局的配置参数 lazyLoadingEnabled。
使用格式:
@Result(column=" “,property=”“,one=@One(select=”"))
-
@Many:实现一对多结果集封装
代替了标签,是是多表查询的关键,在注解中用来指定子查询返回对象集合。注意:聚集元素用来处理“一对多”的关系。需要指定映射的 Java 实体类的属性,属性的 javaType(一般为 ArrayList)但是注解中可以不定义; 使用格式:
@Result(property=“”,column=“”,many=@Many(select=“”))
-
@SelectProvider: 实现动态 SQL 映射
3.3 @select 注解
在UserMapper 中添加如下注解方法。
@Results(id="userResultMap",value = {
@Result(property = "id", column = "id",id = true),
@Result(property = "id", column = "userName"),
@Result(property = "sex", column = "sex"),
@Result(property = "birthday", column = "birthday"),
@Result(property = "address", column = "address")
})
@Select("select id,user_name,sex,birthday,address" +
" from t_user where id = #{id} ")
List<UserInfo> findUserById(Long id);
上面@Results 对应的就是 XML中的resultMap元素,使用这个注解可以实现属性映射。
参数上加上Id = true,代表此属性为主键列,对应 XML中的
那么如果UserMapper 有多个查询条件,需要@Results共用,在MyBatis 3.3.0 及以前版本,注解定义的 @Results 不能共用。但是从Mybatis 3.3.1版本开始,、
@Results注解增加了一个id属性,设置了id属性后,就可以通过id属性直接引用同一个@Results配置,示例代码如下:
@ResultMap("userResultMap")
@Select("select id,user_name,sex,birthday,address from t_user")
List<UserInfo> selectAll();
@Results(id="userResultMap",value = {
@Result(property = "id", column = "id",id = true),
@Result(property = "id", column = "userName"),
@Result(property = "sex", column = "sex"),
@Result(property = "birthday", column = "birthday"),
@Result(property = "address", column = "address")
})
@Select("select id,user_name,sex,birthday,address" +
" from t_user where id = #{id} ")
List<UserInfo> findUserById(Long id);
使用 @ResultMap(“userResultMap”)可以直接对设置好的resultMap进行直接引用。
测试代码如下:
@Test
public void selectAll(){
SqlSession sqlSession = sqlSessionFactory.openSession();
try{
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<UserInfo> userList = mapper.selectAll();
System.out.println(userList);
}finally{
sqlSession.close();
}
}
@Test
public void findUserById(){
SqlSession sqlSession = sqlSessionFactory.openSession();
try{
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
UserInfo userInfo = mapper.findUserById(1L);
System.out.println("inert success....."+userInfo);
}finally{
sqlSession.close();
}
}
查询日志:
[com.xa02.mapper.UserMapper.selectAll]-==> Preparing: select id,user_name,sex,birthday,address from t_user
[com.xa02.mapper.UserMapper.selectAll]-==> Parameters:
[com.xa02.mapper.UserMapper.selectAll]-<== Total: 9
[com.xa02.mapper.UserMapper.findUserById]-==> Preparing: select id,user_name,sex,birthday,address from t_user where id = ?
[com.xa02.mapper.UserMapper.findUserById]-==> Parameters: 1(Long)
[com.xa02.mapper.UserMapper.findUserById]-<== Total: 1
3.4 @insert注解
1.不需要返回主键值
@Insert("insert into t_user(user_name,sex,birthday,address)values(#{userName},#{sex},#{birthday},#{address})")
Integer insertUser(UserInfo userInfo);
2.返回自增主键值
在XML中需要如下配置:
<insert id="insertUser" parameterType="UserInfo" useGeneratedKeys="true" keyProperty="id">
INSERT INTO t_user(user_name,sex,birthday,address ) VALUES
(#{userName},#{sex},#{birthday},#{address});
</insert>
在接口中配置:
@Insert("insert into t_user(user_name,sex,birthday,address)" +
"values(#{userName},#{sex},#{birthday},#{address})")
@Options(useGeneratedKeys = true,keyProperty = "id")
Integer insertUser(UserInfo userInfo);
3.返回非自增主键
在XML中的配置
<insert id="insertUser" parameterType="UserInfo" >
<selectKey resultType="long" keyProperty="id" order="BEFORE">
select max(id)+1 from t_user
</selectKey>
INSERT INTO t_user(id,user_name,sex,birthday,address ) VALUES
(#{id},#{userName},#{sex},#{birthday},#{address});
</insert>
在UserMapper中配置
@Insert("insert into t_user(user_name,sex,birthday,address)" +
"values(#{userName},#{sex},#{birthday},#{address})")
@SelectKey(statement = "select max(id)+1 from t_user",
keyProperty = "id",
resultType = Long.class,
before = false
)
Integer insertUser(UserInfo userInfo);
其中 before = false 等同于XML中的order= “before”
3.5 @update 和 @delete
@Update("update t_user set user_name =#{userName},sex=#{sex},birthday=#{birthday},address=#{address} where id=#{id}")
Integer updateUser(UserInfo userInfo);
@Delete("delete from user where id=#{id}")
Integer deleteUser(Long id);
3.6 复杂查询
首先准备三张表: 用户表、订单表、订单详情表、商品表
// 订单表
CREATE TABLE `t_order` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`user_id` bigint(20) DEFAULT NULL, // 用户ID
`status` char(1) DEFAULT NULL, // 订单状态
`amount` decimal(10,2) DEFAULT NULL, // 订单金额
`create_time` datetime DEFAULT NULL, //订单创建时间
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
// 订单详情表
CREATE TABLE `t_order_detail` (
`id` bigint(20) DEFAULT NULL,
`order_id` bigint(20) DEFAULT NULL,//订单ID
`product_id` bigint(20) DEFAULT NULL,// 商品ID
`amout` decimal(10,0) DEFAULT NULL, // 金额
`num` int(11) DEFAULT NULL // 商品数量
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
3.6.1 一对一查询
需求: 根据订单Id查询出下订单的用户。
SQL:
select user_id from t_order where id = #{订单ID};
select * from t_user where id = #{用户ID}
代码实现
1.OrderMapper
public interface OrderMapper {
@Results(id="orderResultMap",value = {
@Result(property = "id", column = "id",id = true),
@Result(property = "user_id", column = "userId"),
@Result(property = "status", column = "status"),
@Result(property = "amount", column = "birthday"),
@Result(property = "create_time", column = "createTime"),
//Result(one = @One(select = "namespace.id") 代替了<assocation>标签
// select 代表查询哪个接口的方法获取数据
// property: 要封装的属性名称
// javaType : 要封装的实体类型
// column: 即Order表中根据哪个字段去查询用户表
@Result(one = @One(select = "com.xa02.mapper.UserMapper.findUserById")
,property = "userInfo" ,
javaType = com.xa02.domain.UserInfo.class ,
column = "user_id")
})
@Select("select id,user_id,status,amount,create_time" +
" from t_order where id = #{id} ")
OrderInfo findOrderById(Long id);
}
-
@Result(one = @One(select = “namespace.id”) 代替了标签
-
select 代表查询哪个接口的方法获取数据
-
property: 要封装的属性名称,即在OrderInfo中中的变量名称
public class OrderInfo { ..... private UserInfo userInfo; }
- javaType : 要封装的实体类型
- column: 即Order表中根据哪个字段去查询用户表
2.UserMapper
public interface UserMapper {
@Results(id="userResultMap",value = {
@Result(property = "id", column = "id",id = true),
@Result(property = "id", column = "userName"),
@Result(property = "sex", column = "sex"),
@Result(property = "birthday", column = "birthday"),
@Result(property = "address", column = "address")
})
@Select("select id,user_name,sex,birthday,address" +
" from t_user where id = #{id} ")
UserInfo findUserById(Long id);
}
3.测试代码
@Test
public void findOrderById(){
SqlSession sqlSession = sqlSessionFactory.openSession();
try{
OrderMapper orderMapper = sqlSession.getMapper(OrderMapper.class);
OrderInfo orderInfo = orderMapper.findOrderById(6L);
System.out.println("findOrderById....."+orderInfo);
}finally{
sqlSession.close();
}
}
日志如下:
[com.xa02.mapper.OrderMapper.findOrderById]-==> Preparing: select id,user_id,status,amount,create_time from t_order where id = ?
[com.xa02.mapper.OrderMapper.findOrderById]-==> Parameters: 6(Long)
[com.xa02.mapper.OrderMapper.findOrderById]-<== Total: 1
[com.xa02.mapper.UserMapper.findUserById]-==> Preparing: select id,user_name,sex,birthday,address from t_user where id = ?
[com.xa02.mapper.UserMapper.findUserById]-==> Parameters: 1(Long)
[com.xa02.mapper.UserMapper.findUserById]-<== Total: 1
3.6.2 一对多查询
需求: 查询某一个用户下的所有工单。
select * from t_user
seelct * from t_order where user_id = #{userId}
代码实现:
1.UserMapper
public interface UserMapper {
@Results(id="userResultMap",value = {
@Result(property = "id", column = "id",id = true),
@Result(property = "id", column = "userName"),
@Result(property = "sex", column = "sex"),
@Result(property = "birthday", column = "birthday"),
@Result(property = "address", column = "address"),
@Result(column = "id" ,
many = @Many(select = "com.xa02.mapper.OrderMapper.findOrderByUserId"),
property = "orderInfoList" ,
javaType = List.class )
})
@Select("select id,user_name,sex,birthday,address from t_user")
List<UserInfo> selectAll();
}
2.OrdderMapper
public interface OrderMapper {
@Results(id="orderResultMap",value = {
@Result(property = "id", column = "id",id = true),
@Result(property = "user_id", column = "userId"),
@Result(property = "status", column = "status"),
@Result(property = "amount", column = "birthday"),
@Result(property = "create_time", column = "createTime")
})
@Select("select id,user_id,status,amount,create_time" +
" from t_order where user_id = #{userId} ")
OrderInfo findOrderByUserId(Long userId);
}
3.UserInfo 类
public class UserInfo {
private Long id;
private String userName;
private String sex;
private Date birthday;
private String address;
private List<OrderInfo> orderInfoList;
}
4.测试类
@Test
public void selectAll(){
SqlSession sqlSession = sqlSessionFactory.openSession();
try{
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<UserInfo> userList = mapper.selectAll();
System.out.println(userList);
}finally{
sqlSession.close();
}
}
测试结果:
[com.xa02.mapper.UserMapper.selectAll]-==> Preparing: select id,user_name,sex,birthday,address from t_user
[com.xa02.mapper.UserMapper.selectAll]-==> Parameters:
[com.xa02.mapper.OrderMapper.findOrderByUserId]-====> Preparing: select id,user_id,status,amount,create_time from t_order where user_id = ?
[com.xa02.mapper.OrderMapper.findOrderByUserId]-====> Parameters: 1(Long)
[com.xa02.mapper.OrderMapper.findOrderByUserId]-<==== Total: 2
[com.xa02.mapper.OrderMapper.findOrderByUserId]-====> Preparing: select id,user_id,status,amount,create_time from t_order where user_id = ?
[com.xa02.mapper.OrderMapper.findOrderByUserId]-====> Parameters: 2(Long)
[com.xa02.mapper.OrderMapper.findOrderByUserId]-<==== Total: 0
[com.xa02.mapper.OrderMapper.findOrderByUserId]-====> Preparing: select id,user_id,status,amount,create_time from t_order where user_id = ?
[com.xa02.mapper.OrderMapper.findOrderByUserId]-====> Parameters: 10(Long)
[com.xa02.mapper.OrderMapper.findOrderByUserId]-<==== Total: 0
[com.xa02.mapper.OrderMapper.findOrderByUserId]-====> Preparing: select id,user_id,status,amount,create_time from t_order where user_id = ?
[com.xa02.mapper.OrderMapper.findOrderByUserId]-====> Parameters: 11(Long)
[com.xa02.mapper.OrderMapper.findOrderByUserId]-<==== Total: 0
[com.xa02.mapper.OrderMapper.findOrderByUserId]-====> Preparing: select id,user_id,status,amount,create_time from t_order where user_id = ?
[com.xa02.mapper.OrderMapper.findOrderByUserId]-====> Parameters: 12(Long)
[com.xa02.mapper.OrderMapper.findOrderByUserId]-<==== Total: 0
[com.xa02.mapper.OrderMapper.findOrderByUserId]-====> Preparing: select id,user_id,status,amount,create_time from t_order where user_id = ?
[com.xa02.mapper.OrderMapper.findOrderByUserId]-====> Parameters: 13(Long)
[com.xa02.mapper.OrderMapper.findOrderByUserId]-<==== Total: 0
[com.xa02.mapper.OrderMapper.findOrderByUserId]-====> Preparing: select id,user_id,status,amount,create_time from t_order where user_id = ?
[com.xa02.mapper.OrderMapper.findOrderByUserId]-====> Parameters: 14(Long)
[com.xa02.mapper.OrderMapper.findOrderByUserId]-<==== Total: 0
[com.xa02.mapper.OrderMapper.findOrderByUserId]-====> Preparing: select id,user_id,status,amount,create_time from t_order where user_id = ?
[com.xa02.mapper.OrderMapper.findOrderByUserId]-====> Parameters: 15(Long)
[com.xa02.mapper.OrderMapper.findOrderByUserId]-<==== Total: 0
[com.xa02.mapper.OrderMapper.findOrderByUserId]-====> Preparing: select id,user_id,status,amount,create_time from t_order where user_id = ?
[com.xa02.mapper.OrderMapper.findOrderByUserId]-====> Parameters: 16(Long)
[com.xa02.mapper.OrderMapper.findOrderByUserId]-<==== Total: 0
[com.xa02.mapper.UserMapper.selectAll]-<== Total: 9
[UserInfo{id=1, userName='zhangsan', sex='0', birthday=Tue Nov 17 15:07:08 CST 2020, address='上海市', orderInfoList=[OrderInfo{id=6, userId=1, status='1', amount=null, createTime=Wed Aug 31 10:31:56 CST 2022, userInfo=null}, OrderInfo{id=7, userId=1, status='1', amount=null, createTime=Thu Sep 01 11:11:35 CST 2022, userInfo=null}]}, UserInfo{id=2, userName='lisi', sex='0', birthday=Wed Nov 03 15:07:28 CST 2021, address='安徽省合肥市', orderInfoList=[]}, UserInfo{id=10, userName='tom', sex='1', birthday=Tue Sep 06 21:05:04 CST 2022, address='guangzhou', orderInfoList=[]}, UserInfo{id=11, userName='tom', sex='1', birthday=Tue Sep 06 21:11:37 CST 2022, address='guangzhou', orderInfoList=[]}, UserInfo{id=12, userName='tom', sex='1', birthday=Tue Sep 06 21:11:57 CST 2022, address='guangzhou', orderInfoList=[]}, UserInfo{id=13, userName='tom', sex='1', birthday=Tue Sep 06 21:28:36 CST 2022, address='wuhu', orderInfoList=[]}, UserInfo{id=14, userName='jack', sex='1', birthday=Tue Sep 06 22:57:55 CST 2022, address='shenzhen', orderInfoList=[]}, UserInfo{id=15, userName='jack', sex='1', birthday=Tue Sep 06 22:58:31 CST 2022, address='shenzhen', orderInfoList=[]}, UserInfo{id=16, userName='lili', sex='0', birthday=Tue Sep 06 23:06:17 CST 2022, address='shenzhen', orderInfoList=[]}]