MyBatis项目快速搭建及MySQL一个Statement支持多条命令参数

MyBatis项目快速搭建及MySQL一个Statement支持多条命令参数

一、简述

本文以笔记的形式，记录一个基本Mybatis项目的使用，方便后期项目使用到相关配置时直接复制使用。

二、项目结构

pom.xml中的依赖

        <!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.0</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.15</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>

三、文件信息

1、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>
    <!--配置环境  -->
    <environments default="development">
        <environment id="development">
            <!--事务管理  -->
            <transactionManager type="JDBC"/>
            <!--数据源 通过Properties加载配置 -->
            <dataSource type="POOLED">
                <!--驱动driver  -->
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <!--连接URL  -->
                <property name="url" value="jdbc:mysql://127.0.0.1:3306/testdb?characterEncoding=utf8&amp;useUnicode=true&amp;useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;allowMultiQueries=true"/>
                <!--用户名  -->
                <property name="username" value="root"/>
                <!--密码  -->
                <property name="password" value="123456"/>
            </dataSource>
        </environment>
    </environments>
    <!--建立映射  -->
    <mappers>
        <mapper resource="mappers/InitMapper.xml"/>
        <mapper resource="mappers/ConfigureMapper.xml"/>
    </mappers>
</configuration>

注意，（1）、连接URL位于xml中时，原来的”&”需要使用”&”来转义。（2）、如果想最终执行时一个Mapper文件的每一个update/insert/select等Statement中支持多个sql语句的话，需要在连接URL中加入’allowMultiQueries=true’，切记！

2、InitMapper.xml和InitMapper.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="com.test.repository.mappers.InitMapper">
    <update id="ensureStationGroupTab">
        CREATE TABLE IF NOT EXISTS `station_group_tab`(
         `id` BIGINT NOT NULL AUTO_INCREMENT,
         `name` VARCHAR(255) COMMENT '组名称',
         `notes` VARCHAR(255) COMMENT '备注',
         PRIMARY KEY (`Id`),
         INDEX `idx_station_group_groupName`(`groupName`)
         )ENGINE INNODB;
    </update>
    <update id="ensureConfigureTab">
      CREATE TABLE IF NOT EXISTS `configure_tab`(
        `id` BIGINT NOT NULL AUTO_INCREMENT,
        `stationGroupId` BIGINT COMMENT '站点组ID',
        `type` INT COMMENT '模块',
        `name` VARCHAR(255) COMMENT '名称',
        PRIMARY KEY (`Id`),
        INDEX `idx_configure_stationGroupId`(`stationGroupId`),
        CONSTRAINT `fk_configure_stationGroupId` FOREIGN KEY (`stationGroupId`) REFERENCES `station_group_tab`(`id`)
        )ENGINE INNODB;
    </update>
    <update id="ensureStationGroupRelationTab">
        CREATE TABLE IF NOT EXISTS `station_group_relation_tab`(
          `id` BIGINT NOT NULL AUTO_INCREMENT,
          `stationGroupId` BIGINT COMMENT '站点组ID' ,
          `otherInfo` VARCHAR(255) COMMENT '其它信息',
          PRIMARY KEY (`Id`),
          INDEX `idx_station_group_relation_stationGroupId`(`stationGroupId`),
          CONSTRAINT `fk_station_group_relation_stationGroupId` FOREIGN KEY (`stationGroupId`) REFERENCES `station_group_tab`(`id`)
        )ENGINE INNODB;
    </update>

    <update id="initDataTable">
      --  INSERT INTO  `station_group_tab` (name,notes) VALUES('ABC','ABC');
      --  INSERT INTO  `station_group_tab` (name,notes) VALUES('DEF','DEF');
    </update>
</mapper>


package com.test.repository.mappers;

public interface InitMapper {
    int ensureStationGroupTab();
    int ensureConfigureTab();
    int ensureStationGroupRelationTab();
    int initDataTable();
}

3、SqlSessionFactoryUtil工具类

package com.test.repository.utils;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.IOException;
import java.io.Reader;

public class SqlSessionFactoryUtil {
    private static SqlSessionFactory sqlSessionFactory;

    static {
        try {
            // 1.加载配置文件
            Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
            // 2.创建SqlSessionFactory
            SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
            sqlSessionFactory = sqlSessionFactoryBuilder.build(reader);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }

    public static SqlSessionFactory getSqlSessionFactory() {
        return sqlSessionFactory;
    }

    public static SqlSession getSqlSession(boolean autoCommit) {
        return sqlSessionFactory.openSession(autoCommit);
    }

}

4、InitMapperService服务类

package com.test.repository.services;

import org.apache.ibatis.session.SqlSession;
import org.slf4j.Logger;
import com.test.repository.mappers.InitMapper;
import com.test.repository.utils.SqlSessionFactoryUtil;



public class InitMapperService {
    private final Logger log =     
    LoggerFactory.getLogger(InitMapperService.class);

    /** 建表语句 */
    public void createDataTable() {
        try {
            SqlSession session = SqlSessionFactoryUtil.getSqlSession(true);
            InitMapper mapper = session.getMapper(InitMapper.class);
            mapper.ensureStationGroupTab();
            mapper.ensureConfigureTab();
            mapper.ensureStationGroupRelationTab();
            session.close();
        } catch (Exception ex) {
            log.error("An error occurred while creating the data table.", ex);
        }
    }

    /** 初始化数据 */
    public void initDataTable() {
        try {
            SqlSession session = SqlSessionFactoryUtil.getSqlSession(true);
            InitMapper mapper = session.getMapper(InitMapper.class);
            mapper.initDataTable();
            session.close();
        } catch (Exception ex) {
            log.error("An error occurred while creating the data table.", ex);
        }
    }

    public void initData() {
        createDataTable();
        initDataTable();
    }
}
    

5、MapperTest.java

    @Test
    public void testInitData()
    {
        InitMapperService initMapperService=new InitMapperService();
        initMapperService.initData();
    }