mybatis-动态SQL
基本介绍
环境
/*
Navicat Premium Data Transfer
Source Server : localhost
Source Server Type : MySQL
Source Server Version : 80022
Source Host : localhost:3306
Source Schema : school
Target Server Type : MySQL
Target Server Version : 80022
File Encoding : 65001
Date: 18/11/2021 12:20:05
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
`id` int(0) NOT NULL AUTO_INCREMENT,
`tea_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
`tea_age` int(0) NULL DEFAULT NULL,
`tea_email` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES (1, 'tom', 26, 'tom@qq.com');
INSERT INTO `teacher` VALUES (2, 'lili', 22, 'lili@qq.com');
INSERT INTO `teacher` VALUES (3, 'lucy', 22, 'lucy@qq.com');
INSERT INTO `teacher` VALUES (4, 'lisi', 22, 'lisi@qq.com');
SET FOREIGN_KEY_CHECKS = 1;
package com.fly.entity;
/**
* @author 26414
*/
public class Teacher {
private Integer id;
private String teaName;
private Integer teaAge;
private String teaEmail;
@Override
public String toString() {
return "Teacher{" +
"id=" + id +
", teaName='" + teaName + '\'' +
", teaAge=" + teaAge +
", teaEmail='" + teaEmail + '\'' +
'}';
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getTeaName() {
return teaName;
}
public void setTeaName(String teaName) {
this.teaName = teaName;
}
public Integer getTeaAge() {
return teaAge;
}
public void setTeaAge(Integer teaAge) {
this.teaAge = teaAge;
}
public String getTeaEmail() {
return teaEmail;
}
public void setTeaEmail(String teaEmail) {
this.teaEmail = teaEmail;
}
}
package com.fly.dao;
/**
* @author 26414
*/
public interface TeacherMapper {
}
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE log4j:configuration SYSTEM "log4j.dtd">
<log4j:configuration>
<appender name="STDOUT" class="org.apache.log4j.ConsoleAppender">
<param name="Encoding" value="UTF-8" />
<layout class="org.apache.log4j.PatternLayout">
<param name="ConversionPattern" value="%-5p %d{MM-dd HH:mm:ss,SSS} %m (%F:%L) \n" />
</layout>
</appender>
<logger name="java.sql">
<level value="debug" />
</logger>
<logger name="org.apache.ibatis">
<level value="info" />
</logger>
<root>
<level value="debug" />
<appender-ref ref="STDOUT" />
</root>
</log4j:configuration>
db.driver=com.mysql.cj.jdbc.Driver
db.url=jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2B8&allowPublicKeyRetrieval=true
db.username=root
db.password=123456
#dbconfig.properties
<?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.fly.dao.TeacherMapper">
<!--TeacherMapper.xml-->
</mapper>
<?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>
<properties resource="dbconfig.properties"/>
<settings>
<!--开启驼峰命名自动映射-->
<!--mybatis-config.xml-->
<setting name="mapUnderscoreToCamelCase" value="true"/>
<setting name="lazyLoadingEnabled" value="trie"/>
</settings>
<typeAliases>
<package name="com.fly.entity"/>
</typeAliases>
<environments default="development">
<!--每个environment是一个环境，default属性指向使用哪个环境-->
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${db.driver}"/>
<property name="url" value="${db.url}"/>
<property name="username" value="${db.username}"/>
<property name="password" value="${db.password}"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="mapper/TeacherMapper.xml"/>
</mappers>
</configuration>
if
/**
* TeacherMapper
* if标签
* @param teacher 老师对象
* @return 符合条件的数据
*/
List<Teacher> getTeaIf(Teacher teacher);
<!--TeacherMapper.xml-->
<!--
test:OGNL表达式
-->
<select id="getTeaIf" resultType="com.fly.entity.Teacher">
select * from teacher where
<if test="id != null">
id = #{id}
</if>
<if test="teaAge != null">
and tea_age = #{teaAge}
</if>
</select>
@Test
public void test1() throws IOException {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
Teacher teacher = new Teacher();
teacher.setId(1);
List<Teacher> teachers = mapper.getTeaIf(teacher);
System.out.println("teachers = " + teachers);
}
}
where
刚才的查询语句还有点问题，如果id没有带，sql语句为：select * from teacher where and tea_age = ?，这样肯定就会报错
<!--TeacherMapper.xml-->
<!--
test:OGNL表达式
-->
<select id="getTeaIf" resultType="com.fly.entity.Teacher">
select * from teacher
<where>
<if test="id != null">
id = #{id}
</if>
<if test="teaAge != null">
and tea_age = #{teaAge}
</if>
</where>
</select>
set
/**
* TeacherMapper
* set标签
* @param teacher 老师对象
*/
void updateTeaSet(Teacher teacher);
<!--TeacherMapper.xml-->
<update id="updateTeaSet">
update teacher
<set>
<if test="teaName != null">
tea_name = #{teaName},
</if>
<if test="teaAge != null">
tea_age = #{teaAge},
</if>
<if test="teaEmail != null">
tea_email = #{teaEmail}
</if>
</set>
where id = #{id}
</update>
trim
如果and放在语句的后面，where标签解决不了，可以使用trim标签
/**
* TeacherMapper
* trim标签
* @param teacher 老师对象
* @return 符合条件的数据
*/
List<Teacher> getTeaTrim(Teacher teacher);
<!--
TeacherMapper.xml
prefix="":前缀：trim标签体中是整个字符串拼串后的结果。
prefix给拼串后的整个字符串加一个前缀
prefixOverrides="":
前缀覆盖： 去掉整个字符串前面多余的字符
suffix="":后缀
suffix给拼串后的整个字符串加一个后缀
suffixOverrides=""
后缀覆盖：去掉整个字符串后面多余的字符
-->
<select id="getTeaTrim" resultType="com.fly.entity.Teacher">
select * from teacher
<trim prefix="where" prefixOverrides="and">
<if test="id != null">
id = #{id} and
</if>
<if test="teaAge != null">
tea_age = #{teaAge}
</if>
</trim>
</select>
choose
/**
* TeacherMapper
* choose标签
* @param teacher 老师对象
* @return 符合条件的数据
*/
List<Teacher> getTeaChoose(Teacher teacher);
<!--TeacherMapper.xml-->
<select id="getTeaChoose" resultType="com.fly.entity.Teacher">
select * from teacher
<where>
<choose>
<when test="id != null">
id = #{id}
</when>
<when test="teaAge != null">
tea_age = #{teaAge}
</when>
<otherwise>
1=1
</otherwise>
</choose>
</where>
</select>
可以看到，id和age都有值，但是只会拼接id
foreach
批量查询
/**
* TeacherMapper
* 查询id在集合中的老师
* @param list id集合
* @return id在集合中的老师
*/
List<Teacher> getTeachersForeach(List<Integer> list);
<!--TeacherMapper.xml-->
<select id="getTeachersForeach" resultType="com.fly.entity.Teacher">
select * from teacher
<foreach collection="list" item="item" separator=","
open="where id in(" close=")">
#{item}
</foreach>
</select>
批量保存
/**
* TeacherMapper
* 批量添加
* @param teachers 老师集合
*/
void addTeachers(@Param("teachers") List<Teacher> teachers);
<!--TeacherMapper.xml-->
<insert id="addTeachers">
insert into teacher(tea_name,tea_age,tea_email) values
<foreach collection="teachers" item="teacher" separator=",">
(#{teacher.teaName},#{teacher.teaAge},#{teacher.teaEmail})
</foreach>
</insert>
内置参数
<!--mybatis-config.xml-->
<databaseIdProvider type="DB_VENDOR">
<property name="MySQL" value="mysql"/>
<property name="Oracle" value="oracle"/>
</databaseIdProvider>
script
bind
抽取可重用sql
<!--TeacherMapper.xml-->
<sql id="insertColumn">
tea_name,tea_age,tea_email
</sql>
<select id="getTeachersForeach" resultType="com.fly.entity.Teacher">
select <include refid="insertColumn"/> from teacher
<foreach collection="list" item="item" separator=","
open="where id in(" close=")">
#{item}
</foreach>
</select>
浙公网安备 33010602011771号