基本介绍
Controller层的解决方案之一 主要功能就是完成数据的增删改查
SSH: Struts/SpringMVC Spring Hibernate SSM: Struts/SpringMVC Spring MyBatis SSS: Struts/SpringMVC Spring SpringDataJPA JDBC/Hibernate/MyBatis Hibernate 代码最简洁,但效率最低(需要其翻译为SQL语句,造成效率下降) JDBC 代码最复杂,但效率最高 MyBatis 代码与效率位于Hibernate与JDBC之间 MyBatis-Plus MyBatis的功能增强 iBatis是MyBatis以前的名字
代码实现
导包
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.10</version>
<scope>provided</scope>
</dependency>
<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.40</version>
</dependency>
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-api</artifactId>
<version>1.5.10</version>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-log4j12</artifactId>
<version>1.6.1</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
</dependency>
实体
@Data
@AllArgsConstructor
@NoArgsConstructor
public class User {
private Integer userId;
private String username;
private String password;
}
mybatis.xml
<configuration>
<!--
配置环境(数据库运行环境 数据库连接四要素)
default:要用哪一个环境
-->
<environments default="mysql">
<environment id="mysql">
<!--事务的类型配置为 JDBC-->
<transactionManager type="JDBC"></transactionManager>
<!--数据源类型配置为 连接池-->
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver"></property>
<property name="url" value="jdbc:mysql:///data-test"></property>
<property name="username" value="root"></property>
<property name="password" value="root"></property>
</dataSource>
</environment>
<environment id="Oracle">
<transactionManager type=""></transactionManager>
<dataSource type=""></dataSource>
</environment>
</environments>
<mappers>
<mapper resource="./mapper/UserMapper.xml"></mapper>
</mappers>
</configuration>
Mapper.xml
<!--注意:namespace必须唯一-->
<mapper namespace="UserMapper">
<insert id="insert">
insert into t_user(username, password) values ('buliyat', '123')
</insert>
</mapper>
测试
public class Test01 {
public static void main(String[] args) throws IOException {
// 第一步:要获取到配置文件流
Reader resourceAsReader = Resources.getResourceAsReader("mybatis.xml");
// 第二步:创建SqlSessionFactory对象
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsReader);
// 第三步:创建操作数据库的对象
SqlSession sqlSession = sqlSessionFactory.openSession();
// 第四步:操作数据库
int insert = sqlSession.insert("UserMapper.insert");
// 第五步:提交资源
sqlSession.commit();
sqlSession.close();
}
}
数据操作
操作的方法与标签调用可互换 操作与查询是不能互换 操作返回影响行数 查询返回数据
xml
<mapper namespace="UserMapper">
<delete id="insert">
insert into t_user(username, password)
values ('buliyat', '123')
</delete>
<delete id="update">
update t_user set username='xxx'
</delete>
<insert id="delete">
delete from t_user
</insert>
<select id="list" resultType="com.buliyat.qcy.pojo.User">
select * from t_user
</select>
</mapper>
测试
public class Test01 {
public void testAdd() throws IOException {
Reader resourceAsReader = Resources.getResourceAsReader("mybatis.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsReader);
SqlSession sqlSession = sqlSessionFactory.openSession();
int insert = sqlSession.delete("UserMapper.insert");
sqlSession.commit();
sqlSession.close();
}
public void testUpdate() throws IOException {
Reader resourceAsReader = Resources.getResourceAsReader("mybatis.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsReader);
SqlSession sqlSession = sqlSessionFactory.openSession();
int update = sqlSession.delete("UserMapper.update");
sqlSession.commit();
sqlSession.close();
}
public void testDelete() throws IOException {
Reader resourceAsReader = Resources.getResourceAsReader("mybatis.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsReader);
SqlSession sqlSession = sqlSessionFactory.openSession();
int delete = sqlSession.delete("UserMapper.delete");
sqlSession.commit();
sqlSession.close();
}
public void testList() throws IOException {
Reader resourceAsReader = Resources.getResourceAsReader("mybatis.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsReader);
SqlSession sqlSession = sqlSessionFactory.openSession();
List<User> userList = sqlSession.selectList("UserMapper.list");
System.out.println("查询数来的数据是:"+userList);
sqlSession.commit();
sqlSession.close();
}
}
工具类
public class MyBatisUtils {
private static Reader resourceAsReader = null;
private static SqlSessionFactory sqlSessionFactory = null;
private static ThreadLocal<SqlSession> sqlSessionThreadLocal = null;
static {
try {
resourceAsReader = Resources.getResourceAsReader("mybatis.xml");
sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsReader);
sqlSessionThreadLocal = new ThreadLocal<SqlSession>();
} catch (IOException e) {
throw new RuntimeException(e);
}
}
public static SqlSession getSqlSession() {
SqlSession sqlSession = sqlSessionThreadLocal.get();
if(null!=sqlSession){
return sqlSession;
}
sqlSession = sqlSessionFactory.openSession();
sqlSessionThreadLocal.set(sqlSession);
return sqlSession;
}
public static void close() {
SqlSession sqlSession = sqlSessionThreadLocal.get();
if(null!=sqlSession){
sqlSession.commit();
sqlSession.close();
sqlSessionThreadLocal.remove();
}
}
}
参数传入
单个参数
<!--
parameterType中的参数类型
1: 写Java中的全路径
2: 写类型于mybatis中的别名 (mybatis中给这些类型取的别名)
开发时 占位符处为 values
-->
<select id="findUserById01" parameterType="java.lang.Integer" resultType="com.buliyat.qcy.pojo.User">
select * from t_user where userId = #{values}
</select>
<select id="findUserById02" parameterType="integer" resultType="com.qfedu.edu.pojo.User">
select * from t_user where userId = #{values}
</select>
对象类型
<!--占位符处为 对象的属性-->
<insert id="insert" parameterType="com.buliyat.qcy.pojo.User">
insert into t_user(username,password) values(#{username},#{password})
</insert>
map类型
<!--占位符处为 map中key值-->
<select id="findUser" parametertype="map" returnType="com.buliyat.qcy.pojo.User">
select * from t_user where username={#username} and password={#password}
</select>
参数输出
单个参数
<select id="findUsernameByUserId" parameterType="int" resultType="string">
select username from t_user where userId=#{values}
</select>
对象参数
<select id="findUserByUserId" parameterType="int" resultType="user">
select username from t_user where userId=#{values}
</select>
list类型
<select id="list" parameterType="int" resultType="user">
select * from t_user
</select>
结果集映射
取别名
<select id="list" resultType="user">
select id as userId,userName as username,pwd as password from t_user
</select>
结果集映射
<resultMap id="listResultMap" type="user">
<!--主键映射-->
<id property="userId" column="id"></id>
<!--其他字段映射-->
<result property="username" column="userName"></result>
<result property="password" column="pwd"></result>
</resultMap>
<select id="list" resultMap="listResultMap">
select * from t_user
</select>
引入接口
面向接口编程 代码与配置实现分离
<!--将namespace 设置为 接口的全路径-->
<mapper namespace="com.buliyat.qcy.interface.UserMapper">
<!--
描述与方法(id与方法名) 保持一致
描述的输入参数 保持一致
返回值类型 保持一致
-->
<select id="findUserById" parameterType="int" resultType="user">
select * from t_user where userId=#{values}
</select>
<select id="list" resultType="user">
select * from t_user
</select>
</mapper>
动态SQL
<!--namespace值设置为 接口的全路径-->
<mapper namespace="com.buliyat.qcy.dynamicsql.UserMapper">
<sql id="textSQL">
<if test="userId!=null">
and userId=#{userId}
</if>
<if test="password!=null and password!=''">
and password=#{password}
</if>
<if test="username!=null and username!=''">
and username=#{username}
</if>
</sql>
<!--第一种写法-->
<select id="findUserByCondition" parameterType="user" resultType="user">
select * from t_user where 1=1
<include refid="textSQL"></include>
</select>-
<!--第二种写法-->
<select id="findUserByCondition" parameterType="user" resultType="user">
select * from t_user
<where>
<include refid="textSQL"></include>
</where>
</select>
<!--
第三种写法
prefix: 添加SQL前缀
suffix: 添加SQL后缀
prefixOverrides: 删除SQL前缀
suffixOverrides: 删除SQL后缀
-->
<select id="findUserByCondition" parameterType="user" resultType="user">
select * from t_user
<trim prefix="where" prefixOverrides="and">
<include refid="textSQL"></include>
</trim>
</select>
</mapper>
<!--
通过多个id 查询用户数据
若传输类型为list集合 则遍历集合的名字也为list
collection: 遍历的集合类型
item: 遍历的数据
open: 前缀
close: 后缀
separator: 分割数据的字符
-->
<select id="findUserByIds" parameterType="list" resultType="user">
select * from t_user
<foreach collection="list" item="id" open="where userId in(" close=")" separator=",">
#{id}
</foreach>
</select>
<!--
如果传递的是数组的话 那么parameterType依然可以写list
只不过遍历数组的名字 只能写 array
-->
<select id="findUserByIds" parameterType="list" resultType="user">
select * from t_user
<foreach collection="array" item="id" open="where userId in(" close=")" separator=",">
#{id}
</foreach>
</select>
对应关系
一对一
实体
@Data
@AllArgsConstructor
@NoArgsConstructor
public class People {
private Integer pId;
private String pName;
private Integer age;
private Integer idCardId;
private IdCard idCard;
}
@Data
@AllArgsConstructor
@NoArgsConstructor
public class IdCard {
private Integer idCardId;
private String address;
private String idCardNum;
}
Mapper
public interface PeopleMapper {
List<People> list();
}
mapper.xml
<mapper namespace="com.buliyat.qcy.one2one.PeopleMapper">
<!--定义映射关系-->
<resultMap id="listResultMap" type="people">
<id property="pId" column="pId"></id>
<result property="pName" column="pName"></result>
<result property="age" column="age"></result>
<!--定义一对一的关联映射-->
<association property="idCard" javaType="idcard">
<id property="idCardId" column="idCardId"></id>
<result property="address" column="address"></result>
<result property="idCardNum" column="idCardNum"></result>
</association>
</resultMap>
<select id="list" resultMap="listResultMap">
select * from t_people t1, t_idcard t2
where t1.idCardId = t2.idCardId
</select>
</mapper>
测试
public void testList() throws IOException {
SqlSession sqlSession = MyBatisUtils.getSqlSession();
PeopleMapper peopleMapper = sqlSession.getMapper(PeopleMapper.class);
List<People> list = peopleMapper.list();
MyBatisUtils.close();
}
一对多
实体
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Dept {
private Integer deptId;
private String deptName;
private String deptDes;
private List<Emp> empList;
}
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Emp {
private Integer empId;
private String empName;
private Integer age;
private Integer deptId;
}
Mapper
public interface DeptMapper {
List<Dept> list();
}
mapper.xml
<mapper namespace="com.buliyat.qcy.one2many.DeptMapper">
<resultMap id="listResultMap" type="com.buliyat.qcy.one2many.Dept">
<id column="deptId" property="deptId"></id>
<result column="deptName" property="deptName"></result>
<result column="deptDes" property="deptDes"></result>
<collection property="empList" ofType="com.buliyat.qcy.one2many.Emp">
<id column="empId" property="empId"></id>
<result column="empName" property="empName"></result>
<result column="age" property="age"></result>
</collection>
</resultMap>
<select id="list" resultMap="listResultMap">
select * from t_dept t1
left join t_emp t2 on t1.deptId = t2.deptId
</select>
</mapper>
测试
public void testList() throws IOException {
SqlSession sqlSession = MyBatisUtils.getSqlSession();
DeptMapper deptMapper = sqlSession.getMapper(DeptMapper.class);
List<Dept> list = deptMapper.list();
MyBatisUtils.close();
}
懒加载
配置
<settings>
<!--关闭积极加载 打开懒加载-->
<setting name="lazyLoadingEnabled" value="true"/>
<setting name="aggressiveLazyLoading" value="false"/>
</settings>
PeopleLazyMapper.xml
<mapper namespace="com.buliyat.qcy.lazy.PeopleMapper">
<resultMap id="listResultMap" type="com.buliyat.qcy.lazy.People">
<id property="pId" column="pId"></id>
<result property="pName" column="pName"></result>
<result property="age" column="age"></result>
<association property="idCard" javaType="com.buliyat.qcy.lazy.IdCard" column="pId" select="com.buliyat.qcy.lazy.IdCardLazyMapper.findIdCardByPeopleId"></association>
</resultMap>
<select id="list" resultMap="listResultMap">
select * from t_people
</select>
</mapper>
IdCardLazyMapper.xml
<mapper namespace="com.buliyat.qcy.lazy.IdCardMapper">
<select id="findIdCardByPeopleId" parameterType="int" resultType="com.buliyat.qcy.lazy.IdCard">
select * from t_idcard where pId=#{value}
</select>
</mapper>
测试
public class Test01 {
public void testList() throws IOException {
SqlSession sqlSession = MyBatisUtils.getSqlSession();
PeopleMapper peopleMapper = sqlSession.getMapper(PeopleMapper.class);
List<People> list = peopleMapper.list();
System.out.println("---------------------------------------");
System.out.println(list);
list.get(0).getIdCard();
MyBatisUtils.close();
}
}
二级缓存
一级缓存
一级缓存 Session缓存 该缓存的生命周期由Session统一管控 不能跨Session 只能在一个Session内部使用
public static void main(String[] args) throws IOException {
// 第一步:获取 配置文件流
Reader resourceAsReade01r = Resources.getResourceAsReader("mybatis.xml");
// 第二步:创建 SqlSessionFactory对象
SqlSessionFactory sqlSessionFactory01 = new SqlSessionFactoryBuilder().build(resourceAsReader01);
// 第三步:创建 操作数据库对象
SqlSession sqlSession01 = sqlSessionFactory01.openSession();
sqlSession01.selectOne("UserMapper.selectOne", 5);
// sqlSession01.clearCache();
sqlSession01.commit();
sqlSession01.close();
// 第一步:获取 到配置文件流
Reader resourceAsReader02 = Resources.getResourceAsReader("mybatis.xml");
// 第二步:创建 SqlSessionFactory对象
SqlSessionFactory sqlSessionFactory02 = new SqlSessionFactoryBuilder().build(resourceAsReader02);
// 第三步:创建 操作数据库对象
SqlSession sqlSession02 = sqlSessionFactory02.openSession();
sqlSession02.selectOne("UserMapper.selectOne", 5);
sqlSession02.commit();
sqlSession02.close();
}
二级缓存
二级缓存 由程序员管控 需要整合第三方缓存框架 ehcache
导包
<dependency>
<groupId>net.sf.ehcache</groupId>
<artifactId>ehcache-core</artifactId>
<version>2.6.11</version>
</dependency>
<dependency>
<groupId>org.mybatis.caches</groupId>
<artifactId>mybatis-ehcache</artifactId>
<version>1.1.0</version>
</dependency>
ehcache.xml
<ehcache updateCheck="false">
<!--diskStore:缓存路径。ehcache分为内存和磁盘两级,此属性定义磁盘的缓存位置-->
<!--数据于硬盘上的存储位置-->
<diskStore path="G:\\mytemp"/>
<!--
name:缓存名称。
maxElementsInMemory:缓存最大数目
maxElementsOnDisk:硬盘最大缓存个数。
eternal:对象是否永久有效,一但设置了,timeout将不起作用。
overflowToDisk:是否保存到磁盘,当系统宕机时
timeToIdleSeconds:设置对象在失效前的允许闲置时间(单位:秒)。仅当eternal=false对象不是永久有效时使用,可选属性,默认值是0,也就是可闲置时间无穷大。
timeToLiveSeconds:设置对象在失效前允许存活时间(单位:秒)。最大时间介于创建时间和失效时间之间。仅当eternal=false对象不是永久有效时使用,默认是0.,也就是对象存活时间无穷大。
diskPersistent:是否缓存虚拟机重启期数据 Whether the disk store persists between restarts of the Virtual Machine. The default value is false.
diskSpoolBufferSizeMB:这个参数设置DiskStore(磁盘缓存)的缓存区大小。默认是30MB。每个Cache都应该有自己的一个缓冲区。
diskExpiryThreadIntervalSeconds:磁盘失效线程运行时间间隔,默认是120秒。
memoryStoreEvictionPolicy:当达到maxElementsInMemory限制时,Ehcache将会根据指定的策略去清理内存。默认策略是LRU(最近最少使用)。你可以设置为FIFO(先进先出)或是LFU(较少使用)。
clearOnFlush:内存数量最大时是否清除。
memoryStoreEvictionPolicy:可选策略有:LRU(最近最少使用,默认策略)、FIFO(先进先出)、LFU(最少访问次数)。
FIFO,first in first out,这个是大家最熟的,先进先出。
LFU, Less Frequently Used,就是上面例子中使用的策略,直白一点就是讲一直以来最少被使用的。如上面所讲,缓存的元素有一个hit属性,hit值最小的将会被清出缓存。
LRU,Least Recently Used,最近最少使用的,缓存的元素有一个时间戳,当缓存容量满了,而又需要腾出地方来缓存新的元素的时候,那么现有缓存元素中时间戳离当前时间最远的元素将被清出缓存。
-->
<!-- 数据过期策略 -->
<defaultCache
maxElementsInMemory="10000"
eternal="false"
timeToIdleSeconds="120"
timeToLiveSeconds="120"
overflowToDisk="true"
/>
</ehcache>
打开二级缓存
<setting name="cacheEnabled" value="true"/>
mapper.xml
<cache type="org.mybatis.caches.ehcache.EhcacheCache"></cache>
插件使用
导包
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>5.1.4</version>
</dependency>
配置
<plugins>
<plugin interceptor="com.github.pagehelper.PageInterceptor"></plugin>
</plugins>
测试
public class Test01 {
public void testPage() {
SqlSession sqlSession = MyBatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
PageHelper.startPage(3,3);
Page<User> page = (Page<User>) mapper.list();
System.out.println("数据是:"+page.getResult());
System.out.println("一共的条目数:"+page.getTotal());
System.out.println("当前页码:"+page.getPageNum());
System.out.println("一共的页数:"+page.getPages());
System.out.println("每页的数据量:"+page.getPageSize());
MyBatisUtils.close();
}
}