动态sql常用的语句 , 有详细内容注解 , 完整的 测试代码 以及 映射文件代码 在最后,如需要完整工程代码可以留言或者发送所要的文件到1172152500@qq.com中,或者留言给我,看到即回复!
1.模糊查询
1)传值之前+%值% 不能加单引号
在映射器中用#{}接收,作用:自动加单引号的作用,防止sql的注入式攻击
测试界面:
Factory factory = null;
@Test
public void selByUsername() {
String sql = "com.qckj.mapper.UserMapper.selByUsername";
SqlSession session = factory.openSession();
List<User> userlist = session.selectList(sql,"%a%");
for (User user : userlist) {
System.out.println(user.getUserid()+","+user.getUsername()+","+user.getPassword());
}
factory.closeSession(session);
}
映射文件:
<sql id="selColumn">userid,username,password</sql>
<select id="selByUsername" parameterType="String" resultType="User">
select
<include refid="selColumn"/>
from
tab_user
where
username like #{username}
</select>
2)前台正常传值,在映射中通过concat连接百分号与传的值
orcale中concat连接只能连接2个值,
mysql中concat可以连接多个参数
测试界面:
Factory factory = null;
@Test
public void selByUsername() {
String sql = "com.qckj.mapper.UserMapper.selByUsername";
SqlSession session = factory.openSession();
List<User> userlist = session.selectList(sql,"a");
for (User user : userlist) {
System.out.println(user.getUserid()+","+user.getUsername()+","+user.getPassword());
}
factory.closeSession(session);
}
映射文件:
<sql id="selColumn">userid,username,password</sql>
<select id="selByUsername" parameterType="String" resultType="User">
select <include refid="selColumn"/> from tab_user where username like concat('%',#{username},'%')</select>
2.sql标签的作用
在第一条中我们会看到有一下<sql></sql>标签
<sql id="selColumn">userid,username,password</sql>
<sql></sql>标签的作用:将常用的sql文里加入在一个映射文件中常用的字段,如以下代码:
<sql id="colum"> <!--id是随便取得名字-->
id,name,password
</sql>
用的时候利用<include />获取字段,如以下代码:
select
<include refid="column"/> <!--refid的值=sql标签中的id值-->
from
tab_user
3.if标签
相当于if的单项判断:如果······ 属性:test中写判断条件,必须有and,邹泽
<if test="userid != null"> <!-- if标签 相当于if··· test中填写判断条件 必须有and 否则sql文错误 -->
and userid = ${userid}
</if>
if test="username != null">
and username like concat('%',#{username},'%')
</if>
4.choose···when ··· otherwise···
表示如果、当当与if···elseif ···else 表示够则或者那么结果···
5. foreach标签
传集合到后台遍历的时候 用foreach 循环遍历 ,有一下属性
collection:接受的集合/数组/map
item:表示集合中的每一个元素进行迭代的时候的别名
open:表示以什么开始
close:表示以什么结束
separator:表示在每次进行迭代之间以···符号作为分隔符
如一下实例:
<foreach collection="list" item="id" open="(" close=")" separator=",">
//接收list集合 以id为别名进行遍历,以括号开始,以括号结束 中间以逗号分隔
内容
</foreach>
6. update - set 标签
set标签可以自动 去除多余的逗号
如映射文件中这样使用:
<!-- =======================update标签、set标签================================= -->
<update id="updUserSet" parameterType="User">
update tab_user
<set>
<if test="username!=null">
username = #{username},,
</if>
<if test="password != null">
password = #{password},
</if>
</set>
where userid = #{userid}
</update>
7. update - trim 标签
trim以set开始,以逗号分隔,效果等同于set标签,同样可以自动去除sql中的多余的逗号
如以下映射文件内容:
<update id="updUserSet2" parameterType="User">
update tab_user
<trim prefix="set" suffixOverrides=",">
<if test="username!=null">
username = #{username},,
</if>
<if test="password != null">
password = #{password},
</if>
</trim>
where userid = #{userid}
</update>
8. select - trim 标签
trim标签以where开始
sql文之间以and分隔开来
如以下映射文件的代码:
<!-- ========================selsct标签 - trim标签 ====================================== -->
<select id="selByTrim" parameterType="User" resultType="User">
select
<include refid="selColumn"/>
from
tab_user
<trim prefix="where" prefixOverrides="and">
<if test="userid != null">
userid = ${userid}
</if>
<if test="username != null">
username like concat('%',#{username},'%')
</if>
</trim>
</select>
9. 比较关系运算符(< 、 > 、!= 、<= 、 >= 等)
有特殊的使用方法 不用会造成错误,比如“<”+">" 会被看成是尖括号
解决办法: <!CDATA[关系式]>
如一下映射文件代码:
<!-- =====================比较运算符运用,<![CDATA[关系式]]>=========================== -->
<select id="selByCDATA" parameterType="int" resultType="User">
select
<include refid="selColumn"/>
from
tab_user
where
<![CDATA[userid > #{id}]]>
</select>
=============================以上完整测试代码==CURDTest.java================================
package com.qckj.test;
import java.util.ArrayList;
import java.util.List;
import org.apache.ibatis.session.SqlSession;
import static org.junit.Assert.*;
import org.junit.Before;
import org.junit.Test;
import com.qckj.factory.Factory;
import com.qckj.pojo.User;
public class CURDTest {
Factory factory = null;
@Test
public void selByUsername() {
//模糊查询a
String sql = "com.qckj.mapper.UserMapper.selByUsername";
SqlSession session = factory.openSession();
//加百分号的第一种方式
//传值之前+ %值% , 不能加单引号 因为user的映射器中用#{ } 接收 自动加单引号的作用,
//mybatis防止sql注入式攻击
// List<User> userlist = session.selectList(sql,"%a%");
List<User> userlist = session.selectList(sql,"a");
for (User user : userlist) {
System.out.println(user.getUserid()+","+user.getUsername()+","+user.getPassword());
}
factory.closeSession(session);
}
@Test
public void selByIf() {
//if标签
String sql = "com.qckj.mapper.UserMapper.selByIf";
SqlSession session = factory.openSession();
User userbean = new User();
userbean.setUserid(2);
// userbean.setUsername("A");
List<User> userlist = session.selectList(sql,userbean);
for (User user : userlist) {
System.out.println(user.getUserid()+","+user.getUsername()+","+user.getPassword());
}
factory.closeSession(session);
}
@Test
public void selByWhere() {
//include提取sql标签中的内容
String sql = "com.qckj.mapper.UserMapper.selByWhere";
SqlSession session = factory.openSession();
User userbean = new User();
userbean.setUserid(1);
// userbean.setUsername("A");
List<User> userlist = session.selectList(sql,userbean);
for (User user : userlist) {
System.out.println(user.getUserid()+","+user.getUsername()+","+user.getPassword());
}
factory.closeSession(session);
}
@Test
public void selByChoose() {
//choose - when - otherwise 如果 - 否则 - 那么-
String sql = "com.qckj.mapper.UserMapper.selByChoose";
SqlSession session = factory.openSession();
User userbean = new User();
// userbean.setUserid(1);
userbean.setOrdercol("userid");
userbean.setOrderad("desc");
// userbean.setUsername("A");
List<User> userlist = session.selectList(sql,userbean);
for (User user : userlist) {
System.out.println(user.getUserid()+","+user.getUsername()+","+user.getPassword());
}
factory.closeSession(session);
}
@Test
public void selByForeach() {
//foreach循环 in范围
String sql = "com.qckj.mapper.UserMapper.selByForeach";
SqlSession session = factory.openSession();
User userbean = new User();
List<Integer> list = new ArrayList<Integer>();
list.add(1);
list.add(3);
list.add(6);
userbean.setIdlist(list);
List<User> userlist = session.selectList(sql,userbean);
for (User user : userlist) {
System.out.println(user.getUserid()+","+user.getUsername()+","+user.getPassword());
}
factory.closeSession(session);
}
@Test
public void updUserSet() {
//修改内容 update标签、set标签
String sql = "com.qckj.mapper.UserMapper.updUserSet";
SqlSession session = factory.openSession();
User userbean = new User();
userbean.setUserid(8);
// userbean.setUsername("wind big");
userbean.setPassword("glass diu");
int affectrow = session.update(sql,userbean);
session.commit();
assertEquals("修改失败", 1,affectrow);
factory.closeSession(session);
}
@Test
public void updUserSet2() {
//update标签、trim标签
String sql = "com.qckj.mapper.UserMapper.updUserSet2";
SqlSession session = factory.openSession();
User userbean = new User();
userbean.setUserid(2);
// userbean.setUsername("wind big");
userbean.setPassword("oooooooolalala");
int affectrow = session.update(sql,userbean);
session.commit();
assertEquals("修改失败", 1,affectrow);
factory.closeSession(session);
}
@Test
public void selByTrim() {
//selsct标签 - trim标签
String sql = "com.qckj.mapper.UserMapper.selByTrim";
SqlSession session = factory.openSession();
User userbean = new User();
userbean.setUserid(1);
// userbean.setUsername("A");
List<User> userlist = session.selectList(sql,userbean);
for (User user : userlist) {
System.out.println(user.getUserid()+","+user.getUsername()+","+user.getPassword());
}
factory.closeSession(session);
}
@Test
public void selByCDATA() {
//比较运算符运用,<![CDATA[关系式]]>
String sql = "com.qckj.mapper.UserMapper.selByCDATA";
SqlSession session = factory.openSession();
List<User> userlist = session.selectList(sql,1);
for (User user : userlist) {
System.out.println(user.getUserid()+","+user.getUsername()+","+user.getPassword());
}
factory.closeSession(session);
}
@Before
public void initFactory() {
factory = Factory.getInstance();
}
}
======================== ===以上完整映射文件代码==UserMapper.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.qckj.mapper.UserMapper">
<!-- sql标签作用:将sql文中经常用的字段提取出来 方便下方代码重复使用 利用include -->
<sql id="selColumn">userid,username,password</sql>
<sql id="selWhere">
<where> <!-- where标签 -相当于where条件 where标签中加入的第一个条件不加and 不用加1=1
where标签可以自动去除是“AND”或“OR”开头的sql中的“AND”或“OR”关键字。 -->
<if test="userid != null">
userid = ${userid} <!-- 如果第一个条件不符合条件的话, 相当于第二个条件开始自动在and前加1=1-->
</if>
<if test="username != null">
and username like concat('%',#{username},'%')
</if>
</where>
</sql>
<!-- ==================================模糊查询====================================== -->
<select id="selByUsername" parameterType="String" resultType="User">
select
<include refid="selColumn"/>
from
tab_user
where
username like concat('%',#{username},'%')
<!-- 加百分号的第二种方式:concat连接 oracle中只能连接两个参数 mysql中可以连接多个参数 -->
<!-- #{ } 与${ } 的区别 :#{ } 自动加单引号 防止注入式攻击,${ } 则不会自动加单引号 -->
</select>
<!-- ===================================if标签=================================== -->
<select id="selByIf" parameterType="User" resultType="User">
select
<include refid="selColumn"/> <!-- 利用include调用sql标签中提取的常用的字段内容 -->
from
tab_user
where
1 = 1
<if test="userid != null"> <!-- if标签 相当于if··· test中填写判断条件 必须有and 否则sql文错误 -->
and userid = ${userid}
</if>
<if test="username != null">
and username like concat('%',#{username},'%')
</if>
</select>
<!-- ============================include提取sql标签中的内容========================== -->
<select id="selByWhere" parameterType="User" resultType="User">
select
<include refid="selColumn"/>
from
tab_user
<include refid="selWhere"/>
</select>
<!-- ==========================choose - when - otherwise - 排序============================== -->
<select id="selByChoose" parameterType="User" resultType="User">
select
<include refid="selColumn"/>
from
tab_user
<include refid="selWhere"/>
order by #{ordercol}
<choose>
<when test="orderad != null">
${orderad}
</when>
</choose>
</select>
<!-- ===========================传集合用foreach循环 - in范围================================= -->
<select id="selByForeach" parameterType="User" resultType="User">
select
<include refid="selColumn"/>
from
tab_user
where userid in
<foreach collection="idlist" item="id" open="(" close=")" separator=",">
<!-- collection接受的集合/数组/map item表示集合中的每一个元素进行迭代时候的别名
open表示以什么开始 close表示以什么结束
separator表示在每次进行迭代之间以什么符号作为分隔符 -->
${id}
</foreach>
</select>
<!-- =======================update标签、set标签================================= -->
<update id="updUserSet" parameterType="User">
update tab_user
<set> <!-- set标签可以自动去除sql中的多余的“,” -->
<if test="username!=null">
username = #{username},,
</if>
<if test="password != null">
password = #{password},
</if>
</set>
where userid = #{userid}
</update>
<!-- =============================update标签、trim标签================================= -->
<update id="updUserSet2" parameterType="User">
update tab_user
<trim prefix="set" suffixOverrides=","> <!-- trim以set开始 内容以逗号分隔 效果同set标签
可以自动去除sql中的多余的“,” -->
<if test="username!=null">
username = #{username},,
</if>
<if test="password != null">
password = #{password},
</if>
</trim>
where userid = #{userid}
</update>
<!-- ========================selsct标签 - trim标签 ====================================== -->
<select id="selByTrim" parameterType="User" resultType="User">
select
<include refid="selColumn"/>
from
tab_user
<trim prefix="where" prefixOverrides="and"> <!-- trim标签 以where开始,sql文之间以and分隔 -->
<if test="userid != null">
userid = ${userid}
</if>
<if test="username != null">
username like concat('%',#{username},'%')
</if>
</trim>
</select>
<!-- =====================比较运算符运用,<![CDATA[关系式]]>=========================== -->
<select id="selByCDATA" parameterType="int" resultType="User">
select
<include refid="selColumn"/>
from
tab_user
where
<![CDATA[userid > #{id}]]>
</select>
</mapper>