An example of using mysql in mybatis+springboot

Directory dependency introduction
Configuration introduction
Case realization
Case source code

In software development, the introduction of database is essential, among which mysql is the most widely used, while in springboot, there are many ways to integrate mysql (such as jpa). Here, we will show the use of mysql in springboot through mybatis framework.

Dependency introduction

First, add the dependencies related to mybatis and mysql when using the initialization project, as follows:

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.0.1</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.10</version>
        </dependency>

    </dependencies>

Configuration introduction

On the configuration side, configure the data source using the auto-configuration feature of springboot, as follows:

spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    url: jdbc:mysql://127.0.0.1:3306/sbac_master?autoReconnect=true&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true
    username: root
    password: 1234
    driver-class-name: com.mysql.cj.jdbc.Driver

The data source is configured, and the rest is to introduce mybatis. The introduction of mybatis can also be realized by using automatic configuration (MybatisProperties). Here, we choose not to use all automatic configuration attributes, but to introduce the configuration file of mybatis to inject attributes.

mybatis:
  config-location: classpath:mybatis-config.xml
  mapper-locations: classpath:com/lazycece/sbac/mysql/data/dao/*/mapper/*.xml

Here is a simple configuration of mybatis. For more configuration attributes, please refer to XML configuration of mybatis.

<?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>
        <!--  Use jdbc Adj. getGeneratedKEYS  Get the database self-increasing primary key  -->
        <setting name="useGeneratedKeys" value="true"/>
        <!--  Turn on hump naming conversion  -->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
        <!-- Log output sql Statement -->
        <setting name="logImpl" value="STDOUT_LOGGING"/>
    </settings>
    <typeAliases>
        <package name="com.lazycece.sbac.mysql.data.domain"/>
    </typeAliases>
    <typeHandlers>
        <typeHandler handler="org.apache.ibatis.type.EnumOrdinalTypeHandler"
                     javaType="com.lazycece.sbac.mysql.data.domain.Status"/>
    </typeHandlers>
</configuration>

Case realization

Here is a simple case (insertion and query of user information) to show the implementation of mapper layer, and entities are not shown here. The mapper interface is as follows:

@Mapper
public interface UserDao {

    void insert(User user);

    User findByUsername(@Param("username") String username);
}

At the same time, we need to add annotation @ MapperScan to the main function to scan our mapper:

@SpringBootApplication
@MapperScan(basePackages = {"com.lazycece.sbac.mysql.data.dao"})
public class SpringbootAcMysqlSimpleApplication {

    public static void main(String[] args) {
        SpringApplication.run(SpringbootAcMysqlSimpleApplication.class, args);
    }

}

sql implementation of mapper, which is implemented by xml, or annotation (corresponding @ Select and @ Insert):

<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.lazycece.sbac.mysql.data.dao.master.UserDao">

    <sql id="allColumn">
        id,create_time,update_time,username,password,telephone,status,editor
    </sql>

    <insert id="insert" parameterType="User" useGeneratedKeys="true" keyProperty="id">
        INSERT  INTO
        user (create_time,update_time,username,password,telephone,status,editor)
        VALUE (#{createTime},#{updateTime},#{username},#{password},#{telephone},#{status},#{editor});
    </insert>

    <select id="findByUsername" resultType="User">
        SELECT
        <include refid="allColumn"/>
        FROM user
        WHERE username = #{username};
    </select>

</mapper>

Careful people can find that the introduction of mapper in the previous configuration introduction is written classpath: com/lazycece/sbac/mysql/data/dao//mapper/. xml package name path, which will not be successful by default, because the files under the package path will only compile java files by default, so it is necessary to add configuration to pom files so that they can be included in the compiled path when the project is compiled.

    <build>
        <resources>
            <resource>
                <directory>src/main/java</directory>
                <includes>
                    <include>com/lazycece/sbac/mysql/data/dao/*/mapper/*.xml</include>
                </includes>
            </resource>
        </resources>
    </build>

Case source code

Case source address: https://github.com/lazycece/springboot-actual-combat/tree/master/springboot-ac-mysql/springboot-ac-mysql-simple