真理并无尽头

动态SQL环境搭建

HeJin大约 3 分钟数据库技术Mybatis

1、什么是动态SQL

https://mybatis.org/mybatis-3/zh/dynamic-sql.htmlopen in new window

动态 SQL 是 MyBatis 的强大特性之一。如果你使用过 JDBC 或其它类似的框架，你应该能理解根据不同条件拼接 SQL 语句有多痛苦，例如拼接时要确保不能忘记添加必要的空格，还要注意去掉列表最后一个列名的逗号。利用动态 SQL，可以彻底摆脱这种痛苦。

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

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

动态SQL是指根据不同的条件生成不同的SQL语句。

2、创建表

CREATE TABLE `blog`(
`id` VARCHAR(50) NOT NULL COMMENT '博客id',
`title` VARCHAR(100) NOT NULL COMMENT '博客标题',
`author` VARCHAR(30) NOT NULL COMMENT '博客作者',
`create_time` DATETIME NOT NULL COMMENT '创建时间',
`views` INT(30) NOT NULL COMMENT '浏览量'
)ENGINE=INNODB DEFAULT CHARSET=utf8

3、实体类

Blog.java

package com.kuang.pojo;

import lombok.Data;

import java.util.Date;

/**
 * @Description TODO
 * @Author Administrator
 * @Date 2020/11/29 15:57
 */
@Data
public class Blog {
    private String id;
    private String title;
    private String author;
    private Date createTime;
    private int views;
}

4、接口类及mapper文件

BlogMapper.java

package com.kuang.dao;

import com.kuang.pojo.Blog;

/**
 * @Description TODO
 * @Author Administrator
 * @Date 2020/11/29 16:00
 */
public interface BlogMapper {

    /**
     * 插入数据
     * @param blog
     * @return
     */
    int addBlog(Blog blog);

}

BlogMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--核心配置文件-->
<mapper namespace="com.kuang.dao.BlogMapper">

    <insert id="addBlog" parameterType="blog">
        insert into blog (id,title,author,create_time,views)
        values (#{id},#{title},#{author},#{createTime},#{views})
    </insert>

</mapper>

5、新增工具类IdUtils.java

IdUtils.java

package com.kuang.utils;

import java.util.UUID;

/**
 * @Description TODO
 * @Author Administrator
 * @Date 2020/11/29 16:04
 */
public class IdUtils {

    public static String getId(){
        return UUID.randomUUID().toString().replaceAll("-","");
    }

}

6、mybatis核心配置文件修改

<!--设置-->
<settings>
    <setting name="logImpl" value="STDOUT_LOGGING"/>
    <!--数据库下划线与Java驼峰命名自动转换开启-->
    <setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>

7、插入数据

/**
 * @Description TODO
 * @Author Administrator
 * @Date 2020/11/29 16:21
 */
public class MyTest {

    @Test
    public void test01(){

        SqlSession sqlSession = MybatisUtils.getSqlSession();
        BlogMapper blogMapper = sqlSession.getMapper(BlogMapper.class);

        Blog blog = new Blog();
        blog.setId(IdUtils.getId());
        blog.setTitle("Mybatis如此简单");
        blog.setAuthor("狂神说");
        blog.setCreateTime(new Date());
        blog.setViews(99999);
        blogMapper.addBlog(blog);

        blog.setId(IdUtils.getId());
        blog.setTitle("Java如此简单");
        blogMapper.addBlog(blog);

        blog.setId(IdUtils.getId());
        blog.setTitle("Spring如此简单");
        blogMapper.addBlog(blog);

        blog.setId(IdUtils.getId());
        blog.setTitle("微服务如此简单");
        blogMapper.addBlog(blog);

        sqlSession.close();

    }

}

结果

Logging initialized using 'class org.apache.ibatis.logging.stdout.StdOutImpl' adapter.
Class not found: org.jboss.vfs.VFS
JBoss 6 VFS API is not available in this environment.
Class not found: org.jboss.vfs.VirtualFile
VFS implementation org.apache.ibatis.io.JBoss6VFS is not valid in this environment.
Using VFS adapter org.apache.ibatis.io.DefaultVFS
Find JAR URL: file:/E:/code/Java/mybatis-study/mybatis-07/target/classes/com/kuang/pojo
Not a JAR: file:/E:/code/Java/mybatis-study/mybatis-07/target/classes/com/kuang/pojo
Reader entry: Blog.class
Listing file:/E:/code/Java/mybatis-study/mybatis-07/target/classes/com/kuang/pojo
Find JAR URL: file:/E:/code/Java/mybatis-study/mybatis-07/target/classes/com/kuang/pojo/Blog.class
Not a JAR: file:/E:/code/Java/mybatis-study/mybatis-07/target/classes/com/kuang/pojo/Blog.class
Reader entry: ����   4 v
Checking to see if class com.kuang.pojo.Blog matches criteria [is assignable to Object]
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
Opening JDBC Connection
Created connection 1026871825.
==>  Preparing: insert into blog (id,title,author,create_time,views) values (?,?,?,?,?) 
==> Parameters: a4a36cb8722b492e96919a64dae72ff5(String), Mybatis如此简单(String), 狂神说(String), 2020-11-29 16:33:23.411(Timestamp), 99999(Integer)
<==    Updates: 1
==>  Preparing: insert into blog (id,title,author,create_time,views) values (?,?,?,?,?) 
==> Parameters: a230ab751c8b4a7f9e2072bf9d6c18e1(String), Java如此简单(String), 狂神说(String), 2020-11-29 16:33:23.411(Timestamp), 99999(Integer)
<==    Updates: 1
==>  Preparing: insert into blog (id,title,author,create_time,views) values (?,?,?,?,?) 
==> Parameters: f1d89e894ff64098be569769e62e82f9(String), Spring如此简单(String), 狂神说(String), 2020-11-29 16:33:23.411(Timestamp), 99999(Integer)
<==    Updates: 1
==>  Preparing: insert into blog (id,title,author,create_time,views) values (?,?,?,?,?) 
==> Parameters: ab9f312552964bb39500dda06ca2ae61(String), 微服务如此简单(String), 狂神说(String), 2020-11-29 16:33:23.411(Timestamp), 99999(Integer)
<==    Updates: 1
Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@3d34d211]
Returned connection 1026871825 to pool.

Process finished with exit code 0
Copyright © 2023 HeJin