MyBatis - 06映射文件深入

Posted on 2020-11-24 08:25 Kingdomer 阅读(129) 评论(0) 编辑收藏举报

MyBatis - 06映射文件深入

（1）动态sql语句

（1.1）动态sql语句概述（来自官网）

动态 SQL 是 MyBatis 的强大特性之一。如果你使用过 JDBC 或其它类似的框架，你应该能理解根据不同条件拼接 SQL 语句有多痛苦，

例如拼接时要确保不能忘记添加必要的空格，还要注意去掉列表最后一个列名的逗号。利用动态 SQL，可以彻底摆脱这种痛苦。

使用动态 SQL 并非一件易事，但借助可用于任何 SQL 映射语句中的强大的动态 SQL 语言，MyBatis 显著地提升了这一特性的易用性。

如果你之前用过 JSTL 或任何基于类 XML 语言的文本处理器，你对动态 SQL 元素可能会感觉似曾相识。在 MyBatis 之前的版本中，需要花时间了解大量的元素。借助功能强大的基于 OGNL 的表达式，MyBatis 3 替换了之前的大部分元素，大大精简了元素种类，现在要学习的元素种类比原来的一半还要少。

if
choose (when, otherwise)
trim (where, set)
foreach

工程中加入日志配置文件log4j.properties

# 全局日志配置
log4j.rootLogger=info, stdout
# MyBatis 日志配置
log4j.logger.com.bearpx.spring.mybatis.dao.UserMapper=TRACE
# 控制台输出
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.Target=System.out
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%d{ABSOLUTE} %5p %c[1]:%L [%t] - %m%n

log4j.appender.file=org.apache.log4j.FileAppender
log4j.appender.file.Target=e:/app/mybatis.log
log4j.appender.file.layout=org.apache.log4j.PatternLayout
log4j.appender.file.layout.ConversionPattern=%d{ABSOLUTE} %5p %c[1]:%L [%t] - %m%n

（1.2）动态SQL之<if>

根据实体类的不同取值，使用不同的SQL语句进行查询。

<select id="findByCondition" resultType="user" parameterType="user">
    select * from user where 1=1   // 为了保证id为空时，and 不会被添加到where 后面，加上了 1=1
    <if test="id != null">
        and id=#{id}
    </if>
    <if test="username != null">
        and username=#{username}
    </if>
    <if test="password != null">
        and password=#{password}
    </if>
</select>

使用<where> 代替掉 1=1

    <select id="findByCondition" resultType="user" parameterType="user">
        <include refid="selectUser"></include>
        <where>
            <if test="id != 0">
                and id=#{id}
            </if>
            <if test="username != null">
                and username=#{username}
            </if>
            <if test="password != null">
                and password=#{password}
            </if>
        </where>
    </select>

public interface UserMapper {
    public List<User> findByCondition(User user);
}

    @Test
    public void testCondition() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        User user = new User();
//        user.setId(1);
        user.setUsername("zhangsan");
        user.setPassword("123");
        List<User> userList = userMapper.findByCondition(user);
        System.out.println(userList);
        // 释放资源
        sqlSession.close();
    }

07:24:46,395 DEBUG com.bearpx.spring.mybatis.dao.UserMapper.findByCondition[1]:159 [main] - ==>  Preparing: select * from user WHERE username=? and password=? 
07:24:46,419 DEBUG com.bearpx.spring.mybatis.dao.UserMapper.findByCondition[1]:159 [main] - ==> Parameters: zhangsan(String), 123(String)
07:24:46,441 TRACE com.bearpx.spring.mybatis.dao.UserMapper.findByCondition[1]:165 [main] - <==    Columns: id, username, password, birthday
07:24:46,442 TRACE com.bearpx.spring.mybatis.dao.UserMapper.findByCondition[1]:165 [main] - <==        Row: 1, zhangsan, 123, 0
07:24:46,444 DEBUG com.bearpx.spring.mybatis.dao.UserMapper.findByCondition[1]:159 [main] - <==      Total: 1
[User{id=1, username='zhangsan', password='123', birthday=1970-01-01}]

（1.3）动态SQL之<foreach>

循环执行sql的拼接操作，例如： select * from user where id in (1,2,5)

    <select id="findByIds" resultType="user" parameterType="list">
        <include refid="selectUser"></include>
        <where>
            <foreach collection="list" open="id in(" close=")" item="id" separator=",">
                #{id}
            </foreach>
        </where>
    </select>

07:26:16,351 DEBUG com.bearpx.spring.mybatis.dao.UserMapper.findByIds[1]:159 [main] - ==>  Preparing: select * from user WHERE id in( ? , ? ) 
07:26:16,382 DEBUG com.bearpx.spring.mybatis.dao.UserMapper.findByIds[1]:159 [main] - ==> Parameters: 1(Integer), 2(Integer)
07:26:16,401 TRACE com.bearpx.spring.mybatis.dao.UserMapper.findByIds[1]:165 [main] - <==    Columns: id, username, password, birthday
07:26:16,401 TRACE com.bearpx.spring.mybatis.dao.UserMapper.findByIds[1]:165 [main] - <==        Row: 1, zhangsan, 123, 0
07:26:16,403 TRACE com.bearpx.spring.mybatis.dao.UserMapper.findByIds[1]:165 [main] - <==        Row: 2, lisi, 456, 1605357382451
07:26:16,404 DEBUG com.bearpx.spring.mybatis.dao.UserMapper.findByIds[1]:159 [main] - <==      Total: 2
[User{id=1, username='zhangsan', password='123', birthday=1970-01-01}, User{id=2, username='lisi', password='456', birthday=2020-11-14}]

（1.4）SQL片段抽取

sql中将可重复的sql提取出来，使用时用include 引用即可，最终达到sql重用的目的。

    <sql id="selectUser">select * from user</sql>

    <select id="findById" resultType="user" parameterType="int">
        <include refid="selectUser"></include>  where id=#{id}
    </select>

（2）typeHandlers标签

无论是MyBatis在预处理语句（PreparedStatement）中设置一个参数时，还是从结果集中取出一个值时，都会用类型处理器将获取的值以合适的方式转换成java类型。

类型处理器	Java类型	JDBC类型
BooleanTypeHandler	java.lang.Boolean, boolean	数据库兼容的BOOLEAN
ByteTypeHandler	java.lang.Byte, byte	数据库兼容的NUMERIC 或 BYTE
ShortTypeHandler	java.lang.Short, short	数据库兼容的NUMERIC 或 SHORT INTEGER
IntegerTypeHandler	java.lang.Integer, int	数据库兼容的NUMERIC 或 INTEGER
LongTypeHandler	java.lang.Long, long	数据库兼容的NUMERIC 或 LONG INTEGER

可以重写类型处理器或创建自己的类型处理器来处理不支持的或非标准的类型。

具体做法：实现 org.apache.ibatis.type.TypeHandler接口，或继承 org.apache.ibatis.type.BaseTypeHandler，然后选择性地将它映射到一个JDBC类型。

（2.1）需求： Java中的Date数据类型，存到数据库时存成一个1970年至今的毫秒数，取出时转换成java的Date。

public class User {
    private int id;
    private String username;
    private String password;
    private Date birthday;
    // 省略其他代码
}

（2.2）数据库表： birthday 定义为bigint

（2.3）未进行类型转换前

org.apache.ibatis.exceptions.PersistenceException:
### Error updating database. Cause: java.sql.SQLException: Data truncated for column 'birthday' at row 1
### The error may involve com.bearpx.spring.mybatis.dao.UserMapper.save-Inline
### The error occurred while setting parameters
### SQL: insert into user values(?,?,?,?)
### Cause: java.sql.SQLException: Data truncated for column 'birthday' at row 1

（2.4）继承BaseTypeHandler，覆盖4个未实现的方法，重点方法：setNonNullParameter getNullableResult

public class DateTypeHandler extends BaseTypeHandler<Date> {
    // 将java 类型转换成数据库需要的类型
    public void setNonNullParameter(PreparedStatement preparedStatement, int i, Date date, JdbcType jdbcType) throws SQLException {
        long time = date.getTime();
        preparedStatement.setLong(i,time);
    }

    // 将数据库中类型转换成java 类型 【String参数： 要转换的字段名称】【ResultSet： 查询出的结果集】
    public Date getNullableResult(ResultSet resultSet, String s) throws SQLException {
        long aLong = resultSet.getLong(s);
        Date date = new Date(aLong);
        return date;
    }

    public Date getNullableResult(ResultSet resultSet, int i) throws SQLException {
        long aLong = resultSet.getLong(i);
        Date date = new Date(aLong);
        return date;
    }

    public Date getNullableResult(CallableStatement callableStatement, int i) throws SQLException {
        long aLong = callableStatement.getLong(i);
        Date date = new Date(aLong);
        return date;
    }
}

（2.5）在MyBatis核心配置文件中注册

    <typeHandlers>
        <typeHandler handler="com.bearpx.spring.mybatis.handler.DateTypeHandler"/>
    </typeHandlers>

（2.6）测试

    @Test
    public void testSave() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        User user = new User();
        user.setUsername("sunwukong");
        user.setPassword("2121");
        user.setBirthday(new Date(System.currentTimeMillis()));
        userMapper.save(user);
        // 释放资源
        sqlSession.commit();
        sqlSession.close();

    }

执行查询语句：

[User{id=1, username='zhangsan', password='123', birthday=1970-01-01}, User{id=2, username='lisi', password='456', birthday=2020-11-14}]

（3）plugins标签

MyBatis可以使用第三方插件来对功能进行扩展。分页助手PageHelper是将分页的复杂操作进行封装。使用简单的方式即可获得分页的相关数据。

（3.1）导入坐标

        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper</artifactId>
            <version>5.1.8</version>
        </dependency>
        <dependency>
            <groupId>com.github.jsqlparser</groupId>
            <artifactId>jsqlparser</artifactId>
            <version>1.2</version>
        </dependency>

（3.2）在MyBatis核心配置文件中配置插件

    <plugins>
        <plugin interceptor="com.github.pagehelper.PageInterceptor">
            <!--<property name="dialect" value="mysql"/>-->
        </plugin>
    </plugins>

（3.3）测试（映射文件不需要变动）

    <select id="findAll" resultType="user">
        select * from user
    </select>

    @Test
    public void test1() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        // 设置分页相关参数， 当前页 +  每页显示的条数
        PageHelper.startPage(2,3);

        List<User> userList = userMapper.findAll();
        for(User user:userList){
            System.out.println(user);
        }

        PageInfo<User> pageInfo = new PageInfo<User>(userList);
        System.out.println("当前页：" + pageInfo.getPageNum());
        System.out.println("每页显示条数：" + pageInfo.getPageSize());
        System.out.println("总条数："+pageInfo.getTotal());
        System.out.println("总页数："+ pageInfo.getPages());
        System.out.println("上一页："+pageInfo.getPrePage());
        System.out.println("下一页："+pageInfo.getNextPage());
        System.out.println("是否是第一个"+pageInfo.isIsFirstPage());
        System.out.println("是否是最后一个"+ pageInfo.isIsLastPage());
        // 释放资源
        sqlSession.close();

    }

执行结果：

08:35:28,378 DEBUG com.bearpx.mapper.UserMapper.findAll_COUNT[1]:159 [main] - ==>  Preparing: SELECT count(0) FROM user 
08:35:28,428 DEBUG com.bearpx.mapper.UserMapper.findAll_COUNT[1]:159 [main] - ==> Parameters: 
08:35:28,442 TRACE com.bearpx.mapper.UserMapper.findAll_COUNT[1]:165 [main] - <==    Columns: count(0)
08:35:28,442 TRACE com.bearpx.mapper.UserMapper.findAll_COUNT[1]:165 [main] - <==        Row: 6
08:35:28,444 DEBUG com.bearpx.mapper.UserMapper.findAll_COUNT[1]:159 [main] - <==      Total: 1
08:35:28,448 DEBUG com.bearpx.mapper.UserMapper.findAll[1]:159 [main] - ==>  Preparing: select * from user LIMIT ?, ? 
08:35:28,449 DEBUG com.bearpx.mapper.UserMapper.findAll[1]:159 [main] - ==> Parameters: 3(Integer), 3(Integer)
08:35:28,450 TRACE com.bearpx.mapper.UserMapper.findAll[1]:165 [main] - <==    Columns: id, username, password, birthday
08:35:28,450 TRACE com.bearpx.mapper.UserMapper.findAll[1]:165 [main] - <==        Row: 7, sunwukong, 2121, 1605357382451
08:35:28,452 TRACE com.bearpx.mapper.UserMapper.findAll[1]:165 [main] - <==        Row: 8, jerry, 2020, 0
08:35:28,452 TRACE com.bearpx.mapper.UserMapper.findAll[1]:165 [main] - <==        Row: 9, sss, sdsd, 1606177119091
08:35:28,453 DEBUG com.bearpx.mapper.UserMapper.findAll[1]:159 [main] - <==      Total: 3
User{id=7, username='sunwukong', password='2121', birthday=2020-11-14}
User{id=8, username='jerry', password='2020', birthday=1970-01-01}
User{id=9, username='sss', password='sdsd', birthday=2020-11-24}

刷新页面返回顶部

我的运维笔记