Spring 学习小记（十）

于 2020-12-09 23:34:06 发布
阅读量284 收藏
点赞数
分类专栏： 编程 Java 文章标签： spring mybatis
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/advent_java/article/details/110943459
版权
MyBatis动态SQL
 
本小记学习目标
  1. 掌握拼接MyBatis的动态SQL语句
 
一、关于Spring集成MyBatis的预习
在开始进行MyBatis动态SQL的学习前，预习关于Spring与MyBatis的集成配置
 
新增一个Maven的jar工程，在pom.xml中引入相应的jar依赖
< dependencies >
       <!-- 添加Spring核心依赖包 Context -->
             < dependency >
                   < groupId >org.springframework </ groupId >
                   < artifactId >spring-context </ artifactId >
                   < version >5.0.2.RELEASE </ version >
             </ dependency >
            
             <!-- mybatis -->
             < dependency >
                < groupId >org.mybatis </ groupId >
                < artifactId > mybatis </ artifactId >
                < version >3.4.5 </ version >
             </ dependency >
             <!-- mysql -connector-java -->
             < dependency >
                < groupId > mysql </ groupId >
                < artifactId > mysql-connector-java </ artifactId >
                < version >5.1.45 </ version >
             </ dependency >
            
             <!-- https://mvnrepository.com/artifact/log4j/log4j -->
             < dependency >
                < groupId >log4j </ groupId >
                < artifactId >log4j </ artifactId >
                < version >1.2.17 </ version >
             </ dependency >
            
             < dependency >
                < groupId >org.apache.logging.log4j </ groupId >
                < artifactId >log4j-core </ artifactId >
                < version >2.3 </ version >
             </ dependency >
             <!-- spring- jdbc -->
             < dependency >
                < groupId >org.springframework </ groupId >
                < artifactId >spring- jdbc </ artifactId >
                < version >5.0.2.RELEASE </ version >
             </ dependency >
             <!-- aspectjweaver -->
       < dependency >
       < groupId >org.aspectj </ groupId >
       < artifactId > aspectjweaver </ artifactId >
       < version >1.8.13 </ version >
       </ dependency >
       <!-- spring-aspects -->
       < dependency >
       < groupId >org.springframework </ groupId >
       < artifactId >spring-aspects </ artifactId >
       < version >5.0.2.RELEASE </ version >
       </ dependency >
       <!-- aopalliance -->
       < dependency >
          < groupId > aopalliance </ groupId >
          < artifactId > aopalliance </ artifactId >
          < version >1.0 </ version >
       </ dependency >
       <!-- mybatis -spring -->
       < dependency >
          < groupId >org.mybatis </ groupId >
          < artifactId > mybatis-spring </ artifactId >
          < version >1.3.1 </ version >
       </ dependency >
       <!-- commons-dbcp2 -->
       < dependency >
          < groupId > org.apache.commons </ groupId >
          < artifactId >commons-dbcp2 </ artifactId >
          < version >2.2.0 </ version >
       </ dependency >
       <!-- commons-pool2 -->
       < dependency >
          < groupId > org.apache.commons </ groupId >
          < artifactId >commons-pool2 </ artifactId >
          < version >2.5.0 </ version >
       </ dependency >
      
  </ dependencies >
 
新增实体类：com.xiaoxie.pojo.Student(对应的是数据库中的表student)
package com.xiaoxie.pojo;
public class Student {
       private Integer id;
       private String name;
       private Integer age;
      
       public Student() {}
      
       public Student(String name,Integer age) {
             this. name = name;
             this. age = age;
      }
      
       //getter和setter方法
             public Integer getId() {
                   return id;
            }
             public void setId(Integer id) {
                   this. id = id;
            }
             public String getName() {
                   return name;
            }
             public void setName(String name) {
                   this. name = name;
            }
             public Integer getAge() {
                   return age;
            }
             public void setAge(Integer age) {
                   this. age = age;
            }
            
             public String toString() {
                   return "Student[id="+ id+ ",name="+ name+ ",age="+ age+ "]";
            }
}
 
新增配置文件jdbc.properties(在src/main/resources下)
jdbc.driverClass= com.mysql.jdbc.Driver
jdbc.url= jdbc:mysql:// localhost :3306/test?characterEncoding=utf8
jdbc.username= root
jdbc.password= root
jdbc.MaxTotal= 30
jdbc.maxIdle= 10
jdbc.initialSize= 5
 
新增dao接口：com.xiaoxie.dao.StudentDao（对应mapper中的配置）
新增controller实现：com.xiaoxie.controller.StudentController(在其中处理与数据库交互的业务逻辑)
 
新增Spring的配置文件：spring-config.xml
<? xml version= "1.0" encoding= "UTF-8" ?>
       xmlns:xsi= " http://www.w3.org/2001/XMLSchema-instance "
       xmlns:context= " http://www.springframework.org/schema/context "
       xmlns:tx= " http://www.springframework.org/schema/tx "
       <!-- 指定Spring需要扫描的包 -->
       < context:component-scan base-package= "com.xiaoxie.dao" />
       < context:component-scan base-package= "com.xiaoxie.controller" />
      
       <!-- 引入外部资源文件 -->
       < context:property-placeholder location= "classpath:jdbc.properties" />
       <!-- 配置数据源 -->
       < bean class= " org.apache.commons.dbcp2.BasicDataSource " id= "dataSource" >
             < property name= " driverClassName" value= "${jdbc.driverClass}" />
             < property name= " url" value= "${jdbc.url}" />
             < property name= " username" value= "${jdbc.username}" />
             < property name= " password" value= "${jdbc.password}" />
             < property name= " maxTotal" value= "${jdbc.MaxTotal}" />   <!-- 最大连接数 -->
             < property name= " maxIdle" value= "${jdbc.maxIdle}" />     <!-- 最大空闲连接数 -->
             < property name= " initialSize" value= "${jdbc.initialSize}" />   <!-- 初始化连接数 -->
       </ bean >
      
       <!-- 添加事务支持 -->
       < bean class= "org.springframework.jdbc.datasource.DataSourceTransactionManager" id= "txManager" >
             < property name= " dataSource" ref= "dataSource" />
       </ bean >
      
       <!-- 开启事务注解支持 -->
       < tx:annotation-driven transaction-manager= "txManager" />
      
       <!-- 配置MyBatis -->
       < bean class= "org.mybatis.spring.SqlSessionFactoryBean" id= "sqlSessionFactoryBean" >
             <!-- 指定数据源 -->
             < property name= " dataSource" ref= "dataSource" />
             <!-- MyBatis配置文件 -->
             < property name= " configLocation" value= "mybatis-config.xml" />
       </ bean >
      
       <!-- Spring自动扫描MyBatis并进行装配 -->
       < bean class= "org.mybatis.spring.mapper.MapperScannerConfigurer" >
             <!-- 指定需要扫描的包，这里只需要到接口，接口中的方法与 mybatis 映射文件中指定的相同 -->
             < property name= " basePackage" value= "com.xiaoxie.dao" />
             < property name= " sqlSessionFactoryBeanName" value= "sqlSessionFactoryBean" />
       </ bean >
</ beans >
 
新增MyBatis的配置文件：主要是指定Mapper(在src/main/resources下)
<? 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 >
       <!-- 在MyBatis进行嵌套查询时，使用延迟加载可以提高一定的性能所以在这里可把把这个配置打开 -->
       < settings >
             <!-- 延迟加载 -->  
             < setting name= "lazyLoadingEnabled" value= "true" />
             <!-- 按需加载 -->
             < setting name= "aggressiveLazyLoading" value= "false" />
       </ settings >
      
       <!-- 查找映射文件 -->
       < mappers >
             < mapper resource= "mapper/StudentMapper.xml" />
       </ mappers >
      
</ configuration >
 
新增Mapper：StudentMapper(在src/main/resources/mapper下)
<? xml version= "1.0" encoding= "UTF-8" ?>
<! DOCTYPE mapper
  PUBLIC "-// mybatis.org//DTD Mapper 3.0//EN"
< mapper namespace= "com.xiaoxie.dao.StudentDao" >
       <!-- 查询所有记录 -->
       < select id= "selectAllStuents" resultType= "com.xiaoxie.pojo.Student" >
            select id,name,age from student
       </ select >
</ mapper>
 
新增log4j2.xml(在src/main/resources下)
<? xml version= "1.0" encoding= "UTF-8" ?>
< Configuration status= "WARN" >
    < Appenders >
        < Console name= "Console" target= "SYSTEM_OUT" >
            < PatternLayout pattern= "[%t] %d{HH:mm:ss.SSS} - %msg%n" />
        </ Console >
    </ Appenders >
    < Loggers >
        < Root level= "DEBUG" >
            < AppenderRef ref= "Console" />
        </ Root >
        < logger name= "java.sql" level= "DEBUG" ></ logger >
        < logger name= "org.apache.ibatis" level= "INFO" ></ logger >
    </ Loggers >
</ Configuration >
 
编写Dao接口：com.xiaoxie.dao.StudentDao
package com.xiaoxie.dao;
import java.util.List;
import org.apache.ibatis.annotations.Mapper;
import org.springframework.stereotype.Repository;
import com.xiaoxie.pojo.Student;
@Repository( "studentDao")
@Mapper
public interface StudentDao {
      List<Student> selectAllStuents();
}
 
编写controller实现类：com.xiaoxie.controller.StudentController
package com.xiaoxie.controller;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.transaction.annotation.Transactional;
import com.xiaoxie.dao.StudentDao;
import com.xiaoxie.pojo.Student;
@Controller( "studentController")
@Transactional
public class StudentController {
       @Autowired
       private StudentDao studentDao;
      
       public void test() {
            List<Student> stuents = studentDao.selectAllStuents();
             for (Student student : stuents) {
                  System. out.println( student);
            }
      }
}
 
新增测试类，com.xiaoxie.test.Application
package com.xiaoxie.test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import com.xiaoxie.controller.StudentController;
public class Application {
       public static void main(String[] args) {
            ApplicationContext context = new ClassPathXmlApplicationContext( "spring-config.xml");
            StudentController studentController = (StudentController) context.getBean( "studentController");
             studentController.test();
      }
}
 
以上为Spring集成MyBatis的预习，在完成后继续进行下面关于动态SQL的内容介绍
 
二、拼装MyBatis动态SQL
2.1 <if>元素
<if>元素是最常用的元素，它类似于java中的if语句
在StudentMapper中新增一个SQL映射
<!-- 使用<if>元素 -->
< select id= "selectStudentByIf" resultType= "com.xiaoxie.pojo.Student" parameterType= "com.xiaoxie.pojo.Student" >
            select * from student where 1=1
             < if test= "name!=null and name!=''" >
                  and name like concat('%',#{name},'%')
             </ if >
             < if test= "age!=null and age!=0" >
                  and age=#{age}
             </ if >
       </ select >
注意一下这里的<if>元素的写法，<if test="">要拼接的内容</if>
 
在对应的接口中新增对应的接口方法
List<Student> selectStudentByIf(Student student);
 
在controller类中新增相应的业务逻辑方法来调用这个接口方法
public void test_if() {
            Student stu = new Student();
             stu.setName( "赵");
            List<Student> students = studentDao.selectStudentByIf( stu);
             for (Student student : students) {
                  System. out.println( student);
            }
这里要注意一下，创建的对象是没有为age属性赋值的，这个时候拼接的sql语句不会有第二个<if>中的条件出现
 
在测试类中对这个controller实现类中的test_if方法进行调用，在控制台打印的结果中我们可以看到如下信息
[main] 22:04:32.327 - ==>  Preparing: select * from student where 1=1 and name like concat('%',?,'%')
[main] 22:04:32.375 - ==> Parameters: 赵(String)
[main] 22:04:32.402 - <==      Total: 1
 
2.2 <choose>、<when>、<otherwise>元素
当不想用到所有的条件语句，而只是选取其中的部分，这个时候可以使用<choose>元素，这个元素类似于Java中的switch语句
在StudentMapper中新增SQL映射
<!-- 使用choose元素 -->
< select id= "selectStudentByChoose" resultType= "com.xiaoxie.pojo.Student" parameterType= "com.xiaoxie.pojo.Student" >
            select * from student where 1=1
             < choose >
                   < when test= "name!=null and name!=''" >
                        and name like concat('%',#{name},'%')
                   </ when >
                   < when test= "age!=null and age!=0" >
                        and age=#{age}
                   </ when >
                   < otherwise >
                        and id>10
                   </ otherwise >
             </ choose >
       </ select >
 
在对应的Dao接口中新增接口方法
List<Student> selectStudentByChoose(Student student);
 
在controller实现类中对新增相关的业务逻辑方法调用这个接口方法
public void test_choose() {
            Student stu = new Student();
            List<Student> students = studentDao.selectStudentByChoose( stu);
             for (Student student : students) {
                  System. out.println( student);
            }
            
      }
 
在测试类中调用这个方法进行测试
我们可以看到控制台打印的结果如下：
[main] 21:58:48.111 - ==>  Preparing: select * from student where 1=1 and id>10
[main] 21:58:48.159 - ==> Parameters:
[main] 21:58:48.188 - <==      Total: 3
[main] 21:58:48.192 - Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@40d10481]
Student[id=11,name=赵云,age=23]
Student[id=12,name=黄忠,age=35]
Student[id=18,name=刘备,age=30]
 
关于choose的写法如下
<choose>
    <when test="">
        这里是满足上面的测试条件需要拼接的sql语句
    </when>    
    <when test="">
        这里是满足上面的测试条件需要拼接的sql语句
    </when>
    <otherwise>
        上面的条件都不满足时拼接的sql语句
    </otherwise>
</choose>
 
2.3 <trim>、<where>、<set>元素
<trim>元素
可以在自己包含的内容前添加某些前缀，也可以在后面加上某些后缀，对应的属性有prefix，suffix
可以把内容的首部某些内容覆盖，也可以把内容的某些尾部内容覆盖，对应的属性有prefixOverrides，suffixOverrides
在Mapper映射文件中新增SQL映射
<!-- trim元素 -->
< select id= "selectStudentByTrim" resultType= "com.xiaoxie.pojo.Student" parameterType= "com.xiaoxie.pojo.Student" >
            select * from student
             < trim prefix= "where" prefixOverrides= "and | or" >
                   < if test= "name!=null and name!=''" >
                        and name like concat('%',#{name},'%')
                   </ if >
                   < if test= "age!=null and age!=0" >
                        and age = #{age}
                   </ if >
             </ trim >
       </ select > 
 
在对应的Dao接口中新增接口方法
List<Student> selectStudentByTrim(Student student);
 
在controller实现类中新增方法，调用上面的接口方法
public void test_trim() {
            Student stu = new Student();
             stu.setName( "赵");
             stu.setAge(23);
            List<Student> students = studentDao.selectStudentByTrim( stu);
             for (Student student : students) {
                  System. out.println( student);
            }
      }
 
在测试类中测试上面的方法
可以看到在控制台打印的结果如下：
[main] 22:34:29.324 - ==>  Preparing: select * from student where name like concat('%',?,'%') and age = ?
[main] 22:34:29.372 - ==> Parameters: 赵(String), 23(Integer)
[main] 22:34:29.399 - <==      Total: 1
[main] 22:34:29.402 - Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@985696]
Student[id=11,name=赵云,age=23]
 
<where>元素
它的作用是会在写入<where>元素的地方输出一个WHERE语句，这里MyBatis会做相应的智能处理，如果在<where></where>中没有需要拼接的语句则不需要这里的where,会查询所有的记录，如果要拼接的语句开头是and或是or则会把开头的and 或 or去掉
Mapper映射文件中新增SQL映射
<!-- where元素 -->
< select id= "selectStudentByWhere" resultType= "com.xiaoxie.pojo.Student" parameterType= "com.xiaoxie.pojo.Student" >
            select * from student
             < where >
                   < if test= "name!=null and name!=''" >
                        and name like concat('%',#{name},'%')
                   </ if >
                   < if test= "age!=null and age!=0" >
                        and age = #{age}
                   </ if >
             </ where >
       </ select >
 
在Dao接口中新增对应的接口方法
List<Student> selectStudentByWhere(Student student);
 
在controller的类中新增方法调用这个接口方法
public void test_where() {
            Student stu = new Student();
             stu.setName( "赵");
             stu.setAge(23);
            List<Student> students = studentDao.selectStudentByWhere( stu);
             for (Student student : students) {
                  System. out.println( student);
            }
      }
 
在测试类中调用这个方法
在控制台打印结果如下
[main] 22:45:43.241 - ==>  Preparing: select * from student WHERE name like concat('%',?,'%') and age = ?
[main] 22:45:43.286 - ==> Parameters: 赵(String), 23(Integer)
[main] 22:45:43.314 - <==      Total: 1
[main] 22:45:43.318 - Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@6dd91637]
Student[id=11,name=赵云,age=23]
注意：这里打印的sql语句中WHER就是由<where>元素自动添加进去的
 
<set>元素
在update语句中使用<set>元素来更新列
在Mapper映射文件中添加Sql映射
<!-- set元素 -->
       < update id= "updateStudentBySet" parameterType= "com.xiaoxie.pojo.Student" >
            update student
             < set >
                   < if test= "name!=null and name!=''" >name=#{name} </ if >
             </ set >
             < where >
                  id=#{id}
             </ where >
       </ update >
 
在Dao接口中新增对应的接口方法
int updateStudentBySet(Student student);
 
在controller类中对上述方法进行调用
public void test_set() {
            Student stu = new Student();
             stu.setName( "赵子龙");
             stu.setId(11);
             int rows = studentDao.updateStudentBySet( stu);
            System. out.println( "更新成功！影响记录行数：" + rows);
            System. out.println( "更新后的记录：");
            List<Student> students = studentDao.selectStudentByIf( stu);
             for (Student student : students) {
                  System. out.println( student);
            }
      }
 
在测试类中对这个方法进行调用
控制台打印的结果如下
[main] 22:56:40.158 - ==>  Preparing: update student SET name=? WHERE id=?
[main] 22:56:40.210 - ==> Parameters: 赵子龙(String), 11(Integer)
[main] 22:56:40.212 - <==    Updates: 1
[main] 22:56:40.212 - Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@675ffd1d]
更新成功！影响记录行数：1
更新后的记录：
[main] 22:56:40.213 - Fetched SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@675ffd1d] from current transaction
[main] 22:56:40.215 - ==>  Preparing: select * from student where 1=1 and name like concat('%',?,'%')
[main] 22:56:40.216 - ==> Parameters: 赵子龙(String)
[main] 22:56:40.243 - <==      Total: 1
[main] 22:56:40.246 - Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@675ffd1d]
Student[id=11,name=赵子龙,age=23]
 
2.4 <foreach>元素
这个元素主要是用来在构建in条件中，它可以在sql语句中迭代一个集合。
<foreach>元素属性主要有：item,index,collection,open,separator,close
item:表示集合中的每个元素在迭代时的别名
index：指定一个名字，用来表示在迭代过程中每次迭代到的位置
open:表示语句以什么开始
separator:表示在每次迭代之间以什么符号作为分隔符
close:表示以什么结束
collection:这是一个必须的属性，这个属性有三种情况
    如果传入的是单参数且参数类型是一个list则collection属性值设置为list
    如果传入的是单参数且参数类型是一个array数组，collection属性值为array
    如果传入的参数是多参数，需要我们把它封装成为一个Map
 
在Mapper映射文件中添加SQL映射
<!-- foreach 元素 -->
       < select id= "selectStudentByForeach" resultType= "com.xiaoxie.pojo.Student" parameterType= "List" >
            select * from student where id in
             < foreach collection= "list" item= "item" index= "index" separator= "," open= "(" close= ")" >
                  #{item}
             </ foreach >
       </ select >
 
在Dao接口中新增对应的方法
List<Student> selectStudentByForeach(List<Integer> ids);
 
在controller中添加方法调用这个接口方法
public void test_foreach() {
            List<Integer> ids = new ArrayList<>();
             ids.add(10);
             ids.add(11);
             ids.add(12);
            List<Student> students = studentDao.selectStudentByForeach( ids);
             for (Student student : students) {
                  System. out.println( student);
            }
      }
 
在测试类中测试这个方法
在控制台中打印结果如下
[main] 23:15:32.644 - ==>  Preparing: select * from student where id in ( ? , ? , ? )
[main] 23:15:32.695 - ==> Parameters: 10(Integer), 11(Integer), 12(Integer)
[main] 23:15:32.725 - <==      Total: 3
[main] 23:15:32.728 - Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@19569ebd]
Student[id=10,name=诸葛亮,age=25]
Student[id=11,name=赵子龙,age=23]
Student[id=12,name=黄忠,age=35]
 
2.5 <bind>元素
当我们需要做模糊查询的时候我们在上面的例子中可以看到使用了mysql的concat函数做拼接，但是这里要注意的是这仅仅是适用于mysql数据库，对于其它的数据库这个函数不一定适用，那么为了解决这个通用性的问题则可以使用<bind>元素处理
在Mapper映射中新增SQL映射
<!-- bind元素 -->
       < select id= "selectStudentByBind" resultType= "com.xiaoxie.pojo.Student" parameterType= "com.xiaoxie.pojo.Student" >
             <!-- 注意:value中的name是表示实体类中的属性name -->
             < bind name= "bind_name" value= "'%' + name + '%'" />
            select * from student where name like #{bind_name}
       </ select >
 
在Dao接口中新增对应的方法
List<Student> selectStudentByBind(Student student);
 
在controller中新增实现方法
public void test_bind() {
            Student stu = new Student();
             stu.setName( "赵");
            List<Student> students = studentDao.selectStudentByBind( stu);
             for (Student student : students) {
                  System. out.println( student);
            }
      }
 
在测试类中进行测试
控制台打印结果如下
[main] 23:28:18.799 - ==>  Preparing: select * from student where name like ?
[main] 23:28:18.856 - ==> Parameters: %赵%(String)
[main] 23:28:18.883 - <==      Total: 1
[main] 23:28:18.886 - Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@62e6a3ec]
Student[id=11,name=赵子龙,age=23]
 
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值