SpringBoot整合Mybatis基础版

SpringBoot整合Mybatis基础版中涉及到mybatis的基本操作-增删改查和批量添加，批量删除
mybatis中文
主要整合参考链接
可参考整合链接
批量删除的三种方式

本文和其他文章有些许不同
第一点：实体类和数据库表的字段名称不一致
第二点：对于Mapper接口层增加了参数
第三点：在xml文件中对sql进行了抽取
提示：本文引入了lombok可以自动生成实体的set/get方法
也引入了druid连接池

项目结构图：

1.数据库设计

2.pom.xml

<properties>
        <java.version>1.8</java.version>
        <mysql.vesrion>8.0.23</mysql.vesrion>
        <mybatis.version>2.2.0</mybatis.version>
        <druid.version>1.2.6</druid.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>${mysql.vesrion}</version>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>${druid.version}</version>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>${mybatis.version}</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.24</version>
            <scope>compile</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <excludes>
                        <exclude>
                            <groupId>org.projectlombok</groupId>
                            <artifactId>lombok</artifactId>
                        </exclude>
                    </excludes>
                </configuration>
            </plugin>
        </plugins>
    </build>

application.yaml文件替换自己的数据库名称，用户名和密码

Spring:
  datasource:
    druid:
      url: jdbc:mysql://127.0.0.1:3306/mybatis_demo?serverTimezone=GMT%2B8
      driver-class-name: com.mysql.cj.jdbc.Driver
      username: root
      password: 1234

mybatis:
  mapper-locations:  classpath:mapper/*.xml
  type-aliases-package: com.practice.entity
  configuration:
    map-underscore-to-camel-case: true

实体类

@Data
@AllArgsConstructor
@NoArgsConstructor
public class User {

    Integer user_id;
    String user_name;
    String user_sex;
    public User(String name, String sex) {
        this.user_name = name;
        this.user_sex = sex;
    }

}

Mapper接口：

@Mapper
@Repository
public interface UserMapper {
    List<User> findAllUser();
    User findUserById(Integer id);
    int insertUser(User user);
    int updateUser(User user);
    int deleteUser(Integer id);

    //批量操作
    int addBatchUser(@Param("users") List<User> userList);
    int delBatchUser(@Param("ids") List<Integer> idList);
}

xml文件：

<?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.practice.mapper.UserMapper">

    <resultMap id="userMap" type="com.practice.entity.User">
        <id column="id" property="user_id" jdbcType="INTEGER"/>
        <result column="name" property="user_name" jdbcType="VARCHAR"/>
        <result column="sex" property="user_sex" jdbcType="VARCHAR"/>
    </resultMap>

    <sql id="userInfo">
        id,name,sex
    </sql>
	<!--查询用户列表-->
    <select id="findAllUser" resultMap="userMap">
        SELECT
        <include refid="userInfo"/>
        FROM user
    </select>
	<!--通过ID查询User-->
    <select id="findUserById"  parameterType="int" resultMap="userMap">
        SELECT * FROM user WHERE id=#{user_id,jdbcType=INTEGER}
    </select>
	<!--增加用户，用户ID自动增加-->
    <insert id="insertUser" parameterType="User" useGeneratedKeys="true" keyProperty="user_id">
        INSERT INTO user(name,sex)
        VALUES (
        #{user_name,jdbcType=VARCHAR},#{user_sex,jdbcType=VARCHAR}
        )
    </insert>
	<!--修改用户-->
    <update id="updateUser" parameterType="User">
        UPDATE user SET name=#{user_name,jdbcType=VARCHAR},sex=#{user_sex,jdbcType=VARCHAR} WHERE id=#{user_id,jdbcType=INTEGER}
    </update>
	<!--删除用户-->
    <delete id="deleteUser">
        DELETE  FROM  user WHERE id=#{user_id,jdbcType=INTEGER}
    </delete>
   <!--批量增加用户-->
    <insert id="addBatchUser" parameterType="User" useGeneratedKeys="true" keyProperty="user_id" >
        INSERT INTO user(name,sex) VALUES
        <foreach collection="users" item="item" separator=",">
            (#{item.user_name,jdbcType=INTEGER}, #{item.user_sex,jdbcType=INTEGER})
        </foreach>
    </insert>
	<!--批量删除用户-->
    <delete id="delBatchUser" parameterType="int">
        DELETE  FROM  user WHERE id in
        <foreach collection="ids"  item="id" open="(" close=")"  separator=",">
            #{id,jdbcType=INTEGER}
        </foreach>
    </delete>
</mapper>

3.代码测试

@SpringBootTest
public class testUser {

    @Autowired
    private UserMapper userMapper;

    @Test
    void testFindAllUser(){
        List<User> userList=userMapper.findAllUser();
        System.out.println(userList);
    }

    @Test
    void testFindUserById(){
        User user = userMapper.findUserById(1);
        System.out.println(user.getUser_id());

    }


    @Test
    void testInsertUserByAutoId(){
        User user1=new User();
        user1.setUser_name("testNewAuto");
        user1.setUser_sex("男");

        int i = userMapper.insertUser(user1);
        System.out.println(i);
    }

    @Test
    void testUpdateUser(){
        User user=new User();
        user.setUser_id(5);
        user.setUser_name("test05");
        user.setUser_sex("男");
        int i = userMapper.updateUser(user);
        System.out.println(i);

    }

    @Test
    void testDeleteUser(){
        userMapper.deleteUser(1);

    }


    @Test
    void testAddBatchUser(){
        List<User> userList=new ArrayList<User>();

        /*
        User user1 = new User("testBatchAuto", "男");
        User user2 = new User("testAuto", "男");
        User user3 = new User("testAuto", "男");
        User user4 = new User("testAuto", "男");
        User user5 = new User("testAuto", "男");
        userList.add(user1);
        userList.add(user2);
        userList.add(user3);
        userList.add(user4);
        userList.add(user5);
  		*/
        for(int j=0;j<3;j++){
            User user = new User();
            user.setUser_name("test"+j);
            user.setUser_sex("男");
            userList.add(user);
        }
        int i = userMapper.addBatchUser(userList);
        System.out.println(i);

    }
    @Test
    void testDeleteBatchUser(){
        List<Integer> idList=new ArrayList<>();
        idList.add(15);
        idList.add(16);
       // idList.add(6);
        int i = userMapper.delBatchUser(idList);
        System.out.println(i);

    }
}

所有测试保熟，就不贴图了，举例一个查询所有用户



这期是SpringBoot整合Mybatis基础版，目的就是可以快速应用，所以就不涉及很多细节的解释。后续还会出进阶版加入Controller,Service,Swagger,Log等等