旅游网网站建设,网站如何自己做seo,怎样在手机做自己的网站,除了wordpress还有什么MyBatis入门4.0 十 小黑子进行MyBatis参数处理10.1 单个简单类型参数10.1.1 单个参数Long类型10.1.2 单个参数Date类型 10.2 Map参数10.3 实体类参数#xff08;POJO参数#xff09;10.4 多参数10.5 Param注解#xff08;命名参数#xff09;10.6 Param注解源码分析 十一 小… MyBatis入门4.0 十 小黑子进行MyBatis参数处理10.1 单个简单类型参数10.1.1 单个参数Long类型10.1.2 单个参数Date类型 10.2 Map参数10.3 实体类参数POJO参数10.4 多参数10.5 Param注解命名参数10.6 Param注解源码分析 十一 小黑子的MyBatis查询专题11.1 返回Car11.2 返回List Car 11.3 返回Map11.3.1 返回List Map 11.4 返回MapString.Map11.5 resultMap结果映射11.5.1 开启驼峰命名自动映射 11.6返回总记录条数 十二 小黑子的动态SQL12.1 if标签12.2 where标签12.3 trim标签12.4 set标签12.5 chose when otherwise标签12.6 foreach标签12.6.1 批量删除12.6.2 批量插入 12.7 sql标签和include标签 十 小黑子进行MyBatis参数处理
10.1 单个简单类型参数
简单类型包括
byte short int long float double charByte Short Integer Long Float Double CharacterStringjava.util.Datejava.sql.Date
简单类型对于mybatis来说都是可以自动类型识别的
也就是说对于mybatis来说它是可以自动推断出ps.setXxxx()方法的。ps.setString()还是ps.setInt()。它可以自动推断。
其实SQL映射文件中的配置比较完整的写法是
select idselectByName resultTypestudent parameterTypejava.lang.Stringselect * from t_student where name #{name, javaTypeString, jdbcTypeVARCHAR}
/select其中sql语句中的javaTypejdbcType以及select标签中的parameterType属性都是用来帮助mybatis进行类型确定的。不过这些配置多数是可以省略的。因为mybatis它有强大的自动类型推断机制。
javaType可以省略jdbcType可以省略parameterType可以省略
准备数据库 目录展示
10.1.1 单个参数Long类型
StudentMapper
package com.powernode.mybatis.mapper;import com.powernode.mybatis.pojo.Student;import java.util.Date;
import java.util.List;public interface StudentMapper {/*** 当接口中的方法的参数只有一个单给参数并且参数的数据类型都是简单类型* 根据id查询、name查询、birth查询、sex查询* version 1.0*/ListStudent selectById(Long id);ListStudent selectByName(String name);ListStudent selectByBirth(Date birth);ListStudent selectBySex(Character sex);
}StudentMappper.xml
?xml version1.0 encodingUTF-8 ?
!DOCTYPE mapperPUBLIC -//mybatis.org//DTD Mapper 3.0//ENhttp://mybatis.org/dtd/mybatis-3-mapper.dtd
mapper namespacecom.powernode.mybatis.mapper.StudentMapper
!--ListStudent selectById(int id);ListStudent selectByName(String name);ListStudent selectByBirth(String birth);ListStudent selectBySex(String sex);parameterType属性的作用告诉mybatis框架我这个方法的参数类型是什么类型mybatis框架自身带有类型自动推断机制所以大部分情况下parameterType属性都是可以省略不写的SQL语句最终是这样的select * from t_student where id ?JDBC代码是一定要给?传值的怎么传值ps.setXxx(第几个问好传什么值);ps.setLong(1,1L)ps.setString(1,zhangsan)ps.setDate(1,new Date())ps.setInt(1,100)...mybatis底层到底调用setXxx的哪个方法取决于parameterType属性的值
--select idselectById resultTypeStudent parameterTypejava.lang.Longselect * from t_student where id #{id}/select/mapper测试
package com.powernode.mybatis.test;import com.powernode.mybatis.mapper.StudentMapper;
import com.powernode.mybatis.pojo.Student;
import com.powernode.mybatis.utils.SqlSessionUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;import java.util.List;public class StudentMapperTest {Testpublic void SelectById(){SqlSession sqlSession SqlSessionUtil.openSession();StudentMapper mapper sqlSession.getMapper(StudentMapper.class);ListStudent students mapper.selectById(1L);students.forEach(student - System.out.println(student));sqlSession.commit();sqlSession.close();}
} 10.1.2 单个参数Date类型
?xml version1.0 encodingUTF-8 ?
!DOCTYPE mapperPUBLIC -//mybatis.org//DTD Mapper 3.0//ENhttp://mybatis.org/dtd/mybatis-3-mapper.dtd
mapper namespacecom.powernode.mybatis.mapper.StudentMapper
!--ListStudent selectById(int id);ListStudent selectByName(String name);ListStudent selectByBirth(String birth);ListStudent selectBySex(String sex);parameterType属性的作用告诉mybatis框架我这个方法的参数类型是什么类型mybatis框架自身带有类型自动推断机制所以大部分情况下parameterType属性都是可以省略不写的SQL语句最终是这样的select * from t_student where id ?JDBC代码是一定要给?传值的怎么传值ps.setXxx(第几个问好传什么值);ps.setLong(1,1L)ps.setString(1,zhangsan)ps.setDate(1,new Date())ps.setInt(1,100)...mybatis底层到底调用setXxx的哪个方法取决于parameterType属性的值
--select idselectById resultTypeStudent parameterTypejava.lang.Longselect * from t_student where id #{id}/selectselect idselectByName resultTypeStudentselect * from t_student where name #{name,javaTypeString,jdbcTypeVARCHAR}/selectselect idselectByBirth resultTypeStudentselect * from t_student where birth #{birth}/selectselect idselectBySex resultTypeStudentselect * from t_student where sex #{sex}/select/mapper测试
package com.powernode.mybatis.test;import com.powernode.mybatis.mapper.StudentMapper;
import com.powernode.mybatis.pojo.Student;
import com.powernode.mybatis.utils.SqlSessionUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;public class StudentMapperTest {Testpublic void SelectBySex(){SqlSession sqlSession SqlSessionUtil.openSession();StudentMapper mapper sqlSession.getMapper(StudentMapper.class);Character sex Character.valueOf(男);ListStudent students mapper.selectBySex(sex);students.forEach(student - System.out.println(student));sqlSession.commit();sqlSession.close();}Testpublic void SelectByBirth() throws ParseException {SqlSession sqlSession SqlSessionUtil.openSession();StudentMapper mapper sqlSession.getMapper(StudentMapper.class);SimpleDateFormat sdf new SimpleDateFormat(yyyy-MM-dd);Date birth sdf.parse(1980-10-11);ListStudent students mapper.selectByBirth(birth);students.forEach(student - System.out.println(student));sqlSession.commit();sqlSession.close();}Testpublic void SelectByName(){SqlSession sqlSession SqlSessionUtil.openSession();StudentMapper mapper sqlSession.getMapper(StudentMapper.class);ListStudent students mapper.selectByName(李四);students.forEach(student - System.out.println(student));sqlSession.commit();sqlSession.close();}Testpublic void SelectById(){SqlSession sqlSession SqlSessionUtil.openSession();StudentMapper mapper sqlSession.getMapper(StudentMapper.class);ListStudent students mapper.selectById(1L);students.forEach(student - System.out.println(student));sqlSession.commit();sqlSession.close();}
}
10.2 Map参数 这种方式是手动封装Map集合将每个条件以key和value的形式存放到集合中。然后在使用的时候通过#{map集合的key}来取值 需求根据map集合保存学生信息
StudentMapper接口 int insertStudentByMap(MapString,Object map);StudentMapper.xml insert idinsertStudentByMap parameterTypemapinsert into t_student(id,name,age,sex,birth,height) value (null,#{姓名},#{年龄},#{性别},#{生日},#{身高})/insert测试
Testpublic void InsertStudentByMap(){SqlSession sqlSession SqlSessionUtil.openSession();StudentMapper mapper sqlSession.getMapper(StudentMapper.class);MapString,Object map new HashMap();map.put(姓名,麻瓜);map.put(年龄,25);map.put(身高,1.82);map.put(性别,男);map.put(生日,new Date());mapper.insertStudentByMap(map);sqlSession.commit();sqlSession.close();}10.3 实体类参数POJO参数 使用实体类参数这里需要注意的是#{} 里面写的是属性名字。这个属性名其本质上是set/get方法名去掉set/get之后的名字 StudentMapper.xml insert idinsertStudentByPOJOinsert into t_student(id,name,age,sex,birth,height) value (null,#{name},#{age},#{sex},#{birth},#{height})/insertStudentMapper接口
/** description: 保存学生信息通过实体类参数* param student* version 1.0*/int insertStudentByPOJO(Student student);测试 Testpublic void InsertStudentByPOJO(){SqlSession sqlSession SqlSessionUtil.openSession();StudentMapper mapper sqlSession.getMapper(StudentMapper.class);Student student new Student();student.setName(小老板);student.setAge(24);student.setSex(男);student.setBirth(new Date());student.setHeight(1.78);mapper.insertStudentByPOJO(student);sqlSession.commit();sqlSession.close();}10.4 多参数
需求通过name和sex查询
StudentMapper.xml select idselectByNameAndSex resultTypeStudentselect * from t_student where name #{name} and sex #{sex}/selectStudentMapper接口
/** 这是多参数* 根据name和sex查询Student信息* 如果是多个参数的话mybatis框架底层是怎么做到的呢* mybatis框架会自动创建一个Map集合并且Map集合是以这种方式储存参数的* map.put(arg0,name);* map.put(arg1,sex);* map.put(arg1,sex);* map.put(param1,name);* map.put(param2,sex);*/ListStudent selectByNameAndSex(String name,Character sex);测试 Testpublic void selectByNameAndSex(){SqlSession sqlSession SqlSessionUtil.openSession();StudentMapper mapper sqlSession.getMapper(StudentMapper.class);ListStudent students mapper.selectByNameAndSex(张三, 男);students.forEach(student - System.out.println(student));sqlSession.commit();sqlSession.close();}异常信息描述了name参数找不到可用的参数包括[arg1, arg0, param1, param2] 修改StudentMapper.xml配置文件尝试使用[arg1, arg0, param1, param2]去参数
修改StudentMapper.xml文件 select idselectByNameAndSex resultTypeStudent!-- select * from t_student where name #{name} and sex #{sex} --select * from t_student where name #{arg0} and sex #{param2}/select再次尝试修改StudentMapper.xml文件
select idselectByNameAndSex resultTypeStudent!--select * from t_student where name #{name} and sex #{sex}--!--select * from t_student where name #{arg0} and sex #{arg1}--!--select * from t_student where name #{param1} and sex #{param2}--select * from t_student where name #{arg0} and sex #{param2}
/select 通过测试可以看到
arg0 是第一个参数param1是第一个参数arg1 是第二个参数param2是第二个参数
实现原理实际上在mybatis底层会创建一个map集合以arg0/param1为key以方法上的参数为value例如以下代码
MapString,Object map new HashMap();
map.put(arg0, name);
map.put(arg1, sex);
map.put(param1, name);
map.put(param2, sex);// 所以可以这样取值#{arg0} #{arg1} #{param1} #{param2}
// 其本质就是#{map集合的key}注意使用mybatis3.4.2之前的版本时要用#{0}和#{1}这种形式。 10.5 Param注解命名参数
可以不用arg0 arg1 param1 param2吗这个map集合的key我们自定义可以吗当然可以。使用Param( 注解的名称 )注解即可。这样可以增强可读性。
需求根据name和age查询
StudentMapper接口 /** Param注解** mybatis框架底层的实现原理* map.put(name,name);* map.put(sex,sex);* param name* param sex**/ListStudent selectByNameAndSex2(Param(name)String name,Param(sex) Character sex);StudentMapper.xml select idselectByNameAndSex2 resultTypeStudent!-- 使用了Param注解之后arg0和arg1失效了但是param1和param2还可以用 --select * from t_student where name #{name} and sex and #{sex}/select测试
Testpublic void SelectByNameAndSex2(){SqlSession sqlSession SqlSessionUtil.openSession();StudentMapper mapper sqlSession.getMapper(StudentMapper.class);ListStudent students mapper.selectByNameAndSex2(张三, 男);students.forEach(student - System.out.println(student));sqlSession.commit();sqlSession.close();}核心Param(“这里填写的其实就是map集合的key”) 10.6 Param注解源码分析
做个了解
十一 小黑子的MyBatis查询专题
模块名mybatis-007-select 打包方式jar 引入依赖mysql驱动依赖、mybatis依赖、logback依赖、junit依赖。 引入配置文件jdbc.properties、mybatis-config.xml、logback.xml 创建pojo类Car 创建Mapper接口CarMapper 创建Mapper接口对应的映射文件com/powernode/mybatis/mapper/CarMapper.xml 创建单元测试CarMapperTest 拷贝工具类SqlSessionUtil
11.1 返回Car
当查询的结果有对应的实体类并且查询结果只有一条时
CarMapper.xml
?xml version1.0 encodingUTF-8 ?
!DOCTYPE mapperPUBLIC -//mybatis.org//DTD Mapper 3.0//ENhttp://mybatis.org/dtd/mybatis-3-mapper.dtd
mapper namespacecom.powernode.mybatis.mapper.CarMapperselect idselectById resultTypeCarselectid,car_num as carNum,brand,guide_price as guidePrice,produce_time as produceTime,car_type as carTypefrom t_carwhereid #{id}/select/mapperCarMapper接口
package com.powernode.mybatis.mapper;import com.powernode.mybatis.pojo.Car;public interface CarMapper {/*** 根据id主键查询结果最多只有一条* param id* return*/Car selectById(Long id);}测试
package com.powernode.mybatis.test;import com.powernode.mybatis.mapper.CarMapper;
import com.powernode.mybatis.pojo.Car;
import com.powernode.mybatis.utils.SqlSessionUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;public class CarMapperTest {Testpublic void testSelectById(){SqlSession sqlSession SqlSessionUtil.openSession();CarMapper mapper sqlSession.getMapper(CarMapper.class);Car car mapper.selectById(1L);System.out.println(car);sqlSession.commit();sqlSession.close();}} 当返回记录结果有多条却用单个实体类接收时
CarMapper接口
/** description: 根据品牌进行模糊查询* 查询的结果可能有多个但是采用一个POJO对象来接收的问题* param brand* version 1.0*/Car selectByBrandLike(String brand);CarMapper.xml
select idselectByBrandLike resultTypeCarselectid,car_num as carNum,brand,guide_price as guidePrice,produce_time as produceTime,car_type as carTypefrom t_carwherebrand like %#{brand}%/select测试 Testpublic void testSelectByBrandLike(){SqlSession sqlSession SqlSessionUtil.openSession();CarMapper mapper sqlSession.getMapper(CarMapper.class);//出现异常TooManyResultsException//什么意思你期望的结果是返回一条记录但是实际的SQL语句在执行的时候返回的记录条数不是一条是多条Car car mapper.selectByBrandLike(byd);System.out.println(car);sqlSession.commit();sqlSession.close();}11.2 返回List Car
一、采用List集合接收多条数据模糊查询
CarMappper接口
ListCar selectByBrandLike2(String brand);CarMapper.xml select idselectByBrandLike2 resultTypeCarselectid,car_num as carNum,brand,guide_price as guidePrice,produce_time as produceTime,car_type as carTypefrom t_carwherebrand like %#{brand}%/select测试
Testpublic void testSelectByBrandLike2(){SqlSession sqlSession SqlSessionUtil.openSession();CarMapper mapper sqlSession.getMapper(CarMapper.class);ListCar cars mapper.selectByBrandLike2(byd);cars.forEach(car - System.out.println(car));sqlSession.commit();sqlSession.close();} 二、 根据id查询Carid是主键。这个结果一定是一条不可能有多条数据。所以返回可以用List Car 集合进行接收。
CarMapper.xml select idselectById2 resultTypeCarselectid,car_num as carNum,brand,guide_price as guidePrice,produce_time as produceTime,car_type as carTypefrom t_carwhereid #{id}/selectCarMapper接口 /** description: 根据id查询Carid是主键。这个结果一定是一条不可能有多条数据。* param id* version 1.0*/ListCar selectById2(Long id);测试 Testpublic void testSelectById2(){SqlSession sqlSession SqlSessionUtil.openSession();CarMapper mapper sqlSession.getMapper(CarMapper.class);ListCar cars mapper.selectById2(2L);System.out.println(cars);sqlSession.commit();sqlSession.close();}11.3 返回Map
当返回的数据没有合适的实体类对应的话可以采用Map集合接收。字段名做key字段值做value。 查询如果可以保证只有一条数据则返回一个Map集合即可。
CarMappper.xml !-- resultTypejava.util.map有别名 map --select idselectByIdRetMap resultTypemapselect * from t_car where id #{id}/select
CarMapper接口 /** description: 根据id获取汽车信息。将汽车信息放到Map集合中* param id* version 1.0*/MapString,Object selectByIdRetMap(Long id);测试 Testpublic void testSelectByIdRetMap(){SqlSession sqlSession SqlSessionUtil.openSession();CarMapper mapper sqlSession.getMapper(CarMapper.class);MapString, Object car mapper.selectByIdRetMap(34L);System.out.println(car);sqlSession.commit();sqlSession.close();}11.3.1 返回List Map
查询结果条数大于等于1条数据则可以返回一个存储Map集合的List集合
resultMap“map”这是因为mybatis内置了很多别名。【参见mybatis开发手册】
CarMapper.xml !-- 这个resultType不是list是map--select idselectAllRetListMap resultTypemapselect * from t_car/selectCarMapper接口 /** 查询所有的Car信息返回一个存放Map集合的List集合* version 1.0*/ListMapString,Object selectAllRetListMap();测试 Testpublic void testSelectAllRetListMap(){SqlSession sqlSession SqlSessionUtil.openSession();CarMapper mapper sqlSession.getMapper(CarMapper.class);ListMapString, Object maps mapper.selectAllRetListMap();maps.forEach(map - System.out.println(map));sqlSession.commit();sqlSession.close();}当然如果返回一个Map集合可以将Map集合放到List集合中吗当然可以这里就不再测试了。 反过来如果返回的不是一条记录是多条记录的话只采用单个Map集合接收这样同样会出现之前的异常TooManyResultsException
11.4 返回MapString.Map
拿Car的id做key以后取出对应的Map集合时更加方便
CarMapper接口 /** 查询所有的Car返回一个大Map集合* Map集合的key是每条记录的主键值* map集合的value是每条记录*/MapKey(id)//将查询结果的id值作为整个大Map集合的keyMapLong,MapString,Object selectAllRetMap();测试
Testpublic void testSelectAllRetMap(){SqlSession sqlSession SqlSessionUtil.openSession();CarMapper mapper sqlSession.getMapper(CarMapper.class);MapLong, MapString, Object maps mapper.selectAllRetMap();System.out.println(maps);sqlSession.commit();sqlSession.close();}CarMapper.xml select idselectAllRetMap resultTypemapselect * from t_car/select
返回结果一个大Map集合
{1{car_num1001, id1, guide_price10, produce_time2022-10-11, brand宝马520, car_type燃油车},
2{car_num1111, id2, guide_price10, produce_time2020-11-11, brandbyd, car_type电车},
34{car_num9991, id34, guide_price40, produce_time2022-11-11, brand凯迪, car_type能源车},
3{car_num1111, id3, guide_price10, produce_time2020-11-11, brandbyd, car_type电车},
4{car_num9999, id4, guide_price30, produce_time1999-11-10, brandmagua, car_type旧能源},
6{car_num8888, id6, guide_price30, produce_time2000-11-66, brand法克鱿, car_type捞车},
7{car_num8888, id7, guide_price30, produce_time2000-11-66, brand法克鱿, car_type捞车},
8{car_num8888, id8, guide_price30, produce_time2000-11-66, brand法克鱿, car_type捞车},
9{car_num8888, id9, guide_price30, produce_time2000-11-66, brand法克鱿, car_type捞车},
10{car_num8888, id10, guide_price30, produce_time2000-11-66, brand法克鱿, car_type捞车},
15{car_num22222, id15, guide_price3, produce_time2022-10-1, brand小老板, car_type新能源}}11.5 resultMap结果映射
查询结果的列名和java对象的属性名对应不上怎么办
第一种方式as 给列起别名第二种方式使用resultMap进行结果映射第三种方式是否开启驼峰命名自动映射配置settings
使用resultMap进行结果映射
CarMapper接口 /*** 查询所有Car使用resultMap进行结果映射* return*/ListCar selectAllByResultMap();CarMapper.xml !--1.专门定义一个结果映射在这个结果映射当中指定数据库的字段名和Java类的属性名的对应关系2.type属性用来指定POJO类的类名3.id属性指定resultMap的唯一标识。这个id将来要在select标签中使用--resultMap idcarResultMap typeCar!--如果数据库表中有主键一般都是有主键要不然不符合数据库设计的第一范式--!--如果有主键建议这里配置一个id标签注意这不是必须的。但是官方的解释是什么这样配置可以让mybatis提供效率--id propertyid columnid/id!--property后面接的是POJO类的属性名--!--column后面接的是数据库的字段名--result propertycarNum columncar_num javaTypejava.lang.String jdbcTypeVARCHAR/!--如果column和property是一样的这个可以省略。--!-- result propertybrand columnbrand/--result propertyguidePrice columnguide_price/result propertyproduceTime columnproduce_time/result propertycarType columncar_type javaTypestring jdbcTypeVARCHAR//resultMap!--select标签的resultMap属性用来指定使用哪个结果映射。resultMap后面的值是resultMap的id--select idselectAllByResultMap resultMapcarResultMapselect * from t_car/select测试 Testpublic void testSelectAllByResultMap(){SqlSession sqlSession SqlSessionUtil.openSession();CarMapper mapper sqlSession.getMapper(CarMapper.class);ListCar cars mapper.selectAllByResultMap();cars.forEach(car - System.out.println(car));sqlSession.commit();sqlSession.close();}11.5.1 开启驼峰命名自动映射
使用这种方式的前提是属性名遵循Java的命名规范数据库表的列名遵循SQL的命名规范。 Java命名规范首字母小写后面每个单词首字母大写遵循驼峰命名方式。 SQL命名规范全部小写单词之间采用下划线分割。
比如以下的对应关系
实体类中的属性名数据库表的别名carNumcar_numcarTypecar_typeproduceTimeproduce_time
如何启用该功能在mybatis-config.xml文件中进行配置
!--放在properties标签后面--
settingssetting namemapUnderscoreToCamelCase valuetrue/
/settingsCarMapper接口
/**
* 查询所有Car启用驼峰命名自动映射
* return
*/
ListCar selectAllByMapUnderscoreToCamelCase();CarMapper.xml select idselectAllByMapUnderscoreToCamelCase resultTypecarselect * from t_car/select测试
Testpublic void testSelectAllByMapUnderscoreToCamelCase (){SqlSession sqlSession SqlSessionUtil.openSession();CarMapper mapper sqlSession.getMapper(CarMapper.class);ListCar cars mapper.selectAllByMapUnderscoreToCamelCase();cars.forEach(car - System.out.println(car));sqlSession.commit();sqlSession.close();}11.6返回总记录条数
需求查询总记录条数
CarMapper.xml select idselectTotal resultTypeLongselect count(*) from t_car/selectCarMapper接口 /** description: 获取Car的总记录条数* version 1.0*/Long selectTotal();测试 Testpublic void testSelectTotal(){SqlSession sqlSession SqlSessionUtil.openSession();CarMapper mapper sqlSession.getMapper(CarMapper.class);Long total mapper.selectTotal();System.out.println(total);sqlSession.commit();sqlSession.close();}十二 小黑子的动态SQL
什么是动态SQL 有的业务场景也需要SQL语句进行动态拼接例如 批量删除 12.1 if标签
if标签中test属性是必须的。if标签中test属性的值是false或者true。如果test是true则if标签中的sql语句就会拼接。反之则不会拼接。test属性中可以使用的是: 当使用了Param注解那么test中要出现的是Param注解指定的参数名。Param(brand)那么这里只能使用brand当没有使在这里插入代码片用Param注解那么test中要出现的是: param1 param2 param3 arg0 arg1 arg2. . . .当使用了POJO那么test中出现的是POJO类的属性名。 在mybatis的动态SQL当中不能使用只能使用and。
CarMapper.xml
?xml version1.0 encodingUTF-8 ?
!DOCTYPE mapperPUBLIC -//mybatis.org//DTD Mapper 3.0//ENhttp://mybatis.org/dtd/mybatis-3-mapper.dtd
mapper namespacecom.powernode.mybatis.mapper.CarMapperselect idselectByMultiCondition resultTypeCarselect * from t_carwhereif testbrand ! null and brand ! brand like %#{brand}%/ifif testguidePrice ! null and guidePrice ! and guide_price #{guidePrice}/ifif testcarType ! null and carType ! and car_type #{carType}/if/select
/mapperCarMapper接口
package com.powernode.mybatis.mapper;import com.powernode.mybatis.pojo.Car;
import org.apache.ibatis.annotations.Param;import java.util.List;public interface CarMapper {/** 多条件查询* param brand* param guidePrice* param carType* version 1.0*/ListCar selectByMultiCondition(Param(brand) String brand,Param(guidePrice) Double guidePrice, Param(carType) String carType);
}
测试
package com.powernode.mybatis.test;import com.powernode.mybatis.mapper.CarMapper;
import com.powernode.mybatis.pojo.Car;
import com.powernode.mybatis.utils.SqlSessionUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;import java.util.List;public class CarMapperTest {Testpublic void testSelectByMultiCondition(){SqlSession sqlSession SqlSessionUtil.openSession();CarMapper mapper sqlSession.getMapper(CarMapper.class);//假设三个条件都不是空ListCar cars mapper.selectByMultiCondition(byd, 2.0, 电车);cars.forEach(car - System.out.println(car));sqlSession.commit();sqlSession.close();}
} 12.2 where标签
where标签的作用让where子句更加动态智能。
所有条件都为空时where标签保证不会生成where子句。 自动去除某些条件前面多余的and或or。后面的无法去除 继续使用if标签中的需求。
CarMapper接口 /** description: where标签让where子句更加的智能* version 1.0*/ListCar selectByMultiConditionWithWhere(Param(brand) String brand,Param(guidePrice) Double guidePrice, Param(carType) String carType);CarMapper接口 select idselectByMultiConditionWithWhere resultTypeCarselect * from t_car!--where标签是专门负责where子句动态生成的--whereif testbrand ! null and brand ! brand like %#{brand}%/ifif testguidePrice ! null and guidePrice ! and guide_price #{guidePrice}/ifif testcarType ! null and carType ! and car_type #{carType}/if/where测试
Testpublic void selectByMultiConditionWithWhere(){SqlSession sqlSession SqlSessionUtil.openSession();CarMapper mapper sqlSession.getMapper(CarMapper.class);//假设三个条件都不是空
// ListCar cars mapper.selectByMultiConditionWithWhere(byd, 2.0, 电车);//假设三个条件都是空where标签可以自动判断是否符合条件
// ListCar cars mapper.selectByMultiConditionWithWhere(, null, );//假设一个条件为空where标签也会自动判断消去andListCar cars mapper.selectByMultiConditionWithWhere(, 2.0, 电车);cars.forEach(car - System.out.println(car));//但是后面两个条件是空时后面有andwhere标签就无法去除sqlSession.commit();sqlSession.close();}12.3 trim标签
trim标签的属性
prefix再trim标签中的语句前添加内容suffix再trim标签中的语句后添加内容prefixOverrides前缀覆盖掉去掉suffixOverrides后缀覆盖掉去掉 /** description: 使用trim标签* version 1.0*/ListCar selectByMultiConditionWithTrim(Param(brand) String brand,Param(guidePrice) Double guidePrice, Param(carType) String carType);select idselectByMultiConditionWithTrim resultTypeCar!--prefixwhere 是在trim标签所有内容的前面添加where--!--suffixOverridesand|or 把trim标签中内容的后缀and或or去掉以至于后面有and或or的话程序也不会报错--select * from t_cartrim prefixwhere suffixOverridesand|orif testbrand ! null and brand ! brand like %#{brand}% and/ifif testguidePrice ! null and guidePrice ! guide_price #{guidePrice} and/ifif testcarType ! null and carType ! car_type #{carType}/if/trim/selectTestpublic void testSelectByMultiConditionWithTrim(){SqlSession sqlSession SqlSessionUtil.openSession();CarMapper mapper sqlSession.getMapper(CarMapper.class);ListCar cars mapper.selectByMultiConditionWithTrim(, null, );cars.forEach(car - System.out.println(car));sqlSession.commit();sqlSession.close();}12.4 set标签
主要使用在update语句当中用来生成set关键字同时去掉最后多余的“,”
比如我们只更新提交的不为空的字段如果提交的数据是空或者那么这个字段我们将不更新。 /** description: 使用set标签* version 1.0*/int updateBySet(Car car);update idupdateBySetupdate t_carsetif testcarNum ! null and carNum ! car_num #{carNum},/ifif testbrand ! null and brand ! brand #{brand},/ifif testguidePrice ! null and guidePrice ! guide_price #{guidePrice},/ifif testproduceTime ! null and produceTime ! produce_time #{produceTime},/ifif testcarType ! null and carType ! car_type #{carType}/if/setwhereid #{id}/updateTestpublic void testUpdateBySet(){SqlSession sqlSession SqlSessionUtil.openSession();CarMapper mapper sqlSession.getMapper(CarMapper.class);Car car new Car(34L, null, 麻瓜霸道, null, null, null);mapper.updateBySet(car);sqlSession.commit();sqlSession.close();}12.5 chose when otherwise标签
这三个标签是在一起使用的
语法格式
choosewhen/whenwhen/whenwhen/whenotherwise/otherwise
/choose等同于
if(){}else if(){}else if(){}else if(){}else{}只有一个分支会被选择 案例需求先根据品牌查询如果没有提供品牌再根据指导价查询如果没有提供指导架构就根据生产日期查询 /*** 使用choose when otherwise标签查询* param brand* param guidePrice* param carType* return*/ListCar selectWithChoose(Param(brand) String brand, Param(guidePrice) Double guidePrice, Param(carType) String carType);select idselectWithChoose resultTypeCarselect * from t_carwherechoosewhen testbrand ! null and brand ! brand like %#{brand}%/whenwhen testguidePrice ! null and guidePrice ! guide_price #{guidePrice}/whenotherwisecar_type #{carType}/otherwise/choose/where/selectTestpublic void testSelectWithChoose(){SqlSession sqlSession SqlSessionUtil.openSession();CarMapper mapper sqlSession.getMapper(CarMapper.class);//三个条件都不为空
// ListCar cars mapper.selectWithChoose(麻瓜霸道, 1.0, 新能源);//第一个条件是空// ListCar cars mapper.selectWithChoose(null, 1.0, 新能源);//前两个条件是空// ListCar cars mapper.selectWithChoose(null, null, 新能源);//全都是空ListCar cars mapper.selectWithChoose(null, null, null);cars.forEach(car - System.out.println(car));sqlSession.commit();sqlSession.close();}12.6 foreach标签
循环数组或集合动态生成sql比如这样的SQL
批量删除
delete from t_car where id in(1,2,3);
delete from t_car where id 1 or id 2 or id 3;批量增加
insert into t_car values(null,1001,凯美瑞,35.0,2010-10-11,燃油车),(null,1002,比亚迪唐,31.0,2020-11-11,新能源),(null,1003,比亚迪宋,32.0,2020-10-11,新能源)12.6.1 批量删除
用in来删除 foreach标签属性 collection集合或数组item集合或数组中的元素separator循环之间的分隔符openforeach标签中所有内容的开始closeforeach标签中所有内容的结束 /*** 通过foreach完成批量删除* param ids* return*/int deleteBatchByForeach(Param(ids) Long[] ids);delete iddeleteBatchByForeachdelete from t_car where id inforeach collectionids itemid separator, open( close)#{id}/foreach/deleteTestpublic void testDeleteBatchByForeach(){SqlSession sqlSession SqlSessionUtil.openSession();CarMapper mapper sqlSession.getMapper(CarMapper.class);Long[] ids {8L,9L,10L};mapper.deleteBatchByForeach(ids);sqlSession.commit();sqlSession.close();}用or来删除 delete iddeleteById2 delete from t_car whereforeach collectionids itemid separatororid #{id}/foreach/delete/*** 通过id批量删除使用or关键字* param ids* return*/int deleteById2(Param(ids) Long[] ids);Testpublic void testDeleteById2(){SqlSession sqlSession SqlSessionUtil.openSession();CarMapper mapper sqlSession.getMapper(CarMapper.class);Long[] ids {35L,36L,37L};int count mapper.deleteById2(ids);System.out.println(count);sqlSession.commit();sqlSession.close();}12.6.2 批量插入 insert idinsertBatchinsert into t_car valuesforeach collectioncars itemcar separator,(null,#{car.carNum},#{car.brand},#{car.guidePrice},#{car.produceTime},#{car.carType})/foreach/insert/*** 通过foreach批量插入一次插入多条car信息* param cars* return*/int insertBatch(Param(cars) ListCar cars);Testpublic void testInsertBatchByForeach(){CarMapper mapper SqlSessionUtil.openSession().getMapper(CarMapper.class);Car car1 new Car(null, 2001, 兰博基尼, 100.0, 1998-10-11, 燃油车);Car car2 new Car(null, 2001, 兰博基尼, 100.0, 1998-10-11, 燃油车);Car car3 new Car(null, 2001, 兰博基尼, 100.0, 1998-10-11, 燃油车);ListCar cars Arrays.asList(car1, car2, car3);int count mapper.insertBatch(cars);System.out.println(插入了几条记录 count);SqlSessionUtil.openSession().commit();}12.7 sql标签和include标签
sql标签用来声明sql片段
include标签用来将声明的sql片段包含到某个sql语句当中
作用代码复用。易维护。
!--声明一个SQL片段--
sql idcarColsid,car_num as carNum,brand,guide_price as guidePrice,produce_time as produceTime,car_type as carType
/sqlselect idselectAllRetMap resultTypemapselect !--将声明的sql片段包含进来--include refidcarCols/ from t_car
/selectselect idselectAllRetListMap resultTypemapselect include refidcarCols/ carType from t_car
/selectselect idselectByIdRetMap resultTypemapselect include refidcarCols/ from t_car where id #{id}
/select
