1.pom.xml中的maven依赖,maven防止过滤
<dependencies>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.47</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.2</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>test</scope>
</dependency>
</dependencies>
//在父,子pom.xml里都需要加入一下代码,不然不会被maven过滤
<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>
2.配置resource文件夹下的mybatis-config核心配置文件。
<?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"/>
<!--开启日志/驼峰命名-->
<settings>
<setting name="logImpl" value="STDOUT_LOGGING"/>
<setting name="mapUnderscoreToCamelCase" value="true"></setting>
</settings>
<!--开启简化命名,不需再写全限类名了-->
<typeAliases>
<package name="com.jyy.pojo"/> <!--方式1:整个包下都不需再写-->
<typeAlias type="com.it.pojo.User" alias="user"/> <!--方式2-->
<!--方式3:注解,在User类加入注解@Alias("user")-->
</typeAliases>
<environments default="development"> <!--可以配置多套环境,default为默认-->
<environment id="development">
<transactionManager type="JDBC"/> <!--事务管理器-->
<dataSource type="POOLED"> <!--数据源-->
<property name="driver" value="${driver}"/>
<property name="url" value="${url}"/>
<property name="username" value="${username}"/>
<property name="password" value="${password}"/>
</dataSource>
</environment>
<environment id="test">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${driver}"/>
<property name="url" value="${url}"/>
<property name="username" value="${username}"/>
<property name="password" value="${password}"/>
</dataSource>
</environment>
</environments>
<!--resource和class都可配置,效果一样-->
<mappers>
<mapper resource="com/jyy/dao/BlogMapper.xml"></mapper>
<mapper class="com.jyy.dao.BlogMapper"></mapper>
</mappers>
</configuration>
settings中可选的属性
<settings>
<setting name="cacheEnabled" value="true"/> <!--开启缓存-->
<setting name="lazyLoadingEnabled" value="true"/> <!--懒加载-->
<setting name="multipleResultSetsEnabled" value="true"/>
<setting name="useColumnLabel" value="true"/>
<setting name="useGeneratedKeys" value="false"/>
<setting name="autoMappingBehavior" value="PARTIAL"/>
<setting name="autoMappingUnknownColumnBehavior" value="WARNING"/>
<setting name="defaultExecutorType" value="SIMPLE"/>
<setting name="defaultStatementTimeout" value="25"/>
<setting name="defaultFetchSize" value="100"/>
<setting name="safeRowBoundsEnabled" value="false"/>
<setting name="mapUnderscoreToCamelCase" value="false"/>
<setting name="localCacheScope" value="SESSION"/>
<setting name="jdbcTypeForNull" value="OTHER"/>
<setting name="lazyLoadTriggerMethods" value="equals,clone,hashCode,toString"/>
</settings>
3.sql配置文件(8.0以上加时区 UTC)
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/mybatis?useSSL=false&useUnicode=true&characterEncoding=UTF-8
username=root
password=111111
4.可以编写uitils目录下工具类,SSM之后可以不需要了
package com.jyy.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 factory;
static{
try {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
factory = new SqlSessionFactoryBuilder().build(inputStream);
} catch (IOException e) {
e.printStackTrace();
}
}
public static SqlSession getSqlSession(){
return factory.openSession(true); //true参数表示开启事务
}
}
5.Dao下的mapper接口和mapper.xml
package com.jyy.dao;
import com.jyy.pojo.User;
import java.util.List;
public interface UserDao {
List<User> getUserList();
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">
<!--namespace映射mapper接口-->
<mapper namespace="com.jyy.dao.UserDao">
<select id="getUserList" resultType="com.jyy.pojo.User">
select * from mybatis.user
</select>
<select id="getUserById" resultType="com.jyy.pojo.User" parameterType="int">
select * from mybatis.user where id = #{id}
</select>
<!--User可以直接映射pojo下的user对象-->
<insert id="addUser" parameterType="com.jyy.pojo.User">
insert into Mybatis.user (id, name, pwd) values (#{id}, #{name}, #{pwd})
</insert>
<update id="updateUser" parameterType="com.jyy.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>
</mapper>
6.万能map类注入,直接通过key传参
int updateUserByMap(Map<String,Object> map);
<update id="updateUserByMap" parameterType="map">
update mybatis.user set pwd = #{userpwd} where id = #{userid} ;
</update>
测试时:
map.put("userid",1);
map.put("userpwd","111111");
mapper.updateUserByMap(map);
7.模糊查询,两种方法
方式一:推荐
<select id="getUserListByLike" resultType="com.it.pojo.User">
select * from mybatis.user where name like #{value} ;
</select>
value传入 "%张三%"
方式二:不推荐,存在sql注入问题
<select id="getUserListByLike" resultType="com.it.pojo.User">
select * from mybatis.user where name like "%"#{value}"%" ;
</select>
value传入 "张三"
8.ResultMap结果集映射:数据库中user表的属性名是pwd,pojo中类的属性名是password
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.it.Dao.UserMapper">
<resultMap id="UserMap" type="User">
<!-- column:对应数据库表中的列的名字,property:应类的属性名字。-->
<result column="pwd" property="password"/>
<result column="id" property="id"/>
<result column="name" property="name"/>
</resultMap>
<!--添加一个resultMap标签-->
<select id="getUserById" resultMap="UserMap">
select * from mybatis.user where id = #{id}
</select>
</mapper>
9.日志
(1)使用STDOUT_LOGGING默认日志工厂
<!-- 使用标准的默认的日志工厂-->
<settings>
<setting name="logImpl" value="STDOUT_LOGGING"/>
</settings>
(2)使用LOG4J日志工厂
maven依赖
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
在resource文件夹中创建log4j.properties文件
#将等级为DEBUG的日志信息输出到console和file这两个目的地,console和file的定义在下面的代码
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.RollingFileAppender
log4j.appender.file.File=./log/kuang.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
#日志输出级别
log4j.logger.org.mybatis=DEBUG
log4j.logger.java.sql=DEBUG
log4j.logger.java.sql.Statement=DEBUG
log4j.logger.java.sql.ResultSet=DEBUG
log4j.logger.java.sql.PreparedStatement=DEBUG
在mybatis-config.xml中加入配置
<settings>
<setting name="logImpl" value="LOG4J"/>
</settings>
使用:
public class UserDaoTest {
// log4j,获取类字节码
static Logger logger = Logger.getLogger(UserDaoTest.class);
@Test
public void getUserById(){
// 第一步获取SqlSession对象
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User user = mapper.getUserById(1);
System.out.println(user);
// 最后一步关闭sqlSession
sqlSession.close();
}
@Test
public void testLog4j(){
//日志级别
logger.info("info进入了:testLog4j");
logger.debug("debug进入了:testLog4j");
logger.error("error进入了:testLog4j");
}
}
10.分页
方法1:接口中的方法和mapper.xml中的写法:测试时直接传入map即可
List<User> getUserByLimit(Map<String,Integer> map);
<select id="getUserByLimit" resultMap="UserMap" parameterType="map">
select * from mybatis.user limit #{startIndex},#{pageSize}
</select>
方法2:RowBounds分页。不推荐,已经过时
11.注解开发
mappers配置
<!--每一个Mapper.xml都需要在mybatis-config.xml核心配置文件中注册。注解就用class。class的路径和resource的路径不同,class(.不加.xml),否则就写resource(/加.xml)。Mapper.xml和接口在同一包下且必须同名,用clss。-->
<mappers>
<mapper class="com.it.Dao.UserMapper"/>
</mappers>
package com.it.Dao;
import com.it.pojo.User;
import org.apache.ibatis.annotations.*;
import java.util.List;
import java.util.Map;
public interface UserMapper {
// 查询全部用户
@Select("select * from user")
List<User> getUserList();
// 存在多个参数
@Select("select * from user where id = #{id} and pwd = #{pwd}")
User getUserByIdPwd(@Param("id")int id,@Param("pwd") String pwd);
// 增:返回值类型只能用int不能用User,否则会报错org.apache.ibatis.binding.BindingException: Mapper method 'com.it.Dao.UserMapper.addUser' has an unsupported return type: class com.it.pojo.User
@Insert("insert into user(id,name,pwd) values (#{id},#{name},#{pwd})")
int addUser(User user);
// 改 @Param,只要有基本类型参数,有几个参数就写几个@Param。
@Update("update user set name=#{name} where id=#{id}")
int updateUser(@Param("name") String name,@Param("id") int id);
// 删
@Delete("delete from user where id = #{id}")
int deleteUser(@Param("id") int id);
}
12.Lombok
首先得安装:IDEA中:File→Settings→Plugins→搜索Lombok
引入Lombok的maven依赖:
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.8</version>
</dependency>
使用方法:
@Getter@Setter
@FieldNameConstants
@ToString
@EqualsAndHashCode
@AllArgsConstructor(所有参数构造)
@NoArgsConstructor(无参构造)
@Data(相当于添加了@Getter@Setter@ToString@NoArgsConstructor(无参构造))
package com.it.pojo;
import lombok.AllArgsConstructor;
import lombok.Data;
@Data
@AllArgsConstructor
@NoArgsConstructor
public class User {
private int id;
private String name;
private String pwd;
}
13.多对一:association标签
多个学生关联一个老师(让学生关联老师)
Student类和Teacher类
package com.jyy.pojo;
import lombok.Data;
@Data
public class Teacher {
private int id;
private String name;
}
package com.it.pojo;
import lombok.Data;
@Data
public class Student {
private int id;
private String name;
//通过多个学生查老师。此处使用老师类型作为参数,意为返回一个老师对象。
private Teacher teacher;
}
TeacherMapper和StudentMapper接口
public interface StudentMapper {
public List<Student> getStudent();
public List<Student> getStudent2();
}
public interface TeacherMapper {
// @Select("select * from mybatis.teacher where id = #{tid}")
Teacher getTeacher(@Param("tid") int id);
}
方式1:查询嵌套处理
因为Student类中包含teacher类,需要用resultMap映射
<select id="getStudent" resultMap="StudentTeacher">
select * from mybatis.student
</select>
<resultMap id="StudentTeacher" type="Student">
<result property="id" column="id"></result>
<result property="name" column="name"></result>
<association property="teacher" column="tid" javaType="Teacher" select="getTeacher">
</association>
</resultMap>
<select id="getTeacher" resultType="Teacher">
select * from mybatis.teacher where id = #{tid}
</select>
方式2:按结果嵌套处理,相比更简单
<select id="getStudent2" resultMap="StudentTeacher2">
select s.id sid, s.name sname, t.name tname, t.id tid
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"/>
<result property="id" column="tid"/>
</association>
</resultMap>
14.一对多:collection标签
多个学生关联一个老师(让老师关联学生)
Student类和Teacher类
import lombok.Data;
@Data
public class Student {
private int id;
private String name;
private int tid;
}
import lombok.Data;
import java.util.List;
@Data
public class Teacher {
private int id;
private String name;
private List<Student> students;
}
TeacherMapper接口
public interface TeacherMapper {
Teacher getTeacher(@Param("tid") int id);
}
配置mapper.xml:
<select id="getTeacher" resultMap="StudentTeacher">
select s.id sid, s.name sname, t.id tid, t.name tname
from student s, teacher t
where s.tid = t.id and t.id = #{tid}
</select>
<resultMap id="StudentTeacher" type="Teacher">
<result property="id" column="tid" />
<result property="name" column="tname" />
<collection property="students" ofType="Student">
<result property="id" column="sid" />
<result property="name" column="sname" />
<result property="tid" column="tid" />
</collection>
</resultMap>
15.动态SQL
(1)Where-IF语句
<!--Where-IF语句。有了where标签,当只满足title时,语句前面的and会被忽略-->
<select id="queryBlogIF" parameterType="map" resultType="blog">
select * from blog
<where>
<if test="id != null">
--如果id !=null,就追加一个and id=#{id}
id = #{id}
</if>
<if test="title != null">
and title = #{title}
</if>
<if test="author != null">
and author = #{author}
</if>
<if test="create_time != null">
and create_time = #{create_time}
</if>
<if test="views != null">
and views = #{views}
</if>
</where>
</select>
(2)Where-choose-when-otherwise语句
<!--Where-choose-when-otherwise语句。当传入的参数都不在when中存在时,就执行otherwise-->
<select id="queryBlogChoose" parameterType="map" resultType="blog">
select * from blog
<where>
<choose>
<when test="id != null">id = #{id}</when>
<when test="title != null">and title = #{title}</when>
<when test="author != null">and author = #{author}</when>
<when test="create_time != null">and create_time = #{create_time}</when>
<when test="views != null">and views = #{views}</when>
<otherwise>
1=1
</otherwise>
</choose>
</where>
</select>
(3)set-if语句
<!--set-if语句更新Blog-->
<update id="updateBlog" parameterType="map">
update mybatis.blog
<set>
<if test="title != null">title = #{title},</if>
<if test="author != null">author = #{author},</if>
<if test="create_time != null">create_time = #{create_time},</if>
<if test="views != null">views = #{views}</if>
</set>
where id = #{id}
</update>
(4)SQL片段
<!--将每次都要编写的IF语句提取出来,id随便取-->
<sql id = "sql-if">
<if test="title != null">title = #{title},</if>
<if test="author != null">author = #{author},</if>
<if test="create_time != null">create_time = #{create_time},</if>
<if test="views != null">views = #{views}</if>
</sql>
<select id="queryBlogIF" parameterType="map" resultType="blog">
select * from blog
<where>
<!--引用提取出来的SQL片段SQL-->
<include refid="sql-if"/>
</where>
</select>
(5)for-each
<!--查询where 1=1 and (id=0 or id=2 or id=3)用foreach-->
<select id="queryBlogForEach" parameterType="map" resultType="blog">
select * from blog
<where>
<!--类似集合的foreach循环。foreach标签内容分别为:给定一个集合的名字,将取出的内容给定一个名字,开始是的样子and (,结尾的样子),分开分隔是or。id和#{id}对应-->
<foreach clloection="list" item="id" open="and (" close=")" separator="or"> id = #{id} </foreach>
</where>
</select>
16.缓存
一级缓存:
一级缓存默认开启,只在一次sqlSession中有效,也就是从getSqlSession()到close()的过程
失效情况:(1)查询不同东西
(2)增删改语句就会可能改变sql的内容。因此,执行完增删改之后,就会重新执行sql,刷新缓存。
(3)查询不同的Mapper.xml
(4)手动清理缓存:sqlSession.clearCache();
二级缓存:
步骤:
(1)首先,确保开启<setting>中的cacheEnabled,默认是开启的true
(2)在想要开启的mapper.xml中加入cache标签,即开启;也可以设置参数配置
<cache/>
<cache
eviction="FIFO"
flushInterval="60000"
size="512"
readOnly="true"/>
查询顺序:先找二级缓存,再找一级缓存,如果都没有,就去连接数据库
自定义缓存:
(1)导入依赖
<dependency>
<groupId>org.mybatis.caches</groupId>
<artifactId>mybatis-ehcache</artifactId>
<version>1.1.0</version>
</dependency>
(2)配置mapper.xml
<cache type="org.mybatis.caches.encache.EhcacheCache"/>
(3)在resource中创建ehcache.xml文件
<?xml version="1.0" encoding="UTF-8"?>
<ehcache xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="http://ehcache.org/ehcache.xsd"
updateCheck="false">
<defaultCache
eternal="false"
maxElementsInMemory="10000"
overflowToDisk="false"
diskPersistent="false"
timeToIdleSeconds="1800"
timeToLiveSeconds="259200"
memoryStoreEvictionPolicy="LRU"/>
<cache
name="cloud_user"
eternal="false"
maxElementsInMemory="5000"
overflowToDisk="false"
diskPersistent="false"
timeToIdleSeconds="1800"
timeToLiveSeconds="1800"
memoryStoreEvictionPolicy="LRU"/>
<!--
diskStore:为缓存路径,ehcache分为内存和磁盘两级,此属性定义磁盘的缓存位置。参数解释如下:
user.home – 用户主目录
user.dir – 用户当前工作目录
java.io.tmpdir – 默认临时文件路径
-->
<diskStore path="java.io.tmpdir/Tmp_EhCache"/>
<!--
defaultCache:默认缓存策略,当ehcache找不到定义的缓存时,则使用这个缓存策略。只能定义一个。
-->
<!--
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,最近最少使用的,缓存的元素有一个时间戳,当缓存容量满了,而又需要腾出地方来缓存新的元素的时候,那么现有缓存元素中时间戳离当前时间最远的元素将被清出缓存。
-->
</ehcache>
reference:
1. https://github.com/Davis-Samuel/Mybatis-order
2. 狂神说java:https://www.bilibili.com/video/BV1NE411Q7Nx?p=28
3.Mybatis中文手册:https://mybatis.org/mybatis-3/zh/index.html