十二、动态SQL
什么是动态SQL：动态SQL就是根据不同的条件生成不同的SQL语句
动态 SQL 是 MyBatis 的强大特性之一。如果你使用过 JDBC 或其它类似的框架，你应该能理解根据不同条件拼接 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
2. 创建一个基础工程
2.1导包
<dependencies> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <version>1.18.20</version> </dependency> </dependencies>
2.2 编写配置文件
mybaits-config.xml和db.properties
2.3 编写实体类
import lombok.Data; import java.util.Date; @Data public class Blog { private String id; private String title; private String author; private Date createTime;//属性名和字段名不一致 private int views; }
2.4 编写实体类对应的Mapper接口和Mapper.xml文件
BlogMapper和BlogMapper.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"/> <settings> <!--标准的日志工厂实现--> <setting name="logImpl" value="STDOUT_LOGGING"/> <setting name="mapUnderscoreToCamelCase" value="true"/> </settings> <!--可以给实体类起别名--> <typeAliases> <package name="com.hxl.pojo"/> </typeAliases> <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 class="com.hxl.dao.BlogMapper"/> </mappers> </configuration>
2.5 生成UUID的工具类
import java.util.UUID; @SuppressWarnings("all")//抑制警告 public class IDutils { public static String getId(){ return UUID.randomUUID().toString().replaceAll("-",""); } }
2.6 测试加入数据
public interface BlogMapper { //插入数据 int addBlog(Blog blog); }
<?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.hxl.dao.BlogMapper"> <insert id="addBlog" parameterType="blog"> insert into blog (id,title,author,create_time,views) values (#{id},#{title},#{author},#{createTime},#{views}); </insert> </mapper>
import com.hxl.dao.BlogMapper; import com.hxl.pojo.Blog; import com.hxl.utils.IDutils; import com.hxl.utils.MybatisUtils; import org.apache.ibatis.session.SqlSession; import org.junit.Test; import java.util.Date; public class MyTest { @Test public void addInitBlog(){ 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("Spring"); mapper.addBlog(blog); blog.setId(IDutils.getId()); blog.setTitle("微服务"); mapper.addBlog(blog); sqlSession.close(); } }
- 出现了问题
他说是无效绑定。
这里需要绑定，但是我是绑定了的，所以又进行了下面的操作
- 在pom文件下加入下面的话既可以解决，加入后记得刷新maven
<build> <resources> <resource> <directory>src/main/resources</directory> <includes> <include>**/*.properties</include> <include>**/*.xml</include> </includes> <filtering>true</filtering> </resource> <resource> <directory>src/main/java</directory> <includes> <include>**/*.properties</include> <include>**/*.xml</include> </includes> </resource> <resource> <directory>src/main/resources</directory> <filtering>true</filtering> </resource> </resources> <plugins> <plugin> <groupId>org.apache.maven.plugins</groupId> <artifactId>maven-resources-plugin</artifactId> <configuration> <encoding>UTF-8</encoding> </configuration> </plugin> </plugins> </build>
- 我觉得出现上述的问题是因为我的BlogMapper.xml是放在src/main/java/com/hxl/dao下的。而不是像上面的那个放在resources下的。所以出现了那个问题。
3. if
List<Blog> queryBlogIF(Map map);
<select id="queryBlogIF" parameterType="map" resultType="blog"> select * from blog where 1=1 <if test="title != null"> and title = #{title} </if> <if test="author != null"> and author = #{author} </if> </select>
@Test public void queryBlogIF(){ SqlSession sqlSession = MybatisUtils.getSqlSession(); BlogMapper mapper = sqlSession.getMapper(BlogMapper.class); HashMap map = new HashMap(); map.put("title","Java"); List<Blog> blogs = mapper.queryBlogIF(map); for (Blog blog : blogs) { System.out.println(blog); } sqlSession.close(); }
