Mybatis动态SQL
前提环境搭建
1.在数据库中创建表
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; insert into blog values(1,"mybatis","chen","2022-03-26 17:17:00","9999"); insert into blog values(2,"spring","chen","2022-03-26 17:17:00","9999"); insert into blog values(3,"springboot","chen","2022-03-26 17:17:00","9999"); insert into blog values(4,"springmvc","chen","2022-03-26 17:17:00","9999");
2.导入相关依赖包
org.mybatis mybatis 3.5.7 junit junit 4.12 test mysql mysql-connector-java 5.1.3 org.projectlombok lombok 1.18.10
3.创建MyBatis的核心配置文件
先配置一个数据库配置文件db.properties，配置文件存放的位置是src/main/resources目录下
driver=com.mysql.jdbc.Driver url=jdbc:mysql://192.168.111.131:3306/mybatis?characterEncoding=UTF-8 username=root password=123456
然后创建MyBatis核心配置文件，习惯上命名为mybatis-config.xml，核心配置文件存放的位置是src/main/resources目录下
<?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"/> <settings> <setting name="mapUnderscoreToCamelCase" value="true"/> 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="mappers/BlogMapper.xml"/> mappers> configuration>
4.编写Mybatis工具类
public class MybatisUtils { private static SqlSessionFactory sqlSessionFactory; static { try { //读取MyBatis的核心配置文件 InputStream is = Resources.getResourceAsStream("mybatis-config.xml"); //创建SqlSessionFactoryBuilder对象 SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder(); //通过核心配置文件所对应的字节输入流创建工厂类SqlSessionFactory，生产SqlSession对象 sqlSessionFactory = sqlSessionFactoryBuilder.build(is); } catch (IOException e) { e.printStackTrace(); } } public static SqlSession getSqlSession(){ //参数true为设置自动提交 return sqlSessionFactory.openSession(true); } }
动态SQL
1.创建实体类
该地方我们使用的是Lombok配置实体类，可以通过对其进行了解
@Data public class Blog { private String id; private String title; private String author; private Date createTime; private long views; }
2.创建Mapper接口
MyBatis中的mapper接口相当于以前的dao。但是区别在于，mapper仅仅是接口，我们不需要提供实现类。
public interface BlogMapper { //动态SQL之IF:查询博客 ListqueryBlogIF(Map map); //动态SQL之Choose，相当于Java中的switch:查询博客 List queryBlogChoose(Map map); //动态SQL之set:更新博客 int updateBlog(Map map); //动态SQL之Foreach:通过Id动态查询博客 List queryBlogForeach(Map map); }
3.创建MyBatis的映射文件
注：mapper接口的全类名和映射文件中的命名空间（namespace）保持一致。
<?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.chen.mybatis.mapper.BlogMapper"> <select id="queryBlogIF" parameterType="map" resultType="com.chen.mybatis.pojo.Blog"> select * from blog <where> <if test="title != null"> and title = #{title} if> <if test="author != null"> and author = #{author} if> where> select> <select id="queryBlogChoose" parameterType="map" resultType="com.chen.mybatis.pojo.Blog"> select * from blog <where> <choose> <when test="title != null"> title = #{title} when> <when test="author != null"> and author = #{author} when> <otherwise> and views = #{views} otherwise> choose> where> select> <update id="updateBlog" parameterType="map"> update blog <set> <if test="title != null"> title = #{title}, if> <if test="author != null"> author = #{author} if> set> where id = #{id} update> <select id="queryBlogForeach" parameterType="map" resultType="com.chen.mybatis.pojo.Blog"> select * from blog <where> <foreach collection="ids" item="id" open="(" close=")" separator="or"> id = #{id} foreach> where> select> mapper>
4.测试
public class MybatisTest { //动态SQL之IF @Test public void queryBlogIF(){ SqlSession sqlSession = MybatisUtils.getSqlSession(); BlogMapper blogMapper = sqlSession.getMapper(BlogMapper.class); HashMap map = new HashMap(); map.put("title","spring"); Listblogs = blogMapper.queryBlogIF(map); for(Blog blog:blogs){ System.out.println(blog); } sqlSession.close(); } //动态SQL之Choose，相当于Java中的switch @Test public void queryBlogChoose(){ SqlSession sqlSession = MybatisUtils.getSqlSession(); BlogMapper blogMapper = sqlSession.getMapper(BlogMapper.class); HashMap map = new HashMap(); map.put("title","mybatis"); //map.put("views",9999); List blogs = blogMapper.queryBlogChoose(map); for(Blog blog:blogs){ System.out.println(blog); } sqlSession.close(); } //动态SQL之Set @Test public void updateBlog(){ SqlSession sqlSession = MybatisUtils.getSqlSession(); BlogMapper blogMapper = sqlSession.getMapper(BlogMapper.class); HashMap map = new HashMap(); map.put("id",1); map.put("title","mybatis1"); map.put("author","wang"); int res = blogMapper.updateBlog(map); System.out.println(res); sqlSession.close(); } //动态SQL之Foreach @Test public void queryBlogForeach(){ SqlSession sqlSession = MybatisUtils.getSqlSession(); BlogMapper blogMapper = sqlSession.getMapper(BlogMapper.class); HashMap map = new HashMap(); ArrayList ids = new ArrayList (); ids.add(1); ids.add(2); map.put("ids",ids); List blogs = blogMapper.queryBlogForeach(map); for(Blog blog:blogs){ System.out.println(blog); } sqlSession.close(); } }
该文档来自狂神说Java
b站视频链接https://www.bilibili.com/video/BV1NE411Q7Nx?p=25