1. 什么是Mybatis
mybatis 是一个优秀的基于java的持久层框架，它内部封装了jdbc，使开发者只需要关注sql语句本身，而不需要花费精力去处理加载驱动、创建连接、创建statement等繁杂的过程。
mybatis通过xml或注解的方式将要执行的各种 statement配置起来，并通过java对象和statement中sql的动态参数进行映射生成最终执行的sql语句。
最后mybatis框架执行sql并将结果映射为java对象并返回。采用ORM思想解决了实体和数据库映射的问题，对jdbc 进行了封装，屏蔽了jdbc api 底层访问细节，使我们不用与jdbc api 打交道，就可以完成对数据库的持久化操作。
2. MyBatis开发步骤
- 添加
MyBatis的坐标 - 创建
user数据表 - 编写
User实体类 - 编写映射文件
UserMapper.xml - 编写核心文件
SqlMapConfig.xml - 编写测试类
3. Mybatis环境搭建
MyBatis官网地址：https://mybatis.org/mybatis-3/zh/index.html
- 导入
MyBatis的坐标和其他相关坐标
<?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>org.example</groupId>
<artifactId>MyBatis_001</artifactId>
<version>1.0-SNAPSHOT</version>
<properties>
<maven.compiler.source>8</maven.compiler.source>
<maven.compiler.target>8</maven.compiler.target>
</properties>
<!-- dependencies 依赖包的意思-->
<dependencies>
<!--mybatis坐标-->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.4</version>
</dependency>
<!--mysql驱动坐标-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.6</version>
<scope>runtime</scope>
</dependency>
<!--单元测试坐标-->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>
test
</scope>
</dependency>
<!--日志坐标-->
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.12</version>
</dependency>
<dependency>
<groupId>org.junit.jupiter</groupId>
<artifactId>junit-jupiter</artifactId>
<version>RELEASE</version>
<scope>compile</scope>
</dependency>
</dependencies>
</project>
- 执行Maven
- 创建
users数据表
- 编写
User实体
package com.demo.core;
public class User {
private Integer id;
private String username;
private String password;
public User() {
}
public User(Integer id, String username, String password) {
this.id = id;
this.username = username;
this.password = password;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", password='" + password + '\'' +
'}';
}
public Integer getId() {
return id;
}
public void setId(Integer 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;
}
}- 编写
UserMapper映射文件(在resources目录下)
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"https://mybatis.org/dtd/mybatis-3-config.dtd">
<!--配置-->
<configuration>
<!--配置环境变量-->
<environments default="development">
<environment id="development">
<!--使用JDBC作为驱动内核-->
<transactionManager type="JDBC"/>
<!--配置事务的类型-->
<dataSource type="POOLED">
<!--四个属性,驱动,链接,用户名,密码-->
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://192.168.100.141:3306/security"/>
<property name="username" value="root"/>
<property name="password" value="redhat"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="./UserMapper.xml"/>
</mappers>
</configuration>- 创建UserMapper.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="userMapper">
<!--
将 select * from users 获得的结果
作为一个对象的属性封装到com.demo.core.User类型的对象中
-->
<select id="findAll" resultType="com.demo.core.User">
select *
from users
</select>
</mapper>
4. Mybatis执行sql查询并封装数据
- 通过执行
TestClass.java类中的main方法,来实现将sql语句之后的内容,封装到对应的对象之中
package com.demo.core;
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;
import java.util.List;
public class TestClass {
// 通过执行TestClass.java类中的main方法,来实现将sql语句之后的内容,封装到对应的对象之中
public static void main(String[] args) throws IOException {
//加载核心配置文件,从mybatis-config.xml中加载对应的资源
InputStream resourceAsStream = Resources.getResourceAsStream("mybatis-config.xml");
//获得sqlSession工厂对象,底层连接数据库
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
//获得sqlSession对象
SqlSession sqlSession = sqlSessionFactory.openSession();
//执行sql查询语句
// List<User> userList = sqlSession.selectList("userMapper.findAll");
List<Object> userList = sqlSession.selectList("userMapper.findAll");
//打印结果
System.out.println(userList);
//释放资源
sqlSession.close();
}
}5. MyBatis的映射文件概述
6. Mybatis执行增删改语句
6.1. update
修改操作注意问题
- 修改语句使用
update标签 - 修改操作使用的
API是sqlSession.update(“命名空间.id”,实体对象);
// 将id为10的username字段中的内容改为Tom
package com.demo.core;
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;
import java.util.List;
public class TestClass {
// 通过执行TestClass.java类中的main方法,来实现将sql语句之后的内容,封装到对应的对象之中
public static void main(String[] args) throws IOException {
//加载核心配置文件,从mybatis-config.xml中加载对应的资源
InputStream resourceAsStream = Resources.getResourceAsStream("mybatis-config.xml");
//获得sqlSession工厂对象,底层连接数据库
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
//获得sqlSession对象
SqlSession sqlSession = sqlSessionFactory.openSession();
//执行sql查询语句
// List<User> userList = sqlSession.selectList("userMapper.findAll");
// List<Object> userList = sqlSession.selectList("userMapper.findAll");
//打印结果
// System.out.println(userList);
//执行sql修改语句
int update = sqlSession.update("userMapper.updateId");
System.out.println(update);
//释放资源
sqlSession.close();
}
}<?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="userMapper">
<!--
将 select * from users 获得的结果
作为一个对象的属性封装到com.demo.core.User类型的对象中
-->
<select id="findAll" resultType="com.demo.core.User">
select *
from users
</select>
<!--// 将id为10的username字段中的内容改为Tom-->
<update id="updateId">
update users
set username = 'Tom'
where id = 10
</update>
</mapper>
6.2. insert
插入操作注意问题
- 插入语句使用
insert标签 - 在映射文件中使用
parameterType属性指定要插入的数据类型 Sql语句中使用#{实体属性名}方式引用实体中的属性值- 插入操作使用的
API是sqlSession.insert(“命名空间.id”,实体对象); - 插入操作涉及数据库数据变化，所以要使用
sqlSession对象显示的提交事务，即sqlSession.commit()
#{} 将来会被实际值给替换
insert into user values(#{id},#{username},#{password})
package com.demo.core;
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.jupiter.api.Test;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
public class TestClass {
// 通过执行TestClass.java类中的main方法,来实现将sql语句之后的内容,封装到对应的对象之中
public static void main(String[] args) throws IOException {
//加载核心配置文件,从mybatis-config.xml中加载对应的资源
InputStream resourceAsStream = Resources.getResourceAsStream("mybatis-config.xml");
//获得sqlSession工厂对象,底层连接数据库
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
//获得sqlSession对象
SqlSession sqlSession = sqlSessionFactory.openSession();
//执行sql查询语句
// List<User> userList = sqlSession.selectList("userMapper.findAll");
// List<Object> userList = sqlSession.selectList("userMapper.findAll");
//打印结果
// System.out.println(userList);
//执行sql修改语句
// int update = sqlSession.update("userMapper.updateId");
// System.out.println(update);
//执行sql添加语句
User user = new User(26, "hahaha", "123456");
int insert = sqlSession.insert("userMapper.insertOneRow", user);
// 此时的user就是一个parameterType类型的对象
System.out.println(insert);
//释放资源
sqlSession.close();
}
}package com.demo.core;
public class User {
private Integer id;
private String username;
private String password;
public User() {
}
public User(Integer id, String username, String password) {
this.id = id;
this.username = username;
this.password = password;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", password='" + password + '\'' +
'}';
}
public Integer getId() {
return id;
}
public void setId(Integer 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;
}
}<?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="userMapper">
<!--
将 select * from users 获得的结果
作为一个对象的属性封装到com.demo.core.User类型的对象中
-->
<select id="findAll" resultType="com.demo.core.User">
select *
from users
</select>
<!--// 将id为10的username字段中的内容改为Tom-->
<update id="updateId">
update users
set username = 'Tom'
where id = 10
</update>
<!--com.demo.core.User: 传递的参数是User类型的对象-->
<!-- insert into user values(#{id},#{username},#{password}) -->
<!-- #{} 将来会被实际值给替换 -->
<insert id="insertOneRow" parameterType="com.demo.core.User">
insert into users
values (#{id}, #{username}, #{password})
</insert>
</mapper>
6.3. delete
删除操作注意问题
- 删除语句使用
delete标签 Sql语句中使用#{任意字符串}方式引用传递的单个参数- 删除操作使用的
API是sqlSession.delete(“命名空间.id”,Object);
package com.demo.core;
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.jupiter.api.Test;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
public class TestClass {
// 通过执行TestClass.java类中的main方法,来实现将sql语句之后的内容,封装到对应的对象之中
public static void main(String[] args) throws IOException {
//加载核心配置文件,从mybatis-config.xml中加载对应的资源
InputStream resourceAsStream = Resources.getResourceAsStream("mybatis-config.xml");
//获得sqlSession工厂对象,底层连接数据库
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
//获得sqlSession对象
SqlSession sqlSession = sqlSessionFactory.openSession();
//执行sql查询语句
// List<User> userList = sqlSession.selectList("userMapper.findAll");
// List<Object> userList = sqlSession.selectList("userMapper.findAll");
//打印结果
// System.out.println(userList);
//执行sql修改语句
// int update = sqlSession.update("userMapper.updateId");
// System.out.println(update);
//执行sql添加语句
// User user = new User(26, "hahaha", "123456");
// int insert = sqlSession.insert("userMapper.insertOneRow", user);
// 此时的user就是一个parameterType类型的对象
// System.out.println(insert);
//执行sql删除语句
int delete = sqlSession.delete("userMapper.deleteOneRow", 20);
System.out.println(delete);
//释放资源
sqlSession.close();
}
}<?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="userMapper">
<!--
将 select * from users 获得的结果
作为一个对象的属性封装到com.demo.core.User类型的对象中
-->
<select id="findAll" resultType="com.demo.core.User">
select *
from users
</select>
<!--// 将id为10的username字段中的内容改为Tom-->
<update id="updateId">
update users
set username = 'Tom'
where id = 10
</update>
<!--com.demo.core.User: 传递的参数是User类型的对象-->
<!-- insert into user values(#{id},#{username},#{password}) -->
<!-- #{} 将来会被实际值给替换 -->
<insert id="insertOneRow" parameterType="com.demo.core.User">
insert into users
values (#{id}, #{username}, #{password})
</insert>
<delete id="deleteOneRow" parameterType="java.lang.Integer">
delete
from users
where id = #{id}
</delete>
</mapper>6.4. sql注入测试
package com.demo.core;
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.jupiter.api.Test;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
public class TestClass {
// 通过执行TestClass.java类中的main方法,来实现将sql语句之后的内容,封装到对应的对象之中
public static void main(String[] args) throws IOException {
//加载核心配置文件,从mybatis-config.xml中加载对应的资源
InputStream resourceAsStream = Resources.getResourceAsStream("mybatis-config.xml");
//获得sqlSession工厂对象,底层连接数据库
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
//获得sqlSession对象
SqlSession sqlSession = sqlSessionFactory.openSession();
//执行sql查询语句
// List<User> userList = sqlSession.selectList("userMapper.findAll");
// List<Object> userList = sqlSession.selectList("userMapper.findAll");
//打印结果
// System.out.println(userList);
//执行sql修改语句
// int update = sqlSession.update("userMapper.updateId");
// System.out.println(update);
//执行sql添加语句
// User user = new User(26, "hahaha", "123456");
// int insert = sqlSession.insert("userMapper.insertOneRow", user);
// 此时的user就是一个parameterType类型的对象
// System.out.println(insert);
//执行sql删除语句
// int delete = sqlSession.delete("userMapper.deleteOneRow", 26);
// System.out.println(delete);
//sql注入测试
// update users set username = 'helloworld' where id = 26
User helloworld = new User(15, "helloworld' union select 1,2,3#", "123456");
int update = sqlSession.update("userMapper.update", helloworld);
System.out.println(update);
//释放资源
sqlSession.close();
}
}<?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="userMapper">
<!--
将 select * from users 获得的结果
作为一个对象的属性封装到com.demo.core.User类型的对象中
-->
<select id="findAll" resultType="com.demo.core.User">
select *
from users
</select>
<!--// 将id为10的username字段中的内容改为Tom-->
<update id="updateId">
update users
set username = 'Tom'
where id = 10
</update>
<!-- com.demo.core.User: 传递的参数是User类型的对象 -->
<!-- insert into user values(#{id},#{username},#{password}) -->
<!-- #{} 将来会被实际值给替换 -->
<insert id="insertOneRow" parameterType="com.demo.core.User">
insert into users
values (#{id}, #{username}, #{password})
</insert>
<delete id="deleteOneRow" parameterType="java.lang.Integer">
delete
from users
where id = #{id}
</delete>
<!-- sql注入测试 -->
<update id="update">
update users
set username = #{username}
where id = #{id}
</update>
</mapper>将单引号转译
6.5. sql注入测试2
设置id类型为java.lang.Integer(整型),传入字符串类型,直接报错
将id类型改为字符串
当数据类型为String时,会自动在sql语句两端加上对应的单引号
加单引号直接被转译
6.6. 知识总结
增删改查映射配置与API：
查询数据：
List<User> userList = sqlSession.selectList("userMapper.findAll");
<select id="findAll" resultType="com.itbihuo.domain.User">
select * from User
</select>
添加数据：
sqlSession.insert("userMapper.add", user);
<insert id="add" parameterType="com.itbihuo.domain.User">
insert into user values(#{id},#{username},#{password})
</insert>
修改数据：
sqlSession.update("userMapper.update", user);
<update id="update" parameterType="com.itbihuo.domain.User">
update user set username=#{username},password=#{password} where id=#{id}
</update>
删除数据：
sqlSession.delete("userMapper.delete",3);
<delete id="delete" parameterType="java.lang.Integer">
delete from user where id=#{id}
</delete>7. MyBatis核心配置文件概述
7.1. MyBatis核心配置文件层级关系
7.2. MyBatis常用配置解析
7.2.1. environments标签
数据库环境的配置，支持多环境配置
事务管理器（transactionManager）类型有两种：
JDBC：这个配置就是直接使用了JDBC 的提交和回滚设置，它依赖于从数据源得到的连接来管理事务作用域。
MANAGED：这个配置几乎没做什么。它从来不提交或回滚一个连接，而是让容器来管理事务的整个生命周期（比如 JEE 应用服务器的上下文）。 默认情况下它会关闭连接，然而一些容器并不希望这样，因此需要将 closeConnection 属性设置为 false 来阻止它默认的关闭行为。
数据源（dataSource）类型有三种：
UNPOOLED：这个数据源的实现只是每次被请求时打开和关闭连接。
POOLED：这种数据源的实现利用“池”的概念将 JDBC 连接对象组织起来。
JNDI：这个数据源的实现是为了能在如 EJB 或应用服务器这类容器中使用，容器可以集中或在外部配置数据源，然后放置一个 JNDI 上下文的引用。
7.2.2. mapper标签
该标签的作用是加载映射的，加载方式有如下几种：
使用相对于类路径的资源引用，例如：<mapper resource="org/mybatis/builder/AuthorMapper.xml"/>
使用完全限定资源定位符(URL)，例如：<mapper url="file:///var/mappers/AuthorMapper.xml"/>
使用映射器接口实现类的完全限定类名，例如：<mapper class="org.mybatis.builder.AuthorMapper"/>
将包内的映射器接口实现全部注册为映射器，例如：<package name="org.mybatis.builder"/>
7.2.3. Properties标签
实际开发中，习惯将数据源的配置信息单独抽取成一个properties文件，该标签可以加载额外配置的properties文件
7.2.4. typeAliases标签
类型别名是为Java类型设置一个短的名字。原来的类型名称配置如下
配置typeAliases，为com.itbihuo.domain.User定义别名为user
7.3. 导入properties文件并读取配置内容
修改一下内容，做映射
改成这种
package com.demo.core;
public class User {
private Integer id;
private String username;
private String password;
public User() {
}
public User(Integer id, String username, String password) {
this.id = id;
this.username = username;
this.password = password;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", password='" + password + '\'' +
'}';
}
public Integer getId() {
return id;
}
public void setId(Integer 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;
}
}<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"https://mybatis.org/dtd/mybatis-3-config.dtd">
<!--配置-->
<configuration>
<!--属性相关的资源-->
<!--引入mybatis配置文件-->
<properties resource="jdbc.properties"/>
<!--配置环境变量-->
<environments default="development">
<environment id="development">
<!--使用JDBC作为驱动内核-->
<transactionManager type="JDBC"/>
<!--配置事务的类型-->
<dataSource type="POOLED">
<!--四个属性,驱动,链接,用户名,密码-->
<property name="driver" value="${jdbc.driver}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</dataSource>
</environment>
</environments>
<!--映射相关的资源-->
<mappers>
<mapper resource="./UserMapper.xml"/>
</mappers>
</configuration>jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://192.168.100.141:3306/security
jdbc.username=root
jdbc.password=redhat<?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="userMapper">
<!--
将 select * from users 获得的结果
作为一个对象的属性封装到com.demo.core.User类型的对象中
-->
<select id="findAll" resultType="com.demo.core.User">
select *
from users
</select>
<!--// 将id为10的username字段中的内容改为Tom-->
<update id="updateId">
update users
set username = 'Tom'
where id = 10
</update>
<!-- com.demo.core.User: 传递的参数是User类型的对象 -->
<!-- insert into user values(#{id},#{username},#{password}) -->
<!-- #{} 将来会被实际值给替换 -->
<insert id="insertOneRow" parameterType="com.demo.core.User">
insert into users
values (#{id}, #{username}, #{password})
</insert>
<delete id="deleteOneRow" parameterType="String">
delete
from users
where id = #{id}
</delete>
<!-- sql注入测试 -->
<update id="update">
update users
set username = #{username}
where id = #{id}
</update>
</mapper>package com.demo.core;
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.jupiter.api.Test;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
public class TestClass {
// 通过执行TestClass.java类中的main方法,来实现将sql语句之后的内容,封装到对应的对象之中
public static void main(String[] args) throws IOException {
//加载核心配置文件,从mybatis-config.xml中加载对应的资源
InputStream resourceAsStream = Resources.getResourceAsStream("mybatis-config.xml");
//获得sqlSession工厂对象,底层连接数据库
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
//获得sqlSession对象
SqlSession sqlSession = sqlSessionFactory.openSession();
//执行sql查询语句
// List<User> userList = sqlSession.selectList("userMapper.findAll");
// List<Object> userList = sqlSession.selectList("userMapper.findAll");
//打印结果
// System.out.println(userList);
//执行sql修改语句
// int update = sqlSession.update("userMapper.updateId");
// System.out.println(update);
//执行sql添加语句
// User user = new User(26, "hahaha", "123456");
// int insert = sqlSession.insert("userMapper.insertOneRow", user);
// 此时的user就是一个parameterType类型的对象
// System.out.println(insert);
//执行sql删除语句
int delete = sqlSession.delete("userMapper.deleteOneRow", "26");
System.out.println(delete);
//sql注入测试
// update users set username = 'helloworld' where id = 26
// User helloworld = new User(15, "helloworld' union select 1,2,3#", "123456");
// int update = sqlSession.update("userMapper.update", helloworld);
// System.out.println(update);
//释放资源
sqlSession.close();
}
}7.4. 设置typeAliases标签resources下的mybatis-config.xml
注释标签测试
默认别名如下
| 别名 | 数据类型 |
| string | String |
| long | Long |
| int | Integer |
| double | Double |
| boolean | Boolean |
| … … | … … |
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"https://mybatis.org/dtd/mybatis-3-config.dtd">
<!--配置-->
<configuration>
<!--属性相关的资源-->
<!--引入mybatis配置文件-->
<properties resource="jdbc.properties"/>
<!--别名相关的资源-->
<!--设置typeAliases标签-->
<typeAliases>
<typeAlias type="com.demo.core.User" alias="user"/>
</typeAliases>
<!--配置环境变量-->
<environments default="development">
<environment id="development">
<!--使用JDBC作为驱动内核-->
<transactionManager type="JDBC"/>
<!--配置事务的类型-->
<dataSource type="POOLED">
<!--四个属性,驱动,链接,用户名,密码-->
<property name="driver" value="${jdbc.driver}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</dataSource>
</environment>
</environments>
<!--映射相关的资源-->
<mappers>
<mapper resource="./UserMapper.xml"/>
</mappers>
</configuration>7.5. 知识小结
核心配置文件常用配置：
properties标签：该标签可以加载外部的properties文件typeAliases标签：设置类型别名mappers标签：加载映射配置
8. MyBatis相应API
8.1. SqlSession工厂构建器SqlSessionFactoryBuilder
常用API：SqlSessionFactory build(InputStream inputStream)
通过加载mybatis的核心文件的输入流的形式构建一个SqlSessionFactory对象
String resource = "org/mybatis/builder/mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
SqlSessionFactory factory = builder.build(inputStream);其中， Resources 工具类，这个类在 org.apache.ibatis.io 包中。Resources 类帮助你从类路径下、文件系统或一个 web URL 中加载资源文件。
8.2. SqlSession工厂对象SqlSessionFactory
SqlSessionFactory有多个个方法创建 SqlSession 实例。常用的有如下两个：
8.3. SqlSession会话对象
SqlSession实例在 MyBatis 中是非常强大的一个类。在这里你会看到所有执行语句、提交或回滚事务和获取映射器实例的方法。
执行语句的方法主要有：
<T> T selectOne(String statement, Object parameter)
<E> List<E> selectList(String statement, Object parameter)
int insert(String statement, Object parameter)
int update(String statement, Object parameter)
int delete(String statement, Object parameter)操作事务的方法主要有：
//提交到数据库
commit()
//回滚
rollback()8.3.1. 事务的概念
9. MyBatis的Dao层实现方式
9.1. 传统开发方式
- 编写
UserDao接口
package com.demo.core.interfaces;
import com.demo.core.User;
import java.io.IOException;
import java.util.List;
public interface UserDao {
List<User> findAll() throws IOException;
}- 编写
UserDaoImpl实现
package com.demo.core.impl;
import com.demo.core.User;
import com.demo.core.interfaces.UserDao;
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;
import java.util.List;
public class UserDaoImpl implements UserDao {
@Override
public List<User> findAll() throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
List<User> userList = sqlSession.selectList("userMapper.findAll");
sqlSession.close();
return userList;
}
}- 测试传统方式
package com.demo.core;
import com.demo.core.impl.UserDaoImpl;
import com.demo.core.interfaces.UserDao;
import java.io.IOException;
import java.util.List;
public class TestClass {
public static void main(String[] args) throws IOException {
UserDao userDao = new UserDaoImpl();
List<User> all = userDao.findAll();
System.out.println(all);
}
}
9.2. 代理开发方式
9.2.1. 代理开发方式介绍
采用 Mybatis 的代理开发方式实现 DAO 层的开发，这种方式是我们后面进入企业的主流。Mapper接口开发方法只需要程序员编写Mapper接口（相当于Dao 接口），由Mybatis框架根据接口定义创建接口的动态代理对象，代理对象的方法体同上边Dao接口实现类方法。
Mapper接口开发需要遵循以下规范：
Mapper.xml文件中的namespace与mapper接口的全限定名相同Mapper接口方法名和Mapper.xml中定义的每个statement的id相同Mapper接口方法的输入参数类型和mapper.xml中定义的每个sql的parameterType的类型相同Mapper接口方法的输出参数类型和mapper.xml中定义的每个sql的resultType的类型相同
9.2.2. 编写UserMapper接口
9.2.3. 测试代理方式
- 声明一个
UserDao接口,该接口中定义了一个方法,但并没有去实现
- 在
mapper将namespace指向定义好的接口
package com.demo.core;
public class User {
private Integer id;
private String username;
private String password;
public User() {
}
public User(Integer id, String username, String password) {
this.id = id;
this.username = username;
this.password = password;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", password='" + password + '\'' +
'}';
}
public Integer getId() {
return id;
}
public void setId(Integer 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;
}
}<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"https://mybatis.org/dtd/mybatis-3-config.dtd">
<!--配置-->
<configuration>
<!--属性相关的资源-->
<!--引入mybatis配置文件-->
<properties resource="jdbc.properties"/>
<!--别名相关的资源-->
<!--设置typeAliases标签-->
<typeAliases>
<typeAlias type="com.demo.core.User" alias="user"/>
</typeAliases>
<!--配置环境变量-->
<environments default="development">
<environment id="development">
<!--使用JDBC作为驱动内核-->
<transactionManager type="JDBC"/>
<!--配置事务的类型-->
<dataSource type="POOLED">
<!--四个属性,驱动,链接,用户名,密码-->
<property name="driver" value="${jdbc.driver}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</dataSource>
</environment>
</environments>
<!--映射相关的资源-->
<mappers>
<mapper resource="./UserMapper.xml"/>
</mappers>
</configuration>package com.demo.core;
import com.demo.core.interfaces.UserDao;
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 TestClass {
public static void main(String[] args) throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
// 获得MyBatis框架生成的UserMapper接口的实现类
// 通过利用getMapper返回来的代理类型的对象会自动去执行想要执行的操作
UserDao userMapper = sqlSession.getMapper(UserDao.class);
System.out.println(userMapper.findById(1));
System.out.println(userMapper.deleteById(26));
sqlSession.close();
}
}<?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.demo.core.interfaces.UserDao">
<!--
将 select * from users 获得的结果
作为一个对象的属性封装到com.demo.core.User类型的对象中
-->
<select id="findById" parameterType="int" resultType="com.demo.core.User">
select *
from users
where id = #{id}
</select>
<delete id="deleteById" parameterType="int">
delete
from users
where id = #{id}
</delete>
</mapper>package com.demo.core.interfaces;
import com.demo.core.User;
import java.io.IOException;
import java.util.List;
public interface UserDao {
List<User> findAll() throws IOException;
User findById(int id);
Integer deleteById(int id);
}jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://192.168.100.141:3306/security
jdbc.username=root
jdbc.password=redhat
9.3. 知识小结
MyBatis的Dao层实现的两种方式：
- 手动对Dao进行实现：传统开发方式
- 代理方式对
Dao进行实现：UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
10. MyBatis映射文件深入
10.1. 动态sql语句
10.1.1. 动态sql语句概述
Mybatis的映射文件中，前面我们的 SQL 都是比较简单的，有些时候业务逻辑复杂时，我们的 SQL是动态变化的，此时在前面的学习中我们的 SQL 就不能满足要求了。
参考的官方文档，描述如下：
10.1.2. 动态 SQL 之<if>
我们根据实体类的不同取值，使用不同的SQL语句来进行查询。比如在id如果不为空时可以根据id查询，如果username不同空时还要加入用户名作为条件。这种情况在我们的多条件组合查询中经常会碰到。
package com.demo.core;
import com.demo.core.interfaces.UserDao;
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 TestClass {
public static void main(String[] args) throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
// 获得MyBatis框架生成的UserMapper接口的实现类
// 通过利用getMapper返回来的代理类型的对象会自动去执行想要执行的操作
UserDao userMapper = sqlSession.getMapper(UserDao.class);
// System.out.println(userMapper.findById(1));
// System.out.println(userMapper.deleteById(26));
User user = new User();
user.setId(10);
user.setUsername("hahaha");
System.out.println(userMapper.findByCondition(user));
sqlSession.close();
}
}package com.demo.core;
public class User {
private Integer id;
private String username;
private String password;
public User() {
}
public User(Integer id, String username, String password) {
this.id = id;
this.username = username;
this.password = password;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", password='" + password + '\'' +
'}';
}
public Integer getId() {
return id;
}
public void setId(Integer 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;
}
}<?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.demo.core.interfaces.UserDao">
<!--
将 select * from users 获得的结果
作为一个对象的属性封装到com.demo.core.User类型的对象中
-->
<select id="findById" parameterType="int" resultType="com.demo.core.User">
select *
from users
where id = #{id}
</select>
<delete id="deleteById" parameterType="int">
delete
from users
where id = #{id}
</delete>
<!--多个条件都可以-->
<select id="findByCondition" parameterType="user" resultType="com.demo.core.User">
select *
from users
<where>
<if test="id!=null">
and id = #{id}
</if>
<if test="username!=null">
and username = #{username}
</if>
</where>
</select>
</mapper><?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"https://mybatis.org/dtd/mybatis-3-config.dtd">
<!--配置-->
<configuration>
<!--属性相关的资源-->
<!--引入mybatis配置文件-->
<properties resource="jdbc.properties"/>
<!--别名相关的资源-->
<!--设置typeAliases标签-->
<typeAliases>
<typeAlias type="com.demo.core.User" alias="user"/>
</typeAliases>
<!--配置环境变量-->
<environments default="development">
<environment id="development">
<!--使用JDBC作为驱动内核-->
<transactionManager type="JDBC"/>
<!--配置事务的类型-->
<dataSource type="POOLED">
<!--四个属性,驱动,链接,用户名,密码-->
<property name="driver" value="${jdbc.driver}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</dataSource>
</environment>
</environments>
<!--映射相关的资源-->
<mappers>
<mapper resource="./UserMapper.xml"/>
</mappers>
</configuration>package com.demo.core.interfaces;
import com.demo.core.User;
import java.io.IOException;
import java.util.List;
public interface UserDao {
List<User> findAll() throws IOException;
User findById(int id);
Integer deleteById(int id);
User findByCondition(User user);
}jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://192.168.11.128:3306/security
jdbc.username=root
jdbc.password=redhat
当查询条件只有id存在时，控制台打印的sql语句如下：
当查询条件id和username都存在时，控制台打印的sql语句如下：
10.1.3. 动态SQL之<foreach>
循环执行sql的拼接操作，例如：SELECT * FROM USER WHERE id IN (1,2,5)
foreach标签的属性含义如下：
<foreach>标签用于遍历集合，它的属性：
collection：代表要遍历的集合元素，注意编写时不要写#{}
open：代表语句的开始部分
close：代表结束部分
item：代表遍历集合的每个元素，生成的变量名
sperator：代表分隔符
package com.demo.core;
import com.demo.core.interfaces.UserDao;
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;
import java.util.List;
public class TestClass {
public static void main(String[] args) throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
// 获得MyBatis框架生成的UserMapper接口的实现类
// 通过利用getMapper返回来的代理类型的对象会自动去执行想要执行的操作
UserDao userMapper = sqlSession.getMapper(UserDao.class);
// System.out.println(userMapper.findById(1));
// System.out.println(userMapper.deleteById(26));
// User user = new User();
// user.setId(10);
// user.setUsername("hahaha");
// System.out.println(userMapper.findByCondition(user));
int[] ids = new int[]{1, 2, 3, 4, 5};
List<User> userList = userMapper.findByIds(ids);
System.out.println(userList);
sqlSession.close();
}
}<?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.demo.core.interfaces.UserDao">
<!--
将 select * from users 获得的结果
作为一个对象的属性封装到com.demo.core.User类型的对象中
-->
<select id="findById" parameterType="int" resultType="com.demo.core.User">
select *
from users
where id = #{id}
</select>
<delete id="deleteById" parameterType="int">
delete
from users
where id = #{id}
</delete>
<select id="findByCondition" parameterType="user" resultType="com.demo.core.User">
select *
from users
<where>
<if test="id!=null">
and id = #{id}
</if>
<if test="username!=null">
and username = #{username}
</if>
</where>
</select>
<!--array 数组类型-->
<!--select * from users where id in ()-->
<select id="findByIds" parameterType="list" resultType="com.demo.core.User">
select *
from users
<where>
<foreach collection="array" open="id in (" close=")" item="id" separator=",">
#{id}
</foreach>
</where>
</select>
</mapper>package com.demo.core.interfaces;
import com.demo.core.User;
import java.io.IOException;
import java.util.List;
public interface UserDao {
List<User> findAll() throws IOException;
User findById(int id);
Integer deleteById(int id);
User findByCondition(User user);
List<User> findByIds(int[] ids);
}package com.demo.core;
public class User {
private Integer id;
private String username;
private String password;
public User() {
}
public User(Integer id, String username, String password) {
this.id = id;
this.username = username;
this.password = password;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", password='" + password + '\'' +
'}';
}
public Integer getId() {
return id;
}
public void setId(Integer 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;
}
}<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"https://mybatis.org/dtd/mybatis-3-config.dtd">
<!--配置-->
<configuration>
<!--属性相关的资源-->
<!--引入mybatis配置文件-->
<properties resource="jdbc.properties"/>
<!--别名相关的资源-->
<!--设置typeAliases标签-->
<typeAliases>
<typeAlias type="com.demo.core.User" alias="user"/>
</typeAliases>
<!--配置环境变量-->
<environments default="development">
<environment id="development">
<!--使用JDBC作为驱动内核-->
<transactionManager type="JDBC"/>
<!--配置事务的类型-->
<dataSource type="POOLED">
<!--四个属性,驱动,链接,用户名,密码-->
<property name="driver" value="${jdbc.driver}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</dataSource>
</environment>
</environments>
<!--映射相关的资源-->
<mappers>
<mapper resource="./UserMapper.xml"/>
</mappers>
</configuration>jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://192.168.11.128:3306/security
jdbc.username=root
jdbc.password=redhat
10.1.4. 动态SQL之<choose>
choose、when、otherwise
有时候，我们不想使用所有的条件，而只是想从多个条件中选择一个使用。针对这种情况，MyBatis 提供了 choose 元素，它有点像 Java 中的 switch 语句。
当使用choose的时候，即便此时拥有多个条件满足，但仍然只会使用一个条件
package com.demo.core;
import com.demo.core.interfaces.UserDao;
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;
import java.util.List;
public class TestClass {
public static void main(String[] args) throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
// 获得MyBatis框架生成的UserMapper接口的实现类
// 通过利用getMapper返回来的代理类型的对象会自动去执行想要执行的操作
UserDao userMapper = sqlSession.getMapper(UserDao.class);
// System.out.println(userMapper.findById(1));
// System.out.println(userMapper.deleteById(26));
// User user = new User();
// user.setId(10);
// user.setUsername("hahaha");
// System.out.println(userMapper.findByCondition(user));
// int[] ids = new int[]{1, 2, 3, 4, 5};
// List<User> userList = userMapper.findByIds(ids);
// System.out.println(userList);
User user = new User();
user.setId(10);
user.setUsername("admin");
List<User> byChoose = userMapper.findByChoose(user);
System.out.println(byChoose);
sqlSession.close();
}
}<?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.demo.core.interfaces.UserDao">
<!--
将 select * from users 获得的结果
作为一个对象的属性封装到com.demo.core.User类型的对象中
-->
<sql id="commonQuery">
select *
from users
</sql>
<select id="findById" parameterType="int" resultType="com.demo.core.User">
<include refid="commonQuery"></include>
where id = #{id}
</select>
<delete id="deleteById" parameterType="int">
delete
from users
where id = #{id}
</delete>
<!--多个条件都可以-->
<select id="findByCondition" parameterType="user" resultType="com.demo.core.User">
select *
from users
<where>
<if test="id!=null">
and id = #{id}
</if>
<if test="username!=null">
and username = #{username}
</if>
</where>
</select>
<!--array 数组类型-->
<!--select * from users where id in ()-->
<select id="findByIds" parameterType="list" resultType="com.demo.core.User">
select *
from users
<where>
<foreach collection="array" open="id in (" close=")" item="id" separator=",">
#{id}
</foreach>
</where>
</select>
<!--当使用choose的时候，即便此时拥有多个条件满足，但仍然只会使用一个条件-->
<select id="findByChoose" parameterType="User" resultType="user">
<include refid="commonQuery"></include>
<where>
<choose>
<when test="username != null">
AND username like #{username}
</when>
<when test="id != null">
AND id like #{id}
</when>
<otherwise>
AND 1 = 1
</otherwise>
</choose>
</where>
</select>
</mapper>package com.demo.core.interfaces;
import com.demo.core.User;
import java.io.IOException;
import java.util.List;
public interface UserDao {
List<User> findAll() throws IOException;
User findById(int id);
Integer deleteById(int id);
User findByCondition(User user);
List<User> findByIds(int[] ids);
List<User> findByChoose(User user);
}
依次从上往下选择
10.1.5. 动态SQL之<script>
特点是无需再UserMapper.xml中配置对应的mapper
package com.demo.core.interfaces;
import com.demo.core.User;
import org.apache.ibatis.annotations.Update;
import java.io.IOException;
import java.util.List;
public interface UserDao {
List<User> findAll() throws IOException;
User findById(int id);
Integer deleteById(int id);
User findByCondition(User user);
List<User> findByIds(int[] ids);
List<User> findByChoose(User user);
@Update({"<script>",
"update users",
" <set>",
" <if test='username != null'>username=#{username},</if>",
" <if test='password != null'>password=#{password},</if>",
" </set>",
"where id=#{id}",
"</script>"})
// 传一个参数,User类型的对象user
void updateUsersBuId(User user);
}package com.demo.core;
import com.demo.core.interfaces.UserDao;
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;
import java.util.List;
public class TestClass {
public static void main(String[] args) throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
// 获得MyBatis框架生成的UserMapper接口的实现类
// 通过利用getMapper返回来的代理类型的对象会自动去执行想要执行的操作
UserDao userMapper = sqlSession.getMapper(UserDao.class);
// System.out.println(userMapper.findById(1));
// System.out.println(userMapper.deleteById(26));
// User user = new User();
// user.setId(10);
// user.setUsername("hahaha");
// System.out.println(userMapper.findByCondition(user));
// int[] ids = new int[]{1, 2, 3, 4, 5};
// List<User> userList = userMapper.findByIds(ids);
// System.out.println(userList);
// User user = new User();
// user.setId(10);
// user.setUsername("admin");
// List<User> byChoose = userMapper.findByChoose(user);
// System.out.println(byChoose);
User user = new User();
user.setId(15);
user.setUsername("hehe");
userMapper.updateUsersBuId(user);
sqlSession.close();
}
}<?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.demo.core.interfaces.UserDao">
</mapper>package com.demo.core;
public class User {
private Integer id;
private String username;
private String password;
public User() {
}
public User(Integer id, String username, String password) {
this.id = id;
this.username = username;
this.password = password;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", password='" + password + '\'' +
'}';
}
public Integer getId() {
return id;
}
public void setId(Integer 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;
}
}<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"https://mybatis.org/dtd/mybatis-3-config.dtd">
<!--配置-->
<configuration>
<!--属性相关的资源-->
<!--引入mybatis配置文件-->
<properties resource="jdbc.properties"/>
<!--别名相关的资源-->
<!--设置typeAliases标签-->
<typeAliases>
<typeAlias type="com.demo.core.User" alias="user"/>
</typeAliases>
<!--配置环境变量-->
<environments default="development">
<environment id="development">
<!--使用JDBC作为驱动内核-->
<transactionManager type="JDBC"/>
<!--配置事务的类型-->
<dataSource type="POOLED">
<!--四个属性,驱动,链接,用户名,密码-->
<property name="driver" value="${jdbc.driver}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</dataSource>
</environment>
</environments>
<!--映射相关的资源-->
<mappers>
<mapper resource="./UserMapper.xml"/>
</mappers>
</configuration>jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://192.168.11.128:3306/security
jdbc.username=root
jdbc.password=redhat
只修改一个参数
同时修改两个参数
10.2. 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 namespace="com.demo.core.interfaces.UserDao">
<!--
将 select * from users 获得的结果
作为一个对象的属性封装到com.demo.core.User类型的对象中
-->
<sql id="commonQuery">
select *
from users
</sql>
<select id="findById" parameterType="int" resultType="user">
<include refid="commonQuery"></include>
where id = #{id}
</select>
<!-- <select id="findById" parameterType="int" resultType="com.demo.core.User">-->
<!-- select *-->
<!-- from users-->
<!-- where id = #{id}-->
<!-- </select>-->
<delete id="deleteById" parameterType="int">
delete
from users
where id = #{id}
</delete>
<select id="findByCondition" parameterType="user" resultType="com.demo.core.User">
<include refid="commonQuery"></include>
<where>
<if test="id!=null">
and id = #{id}
</if>
<if test="username!=null">
and username = #{username}
</if>
</where>
</select>
<!--array 数组类型-->
<!--select * from users where id in ()-->
<select id="findByIds" parameterType="list" resultType="com.demo.core.User">
<include refid="commonQuery"></include>
<where>
<foreach collection="array" open="id in (" close=")" item="id" separator=",">
#{id}
</foreach>
</where>
</select>
</mapper>
10.3. 知识小结
MyBatis映射文件配置
<select>：查询
<insert>：插入
<update>：修改
<delete>：删除
<where>：where条件
<if>：if判断
<foreach>：循环
<sql>：sql片段抽取
11. MyBatis核心配置文件深入
11.1. typeHandlers标签
无论是 MyBatis 在预处理语句（PreparedStatement）中设置一个参数时，还是从结果集中取出一个值时， 都会用类型处理器将获取的值以合适的方式转换成 Java 类型。下表描述了一些默认的类型处理器（截取部分）。
你可以重写类型处理器或创建你自己的类型处理器来处理不支持的或非标准的类型。具体做法为：实现org.apache.ibatis.type.TypeHandler接口， 或继承一个很便利的类org.apache.ibatis.type.BaseTypeHandler， 然后可以选择性地将它映射到一个JDBC类型。例如需求：一个Java中的Date数据类型，我想将之存到数据库的时候存成一个1970年至今的毫秒数，取出来时转换成java的Date，即java的Date与数据库的varchar毫秒值之间转换。
开发步骤：
- 定义转换类继承类
BaseTypeHandler<T> - 覆盖4个未实现的方法，其中
setNonNullParameter为java程序设置数据到数据库的回调方法，getNullableResult为查询时mysql的字符串类型转换成java的Type类型的方法 - 在
MyBatis核心配置文件中进行注册 - 测试转换是否正确
- 定义转换类继承类
BaseTypeHandler<T>，覆盖4个未实现的方法
package com.demo.core.handles;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
public class MyDateTypeHandler extends BaseTypeHandler<Date> {
@Override
public void setNonNullParameter(PreparedStatement preparedStatement, int i, Date date, JdbcType type) throws SQLException {
preparedStatement.setString(i, date.getTime() + "");
}
@Override
public Date getNullableResult(ResultSet resultSet, String s) throws SQLException {
return new Date(resultSet.getLong(s));
}
@Override
public Date getNullableResult(ResultSet resultSet, int i) throws SQLException {
return new Date(resultSet.getLong(i));
}
@Override
public Date getNullableResult(CallableStatement callableStatement, int i) throws SQLException {
return callableStatement.getDate(i);
}
}涉及到空的数据类型，自动调用对应方法
- 在
MyBatis核心配置文件中进行注册
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"https://mybatis.org/dtd/mybatis-3-config.dtd">
<!--配置-->
<configuration>
<!--属性相关的资源-->
<!--引入mybatis配置文件-->
<properties resource="jdbc.properties"/>
<!--别名相关的资源-->
<!--设置typeAliases标签-->
<typeAliases>
<typeAlias type="com.demo.core.User" alias="user"/>
</typeAliases>
<!--注册类型自定义转换器-->
<typeHandlers>
<typeHandler handler="com.demo.core.handles.MyDateTypeHandler"/>
</typeHandlers>
<!--配置环境变量-->
<environments default="development">
<environment id="development">
<!--使用JDBC作为驱动内核-->
<transactionManager type="JDBC"/>
<!--配置事务的类型-->
<dataSource type="POOLED">
<!--四个属性,驱动,链接,用户名,密码-->
<property name="driver" value="${jdbc.driver}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</dataSource>
</environment>
</environments>
<!--映射相关的资源-->
<mappers>
<mapper resource="./UserMapper.xml"/>
</mappers>
</configuration>
- 在
UserMapper.xml中添加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 namespace="com.demo.core.interfaces.UserDao">
<!--
将 select * from users 获得的结果
作为一个对象的属性封装到com.demo.core.User类型的对象中
-->
<sql id="commonQuery">
select *
from users
</sql>
<select id="findById" parameterType="int" resultType="com.demo.core.User">
<include refid="commonQuery"></include>
where id = #{id}
</select>
<insert id="insertOneRow" parameterType="user">
insert into users (username, password, date)
values (#{username}, #{password}, #{date})
</insert>
<delete id="deleteById" parameterType="int">
delete
from users
where id = #{id}
</delete>
<!--多个条件都可以-->
<select id="findByCondition" parameterType="user" resultType="com.demo.core.User">
select *
from users
<where>
<if test="id!=null">
and id = #{id}
</if>
<if test="username!=null">
and username = #{username}
</if>
</where>
</select>
<!--array 数组类型-->
<!--select * from users where id in ()-->
<select id="findByIds" parameterType="list" resultType="com.demo.core.User">
select *
from users
<where>
<foreach collection="array" open="id in (" close=")" item="id" separator=",">
#{id}
</foreach>
</where>
</select>
<!--当使用choose的时候，即便此时拥有多个条件满足，但仍然只会使用一个条件-->
<select id="findByChoose" parameterType="User" resultType="user">
<include refid="commonQuery"></include>
<where>
<choose>
<when test="username != null">
AND username like #{username}
</when>
<when test="id != null">
AND id like #{id}
</when>
<otherwise>
AND 1 = 1
</otherwise>
</choose>
</where>
</select>
</mapper>
- 测试转换是否正确
package com.demo.core;
import com.demo.core.interfaces.UserDao;
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;
import java.util.Date;
import java.util.List;
public class TestClass {
public static void main(String[] args) throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
// 获得MyBatis框架生成的UserMapper接口的实现类
// 通过利用getMapper返回来的代理类型的对象会自动去执行想要执行的操作
UserDao userMapper = sqlSession.getMapper(UserDao.class);
// System.out.println(userMapper.findById(1));
// System.out.println(userMapper.deleteById(26));
// User user = new User();
// user.setId(10);
// user.setUsername("hahaha");
// System.out.println(userMapper.findByCondition(user));
// int[] ids = new int[]{1, 2, 3, 4, 5};
// List<User> userList = userMapper.findByIds(ids);
// System.out.println(userList);
// User user = new User();
// user.setId(10);
// user.setUsername("admin");
// List<User> byChoose = userMapper.findByChoose(user);
// System.out.println(byChoose);
// User user = new User();
// user.setId(14);
// user.setUsername("hehe");
// user.setPassword("haha");
// userMapper.updateUsersBuId(user);
User user = new User();
user.setUsername("hehe");
user.setPassword("haha");
user.setDate(new Date());
userMapper.insertOneRow(user);
sqlSession.close();
}
}package com.demo.core;
import java.util.Date;
public class User {
private Integer id;
private String username;
private String password;
private Date date;
public User(Integer id, String username, String password, Date date) {
this.id = id;
this.username = username;
this.password = password;
this.date = date;
}
public User(Integer id, String username, String password) {
this.id = id;
this.username = username;
this.password = password;
}
public User() {
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", password='" + password + '\'' +
", date=" + date +
'}';
}
public Date getDate() {
return date;
}
public void setDate(Date date) {
this.date = date;
}
public Integer getId() {
return id;
}
public void setId(Integer 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;
}
}package com.demo.core.interfaces;
import com.demo.core.User;
import org.apache.ibatis.annotations.Update;
import java.io.IOException;
import java.util.List;
public interface UserDao {
List<User> findAll() throws IOException;
User findById(int id);
Integer deleteById(int id);
User findByCondition(User user);
List<User> findByIds(int[] ids);
List<User> findByChoose(User user);
@Update({"<script>",
"update users",
" <set>",
" <if test='username != null'>username=#{username},</if>",
" <if test='password != null'>password=#{password},</if>",
" </set>",
"where id=#{id}",
"</script>"})
// 传一个参数,User类型的对象user
void updateUsersBuId(User user);
void insertOneRow(User user);
}新建一个时间字段
11.2. plugins标签
MyBatis可以使用第三方的插件来对功能进行扩展，分页助手PageHelper是将分页的复杂操作进行封装，使用简单的方式即可获得分页的相关数据
开发步骤：
- 导入通用
PageHelper的坐标 - 在
mybatis核心配置文件中配置PageHelper插件 - 测试分页数据获取
<?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>org.example</groupId>
<artifactId>MyBatis_001</artifactId>
<version>1.0-SNAPSHOT</version>
<properties>
<maven.compiler.source>8</maven.compiler.source>
<maven.compiler.target>8</maven.compiler.target>
</properties>
<!-- dependencies 依赖包的意思-->
<dependencies>
<!--mybatis坐标-->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.4</version>
</dependency>
<!--mysql驱动坐标-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.6</version>
<scope>runtime</scope>
</dependency>
<!--单元测试坐标-->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>
test
</scope>
</dependency>
<!--日志坐标-->
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.12</version>
</dependency>
<dependency>
<groupId>org.junit.jupiter</groupId>
<artifactId>junit-jupiter</artifactId>
<version>RELEASE</version>
<scope>compile</scope>
</dependency>
<!-- 分页助手 -->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>3.7.5</version>
</dependency>
<dependency>
<groupId>com.github.jsqlparser</groupId>
<artifactId>jsqlparser</artifactId>
<version>0.9.1</version>
</dependency>
</dependencies>
</project>
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"https://mybatis.org/dtd/mybatis-3-config.dtd">
<!--配置-->
<configuration>
<!--属性相关的资源-->
<!--引入mybatis配置文件-->
<properties resource="jdbc.properties"/>
<!--别名相关的资源-->
<!--设置typeAliases标签-->
<typeAliases>
<typeAlias type="com.demo.core.User" alias="user"/>
</typeAliases>
<!--注册类型自定义转换器-->
<typeHandlers>
<typeHandler handler="com.demo.core.handles.MyDateTypeHandler"/>
</typeHandlers>
<plugins>
<!-- 注意：分页助手的插件 配置在通用馆mapper之前 -->
<plugin interceptor="com.github.pagehelper.PageHelper">
<!-- 指定方言 -->
<property name="dialect" value="mysql"/>
</plugin>
</plugins>
<!--配置环境变量-->
<environments default="development">
<environment id="development">
<!--使用JDBC作为驱动内核-->
<transactionManager type="JDBC"/>
<!--配置事务的类型-->
<dataSource type="POOLED">
<!--四个属性,驱动,链接,用户名,密码-->
<property name="driver" value="${jdbc.driver}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</dataSource>
</environment>
</environments>
<!--映射相关的资源-->
<mappers>
<mapper resource="./UserMapper.xml"/>
</mappers>
</configuration>
package com.demo.core;
import com.demo.core.interfaces.UserDao;
import com.github.pagehelper.PageHelper;
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;
import java.util.Date;
import java.util.List;
public class TestClass {
public static void main(String[] args) throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
// 获得MyBatis框架生成的UserMapper接口的实现类
// 通过利用getMapper返回来的代理类型的对象会自动去执行想要执行的操作
UserDao userMapper = sqlSession.getMapper(UserDao.class);
// limit (2-1)*5, 5 ===> limit 5, 5
PageHelper.startPage(2, 5);
List<User> users = userMapper.findAll();
for (User user :
users) {
System.out.println(user);
}
// Iterator iterator = page.iterator();
// while (iterator.hasNext()) {
// Object next = iterator.next();
// System.out.println(next);
// }
}
}
package com.demo.core;
import com.demo.core.interfaces.UserDao;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
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;
import java.util.Date;
import java.util.List;
public class TestClass {
public static void main(String[] args) throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
// 获得MyBatis框架生成的UserMapper接口的实现类
// 通过利用getMapper返回来的代理类型的对象会自动去执行想要执行的操作
UserDao userMapper = sqlSession.getMapper(UserDao.class);
// limit (2-1)*5, 5 ===> limit 5, 5
PageHelper.startPage(2, 5);
List<User> users = userMapper.findAll();
for (User user :
users) {
System.out.println(user);
}
// Iterator iterator = page.iterator();
// while (iterator.hasNext()) {
// Object next = iterator.next();
// System.out.println(next);
// }
//其他分页的数据
PageInfo<User> pageInfo = new PageInfo<User>(users);
System.out.println("总条数：" + pageInfo.getTotal());
System.out.println("总页数：" + pageInfo.getPages());
System.out.println("当前页：" + pageInfo.getPageNum());
System.out.println("每页显示长度：" + pageInfo.getPageSize());
System.out.println("是否第一页：" + pageInfo.isIsFirstPage());
System.out.println("是否最后一页：" + pageInfo.isIsLastPage());
}
}相关代码
package com.demo.core;
import java.util.Date;
public class User {
private Integer id;
private String username;
private String password;
private Date date;
public User(Integer id, String username, String password, Date date) {
this.id = id;
this.username = username;
this.password = password;
this.date = date;
}
public User(Integer id, String username, String password) {
this.id = id;
this.username = username;
this.password = password;
}
public User() {
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", password='" + password + '\'' +
", date=" + date +
'}';
}
public Date getDate() {
return date;
}
public void setDate(Date date) {
this.date = date;
}
public Integer getId() {
return id;
}
public void setId(Integer 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;
}
}<?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.demo.core.interfaces.UserDao">
<!--
将 select * from users 获得的结果
作为一个对象的属性封装到com.demo.core.User类型的对象中
-->
<sql id="commonQuery">
select *
from users
</sql>
<select id="findByUserIds" parameterType="list" resultType="user">
<include refid="commonQuery"></include>
<where>
<foreach collection="array" open="id in (" close=")" item="id" separator=",">
#{id}
</foreach>
</where>
</select>
<select id="findByCondition" parameterType="user" resultType="user">
<include refid="commonQuery"></include>
<where>
<if test="id != null">
and id = #{id}
</if>
<if test="username != null">
and username = #{username}
</if>
</where>
</select>
<select id="findByOneCondition" parameterType="user" resultType="user">
<include refid="commonQuery"></include>
<where>
<choose>
<when test="id != null">
and id = #{id}
</when>
<when test="username != null">
and username = #{username}
</when>
</choose>
</where>
</select>
<select id="updateBuId" parameterType="user">
update users
<set>
<if test="username != null">
username = #{username},
</if>
<if test="password != null">
password = #{password},
</if>
</set>
where id = #{id}
</select>
<select id="findAll" resultType="user">
<include refid="commonQuery"></include>
</select>
</mapper>package com.demo.core.interfaces;
import com.demo.core.User;
import java.util.List;
public interface UserDao {
List<User> findByUserIds(int[] ids);
List<User> findByCondition(User user);
List<User> findByOneCondition(User user);
void updateBuId(User user);
List<User> findAll();
}jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://192.168.100.141:3306/security
jdbc.username=root
jdbc.password=redhat11.3. 知识小结
MyBatis核心配置文件常用标签：
properties标签：该标签可以加载外部的properties文件typeAliases标签：设置类型别名environments标签：数据源环境配置标签typeHandlers标签：配置自定义类型处理器plugins标签：配置MyBatis的插件
12. MyBatis的多表操作
12.1. 一对一查询
12.1.1. 一对一查询的模型
用户表和订单表的关系为，一个用户有多个订单，一个订单只从属于一个用户。一对一查询的需求：查询一个订单，与此同时查询出该订单所属的用户
创建表
添加模拟数据
12.1.2. 一对一查询的语句
对应的sql语句：select * from orders o,user u where o.uid=u.id;
查询的结果如下：
12.1.3. 创建Order和User实体
package com.demo.core;
import java.util.Date;
public class Order {
private int id;
private Date ordertime;
private double total;
// 代表当前订单从属于哪一个客户
private User user;
@Override
public String toString() {
return "Order{" +
"id=" + id +
", ordertime=" + ordertime +
", total=" + total +
", user=" + user +
'}';
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public Date getOrdertime() {
return ordertime;
}
public void setOrdertime(Date ordertime) {
this.ordertime = ordertime;
}
public double getTotal() {
return total;
}
public void setTotal(double total) {
this.total = total;
}
public User getUser() {
return user;
}
public void setUser(User user) {
this.user = user;
}
public Order() {
}
public Order(int id, Date ordertime, double total, User user) {
this.id = id;
this.ordertime = ordertime;
this.total = total;
this.user = user;
}
}package com.demo.core;
import java.util.Date;
public class User {
private int id;
private String username;
private String password;
private Date birthday;
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", password='" + password + '\'' +
", birthday=" + birthday +
'}';
}
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 Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public User() {
}
public User(int id, String username, String password, Date birthday) {
this.id = id;
this.username = username;
this.password = password;
this.birthday = birthday;
}
}12.1.4. 创建OrderMapper接口
package com.demo.core.interfaces;
import com.demo.core.Order;
import java.util.List;
public interface OrderMapper {
List<Order> findAll();
}12.1.5. 配置OrderMapper.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.demo.core.interfaces.OrderMapper">
<!--结果集-->
<resultMap id="orderMap" type="com.demo.core.Order">
<!--将查询出来的uid的值赋值给User类中user成员的id值中-->
<!--其中column指向的是查询出来的字段名 property代表赋值给哪个属性-->
<result column="uid" property="user.id"/>
<!--将查询出来的username的值赋值给User类中user成员的username值中-->
<result column="username" property="user.username"/>
<!--将查询出来的password的值赋值给User类中user成员的password值中-->
<result column="password" property="user.password"/>
<!--将查询出来的birthday的值赋值给User类中user成员的birthday值中-->
<result column="birthday" property="user.birthday"/>
</resultMap>
<select id="findAll" resultMap="orderMap">
select *
from orders o,
user u
where o.uid = u.id
</select>
<!--resultMap配置方法二-->
<!-- <resultMap id="orderMap" type="com.itbihuo.domain.Order">-->
<!-- <result property="id" column="id"/>-->
<!-- <result property="ordertime" column="ordertime"/>-->
<!-- <result property="total" column="total"/>-->
<!-- <association property="user" javaType="com.itbihuo.domain.User">-->
<!-- <result column="uid" property="id"/>-->
<!-- <result column="username" property="username"/>-->
<!-- <result column="password" property="password"/>-->
<!-- <result column="birthday" property="birthday"/>-->
<!-- </association>-->
<!-- </resultMap>-->
</mapper>
12.1.6. 数据库配置
<?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>org.example</groupId>
<artifactId>MyBatis_001</artifactId>
<version>1.0-SNAPSHOT</version>
<properties>
<maven.compiler.source>8</maven.compiler.source>
<maven.compiler.target>8</maven.compiler.target>
</properties>
<!--dependencies 依赖包的意思-->
<dependencies>
<!--mybatis坐标-->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.4</version>
</dependency>
<!--mysql驱动坐标-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.6</version>
<scope>runtime</scope>
</dependency>
<!--单元测试坐标-->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>
test
</scope>
</dependency>
<!--日志坐标-->
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.12</version>
</dependency>
<dependency>
<groupId>org.junit.jupiter</groupId>
<artifactId>junit-jupiter</artifactId>
<version>RELEASE</version>
<scope>compile</scope>
</dependency>
<!-- 分页助手 -->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>3.7.5</version>
</dependency>
<dependency>
<groupId>com.github.jsqlparser</groupId>
<artifactId>jsqlparser</artifactId>
<version>0.9.1</version>
</dependency>
</dependencies>
</project><?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"https://mybatis.org/dtd/mybatis-3-config.dtd">
<!--配置-->
<configuration>
<!--属性相关的资源-->
<!--引入mybatis配置文件-->
<properties resource="jdbc.properties"/>
<!--别名相关的资源-->
<!--设置typeAliases标签-->
<typeAliases>
<typeAlias type="com.demo.core.User" alias="user"/>
</typeAliases>
<!--配置环境变量-->
<environments default="development">
<environment id="development">
<!--使用JDBC作为驱动内核-->
<transactionManager type="JDBC"/>
<!--配置事务的类型-->
<dataSource type="POOLED">
<!--四个属性,驱动,链接,用户名,密码-->
<property name="driver" value="${jdbc.driver}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</dataSource>
</environment>
</environments>
<!--映射相关的资源-->
<mappers>
<mapper resource="./UserMapper.xml"/>
<mapper resource="./OrderMapper.xml"/>
</mappers>
</configuration>jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://192.168.11.128:3306/security
jdbc.username=root
jdbc.password=redhat12.1.7. 测试结果
package com.demo.core;
import com.demo.core.interfaces.OrderMapper;
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;
import java.util.List;
public class TestClass {
public static void main(String[] args) throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
// 获得MyBatis框架生成的UserMapper接口的实现类
// 通过利用getMapper返回来的代理类型的对象会自动去执行想要执行的操作
OrderMapper mapper = sqlSession.getMapper(OrderMapper.class);
List<Order> orders = mapper.findAll();
for (Order order : orders) {
System.out.println(order);
}
sqlSession.close();
}
}
12.2. 一对多查询
12.2.1. 一对多查询的模型
用户表和订单表的关系为，一个用户有多个订单，一个订单只从属于一个用户.一对多查询的需求：查询一个用户，与此同时查询出该用户具有的订单
12.2.2. 一对多查询的语句
对应的sql语句：select *,o.id oid from user u left join orders o on u.id=o.uid;
查询的结果如下：
12.2.3. 修改User实体，Order不变
package com.demo.core;
import java.util.Date;
import java.util.List;
public class User {
private int id;
private String username;
private String password;
private Date birthday;
//代表当前用户具备哪些订单
private List<Order> orderList;
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", password='" + password + '\'' +
", birthday=" + birthday +
", orderList=" + orderList +
'}';
}
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 Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public List<Order> getOrderList() {
return orderList;
}
public void setOrderList(List<Order> orderList) {
this.orderList = orderList;
}
public User() {
}
public User(int id, String username, String password, Date birthday, List<Order> orderList) {
this.id = id;
this.username = username;
this.password = password;
this.birthday = birthday;
this.orderList = orderList;
}
}package com.demo.core;
import java.util.Date;
public class Order {
private int id;
private Date ordertime;
private double total;
// 代表当前订单从属于哪一个客户
private User user;
@Override
public String toString() {
return "Order{" +
"id=" + id +
", ordertime=" + ordertime +
", total=" + total +
", user=" + user +
'}';
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public Date getOrdertime() {
return ordertime;
}
public void setOrdertime(Date ordertime) {
this.ordertime = ordertime;
}
public double getTotal() {
return total;
}
public void setTotal(double total) {
this.total = total;
}
public User getUser() {
return user;
}
public void setUser(User user) {
this.user = user;
}
public Order() {
}
public Order(int id, Date ordertime, double total, User user) {
this.id = id;
this.ordertime = ordertime;
this.total = total;
this.user = user;
}
}12.2.4. 创建UserMapper接口，OrderMapper接口不变
package com.demo.core.interfaces;
import com.demo.core.User;
import java.util.List;
public interface UserMapper {
List<User> findAll();
}package com.demo.core.interfaces;
import com.demo.core.Order;
import java.util.List;
public interface OrderMapper {
List<Order> findAll();
}12.2.5. 配置UserMapper.xml，OrderMapper.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.demo.core.interfaces.UserMapper">
<resultMap id="userMap" type="com.demo.core.User">
<result column="id" property="id"/>
<result column="username" property="username"/>
<result column="password" property="password"/>
<result column="birthday" property="birthday"/>
<!--当想要设置的属性为一个集合时-->
<!--此时需要使用collection标签-->
<!--其中ofType指定的集合中的成员所属的类-->
<collection property="orderList" ofType="com.demo.core.Order">
<result column="oid" property="id"/>
<result column="ordertime" property="ordertime"/>
<result column="total" property="total"/>
</collection>
</resultMap>
<select id="findAll" resultMap="userMap">
select *, o.id oid
from user u
left join orders o on u.id = o.uid
</select>
</mapper>
<?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.demo.core.interfaces.OrderMapper">
<!--结果集-->
<resultMap id="orderMap" type="com.demo.core.Order">
<!--将查询出来的uid的值赋值给User类中user成员的id值中-->
<!--其中column指向的是查询出来的字段名 property代表赋值给哪个属性-->
<result column="uid" property="user.id"/>
<!--将查询出来的username的值赋值给User类中user成员的username值中-->
<result column="username" property="user.username"/>
<!--将查询出来的password的值赋值给User类中user成员的password值中-->
<result column="password" property="user.password"/>
<!--将查询出来的birthday的值赋值给User类中user成员的birthday值中-->
<result column="birthday" property="user.birthday"/>
</resultMap>
<select id="findAll" resultMap="orderMap">
select *
from orders o,
user u
where o.uid = u.id
</select>
<!--resultMap配置方法二-->
<!-- <resultMap id="orderMap" type="com.itbihuo.domain.Order">-->
<!-- <result property="id" column="id"/>-->
<!-- <result property="ordertime" column="ordertime"/>-->
<!-- <result property="total" column="total"/>-->
<!-- <association property="user" javaType="com.itbihuo.domain.User">-->
<!-- <result column="uid" property="id"/>-->
<!-- <result column="username" property="username"/>-->
<!-- <result column="password" property="password"/>-->
<!-- <result column="birthday" property="birthday"/>-->
<!-- </association>-->
<!-- </resultMap>-->
</mapper>12.2.6. 数据库配置
<?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>org.example</groupId>
<artifactId>MyBatis_001</artifactId>
<version>1.0-SNAPSHOT</version>
<properties>
<maven.compiler.source>8</maven.compiler.source>
<maven.compiler.target>8</maven.compiler.target>
</properties>
<!--dependencies 依赖包的意思-->
<dependencies>
<!--mybatis坐标-->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.4</version>
</dependency>
<!--mysql驱动坐标-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.6</version>
<scope>runtime</scope>
</dependency>
<!--单元测试坐标-->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>
test
</scope>
</dependency>
<!--日志坐标-->
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.12</version>
</dependency>
<dependency>
<groupId>org.junit.jupiter</groupId>
<artifactId>junit-jupiter</artifactId>
<version>RELEASE</version>
<scope>compile</scope>
</dependency>
<!-- 分页助手 -->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>3.7.5</version>
</dependency>
<dependency>
<groupId>com.github.jsqlparser</groupId>
<artifactId>jsqlparser</artifactId>
<version>0.9.1</version>
</dependency>
</dependencies>
</project><?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"https://mybatis.org/dtd/mybatis-3-config.dtd">
<!--配置-->
<configuration>
<!--属性相关的资源-->
<!--引入mybatis配置文件-->
<properties resource="jdbc.properties"/>
<!--别名相关的资源-->
<!--设置typeAliases标签-->
<typeAliases>
<typeAlias type="com.demo.core.User" alias="user"/>
</typeAliases>
<!--配置环境变量-->
<environments default="development">
<environment id="development">
<!--使用JDBC作为驱动内核-->
<transactionManager type="JDBC"/>
<!--配置事务的类型-->
<dataSource type="POOLED">
<!--四个属性,驱动,链接,用户名,密码-->
<property name="driver" value="${jdbc.driver}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</dataSource>
</environment>
</environments>
<!--映射相关的资源-->
<mappers>
<mapper resource="./UserMapper.xml"/>
<mapper resource="./OrderMapper.xml"/>
</mappers>
</configuration>jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://192.168.100.141:3306/security
jdbc.username=root
jdbc.password=redhat12.2.7. 测试结果
package com.demo.core;
import com.demo.core.interfaces.UserMapper;
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;
import java.util.List;
public class TestClass {
public static void main(String[] args) throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
// 获得MyBatis框架生成的UserMapper接口的实现类
// 通过利用getMapper返回来的代理类型的对象会自动去执行想要执行的操作
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<User> users = mapper.findAll();
for (User user : users) {
System.out.println(user);
}
sqlSession.close();
}
}
12.3. 多对多查询
12.3.1. 多对多查询的模型
用户表和角色表的关系为，一个用户有多个角色，一个角色被多个用户使用。多对多查询的需求：查询用户同时查询出该用户的所有角色
创建表
添加模拟数据
12.3.2. 多对多查询的语句
对应的sql语句：select u.*,r.*,r.id rid from user u left join user_role ur on u.id=ur.user_id inner join role r on ur.role_id=r.id;
查询的结果如下：
多个用户可能对应多个职位
12.3.3. 创建Role实体，修改User实体，Order不变
package com.demo.core;
public class Role {
private int id;
private String rolename;
@Override
public String toString() {
return "Role{" +
"id=" + id +
", rolename='" + rolename + '\'' +
'}';
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getRolename() {
return rolename;
}
public void setRolename(String rolename) {
this.rolename = rolename;
}
public Role() {
}
public Role(int id, String rolename) {
this.id = id;
this.rolename = rolename;
}
}package com.demo.core;
import java.util.Date;
import java.util.List;
public class User {
private int id;
private String username;
private String password;
private Date birthday;
//代表当前用户具备哪些订单
private List<Order> orderList;
//代表当前用户具备哪些角色
private List<Role> roleList;
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", password='" + password + '\'' +
", birthday=" + birthday +
", orderList=" + orderList +
", roleList=" + roleList +
'}';
}
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 Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public List<Order> getOrderList() {
return orderList;
}
public void setOrderList(List<Order> orderList) {
this.orderList = orderList;
}
public List<Role> getRoleList() {
return roleList;
}
public void setRoleList(List<Role> roleList) {
this.roleList = roleList;
}
public User() {
}
public User(int id, String username, String password, Date birthday, List<Order> orderList, List<Role> roleList) {
this.id = id;
this.username = username;
this.password = password;
this.birthday = birthday;
this.orderList = orderList;
this.roleList = roleList;
}
}package com.demo.core;
import java.util.Date;
public class Order {
private int id;
private Date ordertime;
private double total;
// 代表当前订单从属于哪一个客户
private User user;
@Override
public String toString() {
return "Order{" +
"id=" + id +
", ordertime=" + ordertime +
", total=" + total +
", user=" + user +
'}';
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public Date getOrdertime() {
return ordertime;
}
public void setOrdertime(Date ordertime) {
this.ordertime = ordertime;
}
public double getTotal() {
return total;
}
public void setTotal(double total) {
this.total = total;
}
public User getUser() {
return user;
}
public void setUser(User user) {
this.user = user;
}
public Order() {
}
public Order(int id, Date ordertime, double total, User user) {
this.id = id;
this.ordertime = ordertime;
this.total = total;
this.user = user;
}
}12.3.4. 添加UserMapper接口方法，OrderMapper接口不变
package com.demo.core.interfaces;
import com.demo.core.User;
import java.util.List;
public interface UserMapper {
List<User> findAll();
List<User> findAllUserAndRole();
}package com.demo.core.interfaces;
import com.demo.core.Order;
import java.util.List;
public interface OrderMapper {
List<Order> findAll();
}12.3.5. 配置UserMapper.xml，OrderMapper.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.demo.core.interfaces.UserMapper">
<resultMap id="userMap" type="com.demo.core.User">
<result column="id" property="id"/>
<result column="username" property="username"/>
<result column="password" property="password"/>
<result column="birthday" property="birthday"/>
<!--当想要设置的属性为一个集合时-->
<!--此时需要使用collection标签-->
<!--其中ofType指定的集合中的成员所属的类-->
<collection property="orderList" ofType="com.demo.core.Order">
<result column="oid" property="id"/>
<result column="ordertime" property="ordertime"/>
<result column="total" property="total"/>
</collection>
</resultMap>
<select id="findAll" resultMap="userMap">
select *, o.id oid
from user u
left join orders o on u.id = o.uid
</select>
<resultMap id="userRoleMap" type="com.demo.core.User">
<result column="id" property="id"/>
<result column="username" property="username"/>
<result column="password" property="password"/>
<result column="birthday" property="birthday"/>
<collection property="roleList" ofType="com.demo.core.Role">
<result column="rid" property="id"/>
<result column="rolename" property="rolename"/>
</collection>
</resultMap>
<select id="findAllUserAndRole" resultMap="userRoleMap">
select u.*, r.*, r.id rid
from user u
left join user_role ur on u.id = ur.user_id
inner join role r on ur.role_id = r.id
</select>
</mapper>
<?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.demo.core.interfaces.OrderMapper">
<!--结果集-->
<resultMap id="orderMap" type="com.demo.core.Order">
<!--将查询出来的uid的值赋值给User类中user成员的id值中-->
<!--其中column指向的是查询出来的字段名 property代表赋值给哪个属性-->
<result column="uid" property="user.id"/>
<!--将查询出来的username的值赋值给User类中user成员的username值中-->
<result column="username" property="user.username"/>
<!--将查询出来的password的值赋值给User类中user成员的password值中-->
<result column="password" property="user.password"/>
<!--将查询出来的birthday的值赋值给User类中user成员的birthday值中-->
<result column="birthday" property="user.birthday"/>
</resultMap>
<select id="findAll" resultMap="orderMap">
select *
from orders o,
user u
where o.uid = u.id
</select>
<!--resultMap配置方法二-->
<!-- <resultMap id="orderMap" type="com.itbihuo.domain.Order">-->
<!-- <result property="id" column="id"/>-->
<!-- <result property="ordertime" column="ordertime"/>-->
<!-- <result property="total" column="total"/>-->
<!-- <association property="user" javaType="com.itbihuo.domain.User">-->
<!-- <result column="uid" property="id"/>-->
<!-- <result column="username" property="username"/>-->
<!-- <result column="password" property="password"/>-->
<!-- <result column="birthday" property="birthday"/>-->
<!-- </association>-->
<!-- </resultMap>-->
</mapper>12.3.6. 数据库配置
<?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>org.example</groupId>
<artifactId>MyBatis_001</artifactId>
<version>1.0-SNAPSHOT</version>
<properties>
<maven.compiler.source>8</maven.compiler.source>
<maven.compiler.target>8</maven.compiler.target>
</properties>
<!--dependencies 依赖包的意思-->
<dependencies>
<!--mybatis坐标-->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.4</version>
</dependency>
<!--mysql驱动坐标-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.6</version>
<scope>runtime</scope>
</dependency>
<!--单元测试坐标-->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>
test
</scope>
</dependency>
<!--日志坐标-->
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.12</version>
</dependency>
<dependency>
<groupId>org.junit.jupiter</groupId>
<artifactId>junit-jupiter</artifactId>
<version>RELEASE</version>
<scope>compile</scope>
</dependency>
<!-- 分页助手 -->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>3.7.5</version>
</dependency>
<dependency>
<groupId>com.github.jsqlparser</groupId>
<artifactId>jsqlparser</artifactId>
<version>0.9.1</version>
</dependency>
</dependencies>
</project><?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"https://mybatis.org/dtd/mybatis-3-config.dtd">
<!--配置-->
<configuration>
<!--属性相关的资源-->
<!--引入mybatis配置文件-->
<properties resource="jdbc.properties"/>
<!--别名相关的资源-->
<!--设置typeAliases标签-->
<typeAliases>
<typeAlias type="com.demo.core.User" alias="user"/>
</typeAliases>
<!--配置环境变量-->
<environments default="development">
<environment id="development">
<!--使用JDBC作为驱动内核-->
<transactionManager type="JDBC"/>
<!--配置事务的类型-->
<dataSource type="POOLED">
<!--四个属性,驱动,链接,用户名,密码-->
<property name="driver" value="${jdbc.driver}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</dataSource>
</environment>
</environments>
<!--映射相关的资源-->
<mappers>
<mapper resource="./UserMapper.xml"/>
<mapper resource="./OrderMapper.xml"/>
</mappers>
</configuration>jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://192.168.100.141:3306/security
jdbc.username=root
jdbc.password=redhat12.3.7. 测试结果
package com.demo.core;
import com.demo.core.interfaces.UserMapper;
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;
import java.util.List;
public class TestClass {
public static void main(String[] args) throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
// 获得MyBatis框架生成的UserMapper接口的实现类
// 通过利用getMapper返回来的代理类型的对象会自动去执行想要执行的操作
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<User> users = mapper.findAllUserAndRole();
for (User user : users) {
System.out.println(user);
}
sqlSession.close();
}
}
12.4. sql注入
- 添加
sql查询语句
- 添加接口方法
- 调用
findBuId方法
package com.demo.core;
import com.demo.core.interfaces.UserMapper;
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;
import java.util.List;
public class TestClass {
public static void main(String[] args) throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
// 获得MyBatis框架生成的UserMapper接口的实现类
// 通过利用getMapper返回来的代理类型的对象会自动去执行想要执行的操作
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<User> buId = mapper.findBuId("1' union select 1,2,3");
for (User user : buId) {
System.out.println(user);
}
sqlSession.close();
}
}- 当使用
${}，此时变量将不会被预编译，可能导致sql注入
- 存在注入
package com.demo.core;
import com.demo.core.interfaces.UserMapper;
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;
import java.util.List;
public class TestClass {
public static void main(String[] args) throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
// 获得MyBatis框架生成的UserMapper接口的实现类
// 通过利用getMapper返回来的代理类型的对象会自动去执行想要执行的操作
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<User> buId = mapper.findBuId("1 union select 1,user(),database(),2020-2-2");
for (User user : buId) {
System.out.println(user);
}
}
}
12.5. MyBatis解决SQL注入问题
12.6. 知识小结
MyBatis多表配置方式：
一对一配置：使用<resultMap>做配置
一对多配置：使用<resultMap>+<collection>做配置
多对多配置：使用<resultMap>+<collection>做配置
13. MyBatis注解开发
13.1. MyBatis的常用注解
这几年来注解开发越来越流行，Mybatis也可以使用注解开发方式，这样我们就可以减少编写Mapper映射文件了。我们先围绕一些基本的CRUD来学习，再学习复杂映射多表操作。
@Insert：实现新增
@Update：实现更新
@Delete：实现删除
@Select：实现查询
@Result：实现结果集封装
@Results：可以与@Result一起使用，封装多个结果集
@One：实现一对一结果集封装
@Many：实现一对多结果集封装
13.2. MyBatis的增删改查
13.2.1. 查
package com.demo.core;
import com.demo.core.interfaces.UserMapper;
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 TestClass {
public static void main(String[] args) throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
// 获得MyBatis框架生成的UserMapper接口的实现类
// 通过利用getMapper返回来的代理类型的对象会自动去执行想要执行的操作
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User user = new User();
// 查
// List<User> allUsers = mapper.getAllUsers();
// for (User user : allUsers) {
// System.out.println(user);
// }
for (User allUser : mapper.getAllUsers()) {
System.out.println("原数据: " + allUser);
}
System.out.println();
sqlSession.close();
}
}package com.demo.core.interfaces;
import com.demo.core.User;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import java.util.List;
public interface UserMapper {
@Select("select * from user")
@Results(id = "empMap", value = {
@Result(column = "id", property = "id", id = true),
@Result(column = "username", property = "username"),
@Result(column = "password", property = "password"),
@Result(column = "birthday", property = "birthday")
})
List<User> getAllUsers();
}
13.2.2. 改
package com.demo.core;
import com.demo.core.interfaces.UserMapper;
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 TestClass {
public static void main(String[] args) throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
// 获得MyBatis框架生成的UserMapper接口的实现类
// 通过利用getMapper返回来的代理类型的对象会自动去执行想要执行的操作
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User user = new User();
// 查
// List<User> allUsers = mapper.getAllUsers();
// for (User user : allUsers) {
// System.out.println(user);
// }
for (User allUser : mapper.getAllUsers()) {
System.out.println("原数据: " + allUser);
}
System.out.println();
// 改
user.setUsername("test001");
user.setId(1);
mapper.updateUser(user);
for (User allUser : mapper.getAllUsers()) {
System.out.println("修改后: " + allUser);
}
System.out.println();
sqlSession.close();
}
}package com.demo.core.interfaces;
import com.demo.core.User;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import java.util.List;
public interface UserMapper {
@Select("select * from user")
@Results(id = "empMap", value = {
@Result(column = "id", property = "id", id = true),
@Result(column = "username", property = "username"),
@Result(column = "password", property = "password"),
@Result(column = "birthday", property = "birthday"),
}
)
List<User> getAllUsers();
@Update("update user set username = #{username} where id = #{id}")
void updateUser(User user);
}
13.2.3. 删
package com.demo.core;
import com.demo.core.interfaces.UserMapper;
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 TestClass {
public static void main(String[] args) throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
// 获得MyBatis框架生成的UserMapper接口的实现类
// 通过利用getMapper返回来的代理类型的对象会自动去执行想要执行的操作
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User user = new User();
// 查
// List<User> allUsers = mapper.getAllUsers();
// for (User user : allUsers) {
// System.out.println(user);
// }
for (User allUser : mapper.getAllUsers()) {
System.out.println("原数据: " + allUser);
}
System.out.println();
// 改
user.setUsername("test001");
user.setId(1);
mapper.updateUser(user);
for (User allUser : mapper.getAllUsers()) {
System.out.println("修改后: " + allUser);
}
System.out.println();
// 删
user.setId(2);
mapper.deleteUser(user);
for (User allUser : mapper.getAllUsers()) {
System.out.println("删除后: " + allUser);
}
System.out.println();
sqlSession.close();
}
}package com.demo.core.interfaces;
import com.demo.core.User;
import org.apache.ibatis.annotations.*;
import java.util.List;
public interface UserMapper {
@Select("select * from user")
@Results(id = "empMap", value = {
@Result(column = "id", property = "id", id = true),
@Result(column = "username", property = "username"),
@Result(column = "password", property = "password"),
@Result(column = "birthday", property = "birthday"),
}
)
List<User> getAllUsers();
@Update("update user set username = #{username} where id = #{id}")
void updateUser(User user);
@Delete("delete from user where id = #{id}")
void deleteUser(User user);
}
13.2.4. 增
package com.demo.core;
import com.demo.core.interfaces.UserMapper;
import org.apache.ibatis.annotations.Insert;
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 TestClass {
public static void main(String[] args) throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
// 获得MyBatis框架生成的UserMapper接口的实现类
// 通过利用getMapper返回来的代理类型的对象会自动去执行想要执行的操作
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User user = new User();
// 查
// List<User> allUsers = mapper.getAllUsers();
// for (User user : allUsers) {
// System.out.println(user);
// }
for (User allUser : mapper.getAllUsers()) {
System.out.println("原数据: " + allUser);
}
System.out.println();
// 改
user.setUsername("test001");
user.setId(1);
mapper.updateUser(user);
for (User allUser : mapper.getAllUsers()) {
System.out.println("修改后: " + allUser);
}
System.out.println();
// 删
user.setId(2);
mapper.deleteUser(user);
for (User allUser : mapper.getAllUsers()) {
System.out.println("删除后: " + allUser);
}
System.out.println();
// 增
user.setUsername("nihao");
user.setPassword("password");
mapper.insertUser(user);
for (User allUser : mapper.getAllUsers()) {
System.out.println("增加后: " + allUser);
}
sqlSession.close();
}
}package com.demo.core.interfaces;
import com.demo.core.User;
import org.apache.ibatis.annotations.*;
import java.util.List;
public interface UserMapper {
@Select("select * from user")
@Results(id = "empMap", value = {
@Result(column = "id", property = "id", id = true),
@Result(column = "username", property = "username"),
@Result(column = "password", property = "password"),
@Result(column = "birthday", property = "birthday"),
}
)
List<User> getAllUsers();
@Update("update user set username = #{username} where id = #{id}")
void updateUser(User user);
@Delete("delete from user where id = #{id}")
void deleteUser(User user);
@Insert("insert into user (username, password) values (#{username}, #{password})")
void insertUser(User user);
}
13.2.5. 数据库配置
<?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>org.example</groupId>
<artifactId>MyBatis_001</artifactId>
<version>1.0-SNAPSHOT</version>
<properties>
<maven.compiler.source>8</maven.compiler.source>
<maven.compiler.target>8</maven.compiler.target>
</properties>
<!--dependencies 依赖包的意思-->
<dependencies>
<!--mybatis坐标-->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.4</version>
</dependency>
<!--mysql驱动坐标-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.6</version>
<scope>runtime</scope>
</dependency>
<!--单元测试坐标-->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>
test
</scope>
</dependency>
<!--日志坐标-->
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.12</version>
</dependency>
<dependency>
<groupId>org.junit.jupiter</groupId>
<artifactId>junit-jupiter</artifactId>
<version>RELEASE</version>
<scope>compile</scope>
</dependency>
<!-- 分页助手 -->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>3.7.5</version>
</dependency>
<dependency>
<groupId>com.github.jsqlparser</groupId>
<artifactId>jsqlparser</artifactId>
<version>0.9.1</version>
</dependency>
</dependencies>
</project><?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"https://mybatis.org/dtd/mybatis-3-config.dtd">
<!--配置-->
<configuration>
<!--属性相关的资源-->
<!--引入mybatis配置文件-->
<properties resource="jdbc.properties"/>
<!--别名相关的资源-->
<!--设置typeAliases标签-->
<typeAliases>
<typeAlias type="com.demo.core.User" alias="user"/>
</typeAliases>
<!--配置环境变量-->
<environments default="development">
<environment id="development">
<!--使用JDBC作为驱动内核-->
<transactionManager type="JDBC"/>
<!--配置事务的类型-->
<dataSource type="POOLED">
<!--四个属性,驱动,链接,用户名,密码-->
<property name="driver" value="${jdbc.driver}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</dataSource>
</environment>
</environments>
<!--映射相关的资源-->
<mappers>
<!--<mapper resource="./UserMapper.xml"/>-->
<!--<mapper resource="./OrderMapper.xml"/>-->
<mapper class="com.demo.core.interfaces.UserMapper"/>
</mappers>
</configuration>jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://192.168.11.128:3306/security
jdbc.username=root
jdbc.password=redhatpackage com.demo.core;
public class Role {
private int id;
private String rolename;
@Override
public String toString() {
return "Role{" +
"id=" + id +
", rolename='" + rolename + '\'' +
'}';
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getRolename() {
return rolename;
}
public void setRolename(String rolename) {
this.rolename = rolename;
}
public Role() {
}
public Role(int id, String rolename) {
this.id = id;
this.rolename = rolename;
}
}package com.demo.core;
import java.util.Date;
import java.util.List;
public class User {
private int id;
private String username;
private String password;
private Date birthday;
//代表当前用户具备哪些订单
private List<Order> orderList;
//代表当前用户具备哪些角色
private List<Role> roleList;
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", password='" + password + '\'' +
", birthday=" + birthday +
", orderList=" + orderList +
", roleList=" + roleList +
'}';
}
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 Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public List<Order> getOrderList() {
return orderList;
}
public void setOrderList(List<Order> orderList) {
this.orderList = orderList;
}
public List<Role> getRoleList() {
return roleList;
}
public void setRoleList(List<Role> roleList) {
this.roleList = roleList;
}
public User() {
}
public User(int id, String username, String password, Date birthday, List<Order> orderList, List<Role> roleList) {
this.id = id;
this.username = username;
this.password = password;
this.birthday = birthday;
this.orderList = orderList;
this.roleList = roleList;
}
}package com.demo.core;
import java.util.Date;
public class Order {
private int id;
private Date ordertime;
private double total;
// 代表当前订单从属于哪一个客户
private User user;
@Override
public String toString() {
return "Order{" +
"id=" + id +
", ordertime=" + ordertime +
", total=" + total +
", user=" + user +
'}';
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public Date getOrdertime() {
return ordertime;
}
public void setOrdertime(Date ordertime) {
this.ordertime = ordertime;
}
public double getTotal() {
return total;
}
public void setTotal(double total) {
this.total = total;
}
public User getUser() {
return user;
}
public void setUser(User user) {
this.user = user;
}
public Order() {
}
public Order(int id, Date ordertime, double total, User user) {
this.id = id;
this.ordertime = ordertime;
this.total = total;
this.user = user;
}
}13.3. MyBatis的注解实现复杂映射开发
实现复杂关系映射之前我们可以在映射文件中通过配置<resultMap>来实现，使用注解开发后，我们可以使用@Results注解，@Result注解，@One注解，@Many注解组合完成复杂关系的配置
| 注解 | 说明 |
| @Results | 代替的是标签<resultMap>该注解中可以使用单个@Result注解，也可以使用@Result集合。使用格式：@Results（{@Result（），@Result（）}）或@Results（@Result（）） |
| @Resut | 代替了<id>标签和<result>标签@Result中属性介绍：column：数据库的列名property：需要装配的属性名one：需要使用的@One 注解（@Result（one=@One）（）））many：需要使用的@Many 注解（@Result（many=@many）（））） |
| 注解 | 说明 |
| @One （一对一） | 代替了<assocation> 标签，是多表查询的关键，在注解中用来指定子查询返回单一对象。@One注解属性介绍：select: 指定用来多表查询的 sqlmapper使用格式：@Result(column=” “,property=””,one=@One(select=””)) |
| @Many （多对一） | 代替了<collection>标签, 是是多表查询的关键，在注解中用来指定子查询返回对象集合。使用格式：@Result(property=””,column=””,many=@Many(select=””)) |
13.4. 一对一查询
13.4.1. 一对一查询的模型
用户表和订单表的关系为，一个用户有多个订单，一个订单只从属于一个用户。一对一查询的需求：查询一个订单，与此同时查询出该订单所属的用户
13.4.2. 一对一查询的语句
对应的sql语句：
select * from orders;
select * from user where id=查询出订单的uid;
查询的结果如下：
13.4.3. 创建Order和User实体
package com.demo.core;
import java.util.Date;
public class Order {
private int id;
private Date ordertime;
private double total;
// 代表当前订单从属于哪一个客户
private User user;
@Override
public String toString() {
return "Order{" +
"id=" + id +
", ordertime=" + ordertime +
", total=" + total +
", user=" + user +
'}';
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public Date getOrdertime() {
return ordertime;
}
public void setOrdertime(Date ordertime) {
this.ordertime = ordertime;
}
public double getTotal() {
return total;
}
public void setTotal(double total) {
this.total = total;
}
public User getUser() {
return user;
}
public void setUser(User user) {
this.user = user;
}
public Order() {
}
public Order(int id, Date ordertime, double total, User user) {
this.id = id;
this.ordertime = ordertime;
this.total = total;
this.user = user;
}
}package com.demo.core;
import java.util.Date;
import java.util.List;
public class User {
private int id;
private String username;
private String password;
private Date birthday;
//代表当前用户具备哪些订单
private List<Order> orderList;
//代表当前用户具备哪些角色
private List<Role> roleList;
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", password='" + password + '\'' +
", birthday=" + birthday +
", orderList=" + orderList +
", roleList=" + roleList +
'}';
}
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 Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public List<Order> getOrderList() {
return orderList;
}
public void setOrderList(List<Order> orderList) {
this.orderList = orderList;
}
public List<Role> getRoleList() {
return roleList;
}
public void setRoleList(List<Role> roleList) {
this.roleList = roleList;
}
public User() {
}
public User(int id, String username, String password, Date birthday, List<Order> orderList, List<Role> roleList) {
this.id = id;
this.username = username;
this.password = password;
this.birthday = birthday;
this.orderList = orderList;
this.roleList = roleList;
}
}13.4.4. 创建OrderMapper、UserMapper接口，并使用注解配置Mapper
package com.demo.core.interfaces;
import com.demo.core.Order;
import com.demo.core.User;
import org.apache.ibatis.annotations.One;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import java.util.List;
public interface OrderMapper {
@Select("select * from orders")
@Results({
@Result(id = true, column = "id", property = "id"),
@Result(column = "ordertime", property = "ordertime"),
@Result(column = "total", property = "total"),
@Result(column = "uid", property = "user",
javaType = User.class,
one = @One(
select = "com.demo.core.interfaces.UserMapper.findById"
))
})
List<Order> findAll();
}
package com.demo.core.interfaces;
import com.demo.core.User;
import org.apache.ibatis.annotations.*;
public interface UserMapper {
@Select("select * from user where id = #{id}")
User findById(int uid);
}13.4.5. 数据库配置
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://192.168.100.141:3306/security
jdbc.username=root
jdbc.password=redhat<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"https://mybatis.org/dtd/mybatis-3-config.dtd">
<!--配置-->
<configuration>
<!--属性相关的资源-->
<!--引入mybatis配置文件-->
<properties resource="jdbc.properties"/>
<!--别名相关的资源-->
<!--设置typeAliases标签-->
<typeAliases>
<typeAlias type="com.demo.core.User" alias="user"/>
</typeAliases>
<!--配置环境变量-->
<environments default="development">
<environment id="development">
<!--使用JDBC作为驱动内核-->
<transactionManager type="JDBC"/>
<!--配置事务的类型-->
<dataSource type="POOLED">
<!--四个属性,驱动,链接,用户名,密码-->
<property name="driver" value="${jdbc.driver}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</dataSource>
</environment>
</environments>
<!--映射相关的资源-->
<mappers>
<mapper class="com.demo.core.interfaces.UserMapper"/>
<mapper class="com.demo.core.interfaces.OrderMapper"/>
</mappers>
</configuration><?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>org.example</groupId>
<artifactId>MyBatis_001</artifactId>
<version>1.0-SNAPSHOT</version>
<properties>
<maven.compiler.source>8</maven.compiler.source>
<maven.compiler.target>8</maven.compiler.target>
</properties>
<!--dependencies 依赖包的意思-->
<dependencies>
<!--mybatis坐标-->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.4</version>
</dependency>
<!--mysql驱动坐标-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.6</version>
<scope>runtime</scope>
</dependency>
<!--单元测试坐标-->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>
test
</scope>
</dependency>
<!--日志坐标-->
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.12</version>
</dependency>
<dependency>
<groupId>org.junit.jupiter</groupId>
<artifactId>junit-jupiter</artifactId>
<version>RELEASE</version>
<scope>compile</scope>
</dependency>
<!-- 分页助手 -->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>3.7.5</version>
</dependency>
<dependency>
<groupId>com.github.jsqlparser</groupId>
<artifactId>jsqlparser</artifactId>
<version>0.9.1</version>
</dependency>
</dependencies>
</project>13.4.6. 测试结果
package com.demo.core;
import com.demo.core.interfaces.OrderMapper;
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 TestClass {
public static void main(String[] args) throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
// 获得MyBatis框架生成的UserMapper接口的实现类
// 通过利用getMapper返回来的代理类型的对象会自动去执行想要执行的操作
OrderMapper mapper = sqlSession.getMapper(OrderMapper.class);
for (Order order : mapper.findAll()) {
System.out.println(order);
}
sqlSession.close();
}
}
