首先需要下载前面一篇文章的代码,在前一章代码上进行修改.
SpringBoot整合Mybatis(注解方式)
复制前一个项目,修改配置文件,mybatis的相关配置为:
mybatis:
type-aliases-package: con.mybatis.springboot_mybatis.model
configuration:
map-underscore-to-camel-case: true
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
使用注解方式修改mapper
@Mapper
public interface UserMapper {
@Select("SELECT * FROM users")
@Results({
@Result(property = "userSex", column="user_sex",javaType = UserSexEnum.class),
@Result(property = "nickName", column = "nick_name",javaType = String.class)
})
List<User> getAll();
@SelectProvider(type = UserSql.class,method = "getList")
List<User> getList(UserParam userParam);
@SelectProvider(type = UserSql.class,method = "getCount")
int getCount(UserParam userParam);
/*注意$与#的区别*/
@Select("SELECT * FROM users WHERE user_sex = #{user_sex}")
List<User> getListByUserSex(@Param("user_sex") String userSex);
@Select("SELECT * FROM users WHERE username=#{username} AND user_sex = #{user_sex}")
List<User> getListByNameAndSex(Map<String, Object> map);
@Select("SELECT * FROM users WHERE ID = #{id}")
@Results({
@Result(property = "userSex", column="user_sex",javaType = UserSexEnum.class),
@Result(property = "nickName", column = "nick_name",javaType = String.class)
})
User getOne(Long id);
@Insert("INSERT INTO users(userName,passWord,user_sex) VALUES(#{userName},#{passWord},#{userSex})")
void insert(User user);
@UpdateProvider(type = UserSql.class,method = "update")
int update(User user);
@Update({"<script> ",
"UPDATE users ",
"<set>" ,
" <if test='userName != null'>userName=#{userName},</if>" ,
" <if test='nickName != null'>nick_name=#{nickName},</if>" ,
" </set> ",
"where id=#{id} " ,
"</script>"})
int updateUser(User user);
@Delete("DELETE FROM users WHERE id = #{id}")
int delete(Long id);
}
动态sql类:
public class UserSql {
private static final Logger log = LoggerFactory.getLogger(UserSql.class);
public String getList(UserParam userParam) {
StringBuffer sql = new StringBuffer("select id, userName, passWord, user_sex as userSex, nick_name as nickName");
sql.append(" from users where 1=1 ");
if (userParam != null) {
if (!StringUtils.isEmpty(userParam.getUserName())) {
sql.append(" and userName = #{userName}");
}
if (!StringUtils.isEmpty(userParam.getUserSex())) {
sql.append(" and user_sex = #{userSex}");
}
}
sql.append(" order by id desc");
sql.append(" limit " + userParam.getBeginLine() + "," + userParam.getPageSize());
log.info("getList sql is :" +sql.toString());
return sql.toString();
}
public String getCount(UserParam userParam) {
String sql= new SQL(){{
SELECT("count(1)");
FROM("users");
if (!StringUtils.isEmpty(userParam.getUserName())) {
WHERE("userName = #{userName}");
}
if (!StringUtils.isEmpty(userParam.getUserSex())) {
WHERE("user_sex = #{userSex}");
}
//从这个toString可以看出，其内部使用高效的StringBuilder实现SQL拼接
}}.toString();
log.info("getCount sql is :" +sql);
return sql;
}
public String update(User user) {
String sql = new SQL() {{
UPDATE("users");
if(!StringUtils.isEmpty(user.getNickName())){
SET("nick_name=#{nickName}");
}
if(!StringUtils.isEmpty(user.getUserName())) {
SET("userName=#{userName}");
}
if(!StringUtils.isEmpty(user.getPassWord())) {
SET("passWord=#{passWord}");
}
if(!StringUtils.isEmpty(user.getUserSex())) {
SET("user_sex=#{userSex}");
}
WHERE("id=#{id}");
}}.toString();
return sql;
}
}
最后测试类(多添加了两个方法,一个传递String,另一个是传递map):
@SpringBootTest
@RunWith(SpringRunner.class)
public class MybatisAnnoTest {
@Resource
private UserMapper userMapper;
@Test
public void testInsert() {
userMapper.insert(new User("dd", "a123456", UserSexEnum.MAN));
// The total number of data in the database
Assert.assertEquals(3, userMapper.getAll().size());
}
@Test
public void testUpdate() {
long id=4l;
User user = userMapper.getOne(id);
if(user!=null){
System.out.println(user.toString());
user.setNickName("wzlove");
userMapper.update(user);
Assert.assertTrue(("wzlove".equals(userMapper.getOne(id).getNickName())));
}else {
System.out.println("not find user id="+id);
}
}
@Test
public void testDelete() {
int count=userMapper.delete(2l);
if(count>0){
System.out.println("delete is sucess");
}else {
System.out.println("delete if failed");
}
}
@Test
public void findAll(){
UserParam userParam = new UserParam();
userParam.setCurrentPage(0);
userParam.setPageSize(1);
List<User> list = userMapper.getList(userParam);
System.out.println(list.get(0));
Assert.assertEquals(1,list.size());
}
@Test
public void testGetListByUserSex(){
String userSex = "MAN";
List<User> userLists = userMapper.getListByUserSex(userSex);
Assert.assertEquals(2,userLists.size());
}
@Test
public void testGetListByNameAndSex(){
Map<String,Object> condition = new HashMap<>(2);
condition.put("username","dd");
condition.put("user_sex","MAN");
List<User> userList = userMapper.getListByNameAndSex(condition);
Assert.assertEquals(1,userList.size());
}
}
SpringBoot整合Mybatis到这里就总结完了,这里都没有考虑到多数据源的问题,所以有需求的看pdf文档就好.
SpringBoot整合Mybatis也就是两种方式,一种是xml的方式,另一种是注解的方式. 注解方式中需要注意的是动态sql的写法. xml中注意的是mapper的路径等的注意. 了解myabtis的一个执行流程. SqlSessionFactoryBuilder -> SqlSessionFactory -> SqlSession -> Executor -> sql对象 -> 执行返回结果,完毕