11.动态SQL
创建一个基础工程：
1.导包
2.编写配置文件
3.编写一个实体类
4.编写实体类对应的Mapper以及Mapper.xml文件
解决驼峰命名，例如数据库字段为create_time而实体类中写的是createTime这种形式，在settings中设置为true.
配置文件
<?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> <settings> <setting name="mapUnderscoreToCamelCase" value="true"/> </settings> <environments default="development"> <environment id="development"> <transactionManager type="JDBC"/> <dataSource type="POOLED"> <property name="driver" value="com.mysql.jdbc.Driver"/> <property name="url" value="jdbc:mysql://localhost:3306/mybatis?useSSl=true&useUnicode=true&characterEncoding=utf-8"/> <property name="username" value="root"/> <property name="password" value="123456"/> </dataSource> </environment> </environments> <mappers> <mapper class="dao.BlogMapper"></mapper> </mappers> </configuration>
BlogMapper文件
package dao; import pojo.Blog; public interface BlogMapper { 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="dao.BlogMapper"> <insert id="addBlog" parameterType="dao.BlogMapper"> insert into mybatis.blog (id,title,author,create_time,views)values (#{id},#{title},#{author},#{createTime},#{views}) </insert> </mapper>
测试文件
import dao.BlogMapper; import org.apache.ibatis.session.SqlSession; import org.junit.Test; import pojo.Blog; import utils.IDutils; import utils.MyBatisUtil; import java.util.Date; public class MyTest { @Test public void addBlog(){ SqlSession sqlSession = MyBatisUtil.getSqlSession(); BlogMapper mapper = sqlSession.getMapper(BlogMapper.class); Blog blog=new Blog(); blog.setId(IDutils.getId()); blog.setTitle("一日归属吉大，一生念念不忘"); 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("python学习很简单"); mapper.addBlog(blog); sqlSession.commit(); sqlSession.close(); } }
11.1 动态SQL之IF语句
<select id="queryBlogIf" parameterType="map" resultType="pojo.Blog"> select * from mybatis.blog where 1=1 <if test="title!=null"> and title=#{title} </if> <if test="author!=null"> and author=#{author} </if> </select>
如果没有where 1=1则程序会报错，此种错误的解决方式是加一个where标签。如下：
<select id="queryBlogIf" parameterType="map" resultType="pojo.Blog"> select * from mybatis.blog <where> <if test="title!=null"> and title=#{title} </if> <if test="author!=null"> author=#{author} </if> </where> </select>
11.2trim(where和set)
<select id="queryBlogIf" parameterType="map" resultType="pojo.Blog"> select * from mybatis.blog <where> <if test="title!=null"> and title=#{title} </if> <if test="author!=null"> author=#{author} </if> </where> </select>
11.3 SQL片段
1.使用SQL片段提取公共部分。
2.在需要使用的地方通过include的标签引用即可。
<sql id="if-title-author"> <if test="title != null"> title = #{title} </if> <if test="author != null"> and author = #{author} </if> </sql> <select id="queryBlogIF" parameterType="map" resultType="blog"> select * from mybatis.blog <where> <include refid="if-title-author"></include> </where> </select>
开启别名的Settings的设置：
