SpringBoot--整合MyBatis（注解方式）-蒲公英云

整合MyBatis（注解方式）

Users表结构

CREATE TABLE `users` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `username` varchar(32) DEFAULT NULL COMMENT '用户名',
  `password` varchar(32) DEFAULT NULL COMMENT '密码',
  `sex` varchar(32) DEFAULT NULL,
  `nick_name` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`) USING HASH
) ENGINE=InnoDB AUTO_INCREMENT=39 DEFAULT CHARSET=utf8;

添加pom文件

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>com.lynch.springboot</groupId>
    <artifactId>spring-boot-mybatis-annotation</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>1.5.10.RELEASE</version>
    </parent>
    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <java.version>1.8</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.2</version>
        </dependency>
        <!-- mybatis分页插件 -->
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper-spring-boot-starter</artifactId>
            <version>1.1.1</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <optional>true</optional>
        </dependency>
    </dependencies>
    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>
</project>

application.properties 添加相关配置

#mybatis.type-aliases-package=com.lynch.entity
spring.datasource.driverClassName = com.mysql.jdbc.Driver
spring.datasource.url = jdbc:mysql://192.168.1.149:3306/aa?useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.datasource.username = root
spring.datasource.password = attack

springboot会自动加载spring.datasource.*相关配置，数据源就会自动注入到sqlSessionFactory中，sqlSessionFactory会自动注入到Mapper中，对于开发人员不用管，直接拿来使用即可。

在启动类中添加对mapper包扫描@MapperScan

package com.lynch;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
@MapperScan("com.lynch.mapper")
public class Application { 
    public static void main(String[] args) { 
        SpringApplication.run(Application.class, args);
    }
}

或者直接在Mapper类上面添加注解@Mapper，建议使用上面那种，不然每个mapper加个注解也挺麻烦的。

Mapper

package com.lynch.mapper;
import java.util.List;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.InsertProvider;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.SelectProvider;
import org.apache.ibatis.annotations.Update;
import org.apache.ibatis.annotations.UpdateProvider;
import com.lynch.entity.UserEntity;
import com.lynch.enums.SexEnum;
public interface UserMapper { 
    @Select("SELECT * FROM users")
    @Results({  @Result(property = "sex", column = "sex", javaType = SexEnum.class),
               @Result(property = "nickName", column = "nick_name") })
    List<UserEntity> getAll();
    @Select("SELECT * FROM users WHERE id = #{id}")
    @Results({  @Result(property = "sex", column = "sex", javaType = SexEnum.class),
            @Result(property = "nickName", column = "nick_name") })
    UserEntity getOne(Long id);
    @Insert("insert into users(username,password,sex) values(#{username}, #{password}, #{sex})")
    int insert(UserEntity user);
    @Update("UPDATE users SET userName=#{username},nick_name=#{nickName} WHERE id =#{id}")
    void update(UserEntity user);
    @Delete("DELETE FROM users WHERE id =#{id}")
    void delete(Long id);
    @InsertProvider(type=UserProvider.class, method = "batchInsert")
    int batchInsert(@Param("userList")List<UserEntity> userList);
    @SelectProvider(type = UserProvider.class, method = "queryUser")
    @Results({  
        @Result(property = "sex", column = "sex", javaType = SexEnum.class),
        @Result(property = "nickName", column = "nick_name") 
    })
    public List<UserEntity> queryUser(UserEntity user);
    @UpdateProvider(type = UserProvider.class, method = "updateUser")
    public int updateUser(@Param("U")UserEntity user);
}
package com.lynch.mapper;
import java.util.List;
import java.util.Map;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.jdbc.SQL;
import com.github.pagehelper.util.StringUtil;
import com.lynch.entity.UserEntity;
/** * 利用@Provider实现动态SQL * * @author Lynch * */
public class UserProvider { 
    public String queryUser(UserEntity user) { 
        StringBuffer sql = new StringBuffer("select * from users where 1=1 ");
        if(StringUtil.isNotEmpty(user.getUsername())) { 
            sql.append(String.format("and username like '%s'", "%"+user.getUsername()+"%"));
        }
        return sql.toString();
    }
    public String batchInsert(Map map) { 
        List<UserEntity> userList = (List<UserEntity>)map.get("userList");
        StringBuffer sql = new StringBuffer("insert into users (username,password) values ");
        for(UserEntity user : userList) { 
            sql.append(String.format("('%s', '%s'),", user.getUsername(), user.getPassword()));
        }
        sql = sql.deleteCharAt(sql.length() -1);
        System.out.println(sql.toString());
        return sql.toString();
    }
    public String updateUser(@Param("U")UserEntity user) { 
            SQL sql = new SQL(){ { 
            UPDATE("users");
            if (StringUtil.isNotEmpty(user.getNickName())){ 
                SET("nick_name = #{U.nickName}");
            }
            WHERE("id = #{U.id}");
        }};
        return sql.toString();
    }
}

注意，使用#符号和$符号的不同：

// This example creates a prepared statement, something like select * from teacher where name = ?;
@Select("Select * from teacher where name = #{name}")
Teacher selectTeachForGivenName(@Param("name") String name);
// This example creates n inlined statement, something like select * from teacher where name = 'someName';
@Select("Select * from teacher where name = '${name}'")
Teacher selectTeachForGivenName(@Param("name") String name);

单元测试

package com.lynch.mapper;
import java.util.ArrayList;
import java.util.List;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import com.lynch.entity.UserEntity;
import com.lynch.enums.SexEnum;
@RunWith(SpringRunner.class)
@SpringBootTest
public class UserMapperTest { 
    @Autowired
    private UserMapper userMapper;
    @Test
    public void insert() throws Exception { 
        int result = 0;
        try { 
            result = userMapper.insert(new UserEntity("lisi", "123456", SexEnum.MAN));
        } catch (Exception e) { 
            System.out.println(e.getMessage().indexOf("ConstraintViolationException"));
        }
        System.out.println("result=" + result);
    }
    @Test
    public void batchInsert() throws Exception { 
        List<UserEntity> userList = new ArrayList<UserEntity>();
        userList.add(new UserEntity("a","a",SexEnum.MAN));
        userList.add(new UserEntity("c","b",SexEnum.MAN));
        userList.add(new UserEntity("b","b",SexEnum.WOMAN));
        System.out.println("result=" + userMapper.batchInsert(userList)); 
    }
    @Test
    public void getAll() throws Exception { 
        List<UserEntity> users = userMapper.getAll();
        System.out.println(users.toString());
    }
    @Test
    public void testUpdate() throws Exception { 
        UserEntity user = userMapper.getOne(45L);
        System.out.println(user.toString());
        user.setNickName("neo");
        user.setSex(SexEnum.WOMAN);
        userMapper.update(user);
    }
    @Test
    public void queryUser() throws Exception { 
        UserEntity user = new UserEntity();
        user.setUsername("l");
        System.out.println(userMapper.queryUser(user)); 
    }
    @Test
    public void updateUser() throws Exception { 
        UserEntity user = new UserEntity();
        user.setId(45L);
        user.setNickName("aaa");
        System.out.println(userMapper.updateUser(user)); 
    }
}