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

2019-08-16 10:55:58来源：博客园阅读 ()

动态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>