软件版本
jdk1.8
mysql 5.7
maven 3.6.1
JDBC
MYSQL
JAVA基础
Maven
单元测试
MyBatis
-
是什么
持久层框架
简化JDBC
-
如何扩展
Maven仓库
github
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.4.6</version>
</dependency>
持久化
-
什么是持久化
数据持久化
持久化就是将程序的数据在持久化状态和瞬时状态转化的过程
内存: 断电即失 如何持久化
通过jdbc 和 io文件持久化
-
为什么需要持久化
有一些对象, 不能丢
内存太贵了
持久层
-
是什么
Dao层 Service层 Controller层
完成持久化工作的代码
层界限十分明显
为什么需要MyBatis
-
帮助程序员将数据存入数据库中. 自动化
-
方便
-
传统的JDBC代码太复杂, 简化 就有了框架
-
更容易上手
-
sql和代码分离 可维护性高
程序开始
-
环境
-
mysql
create database `mybatis`;
use `mybatis`;
CREATE table `user` (
`id` int(20) not null PRIMARY key,
`name` VARCHAR(30) DEFAULT null,
`pwd` VARCHAR(30) DEFAULT NULL
)ENGINE=INNODB DEFAULT CHARSET=utf8
use `mybatis`;
INSERT into `user`(`id`,`name`,`pwd`) values
(1,'狂神','123456'),
(2,'张三','123456'),
(3,'李四','123456')
-
创建maven项目
-
删除src 作为父工程
-
导入依赖
<?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.cyz</groupId>
<artifactId>MyBatis-Study</artifactId>
<version>1.0-SNAPSHOT</version><!--导入依赖-->
<dependencies>
<!-- mysql驱动-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.47</version>
</dependency>
<!-- mybatis-->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.4.6</version>
</dependency>
<!-- junit-->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
</dependency>
</dependencies>
</project>
创建模块
-
实现模块分离
编写基本配置文件
-
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="development"> <environment id="development"> <transactionManager type="JDBC"/> <!-- 数据库配置--> <dataSource type="POOLED"> <property name="driver" value="com.mysql.jdbc.Driver"/> <property name="url" value="jdbc:mysql://localhost:3306/mybatis?useSSL=true&useUnicode=true&characterEncoding=UTF-8"/> <property name="username" value="root"/> <property name="password" value="123456"/> </dataSource> </environment> </environments> </configuration>
-
构建SqlSessionFactory 通过他拿到sqlSession
-
封装成一个类MybatisUtils
package com.cyz.utils;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 MybatisUtils {
private static SqlSessionFactory sqlSessionFactory; static { try {
// 使用mybatis获取第一步: sqlSessionFactory对象
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
} catch (IOException e) {
e.printStackTrace();
}
}//既然有了 SqlSessionFactory,顾名思义,我们可以从中获得 SqlSession 的实例。 // SqlSession 提供了在数据库执行 SQL 命令所需的所有方法。 public static SqlSession getSqlSession(){ return sqlSessionFactory.openSession(); }
}
-
编写代码
-
实体类
-
package com.cyz.pojo;
public class User {
private int id;
private String name;
private String pwd;public User() { } public User(int id, String name, String pwd) { this.id = id; this.name = name; this.pwd = pwd; } public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getPwd() { return pwd; } public void setPwd(String pwd) { this.pwd = pwd; } @Override public String toString() { return "User{" + "id=" + id + ", name='" + name + '\'' + ", pwd='" + pwd + '\'' + '}'; }
}
-
Mapper接口
package com.cyz.mapper;import com.cyz.pojo.User;
import java.util.List;
public interface UserMapper {
List<User> getUserList();
}
-
接口实现类(Mapper.xml)
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.cyz.mapper.UserMapper"> <select id="getUserList" resultType="com.cyz.pojo.User"> select * from mybatis.user </select> </mapper>
-
测试 junit
package com.cyz.dao;import com.cyz.mapper.UserMapper;
import com.cyz.pojo.User;
import com.cyz.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;import java.util.List;
public class UserDaoTest {
@Test public void Test(){
// 获取sqlSession对象
SqlSession sqlSession = MybatisUtils.getSqlSession();
// 执行sql
// 获取mapper
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
// 执行方法 拿到结果
List<User> userList = mapper.getUserList();// 方式二 过时
// List<User> userList = sqlSession.selectList("com.cyz.mapper.UserMapper.getUserList");for (User user : userList){ System.out.println(user); }
// 关闭sqlSession
sqlSession.close();
}
}
-
注意点 需要注册mapper.xml 在核心配置文件里
org.apache.ibatis.binding.BindingException: Type interface com.cyz.dao.UserDao is not known to the MapperRegistry.
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"> <!--核心配置文件--> <configuration> <!-- 环境--> <environments default="development"> <environment id="development"> <transactionManager type="JDBC"/> <!-- 数据库配置--> <dataSource type="POOLED"> <property name="driver" value="com.mysql.jdbc.Driver"/> <property name="url" value="jdbc:mysql://localhost:3306/mybatis?useSSL=false&useUnicode=true&characterEncoding=UTF-8"/> <property name="username" value="root"/> <property name="password" value="123456"/> </dataSource> </environment> </environments><!-- 每一个Mapper.xml都需要在mybatis核心配置文件中注册--> <mappers> <mapper resource="mapper/UserMapper.xml"/> </mappers>
</configuration>
-
注意点二
-
Could not find resource com/cyz/dao/UserMapper.xml
配置文件无法导出或者生效的问题
解决: 手动配置pom.xml
<!-- 配置resources 来防止外面资源导出失效的问题-->
<build>
<resources>
<resource>
<directory>src/main/resources</directory>
<includes>
<include>/*.properties</include>
<include>/.xml</include>
</includes>
<filtering>true</filtering>
</resource>
<resource>
<directory>src/main/java</directory>
<includes>
<include>**/.properties</include>
<include>**/*.xml</include>
</includes>
<filtering>true</filtering>
</resource>
</resources>
</build> -
注意点三
如遇到空指针 如果是因为中文不允许在pom.xml中加入
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
</properties>
mapper.xml
-
namespace 命名空间 必须是
.
和全名 -
id : 方法名
-
resultType : sql语句的返回值
-
parameterType: 参数类型
提交事务@Test
public void addUser(){
SqlSession sqlSession = MybatisUtils.getSqlSession();UserMapper mapper = sqlSession.getMapper(UserMapper.class); int row = mapper.addUser(new User(4, "哈哈", "123456")); if (row > 0) { System.out.println("插入成功"); //提交事务 sqlSession.commit(); } sqlSession.close(); }</pre>
基本增删改查
-
只需要修改Mapper接口,和mapper.xml,和Test
package com.cyz.mapper;import com.cyz.pojo.User;
import java.util.List;
public interface UserMapper {
//获取全部用户
List<User> getUserList();//根据id查询用户 User getUserById(int id); //新增 int addUser(User user); //修改 int updateUser(User user); //删除 int deleteUser(int 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.cyz.mapper.UserMapper"> <insert id="addUser" parameterType="com.cyz.pojo.User"> insert into mybatis.user (id,name,pwd) values (#{id},#{name},#{pwd}) </insert> <update id="updateUser" parameterType="com.cyz.pojo.User"> update mybatis.user set name=#{name},pwd=#{pwd} where id=#{id} </update> <delete id="deleteUser" parameterType="int"> delete from mybatis.user where id=#{id} </delete> <select id="getUserList" resultType="com.cyz.pojo.User"> select * from mybatis.user </select> <select id="getUserById" parameterType="int" resultType="com.cyz.pojo.User"> select * from mybatis.user where id = #{id} </select> </mapper>
package com.cyz.dao; import com.cyz.mapper.UserMapper; import com.cyz.pojo.User; import com.cyz.utils.MybatisUtils; import org.apache.ibatis.session.SqlSession; import org.junit.Test; import java.util.List; public class UserDaoTest { @Test public void Test() { // 获取sqlSession对象 SqlSession sqlSession = MybatisUtils.getSqlSession(); // 执行sql // 获取mapper UserMapper mapper = sqlSession.getMapper(UserMapper.class); // 执行方法 拿到结果 List<User> userList = mapper.getUserList(); // 方式二 过时 // List<User> userList = sqlSession.selectList("com.cyz.mapper.UserMapper.getUserList"); for (User user : userList) { System.out.println(user); } // 关闭sqlSession sqlSession.close(); } @Test public void getUserById(){ SqlSession sqlSession = MybatisUtils.getSqlSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); User user = mapper.getUserById(1); System.out.println(user); sqlSession.close(); } @Test public void addUser(){ SqlSession sqlSession = MybatisUtils.getSqlSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); int row = mapper.addUser(new User(4, "哈哈", "123456")); if (row > 0) { System.out.println("插入成功"); //提交事务 sqlSession.commit(); } sqlSession.close(); } @Test public void updateUser(){ SqlSession sqlSession = MybatisUtils.getSqlSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); int row = mapper.updateUser(new User(4, "呵呵", "223222")); if (row > 0){ System.out.println("修改成功"); sqlSession.commit(); } sqlSession.close(); } @Test public void deleteUser(){ SqlSession sqlSession = MybatisUtils.getSqlSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); int row = mapper.deleteUser(4); if (row > 0){ System.out.println("删除成功"); sqlSession.commit(); } sqlSession.close(); } }
Map
-
解决pojo字段多的问题
//map写法 User getUserById2(Map<String,Object> obj);
//取值的时候对应 map的键 <select id="getUserById2" parameterType="map" resultType="com.cyz.pojo.User"> select * from mybatis.user where id=#{id} and name=#{name} </select>
@Test public void getUserById2(){ SqlSession sqlSession = MybatisUtils.getSqlSession();UserMapper mapper = sqlSession.getMapper(UserMapper.class); Map<String,Object> map = new HashMap<String, Object>(); map.put("id",6); map.put("name","Hi"); User user = mapper.getUserById2(map); System.out.println(user); sqlSession.close(); }
-
Map传参, 直接在sql中取key parameterType="map"
-
对象传参, 直接在sql中取对象属性 parameterType="com.cyz.pojo.User"
-
只有一个基本类型参数的情况下, 可以直接在sql中取到 parameterType="int"
-
多个参数用Map,或者注解
模糊查询
//模糊查询 List<User> getUserLike(String name);
<select id="getUserLike" resultType="com.cyz.pojo.User"> //这里写死 防止sql注入 使用的时候传拼接通配符% select * from mybatis.user where name like #{value} </select>
@Test public void getUserLike(){ SqlSession sqlSession = MybatisUtils.getSqlSession();UserMapper mapper = sqlSession.getMapper(UserMapper.class); // 拼接 List<User> userList = mapper.getUserLike("%H%"); for (User user : userList){ System.out.println(user); } sqlSession.close(); }
配置文件
属性优化properties
-
核心配置文件
<?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><!-- 引入 db.properties-->
<properties resource="db.properties"/><!-- 环境 默认development--> <environments default="development"> <environment id="development">
<!-- 事务管理器 JDBC 或 MANAGED-->
<transactionManager type="JDBC"/>
<!-- 数据库配置-->
<!-- 数据源 UNPOOLED | POOLED | JNDI-->
<!-- UNPOOLED POOLED(有连接池 用完可以回收 web响应更快) -->
<dataSource type="POOLED">
<!-- property 引用配置文件 db.properties-->
<property name="driver" value="${driver}"/>
<property name="url"
value="${url}"/>
<property name="username" value="${username}"/>
<property name="password" value="${password}"/>
</dataSource>
</environment><environment id="test"> <transactionManager type=""></transactionManager> <dataSource type=""></dataSource> </environment> </environments> <!-- 每一个Mapper.xml都需要在mybatis核心配置文件中注册--> <mappers> <mapper resource="mapper/UserMapper.xml"/> </mappers>
</configuration>
-
外部配置文件db.properties
driver=com.mysql.jdbc.Driver url=jdbc:mysql://localhost:3306/mybatis?useSSL=false&useUnicode=true&characterEncoding=UTF-8 username=root password=123456
注意点
-
内部可以再次定义配置
<properties resource="db.properties"> <property name="username" value="root"/> <property name="pwd" value="1111"/> </properties>
-
外部配置文件优先级内部配置
别名配置typeAliases
-
为java类型设置别名
-
减少类完全限定名的冗余
<select id="getUserList" resultType="User"> select * from mybatis.user </select><!-- 大小写均可-->
<select id="getUserList" resultType="user">
select * from mybatis.user
</select>
-
配置文件
<!-- 别名--> <!-- <typeAliases>--> <!-- 实体类 对应别名 可自定义别名--> <!-- <typeAlias type="com.cyz.pojo.User" alias="User"/>--> <!-- </typeAliases>--><typeAliases>
<!-- 实体类多是使用 直接扫描 实体类包 别名为实体类小写 可以通过在实体类上加注解来指定自定义别名-->
<package name="com.cyz.pojo"/>
</typeAliases>
<?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> <!-- 引入 db.properties--> <properties resource="db.properties"/> <!-- 别名--> <!-- <typeAliases>--> <!-- 实体类 对应别名 可自定义别名--> <!-- <typeAlias type="com.cyz.pojo.User" alias="User"/>--> <!-- </typeAliases>--> <typeAliases> <!-- 实体类多是使用 直接扫描 实体类包 别名为实体类小写 可以通过在实体类上加注解来指定自定义别名--> <package name="com.cyz.pojo"/> </typeAliases> <!-- 环境 默认development--> <environments default="development"> <environment id="development"> <!-- 事务管理器 JDBC 或 MANAGED--> <transactionManager type="JDBC"/> <!-- 数据库配置--> <!-- 数据源 UNPOOLED | POOLED | JNDI--> <!-- UNPOOLED POOLED(有连接池 用完可以回收 web响应更快) --> <dataSource type="POOLED"> <!-- property 引用配置文件 db.properties--> <property name="driver" value="${driver}"/> <property name="url" value="${url}"/> <property name="username" value="${username}"/> <property name="password" value="${password}"/> </dataSource> </environment> <environment id="test"> <transactionManager type=""></transactionManager> <dataSource type=""></dataSource> </environment> </environments> <!-- 每一个Mapper.xml都需要在mybatis核心配置文件中注册--> <mappers> <mapper resource="mapper/UserMapper.xml"/> </mappers> </configuration>
设置settings
-
缓存
-
懒加载
-
日志
-
下划线转驼峰
映射器
-
三种方式
<!-- 每一个Mapper.xml都需要在mybatis核心配置文件中注册--> <mappers> <mapper resource="mapper/UserMapper.xml"/> </mappers>//推荐使用</pre>
<!-- 每一个Mapper.xml都需要在mybatis核心配置文件中注册--> <mappers> <mapper class="com.cyz.mapper.UserMapper.xml"/> </mappers> //接口和映射文件必须在一个包下且同名//扫描包注入 <mappers> <package name="com.cyz.mapper"/> </mappers> //接口和映射文件必须在一个包下且同名生命周期和作用域
-
错误使用会出现并发问题
SqlSessionFactoryBuilder
-
SqlSessionFactory一旦被创建, 就不在需要SqlSessionFactoryBuilder 他了---->局部变量
SqlSessionFactory
-
相当于数据库连接池
-
创建后运行期间一直存在, 所以不需要废弃重写创建 ----> 应用作用域
-
单例模式或静态单例模式
SqlSession
-
相当于连接池的请求
-
非线程安全的 所以最好是 请求或方法作用域
-
需要开启和关闭 重要
流程
SqlSessionFactory -----> SqlSession (多个请求)------> SqlSession ------> SqlSession ------->Mapper (多个业务) ------->Mapper ------->Mapper 最后关闭
ResultMap
-
解决数据库属性名和实体类字段名不一致的问题
package com.cyz.pojo;public class User {
private int id;
private String name;
private String password;//与数据库不一致public User() { } public User(int id, String name, String password) { this.id = id; this.name = name; this.password = password; } @Override public String toString() { return "User{" + "id=" + id + ", name='" + name + '\'' + ", password='" + password + '\'' + '}'; } public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getPassword() { return password; } public void setPassword(String password) { this.password = password; }
}
方法一 <select id="getUserById" parameterType="int" resultType="com.cyz.pojo.User"> select id,name,pwd as password from mybatis.user where id = #{id} </select>方法二
<resultMap id="UserMap" type="user">
<!-- 只需要映射需要映射的-->
<result column="pwd" property="password"/>
</resultMap><select id="getUserById" parameterType="int" resultMap="UserMap"> select * from mybatis.user where id = #{id} </select></pre>
日志
-
排错
日志工厂
-
配置日志
//标准 <settings> <setting name="logImpl" value="STDOUT_LOGGING"/> </settings>
Log4j
-
输出日志信息,
-
可控制输出格式, 和输出级别
-
导入包
<dependency> <groupId>log4j</groupId> <artifactId>log4j</artifactId> <version>1.2.17</version> </dependency>
-
核心配置文件配置
<settings> <setting name="logImpl" value="LOG4J"/> </settings>
-
log4j.properties
### 配置根 ### log4j.rootLogger = Debug,console,file配置输出到控制台
log4j.appender.console = org.apache.log4j.ConsoleAppender
log4j.appender.console.Target = System.out
log4j.appender.console.Threshold = DEBUG
log4j.appender.console.layout = org.apache.log4j.PatternLayout
log4j.appender.console.layout.ConversionPattern = [%c]-%m%n配置输出到文件
log4j.appender.file = org.apache.log4j.FileAppender
log4j.appender.file.File = ./log/cyz.log
log4j.appender.file.MaxFileSize = 10mb
log4j.appender.file.Threshold = DEBUG
log4j.appender.file.layout = org.apache.log4j.PatternLayout
log4j.appender.file.layout.ConversionPattern = [%p][%d{yy-MM-dd}][%c]%m%n设置输出sql的级别,其中logger后面的内容全部为jar包中所包含的包名
log4j.logger.org.apache=DEBUG
log4j.logger.java.sql=DEBUG
log4j.logger.java.sql.Statement=DEBUG
log4j.logger.java.sql.ResultSet=DEBUG
log4j.logger.java.sql.PreparedStatement=dubug
简单使用
-
导包
import org.apache.log4j.Logger;
-
获取日志对象
-
static Logger logger = Logger.getLogger(UserDaoTest.class);
package dao;import org.apache.log4j.Logger;
import org.junit.Test;public class UserDaoTest {
static Logger logger = Logger.getLogger(UserDaoTest.class); @Test public void testLog4j(){ logger.info("info:进入了testtLog4j"); logger.debug("debug:进入了testtLog4j"); logger.error("error:进入了testtLog4j"); }
}
分页
-
使用Limit
select * from user limit startIndex,pageSize
-
使用mybatis
<select id="getUserByLimit" parameterType="map" resultType="user"> select * from mybatis.user limit #{startIndex},#{pageSize} </select>
List<User> getUserByLimit(Map<String,Integer> map);
@Test public void getUserByLimit(){ SqlSession sqlSession = MybatisUtils.getSqlSession();UserMapper mapper = sqlSession.getMapper(UserMapper.class); Map<String,Integer> map = new HashMap<String, Integer>(); map.put("startIndex",1); map.put("pageSize",2); List<User> userList = mapper.getUserByLimit(map); for (User user : userList){ System.out.println(user); } sqlSession.close(); }</pre>
RowBounds分页(不推荐)
<select id="getUserByRowBounds" resultMap="UserMap"> select * from mybatis.user </select>List<User> getUserByRowBounds();@Test public void getUserByRowBounds(){ SqlSession sqlSession = MybatisUtils.getSqlSession();RowBounds rowBounds = new RowBounds(1, 2); List<User> userList = sqlSession.selectList("com.cyz.mapper.UserMapper.getUserByRowBounds",null,rowBounds); for (User user : userList){ System.out.println(user); } sqlSession.close(); }</pre>
分页插件
-
pageHelper
注解开发
@Select("select * from user") List<User> getUsers();
<!-- 绑定接口--> <mappers> <mapper class="com.cyz.mapper.UserMapper"/> </mappers>
@Test public void getUsers(){ SqlSession sqlSession = MybatisUtils.getSqlSession();UserMapper mapper = sqlSession.getMapper(UserMapper.class); List<User> users = mapper.getUsers(); for (User user : users){ System.out.println(user); } sqlSession.close(); }</pre>
-
本质 反射机制实现
-
底层: 动态代理实现
Mybatis详细执行流程
加载配置文件SqlSessionFactoryBuilder
解析配置文件
配置所有配置信息
获取到sqlSessionFactory实例
处理事务
创建执行器
创建sqlSession
实现CRUD
判断是否执行成功 失败 事务回滚
成功 提交事务
关闭
注解实现CRUD 并自动提交事务
//注册<mappers> <mapper class="com.cyz.mapper.UserMapper"/> </mappers></pre>
package com.cyz.utils;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 MybatisUtils {
private static SqlSessionFactory sqlSessionFactory; static { try {
// 使用mybatis获取第一步: sqlSessionFactory对象
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
} catch (IOException e) {
e.printStackTrace();
}
}//既然有了 SqlSessionFactory,顾名思义,我们可以从中获得 SqlSession 的实例。 // SqlSession 提供了在数据库执行 SQL 命令所需的所有方法。 public static SqlSession getSqlSession(){ return sqlSessionFactory.openSession(true);//true 表示自动提交事务 }
}
package com.cyz.mapper; import org.apache.ibatis.annotations.*; import com.cyz.pojo.User; import java.util.List; public interface UserMapper { @Select("select * from user") List<User> getUsers(); @Select("select * from user where id = #{id}") User getUserById(@Param("id") int id); @Insert("insert into user(id,name,pwd) values(#{id}, #{name}, #{password})") int addUser(User user); @Update("update user set name = #{name}, pwd=#{password} where id=#{id}") int updateUser(User user); @Delete("delete from user where id = #{id}") int deleteUserById(@Param("id") int id); }package com.cyz.mapper; import com.cyz.pojo.User; import org.apache.ibatis.session.SqlSession; import org.junit.Test; import com.cyz.utils.MybatisUtils; import java.util.List; public class UserMapperTest { @Test public void getUsers() { SqlSession sqlSession = MybatisUtils.getSqlSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); List<User> users = mapper.getUsers(); for (User user : users) { System.out.println(user); } sqlSession.close(); } @Test public void getUserById() { SqlSession sqlSession = MybatisUtils.getSqlSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); User user = mapper.getUserById(1); System.out.println(user); sqlSession.close(); } @Test public void addUser() { SqlSession sqlSession = MybatisUtils.getSqlSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); int row = mapper.addUser(new User(7,"aa","123456")); if(row > 0){ System.out.println("插入成功"); } sqlSession.close(); } @Test public void updateUser() { SqlSession sqlSession = MybatisUtils.getSqlSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); int row = mapper.updateUser(new User(7,"xx","111111")); if(row > 0){ System.out.println("修改成功"); } sqlSession.close(); } @Test public void deleteUserById() { SqlSession sqlSession = MybatisUtils.getSqlSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); int row = mapper.deleteUserById(7); if(row > 0){ System.out.println("删除成功"); } sqlSession.close(); } }
-
@Param() 注解
-
基本类型和String类型 要加上
-
引用类型不需要加
-
如果只有一个参数可以省略
-
sql中引用的时我们在@Param中定义的属性
-
Lombok
-
安装插件lombok
-
导包
<dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <version>1.18.12</version> <scope>provided</scope> </dependency>
-
@Data //无参构造 getter setter toString hashCode equals
-
@AllArgsConstructor //全参数
@Data @AllArgsConstructor @EqualsAndHashCode @NoArgsConstructor @ToString @Getter @Setter
-
缺点
不建议使用 少了写代码的过程 只是偷懒的东西并不是很好
复杂查询
多对一
-
多个学生对应一个老师
-
sql
CREATE TABLE `teacher` ( `id` INT(10) NOT NULL, `name` VARCHAR(30) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=INNODB DEFAULT CHARSET=utf8INSERT INTO teacher(
id
,name
) VALUES (1, '秦老师');CREATE TABLE
student
(
id
INT(10) NOT NULL,
name
VARCHAR(30) DEFAULT NULL,
tid
INT(10) DEFAULT NULL,
PRIMARY KEY (id
),
KEYfktid
(tid
),
CONSTRAINTfktid
FOREIGN KEY (tid
) REFERENCESteacher
(id
)
) ENGINE=INNODB DEFAULT CHARSET=utf8
INSERT INTOstudent
(id
,name
,tid
) VALUES ('1', '小明', '1');
INSERT INTOstudent
(id
,name
,tid
) VALUES ('2', '小红', '1');
INSERT INTOstudent
(id
,name
,tid
) VALUES ('3', '小张', '1');
INSERT INTOstudent
(id
,name
,tid
) VALUES ('4', '小李', '1');
INSERT INTOstudent
(id
,name
,tid
) VALUES ('5', '小王', '1');
-
新建模板
select s.id,s.name,t.id,t.name from student s, teacher t where s.tid = t.id
package com.cyz.pojo;import lombok.Data;
@Data
public class Student {
private int id;
private String name;
private Teacher teacher;
}package com.cyz.pojo;
import lombok.Data;
@Data
public class Teacher {
private int id;
private String name;
}
public List<Student> getStudent(); public List<Student> getStudent2();
-
方式一 (嵌套查询)
<?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.cyz.mapper.StudentMapper"> <select id="getStudent" resultMap="StudentTeacher"> select * from mybatis.student </select><resultMap id="StudentTeacher" type="Student"> <result property="id" column="id"/> <result property="name" column="name"/> <!-- 复杂属性 对象:association 集合: collection --> <association property="teacher" column="tid" javaType="teacher" select="getTeacher"/> </resultMap> <select id="getTeacher" resultType="teacher"> select * from mybatis.teacher where id = #{id} </select>
</mapper>
-
方式二
<select id="getStudent2" resultMap="StudentTeacher2"> select s.id sid,s.name sname,t.name tname from student s, teacher t where s.tid = t.id </select><resultMap id="StudentTeacher2" type="student"> <result property="id" column="sid"/> <result property="name" column="sname"/> <association property="teacher" javaType="Teacher"> <result property="name" column="tname"/> </association> </resultMap></pre>
一对多
-
一个老师对应多个学生
-
sql
select s.id sid, s.name sname, t.name tname, t.id tid from student s,teacher t where s.tid = tid
package com.cyz.pojo;import lombok.Data;
import java.util.List;
@Data
public class Teacher {
private int id;
private String name;
private List<Student> students;
}package com.cyz.pojo;
import lombok.Data;
@Data
public class Student {
private int id;
private String name;
private int tid;
}
Teacher getTeacher(@Param("tid") int id); Teacher getTeacher2(@Param("tid") int 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.cyz.mapper.TeacherMapper"> <select id="getTeacher" resultMap="TeacherStudent"> select s.id sid, s.name sname, t.name tname, t.id tid from student s,teacher t where s.tid = t.id and t.id = #{tid} </select> <resultMap id="TeacherStudent" type="teacher"> <result property="id" column="tid"/> <result property="name" column="tname"/> <!-- 集合中的泛型使用 ofType--> <collection property="students" ofType="student"> <result property="id" column="sid"/> <result property="name" column="sname"/> <result property="tid" column="tid"/> </collection> </resultMap> </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"> <select id="getTeacher2" resultMap="TeacherStudent2"> select * from mybatis.teacher where id = #{tid} </select> <resultMap id="TeacherStudent2" type="teacher"> <collection property="students" javaType="ArrayList" ofType="student" select="getStudentByTeacherId" column="id"/> </resultMap> <select id="getStudentByTeacherId" resultType="student"> select * from mybatis.student where tid = #{tid} </select> </mapper>
-
collection必须在最后写
动态Sql
-
根据不同的条件生成不同的SQL语句
if choose(when otherwise) trim(where,set) foreach
-
sql
CREATE TABLE `blog`( `id` VARCHAR(50) NOT NULL COMMENT '博客id', `title` VARCHAR(100) NOT NULL COMMENT '博客标题', `author` VARCHAR(30) NOT NULL COMMENT '博客作者', `create_time` DATETIME NOT NULL COMMENT '创建时间', `views` INT(30) NOT NULL COMMENT '浏览量' )ENGINE=INNODB DEFAULT CHARSET=utf8
package com.cyz.pojo;import lombok.Data;
import java.util.Date;
@Data
public class Blog {
private String id;
private String title;
private String author;
private Date createTime;
private int views;
}
-
生成id工具类
package com.cyz.utils;import org.junit.Test;
import java.util.UUID;
@SuppressWarnings("all")//抑制警告
public class IDutils {
private static String getId(){
return UUID.randomUUID().toString().replaceAll("-","");
}
}
-
驼峰命名规则
<!-- 开启驼峰命名规则--> <setting name="mapUnderscoreToCamelCase" value="true"/>
-
新增数据
where
开头为and或or时会去掉 没有条件是where会去掉
if
<where> <if test="title != null"> and title = #{title} </if> <if test="author != null"> and author = #{author} </if> </where>
choose(相当于 swich 和 case) 选择一种
when
otherwise
<select id="queryBlogChoose" parameterType="map" resultType="blog"> select * from mybatis.blog <where> <choose> <when test="title != null"> title = #{title} </when> <when test="author != null"> and author = #{author} </when> <otherwise> and views = #{views} </otherwise> </choose> </where> </select>
set
-
动态前置关键字 会删除逗号
<update id="updateBlog" parameterType="map"> update mybatis.blog <set> <if test="title != null"> title = #{title}, </if> <if test="author != null"> author = #{author} </if> </set> where id = #{id} </update>
trim
//前缀去掉 <trim prefix="WHERE" prefixOverrides="AND | OR">
</trim>
//后缀去掉
<trim prefix="SET" suffixOverrides=","></trim>
sql片段
公用的东西 提取出来<sql id="if-title-author">
<if test="title != null">
and title = #{title}
</if>
<if test="author != null">
and author = #{author}
</if>
</sql><select id="queryBlogIF" parameterType="map" resultType="blog"> select * from mybatis.blog <where> <include refid="if-title-author"></include> </where> </select></pre>
-
最好基于单表来定义SQL标签
-
不要有where
forEach
select * from user where 1=1 and (id = 1 or id =2 or id = 3)
//查询1-3号 List<Blog> queryBlogForeach(Map map);
<select id="queryBlogForeach" parameterType="map" resultType="blog"> select * from mybatis.blog <where> <foreach collection="ids" item="id" open="and (" close=")" separator="or" > id = #{id} </foreach> </where> </select>
@Test public void queryBlogForeach(){ SqlSession sqlSession = MybatisUtils.getSqlSession();BlogMapper mapper = sqlSession.getMapper(BlogMapper.class); Map map = new HashMap(); ArrayList<Integer> ids = new ArrayList<Integer>(); ids.add(1); ids.add(2); ids.add(3); map.put("ids",ids); mapper.queryBlogForeach(map); sqlSession.close(); }</pre>
select * from mybatis.blog WHERE ( id = ? or id = ? or id = ? ) ==> Parameters: 1(Integer), 2(Integer), 3(Integer)缓存
-
是什么
存在内存中的临时数据
-
为什么用
减少和数据库的交互次数, 减少系统开销 , 提高效率
-
什么时候用
经常查询并且不经常改变的数据
Mybatis缓存
一级缓存(默认开启) 本地缓存二级缓存(手动开启) 实现Cache接口 来定义二级缓存
清理缓存策略
LRU 最近最少使用, 移除最长时间不被使用的对象 默认开启FIFO 先进先出, 按对象进入缓存顺序来移除对象
SOFI 软引用 基于垃圾回收器状态和软引用规则移除对象
WEAK 弱引用 更积极地基于垃圾收集器状态和弱引用规则移除对象
一级缓存
-
本地缓存
-
sqlSession开启到关闭的期间会被缓存
-
查询同一条数据
@Test public void queryUserById(){ SqlSession sqlSession = MybatisUtils.getSqlSession();UserMapper mapper = sqlSession.getMapper(UserMapper.class); User user = mapper.queryUserById(1); System.out.println(user); User user2 = mapper.queryUserById(1); System.out.println(user2); System.out.println(user==user2); sqlSession.close(); }</pre>
==> Preparing: select * from mybatis.user where id =? ==> Parameters: 1(Integer) <== Columns: id, name, pwd <== Row: 1, 狂神, 123456 <== Total: 1 User(id=1, name=狂神, pwd=123456) User(id=1, name=狂神, pwd=123456) true缓存失效的情况
-
查询不同的东西
-
增删改操作
-
查询不同的Mapper.xml
-
手动清理 sqlSession.clearCache();
二级缓存
-
一级作用域低
-
全局缓存, 基于namespance级别的缓存 一个命名空间对应一个缓存
-
工作机制
在sqlSession关闭时 , 一级缓存没了, 但是开启后数据会进入二级缓存不同的mapper查出的数据会放在自己对应的缓存(map)中
开启全局缓存 <!-- 显示开启全局缓存--> <setting name="cacheEnabled" value="true"/>
开启缓存在Mapper.xml中加入
<cache/>
在使用缓存的上面加上
useCache="true"需要序列化实体类
自定义策略 <?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <!--核心配置文件--> <mapper namespace="com.cyz.mapper.UserMapper"> <cache eviction="FIFO" flushInterval="60000" size="512" readOnly="true" /> <select id="queryUserById" resultType="user" parameterType="int" useCache="true"> select * from mybatis.user where id =#{id} </select> <update id="updateUser" parameterType="user"> update mybatis.user set name=#{name},pwd=#{pwd} where id=#{id} </update> </mapper>
@Test public void queryUserById(){ SqlSession sqlSession = MybatisUtils.getSqlSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); User user = mapper.queryUserById(1); System.out.println(user); sqlSession.close(); // int row = mapper.updateUser(new User(2, "aa", "111111")); // // if (row > 0){ // System.out.println("修改成功"); // } SqlSession sqlSession2 = MybatisUtils.getSqlSession(); UserMapper mapper2 = sqlSession2.getMapper(UserMapper.class); User user2 = mapper2.queryUserById(1); System.out.println(user2); System.out.println(user==user2); sqlSession2.close(); }
-
没有自定义策略时问题
-
需要给实体类序列化
package com.cyz.pojo;
import lombok.AllArgsConstructor;
import lombok.Data;import java.io.Serializable;
@Data
@AllArgsConstructor
public class User implements Serializable {
private int id;
private String name;
private String pwd;
}
-
只要开启了二级缓存在同一个Mapper下就有效
-
所有数据都会先放在一级缓存中
-
只有当会话提交, 或者关闭的时候, 才会提交到二级缓存中
-
缓存原理
数据库--->sqlSession(一级缓存)------>多个各自的缓存 --->关闭时, 去找各自的Mapper二级缓存 Mapper二级缓存--->多个保存各自的缓存 <------用户 第一次查询先看二级缓存有没有 二级没有看一级 一级没有 在查询数据库</pre>
自定义缓存ehcache
-
分布式缓存, 面向通用缓存
-
导包
<dependency> <groupId>org.mybatis.caches</groupId> <artifactId>mybatis-ehcache</artifactId> <version>1.1.0</version> </dependency>
-
ehcache.xml
-
<cache type="org.mybatis.caches.ehcache.EhBlockingCache"/>
需要关闭
useCache="false"
-
工作中使用redis