Java：MyBatis动态SQL实践
【摘要】 项目结构
$ tree
.
├── README.md
├── pom.xml
└── src ├── main │ ├── java │ │ └── com │ │ └── mouday │ │ ├── mapper │ │ │ ├── PersonMapper.java │ │ │ └── PersonMapper.xml │ ...
项目结构
$ tree
.
├── README.md
├── pom.xml
└── src ├── main │ ├── java │ │ └── com │ │ └── mouday │ │ ├── mapper │ │ │ ├── PersonMapper.java │ │ │ └── PersonMapper.xml │ │ ├── pojo │ │ │ └── Person.java │ │ └── util │ │ └── MyBatisUtil.java │ └── resources │ ├── db.properties │ ├── mybatis-config.xml │ └── sql │ └── person.sql └── test └── java └── com └── mouday └── PersonTest.java
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
依赖 pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>com.mouday</groupId> <artifactId>mybatis-demo</artifactId> <version>1.0-SNAPSHOT</version> <build> <resources> <!--编译src/main/java目录下的xml文件--> <resource> <directory>src/main/java</directory> <includes> <include>**/*.xml</include> </includes> <filtering>true</filtering> </resource> </resources> </build> <dependencies> <dependency> <groupId>org.mybatis</groupId> <artifactId>mybatis</artifactId> <version>3.5.5</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.20</version> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <version>1.18.12</version> <scope>provided</scope> </dependency> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.11</version> <scope>test</scope> </dependency> </dependencies>
</project>
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
数据库配置
db.properties
driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/data
username=root
password=123456
- 1
- 2
- 3
- 4
- 5
MyBatis配置 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> <!-- 加载数据库配置 --> <properties resource="db.properties" /> <!-- 打印sql日志 --> <settings> <setting name="logImpl" value="STDOUT_LOGGING"/> </settings> <!-- 指定整个包下的类都是别名 --> <typeAliases> <package name="com.mouday.pojo"/> </typeAliases> <environments default="development"> <environment id="development"> <transactionManager type="JDBC"/> <dataSource type="POOLED"> <property name="driver" value="${driver}"/> <property name="url" value="${url}"/> <property name="username" value="${username}"/> <property name="password" value="${password}"/> </dataSource> </environment> </environments> <mappers> <mapper class="com.mouday.mapper.PersonMapper"/> </mappers>
</configuration>
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
建表插入数据 person.sql
-- 建表
create table person( id int not null primary key auto_increment, name varchar(20), age int
);
-- 插入数据
insert into person(name, age) values('刘备', 25);
insert into person(name, age) values('关羽', 24);
insert into person(name, age) values('张飞', 23);
insert into person(name, age) values('曹操', 25);
insert into person(name, age) values('许褚', 24);
insert into person(name, age) values('孙权', 25);
insert into person(name, age) values('周瑜', 24);
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
工具类 MyBatisUtil.java
package com.mouday.util;
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 java.io.IOException;
import java.io.InputStream;
public class MyBatisUtil { private static SqlSessionFactory factory = null; // 使用static静态代码块，随着类的加载而加载，只执行一次 static { try { String resource = "mybatis-config.xml"; // 加载MyBatis的主配置文件 InputStream inputStream = Resources.getResourceAsStream(resource); // 通过构建器（SqlSessionFactoryBuilder）构建一个SqlSessionFactory工厂对象 factory = new SqlSessionFactoryBuilder().build(inputStream); } catch (Exception e) { e.printStackTrace(); } } public static SqlSession getSqlSession() throws IOException { return factory.openSession(); }
}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
实体Person.java
package com.mouday.pojo;
import lombok.Getter;
import lombok.Setter;
@Setter
@Getter
public class Person { private Integer id; private String name; private Integer age;
}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
mapper接口 PersonMapper.java
package com.mouday.mapper;
import com.mouday.pojo.Person;
import java.util.List;
public interface PersonMapper { List<Person> selectAll(); /** * 根据输入的信息进行条件检索 * 1. 当只输入用户名时， 使用用户名进行 【模糊检索】 * 2. 当只输入年龄时， 使用性别进行 【完全匹配】 * 3. 当用户名和年龄都存在时， 用这两个条件进行查询匹配的用 */ List<Person> selectByPersonSelective(Person person); /** * 更新非空属性 */ int updateByPrimaryKeySelective(Person person); /** * 插入非空字段 */ int insertSelective(Person person); /** * 当 name 没有值时， 使用 name 进行查询 * 否则使用 id 进行查询 */ List<Person> selectByNameOrId(Person person);
}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
mapper映射文件 PersonMapper.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.mouday.mapper.PersonMapper"> <sql id="Base_Column_List"> id, name, age </sql> <select id="selectAll" resultType="Person"> select <include refid="Base_Column_List"/> from person </select> <select id="selectByPersonSelective" resultType="Person" parameterType="Person"> select <include refid="Base_Column_List" /> from person <where> <if test="name != null and name !=''"> and name like concat('%', #{name}, '%') </if> <if test="age != null"> and age=#{age} </if> </where> </select> <update id="updateByPrimaryKeySelective" parameterType="Person"> update person <set> <if test="name != null"> `name` = #{name,jdbcType=VARCHAR}, </if> <if test="age != null"> `age` = #{age,jdbcType=INTEGER}, </if> </set> where id = #{id,jdbcType=INTEGER} </update> <insert id="insertSelective" parameterType="Person"> insert into person <trim prefix="(" suffix=")" suffixOverrides=","> <if test="id != null"> id, </if> <if test="name != null"> `name`, </if> <if test="age != null"> age, </if> </trim> <trim prefix="values (" suffix=")" suffixOverrides=","> <if test="id != null"> #{id,jdbcType=INTEGER}, </if> <if test="name != null"> #{name,jdbcType=VARCHAR}, </if> <if test="age != null"> #{age,jdbcType=INTEGER}, </if> </trim> </insert> <select id="selectByNameOrId" resultType="Person" parameterType="Person"> select <include refid="Base_Column_List" /> from person where 1=1 <choose> <when test="id != null"> and id=#{id} </when> <otherwise> and name=#{name} </otherwise> </choose> </select>
</mapper>
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
测试文件PersonTest.java
package com.mouday;
import com.mouday.mapper.PersonMapper;
import com.mouday.pojo.Person;
import com.mouday.util.MyBatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import java.io.IOException;
public class PersonTest { private SqlSession session; private PersonMapper mapper; @Before public void init() throws IOException { this.session = MyBatisUtil.getSqlSession(); this.mapper = this.session.getMapper(PersonMapper.class); } @After public void destroy() { this.session.close(); } @Test public void testSelect() { System.out.println(mapper.selectAll()); } /** * 选择数据 */ @Test public void testSelectByStudentSelective() { Person person = new Person(); System.out.println(mapper.selectByPersonSelective(person)); // select id, name, age from person person.setName("操"); System.out.println(mapper.selectByPersonSelective(person)); // select id, name, age from person WHERE name like concat('%', ?, '%') person.setAge(25); System.out.println(mapper.selectByPersonSelective(person)); // select id, name, age from person WHERE name like concat('%', ?, '%') and age=? } /** * 更新数据 */ @Test public void testUpdateByPrimaryKeySelective() { Person person = new Person(); person.setId(1); person.setAge(26); mapper.updateByPrimaryKeySelective(person); // update person SET `age` = ? where id = ? session.commit(); person.setName("刘禅"); mapper.updateByPrimaryKeySelective(person); session.commit(); // update person SET `name` = ?, `age` = ? where id = ? } /** * 插入数据 */ @Test public void testInsertSelective() { Person person = new Person(); person.setName("司马懿"); mapper.insertSelective(person); // insert into person ( `name` ) values ( ? ) session.commit(); person.setAge(26); mapper.insertSelective(person); // insert into person ( `name`, age ) values ( ?, ? ) session.commit(); } /** * 选择查询 */ @Test public void testSelectByNameOrId() { Person person = new Person(); person.setName("司马懿"); mapper.selectByNameOrId(person); // select id, name, age from person where 1=1 and name=? person.setId(1); mapper.selectByNameOrId(person); // select id, name, age from person where 1=1 and id=? }
}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
- 101
- 102
- 103
- 104
- 105
- 106
- 107
- 108
- 109
Where
set 和 where 其实都是 trim 标签的一种类型
where 等价于
<trim prefix="where" prefixOverrides="AND |OR">
</trim>
- 1
- 2
表示当 trim 中含有内容时， 添加 where， 且第一个为 and 或 or 时， 会将其去掉。
而如果没有内容， 则不添加 where。
set 等价于
<trim prefix="SET" suffixOverrides=",">
...
</trim>
- 1
- 2
- 3
表示当 trim 中含有内容时， 添加 set， 且最后的内容为 , 时， 会将其去掉。而没有内容， 不添加 set
trim 的几个属性
prefix: 当 trim 元素包含有内容时， 增加 prefix 所指定的前缀
prefixOverrides: 当 trim 元素包含有内容时， 去除 prefixOverrides 指定的 前缀
suffix: 当 trim 元素包含有内容时， 增加 suffix 所指定的后缀
suffixOverrides：当 trim 元素包含有内容时， 去除 suffixOverrides 指定的后缀
- 1
- 2
- 3
- 4
参考
文章来源: pengshiyu.blog.csdn.net，作者：彭世瑜，版权归原作者所有，如需转载，请联系作者。
原文链接：pengshiyu.blog.csdn.net/article/details/107587007
【版权声明】本文为华为云社区用户转载文章，如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱：
cloudbbs@huaweicloud.com
- 点赞
- 收藏
- 关注作者
评论（0）