动态SQL各个标签作用以及注意事项详解

创建com.mybatis包，包含：usermapper.xml和mybatis-config.xml

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.dao.userdao">
<!--id应该是接口中的方法，结果类型如没有配置别名则应该使用全名称 -->
<!-- if标签 -->
<select id="selectuserbyif" resulttype="com.po.myuser"
parametertype="com.po.myuser">
select * from user where 1=1
<if test ="uname != null and uname !=''">
and uname like concat('%',#{uname},'%')
</if>
<if test="usex != null and usex !=''">
and usex=#{usex}
</if>
</select>
<!-- choose标签 -->
<select id="selectuserbychoose" resulttype="com.po.myuser"
parametertype="com.po.myuser">
select * from user where 1=1
<choose>
<when test="uname != null and uname !=''">
and uname like concat('%',#{uname},'%')
</when>
<when test="usex != null and usex !=''">
and usex=#{usex}
</when>
<otherwise>
and uid >= 10
</otherwise>
</choose>
</select>
<!-- trim标签 -->
<select id="selectuserbytrim" resulttype="com.po.myuser"
parametertype="com.po.myuser">
select * from user
<trim prefix="where" prefixoverrides="and |or">
<!-- prefix指的是将整个语句的前缀‘and’被‘where’覆盖， 若不覆盖sql语句会变成：
select * from user and uname like concat('%',#{uname},'%') -->
<if test="uname != null and uname != ''">
and uname like concat('%',#{uname},'%')
</if>
<if test="usex != null and usex !=''">
and usex=#{usex}
</if>
</trim>
</select>
<!-- set标签 -->
<update id="updateuserbyset" parametertype="com.po.myuser">
update user
<set>
<if test="uname!=null">uname=#{uname},</if>
<if test="usex!=null">usex=#{sex}</if>
</set>
where uid=#{uid}
</update>
<!-- foreach标签 -->
<!-- 三种用法详解：https://blog.csdn.net/hjh908778/article/details/79034395 -->
<select id="selectuserbyforceah" resulttype="com.po.myuser"
parametertype="list">
select * from user where uid in
<foreach item="item" index="index" collection="list"
open="(" separator="," close=")">
<!--collection：代表传递进来的参数名称，可以是一个数组、list、set等集合
    item：配置的是循环中的当前元素
    index：配置的是当前元素在集合中的位置下标
    open和close：配置的是以什么符号将这些集合元素包装起来
    separator：是各个元素的间隔符 -->
#{item}
</foreach> 
</select>
</mapper>

mybatis-config.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>
<mappers>
<mapper resource="com/mybatis/usermapper.xml"/>
</mappers>
</configuration>

创建com.dao包，包含：userdao.java代码：

package com.dao;

import java.util.list;

import org.apache.ibatis.annotations.mapper;
import org.springframework.stereotype.repository;

import com.po.myuser;
@repository("userdao")
@mapper
public interface userdao {
public myuser selectuserbyid(integer uid);
public list<myuser> selectalluser();
public int adduser(myuser user);
public int updateuser(myuser user);
public int deleteuser(integer uid);
}

创建com.po包，包括：myuser.java代码：

package com.po;

public class myuser {
private integer uid;
private string uname;
private string usex;
public integer getuid() {
    return uid;
}
public void setuid(integer uid) {
    this.uid = uid;
}
public string getuname() {
    return uname;
}
public void setuname(string uname) {
    this.uname = uname;
}
public string getusex() {
    return usex;
}
public void setusex(string usex) {
    this.usex = usex;
}
public string tostring() {
    return "user[uid="+ uid +",uname="+uname+",usex"+usex+"]";
}
}

创建com.controller包，包括：testcontroller.java和usercontroller.java

usercontroller.java代码：

package com.controller;

import java.util.list;

import org.springframework.beans.factory.annotation.autowired;
import org.springframework.stereotype.controller;

import com.dao.userdao;
import com.po.myuser;
@controller("usercontroller")
public class usercontroller {
@autowired
private userdao userdao;
public void test() {
    myuser auser = userdao.selectuserbyid(1);
    system.out.println(auser);
    system.out.println("====================");
    myuser addmu = new myuser();
    addmu.setuid(1);
    addmu.setuname("陈恒");
    addmu.setusex("男");
    int add=userdao.adduser(addmu);
    system.out.println("=====================");
    myuser updatemu = new myuser();
    updatemu.setuid(1);
    updatemu.setuname("张三");
    updatemu.setusex("女");
    int up = userdao.updateuser(updatemu);
    system.out.println("修改了"+up+"条记录");
    system.out.println("=====================");
    int dl = userdao.deleteuser(9);
    system.out.println("删除了"+dl+"条记录");
    system.out.println("=====================");
    list<myuser>list =userdao.selectalluser();
    for(myuser myuser: list) {
        system.out.println(myuser);
    }
}
}

testcontroller.java代码：

package com.controller;

import org.springframework.context.applicationcontext;
import org.springframework.context.support.classpathxmlapplicationcontext;

public class testcontroller {
public static void main(string[] args) {
    applicationcontext appcon = new classpathxmlapplicationcontext("applicationcontext.xml");
    usercontroller ct = (usercontroller)appcon.getbean("usercontroller");
    ct.test();
}
}

在src下直接创建applicationcontext.xml，代码：

<?xml version="1.0" encoding="utf-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/xmlschema-instance"
       xmlns:tx="http://www.springframework.org/schema/tx"
       xmlns:context="http://www.springframework.org/schema/context"
       xsi:schemalocation="http://www.springframework.org/schema/beans
       http://www.springframework.org/schema/beans/spring-beans.xsd
       http://www.springframework.org/schema/context
       http://www.springframework.org/schema/context/spring-context.xsd
       http://www.springframework.org/schema/tx
       http://www.springframework.org/schema/tx/spring-tx.xsd">
       <context:component-scan base-package="com.dao"/>
       <context:component-scan base-package="com.controller"/>
       <!-- 配置数据源 -->
       <bean id="datasource" class="org.apache.commons.dbcp2.basicdatasource">
       <property name="driverclassname" value="com.mysql.cj.jdbc.driver"/>
       <property name="url" value="jdbc:mysql://localhost:3306/springtest?characterencoding=utf8"/>
       <property name="username" value="root"/>
       <property name="password" value="root"/>
       <property name="maxtotal" value="30"/>
       <property name="maxidle" value="10"/>
       <property name="initialsize" value="5"/>
       </bean>
       <!-- 添加事务支持 -->
       <bean id="txmanager"
             class="org.springframework.jdbc.datasource.datasourcetransactionmanager">
       <property name="datasource" ref="datasource"/>
       </bean>
       <!-- 开启事务注解 -->
       <!-- 事务管理器 的作用就是告诉spring容器利用jdbc的技术进行处理，如果是采用的hibernate则需要配置hibernatetransactionmanager来实现-->
       <tx:annotation-driven transaction-manager="txmanager" />
       <!-- 配制mybatis工厂，同时指定数据源，并与mybatis完美结合 -->
       <bean id="sqlsessionfactory" class="org.mybatis.spring.sqlsessionfactorybean">
       <property name="datasource" ref="datasource"/>
       <!-- configlocation 的属性为mybatis的核心配置文件  -->
       <property name="configlocation" value="classpath:com/mybatis/mybatis-config.xml"/>
       </bean>
       <!-- 扫描所有被@mapper注解的接口 -->
       <bean class="org.mybatis.spring.mapper.mapperscannerconfigurer">
       <property name="basepackage" value="com.dao"/>
       <property name="sqlsessionfactorybeanname" value="sqlsessionfactory"/>
       </bean>
       </beans>