什么是动态SQl:动态SQL就是指根据不同的条件生成不同的SQl语句
利用动态SQl这一特性可以彻底摆脱这种痛苦。
使用动态 SQL 并非一件易事,但借助可用于任何 SQL 映射语句中的强大的动态 SQL 语言,MyBatis 显著地提升了这一特性的易用性。
如果你之前用过 JSTL 或任何基于类 XML 语言的文本处理器,你对动态 SQL 元素可能会感觉似曾相识。在 MyBatis 之前的版本中,需要花时间了解大量的元素。借助功能强大的基于 OGNL 的表达式,MyBatis 3 替换了之前的大部分元素,大大精简了元素种类,现在要学习的元素种类比原来的一半还要少。
- if
- choose (when, otherwise)
- trim (where, set)
- foreach
搭建环境
SQl语句:
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;
创建一个基础工程
1.导包
<dependencies> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <version>1.18.24</version> </dependency> </dependencies>
2.编写配置文件
db.properties文件:
driver=com.mysql.cj.jdbc.Driver url=jdbc:mysql://localhost:3306/mybatis?useSSL=true&useUnicode=true&characterEncoding=UTF-8 username=root password=123456
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> <!--引入外部配置文件 --> <properties resource="db.properties"> <property name="username" value="root"/> <property name="password" value="123456"/> </properties><!--标准的日志工厂实现--> <settings> <setting name="logImpl" value="STDOUT_LOGGING"/> </settings> <environments default="development" > <environment id="development"> <transactionManager type="JDBC"/> <dataSource type="POOLED"> <property name="driver" value="${driver}"/> <property name="url" value="${url}"/> <property name="username" value="${username}"/> <property name="password" value="${password}"/> </dataSource> </environment> </environments>
<mappers> <mapper resource="com/xu/dao/BlogMapper.xml"/> </mappers>
</configuration>
3.编写实体类
@Data public class Blog { private int id; private String title; private String author; private Date createTime; private int views; }
4.编写实体类对应Mapper接口和Mapper.xml文件
Mapper接口:
package com.xu.dao; import com.xu.pojo.Blog; public interface BlogMapper { //插入数据 int addBlog(Blog blog); }
Mapper.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.xu.dao.BlogMapper"> <insert id="addBlog" parameterType="com.xu.pojo.Blog"> insert into blog (id,title,author,create_time,views) values (#{id},#{title},#{author},#{createTime},#{views}); </insert> </mapper>
Utils工具类:
IDutils生成id类
package com.xu.utils; import org.junit.Test; import java.util.UUID; @SuppressWarnings("all")//抑制警告 public class IDutils { public static String getId(){ return UUID.randomUUID().toString().replaceAll("-",""); } @Test public void test(){ System.out.println(IDutils.getId()); System.out.println(IDutils.getId()); System.out.println(IDutils.getId()); System.out.println(IDutils.getId()); } }
MybatisUtils类
package com.xu.utils; 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; //使用mybatis第一部获取sqlSessionFactory对象 public class MybatisUtils { private static SqlSessionFactory sqlSessionFactory; static { try { String resource = "mybatis-config.xml"; InputStream inputStream = Resources.getResourceAsStream(resource); sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); } catch (IOException e) { e.printStackTrace(); } } //既然有了sqlsessionFactory,顾名思义,我们就可以从中获得sqlsession的实例了 //sqlsession完全包含了面向数据库执行sql命令所需要的方法 public static SqlSession getSqlSession(){ return sqlSessionFactory.openSession(); } }
测试类:
import com.xu.dao.BlogMapper; import com.xu.pojo.Blog; import com.xu.utils.IDutils; import com.xu.utils.MybatisUtils; import org.apache.ibatis.session.SqlSession; import org.junit.Test; import java.util.Date; public class Mytest { @Test public void addBlogTest() { SqlSession sqlSession = MybatisUtils.getSqlSession(); BlogMapper mapper = sqlSession.getMapper(BlogMapper.class); Blog blog = new Blog(); blog.setId(IDutils.getId()); blog.setTitle("Mybatis如此简单"); blog.setAuthor("狂神说"); blog.setCreateTime(new Date()); blog.setViews(9999); mapper.addBlog(blog); blog.setId(IDutils.getId()); blog.setTitle("Java如此简单"); mapper.addBlog(blog); blog.setId(IDutils.getId()); blog.setTitle("Spring666"); mapper.addBlog(blog); blog.setId(IDutils.getId()); blog.setTitle("微服务666"); mapper.addBlog(blog); sqlSession.commit(); sqlSession.close(); } }