spring boot整合mybatis 实现xml方式增删改查

spring boot整合mybatis 实现xml方式增删改查

spring boot 2.x
这里以user表为例子

sql结构

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

pom文件

  		<dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
<!--mybatis相关-->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.3</version>
        </dependency>
		<dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.junit.jupiter</groupId>
            <artifactId>junit-jupiter-api</artifactId>
            <version>5.3.2</version>
            <scope>compile</scope>
        </dependency>
		<dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.5</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>


<build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
        <resources>
            <resource>
                <directory>src/main/resources</directory>
                <includes>
                    <include>**/*.properties</include>
                    <include>**/*.xml</include>
                    <include>**/*.yml</include>
                </includes>
                <filtering>true</filtering>
            </resource>
        </resources>
    </build>

application文件

server:
  port: 9999
spring:
  application:
    name: springboot-mybatis
  # database 部分注释
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    url: jdbc:mysql://localhost:3306/xxxx?useUnicode=true&characterEncoding=utf-8&useSSL=false&autoReconect=true&serverTimezone=GMT%2b8
    username: xxxx
    password: xxxx
    driver-class-name: com.mysql.jdbc.Driver
    filters: stat
    maxActive: 50
    initialSize: 0
    maxWait: 60000
    minIdle: 1
    timeBetweenEvictionRunsMillis: 60000
    minEvictableIdleTimeMillis: 300000
    validationQuery: select 1 from dual
    testWhileIdle: true
    testOnBorrow: false
    testOnReturn: false
    poolPreparedStatements: true
    maxOpenPreparedStatements: 20
    removeAbandoned: true
    removeAbandonedTimeout: 180
mybatis:
   # 此处路径是你存放mapper.xml文件得地方
  mapper-locations: classpath:mybatis/mapper/*.xml

启动类加上扫描mapper注解

// 你的mapper接口路径
@MapperScan("cn.theone.tmp.mybatis.mapper")

正常目录结构应该是这样的，pageModel我省略了，直接用的实体做的，这个是我完整的路径，怕错误的朋友可以直接按照我的目录层级来做，之后再修改

实体类

/**
这里使用了lombok插件，省了set方法了，需要先安装lombok插件，网上很多教程
*/
@Data
public class User implements Serializable {
    private Integer id;

    private String name;

    private static final long serialVersionUID = 1L;

mapper层，由于我的代码采用mybatis-generator插件直接生成的，方法都是生成好的，getAll是自己扩展的

@Repository
public interface UserMapper {

    int deleteByPrimaryKey(Integer id);

    int insert(User record);

    int insertSelective(User record);

    User selectByPrimaryKey(Integer id);

    int updateByPrimaryKeySelective(User record);

    int updateByPrimaryKey(User record);

    List<User> getAll();
}

userService接口层和实现类层，按照分层思想来写

//接口层都没写注释，方法名见名知意
public interface UserServiceI {
	
    void add(User user);

    List<User> findAll();

    User findById(Integer id);

    void update(User user);

    void delete(Integer id);
}

//实现类层
@Service
@Transactional(rollbackFor = Exception.class)
public class UserServiceImpl implements UserServiceI {

    @Autowired
    private UserMapper userMapper;


    @Override
    public void add(User user) {
        userMapper.insert(user);
    }

    @Override
    public List<User> findAll() {
        return userMapper.getAll();
    }

    @Override
    public User findById(Integer id) {
        return userMapper.selectByPrimaryKey(id);
    }

    @Override
    public void update(User user) {
        userMapper.updateByPrimaryKey(user);
    }

    @Override
    public void delete(Integer id) {
        userMapper.deleteByPrimaryKey(id);
    }

UserMapper.xml文件，由于这里是生成的，所以mapper路径和实体返回的路径都是我的路径，自行修改

<?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="cn.theone.tmp.mybatis.mapper.UserMapper">
    <resultMap id="BaseResultMap" type="cn.theone.tmp.mybatis.model.User">
        <id column="id" jdbcType="INTEGER" property="id"/>
        <result column="name" jdbcType="VARCHAR" property="name"/>
    </resultMap>
    <sql id="Base_Column_List">
    id, `name`
  </sql>
    <select id="selectByPrimaryKey" resultType="cn.theone.tmp.mybatis.model.User">
    select * from user where id = #{id}
  </select>

    <select id="getAll"  resultType="cn.theone.tmp.mybatis.model.User">
        select  * from user
    </select>
    <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer">
    delete from user
    where id = #{id,jdbcType=INTEGER}
  </delete>
    <insert id="insert" keyColumn="id" keyProperty="id" parameterType="cn.theone.tmp.mybatis.model.User"
            useGeneratedKeys="true">
    insert into user (`name`)
    values (#{name,jdbcType=VARCHAR})
  </insert>
    <insert id="insertSelective" keyColumn="id" keyProperty="id" parameterType="cn.theone.tmp.mybatis.model.User"
            useGeneratedKeys="true">
        insert into user
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="name != null">
                `name`,
            </if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="name != null">
                #{name,jdbcType=VARCHAR},
            </if>
        </trim>
    </insert>
    <update id="updateByPrimaryKeySelective" parameterType="cn.theone.tmp.mybatis.model.User">
        update user
        <set>
            <if test="name != null">
                `name` = #{name,jdbcType=VARCHAR},
            </if>
        </set>
        where id = #{id,jdbcType=INTEGER}
    </update>
    <update id="updateByPrimaryKey" parameterType="cn.theone.tmp.mybatis.model.User">
    update user
    set `name` = #{name,jdbcType=VARCHAR}
    where id = #{id,jdbcType=INTEGER}
  </update>
</mapper>

编写测试类

@RunWith(SpringJUnit4ClassRunner.class)
@SpringBootTest(classes = TmpApplication.class)
public class mybatisTest {

    @Autowired
    private UserServiceImpl userService;

    @Test
    public void findById() {
        User user = userService.findById(1);
        System.out.println(user.getName());
    }

    @Test
    public void add() {
        User user = new User();
        user.setName("李四");
        userService.add(user);
    }

    @Test
    public void update(){
        User user = new User();
        user.setId(2);
        user.setName("李四111");
        userService.update(user);
    }

    @Test
    public void delete(){
        userService.delete(2);
    }

    @Test
    public void findAll(){
        List<User> userList = userService.findAll();
        for (User user : userList) {
            System.out.println(user.getName());
        }
    }

至此我们springboot整合mybatis就完成了，需要扩展查询和多表查询的情况直接写mapper接口映射xml文件中写sql即可，有什么问题欢迎指出！