mysql.mybatis动态sql_Mybatis的动态SQL

最新推荐文章于 2023-07-05 19:17:17 发布

阿荣田Toronto

最新推荐文章于 2023-07-05 19:17:17 发布

阅读量74

点赞数

文章标签： mysql.mybatis动态sql

本文链接：https://blog.csdn.net/weixin_27322123/article/details/114339291

版权

1 简介

动态SQL是Mybatis强大特性之一。极大的简化我们拼装SQL的操作。

动态SQL元素和使用JSTL或其他类似基于XML的文本处理器相似。

Mybatis采用功能强大的基于OGNL的表达式来简化操作。

choose(when、otherwise)

trim(where、set)

foreach

2 环境搭建

导入相关jar包的Maven坐标：

org.mybatis

mybatis

3.4.1

log4j

1.2.17

mysql

mysql-connector-java

8.0.21

junit

4.13

test

sql脚本

DROP TABLE IF EXISTS `employee`;

CREATE TABLE `employee` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`last_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,

`gender` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,

`email` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,

PRIMARY KEY (`id`) USING BTREE

) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

db.propreties

jdbc.url=jdbc:mysql://192.168.134.100:3306/test?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&useSSL=false&serverTimezone=GMT%2B8&allowPublicKeyRetrieval=true

jdbc.driverClass=com.mysql.cj.jdbc.Driver

jdbc.username=root

jdbc.password=123456

log4j.xml

Employee.java

package com.sunxiaping.domain;

import org.apache.ibatis.type.Alias;

@Alias("emp")

public class Employee {

private Integer id;

private String lastName;

private String email;

private String gender;

public Integer getId() {

return id;

}

public void setId(Integer id) {

this.id = id;

}

public String getLastName() {

return lastName;

}

public void setLastName(String lastName) {

this.lastName = lastName;

}

public String getEmail() {

return email;

}

public void setEmail(String email) {

this.email = email;

}

public String getGender() {

return gender;

}

public void setGender(String gender) {

this.gender = gender;

}

@Override

public String toString() {

return "Employee{" +

"id=" + id +

", lastName='" + lastName + '\'' +

", email='" + email + '\'' +

", gender='" + gender + '\'' +

'}';

}

EmployeeMapper.java

package com.sunxiaping.mapper;

public interface EmployeeMapper {

}

EmployeeMapper.xml

/p>

PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"

"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

mybatis-config.xml

/p>

PUBLIC "-//mybatis.org//DTD Config 3.0//EN"

"http://mybatis.org/dtd/mybatis-3-config.dtd">

3 if判断

示例：

EmployeeMapper.java

package com.sunxiaping.mapper;

import com.sunxiaping.domain.Employee;

import java.util.List;

public interface EmployeeMapper {

List getEmpsByConditionIf(Employee employee);

}

EmployeeMapper.xml

/p>

PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"

"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

SELECT id as id,last_name as lastName,gender as gender,email as email

FROM employee

WHERE 1 = 1

AND id = #{id,jdbcType=INTEGER}

AND last_name LIKE #{lastName,jdbcType=VARCHAR}

AND gender = #{gender,jdbcType=VARCHAR}

AND email = #{email,jdbcType=VARCHAR}

测试：

package com.sunxiaping;

import com.sunxiaping.domain.Employee;

import com.sunxiaping.mapper.EmployeeMapper;

import org.apache.ibatis.io.Resources;

import org.apache.ibatis.session.SqlSession;

import org.apache.ibatis.session.SqlSessionFactory;

import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import org.junit.After;

import org.junit.Before;

import org.junit.Test;

import java.io.IOException;

import java.io.InputStream;

import java.util.List;

public class EmployeeTest {

SqlSessionFactory sqlSessionFactory = null;

SqlSession sqlSession = null;

EmployeeMapper employeeMapper = null;

@Before

public void before() throws IOException {

String resource = "mybatis-config.xml";

InputStream inputStream = Resources.getResourceAsStream(resource);

sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

sqlSession = sqlSessionFactory.openSession(true);

employeeMapper = sqlSession.getMapper(EmployeeMapper.class);

}

@After

public void after() {

if (null != sqlSession) {

sqlSession.close();

}

@Test

public void testFindById() {

Employee example = new Employee();

example.setId(1);

example.setGender("男");

List employeeList = employeeMapper.getEmpsByConditionIf(example);

System.out.println("employeeList = " + employeeList);

}

4 where查询条件

如果查询条件中有多个AND或OR语句，Mybatis推荐使用where标签，其内部会自动将多余的AND或OR去掉。当然，where标签强制类似AND xxx=xxx，AND xxx=xxx的方式。

示例：

EmployeeMapper.java

package com.sunxiaping.mapper;

import com.sunxiaping.domain.Employee;

import java.util.List;

public interface EmployeeMapper {

List getEmpsByConditionWhere(Employee employee);

}

EmployeeMapper.xml

/p>

PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"

"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

SELECT id as id,last_name as lastName,gender as gender,email as email

FROM employee

id = #{id,jdbcType=INTEGER}

AND last_name LIKE #{lastName,jdbcType=VARCHAR}

AND gender = #{gender,jdbcType=VARCHAR}

AND email = #{email,jdbcType=VARCHAR}

测试：

package com.sunxiaping;

import com.sunxiaping.domain.Employee;

import com.sunxiaping.mapper.EmployeeMapper;

import org.apache.ibatis.io.Resources;

import org.apache.ibatis.session.SqlSession;

import org.apache.ibatis.session.SqlSessionFactory;

import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import org.junit.After;

import org.junit.Before;

import org.junit.Test;

import java.io.IOException;

import java.io.InputStream;

import java.util.List;

public class EmployeeTest {

SqlSessionFactory sqlSessionFactory = null;

SqlSession sqlSession = null;

EmployeeMapper employeeMapper = null;

@Before

public void before() throws IOException {

String resource = "mybatis-config.xml";

InputStream inputStream = Resources.getResourceAsStream(resource);

sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

sqlSession = sqlSessionFactory.openSession(true);

employeeMapper = sqlSession.getMapper(EmployeeMapper.class);

}

@After

public void after() {

if (null != sqlSession) {

sqlSession.close();

}

@Test

public void testFindById() {

Employee example = new Employee();

example.setGender("男");

List employeeList = employeeMapper.getEmpsByConditionWhere(example);

System.out.println("employeeList = " + employeeList);

}

5 trim自定义字符串截取

上面的where标签有限制，一旦使用了类似xxx=xxx AND,xxx=xxx AND的情况，where标签就失效了，这个时候可以使用trim自定义字符串截取，当前，trim标签也可以模拟出where标签的效果。

示例：

EmployeeMapper.java

package com.sunxiaping.mapper;

import com.sunxiaping.domain.Employee;

import java.util.List;

public interface EmployeeMapper {

List getEmpsByConditionTrim(Employee employee);

}

EmployeeMapper.xml

/p>

PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"

"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

SELECT id as id,last_name as lastName,gender as gender,email as email

FROM employee

id = #{id,jdbcType=INTEGER} AND

last_name LIKE #{lastName,jdbcType=VARCHAR} AND

gender = #{gender,jdbcType=VARCHAR} AND

email = #{email,jdbcType=VARCHAR}

测试：

package com.sunxiaping;

import com.sunxiaping.domain.Employee;

import com.sunxiaping.mapper.EmployeeMapper;

import org.apache.ibatis.io.Resources;

import org.apache.ibatis.session.SqlSession;

import org.apache.ibatis.session.SqlSessionFactory;

import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import org.junit.After;

import org.junit.Before;

import org.junit.Test;

import java.io.IOException;

import java.io.InputStream;

import java.util.List;

public class EmployeeTest {

SqlSessionFactory sqlSessionFactory = null;

SqlSession sqlSession = null;

EmployeeMapper employeeMapper = null;

@Before

public void before() throws IOException {

String resource = "mybatis-config.xml";

InputStream inputStream = Resources.getResourceAsStream(resource);

sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

sqlSession = sqlSessionFactory.openSession(true);

employeeMapper = sqlSession.getMapper(EmployeeMapper.class);

}

@After

public void after() {

if (null != sqlSession) {

sqlSession.close();

}

@Test

public void testFindById() {

Employee example = new Employee();

example.setGender("男");

List employeeList = employeeMapper.getEmpsByConditionTrim(example);

System.out.println("employeeList = " + employeeList);

}

6 choose分支选择

有点类似Java中的switch-case语句。

示例：如果查询条件中有id，就用id查询;如果查询条件中有lastName，就用lastName查询;如果查询条件中有gender，就用gener查询;否则，用emial查询。

EmployeeMapper.java

package com.sunxiaping.mapper;

import com.sunxiaping.domain.Employee;

import java.util.List;

public interface EmployeeMapper {

List getEmpsByConditionChoose(Employee employee);

}

EmployeeMapper.xml

/p>

PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"

"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

SELECT id as id,last_name as lastName,gender as gender,email as email

FROM employee

id = #{id,jdbcType=INTEGER}

last_name = #{lastName,jdbcType=VARCHAR}

gender = #{gender,jdbcType=VARCHAR}

email = #{email,jdbcType=VARCHAR}

7 set更新

set标签一般结合if标签实现动态更新。Mybatis中的set标签会自动将最右边多出的“,”去掉。

示例：

EmployeeMapper.java

package com.sunxiaping.mapper;

import com.sunxiaping.domain.Employee;

public interface EmployeeMapper {

void updateEmployee(Employee employee);

}

EmployeeMapper.xml

/p>

PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"

"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

UPDATE employee

last_name = #{lastName,jdbcType=VARCHAR},

gender = #{gender,jdbcType=VARCHAR},

email = #{email,jdbcType=VARCHAR}

id = #{id,jdbcType=INTEGER}

8 foreach遍历

foreach标签类似于jstl中的标签，都是迭代一个集合中的对象。

示例：

EmployeeMapper.java

package com.sunxiaping.mapper;

import com.sunxiaping.domain.Employee;

import org.apache.ibatis.annotations.Param;

import java.util.List;

public interface EmployeeMapper {

List findEmpsByConditionForeach(@Param("ids") List ids);

}

EmployeeMapper.xml

/p>

PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"

"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

SELECT id as id,last_name as lastName,gender as gender,email as email

FROM employee

#{id,jdbcType=INTEGER}

9 MySQL下的foreach批量插入的两种方式

第一种批量插入数据的SQL方式如下：

INSERT INTO 表名(字段1,字段2,...) VALUES (值1,值2,...),(值1,值2,...);

示例：

EmployeeMapper.java

package com.sunxiaping.mapper;

import com.sunxiaping.domain.Employee;

import org.apache.ibatis.annotations.Param;

import java.util.List;

public interface EmployeeMapper {

void batchInsertEmps(@Param("emps") List employees);

}

EmployeeMapper.xml

/p>

PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"

"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

(#{emp.lastName,jdbcType=VARCHAR},#{emp.gender,jdbcType=VARCHAR},#{emp.email,jdbcType=VARCHAR})

第二种批量插入数据的SQL方式如下：

INSERT INTO 表名(字段1,字段2,...) VALUES (值1,值2,...);

但是，在JDBC操纵MySQL的时候，需要在jdbc.url上加上“allowMultiQueries=true”参数，当然这个参数也可以用于批量删除、批量更新。

示例：

db.properties

jdbc.url=jdbc:mysql://192.168.134.100:3306/test?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&useSSL=false&serverTimezone=GMT%2B8&allowPublicKeyRetrieval=true&allowMultiQueries=true

jdbc.driverClass=com.mysql.cj.jdbc.Driver

jdbc.username=root

jdbc.password=123456

EmployeeMapper.java

package com.sunxiaping.mapper;

import com.sunxiaping.domain.Employee;

import org.apache.ibatis.annotations.Param;

import java.util.List;

public interface EmployeeMapper {

void batchInsertEmps(@Param("emps") List employees);

}

EmployeeMapper.xml

/p>

PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"

"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

INSERT INTO employee (last_name,gender,email) VALUES (#{emp.lastName,jdbcType=VARCHAR},#{emp.gender,jdbcType=VARCHAR},#{emp.email,jdbcType=VARCHAR})

10 Oracle下的foreach批量插入的两种方式

第一种批量插入数据的SQL方式如下：

begin

INSERT INTO 表名(字段1,字段2,...) VALUES (值1,值2,...);

end;

示例：

EmployeeMapper.java

package com.sunxiaping.mapper;

import com.sunxiaping.domain.Employee;

import org.apache.ibatis.annotations.Param;

import java.util.List;

public interface EmployeeMapper {

void batchInsertEmps(@Param("emps") List employees);

}

EmployeeMapper.xml

/p>

PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"

"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

INSERT INTO employee (id,last_name,gender,email) VALUES (#{employee_seq.nextval},#{emp.lastName,jdbcType=VARCHAR},#{emp.gender,jdbcType=VARCHAR},#{emp.email,jdbcType=VARCHAR});

第二种批量插入数据的SQL方式如下：

INSERT INTO 表名(字段1,字段2,...)

SELECT 别名1,别名2,... FROM (

SELECT 值1 别名1,值2 别名2,... FROM dual

UNION

SELECT 值1 别名1,值2 别名2<?xml version="1.0" encoding="UTF-8" ?>

/p>

PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"

"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

SELECT #{emp.lastName,jdbcType=VARCHAR} lastName,#{emp.gender,jdbcType=VARCHAR} gender,#{emp.email,jdbcType=VARCHAR} email FROM dual

,... FROM dual

)

示例：

EmployeeMapper.java

package com.sunxiaping.mapper;

import com.sunxiaping.domain.Employee;

import org.apache.ibatis.annotations.Param;

import java.util.List;

public interface EmployeeMapper {

void batchInsertEmps(@Param("emps") List employees);

}

EmployeeMapper.xml

/p>

PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"

"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

SELECT #{emp.lastName,jdbcType=VARCHAR} lastName,#{emp.gender,jdbcType=VARCHAR} gender,#{emp.email,jdbcType=VARCHAR} email FROM dual

11 内置参数

Mybatis不只是方法传递过来的参数可以用来判断，取值…，Mybatis默认还有两个默认的参数：_parameter和_databaseId。

_parameter：代表整个参数。

如果是单个参数：_parameter就是这个参数。

如果是多个参数：参数会封装到一个Map中，_parameter就是代表这个Map。

_databaseId：如果在全局配置文件中配置了databaseIdProvider标签，那么_databaseId就是代表当前数据库的别名。

示例：

mybatis-config.xml

/p>

PUBLIC "-//mybatis.org//DTD Config 3.0//EN"

"http://mybatis.org/dtd/mybatis-3-config.dtd">

EmployeeMapper.java

package com.sunxiaping.mapper;

import com.sunxiaping.domain.Employee;

import java.util.List;

public interface EmployeeMapper {

List findByBuiltParameter();

}

EmployeeMapper.xml

/p>

PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"

"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

SELECT id as id,last_name as lastName,email as email ,gender as gender

FROM employee

LIMIT 0,5

SELECT * FROM

(SELECT e.*,rownum as r1 FROM employee e WHERE rownum < 5)

WHERE r1 > 1

12 bind绑定

bind标签可以从OGNL表达式中创建一个变量并将其绑定到上下文。

示例：

EmployeeMapper.java

package com.sunxiaping.mapper;

import com.sunxiaping.domain.Employee;

import org.apache.ibatis.annotations.Param;

import java.util.List;

public interface EmployeeMapper {

List findByLastNameLike(@Param("lastName") String lastName);

}

EmployeeMapper.xml

/p>

PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"

"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

SELECT id as id,last_name as lastName,email as email ,gender as gender

FROM employee

WHERE last_name like #{_lastName,jdbcType=VARCHAR}

测试：

package com.sunxiaping;

import com.sunxiaping.domain.Employee;

import com.sunxiaping.mapper.EmployeeMapper;

import org.apache.ibatis.io.Resources;

import org.apache.ibatis.session.SqlSession;

import org.apache.ibatis.session.SqlSessionFactory;

import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import org.junit.After;

import org.junit.Before;

import org.junit.Test;

import java.io.IOException;

import java.io.InputStream;

import java.util.List;

public class EmployeeTest {

SqlSessionFactory sqlSessionFactory = null;

SqlSession sqlSession = null;

EmployeeMapper employeeMapper = null;

@Before

public void before() throws IOException {

String resource = "mybatis-config.xml";

InputStream inputStream = Resources.getResourceAsStream(resource);

sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

sqlSession = sqlSessionFactory.openSession(true);

employeeMapper = sqlSession.getMapper(EmployeeMapper.class);

}

@After

public void after() {

if (null != sqlSession) {

sqlSession.close();

}

@Test

public void testFindById() {

List employeeList = employeeMapper.findByLastNameLike("a");

System.out.println("employeeList = " + employeeList);

}

13 抽取可重用的SQL片段

sql标签可以抽取可重用的SQL片段，方便后面引用。

示例：

EmployeeMapper.java

package com.sunxiaping.mapper;

import com.sunxiaping.domain.Employee;

import org.apache.ibatis.annotations.Param;

import java.util.List;

public interface EmployeeMapper {

List findByLastNameLike(@Param("lastName") String lastName);

}

EmployeeMapper.xml

/p>

PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"

"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

id as id,last_name as lastName,email as email ,gender as gender

SELECT

FROM employee

WHERE last_name like #{_lastName,jdbcType=VARCHAR}

阿荣田Toronto

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql.mybatis动态sql_Mybatis的动态SQL

1 简介动态SQL是Mybatis强大特性之一。极大的简化我们拼装SQL的操作。动态SQL元素和使用JSTL或其他类似基于XML的文本处理器相似。Mybatis采用功能强大的基于OGNL的表达式来简化操作。ifchoose(when、otherwise)trim(where、set)foreach2 环境搭建导入相关jar包的Maven坐标：org.mybatismybatis3.4.1log4j...
复制链接

扫一扫