<select id="selectUserByUsernameAndSex" resultType="user" parameterType="com.ys.po.User"> <!-- 这里和普通的sql 查询语句差不多,对于只有一个参数,后面的 #{id}表示占位符,里面 不一定要写id, 写啥都可以,但是不要空着,如果有多个参数则必须写pojo类里面的属性 --> select * from user where username=#{username} and sex=#{sex} </select>if 语句使用方法简单,常常与 test 属性联合使用。语法如下:
<if test="判断条件"> SQL语句</if>
<select id="selectAllWebsite" resultMap="myResult"> select id,name,url from website where 1=1 <if test="name != null"> AND name like #{name} </if> <if test="url!= null"> AND url like #{url} </if> </select>2.where+if标签
<select id="findQuery" resultType="Student"> <include refid="selectvp"/> <where> <if test="sacc != null"> sacc like concat('%' #{sacc} '%') </if> <if test="sname != null"> AND sname like concat('%' #{sname} '%') </if> <if test="sex != null"> AND sex=#{sex} </if> <if test="phone != null"> AND phone=#{phone} </if> </where> </select>这个“where”标签会知道如果它包含的标签中有返回值的话,它就插入一个‘where’。此外,如果标签返回的内容是以AND 或OR 开头的,则它会剔除掉。
<update id="upd"> update student <set> <if test="sname != null">sname=#{sname},</if> <if test="spwd != null">spwd=#{spwd},</if> <if test="sex != null">sex=#{sex},</if> <if test="phone != null">phone=#{phone}</if> sid=#{sid} </set> where sid=#{sid} </update>4.choose(when,otherwise) 语句
# 堆代码 duidaima.com <select id="selectUserByChoose" resultType="com.ys.po.User" parameterType="com.ys.po.User"> select * from user <where> <choose> <when test="id !='' and id != null"> id=#{id} </when> <when test="username !='' and username != null"> and username=#{username} </when> <otherwise> and sex=#{sex} </otherwise> </choose> </where> </select>也就是说,这里我们有三个条件,id、username、sex,只能选择一个作为查询条件。
如果 username 为空,那么查询语句为 select * from user where sex=?
<select id="selectUserByUsernameAndSex" resultType="user" parameterType="com.ys.po.User"> select * from user <!-- <where> <if test="username != null"> username=#{username} </if> <if test="username != null"> and sex=#{sex} </if> </where> --> <trim prefix="where" prefixOverrides="and | or"> <if test="username != null"> and username=#{username} </if> <if test="sex != null"> and sex=#{sex} </if> </trim> </select>prefix:前缀
<!-- 根据 id 更新 user 表的数据 --> <update id="updateUserById" parameterType="com.ys.po.User"> update user u <!-- <set> <if test="username != null and username != ''"> u.username = #{username}, </if> <if test="sex != null and sex != ''"> u.sex = #{sex} </if> </set> --> <trim prefix="set" suffixOverrides=","> <if test="username != null and username != ''"> u.username = #{username}, </if> <if test="sex != null and sex != ''"> u.sex = #{sex}, </if> </trim> where id=#{id} </update>suffix:后缀
<insert id="add"> insert into student <trim prefix="(" suffix=")" suffixOverrides=","> <if test="sname != null">sname,</if> <if test="spwd != null">spwd,</if> <if test="sex != null">sex,</if> <if test="phone != null">phone,</if> </trim> <trim prefix="values (" suffix=")" suffixOverrides=","> <if test="sname != null">#{sname},</if> <if test="spwd != null">#{spwd},</if> <if test="sex != null">#{sex},</if> <if test="phone != null">#{phone}</if> </trim> </insert>6.MyBatis foreach标签
//堆代码 duidaima.com //批量查询 <select id="findAll" resultType="Student" parameterType="Integer"> <include refid="selectvp"/> WHERE sid in <foreach item="ids" collection="array" open="(" separator="," close=")"> #{ids} </foreach> </select> //批量删除 <delete id="del" parameterType="Integer"> delete from student where sid in <foreach item="ids" collection="array" open="(" separator="," close=")"> #{ids} </foreach> </delete>整合案例
<?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.yzx.mapper.StuMapper"> <sql id="selectvp"> select * from student </sql> <select id="find" resultType="Student"> <include refid="selectvp"/> </select> <select id="findbyid" resultType="student"> <include refid="selectvp"/> WHERE 1=1 <if test="sid != null"> AND sid like #{sid} </if> </select> <select id="findQuery" resultType="Student"> <include refid="selectvp"/> <where> <if test="sacc != null"> sacc like concat('%' #{sacc} '%') </if> <if test="sname != null"> AND sname like concat('%' #{sname} '%') </if> <if test="sex != null"> AND sex=#{sex} </if> <if test="phone != null"> AND phone=#{phone} </if> </where> </select> <update id="upd"> update student <set> <if test="sname != null">sname=#{sname},</if> <if test="spwd != null">spwd=#{spwd},</if> <if test="sex != null">sex=#{sex},</if> <if test="phone != null">phone=#{phone}</if> sid=#{sid} </set> where sid=#{sid} </update> <insert id="add"> insert into student <trim prefix="(" suffix=")" suffixOverrides=","> <if test="sname != null">sname,</if> <if test="spwd != null">spwd,</if> <if test="sex != null">sex,</if> <if test="phone != null">phone,</if> </trim> <trim prefix="values (" suffix=")" suffixOverrides=","> <if test="sname != null">#{sname},</if> <if test="spwd != null">#{spwd},</if> <if test="sex != null">#{sex},</if> <if test="phone != null">#{phone}</if> </trim> </insert> <select id="findAll" resultType="Student" parameterType="Integer"> <include refid="selectvp"/> WHERE sid in <foreach item="ids" collection="array" open="(" separator="," close=")"> #{ids} </foreach> </select> <delete id="del" parameterType="Integer"> delete from student where sid in <foreach item="ids" collection="array" open="(" separator="," close=")"> #{ids} </foreach> </delete> </mapper>测试类:
package com.yzx.test; import com.yzx.entity.Student; import com.yzx.mapper.StuMapper; 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 org.junit.After; import org.junit.Before; import org.junit.Test; import java.io.IOException; import java.io.InputStream; import java.util.List; public class StuTest { SqlSession sqlSession=null; InputStream is=null; @Before public void before() throws IOException { //1.读取核心配置文件 is= Resources.getResourceAsStream("sqlMapperConfig.xml"); //2.拿到工厂构建类 SqlSessionFactoryBuilder sqlSessionFactoryBuilder=new SqlSessionFactoryBuilder(); //3.拿到具体工厂 SqlSessionFactory build=sqlSessionFactoryBuilder.build(is); //4.拿到session sqlSession = build.openSession(); } @After public void after(){ //7,提交事务 sqlSession.commit(); //8.关闭资源 sqlSession.close(); if(is!=null){ try { is.close(); } catch (IOException e) { e.printStackTrace(); } }; } //查询所有 @Test public void find(){ //5.获取具体的mapper接口 StuMapper mapper=sqlSession.getMapper(StuMapper.class); //6.调用执行 List<Student> list=mapper.find(); list.forEach(a-> System.out.println(a)); } //查询单个 @Test public void findbyid(){ StuMapper mapper=sqlSession.getMapper(StuMapper.class); List<Student> list=mapper.findbyid(2); list.forEach(a-> System.out.println(a)); } //模糊查询 @Test public void findQuery(){ StuMapper mapper=sqlSession.getMapper(StuMapper.class); Student stu=new Student(); stu.setSname("小"); stu.setSex("男"); List<Student> list=mapper.findQuery(stu); list.forEach(a-> System.out.println(a)); } //修改 @Test public void upd(){ StuMapper mapper=sqlSession.getMapper(StuMapper.class); Student stu=new Student(); stu.setSid(3); stu.setSname("小若"); stu.setSex("人妖"); int i=mapper.upd(stu); System.out.println("修改了"+i+"条数据"+" "+stu.toString()); } //添加 @Test public void add(){ StuMapper mapper=sqlSession.getMapper(StuMapper.class); Student stu=new Student(); stu.setSname("小贺"); stu.setSex("男"); stu.setPhone("99999999"); int i=mapper.add(stu); System.out.println("添加了"+i+"条数据"+" "+stu.toString()); } //批量操作 @Test public void findAll(){ StuMapper mapper=sqlSession.getMapper(StuMapper.class); Integer[] i={1,2,3,4}; List<Student> list=mapper.findAll(i); list.forEach(a-> System.out.println(a)); } //批量操作 //批量删除 @Test public void del(){ StuMapper mapper=sqlSession.getMapper(StuMapper.class); Integer[] i={1,2,3,4}; int i1=mapper.del(i); System.out.println("删除了"+i1+"条数据"); } }
<sql id="selectvp"> select * from student </sql>
<select id="findbyid" resultType="student"> <include refid="selectvp"/> WHERE 1=1 <if test="sid != null"> AND sid like #{sid} </if> </select>9.如何引用其他XML中的SQL片段
<sql id="Base_Column_List"> ID,MAJOR,BIRTHDAY,AGE,NAME,HOBBY</sql>此时我在com.xxx.dao.PatinetMapper中的XML文件中需要引用,如下:
<include refid="com.xxx.dao.xxMapper.Base_Column_List"></include>三MyBatis关联查询
<!--一对多--> <resultMap id="myStudent1" type="student1"> <id property="sid" column="sid"/> <result property="sname" column="sname"/> <result property="sex" column="sex"/> <result property="sage" column="sage"/> <collection property="list" ofType="teacher"> <id property="tid" column="tid"/> <result property="tname" column="tname"/> <result property="tage" column="tage"/> </collection> </resultMap> <!--一对多--> <select id="find1" resultMap="myStudent1"> select * from student1 s left join teacher t on s.sid=t.sid </select>2.MyBatis多对一关联查询
<!--多对一--> <resultMap id="myTeacher" type="teacher"> <id property="tid" column="tid"/> <result property="tname" column="tname"/> <result property="tage" column="tage"/> <association property="student1" javaType="Student1"> <id property="sid" column="sid"/> <result property="sname" column="sname"/> <result property="sex" column="sex"/> <result property="sage" column="sage"/> </association> </resultMap> <!--多对一--> <select id="find2" resultMap="myTeacher"> select * from teacher t right join student1 s on t.sid=s.sid </select>3.MyBatis多对多关联查询
<!--多对多 以谁为主表查询的时候,主表约等于1的一方,另一方相当于多的一方--> <select id="find3" resultMap="myStudent1"> select * from student1 s left join relevance r on s.sid=r.sid left join teacher t on r.tid=t.tid </select>