Mybatis动态Sql核心
目录
2.当使用set必须保证有一个条件成立 否则sql语法异常(有一定的局限性)
3.当使用trim标签必须保证有一个条件成立 否则sql语法异常(有一定的局限性)
1.Mybatis的XML配置文件解析
Mybatis 缺点总结
1.必须传的是类型或者Map集合 无法传入单值 否则报错
<select id="findAllUsers_if"
resultMap="user_map"
parameterType="User">
<include refid="select_user"></include>
where address='北京'
<if test="name != null">
and username =#{name}
</if>
</select>
<!-- choose when otherwise -->
<select id="findAllUsers_choose"
resultMap="user_map"
parameterType="java.util.Map">
<include refid="select_user"></include>
where address='北京'
<choose>
<when test="uname !=null">
and username =#{uname}
</when>
<!-- <otherwise>
and userpassword like '%%'
</otherwise> -->
</choose>
</select>2.当使用set必须保证有一个条件成立 否则sql语法异常(有一定的局限性)
<!-- update标签
第一个if条件成立,第二条件不成立,会自动去掉后面的逗号
如果两个条件都不成立,set不会出现,但是会报异常,sql语法异常
至少有一个条件要成立
-->
<update id="updateUser_set"
parameterType="java.util.Map">
update t_user
<set>
<if test="uname!=null">username=#{uname},</if>
<if test="upwd!=null">userpassword=#{upwd}</if>
</set>
where id=#{uid}
</update>
3.当使用trim标签必须保证有一个条件成立 否则sql语法异常(有一定的局限性)
<!-- 用trim 替换set
prefix="SET" 前缀为set
suffixOverrides="," 后缀覆盖
第一个if成立,第二 if不成立会去掉后面的逗号
至少有一个if要成立,否者会语法异常
-->
<update id="updateUser_trim2"
parameterType="java.util.Map">
update t_user
<trim prefix="SET" suffixOverrides=",">
<if test="uname!=null">username=#{uname},</if>
<if test="upwd!=null">userpassword=#{upwd}</if>
</trim>
where id=#{uid}
</update>
2.Demo接口测试代码
package com.tarena.dao;
import java.security.acl.Group;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import com.tarena.entity.User;
public interface UserMapper {
public int addUser(User user);
public int deleteUser(Integer id);
public int updateUser(User user);
public User findUserById(Integer id);
public List<User> findAllUsers();
//动态sql
public List<User> findAllUsers_if(User user); //不能放单值
public List<User> findAllUsers_choose(Map data);//Map
public List<User> findAllUsers_where(Map data);
public int updateUser_set(Map data);
public List<User> findAllUsers_trim1(Map data);
public int updateUser_trim2(Map data);
public List<User> findAllUsers_foreach(ArrayList ids);
public User findUserById_association(int id);
public Group findGroupById_collection(int id);
}
3.单元测试小案例
package com.tarena.test;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import com.tarena.dao.UserMapper;
import com.tarena.entity.Group;
import com.tarena.entity.User;
import com.tarena.util.MyBatisUtil;
public class TestUserMapperClass {
@Test
public void testFindUserById(){
SqlSession sqlSession=null;
try{
sqlSession=MyBatisUtil.getSession();
//用UserMaper接口利用jdk动态代理生成接口儿子代理类,用代理生成代理对象
//用代理对象调用目标方法,实际上执行的是InvocationHandler中的invoke方法
//在invoke方法中调用selectOne等原生api方法,来做增删改查
// 接口类型 jdk动态代理对象=sqlSession.getMapper(接口类型.class);
UserMapper userMapper=sqlSession.getMapper(UserMapper.class);
User user=userMapper.findUserById(7);
System.out.println(user);
sqlSession.commit();
}catch(Exception e){
e.printStackTrace();
sqlSession.rollback();
}finally{
sqlSession.close();
}
}
@Test
public void testFindAllUsers(){
SqlSession sqlSession=null;
try{
sqlSession=MyBatisUtil.getSession();
UserMapper userMapper=sqlSession.getMapper(UserMapper.class);
List<User> users=userMapper.findAllUsers();
for(User user : users)
System.out.println(user);
sqlSession.commit();
}catch(Exception e){
e.printStackTrace();
sqlSession.rollback();
}finally{
sqlSession.close();
}
}
@Test
public void testAddUser(){
SqlSession sqlSession=null;
try{
sqlSession=MyBatisUtil.getSession();
UserMapper userMapper=sqlSession.getMapper(UserMapper.class);
User user=new User();
user.setName("ff");
user.setAddress("ff");
user.setPassword("ff");
int rowAffect=userMapper.addUser(user);
System.out.println(rowAffect);
sqlSession.commit();
}catch(Exception e){
e.printStackTrace();
sqlSession.rollback();
}finally{
sqlSession.close();
}
}
@Test
public void testDeleteUser(){
SqlSession sqlSession=null;
try{
sqlSession=MyBatisUtil.getSession();
UserMapper userMapper=sqlSession.getMapper(UserMapper.class);
int rowAffect=userMapper.deleteUser(2);
System.out.println(rowAffect);
sqlSession.commit();
}catch(Exception e){
e.printStackTrace();
sqlSession.rollback();
}finally{
sqlSession.close();
}
}
@Test
public void testUPdateUser(){
SqlSession sqlSession=null;
try{
sqlSession=MyBatisUtil.getSession();
UserMapper userMapper=sqlSession.getMapper(UserMapper.class);
User user=new User();
user.setId(7);
user.setName("ff");
user.setAddress("ff");
user.setPassword("ff");
int rowAffect=userMapper.updateUser(user);
System.out.println(rowAffect);
sqlSession.commit();
}catch(Exception e){
e.printStackTrace();
sqlSession.rollback();
}finally{
sqlSession.close();
}
}
@Test
public void testFindAllUser_if(){
SqlSession sqlSession=null;
try{
sqlSession=MyBatisUtil.getSession();
UserMapper userMapper=sqlSession.getMapper(UserMapper.class);
User user=new User();
//user.setName("eee");
List<User> users=userMapper.findAllUsers_if(user);
for(User u : users){
System.out.println(u);
}
sqlSession.commit();
}catch(Exception e){
e.printStackTrace();
sqlSession.rollback();
}finally{
sqlSession.close();
}
}
@Test
public void testFindAllUser_choose(){
SqlSession sqlSession=null;
try{
sqlSession=MyBatisUtil.getSession();
UserMapper userMapper=sqlSession.getMapper(UserMapper.class);
Map data=new HashMap();
//data.put("uname", "eee");
List<User> users=userMapper.findAllUsers_choose(data);
for(User u : users){
System.out.println(u);
}
sqlSession.commit();
}catch(Exception e){
e.printStackTrace();
sqlSession.rollback();
}finally{
sqlSession.close();
}
}
@Test
public void testFindAllUser_where(){
SqlSession sqlSession=null;
try{
sqlSession=MyBatisUtil.getSession();
UserMapper userMapper=sqlSession.getMapper(UserMapper.class);
Map data=new HashMap();
//data.put("uname", "eee");
data.put("uaddress", "北京");
List<User> users=userMapper.findAllUsers_where(data);
for(User u : users){
System.out.println(u);
}
sqlSession.commit();
}catch(Exception e){
e.printStackTrace();
sqlSession.rollback();
}finally{
sqlSession.close();
}
}
@Test
public void testUpdateUser_Set(){
SqlSession sqlSession=null;
try{
sqlSession=MyBatisUtil.getSession();
UserMapper userMapper=sqlSession.getMapper(UserMapper.class);
Map data=new HashMap();
data.put("uid", new Integer(8));
//data.put("uname", "ffff");
//data.put("upwd", "ffff");
int rowAffect=userMapper.updateUser_set(data);
System.out.println(rowAffect);
sqlSession.commit();
}catch(Exception e){
e.printStackTrace();
sqlSession.rollback();
}finally{
sqlSession.close();
}
}
@Test
public void testFindAllUser_trim1(){
SqlSession sqlSession=null;
try{
sqlSession=MyBatisUtil.getSession();
UserMapper userMapper=sqlSession.getMapper(UserMapper.class);
Map data=new HashMap();
//data.put("uname", "eee");
//data.put("uaddress", "北京");
List<User> users=userMapper.findAllUsers_trim1(data);
for(User u : users){
System.out.println(u);
}
sqlSession.commit();
}catch(Exception e){
e.printStackTrace();
sqlSession.rollback();
}finally{
sqlSession.close();
}
}
@Test
public void testUpdateUser_trim2(){
SqlSession sqlSession=null;
try{
sqlSession=MyBatisUtil.getSession();
UserMapper userMapper=sqlSession.getMapper(UserMapper.class);
Map data=new HashMap();
data.put("uid", new Integer(8));
//data.put("uname", "f");
//data.put("upwd", "ffff");
int rowAffect=userMapper.updateUser_trim2(data);
System.out.println(rowAffect);
sqlSession.commit();
}catch(Exception e){
e.printStackTrace();
sqlSession.rollback();
}finally{
sqlSession.close();
}
}
@Test
public void testFindAllUser_foreach(){
SqlSession sqlSession=null;
try{
sqlSession=MyBatisUtil.getSession();
UserMapper userMapper=sqlSession.getMapper(UserMapper.class);
ArrayList ids=new ArrayList();
ids.add(4);
ids.add(6);
ids.add(7);
List<User> users=userMapper.findAllUsers_foreach(ids);
for(User u : users){
System.out.println(u);
}
sqlSession.commit();
}catch(Exception e){
e.printStackTrace();
sqlSession.rollback();
}finally{
sqlSession.close();
}
}
@Test
public void testFindUserById_association(){
SqlSession sqlSession=null;
try{
sqlSession=MyBatisUtil.getSession();
UserMapper userMapper=sqlSession.getMapper(UserMapper.class);
User user=userMapper.findUserById_association(8);
System.out.println(user);
sqlSession.commit();
}catch(Exception e){
e.printStackTrace();
sqlSession.rollback();
}finally{
sqlSession.close();
}
}
@Test
public void testFindUserById_collection(){
SqlSession sqlSession=null;
try{
sqlSession=MyBatisUtil.getSession();
UserMapper userMapper=sqlSession.getMapper(UserMapper.class);
Group group=userMapper.findGroupById_collection(1);
System.out.println(group);
sqlSession.commit();
}catch(Exception e){
e.printStackTrace();
sqlSession.rollback();
}finally{
sqlSession.close();
}
}
}
<?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.tarena.dao.UserMapper">
<!-- namespace必须为包名.接口名 -->
<!-- resultMap标签 -->
<resultMap type="User" id="user_map">
<!-- 主键 -->
<id property="id" column="id"/>
<!-- 非主键 -->
<result property="name" column="username"/>
<result property="password" column="userpassword"/>
<result property="address" column="address"/>
</resultMap>
<!-- sql标签 -->
<sql id="select_user" >
select
id,
username,
userpassword,
address
from
t_user
</sql>
<!-- 根据id查询一个对象
id="findUserById" 必须接口中的方法名称
parameterType="java.lang.Integer" 必须对应接口方法参数类型
resultType="User" 必须对应接口中的方法的返回类型
-->
<select id="findUserById"
parameterType="java.lang.Integer"
resultMap="user_map" >
<include refid="select_user"></include>
where id=#{id}
</select>
<!-- 查询多个 -->
<select id="findAllUsers"
resultMap="user_map">
<include refid="select_user"></include>
</select>
<insert id="addUser"
parameterType="com.tarena.entity.User">
insert into t_user
(
username,
userpassword,
address
)
values
(
#{name},
#{password},
#{address}
)
</insert>
<!-- 删除用户 -->
<delete id="deleteUser"
parameterType="java.lang.Integer">
delete from t_user where id=#{id}
</delete>
<!-- 更新用户信息 -->
<update id="updateUser"
parameterType="com.tarena.entity.User">
update t_user set
username=#{name},
userpassword=#{password},
address=#{address}
where
id=#{id}
</update>
<!-- 动态sql的开始 -->
<!-- if标签
parameterType="User" 要求是实体对象和map
if条件成立连接sql条件
-->
<select id="findAllUsers_if"
resultMap="user_map"
parameterType="User">
<include refid="select_user"></include>
where address='北京'
<if test="name != null">
and username =#{name}
</if>
</select>
<!-- choose when otherwise -->
<select id="findAllUser s_choose"
resultMap="user_map"
parameterType="java.util.Map">
<include refid="select_user"></include>
where address='北京'
<choose>
<when test="uname !=null">
and username =#{uname}
</when>
<otherwise>
and userpassword like '%%'
</otherwise>
</choose>
</select>
<!-- where 标签
if的条件,第一个不成,第二成立
where and address=#{uaddress}
如果mybatis检测到where 后and,则去除and关键字
如果if条件都不成立 where关键会去掉
-->
<select id="findAllUsers_where"
resultMap="user_map"
parameterType="java.util.Map">
<include refid="select_user"></include>
<where>
<if test="uname !=null">
username=#{uname}
</if>
<if test="uaddress !=null">
and address=#{uaddress}
</if>
</where>
</select>
<!-- update标签
第一个if条件成立,第二条件不成立,会自动去掉后面的逗号
如果两个条件都不成立,set不会出现,但是会报异常,sql语法异常
至少有一个条件要成立 Mybatis底层写的还是不太好
-->
<update id="updateUser_set"
parameterType="java.util.Map">
update t_user
<set>
<if test="uname!=null">username=#{uname},</if>
<if test="upwd!=null">userpassword=#{upwd}</if>
</set>
where id=#{uid}
</update>
<!-- 用trim标签替换where标签
prefix="WHERE" prefix:前缀 where
prefixOverrides="AND|OR" prefixOverrides:前缀覆盖
第一个if不成立,第二if成立,会prefixOverrides中设置的值去覆盖
都不成立where前缀会去掉
-->
<select id="findAllUsers_trim1"
resultMap="user_map"
parameterType="java.util.Map">
<include refid="select_user"></include>
<trim prefix="WHERE" prefixOverrides="AND|OR">
<if test="uname !=null">
username=#{uname}
</if>
<if test="uaddress !=null">
and address=#{uaddress}
</if>
</trim>
</select>
<!-- 用trim 替换set
prefix="SET" 前缀为set
suffixOverrides="," 后缀覆盖
第一个if成立,第二 if不成立会去掉后面的逗号
至少有一个if要成立,否者会语法异常
-->
<update id="updateUser_trim2"
parameterType="java.util.Map">
update t_user
<trim prefix="SET" suffixOverrides=",">
<if test="uname!=null">username=#{uname},</if>
<if test="upwd!=null">userpassword=#{upwd}</if>
</trim>
where id=#{uid}
</update>
<!-- foreach标签 -->
<select id="findAllUsers_foreach"
parameterType="java.util.ArrayList"
resultMap="user_map">
<include refid="select_user"></include>
where id in
<foreach collection="list"
item="id"
open="("
separator=","
close=")">
#{id}
</foreach>
</select>
<!-- 动态sql的结束 -->
<!-- mybatis的关联映射查询开始 -->
<!-- 对一关联 查用户信息 -->
<resultMap type="User" id="userMap">
<id property="id" column="uid"/>
<result property="name" column="username"/>
<result property="password" column="userpassword"/>
<result property="address" column="address"/>
<association property="group" javaType="Group">
<id property="id" column="gid"/>
<result property="name" column="groupname"/>
<result property="loc" column="grouploc"/>
</association>
</resultMap>
<select id="findUserById_association"
parameterType="java.lang.Integer"
resultMap="userMap">
select
u.id uid,
u.username,
u.userpassword,
u.address,
g.id gid,
g.groupname,
g.grouploc
from
(
select * from t_user where id=#{id}
) u
left join t_group g
on u.group_id=g.id
</select>
<!--对多关联 查组信息 -->
<resultMap type="User" id="tempUserMap">
<id property="id" column="uid"/>
<result property="name" column="username"/>
<result property="password" column="userpassword"/>
<result property="address" column="address"/>
</resultMap>
<resultMap type="Group" id="groupMap">
<id property="id" column="gid"/>
<result property="name" column="groupname"/>
<result property="loc" column="grouploc"/>
<collection property="users"
ofType="User"
javaType="java.util.List"
resultMap="tempUserMap">
</collection>
</resultMap>
<select id="findGroupById_collection"
parameterType="java.lang.Integer"
resultMap="groupMap">
select
g.id gid,
g.groupname,
g.grouploc,
u.id uid,
u.username,
u.userpassword,
u.address
from
(
select * from t_group where id=#{id}
) g
left outer join t_user u
on g.id=u.group_id
</select>
<!-- mybatis的关联映射查询结束 -->
</mapper>
扫一扫
598
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
