flea-db使用之SQL模板接入

原创

huazie

发布于 2025-07-13 22:42:01

1421

文章被收录于专栏：开发框架-Flea开发框架-Flea

引言

本篇将要演示 SQL 模板的使用，目前包含 INSERT 模板、SELECT 模板、UPDATE 模板、DELETE模板。

1. 准备工作

为了演示SQL模板接入（参考 JPA接入中的准备工作），需要如下准备：

MySQL数据库 (客户端可以使用 navicat for mysql)
新建测试数据库 fleajpatest
新建测试表 student

2. 使用讲解

2.1 SQL模板配置

SQL模板配置包含了SQL模板规则，SQL模板定义，SQL模板参数，SQL关系配置。具体配置可至GitHub，查看 flea-sql-template.xml

SQL 模板规则，即定义 SQL 模板的校验规则，主要包含增删改查的 4 类模板。

以 INSERT SQL 模板的校验规则配置举例，如下所示【这里属性 value 值其实就是 INSERT SQL 模板的正则表达式】：

    <!-- 这边的规则为固定写法，一般不做修改 -->
    <rules>
        <rule id="insert" name="INSERT SQL模板的校验规则配置">
            <property key="sql" value="[ ]*(INSERT)[ ]+(INTO)[ ]+##table##[ ]+\([ ]*##columns##[ ]+\)[ ]+(VALUES)[ ]+\([ ]*##values##[ ]+\)[ ]*" />
        </rule>
    </rules>

SQL 模板定义，即定义通用的增删改查 SQL 模板。

以 INSERT SQL 模板定义举例，如下所示：

    <!-- SQL模板定义配置 -->
    <templates>
        <template id="insert" ruleId="insert" name="INSERT SQL模板" desc="用于原生SQL中INSERT语句的使用">
            <!-- SQL模板数据 -->
            <property key="template" value="INSERT INTO ##table## (##columns## ) VALUES (##values## )" />
            <!-- SQL模板类型 -->
            <property key="type" value="insert"/>
        </template>
    </templates>

SQL 模板参数，即定义 SQL 模板中的参数取值。

以 INSERT SQL 模板参数举例，如下所示：

    <!-- SQL模板参数配置 -->
    <params>
        <param id="insert" name="SQL模板參數" desc="用于定义SQL模板中的替换参数">
            <!-- 表名 -->
            <property key="table" value="flea_config_data" />
            <!-- 这两个不填，表示表的字段全部使用
            <property key="columns" value="config_id, config_type, config_code, config_name, data1, config_state" />
            <property key="values" value=":configId:, :configType:, :configCode:, :configName:, :data1:, :configState:" />-->
        </param>
    </params>

SQL 关系配置，用于关联 SQL 模板和 SQL 模板参数。

以 INSERT SQL 关系配置举例，如下所示：

    <!-- SQL模板和模板参数关联关系配置（简称 SQL关系配置）-->
    <relations>
        <relation id="insert" templateId="insert" paramId="insert" name="SQL关系"/>
    </relations>

relation 用于定义一条 SQL 关系配置：

id : SQL关系编号
templateId : SQL模板编号
paramId : SQL模板参数编号

2.2 新增数据

相关配置可查看：

    <param id="insert" name="SQL模板參數" desc="用于定义SQL模板中的替换参数">
        <!-- 表名 -->
        <property key="table" value="student" />
        <!-- 这两个不填，表示表的字段全部使用-->
        <property key="columns" value="stu_name, stu_age, stu_sex, stu_state" />
        <property key="values" value=":stuName:, :stuAge:, :stuSex:, :stuState:" />
    </param>

    <relation id="insert" templateId="insert" paramId="insert" name="SQL关系"/>

JPA方式接入SQL模板：

    @Test
    public void testInsertSqlTemplateFromJPA() throws Exception{
        IStudentSV studentSV = (IStudentSV) applicationContext.getBean("studentSV");

        Student student = new Student();
        student.setStuName("王老五");
        student.setStuAge(35);
        student.setStuSex(1);
        student.setStuState(1);

        int ret = studentSV.insert("insert", student);
        LOGGER.debug("result = {}", ret);
    }

运行结果：

新增数据：

JDBC方式接入SQL模板：

    @Test
    public void testInsertSqlTemplateFromJDBC() throws Exception {
        FleaJDBCConfig.init(DBSystemEnum.MySQL.getName(), "fleajpatest");

        Student student = new Student();
        student.setStuName("钱老六");
        student.setStuAge(30);
        student.setStuSex(1);
        student.setStuState(1);

        int ret = FleaJDBCHelper.insert("insert", student);
        LOGGER.debug("result = {}", ret);
    }

运行结果：

新增数据：

2.3 查询数据

相关配置可查看：

    <param id="select" name="SQL模板參數" desc="用于定义SQL模板中的替换参数; 如需查询全部，则设置key=columns的属性值为 *，即可">
        <!-- 表名 -->
        <property key="table" value="student" />
        <!-- SELECT 显示列 -->
        <property key="columns" value="*" />
        <!-- WHERE 子句 , 出现 xml不能直接识别的需要转义，如 >, < 等-->
        <property key="conditions" value="stu_name LIKE :stuName: AND stu_sex = :stuSex: AND stu_age &gt;= :minAge: AND stu_age &lt;= :maxAge:" />
    </param>

    <relation id="select" templateId="select" paramId="select" name="SQL关系"/>

JPA方式接入SQL模板：

    @Test
    public void testQuerySqlTemplateFromJPA() throws Exception {
        IStudentSV studentSV = (IStudentSV) applicationContext.getBean("studentSV");

        Student student = new Student();
        student.setStuName("%老%");
        student.setStuSex(1);
        student.put("minAge", 20);
        student.put("maxAge", 40);

        LOGGER.debug("Student List = {}", studentSV.query("select", student));
    }

运行结果：

JDBC方式接入SQL模板：

    @Test
    public void testQuerySqlTemplateFromJDBC() throws Exception {
        FleaJDBCConfig.init(DBSystemEnum.MySQL.getName(), "fleajpatest");

        Student student = new Student();
        student.setStuName("%老%");
        student.setStuSex(1);
        student.put("minAge", 20);
        student.put("maxAge", 40);

        LOGGER.debug("Student List = {}", FleaJDBCHelper.query("select", student));
    }

运行结果：

2.4 更新数据

相关配置可查看：

    <param id="update" name="SQL模板參數" desc="用于定义SQL模板中的替换参数">
        <!-- 表名 -->
        <property key="table" value="student" />
        <!-- SET 子句 -->
        <property key="sets" value="stu_name = :stuName, stu_age = :stuAge" />
        <!-- WHERE 子句 , 出现 xml不能直接识别的需要转义，如 >, < 等-->
        <property key="conditions" value="stu_name LIKE :sName: AND stu_state = :stuState: AND stu_sex = :stuSex: AND stu_age &gt;= :minAge: AND stu_age &lt;= :maxAge:" />
    </param>
        
    <relation id="update" templateId="update" paramId="update" name="SQL关系"/>

JPA方式接入SQL模板：

    @Test
    public void testUpdateSqlTemplateFromJPA() throws Exception {
        IStudentSV studentSV = (IStudentSV) applicationContext.getBean("studentSV");

        Student student = new Student();
        student.setStuName("王老五1");
        student.setStuAge(40);
        student.setStuState(1);
        student.setStuSex(1);
        student.put("sName", "%王老五%");
        student.put("minAge", 20);
        student.put("maxAge", 40);

        LOGGER.debug("Result = {}", studentSV.update("update", student));
    }

运行结果：

JDBC方式接入SQL模板：

    @Test
    public void testUpdateSqlTemplateFromJDBC() throws Exception {
        FleaJDBCConfig.init(DBSystemEnum.MySQL.getName(), "fleajpatest");

        Student student = new Student();
        student.setStuName("钱老六1");
        student.setStuAge(35);
        student.setStuState(1);
        student.setStuSex(1);
        student.put("sName", "%钱老六%");
        student.put("minAge", 20);
        student.put("maxAge", 40);

        LOGGER.debug("Result = {}", FleaJDBCHelper.update("update", student));
    }

运行结果：

2.5 删除数据

相关配置可查看：

    <param id="delete" name="SQL模板參數" desc="用于定义SQL模板中的替换参数">
        <!-- 表名 -->
        <property key="table" value="student" />
        <!-- WHERE 子句 -->
        <property key="conditions" value="stu_name LIKE :stuName: AND stu_state = :stuState: AND stu_sex = :stuSex: AND stu_age &gt;= :minAge: AND stu_age &lt;= :maxAge:" />
    </param>
        
    <relation id="delete" templateId="delete" paramId="delete" name="SQL关系"/>

JPA方式接入SQL模板：

    @Test
    public void testDeleteSqlTemplateFromJPA() throws Exception {
        IStudentSV studentSV = (IStudentSV) applicationContext.getBean("studentSV");

        Student student = new Student();
        student.setStuName("%王老五%");
        student.setStuState(1);
        student.setStuSex(1);
        student.put("minAge", 20);
        student.put("maxAge", 40);

        LOGGER.debug("Result = {}", studentSV.delete("delete", student));
    }

运行结果：

JDBC方式接入SQL模板：

    @Test
    public void testDeleteSqlTemplateFromJDBC() throws Exception {
        FleaJDBCConfig.init(DBSystemEnum.MySQL.getName(), "fleajpatest");

        Student student = new Student();
        student.setStuName("%钱老六%");
        student.setStuState(1);
        student.setStuSex(1);
        student.put("minAge", 20);
        student.put("maxAge", 40);

        LOGGER.debug("Result = {}", FleaJDBCHelper.delete("delete", student));
    }

运行结果：

2.6 分页查询

当前数据库数据如下：

相关配置可查看：

    <param id="select_1" name="SQL模板參數" desc="用于定义SQL模板中的替换参数; 如需查询全部，则设置key=columns的属性值为 *，即可">
        <!-- 表名 -->
        <property key="table" value="student" />
        <!-- SELECT 显示列 -->
        <property key="columns" value="*" />
        <!-- WHERE 子句 , 出现 xml不能直接识别的需要转义，如 >, < 等-->
        <property key="conditions" value="stu_name LIKE :stuName: AND stu_sex = :stuSex: AND stu_age &gt;= :minAge: AND stu_age &lt;= :maxAge: ORDER BY stu_id DESC LIMIT :pageStart:, :pageCount:" />
    </param>
    
    <relation id="select_1" templateId="select" paramId="select_1" name="SQL关系"/>

JPA方式接入SQL模板：

    @Test
    public void testQueryPageSqlTemplateFromJPA() throws Exception {
        IStudentSV studentSV = (IStudentSV) applicationContext.getBean("studentSV");

        Student student = new Student();
        student.setStuName("%张三%");
        student.setStuSex(1);
        student.put("minAge", 18);
        student.put("maxAge", 20);
        int pageNum = 1;   // 第一页
        int pageCount = 5; // 每页5条记录
        student.put("pageStart", (pageNum - 1) * pageCount);
        student.put("pageCount", pageCount);

        List<Student> studentList = studentSV.query("select_1", student);
        LOGGER.debug("Student List = {}", studentList);
        LOGGER.debug("Student Count = {}", studentList.size());
    }

运行结果：

JDBC方式接入SQL模板：

     @Test
    public void testQueryPageSqlTemplateFromJDBC() throws Exception {
        FleaJDBCConfig.init(DBSystemEnum.MySQL.getName(), "fleajpatest");

        Student student = new Student();
        student.setStuName("%李四%");
        student.setStuSex(1);
        student.put("minAge", 18);
        student.put("maxAge", 20);
        int pageNum = 1;   // 第一页
        int pageCount = 5; // 每页5条记录
        student.put("pageStart", (pageNum - 1) * pageCount);
        student.put("pageCount", pageCount);

        List<Map<String, Object>> studentList = FleaJDBCHelper.query("select_1", student);
        LOGGER.debug("Student List = {}", studentList);
        LOGGER.debug("Student Count = {}", studentList.size());
    }

运行结果：

2.7 单个结果查询--计数

相关配置可查看：

    <param id="select_2" name="SQL模板參數" desc="用于定义SQL模板中的替换参数; 如需查询全部，则设置key=columns的属性值为 *，即可">
        <!-- 表名 -->
        <property key="table" value="student" />
        <!-- SELECT 显示列 -->
        <property key="columns" value="count(*)" />
        <!-- WHERE 子句 , 出现 xml不能直接识别的需要转义，如 >, < 等-->
        <property key="conditions" value="stu_name LIKE :stuName: AND stu_sex = :stuSex: AND stu_age &gt;= :minAge: AND stu_age &lt;= :maxAge:" />
    </param>
        
    <relation id="select_2" templateId="select" paramId="select_2" name="SQL关系"/>

JPA方式接入SQL模板：

    @Test
    public void testQueryCountSqlTemplateFromJPA() throws Exception {
        IStudentSV studentSV = (IStudentSV) applicationContext.getBean("studentSV");

        Student student = new Student();
        student.setStuName("%张三%");
        student.setStuSex(1);
        student.put("minAge", 18);
        student.put("maxAge", 20);

        LOGGER.debug("Student Count = {}", studentSV.querySingle("select_2", student));
    }

运行结果：

JDBC方式接入SQL模板：

    @Test
    public void testQueryCountSqlTemplateFromJDBC() throws Exception {
        FleaJDBCConfig.init(DBSystemEnum.MySQL.getName(), "fleajpatest");

        Student student = new Student();
        student.setStuName("%李四%");
        student.setStuSex(1);
        student.put("minAge", 18);
        student.put("maxAge", 20);

        LOGGER.debug("Student Count = {}", FleaJDBCHelper.querySingle("select_2", student));
    }

运行结果：

2.8 单个结果查询--总和

相关配置可查看：

    <param id="select_3" name="SQL模板參數" desc="用于定义SQL模板中的替换参数; 如需查询全部，则设置key=columns的属性值为 *，即可">
        <!-- 表名 -->
        <property key="table" value="student" />
        <!-- SELECT 显示列 -->
        <property key="columns" value="sum(stu_age)" />
        <!-- WHERE 子句 , 出现 xml不能直接识别的需要转义，如 >, < 等-->
        <property key="conditions" value="1=1" />
    </param>
        
    <relation id="select_3" templateId="select" paramId="select_3" name="SQL关系"/>

JPA方式接入SQL模板：

    @Test
    public void testQuerySumSqlTemplateFromJPA() throws Exception {
        IStudentSV studentSV = (IStudentSV) applicationContext.getBean("studentSV");
        LOGGER.debug("Student Age = {}", studentSV.querySingle("select_3", new Student()));
    }

运行结果：

JDBC方式接入SQL模板：

    @Test
    public void testQuerySumSqlTemplateFromJDBC() throws Exception {
        FleaJDBCConfig.init(DBSystemEnum.MySQL.getName(), "fleajpatest");
        LOGGER.debug("Student Age = {}", FleaJDBCHelper.querySingle("select_3", new Student()));
    }

运行结果：