mybatis 1-3

诗言酒

456人浏览 · 2026-02-08 20:32:37

诗言酒 · 2026-02-08 20:32:37 发布

6.可重用 SQL 片

        核心概念：<sql> 与 <include> 到底是什么？
1. 本质作用
        <sql> 是 MyBatis 提供的「SQL 片段定义标签」，用于把重复出现的 SQL 片段（如列列表、固定条件、排序规则）抽离成独立模块；<include> 是「引用标签」，用于在主 SQL 中复用这些片段。
2. 核心价值
        减少重复代码：比如 10 个查询都需要查 id, username, email, created_at，无需重复写 10 次，抽成 <sql> 后只需 <include> 引用；
        统一维护：如果需要新增查询列（如 phone），只需修改 <sql> 片段，所有引用处自动生效，避免漏改 / 错改；
        提升可读性：主 SQL 只保留核心逻辑，通用片段抽离后更易理解。

三种主要方式：

（1）复用列的列表

Java类如下1-1图：

package com.lo.mybatis.model;

public class Books {
    private int id;
    private String name;
    private  String author;
    private double price;
    private String publish;
    private Students students;
//setget省略

   }

1-1

xml配置如下图1-2：

<?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.lo.mybatis.dao.BooksDao">

<!--    定义sql片段-->
    <sql id="com-lon-bookslie">
        id,
        name,
        author,
        price,
        publish
    </sql>

    <select id="finddBooks" resultType="com.lo.mybatis.model.Books" parameterType="java.lang.Integer">
        select
            <include refid="com-lon-bookslie"/>
        from books where id=#{id}
    </select>

</mapper>

1-2

数据库字段如下1-3：

1-3

运行代码如下

public class T {

    private static final String MYBATIS_CONFIG_PATH = "mybatis-config.xml";

    public static void main(String[] args) {
        try (InputStream configInputStream = Resources.getResourceAsStream(MYBATIS_CONFIG_PATH);
             SqlSession sqlSession = new SqlSessionFactoryBuilder()
                     .build(configInputStream)
                     .openSession()) {


            BooksDao booksDao = sqlSession.getMapper(BooksDao.class);
           Books book=booksDao.finddBooks(1);
            System.out.println(book);

        } catch (IOException e) {
            // 5. 异常捕获与提示，便于排查问题
            System.err.println("加载 MyBatis 配置文件失败：" + e.getMessage());
            e.printStackTrace();
        } catch (Exception e) {
            System.err.println("查询书籍数据失败：" + e.getMessage());
            e.printStackTrace();
        }
    }
}

结果如下：

Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@47eaca72]
==>  Preparing: select id, name, author, price, publish from books where id=?
==> Parameters: 1(Integer)
<==    Columns: id, name, author, price, publish
<==        Row: 1, book_124, author_50, 27, publisher_6
<==      Total: 1
Books{id=1, Myname='book_124', author='author_50', price=27.0, publish='publisher_6', students=null}
Resetting autocommit to true on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@47eaca72]
Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@47eaca72]
Returned connection 1206569586 to pool.

可以看出完全能查询出结果。

（2）.复用固定 WHERE 条件（静态条件）

类和上面是相同的：

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.lo.mybatis.dao.BooksDao">

<!--    定义sql片段-->
    <sql id="com-lon-bookslie">
        id,
        name,
        author,
        price,
        publish
    </sql>


<!--    where的sql片段-->
    <sql id="com-lon-where">
        AND students_id =#{Id}
    </sql>

    <select id="selectById" parameterType="java.lang.Integer" resultType="com.lo.mybatis.model.Books">
        select
            <include refid="com-lon-bookslie"></include>
            from books
        <where>
            <if test="book !=null and book != ''">
                AND name LIKE CONCAT('%',#{book},'%')
            </if>
            <include refid="com-lon-where"></include>
        </where>
    </select>



</mapper>

一般 AND students_id =#{Id}是一个具体的值但是我的数据表没有使用students_id来进行对比。

<where> 标签的必要性：

        自动清理前缀：无论条件块开头是 AND 还是 OR，都会被移除，避免生成 WHERE AND is_deleted = 0 这种语法错误的 SQL；
        条件为空时不生成 WHERE：避免生成 FROM users WHERE 这种无意义的语法错误；
        兼容多条件组合：无论哪个条件先满足，都能保证 WHERE 子句语法正确。

Java接口代码如下：

public interface BooksDao {
 

//    where sql用法
    public List<Books> selectById(@Param("Id") int id,@Param("book") String bookname);

}

运行代码如下：

public class T {

    private static final String MYBATIS_CONFIG_PATH = "mybatis-config.xml";

    public static void main(String[] args) {
        try (InputStream configInputStream = Resources.getResourceAsStream(MYBATIS_CONFIG_PATH);
             SqlSession sqlSession = new SqlSessionFactoryBuilder()
                     .build(configInputStream)
                     .openSession()) {


            BooksDao booksDao = sqlSession.getMapper(BooksDao.class);
            List<Books> book = booksDao.selectById(6, "book");
            book.forEach(System.out::println);
        } catch (IOException e) {
            // 5. 异常捕获与提示，便于排查问题
            System.err.println("加载 MyBatis 配置文件失败：" + e.getMessage());
            e.printStackTrace();
        } catch (Exception e) {
            System.err.println("查询书籍数据失败：" + e.getMessage());
            e.printStackTrace();
        }
    }
}

数据库如下：

有50条数据。

执行后查询结果如下：

Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@4c402120]
==>  Preparing: select id, name, author, price, publish, students_id AS studentsId from books WHERE name LIKE CONCAT('%',?,'%') AND students_id =?
==> Parameters: book(String), 6(Integer)
<==    Columns: id, name, author, price, publish, studentsId
<==        Row: 2, book_706, author_11, 55, publisher_17, 6
<==        Row: 5, book_159, author_12, 23, publisher_6, 6
<==        Row: 16, book_27, author_32, 65, publisher_14, 6
<==        Row: 45, book_488, author_28, 107, publisher_0, 6
<==      Total: 4
Books{id=2, name='book_706', author='author_11', price=55.0, publish='publisher_17', students=null, studentsId=6}
Books{id=5, name='book_159', author='author_12', price=23.0, publish='publisher_6', students=null, studentsId=6}
Books{id=16, name='book_27', author='author_32', price=65.0, publish='publisher_14', students=null, studentsId=6}
Books{id=45, name='book_488', author='author_28', price=107.0, publish='publisher_0', students=null, studentsId=6}
Resetting autocommit to true on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@4c402120]
Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@4c402120]
Returned connection 1279271200 to pool.

可以看出执行成功。

（3）.参数作用域与动态片段

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.lo.mybatis.dao.BooksDao">

    <resultMap id="book_students" type="com.lo.mybatis.model.Books">
        <id column="id" property="id"/>
        <result column="name" property="name"/>
        <result column="author" property="author"/>
        <result column="price" property="price"/>
        <result column="publish" property="publish"/>
        <result column="studentsId" property="studentsId"/>
    </resultMap>


<!--    定义sql片段-->
    <sql id="com-lon-bookslie">
        id,
        name,
        author,
        price,
        publish,
        students_id AS studentsId
    </sql>

<!--    where的sql片段-->
    <sql id="com-lon-where">
        AND students_id =#{Id}
    </sql>

<!--    使用${}-->
    <sql id="fragment_user_orderBy">
        ORDER BY 
            <choose>
                <when test="sortColumn !=null and sortColumn in ('id','name','price','publish','studentsId')">
                    ${sortColumn}
                </when>
                <otherwise>id</otherwise>
            </choose>
            <choose>
                <when test="sortDirection !=null and sortDirection in('ASC','DESC')">
                    ${sortDirection}
                </when>
                <otherwise>ASC</otherwise>
            </choose>
    </sql>

    <select id="selectALLL" parameterType="java.lang.String" resultMap="book_students">
        select
            <include refid="com-lon-bookslie"></include>
            from books
            <include refid="fragment_user_orderBy"></include>
    </select>


</mapper>

Java类如下：

public class Books {
    private int id;
    private String name;
    private  String author;
    private double price;
    private String publish;
    private Students students;
    private int studentsId;
}

java接口如下：


    public List<Books> selectALLL(
            @Param("sortColumn")String a1,@Param("sortDirection")String a2
    );

测试代码：


public class T {

    private static final String MYBATIS_CONFIG_PATH = "mybatis-config.xml";

    public static void main(String[] args) {
        try (InputStream configInputStream = Resources.getResourceAsStream(MYBATIS_CONFIG_PATH);
             SqlSession sqlSession = new SqlSessionFactoryBuilder()
                     .build(configInputStream)
                     .openSession()) {


            BooksDao booksDao = sqlSession.getMapper(BooksDao.class);
            List<Books> book = booksDao.selectALLL("studentsId","ASC");
            book.forEach(System.out::println);
        } catch (IOException e) {
            // 5. 异常捕获与提示，便于排查问题
            System.err.println("加载 MyBatis 配置文件失败：" + e.getMessage());
            e.printStackTrace();
        } catch (Exception e) {
            System.err.println("查询书籍数据失败：" + e.getMessage());
            e.printStackTrace();
        }
    }
}

数据库图片：

执行结果：

Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@6a78afa0]
==>  Preparing: select id, name, author, price, publish, students_id AS studentsId from books ORDER BY studentsId ASC
==> Parameters: 
<==    Columns: id, name, author, price, publish, studentsId
<==        Row: 22, book_13, author_35, 84, publisher_13, 2
<==        Row: 14, book_763, author_12, 41, publisher_3, 4
<==        Row: 39, book_971, author_74, 93, publisher_18, 4
<==        Row: 11, book_14, author_13, 74, publisher_16, 5
<==        Row: 2, book_706, author_11, 55, publisher_17, 6
<==        Row: 5, book_159, author_12, 23, publisher_6, 6

Books{id=22, name='book_13', author='author_35', price=84.0, publish='publisher_13', students=null, studentsId=2}
Books{id=14, name='book_763', author='author_12', price=41.0, publish='publisher_3', students=null, studentsId=4}
Books{id=39, name='book_971', author='author_74', price=93.0, publish='publisher_18', students=null, studentsId=4}
Books{id=11, name='book_14', author='author_13', price=74.0, publish='publisher_16', students=null, studentsId=5}
Books{id=2, name='book_706', author='author_11', price=55.0, publish='publisher_17', students=null, studentsId=6}
Books{id=5, name='book_159', author='author_12', price=23.0, publish='publisher_6', students=null, studentsId=6}

Resetting autocommit to true on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@6a78afa0]
Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@6a78afa0]
Returned connection 1786294176 to pool.

Process finished with exit code 0

可以看出执行成功了且和数据库一致。

注：sql语句执行我简单记忆为静（<sql>、<resultMap>、<mapper>）->参数->动态（<include>、<if>、<choose>、<foreach>、<where>），select等标签是混合态的；MyBatis 中 resultMap 的 column 匹配的是SQL 执行后返回的结果集列名。

片段职责区分：
纯静态（列列表、固定 join、固定 where）放 <sql>；与参数相关或带 <if> 的动态逻辑尽量放在主语句内，用 <where>/<if>/<choose> 控制。

7. 参数处理方式

其他的参数处理前面都有涉及现在介绍MyBatis 中 TypeHandler 处理枚举类型转换。

TypeHandler 处理枚举类型转换

数据库结构下图1-1：

1-1

接口如下图1-2：

public interface TestUserMapper {

    @Select("SELECT id, username, password, nickname, phone, email, user_status as enue " +
            "FROM testuser WHERE id = #{id}")
    TestUser getTestUserById(@Param("id") Integer id);
}

1-2

以上的是注解的方式没有xml文件

再总xml中添加下图1-3：

<?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"/>
                <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>
   
<!--    枚举-->
    <typeHandlers>
        <typeHandler handler="com.lo.mybatis.enue.UserEnueTypeHandler"/>
    </typeHandlers>

    <!--配置数据库的链接信息-->
    <environments default="abc">
        <environment id="abc">
            <!--        配置事务管理器-->
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <!--            配置数据库链接信息-->
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/mybatis_yc?serverTimezone=UTC&amp;characterEncoding=UTF-8"/>
                <property name="username" value="root"/>
                <property name="password" value="root"/>
            </dataSource>
        </environment>
    </environments>

    <!-- 注册Mapper接口 -->
    <mappers>
        <mapper class="com.lo.mybatis.dao.TestUserMapper"/>
    </mappers>

</configuration>

1-3

JSON类（实体类）下图1-4：

public class TestUser {
    private int id;
    private String username;
    private String password;
    private String nickname;
    private String phone;
    private String email;
    private UserEnue enue;

    @Override
    public String toString() {
        return "TestUser{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                ", nickname='" + nickname + '\'' +
                ", phone='" + phone + '\'' +
                ", email='" + email + '\'' +
                ", enue=" + enue.getResult() +enue.getCode()+
                '}';
    }
//setget省略

  }

1-4

定义枚举类如下图1-5：

public enum UserEnue {
//    数据库到 Java字段（展示或是）
    ZHENGCHANG(1,"正常"),
    JINYONG(2,"禁用"),
    SHANCHU(3,"删除");

//枚举私有属性
    private final int code;
    private final String result;

   private UserEnue(int code, String result) {
        this.code = code;
        this.result = result;
    }

    public int getCode() {
        return code;
    }

    public String getResult() {
        return result;
    }

//    查询并返回相应的值
    public static UserEnue getJavaValue(int code){
        for (UserEnue userEnue:
             values()) {
            if(userEnue.getCode()==code){
                return userEnue;
            }
        }
        throw new IllegalArgumentException("无效的用户状态码：" + code + "，仅支持1、2、3");
    }
}

1-5

作用：例如数据库字段user_status字段存储的值是1查询时查询到的值也是1但是有枚举类和枚举转换器后会将对应的值赋给对应的属性，如 UserEnue enue的值不是一而是“禁用或其他”。

枚举类的处理器下图1-6：

@MappedTypes(UserEnue.class)
@MappedJdbcTypes(JdbcType.INTEGER)
public class UserEnueTypeHandler extends BaseTypeHandler<UserEnue> {
    @Override
    public void setParameter(PreparedStatement ps, int i, UserEnue parameter, JdbcType jdbcType) throws SQLException {
       ps.setInt(i,parameter.getCode());
    }

    @Override
    public UserEnue getResult(ResultSet rs, String columnName) throws SQLException {
       int code= rs.getInt(columnName);
       return code != 0 ? UserEnue.getJavaValue(code) :null;
    }

    @Override
    public UserEnue getResult(ResultSet rs, int columnIndex) throws SQLException {
        int code= rs.getInt(columnIndex);
        return UserEnue.getJavaValue(code);
    }

    @Override
    public UserEnue getResult(CallableStatement cs, int columnIndex) throws SQLException {
        int code= cs.getInt(columnIndex);
        return UserEnue.getJavaValue(code);
    }

   
}

1-6

处除了第一个时从Java-->数据库，其余三个都是数据库-->Java。

执行代码下图1-7：

public class T {

    private static final String MYBATIS_CONFIG_PATH = "mybatis-config.xml";

    public static void main(String[] args) {
        try (InputStream configInputStream = Resources.getResourceAsStream(MYBATIS_CONFIG_PATH);
             SqlSession sqlSession = new SqlSessionFactoryBuilder()
                     .build(configInputStream)
                     .openSession()) {

            TestUserMapper testUserMapper=sqlSession.getMapper(TestUserMapper.class);
            TestUser testUserById = testUserMapper.getTestUserById(1);
            System.out.println(testUserById);

        } catch (IOException e) {
            // 5. 异常捕获与提示，便于排查问题
            System.err.println("加载 MyBatis 配置文件失败：" + e.getMessage());
            e.printStackTrace();
        } catch (Exception e) {
            System.err.println("查询数据失败：" + e.getMessage());
            e.printStackTrace();
        }
    }
}

1-7

执行流程：加载配置文件-->调用getTestUserById（1）触发SQL执行-->Mybatis解析结果集，触发TypeHandler转换（Mybatis发现enue属性是UserEnue类型，并且配置了枚举类型处理器，Mybatis会调用这个处理器；Mybatis从结果集中取出enue（即数据库的 user_status））字段的值 1，作为 code 参数，调用 getJavaValue(1)，最终赋值给enue属性；最后Mybatis封装后返回结果。

执行结果如下图1-8：

Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@2e005c4b]
==>  Preparing: SELECT id, username, password, nickname, phone, email, user_status as enue FROM testuser WHERE id = ?
==> Parameters: 1(Integer)
<==    Columns: id, username, password, nickname, phone, email, enue
<==        Row: 1, zhangsan, e10adc3949ba59abbe56e057f20f883e, 张三, 13800138000, zhangsan@test.com, 1
<==      Total: 1
TestUser{id=1, username='zhangsan', password='e10adc3949ba59abbe56e057f20f883e', nickname='张三', phone='13800138000', email='zhangsan@test.com', enue=正常1}
Resetting autocommit to true on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@2e005c4b]
Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@2e005c4b]
Returned connection 771775563 to pool.

1-8

动态sql前面有提及，这里就不作解释了。

2048 AI社区

有“AI”的1024 = 2048，欢迎大家加入2048 AI社区

更多推荐

函数调用：AI原生应用领域的核心驱动力

2023年，OpenAI推出Function Call（函数调用）能力，彻底改变了这一局面。AI原生应用（AI-Native App）的本质，就是以大模型为大脑，通过函数调用连接现实世界的工具/数据，实现“意图理解→任务执行→结果反馈”的完整闭环。用户需求：“我明天要去北京出差，帮我查天气+订酒店”；AI的行动：理解意图：需要调用“天气查询”和“酒店预订”两个工具；函数调用：先调用get_weat