138 MyBatis

tech2023-03-04 108

文章目录

一、引言1.1什么是框架?1.2什么是ORM框架?1.3使用JDBC完成ORM操作的缺点? 二、MyBatis框架2.1概念2.2访问与下载三、构建Maven项目3.1新建项目3.2选择Maven目录3.3GAV坐标四、MyBatis环境搭建【重点】4.1 pom.xml中引入MyBatis核心依赖4.2创建MyBatis配置文件五、MyBatis开发步骤【重点】5.1建表5.2定义实体类5.3定义DAO接口5.4编写Mapper.xml5.5注册Mapper5.6测试一5.7测试二[了解] 六、细节补充6.1解决mapper.xml存放在resources以外路径中的读取问题6.2 properties配置文件6.3类型别名6.4创建log4j配置文件七、MyBatis的CRUD操作【重点】7.1查询7.1.1序号参数绑定7.1.2注解参数绑定[推荐]7.1.3 Map参数绑定7.1.4对象参数绑定7.1.5模糊查询 7.2删除7.3修改7.4添加7.5主键回填7.5.1通过last_insert_id()查询主键7.5.2通过uuid()查询主键八、MyBatis工具类【重点】8.1封装工具类8.2测试工具类九、ORM映射【重点】9.1 MyBatis自动ORM失效9.2方案一:列的别名9.3方案二:结果映射(ResultMap- 查询结果的封装规则) 十、MyBatis处理关联关系---多表连接【重点】10.1 OneToOne10.1.1单向10.1.2双向 10.2 OneToMany10.3 ManyToMany10.4关系总结十一、动态SQL 【重点】11.1< sql>11.2 < where>11.3< set>11.4< trim>11.5< foreach> 十二、缓存(Cache) 【重点】12.1 一级缓存12.2二级缓存12.2.1开启全局缓存12.2.2指定Mapper缓存12.2.3缓存清空并重新缓存十三、Druid连接池13.1概念13.2不同连接池对比13.2.1测试环境13.2.2基准测试结果对比13.2.3测试结论 13.3配置pom.xml13.4创建DruidDataSourceFactory13.5修改mybatis-config.xml 十四、PageHelper14.1概念14.2访问与下载14.3开发步骤14.3.1引入依赖14.3.2配置MyBatis- confg.xml14.3.3 PageHelper应用方式 14.4 PageInfo对象14.4.1 PageInfo应用方式14.4.2注意事项14.4.3分页练习十五、补充[了解]15.1 MyBatis注解操作15.1.1查询15.1.2删除15.1.3修改15.1.4插入 15.2 $符号的应用场景15.2.1 $符号参数绑定15.2.2 $符号注入攻击 15.3 MyBatis处理关联关系嵌套查询[了解]15.3.1主表查询15.3.2及联调用15.3.3延迟加载

一、引言

1.1什么是框架?

软件的半成品，解决了软件开发过程当中的普适性问题，从而简化了开发步骤，提供了开发的效率。

1.2什么是ORM框架?

ORM (Object Relational Mapping) 对象关系映射，将程序中的一个对象与表中的一行数据一一对应。 ORM框架提供了持久化类与表的映射关系，在运行时参照映射文件的信息，把对象持久化到数据库中。

1.3使用JDBC完成ORM操作的缺点?

存在大量的冗余代码。手工创建Connection、 Statement等。手工将结果集封装成实体对象。查询效率低，没有对数据访问进行过优化(Not Cache)。

二、MyBatis框架

2.1概念

MyBatis本是Apache软件基金会的一个开源项目iBatis, 2010年这个项目由apache software foundation迁移到了Google Code,并且改名为MyBatis。2013年11 月迁移到Github。MyBatis是一个优秀的基于Java的持久层框架，支持自定义SQL, 存储过程和高级映射。MyBatis对原有JDBC操作进行了封装，几乎消除了所有JDBC代码，使开发者只需关注SQL本身。MyBatis可以使用简单的XML或Annotation来配置执行SQL, 并自动完成ORM操作，将执行结果返回。

2.2访问与下载

官方网站: http://www.mybatis.org/mybatis-3/下载地址: https://github.com/mbatis/mybatis-3/releases/tag/mybatis-3.5.1

三、构建Maven项目

3.1新建项目

3.2选择Maven目录

3.3GAV坐标

四、MyBatis环境搭建【重点】

4.1 pom.xml中引入MyBatis核心依赖

在pom.xml中引入相关依赖

<?xml version="1.0" encoding="UTF-8"?> <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>com.wlw</groupId> <artifactId>mybatis_hello</artifactId> <version>1.0-SNAPSHOT</version>  <dependencies>  <dependency> <groupId>org.mybatis</groupId> <artifactId>mybatis</artifactId> <version>3.4.6</version> </dependency>  <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.47</version> </dependency> </dependencies> </project>

4.2创建MyBatis配置文件

创建并配置mybatis-config.xml

<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"> <configuration>  <environments default="wlw_mysql_config">   <environment id="wlw_mysql_config">  <transactionManager type="jdbc"></transactionManager>  <dataSource type="org.apache.ibatis.datasource.pooled.PooledDataSourceFactory"> <property name="driver" value="com.mysql.jdbc.Driver"/>  <property name="url" value="jdbc:mysql://localhost:3306/mybatis_wlw?useUnicode=true & characterEncoding=utf-8"/> <property name="username" value="root"/> <property name="password" value="123456"/> </dataSource> </environment> </environments> </configuration>

注意: mapper.xml默认建议存放在resources中,路径不能以/开头

五、MyBatis开发步骤【重点】

5.1建表

create table t_user( id int primary key auto_increment, username varchar(50), password varchar(50), gender tinyint, regist_time datetime )default charset =utf8;

5.2定义实体类

定义所需CURD操作的实体类

package com.wlw.entity; import java.util.Date; public class User { private Integer id; private String username; private String password; private Boolean gender; private Date registTime; public User(){} public User(Integer id, String username, String password, Boolean gender, Date registTime) { this.id = id; this.username = username; this.password = password; this.gender = gender; this.registTime = registTime; } @Override public String toString() { return "User{" + "id=" + id + ", username='" + username + '\'' + ", password='" + password + '\'' + ", gender=" + gender + ", registTime=" + registTime + '}'; } 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; } public Boolean getGender() { return gender; } public void setGender(Boolean gender) { this.gender = gender; } public Date getRegistTime() { return registTime; } public void setRegistTime(Date registTime) { this.registTime = registTime; } }

5.3定义DAO接口

根据所需DAO定义接口、以及方法

package com.wlw.dao; import com.wlw.entity.User; public interface UserDAO { User queryUserById(Integer id); }

5.4编写Mapper.xml

在resources目录中创建Mapper.xml文件 (UserDAOMapper.xml)

这个Mapper.xml文件就代替了之前我们写的接口DAO的实现类，但要在mybatis-config.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.wlw.dao.UserDAO">   <select id="queryUserById" resultType="com.wlw.entity.User"> select id,username,password,gender,regist_time as registTime from t_user where id=#{arg0} </select> </mapper>

5.5注册Mapper

将Mapper.xml注册到mybatis-config.xml中

<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"> <configuration>  <environments default="wlw_mysql_config">   <environment id="wlw_mysql_config">  <transactionManager type="jdbc"></transactionManager>  <dataSource type="org.apache.ibatis.datasource.pooled.PooledDataSourceFactory"> <property name="driver" value="com.mysql.jdbc.Driver"/>  <property name="url" value="jdbc:mysql://localhost:3306/mybatis_wlw?useUnicode=true&characterEncoding=utf-8"/> <property name="username" value="root"/> <property name="password" value="123456"/> </dataSource> </environment> </environments>  <mappers> <mapper resource="UserDAOMapper.xml"></mapper> </mappers> </configuration>

5.6测试一

MyBatis的API操作方式

package com.wlw.test; import com.wlw.dao.UserDAO; import com.wlw.entity.User; 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 TestMyBatis { public static void main(String[] args) throws IOException { //MyBatis API //1.加载配置文件 InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml"); //2.构建SqlSessionFactory SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); //3.通过SqlSessionFactory 创建 SqlSession SqlSession sqlSession = sqlSessionFactory.openSession(); //4.通过SqlSession 获得 DAO实现类的对象 UserDAO mapper = sqlSession.getMapper(UserDAO.class);//获取UserDAO对应的实现类对象 //5.测试查询方法 User user1 = mapper.queryUserById(1); User user2 = mapper.queryUserById(2); System.out.println(user1); System.out.println(user2); } }

5.7测试二[了解]

iBatis传统操作方式

package com.wlw.test; import com.wlw.dao.UserDAO; import com.wlw.entity.User; 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 TestMyBatis { public static void main(String[] args) throws IOException { //MyBatis API //1.加载配置文件 InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml"); //2.构建SqlSessionFactory SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); //3.通过SqlSessionFactory 创建 SqlSession SqlSession sqlSession = sqlSessionFactory.openSession(); Object o = sqlSession.selectOne("com.wlw.dao.UserDAO.queryUserById", 1); User user = (User) o; System.out.println(user); /* //4.通过SqlSession 获得 DAO实现类的对象 UserDAO mapper = sqlSession.getMapper(UserDAO.class);//获取UserDAO对应的实现类对象 //5.测试查询方法 User user1 = mapper.queryUserById(1); User user2 = mapper.queryUserById(2); System.out.println(user1); System.out.println(user2); */ } }

六、细节补充

6.1解决mapper.xml存放在resources以外路径中的读取问题

xxxMapper.xml文件放在com.wlw.dao中（为了结构清晰），会出现不会被编译的问题

在pom.xml文件最后追加< build >标签，以便可以将xmI文件复制到classes中，并在程序运行时正确读取。

同时mybatis-config.xml里面的注册mapper的路径要改

这样改完运行测试，肯会出现了（Caused by: com.sun.org.apache.xerces.internal.impl.io.MalformedByteSequenceException: 2 字节的 UTF-8 序列的字节 2 无效）异常，这应该是因为改动位置之后，在编译时，UserDAOMapper.xml文件中的中文注释导致乱码异常，删除注释就好；或者重启idea，或者将文件重新保存utf-8格式

6.2 properties配置文件

对于mybatis-configxml的核心配置中，如果存在需要频繁改动的数据内容，可以提取到jdbc.properties中。

jdbc.driver=com.mysql.jdbc.Driver jdbc.url=jdbc:mysql://localhost:3306/mybatis_wlw?useUnicode=true&characterEncoding=utf-8 jdbc.username=root jdbc.password=123456

修改mybatis-config.xml

<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"> <configuration>  <properties resource="jdbc.properties"/>  <environments default="wlw_mysql_config">   <environment id="wlw_mysql_config">  <transactionManager type="jdbc"></transactionManager>  <dataSource type="org.apache.ibatis.datasource.pooled.PooledDataSourceFactory"> <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="com/wlw/dao/UserDAOMapper.xml"/> </mappers> </configuration>

6.3类型别名

为实体类定义别名，提高书写效率。

<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"> <configuration>  <properties resource="jdbc.properties"/>  <typeAliases>    <package name="com.wlw.entity"/> </typeAliases>  <environments default="wlw_mysql_config">   <environment id="wlw_mysql_config">  <transactionManager type="jdbc"></transactionManager>  <dataSource type="org.apache.ibatis.datasource.pooled.PooledDataSourceFactory"> <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="com/wlw/dao/UserDAOMapper.xml"/> </mappers> </configuration>

6.4创建log4j配置文件

pom.xml添加log4j依赖

<dependency> <groupId>log4j</groupId> <artifactId>log4j</artifactId> <version>1.2.17</version> </dependency>

创建并配置log4j.properties

# Global logging configuration log4j.rootLogger=DEBUG, stdout # MyBatis logging configuration... log4j.logger.org.mybatis.example.BlogMapper=TRACE # Console output... log4j.appender.stdout=org.apache.log4j.ConsoleAppender log4j.appender.stdout.layout=org.apache.log4j.PatternLayout log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n 级别描述ALL LEVEL打开所有日志记录开关；是最低等级的，用于打开所有日志记录。DEBUG输出调试信息；指出细粒度信息事件对调试应用程序是非常有帮助的。INFO输出提示信息；消息在粗粒度级别上突出强调应用程序的运行过程。WARN输出警告信息；表明会出现潜在错误的情形。ERROR输出错误信息；指出虽然发生错误事件，但仍然不影响系统的继续运行。FATAL输出致命错误；指出每个严重的错误事件将会导致应用程序的退出。OFF LEVEL关闭所有日志记录开关；是最高等级的，用于关闭所有日志记录。

七、MyBatis的CRUD操作【重点】

7.1查询

标签: < select id="" resultType="">

7.1.1序号参数绑定

package com.wlw.dao; import com.wlw.entity.User; import org.apache.ibatis.annotations.Param; import java.util.List; import java.util.Map; public interface UserDAO { User queryUserById(Integer id); User queryUserByIdAndUsername(Integer id, String username); }  <select id="queryUserById" resultType="User"> select id,username,password,gender,regist_time as registTime from t_user where id=#{arg0} </select> <select id="queryUserByIdAndUsername" resultType="User"> select id,username,password,gender,regist_time as registTime from t_user where id=#{param1} and username=#{param2} </select>

7.1.2注解参数绑定[推荐]

package com.wlw.dao; import com.wlw.entity.User; import org.apache.ibatis.annotations.Param; import java.util.List; import java.util.Map; public interface UserDAO { User queryUserByIdAndPassword(@Param("id") Integer id, @Param("password") String password); }  <select id="queryUserByIdAndPassword" resultType="User"> select id,username,password,gender,regist_time as registTime from t_user where id=#{id} and password=#{password} </select>

7.1.3 Map参数绑定

package com.wlw.dao; import com.wlw.entity.User; import org.apache.ibatis.annotations.Param; import java.util.List; import java.util.Map; public interface UserDAO { User queryUserByIdAndPassword2(Map map); }  <select id="queryUserByIdAndPassword2" resultType="User"> select id,username,password,gender,regist_time as registTime from t_user where id=#{id} and password=#{password}  </select> HashMap map = new HashMap();//测试类创建map map.put("id",1); //自定义key ，查询语句通过这个key来接收参数 map.put("password","123"); User user5 = mapper.queryUserByIdAndPassword2(map); System.out.println(user5); System.out.println("-----------------");

7.1.4对象参数绑定

package com.wlw.dao; import com.wlw.entity.User; import org.apache.ibatis.annotations.Param; import java.util.List; import java.util.Map; public interface UserDAO { User queryUserByIdAndPassword3(User user); }  <select id="queryUserByIdAndPassword3" resultType="User"> select id,username,password,gender,regist_time as registTime from t_user where id=#{id} and password=#{password}  </select> User user = new User(); user.setId(2); user.setPassword("1234"); User user6 = mapper.queryUserByIdAndPassword3(user); System.out.println(user6);

7.1.5模糊查询

package com.wlw.dao; import com.wlw.entity.User; import org.apache.ibatis.annotations.Param; import java.util.List; import java.util.Map; public interface UserDAO { List<User> queryUserByUsername(@Param("username") String username); }  <select id="queryUserByUsername" resultType="User"> select id,username,password,gender,regist_time as registTime from t_user where username like concat('%',#{username},'%') </select> System.out.println("---------模糊查询--------"); List<User> users = mapper.queryUserByUsername("sh"); for (User user7 : users) { System.out.println(user7); }

7.2删除

增删改是要控制事务的，不然表中的数据是不会发生改变的

事务提交：sqlSession.commit();

事务回滚：sqlSession.rollback();

标签: < delete id="" parameterType="">

<delete id="deleteUser" parameterType="int"> delete from t_user where id = #{id} </delete>

7.3修改

标签: < update id="" parameterType="">

<update id="updateUser" parameterType="User"> update t_user set username=#{username},password=#{password},gender=#{gender},regist_time=#{registTime} where id=#{id} </update>

7.4添加

标签: < insert id="" parameterType="">

<insert id="insertUser" parameterType="User"> insert into t_user values(#{id},#{username},#{password},#{gender},#{registTime}) </insert>

7.5主键回填

当新增数据时，因为id这一列是主键，可以不填，让数据库自动生成，但是在代码中我们希望看到数据库新增的这个主键，这就需要主键回填

标签: < selectKey id="" parameterType="" order=“AFTERIBEFORE”>

7.5.1通过last_insert_id()查询主键

自增的主键类型为intUserDAOMapper.xml：  <insert id="insertUser" parameterType="User">    <selectKey order="AFTER" resultType="int" keyProperty="id"> select last_insert_id() </selectKey> insert into t_user values(#{id},#{username},#{password},#{gender},#{registTime}) </insert> //新增 //id,主键自动增长 User user = new User(null,"wlw_2","123456",false,new Date()); mapper.insertUser(user); //查看回填效果 System.out.println(user); //6.事务提交 sqlSession.commit(); /*执行结果： User{id=4, username='wlw_2', password='123456', gender=false, registTime=Tue Sep 01 12:34:49 CST 2020} */

7.5.2通过uuid()查询主键

主键类型不是int,采用varcharStudentDAOMapper.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.wlw.dao.StudentDAO"> <insert id="insertStudent" parameterType="Student">   <selectKey order="BEFORE" keyProperty="id" resultType="string"> select replace(uuid(),'-','') </selectKey> insert into t_student values(#{id},#{name},#{gender}) </insert> </mapper> package com.wlw.test; import com.wlw.dao.StudentDAO; import com.wlw.dao.UserDAO; import com.wlw.entity.Student; import com.wlw.entity.User; 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.HashMap; import java.util.List; public class TestMyBatis { public static void main(String[] args) throws IOException { //MyBatis API //1.加载配置文件 InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml"); //2.构建SqlSessionFactory SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); //3.通过SqlSessionFactory 创建 SqlSession SqlSession sqlSession = sqlSessionFactory.openSession(); //4.通过SqlSession 获得 DAO实现类的对象 UserDAO mapper = sqlSession.getMapper(UserDAO.class);//获取UserDAO对应id的实现类对象 StudentDAO studentMapper = sqlSession.getMapper(StudentDAO.class); //5.测试查询等方法 //id设为null，是为了使用uuid自动生成的id Student student = new Student(null, "wlw", false); studentMapper.insertStudent(student); //查看回填效果 System.out.println(student); //6.事务提交 sqlSession.commit(); //事务回滚 //sqlSession.rollback(); } } /*执行结果 Student{id='b7b2493eec1011ea901a54e1ad756985', name='wlw', gender=false} */

八、MyBatis工具类【重点】

8.1封装工具类

Resource：用于获得读取配置文件的IO对象，耗费资源，建议通过IO一次性读取所有所需要的数据。SqlSessionFactory： SqlSession工厂类，内存占用多，耗费资源，建议每个应用只创建一个对象。SqlSession：相当于Connection, 可控制事务，应为线程私有，不被多线程共享。 SqlSession 的实例不是线程安全的，因此是不能被共享的。SqlSession每次使用完成后需要正确关闭，这个关闭操作是必须的将获得连接、关闭连接、提交事务、回滚事务、获得接口实现类等方法进行封装。 package com.wlw.util; import org.apache.ibatis.io.Resources; import org.apache.ibatis.session.SqlSession; import org.apache.ibatis.session.SqlSessionFactory; import org.apache.ibatis.session.SqlSessionFactoryBuilder; import java.io.IOException; import java.io.InputStream; /* 1.加载配置文件 2.构建SqlSessionFactory 3.通过SqlSessionFactory 创建 SqlSession 4.事务管理 5.通过SqlSession 获得 DAO实现类的对象，Mapper获取 */ public class MyBatisUtil { private static SqlSessionFactory sqlSessionFactory; //创建ThreadLocal绑定当前线程中的SqlSession对象 private static ThreadLocal<SqlSession> threadLocal = new ThreadLocal<SqlSession>(); static {//加载配置信息 try { //1.加载配置文件 InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml"); //2.构建SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); } catch (IOException e) { e.printStackTrace(); } } //SqlSession 相当于 Connection ，要保证线程唯一，全局不唯一，需要用到ThreadLoacl // 3.通过SqlSessionFactory 创建 SqlSession public static SqlSession openSession(){ SqlSession sqlSession = threadLocal.get(); //先获取线程中的，如果没有则创建 if(sqlSession == null){ sqlSession = sqlSessionFactory.openSession(); threadLocal.set(sqlSession); } return sqlSession; } //事务提交 public static void commit(){ SqlSession sqlSession = openSession();//拿到当前线程绑定的sqlSession sqlSession.commit(); closeSession(); } //事务回滚 public static void rollback(){ SqlSession sqlSession = openSession(); sqlSession.rollback(); closeSession(); } //事务释放,关闭 public static void closeSession(){ SqlSession sqlSession = threadLocal.get(); sqlSession.close(); } //5.通过SqlSession 获得 DAO实现类的对象 public static <T> T getMapper(Class<T> tClass){ SqlSession sqlSession = openSession(); return sqlSession.getMapper(tClass); } }

8.2测试工具类

调用MyBatistis中的封装方法。 package com.wlw.test; import com.wlw.dao.StudentDAO; import com.wlw.dao.UserDAO; import com.wlw.entity.Student; import com.wlw.entity.User; import com.wlw.util.MyBatisUtil; 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.HashMap; import java.util.List; public class TestMyBatis { public static void main(String[] args) throws IOException { //用工具类编写 StudentDAO StudentMapper = MyBatisUtil.getMapper(StudentDAO.class); Student student = new Student(null, "wlw_2_test", false); Integer integer = StudentMapper.insertStudent(student); System.out.println(integer); MyBatisUtil.commit(); } }

九、ORM映射【重点】

9.1 MyBatis自动ORM失效

MyBatis只能自动维护库表“列名“与"属性名“相同时的一一对应关系，二者不同时，无法自动ORM。

9.2方案一:列的别名

在SQL中使用as为查询字段添加列别名，以匹配属性名。

<select id="queryUserById" resultType="User"> select id,username,password,gender,regist_time as registTime from t_user where id=#{arg0} </select>

9.3方案二:结果映射(ResultMap- 查询结果的封装规则)

通过< resultMap id="" type="" >映射，匹配列名与属性名。

<resultMap id="user_resultMap" type="User">   <id column="id" property="id"></id> <result column="username" property="username"></result> <result column="password" property="password"></result> <result column="gender" property="gender"></result> <result column="regist_time" property="registTime"></result> </resultMap> <select id="queryUserById" resultMap="user_resultMap"> select id,username,password,gender,regist_time from t_user where id=#{arg0} </select>

十、MyBatis处理关联关系—多表连接【重点】

实体间的关系:关联关系(拥有has、属于belong)：

OneToOne：一对一关系(Passenger-- Passport)OneToMany：一对多关系(Employee – Department)ManyToMany：多对多关系(Student – Subject)

10.1 OneToOne

10.1.1单向

SQL语句：

create table t_passengers( id int primary key auto_increment, name varchar(50), sex varchar(1), birthday date )default charset =utf8; create table t_passports( id int primary key auto_increment, nationality varchar(50), expire date, passenger_id int unique, foreign key (passenger_id) references t_passengers(id) )default charset =utf8; insert into t_passengers values(null,'shine_01','f','2018-11-11'); insert into t_passengers values(null,'shine_02','m','2019-12-12'); insert into t_passports values(null,'China','2030-12-12',1); insert into t_passports values(null,'America','2035-12-12',2); select t_passengers.id,t_passengers.name,t_passengers.sex,t_passengers.birthday, t_passports.id as passId,t_passports.nationality,t_passports.expire from t_passengers join t_passports on t_passengers.id = t_passports.passenger_id where t_passengers.id=1

PassengerDAO

package com.wlw.dao; import com.wlw.entity.Passenger; import org.apache.ibatis.annotations.Param; public interface PassengerDAO { //通过旅客id，查询旅客信息及其护照信息 //这是一个关联查询（及联查询） Passenger queryPassengerById(@Param("id") Integer id); }

PassengerDAOMapper.xml

注意:指定“一方”关系时(对象) , 使用< association property="" javaType="" >

<?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.wlw.dao.PassengerDAO"> <resultMap id="passenger_passsport" type="Passenger"> <id column="id" property="id"></id> <result column="name" property="name"></result> <result column="sex" property="sex"></result> <result column="birthday" property="birthday"></result>  <association property="passport" javaType="Passport">  <id column="passId" property="id"></id> <result column="nationality" property="nationality"></result> <result column="expire" property="expire"></result> </association> </resultMap> <select id="queryPassengerById" resultMap="passenger_passsport"> select t_passengers.id,t_passengers.name,t_passengers.sex,t_passengers.birthday, t_passports.id as passId,t_passports.nationality,t_passports.expire from t_passengers join t_passports on t_passengers.id = t_passports.passenger_id where t_passengers.id=#{id} </select> </mapper>   <mappers> <mapper resource="com/wlw/dao/PassengerDAOMapper.xml"/> </mappers>v public class TestMyBatis { public static void main(String[] args) throws IOException { //测试PassengerDAO 中一对一关系sql PassengerDAO passengerMapper = MyBatisUtil.getMapper(PassengerDAO.class); Passenger passenger = passengerMapper.queryPassengerById(1); System.out.println("----------------------------"); System.out.println(passenger); System.out.println(passenger.getPassport()); } } /* 执行结果： ---------------------------- Passenger{id=1, name='shine_01', sex=false, birthday=Sun Nov 11 00:00:00 CST 2018} Passport{id=1, nationality='China', expire=Thu Dec 12 00:00:00 CST 2030} */

10.1.2双向

在一对一的双向关系中，实现方式就是在双方的xxMapper.xml中都要定义相关映射关系

以下是护照的Mapper文件（PassportDAOMapper.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.wlw.dao.PassportDAO"> <resultMap id="passport_passenger" type="Passport"> <id column="id" property="id"/> <result column="nationality" property="nationality"/> <result column="expire" property="expire"/> <association property="passenger" javaType="Passenger"> <id column="passenger_id" property="id"/> <result column="name" property="name"/> <result column="sex" property="sex"/> <result column="birthday" property="birthday"/> </association> </resultMap> <select id="queryPassportById" resultMap="passport_passenger"> select t_passports.id,t_passports.nationality,t_passports.expire, t_passengers.id as passenger_id,t_passengers.name,t_passengers.sex,t_passengers.birthday from t_passports join t_passengers on t_passengers.id = t_passports.passenger_id where t_passports.id=#{id} </select> </mapper>

PassportDAO

package com.wlw.dao; import com.wlw.entity.Passport; import org.apache.ibatis.annotations.Param; public interface PassportDAO { Passport queryPassportById(@Param("id") Integer id); }

10.2 OneToMany

在One的那一方要用List< T>来定义Many的一方，如下图：

SQL语句

create table t_departments( id int primary key auto_increment, name varchar(50), location varchar(100) )default charset =utf8; create table t_employees( id int primary key auto_increment, name varchar(50), salary double, dept_id int, foreign key (dept_id) references t_departments(id) )default charset =utf8; insert into t_departments values(1,'教学部','北京'),(2,'研发部','上海'); insert into t_employees values(1,'shine01',10000.5,1),(2,'shine02',20000.5,1), (3,'张三',9000.5,2),(4,'李四',8000.5,2);

DepartmentDAOMapper.xml

注意:指定“多方"关系时(集合)，使用< collectionon property=”“ ofType="" >

<?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.wlw.dao.DepartmentDAO"> <resultMap id="dept_emp" type="Department"> <id column="id" property="id"/> <result column="name" property="name"/> <result column="location" property="location"/>  <collection property="employees" ofType="Employee"> <id column="emp_id" property="id"/> <result column="emp_name" property="name"/> <result column="salary" property="salary"/> </collection> </resultMap> <select id="queryDepartmentById" resultMap="dept_emp">  select t_departments.id,t_departments.name,t_departments.location, t_employees.id as emp_id,t_employees.name as emp_name,t_employees.salary from t_departments join t_employees on t_departments.id = t_employees.dept_id where t_departments.id = #{id} </select> </mapper>

EmployeeDAOMapper.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.wlw.dao.EmployeeDAO"> <resultMap id="emp_dept" type="Employee"> <id column="id" property="id"/> <result column="name" property="name"/> <result column="salary" property="salary"/>  <association property="department" javaType="Department"> <id column="deptId" property="id"/> <result column="deptName" property="name"/> <result column="location" property="location"/> </association> </resultMap> <select id="queryEmployeeById" resultMap="emp_dept">  select t_employees.id,t_employees.name,t_employees.salary, t_departments.id as deptId,t_departments.name as deptName,t_departments.location from t_employees join t_departments on t_employees.dept_id = t_departments.id where t_employees.id = #{id} </select> </mapper>

10.3 ManyToMany

SQL语句：

create table t_students( id int primary key auto_increment, name varchar(50), sex varchar(1) )default charset =utf8; create table t_subjects( id int primary key auto_increment, name varchar(50), grade int )default charset =utf8; create table t_stu_sub( student_id int, subject_id int, foreign key (student_id) references t_students(id), foreign key (subject_id) references t_subjects(id), primary key (student_id,subject_id) )default charset =utf8; insert into t_students values (1,'shine','f'),(2,'wlw','m'); insert into t_subjects values (1001,'JavaSE',1),(1002,'JavaWeb',2); insert into t_stu_sub values (1,1001),(1,1002),(2,1001),(2,1002);

SubjectDAOMapper.xml

注意:指定“多方"关系时(集合)，使用< collection property=”“ ofType="">

<?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.wlw.dao.SubjectDAO"> <resultMap id="sub_stu" type="Subject"> <id column="id" property="id"/> <result column="name" property="name"/> <result column="grade" property="grade"/>  <collection property="students" ofType="Student2"> <id column="stuId" property="id"/> <result column="stuName" property="name"/> <result column="sex" property="sex"/> </collection> </resultMap> <select id="querySubjectById" resultMap="sub_stu"> select t_subjects.id,t_subjects.name,t_subjects.grade, t_students.id as stuId,t_students.name as stuName,t_students.sex from t_subjects join t_stu_sub on t_subjects.id = t_stu_sub.subject_id join t_students on t_stu_sub.student_id = t_students.id where t_subjects.id = #{id}; </select> </mapper>

Student2DAOMapper.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.wlw.dao.Student2DAO"> <resultMap id="stu_sub" type="Student2"> <id column="id" property="id"/> <result column="name" property="name"/> <result column="sex" property="sex"/>  <collection property="subjects" ofType="Subject"> <id column="subId" property="id"/> <result column="subName" property="name"/> <result column="grade" property="grade"/> </collection> </resultMap> <select id="queryStudent2ById" resultMap="stu_sub"> select t_students.id,t_students.name,t_students.sex, t_subjects.id as subId,t_subjects.name subName,t_subjects.grade from t_students join t_stu_sub on t_students.id = t_stu_sub.student_id join t_subjects on t_stu_sub.subject_id = t_subjects.id where t_students.id = #{id} </select> </mapper>

mybatis-config.xml

<mappers> <mapper resource="com/wlw/dao/SubjectDAOMapper.xml"/> <mapper resource="com/wlw/dao/Student2DAOMapper.xml"/> </mappers>

TestMyBatis

public class TestMyBatis { public static void main(String[] args) throws IOException { //查询多对多一个课程对应的选择这门课程的学生 /*SubjectDAO subjectMapper = MyBatisUtil.getMapper(SubjectDAO.class); Subject subject = subjectMapper.querySubjectById(1001); System.out.println(subject); List<Student2> students = subject.getStudents(); for (Student2 student : students) { System.out.println(student); }*/ //查询一个学生及这个学生选择的课程 Student2DAO student2Mapper = MyBatisUtil.getMapper(Student2DAO.class); Student2 student2 = student2Mapper.queryStudent2ById(1); System.out.println(student2); List<Subject> subjects = student2.getSubjects(); for (Subject subject : subjects) { System.out.println(subject); } } }

10.4关系总结

一方，添加集合；多方，添加对象。双方均可建立关系属性，建立关系属性后，对应的Mapper文件中需使用< ResultMap >完成多表映射。持有对象关系属性，使用< association property=“dept” javaType=“department” >持有集合关系属性，使用< collection property=“emps” ofType=“employee” >

十一、动态SQL 【重点】

MyBatis的映射文件中支持在基础SQL上添加一些逻辑操作，并动态拼接成完整的SQL之后再执行，以达到SQL复用、简化编程的效果。

11.1< sql>

就是将sql语句的共同部分提取出来，方便引用与修改 <?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.wlw.dao.UserDAO">  <sql id="user_field"> select id,username,password,gender,regist_time as registTime from t_user </sql> <select id="queryUsers" resultType="User">  <include refid="user_field"></include> </select> <select id="queryUserById" resultType="User"> <include refid="user_field"></include> where id = #{id} </select> <select id="queryUserByUsername" resultType="User"> <include refid="user_field"></include> where username = #{username} </select> </mapper>

11.2 < where>

判断查询条件，有哪个用哪个,可用来合并几个近似的sql语句where标签：1.补充where关键字，2.识别where子句中如果以or，and开头，会将or，and去除 <?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.wlw.dao.UserDAO">  <sql id="user_field"> select id,username,password,gender,regist_time as registTime from t_user </sql>   <select id="queryUser" resultType="User"> <include refid="user_field"></include> where <if test="id != null"> id = #{id} </if> <if test="username != null"> username = #{username} </if> </select>  <select id="queryUser2" resultType="User"> <include refid="user_field"></include>  <where> <if test="username != null"> username = #{username} </if> <if test="gender != null"> or gender = #{gender} </if> </where> </select> </mapper>

11.3< set>

主要针对更新set标签：1.补充set ， 2. 自动将set子句的最后的逗号去除 <?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.wlw.dao.UserDAO"> <update id="updateUser" parameterType="User"> update t_user  <set> <if test="username != null"> username=#{username}, </if> <if test="password != null"> password=#{password}, </if> <if test="gender != null"> gender=#{gender}, </if> <if test="registTime != null"> regist_time=#{registTime} </if> </set> where id = #{id} </update> </mapper>

11.4< trim>

< trim prefix=“前缀” suffix=“后缀” prefixOverrides=“去除多余的关键字” suffixOverrides="" > 代替< where>、< set>

11.5< foreach>

批量操作 <?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.wlw.dao.UserDAO"> <delete id="deleteManyUser" parameterType="java.util.List">  delete from t_user where id in <foreach collection="list" open="(" close=")" separator="," item="id9"> #{id9} </foreach> </delete> <insert id="insertManyUser" parameterType="java.util.List">  insert into t_user values <foreach collection="list" item="user9" separator=","> (null,#{user9.username},#{user9.password},#{user9.gender},#{user9.registTime}) </foreach> </insert> </mapper> 参数描述取值collection容器类型list、 array、 mapopen起始符(close结束符）separator分隔符，index下标号从0开始，依次递增item当前项任意名称(循环中通过#任意名称}表达式访问)

总：

<?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.wlw.dao.UserDAO"> <sql id="user_field"> select id,username,password,gender,regist_time as registTime from t_user </sql> <select id="queryUsers" resultType="User"> <include refid="user_field"></include> </select>   <select id="queryUser" resultType="User"> <include refid="user_field"></include> where <if test="id != null"> id = #{id} </if> <if test="username != null"> username = #{username} </if> </select>  <select id="queryUser2" resultType="User"> <include refid="user_field"></include>  <trim prefix="where" prefixOverrides="or|and"> <if test="username != null"> username = #{username} </if> <if test="gender != null"> or gender = #{gender} </if> </trim> </select> <delete id="deleteUser" parameterType="int"> delete from t_user where id = #{id} </delete> <update id="updateUser" parameterType="User"> update t_user  <trim prefix="set" suffixOverrides=","> <if test="username != null"> username=#{username}, </if> <if test="password != null"> password=#{password}, </if> <if test="gender != null"> gender=#{gender}, </if> <if test="registTime != null"> regist_time=#{registTime} </if> </trim> where id = #{id} </update> <insert id="insertUser" parameterType="User"> <selectKey order="AFTER" resultType="int" keyProperty="id"> select last_insert_id() </selectKey> insert into t_user values (#{id},#{username},#{password},#{gender},#{registTime}); </insert> <delete id="deleteManyUser" parameterType="java.util.List">  delete from t_user where id in <foreach collection="list" open="(" close=")" separator="," item="id9"> #{id9} </foreach> </delete> <insert id="insertManyUser" parameterType="java.util.List">  insert into t_user values <foreach collection="list" item="user9" separator=","> (null,#{user9.username},#{user9.password},#{user9.gender},#{user9.registTime}) </foreach> </insert> </mapper> package com.wlw.test; import com.wlw.dao.UserDAO; import com.wlw.entity.User; import com.wlw.util.MyBatisUtil; import org.junit.Test; import java.util.ArrayList; import java.util.Arrays; import java.util.Date; import java.util.List; public class TestMyBatis { @Test public void test1(){ UserDAO userMapper = MyBatisUtil.getMapper(UserDAO.class); /* User user = userMapper.queryUserById(1); System.out.println(user);*/ System.out.println("----------------------"); List<User> users = userMapper.queryUsers(); for (User user1 : users) { System.out.println(user1); } } @Test public void test2(){ UserDAO userMapper = MyBatisUtil.getMapper(UserDAO.class); User user = new User(); //user.setId(3); user.setUsername("wlw_2"); User user1 = userMapper.queryUser(user); System.out.println(user1); } @Test public void test3(){ UserDAO userMapper = MyBatisUtil.getMapper(UserDAO.class); User user = new User(); user.setUsername("wlw"); user.setGender(false); List<User> users = userMapper.queryUser2(user); for (User user1 : users) { System.out.println(user1); } } @Test public void test4(){ UserDAO userMapper = MyBatisUtil.getMapper(UserDAO.class); User user = new User(1,"shine","1234578",true,null); Integer integer = userMapper.updateUser(user); System.out.println(integer); MyBatisUtil.commit(); } @Test public void test5(){ UserDAO userMapper = MyBatisUtil.getMapper(UserDAO.class); List<Integer> ids = Arrays.asList(1, 3, 4); userMapper.deleteManyUser(ids); MyBatisUtil.commit(); } @Test public void test6(){ UserDAO userMapper = MyBatisUtil.getMapper(UserDAO.class); List<User> users = Arrays.asList(new User(null, "shine", "12396", true, new Date()), new User(null, "wlw", "789456", true, new Date())); userMapper.insertManyUser(users); MyBatisUtil.commit(); } }

十二、缓存(Cache) 【重点】

内存中的一块存储空间，服务于某个应用程序，旨在将频繁读取的数据临时保存在内存中，便于二次快速访问。

12.1 一级缓存

SqlSession级别的缓存，同一个SqlSession的发起多次同构查询，会将数据保存在一级缓存中。

注意:无需任何配置，默认开启一级缓存。

@Test public void test7(){ //查看一级缓存 SqlSession sqlSession = MyBatisUtil.openSession(); UserDAO userMapper = sqlSession.getMapper(UserDAO.class); List<User> users = userMapper.queryUsers(); System.out.println("============================="); List<User> user2 = userMapper.queryUsers(); //上面的输出结果只有一次sql查询 System.out.println("============================="); SqlSession sqlSession2 = MyBatisUtil.getSession(); //另外一个SqlSession UserDAO userMapper2 = sqlSession2.getMapper(UserDAO.class); List<User> users1 = userMapper2.queryUsers(); //这次会再次出现一次查询 }

12.2二级缓存

SqlSessionFactory级别的缓存，同一个SqlSessionFactory构建的SqlSession发起的多次同构查询，会将数据保存在二级缓存中。（全局缓存）注意: 在sqlSession.commit()或者sqlSession.close()之后生效。

12.2.1开启全局缓存

< setings >是MyBatis中极为重要的调整设置，他们会改变MyBatis的运行行为，其他详细配置可参考官方文档。mybatis-config.xml： <?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"> <configuration>   <properties resource="jdbc.properties"/>   <settings> <setting name="cacheEnabled" value="true"/> </settings>  <typeAliases>  <package name="com.wlw.entity"/> </typeAliases>  <environments default="wlw_mysql_config"> ... </environments>  <mappers> ... </mappers> </configuration>

12.2.2指定Mapper缓存

在相应的xxxMapper.xml文件中：（UserDAOMapper.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.wlw.dao.UserDAO">  <cache/> <sql id="user_field"> select id,username,password,gender,regist_time as registTime from t_user </sql> <select id="queryUsers" resultType="User"> <include refid="user_field"></include> </select>   <select id="queryUser" resultType="User"> <include refid="user_field"></include> where <if test="id != null"> id = #{id} </if> <if test="username != null"> username = #{username} </if> </select>  <select id="queryUser2" resultType="User"> <include refid="user_field"></include>  <trim prefix="where" prefixOverrides="or|and"> <if test="username != null"> username = #{username} </if> <if test="gender != null"> or gender = #{gender} </if> </trim> </select> <delete id="deleteUser" parameterType="int"> delete from t_user where id = #{id} </delete> <update id="updateUser" parameterType="User"> update t_user  <trim prefix="set" suffixOverrides=","> <if test="username != null"> username=#{username}, </if> <if test="password != null"> password=#{password}, </if> <if test="gender != null"> gender=#{gender}, </if> <if test="registTime != null"> regist_time=#{registTime} </if> </trim> where id = #{id} </update> <insert id="insertUser" parameterType="User"> <selectKey order="AFTER" resultType="int" keyProperty="id"> select last_insert_id() </selectKey> insert into t_user values (#{id},#{username},#{password},#{gender},#{registTime}); </insert> <delete id="deleteManyUser" parameterType="java.util.List">  delete from t_user where id in <foreach collection="list" open="(" close=")" separator="," item="id9"> #{id9} </foreach> </delete> <insert id="insertManyUser" parameterType="java.util.List">  insert into t_user values <foreach collection="list" item="user9" separator=","> (null,#{user9.username},#{user9.password},#{user9.gender},#{user9.registTime}) </foreach> </insert> </mapper> 测试 TestMyBatis： @Test public void test8(){ //查看二级缓存 //通过相同的SqlSessionFactory获取多个sqlsession SqlSession sqlsession1 = MyBatisUtil.getSession(); SqlSession sqlsession2 = MyBatisUtil.getSession(); SqlSession sqlsession3 = MyBatisUtil.getSession(); UserDAO mapper1 = sqlsession1.getMapper(UserDAO.class); UserDAO mapper2 = sqlsession2.getMapper(UserDAO.class); UserDAO mapper3 = sqlsession3.getMapper(UserDAO.class); List<User> users1 = mapper1.queryUsers(); sqlsession1.close();//只有当这个sqlsession关闭之后，mybatis才会把所查到的数据放到二级缓存中 System.out.println("======================"); List<User> users2 = mapper2.queryUsers(); sqlsession2.close(); System.out.println("======================"); List<User> users3 = mapper3.queryUsers(); sqlsession3.close(); } /* DEBUG [main] - Created connection 673186785. DEBUG [main] - Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@282003e1] DEBUG [main] - ==> Preparing: select id,username,password,gender,regist_time as registTime from t_user DEBUG [main] - ==> Parameters: DEBUG [main] - <== Total: 5 DEBUG [main] - Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@282003e1] DEBUG [main] - Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@282003e1] DEBUG [main] - Returned connection 673186785 to pool. ====================== DEBUG [main] - Cache Hit Ratio [com.wlw.dao.UserDAO]: 0.5 （命中率） ====================== DEBUG [main] - Cache Hit Ratio [com.wlw.dao.UserDAO]: 0.6666666666666666 */ 注意点：所有的缓存都是临时数据，具体数据还是要以数据中的数据为准，所有当数据库中的数据发生增删改之后，缓存中的相关数据也就成了脏数据，但Mybatis会帮我们删除这些脏数据

12.2.3缓存清空并重新缓存

@Test public void test8(){ //查看二级缓存 //通过相同的SqlSessionFactory获取多个sqlsession SqlSession sqlsession1 = MyBatisUtil.getSession(); SqlSession sqlsession2 = MyBatisUtil.getSession(); SqlSession sqlsession3 = MyBatisUtil.getSession(); UserDAO mapper1 = sqlsession1.getMapper(UserDAO.class); UserDAO mapper2 = sqlsession2.getMapper(UserDAO.class); UserDAO mapper3 = sqlsession3.getMapper(UserDAO.class); List<User> users1 = mapper1.queryUsers(); sqlsession1.close();//只有当这个sqlsession关闭之后，mybatis才会把所查到的数据放到二级缓存中 //修改，修改之后，相关的缓存就被清除 SqlSession sqlsession4 = MyBatisUtil.getSession(); UserDAO mapper4 = sqlsession4.getMapper(UserDAO.class); mapper4.deleteUser(5); sqlsession4.commit(); //数据修改成功，相关的缓存被清除 sqlsession4.close(); System.out.println("======================"); List<User> users2 = mapper2.queryUsers(); sqlsession2.close(); System.out.println("======================"); List<User> users3 = mapper3.queryUsers(); sqlsession3.close(); } /* DEBUG [main] - Created connection 673186785. DEBUG [main] - Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@282003e1] DEBUG [main] - ==> Preparing: select id,username,password,gender,regist_time as registTime from t_user DEBUG [main] - ==> Parameters: DEBUG [main] - <== Total: 5 DEBUG [main] - Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@282003e1] DEBUG [main] - Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@282003e1] DEBUG [main] - Returned connection 673186785 to pool. DEBUG [main] - Opening JDBC Connection DEBUG [main] - Checked out connection 673186785 from pool. DEBUG [main] - Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@282003e1] DEBUG [main] - ==> Preparing: delete from t_user where id = ? DEBUG [main] - ==> Parameters: 5(Integer) DEBUG [main] - <== Updates: 1 DEBUG [main] - Committing JDBC Connection [com.mysql.jdbc.JDBC4Connection@282003e1] DEBUG [main] - Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@282003e1] DEBUG [main] - Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@282003e1] DEBUG [main] - Returned connection 673186785 to pool. ====================== DEBUG [main] - Cache Hit Ratio [com.wlw.dao.UserDAO]: 0.0 DEBUG [main] - Opening JDBC Connection DEBUG [main] - Checked out connection 673186785 from pool. DEBUG [main] - Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@282003e1] DEBUG [main] - ==> Preparing: select id,username,password,gender,regist_time as registTime from t_user DEBUG [main] - ==> Parameters: DEBUG [main] - <== Total: 4 DEBUG [main] - Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@282003e1] DEBUG [main] - Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@282003e1] DEBUG [main] - Returned connection 673186785 to pool. ====================== DEBUG [main] - Cache Hit Ratio [com.wlw.dao.UserDAO]: 0.3333333333333333 Process finished with exit code 0 */

十三、Druid连接池

13.1概念

Druid是阿里巴巴开源平台上的一个项目，整个项目由数据库连接池、插件框架和SQL解析器组成。该项目主要是为了扩展JDBC的一些限制，可以让程序员实现一些特殊的需求，比如向密钥服务请求凭证、统计SQL信息、SQL 性能收集、SQL 注入检查、SQL翻译等，程序员可以通过定制来实现自己需要的功能。

13.2不同连接池对比

测试执行申请归还连接100.0000 (一百万)次总耗时性能对比。

13.2.1测试环境

环境版本OSOSX 10.8.2CPUIntel i7 2GHz4 CoreJVMJava Version 1.7.0 05

13.2.2基准测试结果对比

JDBC Conn Pool1 Thread2 threads5 threads10 threads20 threads50 threadsDruid8981,1911,3241,3621,3251,459tomcat. jdbc1,2691,3782,0292,1031,8792,025DBCP2,3245,0555,4465,4715,5245,415BoneCP3,7383,1503,1945,68111,01823,125jboss-datasource4,3772,9883,6803,98032,70837,742C3P010,84113,63710,68211,05514,49720,351Proxool16,33716,18718,310(Ex)25,94533,706(Ex)39,501 (Ex)

13.2.3测试结论

Druid是性能最好的数据库连接池，tomcat-jdbc和druid性能接近。Proxool 在激烈并发时会抛异常，不适用。C3P0 和Proxool都相当慢，影响sql执行效率。BoneCP 性能并不优越，采用LinkedTransferQueue并没有能够获得性能提升。除了bonecp, 其他的在JDK7. 上跑得比JDK6上快。jboss-datasource虽然稳定，但性能很糟糕。

13.3配置pom.xml

引入Druid依赖

<dependencies>  <dependency> <groupId>com.alibaba</groupId> <artifactId>druid</artifactId> <version>1.1.16</version> </dependency> </dependencies>

13.4创建DruidDataSourceFactory

MyDruidDataSourceFactory并继承PooledDataSourceFactory,并替换数据源。

package com.wlw.datasource; import com.alibaba.druid.pool.DruidDataSource; import org.apache.ibatis.datasource.pooled.PooledDataSourceFactory; import javax.sql.DataSource; /** * 连接池工厂 */ public class MyDruidDataSourceFactory extends PooledDataSourceFactory { public MyDruidDataSourceFactory() { this.dataSource = new DruidDataSource();//替换数据源 } }

13.5修改mybatis-config.xml

mybatis-config.xml中连接池相关配置。

注意: < property name=“属性名” />属性名必须与com.alibaba.druid.pool.DruidAbstractDataSource中一致。

十四、PageHelper

14.1概念

PageHelper是适用于MyBatis框架的一个分页插件，使用方式极为便捷，支持任何复杂的单表、多表分页查询操作。

14.2访问与下载

官方网站: https://pagehelper.github.io/下载地址: https://github.com/pagehelper/Mybatis-PageHelper

14.3开发步骤

PageHelper中提供了多个分页操作的静态方法入口。

14.3.1引入依赖

pom.xml中引入PageHelper依赖。

<dependencies> <dependency> <groupId>com.github.pagehelper</groupId> <artifactId>pagehelper</artifactId> <version>5.1.10</version> </dependency> </dependencies>

14.3.2配置MyBatis- confg.xml

在MyBatis-config.xml中添加< plugins>。

<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"> <configuration>   <properties resource="jdbc.properties"/>   <settings> <setting name="cacheEnabled" value="true"/> </settings>  <typeAliases>    <package name="com.wlw.entity"/> </typeAliases> <plugins>  <plugin interceptor="com.github.pagehelper.PageInterceptor"></plugin> </plugins>  <environments default="wlw_mysql_config">   <environment id="wlw_mysql_config">  <transactionManager type="jdbc"></transactionManager>  <dataSource type="com.wlw.datasource.MyDruidDataSourceFactory"> <property name="driverClass" value="${jdbc.driver}"/>  <property name="jdbcUrl" value="${jdbc.url}"/> <property name="username" value="${jdbc.username}"/> <property name="password" value="${jdbc.password}"/> </dataSource> </environment> </environments>  <mappers> <mapper resource="com/wlw/dao/UserDAOMapper.xml"/> </mappers> </configuration>

14.3.3 PageHelper应用方式

使用PageHelper提供的静态方法设置分页查询条件。

@Test public void testPage(){ UserDAO mapper = MyBatisUtil.getMapper(UserDAO.class); //在查询前，设置分页查询第一页，每页2条数据 // PageHelper 对其之后的第一个查询，进行分页功能追加 PageHelper.startPage(2, 2); List<User> users = mapper.queryUsers(); for (User user : users) { System.out.println(user); } // 将查询结果封装到 PageInfo对象中 PageInfo<User> pageInfo = new PageInfo(users); System.out.println("=================================="); }

14.4 PageInfo对象

PageInfo对象中包含了分页操作中的所有相关数据。

14.4.1 PageInfo应用方式

使用Pagelnfo保存分页查询结果。

14.4.2注意事项

只有在PageHelper.startPage()方法之后的第一个查询会有执行分页。

分页插件不支持带有"for update"的查询语句。

分页插件不支持**“嵌套查询”**，由于嵌套结果方式会导致结果集被折叠，所以无法保证分页结果数量正确。

14.4.3分页练习

使用Servlet+JSP+MyBatis+分页插件，完成分页查询功能。项目为： D:\Program Files\IDEAworkspace\projects\JavaWeb_high\mybatis_page

十五、补充[了解]

以下内容并非必备知识，了解即可。

15.1 MyBatis注解操作

通过在接口中直接添加MyBatis注解，完成CRUD。(不使用xxxMapper.xml文件)

注意:接口注解定义完毕后，需将接口全限定名注册到mybatis-config.xml的< mappers >中。

经验:注解模式属于硬编码到.java文件中，失去了使用配置文件外部修改的优势，可结合需求选用。

package com.wlw.dao; import com.wlw.entity.User; import org.apache.ibatis.annotations.*; import java.util.List; public interface UserDAO { @Select(" select id,username,password,gender,regist_time from t_user") List<User> queryUsers(); }  <mappers>  <mapper class="com.wlw.dao.UserDAO"/>  </mappers>

15.1.1查询

package com.wlw.dao; import com.wlw.entity.User; import org.apache.ibatis.annotations.*; import java.util.List; public interface UserDAO { @Select(" select id,username,password,gender,regist_time from t_user") List<User> queryUsers(); @Select("select id,username,password,gender,regist_time as registTime from t_user where id=#{id}") User queryUserById(@Param("id") Integer id); }

15.1.2删除

package com.wlw.dao; import com.wlw.entity.User; import org.apache.ibatis.annotations.*; import java.util.List; public interface UserDAO { @Delete("delete from t_user where id = #{id}") Integer deleteUser(@Param("id") Integer id); }

15.1.3修改

package com.wlw.dao; import com.wlw.entity.User; import org.apache.ibatis.annotations.*; import java.util.List; public interface UserDAO { @Update("update t_user set username=#{username}, password=#{password},\n " + " gender=#{gender}, regist_time=#{registTime} where id = #{id}") Integer updateUser(User user); }

15.1.4插入

package com.wlw.dao; import com.wlw.entity.User; import org.apache.ibatis.annotations.*; import java.util.List; public interface UserDAO { @Options(useGeneratedKeys = true , keyProperty = "id") // 自增key，主键为id,主键回填 @Insert("insert into t_user values(#{id},#{username},#{password},#{gender},#{registTime})") Integer insertUser(User user); }

15.2 $符号的应用场景

${attribute}属于字符串拼接SQL，而非预编译占位符，会有注入攻击问题，不建议在常规SQL中使用，常用于可解决动态生降序问题。${} 与#{} 表达式的区别：

{} 表示的是当前方法参数的属性，如果方法的参数是零散的，要想使用

{}，是必须要加@Parm()注解的如果参数是String类型，

{}需要用单引号，如select * from t_user where username = '

{username}’;两者生成的sql语句，${} 是把参数直接拼接在sql语句中，在控制台可以看到完整的参数；而#{}是用占位符（？）来表示参数 ${} , #{}使用原则：能用#{} 尽量用#{}，如果这个位置并不是为某个列的值做一些相关操作，仅仅只是在某些sql片段上动态填充（比如添加降序desc/升序asc）,就要用${}

15.2.1 $符号参数绑定

package com.wlw.dao; import com.wlw.entity.User; import org.apache.ibatis.annotations.*; import java.util.List; public interface UserDAO { List<User> queryUsers(@Param("rule") String rule);//排序查询 User queryUserById(@Param("id") Integer id);// #{arg0} ${arg0} User queryUserById2(@Param("id") Integer id);// #{arg0} ${arg0} List<User> queryUserByUsername(@Param("username") String username); List<User> queryUserByUsernameorId(User user);// ${username} ${id} } <?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.wlw.dao.UserDAO">  <select id="queryUsers" resultType="User"> select id,username,password,gender,regist_time from t_user order by id ${rule}  </select> <select id="queryUserById" resultType="User"> select id,username,password,gender,regist_time from t_user where id=#{id} </select> <select id="queryUserById2" resultType="User"> select id,username,password,gender,regist_time from t_user where id=${id} </select> <select id="queryUserByUsername" resultType="User"> select id,username,password,gender,regist_time from t_user where username='${username}' </select>  <select id="queryUserByUsernameorId" resultType="User"> select id,username,password,gender,regist_time from t_user where username='${username}' or id=${id}  </select> </mapper>

15.2.2 $符号注入攻击

package com.wlw.test; import com.wlw.dao.UserDAO; import com.wlw.entity.User; import com.wlw.util.MyBatisUtil; import org.junit.Test; import java.sql.*; import java.util.Date; import java.util.List; public class MyBatisTest { @Test public void test1(){ UserDAO userMapper = MyBatisUtil.getMapper(UserDAO.class); /* List<User> users = userMapper.queryUsers(); for (User user : users) { System.out.println(user); } System.out.println("======================"); User user = userMapper.queryUserById(6); System.out.println(user);*/ /*userMapper.deleteUser(6); userMapper.insertUser(new User(null,"new_user","123456",true,new Date())); userMapper.updateUser(new User(7,"update_user","1111",false,new Date())); MyBatisUtil.commit();*/ } @Test public void test1_1(){ UserDAO userMapper = MyBatisUtil.getMapper(UserDAO.class); /*User user = userMapper.queryUserById(7); System.out.println(user);*/ User user1 = new User(); user1.setUsername("wlw"); user1.setId(10); List<User> users = userMapper.queryUserByUsernameorId(user1); for (User user2 : users) { System.out.println(user2); } } /** #{} * 1.占位符：优势：规避sql注入风险 * 2.劣势：要和列相关位置才可以使用,不能实现升序或降序查询 * 原则：填充的数据，要和列相关 * select * from t_user where id=? * insert into t_user values(?,?,?) * update t_user set username=?,password=? * @throws ClassNotFoundException * @throws SQLException */ @Test public void test2() throws ClassNotFoundException, SQLException { Class.forName("com.mysql.jdbc.Driver"); Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/mybatis_wlw?useUnicode=true&characterEncoding=utf-8","root","123456"); //要填充的数据 String username = "wlw' or '1'='1"; String rule = "desc"; String sql = "select * from t_user where username=?"; String sql2 = "select * from t_user order by id ?"; PreparedStatement preparedStatement = connection.prepareStatement(sql2); // 在占位符上填充desc preparedStatement.setString(1,rule); ResultSet resultSet = preparedStatement.executeQuery(); //会报异常，因为占位符填充的数据要和列相关 while(resultSet.next()){ System.out.println(resultSet.getInt("id")); System.out.println(resultSet.getString("username")); } } /** ${} * 1. 劣势：有sql注入风险 * 2. 优势：可以随意拼接，可以实现升序或降序查询 * @throws ClassNotFoundException * @throws SQLException */ @Test public void test3() throws ClassNotFoundException, SQLException { Class.forName("com.mysql.jdbc.Driver"); Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/mybatis_wlw?useUnicode=true&characterEncoding=utf-8","root","123456"); //要填充的数据 String username = "wa' or '1'='1"; String rule = "desc"; //sq拼接填充数据 select * from t_user where username='wa' //sq拼接填充数据 select * from t_user where username='wa' or '1'='1' // 当拼接sql片段，有sql注入风险，外界参数改变原有sql的语义 String sql = "select * from t_user where username='"+username+"'"; String sql2 = "select * from t_user order by id "+rule; Statement statement = connection.createStatement(); ResultSet resultSet = statement.executeQuery(sql2); while(resultSet.next()){ System.out.println(resultSet.getInt("id")); System.out.println(resultSet.getString("username")); } } @Test public void test5(){ UserDAO mapper = MyBatisUtil.getMapper(UserDAO.class); List<User> users = mapper.queryUserByUsername("shine_xxxx' or '1'='1"); for (User user : users) { System.out.println(user); } } @Test public void test6(){ UserDAO mapper = MyBatisUtil.getMapper(UserDAO.class); Integer sig=0; // 0 desc 1 asc if(sig==0){ mapper.queryUsers("desc");//${}可以实现排序 #{}不可以实现排序 }else { mapper.queryUsers("asc"); } } }

15.3 MyBatis处理关联关系嵌套查询[了解]

思路:查询部门信息时，及联查询所属的员工信息。

DepartmentDao接口中定义queryDepartmentById, 并实现Mapper。EmployeeDao接口中定义queryEmployeeByDeptId, 并实现Mapper,当queryDepartmentById被执行时，通过< collection >调用queryEmployeeByDeptId方法，并传入条件参数。

15.3.1主表查询

定义queryEmployeeByDeptId,并书写Mapper,实现根据部门ID查询员工信息

package com.wlw.dao; import com.wlw.entity.Employee; import org.apache.ibatis.annotations.Param; import java.util.List; public interface EmployeeDAO { // 查询员工信息并且查到对应的部门信息 Employee queryEmployeeById(@Param("id") Integer id); // 查询某个部门下的所有员工 List<Employee> queryEmployeeByDeptId(@Param("deptId") Integer deptId); } <?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.wlw.dao.EmployeeDAO"> <resultMap id="emp_dept" type="Employee"> <id column="id" property="id"></id> <result column="name" property="name"></result> <result column="salary" property="salary"></result>  <association property="department" javaType="Department" select="com.wlw.dao.DepartmentDAO.queryDepartmentById" column="dept_id"/> </resultMap> <select id="queryEmployeeById" resultMap="emp_dept"> select id,name,salary,dept_id from t_employees where id=#{id}  </select> <select id="queryEmployeeByDeptId" resultType="Employee"> select id,name,salary from t_employees where dept_id=#{deptId} </select> </mapper>

15.3.2及联调用

定义queryDepartmentById,井书写Mapper,实现根据部门ID查询部门信息，井及联查询该部门员工信息

package com.wlw.dao; import com.wlw.entity.Department; import org.apache.ibatis.annotations.Param; public interface DepartmentDAO { // 查询部门，及其所有员工信息 Department queryDepartmentById(@Param("id") Integer id); } <?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.qf.dao.DepartmentDAO"> <resultMap id="dept_emp" type="Department"> <id column="id" property="id"></id> <result column="name" property="name"></result> <result column="location" property="location"></result>    <collection property="employees" ofType="Employee" select="com.wlw.dao.EmployeeDAO.queryEmployeeByDeptId" column="id"> </collection> </resultMap> <select id="queryDepartmentById" resultMap="dept_emp"> select id ,name,location from t_departments where id=#{id}  </select> </mapper>  <mappers> <mapper resource="com/wlw/dao/DepartmentDAOMapper.xml"/> <mapper resource="com/wlw/dao/EmployeeDAOMapper.xml"/> </mappers>

15.3.3延迟加载

mybatis-config.xml中开启延迟加载

<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"> <configuration>  <settings>  <setting name="cacheEnabled" value="true"/>  <setting name="lazyLoadingEnabled" value="true"/>  </settings> </configuration> package com.wlw.test; import com.wlw.dao.DepartmentDAO; import com.wlw.dao.EmployeeDAO; import com.wlw.entity.Department; import com.wlw.entity.Employee; import com.wlw.entity.User; import com.wlw.util.MyBatisUtil; import org.junit.Test; import java.io.IOException; import java.util.List; public class MyBatisTest2 { @Test public void test1(){ /*DepartmentDAO mapper = MyBatisUtil.getMapper(DepartmentDAO.class); Department department = mapper.queryDepartmentById(1); System.out.println(department); List<Employee> employees = department.getEmployees(); for (Employee employee : employees) { System.out.println(employee); }*/ EmployeeDAO mapper = MyBatisUtil.getMapper(EmployeeDAO.class); Employee employee = mapper.queryEmployeeById(1); System.out.println(employee); //开启了延迟加载如果不执行这一句，是不会执行queryDepartmentById System.out.println(employee.getDepartment());//开启了延迟加载如果不执行这一句，是不会执行queryEmployeeByDeptId } }

注意：开启延迟加载后，如果不使用及联数据，则不会触发及联查询操作，有利于加快查询速度、节省内存资源。

最新回复(0)