Springboot实现mybatis访问数据库

SpringBoot+Mybatis

项目布局

pom文件导入依赖

<?xml version="1.0" encoding="UTF-8"?>
<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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.4.0</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>lab-12</groupId>
    <artifactId>demo</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>demo</name>
    <description>Demo project for Spring Boot</description>

    <properties>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <!--    数据库链接依赖    -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
<!--        -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.16.14</version>
            <scope>provided</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.1</version>
        </dependency>



    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

application.yml文件的配置

该文件所在的位置为：resources/application.yml

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai
    driver-class-name: com.mysql.cj.jdbc.Driver
    username:
    password:
mybatis:
  mapper-locations: classpath:mapper/*.xml #mybatis配置文件位置
  config-location: classpath:mybatis-config.xml  #配置Mapper XML地址
  type-aliases-package:  lab12.demo.dataobject
#type-aliases-package: lab12.demo.mapper #配置数据库实体包路径

创建Mybatis配置文件

文件所属位置：resources/mybatis-config.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>

    <settings>
        <!-- 使用驼峰命名法转换字段。 -->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>

    <typeAliases>
        <typeAlias alias="Integer" type="java.lang.Integer"/>
        <typeAlias alias="Long" type="java.lang.Long"/>
        <typeAlias alias="HashMap" type="java.util.HashMap"/>
        <typeAlias alias="LinkedHashMap" type="java.util.LinkedHashMap"/>
        <typeAlias alias="ArrayList" type="java.util.ArrayList"/>
        <typeAlias alias="LinkedList" type="java.util.LinkedList"/>
    </typeAliases>

</configuration>

创建DO类

该类的作用是对应着数据表中的数据，因为开启了驼峰命名的方式，因此注意属性和数据表字段的关系

因为在导入依赖的时候，导入了Lombok依赖，以此不需要写getter和setter方法，只需要使用@Data注解即可。

该类代码：

package lab12.demo.dataobject;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import javax.security.auth.callback.PasswordCallback;
import java.util.Date;

/**
 * @Package: lab12.demo.dataobject
 * @ClassName: UserDO
 * @Author: lx
 * @CreateTime: 2020/12/2 19:37
 * @Description:
 */
@Data
@NoArgsConstructor
@AllArgsConstructor
public class UserDO {
    /**
     * 用户编号
     */
    private Integer id;
    /**
     * 账号
     */
    private String username;
    /**
     * 用户密码
     */
    private  String password;
    /**
     * 用户创建时间
     */
    private Date createTime;


}

创建UserMapper接口

package lab12.demo.mapper;

import lab12.demo.dataobject.UserDO;
import org.apache.ibatis.annotations.Param;
import org.springframework.stereotype.Repository;

import java.util.Collection;
import java.util.List;

/**
 * @Package: lab12.demo.mapper
 * @ClassName: UserMapper
 * @Author: lx
 * @CreateTime: 2020/12/2 19:47
 * @Description:
 */
@Repository
public interface UserMapper {

    int insert(UserDO user);

    int updateById(UserDO user);

    int deleteById(@Param("id") Integer id); // 生产请使用标记删除，除非有点想不开，嘿嘿。

    UserDO selectById(@Param("id") Integer id);//禁止使用map作为查询参数

    UserDO selectByUsername(@Param("username") String username);

    List<UserDO> selectByIds(@Param("ids") Collection<Integer> ids);
}

创建UserMapper.xml文件

该文件实现了之前的UserMapper.java文件，用来操作数据库，是数据持久层

<?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="lab12.demo.mapper.UserMapper">
    <sql id="FIELDS">
        id,username,password,create_time
    </sql>

    <insert id="insert" parameterType="UserDO" useGeneratedKeys="true" keyProperty="id">
        INSERT  INTO users(
        username,password,create_time
        )VALUES (
        #{username},#{password},#{createTime}
        )
    </insert>
    <update id="updateById" parameterType="UserDO">
        UPDATE users
        <set>
            <if test="username!=null">
                ,username=#{username}
            </if>
            <if test="password!=null">
                ,password =#{password}
            </if>
        </set>
        where id=#{id}
    </update>
    <delete id="deleteById" parameterType="Integer">
        DELETE FROM users
        WHENEVER id=#{id}
    </delete>
    <select id="selectByUserName" parameterType="String" resultType="UserDO">
        SELECT
        <include refid="FIELDS"/>
        FROM users
        WHERE username =#{username}
        limit 1
    </select>

    <select id="selectById" resultType="UserDO">
        SELECT
        <include refid="FIELDS"/>
        FROM users
        WHERE id IN
        <foreach item="id" collection="ids" separator="," open="(" close=")" index="">
            #{id}
        </foreach>
    </select>
</mapper>

创建测试类用来测试mapper映射

import lab12.demo.DemoApplication;
import lab12.demo.dataobject.UserDO;
import lab12.demo.mapper.UserMapper;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import java.util.Arrays;
import java.util.Date;
import java.util.List;
import java.util.UUID;

/**
 * @Package: PACKAGE_NAME
 * @ClassName: UserMapperTest
 * @Author: lx
 * @CreateTime: 2020/12/2 20:10
 * @Description:
 */

@SpringBootTest(classes = DemoApplication.class)
public class UserMapperTest {

    @Autowired
    private UserMapper userMapper;

    @Test
    public void testInsert() {
        UserDO user = new UserDO();
                user.setUsername(UUID.randomUUID().toString());
                user.setPassword("nicai");
                user.setCreateTime(new Date());
        userMapper.insert(user);
    }

    @Test
    public void testUpdateById() {
        UserDO updateUser = new UserDO();
        updateUser.setId(1);

               updateUser.setPassword("wobucai");
        userMapper.updateById(updateUser);
    }

    @Test
    public void testDeleteById() {
        userMapper.deleteById(2);
    }

    @Test
    public void testSelectById() {
        userMapper.selectById(1);
    }

    @Test
    public void testSelectByUsername() {
        userMapper.selectByUsername("yunai");
    }

    @Test
    public void testSelectByIds() {
        List<UserDO> users = userMapper.selectByIds(Arrays.asList(1, 3));
        System.out.println("users：" + users.size());
    }

}