一、什么是MyBatis?
- MyBatis 是一款优秀的半自动化的持久层框架,在互联网公司使用率非常高。
- MyBatis 支持自定义SQL、存储过程、高级映射、插件。
- MyBatis 对JDBC的能力,进行了更高维度的抽象,操作数据库更加简单、高效。
- MyBatis 通过XML或注解来配置和映射原始类型、接口、Java对象,作为数据库中的记录。
二、第一个MyBatis应用
独立版本
链接:learn-mybatis: 从0到1学习mybatis - Gitee.com
java代码
public class HelloMybatis {
private static SqlSessionFactory sqlSessionFactory;
static {
try {
// 获取 SqlSessionFactory对象
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
} catch (IOException e) {
e.printStackTrace();
}
}
// 既然有了 SqlSessionFactory,顾名思义,我们可以从中获得 SqlSession 的实例。
// SqlSession 提供了在数据库执行 SQL 命令所需的所有方法。
public static SqlSession getSqlSession() {
return sqlSessionFactory.openSession();
}
}
public interface UserDao {
List<User> getUsers();
}
public class User {
private int id;
private String name;
}
配置
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"https://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.cj.jdbc.Driver"/>
<property name="url"
value="jdbc:mysql://localhost:3306/root?useSSL=true&useUnicode=true&characterEncoding=UTF-8"/>
<property name="username" value="root"/>
<property name="password" value="Mysql123()"/>
</dataSource>
</environment>
</environments>
<!-- 每一个Mapper.xml都需要在MyBatis核心配置文件中注册-->
<mappers>
<mapper resource="user-mapper.xml"/>
</mappers>
</configuration>
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--namespace绑定了一个对应的Dao/Mapper接口-->
<mapper namespace="com.junzitaotao.mybatis.hello.dao.UserDao">
<!-- select查询 -->
<select id="getUsers" resultType="com.junzitaotao.mybatis.hello.po.User">
select id, name
from user
</select>
</mapper>
整合SpringBoot版本
链接:learn-mybatis: 从0到1学习mybatis - Gitee.com
java代码
@SpringBootApplication
@MapperScan("com.junzitaotao.mybatis.springboot.dao")
public class Application implements CommandLineRunner {
public static void main(String[] args) {
SpringApplication.run(Application.class, args);
}
@Resource
private UserService userService;
// 测试代码
@Override
public void run(String... args) throws Exception {
List<User> users = userService.getUsers();
System.out.println("=====================================================");
for (User user : users) {
System.out.println(user);
}
System.out.println("=====================================================");
}
}
@Mapper
public interface UserDao {
List<User> getUsers();
}
@Service
public class UserService {
@Resource
private UserDao userDao;
public List<User> getUsers() {
return userDao.getUsers();
}
}
public class User {
private int id;
private String name;
}
配置
project.name=learn-mybatis
# mysql
spring.datasource.url=jdbc:mysql://localhost:3306/root?useSSL=true&useUnicode=true&characterEncoding=UTF-8
spring.datasource.username=root
spring.datasource.password=Mysql123()
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
# mybatis
mybatis.mapper-locations=classpath:*.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--namespace绑定了一个对应的Dao/Mapper接口-->
<mapper namespace="com.junzitaotao.mybatis.springboot.dao.UserDao">
<!-- select查询 -->
<select id="getUsers" resultType="com.junzitaotao.mybatis.springboot.po.User">
select id, name
from user
</select>
</mapper>
三、MyBatis式的CRUD
链接:learn-mybatis: 从0到1学习mybatis - Gitee.com
代码
@SpringBootApplication
@MapperScan("com.junzitaotao.mybatis.springboot.dao")
public class Application implements CommandLineRunner {
public static void main(String[] args) {
SpringApplication.run(Application.class, args);
}
@Resource
private UserService userService;
@Override
public void run(String... args) {
System.out.println("======================== start =============================");
System.out.println("======================= insert ==============================");
UserDTO user = new UserDTO("Lucy");
Long userId = userService.insert(user);
System.out.println("userId = " + userId);
System.out.println("======================== select =============================");
UserDTO newUser = userService.getUser(userId);
System.out.println(newUser.toString());
System.out.println("======================== update =============================");
userService.updateName(userId, "Robby");
newUser = userService.getUser(userId);
System.out.println(newUser.toString());
System.out.println("======================== delete =============================");
userService.deleteById(userId);
newUser = userService.getUser(userId);
System.out.println(Objects.isNull(newUser) ? "空" : newUser.toString());
System.out.println("======================== end =============================");
}
}
@Mapper
public interface UserDao {
// insert
int insert(UserDO convertDO);
// select
UserDO getById(@Param("id") Long id);
// update
int updateName(@Param("id") Long id, @Param("name") String name);
// delete
int deleteById(@Param("id") Long id);
}
public class UserDO {
private Long id;
private String name;
}
public class UserDTO implements Serializable {
private Long id;
private String name;
}
@Service
public class UserService {
@Resource
private UserDao userDao;
// insert
public Long insert(UserDTO user) {
if (Objects.isNull(user)) {
return null;
}
UserDO userDO = UserConverts.convertDO(user);
userDao.insert(userDO);
return userDO.getId();
}
// select
public UserDTO getUser(Long userId) {
return UserConverts.convertDTO(userDao.getById(userId));
}
// update
public void updateName(Long userId, String name) {
userDao.updateName(userId, name);
}
// delete
public void deleteById(Long userId) {
userDao.deleteById(userId);
}
}
public class UserConverts {
public static UserDO convertDO(UserDTO user) {
if (Objects.isNull(user)) {
return null;
}
UserDO userDO = new UserDO();
BeanUtils.copyProperties(user, userDO);
return userDO;
}
public static List<UserDO> convertDOS(List<UserDTO> users) {
if (CollectionUtils.isEmpty(users)) {
return Collections.emptyList();
}
List<UserDO> userDOS = new ArrayList<>();
for (UserDTO user : users) {
UserDO userDO = new UserDO();
BeanUtils.copyProperties(user, userDO);
userDOS.add(userDO);
}
return userDOS;
}
public static UserDTO convertDTO(UserDO user) {
if (Objects.isNull(user)) {
return null;
}
UserDTO userDTO = new UserDTO();
BeanUtils.copyProperties(user, userDTO);
return userDTO;
}
public static List<UserDTO> convertDTOS(List<UserDO> users) {
if (CollectionUtils.isEmpty(users)) {
return Collections.emptyList();
}
List<UserDTO> userDTOS = new ArrayList<>();
for (UserDO user : users) {
UserDTO userDTO = new UserDTO();
BeanUtils.copyProperties(user, userDTO);
userDTOS.add(userDTO);
}
return userDTOS;
}
}
配置
project.name=learn-mybatis
# mysql
spring.datasource.url=jdbc:mysql://localhost:3306/root?useSSL=true&useUnicode=true&characterEncoding=UTF-8
spring.datasource.username=root
spring.datasource.password=Mysql123()
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
# mybatis
mybatis.mapper-locations=classpath:*.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--namespace绑定了一个对应的Dao/Mapper接口-->
<mapper namespace="com.junzitaotao.mybatis.crud.dao.UserDao">
<insert id="insert" parameterType="com.junzitaotao.mybatis.crud.domain.UserDO">
<!-- LAST_INSERT_ID():查询刚刚插入的id -->
<!-- keyProperty:设置接收回传主键的属性 -->
<selectKey keyProperty="id" keyColumn="id" resultType="long" order="AFTER">
SELECT LAST_INSERT_ID()
</selectKey>
insert user (name) values (#{name})
</insert>
<!-- select查询 -->
<select id="getById" resultType="com.junzitaotao.mybatis.crud.domain.UserDO">
select id, name
from user
where id = #{id}
</select>
<update id="updateName">
update user
set name = #{name}
where id = #{id}
</update>
<delete id="deleteById">
delete from user
where id = #{id}
</delete>
</mapper>
四、参数处理
链接:learn-mybatis: 从0到1学习mybatis - Gitee.com
单个简单类型参数
简单类型包括:
- 七种数据类型(除了boolean)以及他们的包装类
- String
- java.util.Date
- java.sql.Date
@Mapper
public interface UserDao {
/**
* 当接口的方法的参数只有一个,并且参数的数据类型都是简单类型
* 根据id、name查询
*/
UserDO getById(Long id);
UserDO getByName(String name);
}
parameterType属性: 表示方法的参数类型,但MyBatis能感知类型,所以parameterType属性可以省略不写
<select id="getById" resultType="com.junzitaotao.mybatis.crud.domain.UserDO">
select id, name
from user
where id = #{id}
</select>
<select id="getByName" resultType="com.junzitaotao.mybatis.crud.domain.UserDO">
select id, name
from user
where name = #{name}
</select>
Map参数
插入信息
@Mapper
public interface UserDao {
int insertByMap(Map<String, Object> map);
}
<insert id="insertByMap" parameterType="map">
insert into user
values (#{id},#{name})
</insert>
查询单个信息
@Mapper
public interface UserDao {
/**
* 根据id获取用户,放到Map集合
*/
Map<String, Object> getByIdForMap(Long id);
}
<select id="getByIdForMap" resultType="map">
select id, name
from user
where id = #{id}
</select>
返回多个Map
@Mapper
public interface UserDao {
List<Map<String, Object>> getAllForMaps();
}
<!--注意:resultType依然是map,不是list-->
<select id="getAllForMaps" resultType="map">
select id, name
from user
</select>
业务类型参数
@Mapper
public interface UserDao {
/**
* 参数类型是业务类型,不再是基础类型
*/
int insertUser(UserDO user);
}
<insert id="insertUser">
insert into user
values (#{id}, #{name})
</insert>
方法带多个参数(@Param)
不使用arg0、arg1、param1、param2等等,使用@Param注解,可读性强。
@Mapper
public interface UserDao {
UserDO getByIdName(@Param("id") Long id,
@Param("name") String name);
}
<select id="getByIdName" resultType="com.junzitaotao.mybatis.crud.domain.UserDO">
select id, name
from user
where id = #{id} and name = #{name}
</select>
返回结果映射
resultType
虽然能满足返回结果的映射需求,但是不够灵活,MyBatis官方推荐resultMap
。
@Mapper
public interface UserDao {
UserDO getByIdForResultMap(Long id);
}
<mapper namespace="com.xxxx.UserDao">
<!--
1、定义一个结果映射
2、type属性:用来指定POJO类的类名
-->
<resultMap id="userResultMap" type="com.junzitaotao.mybatis.crud.domain.UserDO">
<id property="id" column="id" />
<result property="name" column="name" />
</resultMap>
<select id="getByIdForResultMap" resultMap="userResultMap">
select id, name
from user
where id = #{id}
</select>
</mapper>
五、动态SQL
链接:learn-mybatis: 从0到1学习mybatis - Gitee.com
IF
if就是简单的条件判断,利用if语句我们可以实现某些简单的条件选择。先来看如下一个例子:
<select id="dynamicIfTest" parameterType="Blog" resultType="Blog">
select * from t_blog where 1 = 1
<if test="title != null">
and title = #{title}
</if>
<if test="content != null">
and content = #{content}
</if>
<if test="owner != null">
and owner = #{owner}
</if>
</select>
这条语句的意思非常简单,如果你提供了title参数,那么就要满足title=#{title}
,同样如果你提供了Content和Owner的时候,它们也需要满足相应的条件,之后就是返回满足这些条件的所有Blog,这是非常有用的一个功能,以往我们使用其他类型框架或者直接使用JDBC的时候, 如果我们要达到同样的选择效果的时候,我们就需要拼SQL语句,这是极其麻烦的,比起来,上述的动态SQL就要简单多了。
CHOOSE
choose元素的作用就相当于JAVA中的switch语句,基本上跟JSTL中的choose的作用和用法是一样的,通常都是与when和otherwise搭配的。看如下一个例子:
<select id="dynamicChooseTest" parameterType="Blog" resultType="Blog">
select * from t_blog where 11 = 1
<choose>
<when test="title != null">
and title = #{title}
</when>
<when test="content != null">
and content = #{content}
</when>
<otherwise>
and owner = "owner1"
</otherwise>
</choose>
</select>
when元素表示当when中的条件满足的时候就输出其中的内容,跟JAVA中的switch效果差不多的是按照条件的顺序,当when中有条件满足的时候,就会跳出choose,即所有的when和otherwise条件中,只有一个会输出,当所有的我很条件都不满足的时候就输出otherwise中的内容。所以上述语句的意思非常简单, 当title!=null的时候就输出and titlte = #{title}
,不再往下判断条件,当title为空且content!=null
的时候就输出and content = #{content}
,当所有条件都不满足的时候就输出otherwise中的内容。
WHERE
where语句的作用主要是简化SQL语句中where中的条件判断的,先看一个例子,再解释一下where的好处。
<select id="dynamicWhereTest" parameterType="Blog" resultType="Blog">
select * from t_blog
<where>
<if test="title != null">
title = #{title}
</if>
<if test="content != null">
and content = #{content}
</if>
<if test="owner != null">
and owner = #{owner}
</if>
</where>
</select>
where元素的作用是会在写入where元素的地方输出一个where,另外一个好处是你不需要考虑where元素里面的条件输出是什么样子的,MyBatis会智能的帮你处理,如果所有的条件都不满足那么MyBatis就会查出所有的记录,如果输出后是and 开头的,MyBatis会把第一个and忽略,当然如果是or开头的,MyBatis也会把它忽略;此外,在where元素中你不需要考虑空格的问题,MyBatis会智能的帮你加上。像上述例子中,如果title=null, 而content != null,那么输出的整个语句会是select * from t_blog where content = #{content}
,而不是select * from t_blog where and content = #{content}
,因为MyBatis会智能的把首个and 或 or 给忽略。
TRIM
trim元素的主要功能是可以在自己包含的内容前加上某些前缀,也可以在其后加上某些后缀,与之对应的属性是prefix和suffix;可以把包含内容的首部某些内容覆盖,即忽略,也可以把尾部的某些内容覆盖,对应的属性是prefixOverrides和suffixOverrides;正因为trim有这样的功能,所以我们也可以非常简单的利用trim来代替where元素的功能,示例代码如下:
<select id="dynamicTrimTest" parameterType="Blog" resultType="Blog">
select * from t_blog
<trim prefix="where" prefixOverrides="and | or">
<if test="title != null">
title = #{title}
</if>
<if test="content != null">
and content = #{content}
</if>
<if test="owner != null">
or owner = #{owner}
</if>
</trim>
</select>
SET
set元素主要是用在更新操作的时候,它的主要功能和where元素其实是差不多的,主要是在包含的语句前输出一个set,然后如果包含的语句是以逗号结束的话将会把该逗号忽略,如果set包含的内容为空的话则会出错。有了set元素我们就可以动态的更新那些修改了的字段。下面是一段示例代码:
<update id="dynamicSetTest" parameterType="Blog">
update t_blog
<set>
<if test="title != null">
title = #{title},
</if>
<if test="content != null">
content = #{content},
</if>
<if test="owner != null">
owner = #{owner}
</if>
</set>
where id = #{id}
</update>
上述示例代码中,如果set中一个条件都不满足,即set中包含的内容为空的时候就会报错。
FOREACH
foreach的主要用在构建in条件中,它可以在SQL语句中进行迭代一个集合。foreach元素的属性主要有item,index,collection,open,separator,close。item表示集合中每一个元素进行迭代时的别名,index指定一个名字,用于表示在迭代过程中,每次迭代到的位置,open表示该语句以什么开始,separator表示在每次进行迭代之间以什么符号作为分隔符,close表示以什么结束,在使用foreach的时候最关键的也是最容易出错的就是collection属性,该属性是必须指定的,但是在不同情况下,该属性的值是不一样的,主要有一下3种情况:
- 如果传入的是单参数且参数类型是一个List的时候,collection属性值为list
- 如果传入的是单参数且参数类型是一个array数组的时候,collection的属性值为array
- 如果传入的参数是多个的时候,我们就需要把它们封装成一个Map了,当然单参数也可以封装成map,实际上如果你在传入参数的时候,在MyBatis里面也是会把它封装成一个Map的,map的key就是参数名,所以这个时候collection属性值就是传入的List或array对象在自己封装的map里面的key
下面分别来看看上述三种情况的示例代码:
1.单参数List的类型:
<select id="dynamicForeachTest" resultType="Blog">
select * from t_blog where id in
<foreach collection="list" index="index" item="item" open="(" separator="," close=")">
#{item}
</foreach>
</select>
上述collection的值为list,对应的Mapper是这样的
public List<Blog> dynamicForeachTest(List<Integer> ids);
@Test
public void dynamicForeachTest() {
SqlSession session = Util.getSqlSessionFactory().openSession();
BlogMapper blogMapper = session.getMapper(BlogMapper.class);
List<Integer> ids = new ArrayList<Integer>();
ids.add(1);
ids.add(3);
ids.add(6);
List<Blog> blogs = blogMapper.dynamicForeachTest(ids);
for (Blog blog : blogs) System.out.println(blog);
session.close();
}
2.单参数array数组的类型:
<select id="dynamicForeach2Test" resultType="Blog">
select * from t_blog where id in
<foreach collection="array" index="index" item="item" open="(" separator="," close=")">
#{item}
</foreach>
</select>
上述collection为array,对应的Mapper代码:
public List<Blog> dynamicForeach2Test(int[] ids);
@Test
public void dynamicForeach2Test() {
SqlSession session = Util.getSqlSessionFactory().openSession();
BlogMapper blogMapper = session.getMapper(BlogMapper.class);
int[] ids = new int[] {1,3,6,9};
List<Blog> blogs = blogMapper.dynamicForeach2Test(ids);
for (Blog blog : blogs) System.out.println(blog);
session.close();
}
3.自己把参数封装成Map的类型
<select id="dynamicForeach3Test" resultType="Blog">
select * from t_blog where title like "%"#{title}"%" and id in
<foreach collection="ids" index="index" item="item" open="(" separator="," close=")">
#{item}
</foreach>
</select>
上述collection的值为ids,是传入的参数Map的key,对应的Mapper代码:
public List<Blog> dynamicForeach3Test(Map<String, Object> params);
@Test
public void dynamicForeach3Test() {
SqlSession session = Util.getSqlSessionFactory().openSession();
BlogMapper blogMapper = session.getMapper(BlogMapper.class);
final List<Integer> ids = new ArrayList<Integer>();
ids.add(1);
ids.add(2);
ids.add(3);
ids.add(6);
ids.add(7);
ids.add(9);
Map<String, Object> params = new HashMap<String, Object>();
params.put("ids", ids);
params.put("title", "中国");
List<Blog> blogs = blogMapper.dynamicForeach3Test(params);
for (Blog blog : blogs) System.out.println(blog);
session.close();
}
sql、include
sql标签:用来声明sql片段;
include标签:将sql片段,包含到某个sql语句当中。
<mapper namespace="com.xxx.UserDao">
<!--声明一个SQL片段-->
<sql id="userColumns">
id, name
</sql>
<!--将声明的SQL片段包含进来-->
<select id="selectById" resultType="com.xxx.UserDO">
select <include refid="userColumns" />
from user
where id = #{id}
</select>
</mapper>
六、MyBatis推荐玩法
设计原则:
- 开发效率、排查效率、可读性,均衡考量。绝对的开发效率,绝对的故障率,这是无数重大故障的血的教训。
- 分层开发:service => repository => dao
- service,负责处理业务逻辑
- repository,负责数据转换,判断数据库操作是否成功,或者其他个性化的纯数据处理,不含业务逻辑。
- dao,定义操作数据库接口
- 通用SQL兜底:SQL不要过于动态化,一个操作场景,一个SQL,虽然效率低一点,但是可读性和排查效率,要高很多。动态化强的场景,可以用通用SQL,例如页面的分页查询。
链接:learn-mybatis: 从0到1学习mybatis - Gitee.com
代码
@Service("userService")
public class UserServiceImpl implements UserService {
@Resource
private UserRepository userRepository;
@Override
public Long create(UserDTO user) {
userRepository.create(user);
return user.getId();
}
@Override
public List<Long> createBatch(List<UserDTO> users) {
userRepository.createBatch(users);
return Optional.ofNullable(users)
.map(Collection::stream)
.orElse(Stream.empty())
.map(UserDTO::getId)
.filter(Objects::nonNull)
.collect(Collectors.toList());
}
@Override
public UserDTO queryById(Long id) {
return userRepository.queryById(id);
}
@Override
public List<UserDTO> queryByIds(List<Long> ids) {
return userRepository.queryByIds(ids);
}
/**
* 该方法仅供动态化的分页查询使用
* 如果是个性化的单条查询、批量查询等业务属性比较明显的方法,尽量创建新方法,提高代码可读性、排查效率
*/
@Override
public List<UserDTO> queryPage(UserQuery query) {
return userRepository.queryPage(query);
}
@Override
public int queryCount(UserQuery query) {
return userRepository.queryCount(query);
}
@Override
public Pagination<UserDTO> queryPagination(UserQuery query) {
List<UserDTO> users = userRepository.queryPage(query);
int count = userRepository.queryCount(query);
return Pagination.<UserDTO>of()
.data(users)
.count(count)
.pageSize(query.getPageSize())
.pageNo(query.getPageNo());
}
@Override
public void updateById(UserQuery query) {
userRepository.updateById(query);
}
@Override
public void updateByIds(UserQuery query) {
userRepository.updateByIds(query);
}
@Override
public void deleteById(Long id) {
userRepository.deleteById(id);
}
@Override
public void deleteByIds(List<Long> ids) {
userRepository.deleteByIds(ids);
}
@Override
public UserDTO queryByName(String name) {
return userRepository.queryByName(name);
}
@Override
public void updateNameById(Long id, String name) {
userRepository.updateNameById(id, name);
}
}
@Component("userRepository")
public class UserRepositoryImpl implements UserRepository {
@Resource
private UserDao userDao;
@Override
public void create(UserDTO user) {
UserDO userDO = UserConverts.dto2do(user);
Assert.notNull(userDO, "参数为空");
int result = userDao.create(userDO);
Assert.isTrue(result == 1, "插入失败");
Assert.notNull(userDO.getId(), "ID获取失败");
user.setId(userDO.getId());
}
@Override
public void createBatch(List<UserDTO> users) {
List<UserDO> userDOS = UserConverts.dto2do(users);
Assert.notEmpty(userDOS, "参数为空");
int result = userDao.createBatch(userDOS);
Assert.isTrue(result == users.size(), "插入失败");
for (int i = 0; i < result; i++) {
UserDTO user = users.get(i);
user.setId(userDOS.get(i).getId());
Assert.notNull(user.getId(), "ID获取失败");
}
}
@Override
public UserDTO queryById(Long id) {
return UserConverts.do2dto(userDao.queryById(id));
}
@Override
public List<UserDTO> queryByIds(List<Long> ids) {
return UserConverts.do2dto(userDao.queryByIds(ids));
}
@Override
public List<UserDTO> queryPage(UserQuery query) {
return UserConverts.do2dto(userDao.queryPage(query));
}
@Override
public int queryCount(UserQuery query) {
return userDao.queryCount(query);
}
@Override
public void updateById(UserQuery query) {
int result = userDao.updateById(query);
Assert.isTrue(result == 1, "更新失败");
}
@Override
public void updateByIds(UserQuery query) {
int result = userDao.updateByIds(query);
Assert.isTrue(result == query.getIds().size(), "更新失败");
}
@Override
public void deleteById(Long id) {
int result = userDao.deleteById(id);
Assert.isTrue(result == 1, "删除失败");
}
@Override
public void deleteByIds(List<Long> ids) {
int result = userDao.deleteByIds(ids);
Assert.isTrue(result == ids.size(), "删除失败");
}
// 业务方法
@Override
public void updateNameById(Long id, String name) {
int result = userDao.updateNameById(id, name);
Assert.isTrue(result == 1, "更新失败");
}
@Override
public UserDTO queryByName(String name) {
return UserConverts.do2dto(userDao.queryByName(name));
}
}
配置
<?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.junzitaotao.mybatis.production.dao.UserDao">
<resultMap id="baseResultMap" type="com.junzitaotao.mybatis.production.domain.UserDO">
<result property="id" column="id"/>
<result property="name" column="name"/>
</resultMap>
<sql id="query_column_list">
<include refid="insert_column_list"/>
</sql>
<sql id="insert_column_list">
id, name
</sql>
<sql id="where_condition">
where 1 = 1
<if test="id != null">
and id = #{id}
</if>
<if test="name != null and name != ''">
and name = #{name}
</if>
</sql>
<sql id="base_update_column_list">
<if test="id != null">
id = #{id},
</if>
<if test="name != null and name != ''">
name = #{name},
</if>
gmt_modified = now()
</sql>
<insert id="create" useGeneratedKeys="true" keyProperty="id">
insert into user (<include refid="insert_column_list"/>)
values ( #{id}, #{name} )
</insert>
<insert id="createBatch" parameterType="list" useGeneratedKeys="true" keyColumn="id" keyProperty="id">
insert into user (<include refid="insert_column_list"/>)
values
<foreach collection="list" open="" close="" item="item" separator=",">
(#{item.id}, #{item.name})
</foreach>
</insert>
<select id="queryById" resultMap="baseResultMap">
select
<include refid="query_column_list"/>
from user
where id = #{id}
</select>
<select id="queryByIds" resultMap="baseResultMap">
select
<include refid="query_column_list"/>
from user
where id in
<foreach collection="ids" item="item" open="(" close=")" separator=",">
#{item}
</foreach>
</select>
<select id="queryPage" resultMap="baseResultMap">
select
<include refid="query_column_list"/>
from user
<include refid="where_condition"/>
limit #{queryFrom}, #{queryLimit}
</select>
<select id="queryCount" resultType="java.lang.Integer">
select count(*)
from user
<include refid="where_condition"/>
</select>
<select id="queryByName" resultMap="baseResultMap">
select
<include refid="query_column_list"/>
from user
where name = #{name}
limit 1
</select>
<update id="updateById">
update user set
<include refid="base_update_column_list"/>
where id = #{id}
</update>
<update id="updateByIds">
update user set
<include refid="base_update_column_list"/>
where id in
<foreach collection="ids" item="item" open="(" close=")" separator=",">
#{item}
</foreach>
</update>
<update id="updateNameById">
update user
set name = #{name}
where id = #{id}
</update>
<delete id="deleteById">
delete from user
where id = #{id}
</delete>
<delete id="deleteByIds">
delete from user
where id in
<foreach collection="ids" item="item" open="(" close=")" separator=",">
#{item}
</foreach>
</delete>
</mapper>