MyBatis 实现通用化的数据表动态创建、写入与读取

置顶 nx__xn

已于 2024-08-15 12:32:17 修改

阅读量2.0k

点赞数 45

分类专栏：技术随笔 MyBatis 文章标签： mybatis 数据库

于 2024-08-15 12:22:25 首次发布

本文链接：https://blog.csdn.net/weixin_59271089/article/details/141145708

版权

MyBatis 同时被 2 个专栏收录

5 篇文章 0 订阅

订阅专栏

技术随笔

2 篇文章 0 订阅

订阅专栏

背景

前段时间做了一个项目，其中有读取并保存用户上传的 Excel 表格的需求，当时我选择了用 EasyExcel 库来解析文件，并计划将解析后的全表数据转换成 CSV 格式的字符串，直接存储到数据库表的一个字段中。
然而，经过实践后发现，这种存储方式存在显著的弊端：它不仅不便于后续的个性化查询和编辑操作，而且在面对大量数据或数据表格较大的情况下，会导致数据库表体积迅速膨胀，进而引发查询性能的显著下降。
对此，我想到可以采用分表策略的解决方案，即把每个原始的Excel表格存储在一个单独的数据表中。
这样做的好处显而易见：通过将原始数据以标准的表格形式存储在各自独立的数据表中，我们得以利用SQL语句进行高效、灵活的数据检索，仅针对需要查询的列或行进行操作。同时，数据库索引等优化技术的运用也变得更为自然和高效，能够极大地提升数据的定位精度和查询速度，从而显著提高系统的整体响应性能和用户体验。

实现

项目初始化

为了进行测试，我写了一个简单的 demo 项目，项目依赖如下，分别引入了 mysql 的 jdbc 驱动和 MyBatis 依赖包：

<dependency>
  <groupId>com.mysql</groupId>
  <artifactId>mysql-connector-j</artifactId>
  <version>8.4.0</version>
</dependency>
<dependency>
  <groupId>org.mybatis</groupId>
  <artifactId>mybatis</artifactId>
  <version>3.5.16</version>
</dependency>

同时，在 src/main/resources 下创建映射文件 ChartMapper.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.nx.mapper.ChartMapper">

</mapper>

然后创建对应的映射接口 ChartMapper

package com.nx.mapper;

/**
 * chart mapper
 *
 * @author nx-xn2002
 */
public interface ChartMapper {
}

在 src/main/resources 下创建 MyBatis 配置文件 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="logImpl" value="STDOUT_LOGGING"/>
    </settings>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/dynamic_tables"/>
                <property name="username" value="root"/>
                <property name="password" value="1234567890"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="ChartMapper.xml"/>
    </mappers>
</configuration>

在本地创建对应的数据库 dynamic_tables

create database if not exists dynamic_tables;

项目文件结构如下：

动态建表

实践

为了方便对所有表格都能进行通用的建表，我们要先考虑一个统一的存储格式，我觉得可以使用 HashMap<String, Object> 维护一个类 JSON 格式的数据来表示表格，格式大致如下：

{
  tableName: 表名
  columns: [字段1, 字段2, 字段3]
  data: [
    [数据1-1, 数据1-2, 数据1-3],
    [数据2-1, 数据2-2, 数据2-3]
  ]
}

我们创建测试用的假数据表如下：

private static HashMap<String, Object> getFakeChart() {
    HashMap<String, Object> fakeChart = new LinkedHashMap<>();
    fakeChart.put("tableName", "users");
    fakeChart.put("columns", Arrays.asList("id", "name", "email"));
    fakeChart.put("data", Arrays.asList(
            Arrays.asList("1", "John", "john@example.com"),
            Arrays.asList("2", "Bob", "bob@example.com")
    ));
    return fakeChart;
}

通过上面的 HashMap，我们可以表示出这样的数据表

id	name	email
1	John	john@example.com
2	Bob	bob@example.com

接下来就可以开始在 ChartMapper.xml 中尝试去进行动态建表语句的编写了，我们可以按照建表逻辑先初步写一个建表语句：

<update id="createTable" parameterType="map">
    create table if not exists ${tableName} (
    <foreach collection="columns" item="column" index="index" separator=",">
        ${column} VARCHAR(255)
    </foreach>
    )
</update>

在这里面，我们把刚刚 HashMap 中定义的 tableName 和 columns 属性都拼接进了 SQL 语句中，此处因为是直接拼接，所以使用的是 ${} 符，因此实际使用的时候，需要先对这些字段名做校验和过滤，来避免 SQL 注入的风险。
为了方便以后进行编辑和扩展，我想为表格添加一个主键，但是因为不能确定原数据表的情况，不能随意指定某一列作为主键，所以我单独添加了一列作为主键，同时，为了避免一些错误和无效操作，我加上了一些校验，最终的建表语句如下：

<update id="createTable" parameterType="map">
    <if test="tableName != null and tableName.length() > 0">
        create table if not exists ${tableName} (
        <if test="columns != null and columns.size > 0">chart_column_index INT AUTO_INCREMENT PRIMARY KEY,</if>
        <foreach collection="columns" item="column" index="index" separator=",">
            ${column} VARCHAR(255)
        </foreach>
        )
    </if>
</update>

然后在映射接口 ChartMapper 中补上方法定义即可：

public interface ChartMapper {
    /**
     * 动态创建表
     *
     * @param params params
     * @author nx-xn2002
     */
    void createTable(Map<String, Object> params);
}

测试

接下来进行测试，可以看到，一开始我的 dynamic_tables 库中没有任何表

我编写了以下测试代码：

/**
 * chart mapper test
 *
 * @author nx-xn2002
 * @date 2024-08-13
 */
class ChartMapperTest {
    @Test
    void createTable() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        ChartMapper mapper = sqlSession.getMapper(ChartMapper.class);
        //假数据
        HashMap<String, Object> fakeChart = getFakeChart();
        //调用建表
        mapper.createTable(fakeChart);
        sqlSession.close();
    }

    private static HashMap<String, Object> getFakeChart() {
        HashMap<String, Object> fakeChart = new LinkedHashMap<>();
        fakeChart.put("tableName", "users");
        fakeChart.put("columns", Arrays.asList("id", "name", "email"));
        fakeChart.put("data", Arrays.asList(
                Arrays.asList("1", "John", "john@example.com"),
                Arrays.asList("2", "Bob", "bob@example.com")
        ));
        return fakeChart;
    }
}

运行时控制台打印日志如下：
在这里插入图片描述
可以看到，MyBatis 成功拼接出了预期的建表语句 create table if not exists users ( chart_column_index INT AUTO_INCREMENT PRIMARY KEY, id VARCHAR(255) , name VARCHAR(255) , email VARCHAR(255) )
数据库中也建表完成：

动态写入数据

实践

对于动态写入，整体思路也和刚刚是一样的，传入并拼接参数即可

<update id="insertData" parameterType="map">
    insert into ${tableName} (
    <foreach collection="columns" item="column" separator=",">
        ${column}
    </foreach>
    ) values
    <foreach collection="data" item="row" separator=",">
        ( <foreach collection="row" item="value" separator=",">#{value}</foreach> )
    </foreach>
</update>

注意这里对 tableName、column这些都是使用 ${} 直接进行拼接，存在 sql 注入风险，需要预先进行过滤，而对传入值 value 则使用的是 # 占位符进行预编译，这样能够使得执行效率提升，同时避免了 sql 注入的风险

测试

在刚刚建表测试的基础上，我们添加写入的测试如下：

@Test
void insertData() throws IOException {
    String resource = "mybatis-config.xml";
    InputStream inputStream = Resources.getResourceAsStream(resource);
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    SqlSession sqlSession = sqlSessionFactory.openSession();
    ChartMapper mapper = sqlSession.getMapper(ChartMapper.class);
    //假数据
    HashMap<String, Object> fakeChart = getFakeChart();
    //调用写入
    mapper.insertData(fakeChart);
    sqlSession.commit();
    sqlSession.close();
}

运行测试并查看数据库，可以看到，数据插入成功
在这里插入图片描述
代码执行日志如下，可以看到，sql 语句按照我们预期进行了拼接和生成，使用 $ 占位符的直接进行了拼接，而使用 # 占位符的是用 ? 来表示，执行时直接传值

注意

在 MyBatis 中，每个 SqlSession 默认是在一个事务中运行的，并且这个事务默认是不自动提交的，需要显式地调用 commit 或 rollback 方法来结束事务，此处进行数据插入就需要调用 commit 方法进行事务提交

动态读取数据

实践

要实现动态读取数据实际上并不复杂，但一开始我没有找到合适的方法，因为通常使用 MyBatis 去读取数据都是自己写一个 POJO 类，然后让 MyBatis 通过 resultMap 将结果映射为这个类的对象进行返回，然而此处因为表结构不确定，无法有效地进行封装。
后来我还是找到了解决办法，单行数据其实是可以使用 HashMap<String, String> 进行封装读取的，要保证原表中字段的有序性，可以使用 LinkedHashMap<String, String>，它底层使用一个双向链表维护了键的插入顺序。找到读取单行数据的方法，结合 MyBatis 读取多行数据可以自动封装成 List，因此有如下实现：
映射文件

<select id="selectAll" parameterType="String" resultType="java.util.LinkedHashMap">
    SELECT *
    FROM ${tableName}
</select>

接口

/**
 * select all
 *
 * @param tableName table name
 * @return {@link LinkedHashMap }<{@link String }, {@link String }>
 * @author nx-xn2002
 */
List<LinkedHashMap<String, String>> selectAll(String tableName);

测试

对于查表，我写了以下测试：

@Test
void selectAll() throws IOException {
    String resource = "mybatis-config.xml";
    InputStream inputStream = Resources.getResourceAsStream(resource);
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    SqlSession sqlSession = sqlSessionFactory.openSession();
    ChartMapper mapper = sqlSession.getMapper(ChartMapper.class);
    //调用查表
    List<LinkedHashMap<String, String>> chart = mapper.selectAll((String) getFakeChart().get("tableName"));
    chart.forEach(System.out::println);
    sqlSession.close();
}

执行结果如下：
在这里插入图片描述
结果符合预期，至此，功能开发完毕

省流

代码与实现

将原始数据使用 HashMap<String, Object> 进行存储，然后以形如 JSON 的格式进行保存

{
  tableName: 表名
  columns: [字段1, 字段2, 字段3]
  data: [
    [数据1-1, 数据1-2, 数据1-3],
    [数据2-1, 数据2-2, 数据2-3]
  ]
}

引入依赖：

<dependency>
  <groupId>com.mysql</groupId>
  <artifactId>mysql-connector-j</artifactId>
  <version>8.4.0</version>
</dependency>
<dependency>
  <groupId>org.mybatis</groupId>
  <artifactId>mybatis</artifactId>
  <version>3.5.16</version>
</dependency>

在 src/main/resources 下创建映射文件 ChartMapper.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.nx.mapper.ChartMapper">
    <!-- 动态创建表 -->
    <update id="createTable" parameterType="map">
        <if test="tableName != null and tableName.length() > 0">
            create table if not exists ${tableName} (
            <if test="columns != null and columns.size > 0">chart_column_index INT AUTO_INCREMENT PRIMARY KEY,</if>
            <foreach collection="columns" item="column" index="index" separator=",">
                ${column} VARCHAR(255)
            </foreach>
            )
        </if>
    </update>
    <!-- 动态插入数据 -->
    <update id="insertData" parameterType="map">
        insert into ${tableName} (
        <foreach collection="columns" item="column" separator=",">
            ${column}
        </foreach>
        ) values
        <foreach collection="data" item="row" separator=",">
            ( <foreach collection="row" item="value" separator=",">#{value}</foreach> )
        </foreach>
    </update>
    <select id="selectAll" parameterType="String" resultType="java.util.LinkedHashMap">
        SELECT *
        FROM ${tableName}
    </select>
</mapper>

然后创建对应的映射接口 ChartMapper

package com.nx.mapper;

import org.apache.ibatis.annotations.MapKey;

import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

/**
 * chart mapper
 *
 * @author nx-xn2002
 * @date 2024-08-11
 */
public interface ChartMapper {
    /**
     * 动态创建表
     *
     * @param params params
     * @author nx-xn2002
     */
    void createTable(Map<String, Object> params);

    /**
     * 插入数据
     *
     * @param params params
     * @author nx-xn2002
     */
    void insertData(Map<String, Object> params);

    /**
     * select all
     *
     * @param tableName table name
     * @return {@link LinkedHashMap }<{@link String }, {@link String }>
     * @author nx-xn2002
     */
    List<LinkedHashMap<String, String>> selectAll(String tableName);
}

在 src/main/resources 下创建 MyBatis 配置文件 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="logImpl" value="STDOUT_LOGGING"/>
    </settings>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/dynamic_tables"/>
                <property name="username" value="root"/>
                <property name="password" value="1234567890"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="ChartMapper.xml"/>
    </mappers>
</configuration>

在本地创建对应的数据库 dynamic_tables

create database if not exists dynamic_tables;

最后项目文件结构如下：

运行测试程序，尝试插入以下数据表

id	name	email
1	John	john@example.com
2	Bob	bob@example.com

package com.nx;

import com.nx.mapper.ChartMapper;
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.InputStream;
import java.util.*;

/**
 * Chart App
 *
 * @author nx-xn2002
 * @date 2024-08-13
 */
public class App {
    public static void main(String[] args) throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        ChartMapper mapper = sqlSession.getMapper(ChartMapper.class);
        //假数据
        HashMap<String, Object> fakeChart = getFakeChart();
        //调用建表
        mapper.createTable(fakeChart);
        mapper.insertData(fakeChart);
        sqlSession.commit();
        List<LinkedHashMap<String, String>> chart = mapper.selectAll((String) fakeChart.get("tableName"));
        System.out.println(chart);
        sqlSession.close();
    }

    private static HashMap<String, Object> getFakeChart() {
        HashMap<String, Object> fakeChart = new LinkedHashMap<>();
        fakeChart.put("tableName", "users");
        fakeChart.put("columns", Arrays.asList("id", "name", "email"));
        fakeChart.put("data", Arrays.asList(
                Arrays.asList("1", "John", "john@example.com"),
                Arrays.asList("2", "Bob", "bob@example.com")
        ));
        return fakeChart;
    }
}