文章目录
MyBatis
基础
在使用MyBatis
之前,需要在工程中导入JDBC
驱动包以及MyBatis
包。
配置文件
全局配置文件
全局配置文件用于存储数据库基础连接配置(url,user,password,driver
)以及注册SQL
映射文件。
<?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>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<!-- JDBC驱动 -->
<property name="driver" value="${driver}"/>
<!-- 数据库连接url -->
<property name="url" value="${url}"/>
<!-- 数据库连接用户名 -->
<property name="username" value="${username}"/>
<!-- 数据库连接密码 -->
<property name="password" value="${password}"/>
</dataSource>
</environment>
</environments>
<!-- 已经映射的SQL语句 -->
<mappers>
<!-- SQL映射文件路径 -->
<mapper resource="org/mybatis/example/BlogMapper.xml"/>
</mappers>
</configuration>
SQL
映射文件
SQL
映射文件用于为每一条所需的SQL
语句创建唯一标识(命名空间+编号),使得代码中可以精确执行指定的SQL
语句。SQL
映射文件必须在全局配置文件中注册。
<?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标签用于表示SQL映射,为每条SQL语句使用namespace+id创建唯一标识,
在代码中引用SQL语句的namespace+id即可执行指定的SQL语句并获取指定形式的结果。
SQL映射文件必须在全局配置文件中注册,否则代码中将无法找到对应的SQL语句,
具体的做法是将SQL映射文件的路径添加到全局配置文件的<mappers>中
-->
<mapper namespace="org.mybatis.example.BlogMapper">
<!-- id指定SQL语句在命名空间下的唯一标识,resultType指定用于承接SELECT查询数据的对象 -->
<select id="selectBlog" resultType="Blog">
<!-- 具体SQL语句,其中#{id}类似于JDBC中的占位符,用于接收指定字段值 -->
select * from Blog where id = #{id}
</select>
</mapper>
基础API
SqlSessionFactory
接口
SqlSessionFactory
实例是MyBatis
与数据库交互的核心,用于加载全局配置文件,创建数据库连接,开启一次数据库会话等。
package org.apache.ibatis.session;
public interface SqlSessionFactory {
// ...
/* 开启一次数据库会话,手动提交事务 */
SqlSession openSession();
/* 开启一次会话,传入true时,自动提交事务;传入false时,手动提交事务 */
SqlSession openSession(boolean var1);
//...
}
SqlSessionFactoryBuilder
类
SqlSessionFactoryBuilder
类的用于加载全局配置文件,并获取SqlSessionFactory
实例。
package org.apache.ibatis.session;
public class SqlSessionFactoryBuilder {
// ...
/* 构造器 */
public SqlSessionFactoryBuilder() {}
/* 使用Reader加载全局配置文件 */
public SqlSessionFactory build(Reader reader);
/* 使用InputStream加载全局配置文件 */
public SqlSessionFactory build(InputStream inputStream);
/* 使用Configuration加载全局配置文件 */
public SqlSessionFactory build(Configuration config);
// ...
}
SqlSession
接口
SqlSession
实例用于定位已经映射的SQL
语句,执行增删改查操作,获取并处理结果集。
package org.apache.ibatis.session;
public interface SqlSession ... {
// ...
/* 查询一条记录,var1传入已经映射的SQL语句的唯一标识(namespase.id) */
<T> T selectOne(String var1);
/* 查询一条记录,var1传入已经映射的SQL语句的唯一标识(namespase.id),
var2传入参数替换#{字段} */
<T> T selectOne(String var1,Object var2);
/* 查询多条记录,var1传入已经映射的SQL语句的唯一标识(namespase.id) */
<E> List<E> selectList(String var1);
/* 查询多条记录,var1传入已经映射的SQL语句的唯一标识(namespase.id),
var2传入参数替换#{字段} */
<E> List<E> selectList(String var1,Object var2);
void select(String var1,Object var2,ResultHandler var3);
void select(String var1,ResultHandler var2);
<K,V> Map<K,V> selectMap(String var1,String var2);
<K,V> Map<K,V> selectMap(String var1,Object var2,String var3);
int insert(String var1);
int insert(String var1,Object var2);
int update(String var1);
int update(String var1,Object var2);
int delete(String var1);
int delete(String var1,Object var2);
/* 提交事务 */
void commit();
void commit(boolean var1);
/* 回滚事务 */
void rollback();
void rollback(boolean var1);
/* 获取接口的代理类对象,参数var1传入对应接口的Class对象 */
<T> T getMapper(Class<T> var1);
/* 关闭会话 */
void close();
/* 清理缓存 */
void clearCache();
Connection getConnection();
// ...
}
Resources
类
Resources
类是位于org.apache.ibatis.io
包下的工具类,用于资源的加载。
package org.apache.ibatis.io;
public class Resources {
// ...
public static URL getResourceURL(String resource) throws IOException;
public static InputStream getResourceAsStream(String resource) throws IOException;
public static Properties getResourceAsProperties(String resource) throws IOException;
public static Reader getResourceAsReader(String resource) throws IOException;
public static File getResourceAsFile(String resource) throws IOException;
public static void setCharset(Charset charset);
// ...
}
示例
实例表和映射类
表结构:
id | username | password | gender | age | photo | |
---|---|---|---|---|---|---|
INT | VARCHAR(100) | VARCHAR(100) | ENUM(‘f’,‘m’) | TINYINT | VARCHAR(100) | BLOB |
建表语句:
CREATE TABLE IF NOT EXISTS accounts(
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(100) NOT NULL UNIQUE,
password VARCHAR(100) NOT NULL,
gender ENUM('f','m') NOT NULL,
age TINYINT NOT NULL,
email VARCHAR(100) UNIQUE,
photo MEDIUMBLOB
);
映射类:
Accounts.java
public class Accounts {
private int id;
private String username;
private String password;
private String gender;
private int age;
private String email;
private Blob photo;
public Accounts() {super();}
public Accounts(
int id, String username, String password,
String gender, int age, String email) {
this.id = id;
this.username = username;
this.password = password;
this.gender = gender;
this.age = age;
this.email = email;
}
public int getId() {return id;}
public void setId(int id) {this.id = id;}
public String getUsername() {return username;}
public void setUsername(String username) {this.username = username;}
public String getPassword() {return password;}
public void setPassword(String password) {this.password = password;}
public String getGender() {return gender;}
public void setGender(String gender) {this.gender = gender;}
public int getAge() {return age;}
public void setAge(int age) {this.age = age;}
public String getEmail() {return email;}
public void setEmail(String email) {this.email = email;}
public Blob getPhoto() {return photo;}
public void setPhoto(Blob photo) {this.photo = photo;}
@Override
public String toString() {
return "id : " + id
+ ", username : " + username
+ ", password : " + password
+ ", gender : " + gender
+ ", age : " + age
+ ", email : " + email;
}
}
配置文件
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>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<!-- JDBC驱动 -->
<property name="driver" value="com.mysql.cj.jdbc.Driver"/>
<!-- 数据库连接url,注意:"&"在XML中使用"&"转义 -->
<property name="url" value="jdbc:mysql://localhost:3306/mybatis_test?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC"/>
<!-- 数据库连接用户名 -->
<property name="username" value="root"/>
<!-- 数据库连接密码 -->
<property name="password" value="123456"/>
</dataSource>
</environment>
</environments>
<!-- 已经映射的SQL语句 -->
<mappers>
<!-- SQL映射文件路径 -->
<mapper resource="com/lishaoyin/mybatis_test/res/accounts-mapper.xml"/>
</mappers>
</configuration>
accounts-mapper.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.lishaoyin.mybatis_test.res.accounts-mapper">
<!-- 命名空间下的标识为"selectAccounts",使用Accounts类存储结果集,
注意:类名必须加上完整的包名!!! -->
<select id="selectAccounts" resultType="com.lishaoyin.mybatis_test.entity.Accounts">
<!-- 具体SQL语句,其中#{id}类似于JDBC中的占位符,用于接收指定字段值 -->
select * from accounts where id=#{id}
</select>
</mapper>
连接数据库并执行SQL
语句
Main.java
public class Main {
public static void main(String[] args) {
InputStream is = null;
/* 配置文件路径 */
final String configPath = "com/lishaoyin/mybatis_test/res/mybatis-config.xml";
/* SQL标识 */
String SqlMapperId;
SqlSession session = null;
try {
/* 加载配置文件 */
is = Resources.getResourceAsStream(configPath);
/* 创建SqlSessionFactory实例 */
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
/* 开启会话并获取SqlSession实例 */
session = factory.openSession();
/* 使用namespace+id标识SQL语句,避免不同的namespace中有相同的id */
SqlMapperId =
"com.lishaoyin.mybatis_test.res.accounts-mapper.selectAccounts";
/* 执行查询并获取结果集 */
Accounts account = session.selectOne(SqlMapperId,1);
System.out.println(account);
} catch(Exception e) {
e.printStackTrace();
} finally {
try {
/* 关闭资源 */
if(is != null) {is.close();}
if(session != null) {session.close();}
} catch(Exception ex) {
ex.printStackTrace();
}
}
}
}
接口式编程
MyBatis
可以将接口绑定到SQL
映射文件中,使用动态代理为接口创建实现类对象,使得接口中的每一个方法都对应SQL
映射文件中的一条SQL
语句。
创建查询接口
AccountsMapper.java
package com.lishaoyin.mybatis_test.mapper;
public interface AccountsMapper {
Accounts getAccountById(int id);
Accounts getAccountByUsername(String username);
Accounts getAccountByEmail(String email);
}
绑定接口
绑定接口的具体操作是:将SQL
映射文件中的命名空间改为查询接口的完整类名(完整包名+类名),将每条SQL
语句的id
改为查询接口中对应的查询方法名。
accounts-mapper.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.lishaoyin.mybatis_test.mapper.AccountsMapper">
<!-- 对应getAccountById(int id)方法 -->
<select id="getAccountById" resultType="com.lishaoyin.mybatis_test.entity.Accounts">
select * from accounts where id=#{id}
</select>
<!-- 对应getAccountByUsername(String username)方法 -->
<select id="getAccountByUsername" resultType="com.lishaoyin.mybatis_test.entity.Accounts">
select * from accounts where username=#{username}
</select>
<!-- 对应getAccountByEmail(String email)方法 -->
<select id="getAccountByEmail" resultType="com.lishaoyin.mybatis_test.entity.Accounts">
select * from accounts where email=#{email}
</select>
</mapper>
查询示例
Main.java
public class Main {
public static void main(String[] args) {
InputStream is = null;
/* 配置文件路径 */
final String configPath = "com/lishaoyin/mybatis_test/res/mybatis-config.xml";
SqlSession session = null;
try {
/* 加载配置文件 */
is = Resources.getResourceAsStream(configPath);
/* 创建SqlSessionFactory实例 */
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
/* 开启会话并获取SqlSession实例 */
session = factory.openSession();
/* 绑定查询接口,获取查询接口的代理类对象 */
AccountsMapper accountsMapper = session.getMapper(AccountsMapper.class);
/* 调用接口中的查询方法 */
Accounts account_1 = accountsMapper.getAccountById(1);
System.out.println(account_1);
Accounts account_2 = accountsMapper.getAccountByUsername("Tony");
System.out.println(account_2);
Accounts account_3 = accountsMapper.getAccountByEmail("3489418607@qq.com");
System.out.println(account_3);
} catch(Exception e) {
e.printStackTrace();
} finally {
try {
/* 关闭资源 */
if(is != null) {is.close();}
if(session != null) {session.close();}
} catch(Exception ex) {
ex.printStackTrace();
}
}
}
}
接口式编程总结
接口式编程的一般流程:
1.编写全局配置文件,指定数据库连接所需的url,username,password,driver
2.编写查询接口,使用接口规范查询的方法
3.编写SQL映射文件
4.在全局配置文件中注册SQL映射文件
5.预绑定接口:SQL映射文件中的namespace为查询接口的全类名,SQL语句的id为查询接口的方法名
6.使用Resources工具类加载全局配置文件
7.使用SqlSessionFactoryBuilder构建(build())SqlSessionFactory实例
8.使用SqlSessionFactory实例开启数据库连接会话(openSession())并获取SqlSession实例
9.使用SqlSession实例绑定查询接口(getMapper()),并获取查询接口的代理类对象
10.使用查询接口的代理类对象执行查询,获取查询结果
DAO
与接口式编程(Mapper
)对比
DAO :
1.一个DAO接口对应一个DAO实现类,SQL语句的执行由DAO实现类实现
2.DAO使用Connection实例创建连接以及操作数据库,Connection实例是非线程安全的,
每次使用必须重新获取
接口式编程(Mapper) :
1.一个Mapper接口对应一个SQL映射文件,SQL语句的执行由Mapper接口的代理实现类实现
2.接口式编程使用SqlSession实例创建连接以及操作数据库,SqlSession实例是非线程安全的,
每次使用必须重新获取
全局配置文件深入
dtd
约束
MyBatis
全局配置文件中文档声明之后的<!DOUTYPE ...>
表签的作用是引入dtd
约束,dtd
约束的作用是控制XML
的语法。类似的SQL
映射文件中也包含有dtd
约束。
<?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>...</configuration>
properties
标签
properties
标签是configuration
标签的子标签,用于引入外部配置文件至全局配置文件中。将数据库连接的url
等信息硬编码到全局配置文件中不利于整体的修改和维护,因此可以使用外部配置文件解耦。
properties
标签的使用:
<!-- resource属性用于指定配置文件的类路径,也可替换为url属性,
url属性用于指定配置文件的磁盘路径或网络路径 -->
<properties resource="com/lishaoyin/mybatis_test/res/connection-config.properties"/>
引入properties
配置文件之后,全局配置文件中对应的只使用${属性名}
的方式占位:
<property name="driver" value="${driver}"/>
<property name="url" value="${url}"/>
<property name="username" value="${username}"/>
<property name="password" value="${password}"/>
示例:
connection-config.properties
url=jdbc:mysql://localhost:3306/mybatis_test?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC
username=root
password=123456
driver=com.mysql.cj.jdbc.Driver
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>
<!-- 引入connection-config.properties配置文件 -->
<properties resource="com/lishaoyin/mybatis_test/res/connection-config.properties"/>
<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>
<!-- 已经映射的SQL语句 -->
<mappers>
<!-- SQL映射文件路径 -->
<mapper resource="com/lishaoyin/mybatis_test/res/accounts-mapper.xml"/>
</mappers>
</configuration>
settings
标签
settings
标签是configuration
标签的子标签,包含了很多MyBatis
的重要设置项。例如:
<?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>
...
<settings>
<!-- 是否将数据库中的下划线命名法对应封装为java中的驼峰命名法,默认为false;
如果为true,则会将数据库中类似于first_name(下划线)的命名形式封装为
java中经典的firstName(驼峰命名)命名形式 -->
<setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>
...
</configuration>
typeAliases
标签
typeAliases
标签是configuration
标签的子标签,用于为类起别名。在全局配置文件中引用的类名均为冗长的全类名,为全类名起别名可减少代码量。别名不区分大小写。typeAliases
标签的使用:
<typeAliases>
<!-- 未指定别名时,别名默认为最简类名的全小写形式,以下指定"Accounts"为
"com.lishaoyin.mybatis_test.entity.Accounts"的别名 -->
<typeAlias type="com.lishaoyin.mybatis_test.entity.Accounts" alias="Accounts"/>
</typeAliases>
示例:
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>
...
<typeAliases>
<typeAlias type="com.lishaoyin.mybatis_test.entity.Accounts" alias="Accounts"/>
</typeAliases>
...
</configuration>
accounts-mapper.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.lishaoyin.mybatis_test.mapper.AccountsMapper">
...
<!-- 引用别名"Accounts" -->
<select id="getAccountById" resultType="Accounts">
select * from accounts where id=#{id}
</select>
...
</mapper>
package
标签
package
标签是typeAliases
标签的子标签,用于为同一包下的所有类的全类名批量起别名。别名不区分大小写。package
标签的用法:
<typeAliases>
<!-- 指定包名,包下每个类的别名默认为最简类名的全小写形式 -->
<package name="com.lishaoyin.mybatis_test.entity"/>
</typeAliases>
如需为指定包下的类批量指定自定义别名,需要使用@Alias("别名")
注解修饰对应类。例如:
package com.lishaoyin.mybatis_test.entity;
/* 使用"Accounts"代替"com.lishaoyin.mybatis_test.entity.Accounts" */
@Alias("Accounts")
public class Accounts {...}
typeHandlers
标签
typeHandlers
标签是configuration
标签的子标签,用于数据库数据类型与java
数据类型的适配,例如VARCHAR
与String
的适配。在新版本的MyBatis
中,基本的数据类型均为自动注册适配,typeHandlers
标签主要用于自定义数据类型的适配,之后再做详析。
environments
标签
environments
标签是configuration
标签的子标签,用于配置数据库运行环境,environments
标签由一个或多个environment
子标签组成,environment
子标签中必须包含transactionManager
子标签和dataSource
子标签。transactionManager
子标签用于指定事务管理器,dataSource
用于指定基本的数据库连接信息。
<?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>
...
<!-- 通过选择某个environment的id属性为environments的default属性,可指定具体使用的是哪个环境。
开发前期可使用test测试环境,开发过程中可迅速切换至实际开发环境 -->
<environments default="development">
<!-- 实际开发使用的环境 -->
<environment id="development">
<!-- 事务管理器 -->
<transactionManager type="JDBC"/>
<!-- 数据库连接信息,type可选择POOLED|UNPOOLED,type="POOLED"则使用
数据库连接池技术,每次从数据库连接池中取连接,type="UNPOOLED"则不使用
数据库连接池技术,每次重新创建连接 -->
<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>
<!-- 开发前期使用的测试环境 -->
<environment id="test">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${test.driver}"/>
<property name="url" value="${test.url}"/>
<property name="username" value="${test.username}"/>
<property name="password" value="${test.password}"/>
</dataSource>
</environment>
</environments>
...
</configuration>
mapper
标签
mappers
标签是mappers
标签的子标签(mappers
标签是configuration
标签的子标签),用于注册SQL
映射文件和接口。
<mappers>
<!-- 使用resource属性注册SQL映射文件,resource的属性值是SQL映射文件的类路径名 -->
<mapper resource="com/lishaoyin/mybatis_test/res/accounts-mapper.xml"/>
<!-- 使用url属性注册SQL映射文件,url的属性值是SQL映射文件磁盘路径名或网络路径名 -->
<mapper url="D:/test/com/lishaoyin/mybatis_test/res/accounts-mapper.xml"/>
<!-- 使用接口的全类名注册接口,这种方式要求接口与对应的SQL文件在同一目录下,
且SQL映射文件与接口同名。使用class属性注册接口时可以使用注解代替SQL映射文件 -->
<mapper class="com.lishaoyin.mybatis_test.mapper.AccountsMapper"/>
</mappers>
使用注解注册接口:
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="com/lishaoyin/mybatis_test/res/connection-config.properties"/>
<typeAliases>
<!-- 为类起别名 -->
<typeAlias type="com.lishaoyin.mybatis_test.entity.Accounts" alias="Accounts"/>
</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>
<!-- 已经映射的SQL语句 -->
<mappers>
<!-- 接口的全类名 -->
<mapper class="com.lishaoyin.mybatis_test.mapper.AccountsMapper"/>
</mappers>
</configuration>
AccountsMapper.java
package com.lishaoyin.mybatis_test.mapper;
public interface AccountsMapper {
@Select("select * from accounts where id=#{id}")
Accounts getAccountById(int id);
@Select("select * from accounts where username=#{username}")
Accounts getAccountByUsername(String username);
@Select("select * from accounts where email=#{email}")
Accounts getAccountByEmail(String email);
}
Main.java
public class Main {
public static void main(String[] args) {
InputStream is = null;
/* 配置文件路径 */
final String configPath = "com/lishaoyin/mybatis_test/res/mybatis-config.xml";
SqlSession session = null;
try {
/* 加载配置文件 */
is = Resources.getResourceAsStream(configPath);
/* 创建SqlSessionFactory实例 */
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
/* 开启会话并获取SqlSession实例 */
session = factory.openSession();
/* 绑定查询接口,获取查询接口的代理类对象 */
AccountsMapper accountsMapper = session.getMapper(AccountsMapper.class);
/* 调用接口中的查询方法 */
Accounts account_1 = accountsMapper.getAccountById(1);
System.out.println(account_1);
Accounts account_2 = accountsMapper.getAccountByUsername("Tony");
System.out.println(account_2);
Accounts account_3 = accountsMapper.getAccountByEmail("3489418607@qq.com");
System.out.println(account_3);
} catch(Exception e) {
e.printStackTrace();
} finally {
try {
/* 关闭资源 */
if(is != null) {is.close();}
if(session != null) {session.close();}
} catch(Exception ex) {
ex.printStackTrace();
}
}
}
}
使用注解注册接口的步骤:
1.编写全局配置文件,指定数据库连接所需的url,username,password,driver
2.编写查询接口,使用接口规范查询的方法
3.在全局配置文件中使用mapper标签的class属性注册接口(传入接口的全类名)
4.预绑定接口:为接口中的查询方法添加对应的查询注解(@Select(...)...)实现查询
5.使用Resources工具类加载全局配置文件
6.使用SqlSessionFactoryBuilder构建(build())SqlSessionFactory实例
7.使用SqlSessionFactory实例开启数据库连接会话(openSession())并获取SqlSession实例
8.使用SqlSession实例绑定查询接口(getMapper()),并获取查询接口的代理类对象
9.使用查询接口的代理类对象执行查询,获取查询结果
全局配置文件中标签的排布顺序
<!-- 遵循以下顺序 -->
<properties>...</properties>
<settings>...</settings>
<typeAliases>...</typeAliases>
<typeHandlers>...</typeHandlers>
<objectFactory>...</objectFactory>
<objectWrapperFactory>...</objectWrapperFactory>
<reflectFactory>...</reflectFactory>
<plugins>...</plugins>
<environments>...</environments>
<databaseIdProvider>...</databaseIdProvider>
<mappers>...</mappers>
SQL
映射文件增删改查
全局配置文件和接口
全局配置文件
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="com/lishaoyin/mybatis_test/res/connection-config.properties"/>
<typeAliases>
<!-- 为实体类起别名 -->
<typeAlias type="com.lishaoyin.mybatis_test.entity.Accounts" alias="Accounts"/>
</typeAliases>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<!-- JDBC驱动 -->
<property name="driver" value="${driver}"/>
<!-- 数据库连接url -->
<property name="url" value="${url}"/>
<!-- 数据库连接用户名 -->
<property name="username" value="${username}"/>
<!-- 数据库连接密码 -->
<property name="password" value="${password}"/>
</dataSource>
</environment>
</environments>
<mappers>
<!-- 注册SQL映射文件 -->
<mapper resource="com/lishaoyin/mybatis_test/res/accounts-mapper.xml"/>
</mappers>
</configuration>
接口改进
AccountsMapper.java
package com.lishaoyin.mybatis_test.mapper;
public interface AccountsMapper {
/* 插入一个账户 */
void insert(Accounts account);
/* 根据id修改一个账户 */
void updateById(Accounts account);
/* 根据id删除一个账户 */
void deleteById(int id);
/* 根据指定的id区间删除多个账户 */
void deleteByIds(int lowerId,int upperId);
/* 使用id查询一个账户 */
Accounts getAccountById(int id);
/* 根据指定的id区间查询多个账户 */
List<Accounts> getAccountsByIds(int lowerId,int upperId);
/* 查询所有账户 */
List<Accounts> getAll();
}
Insert
SQL
映射文件
accounts-mapper.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.lishaoyin.mybatis_test.mapper.AccountsMapper">
<!-- void insert(Accounts account):
id指定接口中的方法名,parameterType指定插入的数据类型,在此使用全局配置文件中声明的别名, parameterType属性可以省略,只要#{}中正确对应实体类属性名即可 -->
<insert id="insert" parameterType="Accounts">
<!-- #{}中的内容对应为实体类中的字段名!!! -->
insert into accounts(username,password,gender,age,email)
values(#{username},#{password},#{gender},#{age},#{email})
</insert>
</mapper>
使用示例
Main.java
public class Main {
public static void main(String[] args) {
SqlSession session = null;
try {
/* 加载全局配置文件 */
InputStream is =
Resources.getResourceAsStream(
"com/lishaoyin/mybatis_test/res/mybatis-config.xml");
/* 构建SqlSessionFactory实例 */
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
is.close();
/* 开启会话 */
session = factory.openSession();
/* 绑定接口 */
AccountsMapper mapper = session.getMapper(AccountsMapper.class);
Accounts account = new Accounts(2,"Steve","123456","m",35,"Steve@163.com");
/* 插入 */
mapper.insert(account);
/* 特别注意:增删改是事务操作,因此修改完成后必须提交事务,否则不做更改 */
session.commit();
} catch(Exception e) {
e.printStackTrace();
/* 出现异常回滚事务 */
if(session != null) {session.rollback();}
} finally {
/* 关闭会话 */
if(session != null) {session.close();}
}
}
}
update
SQL
映射文件
accounts-mapper.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.lishaoyin.mybatis_test.mapper.AccountsMapper">
<!-- void insert(Accounts account):
id指定接口中的方法名,parameterType指定插入的数据类型,在此使用全局配置文件中声明的别名, parameterType属性可以省略,只要#{}中正确对应实体类属性名即可 -->
<insert id="insert" parameterType="Accounts">
<!-- #{}中的内容对应为实体类中的字段名!!! -->
insert into accounts(username,password,gender,age,email)
values(#{username},#{password},#{gender},#{age},#{email})
</insert>
<!-- void updateById(Accounts account):
id指定接口中的方法名,此处省略parameterType -->
<update id="updateById">
update accounts set
username=#{username},
password=#{password},
gender=#{gender},
age=#{age},
email=#{email}
where id=#{id}
</update>
</mapper>
使用示例
Main.java
public class Main {
public static void main(String[] args) {
SqlSession session = null;
try {
/* 加载全局配置文件 */
InputStream is =
Resources.getResourceAsStream(
"com/lishaoyin/mybatis_test/res/mybatis-config.xml");
/* 构建SqlSessionFactory实例 */
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
is.close();
/* 开启会话 */
session = factory.openSession();
/* 绑定接口 */
AccountsMapper mapper = session.getMapper(AccountsMapper.class);
Accounts account = new Accounts(2,"Judy","123456","f",20,"Judy@163.com");
/* 修改 */
mapper.updateById(account);
/* 特别注意:增删改是事务操作,因此修改完成后必须提交事务,否则不做更改 */
session.commit();
} catch(Exception e) {
e.printStackTrace();
/* 出现异常回滚事务 */
if(session != null) {session.rollback();}
} finally {
/* 关闭会话 */
if(session != null) {session.close();}
}
}
}
delete
SQL
映射文件
accounts-mapper.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.lishaoyin.mybatis_test.mapper.AccountsMapper">
<!-- void insert(Accounts account):
id指定接口中的方法名,parameterType指定插入的数据类型,在此使用全局配置文件中声明的别名, parameterType属性可以省略,只要#{}中正确对应实体类属性名即可 -->
<insert id="insert" parameterType="Accounts">
<!-- #{}中的内容对应为实体类中的字段名!!! -->
insert into accounts(username,password,gender,age,email)
values(#{username},#{password},#{gender},#{age},#{email})
</insert>
<!-- void updateById(Accounts account):
id指定接口中的方法名,此处省略parameterType -->
<update id="updateById">
update accounts set
username=#{username},
password=#{password},
gender=#{gender},
age=#{age},
email=#{email}
where id=#{id}
</update>
<!-- void deleteById(int id):
id指定接口中的方法名,此处省略parameterType -->
<delete id="deleteById">
delete from accounts where id=#{id}
</delete>
<!-- void deleteByIds(int lowerId,int upperId):
id指定接口中的方法名,此处省略parameterType;
特别注意:
#{}中的内容也可以是类似于arg0(或param1),arg1(或param2),...,argn(或paramn+1)的形式,
分别对应接口中方法传入的第1个,第2个,...,第n个参数 -->
<delete id="deleteByIds">
delete from accounts where id between #{arg0} and #{arg1}
</delete>
</mapper>
使用示例
Main.java
public class Main {
public static void main(String[] args) {
SqlSession session = null;
try {
/* 加载全局配置文件 */
InputStream is =
Resources.getResourceAsStream(
"com/lishaoyin/mybatis_test/res/mybatis-config.xml");
/* 构建SqlSessionFactory实例 */
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
is.close();
/* 开启会话 */
session = factory.openSession();
/* 绑定接口 */
AccountsMapper mapper = session.getMapper(AccountsMapper.class);
/* 删除一个记录 */
mapper.deleteById(2);
/* 删除多个记录 */
mapper.deleteByIds(1,5);
/* 特别注意:增删改是事务操作,因此修改完成后必须提交事务,否则不做更改 */
session.commit();
} catch(Exception e) {
e.printStackTrace();
/* 出现异常回滚事务 */
if(session != null) {session.rollback();}
} finally {
/* 关闭会话 */
if(session != null) {session.close();}
}
}
}
增删改提要
事务处理
增删改是事务性操作,因此涉及到事务的提交与回滚:执行成功后提交事务;出现异常时回滚事务。实际上MyBatis
可以设置自动提交事务,实现方法在于openSession()
方法的重载:
package org.apache.ibatis.session;
public interface SqlSessionFactory {
// ...
/* 开启一次数据库会话,手动提交事务 */
SqlSession openSession();
/* 开启一次会话,传入true时,自动提交事务;传入false时,手动提交事务 */
SqlSession openSession(boolean var1);
//...
}
参数处理
参数处理指MyBatis
通过#{参数名}
或${参数名}
的形式将接口方法中的参数传入SQL
语句中的处理。
单个参数
对于单个参数的接口方法传递参数,MyBatis
不检查#{}
中的参数名,可以在#{}
中填入任何合法字符串:
<!-- void deleteById(int id) -->
<delete id="deleteById">
<!-- 不对单个参数检查 -->
delete from accounts where id=#{abcdef}
</delete>
多个参数
对于多个参数的接口方法传递参数,MyBatis
会将多个参数特殊处理,将参数存储为Map
形式。Map
的key
由MayBatis
自动设置为arg0(或param1),arg1(或param2),...,argn(或paramn+1)
,对应的value
为接口方法的第一个参数,第二个参数,…,第n
个参数。
<!-- void deleteByIds(int lowerId,int upperId) -->
<delete id="deleteByIds">
<!-- "param1","param2"...是MyBatis为参数Map设置的key名 -->
delete from accounts where id between #{param1} and #{param2}
</delete>
arg0(或param1)
的形式不利于阅读,推荐使用@Param
注解明确指定参数Map
中接口方法参数的key
名:
public interface AccountsMapper {
// ...
public void deleteByIds(@Param("lowerId")int lowerId,@Param("upperId")int upperId);
// ...
}
在SQL
映射文件中直接使用指定的key
名填充#{}
即可:
<!-- void deleteByIds(int lowerId,int upperId) -->
<delete id="deleteByIds">
<!-- "lowerId","upperId"是使用@Param注解为参数Map设置的key名 -->
delete from accounts where id between #{lowerId} and #{upperId}
</delete>
如果SQL
的多个参数正好对应实体类模型,使用实体类属性名填充#{}
即可:
<!-- void insert(Accounts account) -->
<insert id="insert">
<!-- 使用实体类属性名填充#{} -->
insert into accounts(username,password,gender,age,email)
values(#{username},#{password},#{gender},#{age},#{email})
</insert>
Map
参数
如果接口方法的参数是一个Map
,使用Map
的key
填充#{}
即可:
public interface AccountsMapper {
// ...
void deleteByMap(Map<String,Object> map);
// ...
}
集合参数
对于参数是集合(List
,Set
或数组)的接口方法传递参数,MyBatis
将参数封装为Map
形式,并作特殊处理。
public interface AccountsMapper {
// ...
public void deleteByList(List<Integer> ids);
// ...
}
MyBatis
定义了专用标识分别对不同的集合进行取值,对于Connection
,使用connection
+索引取值;对于List
,使用list
+索引取值;对于数组,使用array
+索引取值。例如:
<!-- void deleteByList(List<Integer> list) -->
<delete id="deleteByList">
<!-- "list"是特定标识 -->
delete from accounts where id=#{list[0]} or id=#{list[2]} or id=#{list[5]}
</delete>
混合参数
对于参数是其他数据与实体类的混合时,可使用标识.属性
取值。
public interface AccountsMapper {
// ...
public void updateById(@Param("id")int id,@Param("account")Accounts account);
// ...
}
SQL
中使用类似于对象调用成员的方式取值:
<update id="updateById">
update accounts set
username=#{account.username},
password=#{account.password},
gender=#{account.gender},
age=#{account.age},
email=#{account.email}
where id=#{id}
</update>
#{}
与${}
的区别
#{}
在构造SQL
语句时,使用?
作为参数值的占位符,相当于使用PreparedStatement
构造SQL
语句;${}
构造SQL
语句时,直接使用参数进行字符串拼接,相当于使用Statement
构造SQL
语句。因此#{}
更安全,而${}
存在SQL
注入隐患。
${}
在进行参数查询时存在隐患,但可以用在其它场合。例如查询数据库时不确定具体表名,而原生JDBC
不支持使用占位符?
占位表名,由于${}
采用字符串拼接方式,可以使用${}
传参拼接表名。
public interface AccountsMapper {
// ...
Accounts getByIdFrom(@Param("id")int id,@Param("from")String from);
// ...
}
使用${}
进行SQL
拼接:
<!-- Accounts getByIdFrom(@Param("id")int id,@Param("from")String from) -->
<select id="getByIdFrom" resultType="Accounts">
<!-- 将表名作为参数传入 -->
select * from ${from} where id=#{id}
</select>
调用:
/* 传入id=2,表名为"accounts" */
Accounts account = mapper.getByIdFrom(2,"accounts");
增删改的返回值
可以为接口中的增删改方法添加返回值,MyBatis
支持的类型有void,int,long,boolean
,无需在SQL
映射文件中添加返回类型,MyBatis
可根据绑定接口中方法的定义自动处理。返回类型为int/long
时,返回受影响的记录条数;返回类型为boolean
时,只要有一条记录受影响就返回true
.可将接口改为:
AccountsMapper.java
package com.lishaoyin.mybatis_test.mapper;
public interface AccountsMapper {
/* 插入一个账户,返回true则插入成功 */
boolean insert(Accounts account);
/* 根据id修改一个账户,返回true则指定记录被更改 */
boolean updateById(Accounts account);
/* 根据id删除一个账户,返回true则删除成功 */
boolean deleteById(int id);
/* 根据指定的id区间删除多个账户,返回实际删除的记录条数 */
int deleteByIds(int lowerId,int upperId);
/* 使用id查询一个账户 */
Accounts getAccountById(int id);
/* 根据指定的id区间查询多个账户 */
List<Accounts> getAccountsByIds(int lowerId,int upperId);
/* 查询所有账户 */
List<Accounts> getAll();
}
insert
获取主键值
获取自增主键值
insert
操作会引起自增主键值自增,对于进行了大量增删改混合操作的数据库表而言,插入一条新记录时很难计算出当前自增主键的值。可以使用insert
标签中的属性方便地获取当前自增主键值。
<!-- useGeneratedKeys="true"指定使用自增主键值,KeyProperty指定将自增主键值存入实体类的id属性中,
插入记录后,调用实体类的getId()方法即可获得自增主键值。databaseId="MySQL"指定数据库厂商标识 -->
<insert id="insert" resultType="Accounts"
useGeneratedKeys="true" KeyProperty="id" databaseId="MySQL">
INSERT INTO ...
</insert>
获取非自增主键值
对于非自增主键值的查询,可以使用insert
标签的子标签实现:
<insert id="insert" databaseId="MySQL">
<!-- KeyProperty指定将主键值存入到实体类的那个属性中;order取值为"BEFORE"或"AFTER",
order="BEFORE"时,先查询主键,再执行插入;order="AFTER"时,先执行插入,再查询主键。
resultType指定返回的主键类型 -->
<selectKey KeyProperty="key1" order="BEFORE" resultType="int">
SELECT key1 FROM ...
</selectKey>
<selectKey KeyProperty="key2" order="BEFORE" resultType="int">
SELECT key2 FROM ...
</selectKey>
...
INSERT INTO ...
</insert>
select
多条结果封装为List
AccountsMapper.java(缺省)
public interface AccountsMapper {
// ...
/* 使用@Param注解标识参数 */
List<Accounts> getAccountsByIds(@Param("lowerId")int lowerId,@Param("upperId")int upperId);
// ...
}
account-mapper.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.lishaoyin.mybatis_test.mapper.AccountsMapper">
...
<!-- Accounts是在全局配置文件中生命的全类名的别名
注意:resultType不可传入"List<...>",只需传入实体类类型 -->
<select id="getAccountsByIds" resultType="Accounts">
select * from accounts where id between #{lowerId} and #{upperId}
</select>
...
</mapper>
Main.java
public class Main {
public static void main(String[] args) {
SqlSession session = null;
try {
/* 加载全局配置文件 */
InputStream is =
Resources.getResourceAsStream(
"com/lishaoyin/mybatis_test/res/mybatis-config.xml");
/* 构建SqlSessionFactory实例 */
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
is.close();
/* 开启会话 */
session = factory.openSession();
/* 绑定接口 */
AccountsMapper mapper = session.getMapper(AccountsMapper.class);
/* 查询 */
List<Accounts> list = mapper.getAccountsByIds(1,5);
/* 遍历输出 */
for(Accounts account : list) {
System.out.println(account);
}
} catch(Exception e) {
e.printStackTrace();
} finally {
/* 关闭会话 */
if(session != null) {session.close();}
}
}
}
单条结果封装为Map
AccountsMapper.java(缺省)
public interface AccountsMapper {
// ...
Map<String,Object> getOneAsMapById(int id);
// ...
}
account-mapper.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.lishaoyin.mybatis_test.mapper.AccountsMapper">
...
<!-- 注意:"map"是MyBatis为java.util.Map起的别名(MyBatis同样为其它常用类起了别名)
resultType="map"时,得到的map的key为表的列名(或别名),value为列值 -->
<select id="getOneAsMapById" resultType="map">
select * from accounts where id=#{id}
</select>
...
</mapper>
Main.java
public class Main {
public static void main(String[] args) {
SqlSession session = null;
try {
/* 加载全局配置文件 */
InputStream is =
Resources.getResourceAsStream(
"com/lishaoyin/mybatis_test/res/mybatis-config.xml");
/* 构建SqlSessionFactory实例 */
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
is.close();
/* 开启会话 */
session = factory.openSession();
/* 绑定接口 */
AccountsMapper mapper = session.getMapper(AccountsMapper.class);
Map<String,Object> map = mapper.getOneAsMapById(1);
System.out.println(map);
} catch(Exception e) {
e.printStackTrace();
} finally {
/* 关闭会话 */
if(session != null) {session.close();}
}
}
}
select
标签的resultType
属性为map
时,MyBatis
会自动将结果集封装为Map<String,Object>
形式,其中key
值为表的列名(或别名),value
为具体的列值。
多条结果封装为Map
可将多条查询结果封装为Map
,key
为每条结果中的唯一属性(例如id
,username
等),value
为对应的实体类对象。具体的做法是为接口方法添加@MapKey
注解,并传入字符串指定将实体类中的哪个属性值作为key
:
/* 将id作为key */
@MapKey("id")
Map<Integer,Accounts> getSomeByIds(int lowerId,int upperId);
/* 将username作为key */
@MapKey("username")
Map<String,Accounts> getSomeByIds(int lowerId,int upperId);
AccountsMapper.java(缺省)
public interface AccountsMapper {
// ...
@MapKey("id")
Map<Integer,Accounts> getSomeAsMapByIdsKeyById(
@Param("lowerId")int lowerId,@Param("upperId")int upperId);
@MapKey("username")
Map<String,Accounts> getSomeAsMapByIdsKeyByUsername(
@Param("lowerId")int lowerId,@Param("upperId")int upperId);
// ...
}
account-mapper.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.lishaoyin.mybatis_test.mapper.AccountsMapper">
...
<select id="getSomeAsMapByIdsKeyById" resultType="Accounts">
select * from accounts where id between #{lowerId} and #{upperId}
</select>
<select id="getSomeAsMapByIdsKeyByUsername" resultType="Accounts">
select * from accounts where id between #{lowerId} and #{upperId}
</select>
...
</mapper>
Main.java
public class Main {
public static void main(String[] args) {
SqlSession session = null;
try {
/* 加载全局配置文件 */
InputStream is =
Resources.getResourceAsStream(
"com/lishaoyin/mybatis_test/res/mybatis-config.xml");
/* 构建SqlSessionFactory实例 */
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
is.close();
/* 开启会话 */
session = factory.openSession();
/* 绑定接口 */
AccountsMapper mapper = session.getMapper(AccountsMapper.class);
/* 查询id在1~5之间的记录,并以key为id的Map形式存储 */
Map<Integer,Accounts> map1 = mapper.getSomeAsMapByIdsKeyById(1,5);
/* 查询id在1~5之间的记录,并以key为username的Map形式存储 */
Map<String,Accounts> map2 = mapper.getSomeAsMapByIdsKeyByUsername(1,5);
System.out.println(map1);
System.out.println(map2);
} catch(Exception e) {
e.printStackTrace();
} finally {
/* 关闭会话 */
if(session != null) {session.close();}
}
}
}
resultMap
resultMap
是select
标签的重要属性之一,用于自定义查询结果的封装形式。同时resultMap
也是Mapper
的子标签,resultMap
作为Mapper
的子标签时,可在resultMap
标签下作具体的封装规则定义。注意:resultType
属性与resultMap
属性均指定select
查询结果的封装形式,因此不可同时使用。
基本使用
accounts-mapper.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.lishaoyin.mybatis_test.mapper.AccountsMapper">
<!-- type指定封装实体类(在此使用别名),id为封装规则在命名空间下的唯一标识,便于引用 -->
<resultMap type="Accounts" id="AccountsMap">
<!-- column指定表中的字段名,property指定实体类中的属性名;
<id>与<result>的作用均为将表中的字段映射至指定的实体类属性 -->
<id column="id" property="id"/>
<!-- <result>与<id>的区别在于<id>用于封装主键,MyBatis可以根据<id>标签优化主键的
操作逻辑;<result>用于封装非主键,但也可以封装主键 -->
<result column="username" property="username"/>
<result column="password" property="password"/>
<result column="gender" property="gender"/>
<result column="age" property="age"/>
<result column="email" property="email"/>
<!-- 可以仅映射部分字段,但推荐一次映射全部字段 -->
</resultMap>
<!-- 使用resultMap传入封装规则的唯一标识来指定select查询返回结果的封装形式 -->
<select id="getOneById" resultMap="AccountsMap">
select * from accounts where id=#{id};
</select>
</mapper>
级联属性实现连接查询
创建新表:
employees(员工表)
id | name | gender | dptid |
---|---|---|---|
INT | VARCHR(16) | ENUM(‘f’,‘m’) | INT |
# 建表语句
CREATE TABLE IF NOT EXISTS employees(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(16) NOT NULL UNIQUE,
gender ENUM('f','m') NOT NULL,
dptid INT NOT NULL
);
departments(部门表)
id | dptname | manager | tel |
---|---|---|---|
INT | VARCHAR(100) | VARCHAR(16) | VARCHAR(50) |
# 建表语句
CREATE TABLE IF NOT EXISTS departments(
id INT PRIMARY KEY AUTO_INCREMENT,
dptname VARCHAR(100) NOT NULL UNIQUE,
manager VARCHAR(16) NOT NULL,
tel VARCHAR(50) NOT NULL UNIQUE
);
实体类:
Employees.java(缺省)
public class Employees {
private int id;
private String name;
private String gender;
private int departmentId;
/* 注意:必须添加public空参构造器,否则MyBatis内部反射机制无法运行!!! */
public Employees() {super();}
/* getters and setters... */
}
departments.java(缺省)
public class Departments {
private int id;
private String departmentName;
private String manager;
private String tel;
/* 注意:必须添加public空参构造器,否则MyBatis内部反射机制无法运行!!! */
public Departments() {super();}
/* getters and setters... */
}
映射接口:
EmployeesMapper.java
public interface EmployeesMapper {
boolean insert(Employees employee);
boolean updateById(Employees employee);
boolean deleteById(int id);
int deleteByIds(@Param("lowerId")int lowerId,@Param("upperId")int upperId);
Employees getById(int id);
List<Employees> getByIds(@Param("lowerId")int lowerId,@Param("upperId")int upperId);
}
DepartmentsMapper.java
public interface DepartmentsMapper {
boolean insert(Departments department);
boolean updateById(Departments department);
boolean deleteById(int id);
int deleteByIds(@Param("lowerId")int lowerId,@Param("upperId")int upperId);
Departments getById(int id);
List<Departments> getByIds(@Param("lowerId")int lowerId,@Param("upperId")int upperId);
}
映射SQL
文件:
employees-mapper.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.lishaoyin.mybatis_test.mapper.EmployeesMapper">
<resultMap type="com.lishaoyin.mybatis_test.entity.Employees" id="EmployeesMap">
<id column="id" property="id"/>
<result column="name" property="name"/>
<result column="gender" property="gender"/>
<result column="dptid" property="departmentId"/>
</resultMap>
<!-- boolean insert(Employees employee) -->
<insert id="insert">
insert into employees(name,gender,dptid)
values(#{name},#{gender},#{departmentId})
</insert>
<!-- boolean updateById(Employees employee) -->
<update id="updateById">
update employees set name=#{name},gender=#{gender},dptid=#{departmentId}
where id=#{id}
</update>
<!-- boolean deleteById(int id) -->
<delete id="deleteById">
delete from employees where id=#{id}
</delete>
<!-- int deleteByIds(@Param("lowerId")int lowerId,@Param("upperId")int upperId) -->
<delete id="deleteByIds">
delete from employees where id between #{lowerId} and #{upperId}
</delete>
<!-- Employees getById(int id) -->
<select id="getById" resultMap="EmployeesMap">
select * from employees where id=#{id}
</select>
<!-- List<Employees> getByIds(@Param("lowerId")int lowerId,@Param("upperId")int upperId) -->
<select id="getByIds" resultMap="EmployeesMap">
select * from employees where id between #{lowerId} and #{upperId}
</select>
</mapper>
departments-mapper.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.lishaoyin.mybatis_test.mapper.DepartmentsMapper">
<resultMap type="com.lishaoyin.mybatis_test.entity.Departments" id="DepartmentsMap">
<id column="id" property="id"/>
<result column="dptname" property="departmentName"/>
<result column="manager" property="manager"/>
<result column="tel" property="tel"/>
</resultMap>
<!-- boolean insert(Departments department) -->
<insert id="insert">
insert into departments(dptname,manager,tel)
values(#{departmentName},#{manager},#{tel})
</insert>
<!-- boolean updateById(Departments department) -->
<update id="updateById">
update departments set dptname=#{departmentName},manager=#{manager},tel=#{tel}
where id=#{id}
</update>
<!-- boolean deleteById(int id) -->
<delete id="deleteById">
delete from departments where id=#{id}
</delete>
<!-- int deleteByIds(@Param("lowerId")int lowerId,@Param("upperId")int upperId) -->
<delete id="deleteByIds">
delete from departments where id between #{lowerId} and #{upperId}
</delete>
<!-- Departments getById(int id) -->
<select id="getById" resultMap="DepartmentsMap">
select * from departments where id=#{id}
</select>
<!-- List<Departments> getByIds(@Param("lowerId")int lowerId,@Param("upperId")int upperId) -->
<select id="getByIds" resultMap="DepartmentsMap">
select * from departments where id between #{lowerId} and #{upperId}
</select>
</mapper>
在全局配置文件中注册SQL
映射文件:
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>
...
<mappers>
...
<!-- 注册SQL映射文件 -->
<mapper resource="com/lishaoyin/mybatis_test/res/employees-mapper.xml"/>
<mapper resource="com/lishaoyin/mybatis_test/res/departments-mapper.xml"/>
</mappers>
...
</configuration>
以上两表中,员工表的dptid
与部门表的id
具有连接关系,对于需要连接查询的操作,例如查询某员工的部门信息,可以使用级联属性方式。注意:使用连接查询需要在实体类中添加对应的级联实体类属性:
Employees.java(缺省)
public class Employees {
// ...
/* 添加department属性 */
private Departments department;
// ...
}
employees-mapper.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.lishaoyin.mybatis_test.mapper.EmployeesMapper">
<!-- type指定映射的类型,id指定在namespace下的唯一标识,方便select引用 -->
<resultMap type="com.lishaoyin.mybatis_test.entity.Employees" id="EmpDptMap">
<!-- 注意在多表连接时,column属性使用与select中对应的列的别名!!! -->
<id column="eid" property="id"/>
<result column="ename" property="name"/>
<result column="gender" property="gender"/>
<result column="edptid" property="departmentId"/>
<!-- 级联属性 -->
<result column="did" property="department.id"/>
<result column="dname" property="department.departmentName"/>
<result column="manager" property="department.manager"/>
<result column="tel" property="department.tel"/>
</resultMap>
<!-- Employees getById(int id) -->
<!-- 引用resultMap -->
<select id="getById" resultMap="EmpDptMap">
select
e.id as eid,
e.name as ename,
e.gender as gender,
e.dptid as edptid,
d.id as did,
d.dptname as dname,
d.manager as manager,
d.tel as tel
from employees as e
inner join departments as d
on e.dptid=d.id
where e.id=#{id}
</select>
<!-- List<Employees> getByIds(@Param("lowerId")int lowerId,@Param("upperId")int upperId) -->
<!-- 引用resultMap -->
<select id="getByIds" resultMap="EmpDptMap">
select
e.id as eid,
e.name as ename,
e.gender as gender,
e.dptid as edptid,
d.id as did,
d.dptname as dname,
d.manager as manager,
d.tel as tel
from employees as e
inner join departments as d
on e.dptid=d.id
where e.id between #{lowerId} and #{upperId}
</select>
</mapper>
调用:
/* 获取id在1~10之间的员工信息 */
List<Employees> list = mapper.getByIds(1,10);
for(Employees employee : list) {
System.out.println(employee.department);
}
association
标签实现连接查询
使用级联属性进行连接查询的关键在于属性.属性
的用法,即.
运算符的使用,级联属性方式将select
查询中声明的字段别名封装为级联属性。
可以使用association
标签实现连接查询。association
标签是resultMap
标签的子标签,用于封装具有连接关系的数据库表字段和实体类属性。这种方式将select
查询中声明的字段别名封装为指定实体类属性。
employees-mapper.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.lishaoyin.mybatis_test.mapper.EmployeesMapper">
<!-- type指定映射的类型,id指定在namespace下的唯一标识,方便select引用 -->
<resultMap type="com.lishaoyin.mybatis_test.entity.Employees" id="EmpDptMap">
<!-- 注意在多表连接时,column属性使用与select中对应的列的别名!!! -->
<id column="eid" property="id"/>
<result column="ename" property="name"/>
<result column="gender" property="gender"/>
<result column="edptid" property="departmentId"/>
<!-- property指定需要关联的实体类的属性,javaType指定实体类属性的全类名 -->
<association property="department"
javaType="com.lishaoyin.mybatis_test.entity.Departments">
<!-- 注意在多表连接时,column属性使用与select中对应的列的别名!!! -->
<id column="did" property="id"/>
<result column="dname" property="departmentName"/>
<result column="manager" property="manager"/>
<result column="tel" property="tel"/>
</association>
</resultMap>
<!-- Employees getById(int id) -->
<!-- 引用resultMap -->
<select id="getById" resultMap="EmpDptMap">
select
e.id as eid,
e.name as ename,
e.gender as gender,
e.dptid as edptid,
d.id as did,
d.dptname as dname,
d.manager as manager,
d.tel as tel
from employees as e
inner join departments as d
on e.dptid=d.id
where e.id=#{id}
</select>
<!-- List<Employees> getByIds(@Param("lowerId")int lowerId,@Param("upperId")int upperId) -->
<!-- 引用resultMap -->
<select id="getByIds" resultMap="EmpDptMap">
select
e.id as eid,
e.name as ename,
e.gender as gender,
e.dptid as edptid,
d.id as did,
d.dptname as dname,
d.manager as manager,
d.tel as tel
from employees as e
inner join departments as d
on e.dptid=d.id
where e.id between #{lowerId} and #{upperId}
</select>
</mapper>
调用:
/* 获取id在1~10之间的员工信息 */
List<Employees> list = mapper.getByIds(1,10);
for(Employees employee : list) {
System.out.println(employee.department);
}
association
标签实现分步查询
association
标签中的select
属性可以引用其它SQL
映射文件中的查询方法,使用column
属性传递列值给外部查询方法,外部查询方法的查询结果传给指定实体类的属性。这种方式可以组合已有的方法完成复杂的查询。
employees-mapper.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.lishaoyin.mybatis_test.mapper.EmployeesMapper">
<!-- type指定映射的类型,id指定在namespace下的唯一标识,方便select引用 -->
<resultMap type="com.lishaoyin.mybatis_test.entity.Employees" id="EmpDptMap">
<!-- 注意在多表连接时,column属性使用与select中对应的列的别名!!! -->
<id column="eid" property="id"/>
<result column="ename" property="name"/>
<result column="gender" property="gender"/>
<result column="edptid" property="departmentId"/>
<!-- property指定需要关联的实体类的属性,select引用外部SQL映射文件中的查询方法,
column将指定列值传递给外部查询方法。注意:column使用select查询中声明的别名 -->
<association property="department"
select="com.lishaoyin.mybatis_test.mapper.DepartmentsMapper.getById"
column="did"/>
</resultMap>
<!-- Employees getById(int id) -->
<!-- 引用resultMap -->
<select id="getById" resultMap="EmpDptMap">
select
e.id as eid,
e.name as ename,
e.gender as gender,
e.dptid as edptid,
d.id as did,
d.dptname as dname,
d.manager as manager,
d.tel as tel
from employees as e
inner join departments as d
on e.dptid=d.id
where e.id=#{id}
</select>
<!-- List<Employees> getByIds(@Param("lowerId")int lowerId,@Param("upperId")int upperId) -->
<!-- 引用resultMap -->
<select id="getByIds" resultMap="EmpDptMap">
select
e.id as eid,
e.name as ename,
e.gender as gender,
e.dptid as edptid,
d.id as did,
d.dptname as dname,
d.manager as manager,
d.tel as tel
from employees as e
inner join departments as d
on e.dptid=d.id
where e.id between #{lowerId} and #{upperId}
</select>
</mapper>
调用:
/* 获取id在1~10之间的员工信息 */
List<Employees> list = mapper.getByIds(1,10);
for(Employees employee : list) {
System.out.println(employee.department);
}
可以在association
分步查询的基础上实现延迟加载。以上的连接查询均在查询employees
表时同时查询了departments
表,MyBatis
可以实现按需查询,仅在需要时执行association
标签指定的关联查询,在不需要时可以不执行,有利于节省数据库资源。具体做法是在全局<settings>
中改变以下属性:
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>
...
<settings>
<!-- 开启懒加载 -->
<setting name="lazyLoadingEnabled" value="true"/>
<!-- 关闭侵略性的懒加载,只在需要时加载 -->
<setting name="aggressiveLazyLoading" value="false"/>
</settings>
...
</configuration>
假如有多个关联属性,还可以使用association
标签的fetchType
关闭或打开某个分支的延迟加载:
<!-- fetchType="lazy"指定当前分支懒加载;fetchType="eager"指定当前分支立即加载 -->
<association property="..."
select="..."
column="..."
fetchType="true"/>
collection
标签实现一对多的连接查询
以上使用association
标签进行连接查询时,均为一对一的连接查询,即一个员工对应一个部门,因此Employees
类中对应的关联属性仅仅是一个单独的实体类Departments
。对于多对一的连接查询,例如一个部门对应多个员工,则需要添加一个集合性质的属性,例如在Departments
类中添加以下属性:
Departments.java(缺省)
public class Departments {
// ...
/* 添加employeesList属性 */
private List<Employees> employeesList;
// ...
}
departments-mapper.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.lishaoyin.mybatis_test.mapper.DepartmentsMapper">
<!-- type指定映射的类型,id指定在namespace下的唯一标识,方便select引用 -->
<resultMap type="com.lishaoyin.mybatis_test.entity.Departments" id="DptEmpMap">
<!-- 注意在多表连接时,column属性使用与select中对应的列的别名!!! -->
<id column="did" property="id"/>
<result column="dname" property="departmentName"/>
<result column="manager" property="manager"/>
<result column="tel" property="tel"/>
<!-- property指定关联属性,ofType指定集合中元素类型的全类名 -->
<collection property="employeesList"
ofType="com.lishaoyin.mybatis_test.entity.Employees">
<!-- 注意在多表连接时,column属性使用与select中对应的列的别名!!! -->
<id column="eid" property="id"/>
<result column="ename" property="name"/>
<result column="gender" property="gender"/>
<result column="edptid" property="departmentId"/>
</collection>
</resultMap>
<!-- Departments getById(int id) -->
<select id="getById" resultMap="DptEmpMap">
select
e.id as eid,
e.name as ename,
e.gender as gender,
e.dptid as edptid,
d.id as did,
d.dptname as dname,
d.manager as manager,
d.tel as tel
from employees as e
inner join departments as d
on e.dptid=d.id
where d.id=#{id}
</select>
<!-- List<Departments> getByIds(@Param("lowerId")int lowerId, @Param("upperId")int upperId)
-->
<select id="getByIds" resultMap="DptEmpMap">
select
e.id as eid,
e.name as ename,
e.gender as gender,
e.dptid as edptid,
d.id as did,
d.dptname as dname,
d.manager as manager,
d.tel as tel
from employees as e
inner join departments as d
on e.dptid=d.id
where d.id between #{lowerId} and #{upperId }
</select>
</mapper>
调用:
/* 查询id在1~6之间的部门 */
List<Departments> list = mapper.getByIds(1,6);
for(Departments department : list) {
for(Employees employee : department.getEmployeesList()) {
System.out.println(employee);
}
System.out.println("----------------------------------------");
}
collection
标签实现一对多的分步查询
与association
标签用法一致,collection
标签中的select
属性可以引用其它SQL
映射文件中的查询方法,使用column
属性传递列值给外部查询方法,外部查询方法的查询结果传给指定实体类的属性。这种方式可以组合已有的方法完成复杂的查询。在EmployeesMapper
接口中添加以下方法:
EmployeesMapper.java(缺省)
public interface EmployeesMapper {
// ...
List<Employees> getByDepartmentId(int departmentId);
// ...
}
对应SQL
映射文件中添加:
employees-mapper.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.lishaoyin.mybatis_test.mapper.EmployeesMapper">
...
<!-- List<Employees> getByDepartmentId(int departmentId) -->
<select id="getByDepartmentId" resultMap="EmployeesMap">
select * from employees where dptid=#{dptid}
</select>
...
</mapper>
departments-mapper.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.lishaoyin.mybatis_test.mapper.DepartmentsMapper">
<!-- type指定映射的类型,id指定在namespace下的唯一标识,方便select引用 -->
<resultMap type="com.lishaoyin.mybatis_test.entity.Departments" id="DptEmpMap">
<!-- 注意在多表连接时,column属性使用与select中对应的列的别名!!! -->
<id column="did" property="id"/>
<result column="dname" property="departmentName"/>
<result column="manager" property="manager"/>
<result column="tel" property="tel"/>
<!-- property指定关联属性,select调用外部方法,column传递列值给外部方法 -->
<collection property="employeesList"
select="com.lishaoyin.mybatis_test.mapper.EmployeesMapper.getByDepartmentId"
column="did"/>
</resultMap>
<!-- Departments getById(int id) -->
<select id="getById" resultMap="DptEmpMap">
select
e.id as eid,
e.name as ename,
e.gender as gender,
e.dptid as edptid,
d.id as did,
d.dptname as dname,
d.manager as manager,
d.tel as tel
from employees as e
inner join departments as d
on e.dptid=d.id
where d.id=#{id}
</select>
<!-- List<Departments> getByIds(@Param("lowerId")int lowerId, @Param("upperId")int upperId)
-->
<select id="getByIds" resultMap="DptEmpMap">
select
e.id as eid,
e.name as ename,
e.gender as gender,
e.dptid as edptid,
d.id as did,
d.dptname as dname,
d.manager as manager,
d.tel as tel
from employees as e
inner join departments as d
on e.dptid=d.id
where d.id between #{lowerId} and #{upperId }
</select>
</mapper>
调用:
/* 查询id在1~6之间的部门 */
List<Departments> list = mapper.getByIds(1,6);
for(Departments department : list) {
for(Employees employee : department.getEmployeesList()) {
System.out.println(employee);
}
System.out.println("----------------------------------------");
}
补充:association
标签与collection
标签引用外部方法使用column
属性传值时,如果外部方法需要多个参数,可以使用column
属性传递多个参数,具体的做法为:
<association property="department"
select="..."
column="{参数名1=列名1,参数名2=列名2},...,"/>
<collection property="employeesList"
select="..."
column="{参数名1=列名1,参数名2=列名2},...,"/>
collection
标签同样拥有与association
标签相同的延迟加载机制。
动态SQL
概述
动态SQL
是MyBatis
的重要特性之一。之前的做法中,使用SQL
语句实现查询方法时,每个SQL
语句的结构是固定的,使用动态SQL
可以根据具体需求改变SQL
结构。
if
动态SQL
用法
if
表达式使用test
属性判断指定条件是否为真,如果为真,则查询语句追加指定内容;如果为假,则不追加指定内容。if
表达式是OGNL
表达式中的一种。
<select ...>
<!-- 残缺查询语句 -->
select * from employees where
<!-- 如果传入的实体类属性id不等于0,则查询语句追加id=#{id} -->
<if test="id!=0">
id=#{id}
</if>
<!-- 如果传入的实体类属性gender不为空且不为空字符串,则查询语句追加gender=#{gender} -->
<if test="gender!=null and gender!=''">
and gender=#{gender}
</if>
<!-- OGNL表达式也可以调用方法(trim()是String的方法) -->
<if test="name!=null and name.trim()!=''">
and name=#{name}
</if>
</select>
示例
在EmployeesMapper
接口中添加以下方法:
EmployeesMapper.java(缺省)
public interface EmployeesMapper {
// ...
List<Employees> getEmployees(Employees employees);
// ...
}
employees-mapper.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.lishaoyin.mybatis_test.mapper.EmployeesMapper">
...
<!-- List<Employees> getEmployees(Employees employees) -->
<select id="getEmployees" resultMap="EmployeesMap">
select * from employees where
<!-- 注意<if>中判断的均为接口方法传入的参数 -->
<if test="id!=0">
id>#{id}
</if>
<if test="name!=null and name.trim()!=''">
and name=#{name}
</if>
<!-- "是java中字符"的转义,在进行字符串判断时,必须使用"的转义,
否则将出现类型转换错误 -->
<if test="gender=="f" or gender=="m" and gender.trim()!=''">
and gender=#{gender}
</if>
<if test="departmentId!=0">
and dptid=#{departmentId}
</if>
</select>
...
</mapper>
调用:
Employees employees = new Employees(1,"","f",2);
List<Employees> list = mapper.getEmployees(employees);
for(Employees e : list) {
System.out.println(e);
}
if
语句的缺陷
if
语句使用时,必须保证第一个test
条件成立,否则将出现SQL
语法错误,因为之后的if
语句中的SQL
追加都包含了and
语句,而第一条SQL
追加中不包含and
语句。如果第一条SQL
追加未被加上而其余被加上则会出现SQL
语法错误。例如:
# and多余导致SQL语法错误
select * from employees where and name=? and gender=? and dptid=?
解决方式一
将残缺SQL
后面添加1=1
条件,为第一个SQL
追加添加and
语句:
<!-- List<Employees> getEmployees(Employees employees) -->
<select id="getEmployees" resultMap="EmployeesMap">
<!-- 添加第一个条件:"1=1" -->
select * from employees where 1=1
<if test="id!=0">
<!-- 原本的第一个条件添加and语句变为第二个条件 -->
and id=#{id}
</if>
<if test="name!=null and name.trim()!=''">
and name=#{name}
</if>
<if test="gender=="f" or gender=="m" and gender.trim()!=''">
and gender=#{gender}
</if>
<if test="departmentId!=0">
and dptid=#{departmentId}
</if>
</select>
解决方式二
使用MyBatis
提供的where
语句代替残缺SQL
语句中的where
语句,MyBatis
中的where
语句的作用是:去除拼装SQL
语句中第一个可能出现的多余的and/or
语句,并加上where
语句。在动态SQL
中推荐使用MyBatis
的where
语句代替SQL
中的where
语句。
<!-- List<Employees> getEmployees(Employees employees) -->
<select id="getEmployees" resultMap="EmployeesMap">
<!-- 去除残缺SQL中的where语句 -->
select * from employees
<!-- 使用MyBatis提供的where语句代替残缺SQL中的where语句 -->
<where>
<if test="id!=0">
id=#{id}
</if>
<if test="name!=null and name.trim()!=''">
and name=#{name}
</if>
<if test="gender=="f" or gender=="m" and gender.trim()!=''">
and gender=#{gender}
</if>
<if test="departmentId!=0">
and dptid=#{departmentId}
</if>
</where>
</select>
解决方式三
where
标签只能去掉拼装SQL
语句中第一个多余的and/or
,实际应用中的需求是复杂多样的,例如可能会出现and/or
置于每个追加SQL
语句之后的情况:
<if test="id!=0">
<!-- and置于追加SQL语句之后 -->
id=#{id} and
</if>
<if test="name!=null and name.trim()!=''">
<!-- and置于追加SQL语句之后 -->
name=#{name} and
</if>
此时再使用where
语句将导致出现SQL
语法错误。使用trim
语句可以自定义拼装SQL
语句的修正:
<!-- prefix属性:前缀追加,将拼装SQL语句整体之前加上指定字符串
prefixOverrides属性:前缀覆盖,将拼装SQL语句整体之前的指定字符串去除
suffix属性:后缀追加:将拼装SQL语句整体之后加上指定字符串
suffixOverrides属性:后缀去除,将拼装SQL语句整体之后的指定字符串去除
-->
<trim prefix="..." prefixOverrides="..." suffix="..." suffixOverrides="...">
<if...>...</if...>
...
</trim>
示例:
<!-- List<Employees> getEmployees(Employees employees) -->
<select id="getEmployees" resultMap="EmployeesMap">
select * from employees
<!-- 加上"where"前缀,去除可能产生的"and"后缀 -->
<trim prefix="where" suffixOverrides="and">
<if test="id!=0">
id=#{id} and
</if>
<if test="name!=null and name.trim()!=''">
name=#{name} and
</if>
<if test="gender=="f" or gender=="m" and gender.trim()!=''">
gender=#{gender} and
</if>
<if test="departmentId!=0">
dptid=#{departmentId} and
</if>
</trim>
</select>
if
语句与set
语句的配合使用
动态SQL
不仅可以用于动态改变查询条件,也可以用于动态改变其它SQL
结构,例如可以用于更新语句。在EmployeesMapper
接口中添加以下方法:
EmployeesMapper.java(缺省)
public interface EmployeesMapper {
// ...
void update(Employees employee);
// ...
}
employees-mapper.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.lishaoyin.mybatis_test.mapper.EmployeesMapper">
...
<!-- void update(Employees employee) -->
<update id="update">
update employees set
<if test="name!=null and name.trim()!=''">
name=#{name},
</if>
<if test="gender=="f" or gender=="m" and gender.trim()!=''">
gender=#{gender},
</if>
<if test="departmentId!=0">
dptid=#{departmentId}
</if>
where id=#{id}
</update>
...
</mapper>
调用:
/* 将id为2的员工的部门编号修改为1 */
mapper.update(new Employees(2,"","",1));
/* 注意提交事务或回滚事务 */
session.commit();
update
语句的set
列表中使用","
隔开多个设置子语句,如果最后一个if
条件不成立,将导致拼装SQL
语句中出现多余的","
,使用MyBatis
提供的set
语句可解决此问题:
employees-mapper.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.lishaoyin.mybatis_test.mapper.EmployeesMapper">
...
<!-- void update(Employees employee) -->
<update id="update">
<!-- 去除SQL语句中的set语句 -->
update employees
<!-- 使用MyBatis提供的set语句代替SQL中的set语句 -->
<set>
<if test="name!=null and name.trim()!=''">
name=#{name},
</if>
<if test="gender=="f" or gender=="m" and gender.trim()!=''">
gender=#{gender},
</if>
<if test="departmentId!=0">
dptid=#{departmentId}
</if>
</set>
where id=#{id}
</update>
...
</mapper>
类似于where
语句,set
语句的作用是:为拼装sql
语句添加set
前缀,去除可能出现的多余的","
。可使用trim
语句实现相同功能。在动态SQL
中推荐使用MyBatis
的set
语句代替SQL
中的set
语句。
choose
动态SQL
用法
if
语句将多个条件拼装,而choose
语句类似于java
中的switch/case
语句,仅选择符合条件的一个SQL
追加语句进行拼装:
<choose>
<!-- 仅选择符合条件的一个SQL追加语句进行拼装 -->
<when test="id!=null">
id=#{id}
</when>
...
<!-- otherwise指定不符合所有choose列表时的SQL追加语句 -->
<otherwise>
1=1
</otherwise>
</choose>
如果实际传入的值同时满足多个choose
条件,则选择最靠前的符合条件的choose
分支,因为choose
语句仅选择一个分支进行拼装。
示例
employees-mapper.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.lishaoyin.mybatis_test.mapper.EmployeesMapper">
...
<!-- List<Employees> getEmployees(Employees employees) -->
<select id="getEmployees" resultMap="EmployeesMap">
select * from employees
<where>
<choose>
<when test="id!=0">
id=#{id}
</when>
<when test="name!=null and name.trim()!=''">
name=#{name}
</when>
<when test="gender=="f" or gender=="m" and gender.trim()!=''">
gender=#{gender}
</when>
<when test="departmentId!=0">
dpid=#{departmentId}
</when>
<!-- 未带任何条件则查询所有 -->
<otherwise>
1=1
</otherwise>
</choose>
</where>
</select>
...
</mapper>
调用:
Employees employees = new Employees(1,"","f",2);
List<Employees> list = mapper.getEmployees(employees);
for(Employees e : list) {
System.out.println(e);
}
foreach
动态SQL
用法
使用foreach
可以在SQL
语句中实现类似于java
中的for
循环,每循环一次可以产生一个值提供SQL
语句使用。foreach
的用法:
<select ...>
select * from employees where id in
(
<!-- collection指定接口方法中传入的集合,item指定一个名称用于引用每次取出的集合元素 -->
<foreach collection="..." item="...">
#{item},
</foreach>
)
</select>
in
列表中的元素之间需要使用","
分隔,因此以上foreach
结尾会多出一个","
,并且将整个foreach
置于()
中影响排版,可以使用foreach
的其它属性改善:
<select ...>
select * from employees where id in
<!-- collection指定接口方法中传入的集合,可以使用MyBatis预留的list标识;
item指定一个名称用于引用每次取出的集合元素;
separator=","指定元素之间使用","分隔;
open="(" close=")"指定使用"()"包括foreach语句;
index对于list而言是索引,对于map而言是key -->
<foreach collection="..." item="..." separator="," open="(" close=")" index="...">
#{item}
</foreach>
</select>
示例
在EmployeesMapper
中添加以下方法:
EmployeesMapper.java(缺省)
public interface EmployeesMapper {
// ...
/* 使用@Param注解为参数指定标识 */
List<Employees> getByIdList(@Param("ids")List<Integer> ids);
// ...
}
employees-mapper.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.lishaoyin.mybatis_test.mapper.EmployeesMapper">
...
<!-- List<Employees> getByIdList(@Param("ids")List<Integer> ids) -->
<select id="getByIdList" resultMap="EmployeesMap">
select * from employees where id in
<!-- collection属性指定接口方法中传入的集合,在此引用@Param指定的参数标识 -->
<foreach collection="ids" item="item_id" separator="," open="(" close=")">
#{item_id}
</foreach>
</select>
...
</mapper>
调用:
/* 查询id为1,2,3,4,5,6,7,8,9的员工 */
List<Employees> list = mapper.getByIdList(Arrays.asList(1,2,3,4,5,6,7,8,9));
for(Employees e : list) {
System.out.println(e);
}
foreach
用于批量插入
方式一
在EmployeesMapper
接口中添加以下方法:
EmployeesMapper.java(缺省)
public interface EmployeesMapper {
// ...
/* 使用@Param注解为参数指定标识 */
void insertByList(@Param("employees")List<Employees> employees);
// ...
}
employees-mapper.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.lishaoyin.mybatis_test.mapper.EmployeesMapper">
...
<!-- void insertByList(@Param("employees")List<Employees> employees) -->
<insert id="insertByList">
insert into employees(name,gender,dptid) values
<!-- collection属性指定接口方法中传入的集合,在此引用@Param指定的参数标识 -->
<foreach collection="employees" item="e" separator=",">
(#{e.name},#{e.gender},#{e.departmentId})
</foreach>
</insert>
...
</mapper>
调用:
Employees employee_1 = new Employees(0,"王辉","m",6);
Employees employee_2 = new Employees(0,"雷明","m",6);
Employees employee_3 = new Employees(0,"张严","m",7);
Employees employee_4 = new Employees(0,"吕兰","f",7);
Employees employee_5 = new Employees(0,"肖萧","f",7);
List<Employees> employees = new ArrayList<Employees>();
employees.add(employee_1);
employees.add(employee_2);
employees.add(employee_3);
employees.add(employee_4);
employees.add(employee_5);
mapper.insertByList(employees);
/* 注意提交事务或回滚事务 */
session.commit();
方式二
employees-mapper.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.lishaoyin.mybatis_test.mapper.EmployeesMapper">
...
<!-- void insertByList(@Param("employees")List<Employees> employees) -->
<insert id="insertByList">
<!-- collection属性指定接口方法中传入的集合,在此引用@Param指定的参数标识 -->
<!-- MySQL的SQL语句之间使用";"分隔 -->
<foreach collection="employees" item="e" separator=";">
<!-- 遍历完整的SQL语句 -->
insert into employees(name,gender,dptid)
values(#{e.name},#{e.gender},#{e.departmentId})
</foreach>
</insert>
...
</mapper>
注意:MySQL
连续执行SQL
语句需要将连接基本信息中的url
中追加参数:
# 追加allowMultiQueries=true
url=jdbc:mysql://localhost:1003/mybatis_test?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC&allowMultiQueries=true
调用:
Employees employee_1 = new Employees(0,"王辉","m",6);
Employees employee_2 = new Employees(0,"雷明","m",6);
Employees employee_3 = new Employees(0,"张严","m",7);
Employees employee_4 = new Employees(0,"吕兰","f",7);
Employees employee_5 = new Employees(0,"肖萧","f",7);
List<Employees> employees = new ArrayList<Employees>();
employees.add(employee_1);
employees.add(employee_2);
employees.add(employee_3);
employees.add(employee_4);
employees.add(employee_5);
mapper.insertByList(employees);
/* 注意提交事务或回滚事务 */
session.commit();
抽取可重用SQL
片段
用法
实际使用SQL
映射文件编写SQL
时,会产生大量重复的SQL
片段,例如插入列表,可以使用sql
标签抽取这些SQL
片段,编写SQL
时直接引用即可。
<!-- sql标签是select,insert等的同级标签 -->
<sql id="唯一标识">
SQL片段
</sql>
<insert id="...">
<!-- 使用include标签引用抽取的SQL语句,refid属性指定引用的唯一标识 -->
insert into employees(<include refid="insertColumns"/>) values(...)
</insert>
示例
以下示例使用sql
标签抽取插入列表:
employees-mapper.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.lishaoyin.mybatis_test.mapper.EmployeesMapper">
...
<sql id="insertColumns">
name,gender,dptid
</sql>
<!-- boolean insert(Employees employee) -->
<insert id="insert">
<!-- 引用插入列表 -->
insert into employees(<include refid="insertColumns"/>)
values(#{name},#{gender},#{departmentId})
</insert>
...
</mapper>
缓存机制
一级缓存
概述
一级缓存是会话级别的缓存,一个一级缓存的作用范围仅限于对应的SqlSession
对象。一级缓存是一直开启的,不能关闭。一级缓存会将被查询过的每一条记录保存在本地,当需要重复查询某些记录时,可以直接从本地缓存中取出,而无需再次发送SQL
语句,提高数据库资源的利用率。
一级缓存是SqlSession
中的一个Map
。SqlSession
每次执行查询时,先在一级缓存Map
中查找是否有存在该记录,如果不存在,再向数据库发送SQL
语句。一级缓存会在会话关闭后自动清空。
一级缓存失效的情况
1.会话不同,记录相同
对于新的会话(新的SqlSession对象)查询已经查询过的记录,仍然会发送相同的SQL语句
2.会话相同,记录不同
同一个会话(同一个SqlSession对象)查询未被本次会话查询过的记录
3.执行了事务性操作
同一个会话(同一个SqlSession对象)先查询一条记录,进行增删改操作之后再次查询同一条记录,
仍然会发送相同的SQL语句,因为MyBatis认为事务性的操作可能影响了被查询过的记录,
为了安全起见再次发送相同的SQL语句
4.清空一级缓存
使用SqlSession中的clearCache()方法清空缓存,将导致一级缓存中的所有记录被清除。
注意:clearCache()只能清除一级缓存,不能清除二级缓存
二级缓存
概述
二级缓存基于mapper
的namespace
,其作用范围是全局的,对于同一个mapper
的所有查询均有作用。二级缓存使用需要开启,如果开启了二级缓存,只有会话提交或关闭后,对应的一级缓存中的记录才会被保存到二级缓存中,新的会话可以查询到已被其他会话查询过的记录。
二级缓存的开启
1.全局使能二级缓存:在全局配置文件中的settings
标签中开启二级缓存。
<?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>
...
<settings>
...
<!-- 开启全局二级缓存 -->
<setting name="cacheEnabled" value="true"/>
...
</settings>
...
</configuration>
2.Mapper
开启二级缓存:在对应SQL
映射文件中添加cache
标签并配置相应属性。
<?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.lishaoyin.mybatis_test.mapper.EmployeesMapper">
...
<!-- eviction:指定二级缓存的回收策略,即二级缓存存满之后,删除哪些多余部分,其值有:
LRU - 删除最少使用的,默认值
FIFO - 先进先出,按照存入顺序移除多余的
SOFT - 软引用,移除基于垃圾回收器状态和软引用规则的对象
WEAK - 弱引用,更积极地移除基于垃圾回收器状态和软引用规则的对象
flushInterval:指定缓存刷新间隔,即指定缓存的清空周期,默认不清空,可以自定义毫秒值
reasOnly:指定缓存是否只读,true|false
size:指定缓存中存储的最大元素数量
type:指定自定义缓存全类名,自定义缓存需要实现Cache接口,MyBatis有默认缓存实现 -->
<cache eviction="..." flushInterval="..." readOnly="..." size="..." type="..."/>
...
</mapper>
3.实体类序列化:对应实体类对象实现序列化接口。
public class Employees implements Serializable {
public static final long serialVersionUID = 12321313123L;
// ...
}
缓存的使用
select
标签具有useCache
属性,可以分别设置每个select
标签的useCache
属性从而控制具体查询操作的缓存使用行为。
<!-- useCache="true",指定本次查询使用一级缓存和二级缓存,默认为true;
useCache="false",指定本次查询使用一级缓存,不使用二级缓存 -->
<select id="..." resultMap="..." useCache="true">
...
</select>
事务性的查询标签(insert,delete,update
)具有flushCache
标签,指定提交事务之后是否清空缓存。
<!-- flushCache="true",指定提交事务后清空缓存(一级缓存和二级缓存都清空),默认为true;
flushCache="false",指定提交事务后不清空缓存 -->
<insert id="..." flushCache="true">
...
</insert>
select
标签中也可设置flushCache
属性,但默认值为false
.