Mybatis入门
Mybatis相关jar包
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>x.x.x</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>x.x.x</version>
</dependency>
核心配置文件
<?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"></properties>
<!--别名设置-->
<typeAliases>
<package name="com.narrat.pojo"/>
</typeAliases>
<!--数据连接池配置-->
<!--environments和environment的id必须一致-->
<environments default="development">
<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>
</environments>
<!--SQL映射路径-->
<mappers>
<mapper resource="com/narrat/Mapper/BlogMapper.xml"/>
</mappers>
</configuration>
Mapper.xml文件SQL语句映射
- namespace:命名空间一定不要忘记
<?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">
<!--BlogMapper接口-->
<mapper namespace="com.narrat.Mapper.BlogMapper">
<select id="selectBlog" resultType="Blog">
select * from Blog where id = #{id}
</select>
</mapper>
Mapper接口
public interdface Mapper{
Blog selectBlog(int id);
}
测试运行
- 数据添加/修改/删除时,需要在流关闭前,进行提交事务commit()
public class test{
@Test
public void test() throws IOException {
InputStream in = null;
in = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(in);
SqlSession sqlSession = sqlSessionFactory.openSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
Blog blog = mapper.selectBlog(1);
System.out.println(blog);
//sqlSession.commit();
in.close();
sqlSession.close();
}
}
Select
属性:
- id:命名空间中唯一的标识符
- parameterType:传入SQL语句的参数类型
- resultType:SQL语句返回结果类型
- resultMap:SQL语句返回集合结果类型,resultType和resultMap只能同时使用一个
- id:标记出作为 ID 的结果
- constructor:有参构造属性注入
- association:复杂类型的关联
- collection:复杂类型的集合
<select id="selectPerson" parameterType="int" resultType="hashmap">
SELECT * FROM Blog WHERE ID = #{id}
</select>
<!--constructor-->
<constructor>
<idArg column="id" javaType="int" name="id" />
<arg column="age" javaType="_int" name="age" />
<arg column="username" javaType="String" name="username" />
</constructor>
<!--association-->
<resultMap id="blogResult" type="Blog">
<id property="id" column="blog_id" />
<result property="title" column="blog_title"/>
<association property="author" javaType="Author">
<id property="id" column="author_id"/>
<result property="username" column="author_username"/>
<result property="password" column="author_password"/>
<result property="email" column="author_email"/>
<result property="bio" column="author_bio"/>
</association>
</resultMap>
<!--collection-->
<resultMap id="blogResult" type="Blog">
<id property="id" column="blog_id" />
<result property="title" column="blog_title"/>
<collection property="posts" ofType="Post">
<id property="id" column="post_id"/>
<result property="subject" column="post_subject"/>
<result property="body" column="post_body"/>
</collection>
</resultMap>
模糊查询
<select id="selectPerson" parameterType="int" resultType="hashmap">
SELECT * FROM Blog WHERE name like #{name}
</select>
- 拼接"%"模糊查询,不安全,存在SQL注入问题
<select id="selectPerson" parameterType="int" resultType="hashmap">
SELECT * FROM Blog WHERE name like "%"#{name}"%"
</select>
分页查询
<select id="selectPerson" parameterType="int" resultType="hashmap">
SELECT * FROM Blog limit #{start},#{pageSize}
</select>
Insert
<insert id="insertAuthor">
insert into Blog (id,username,password,email,bio)
values (#{id},#{username},#{password},#{email},#{bio})
</insert>
Update
属性:
- id
- parameterType
<update id="updateAuthor">
update Blog set
username = #{username},
password = #{password},
email = #{email},
bio = #{bio}
where id = #{id}
</update>
Delete
<delete id="deleteAuthor">
delete from books where id = #{id}
</delete>
动态SQL
if
<select id="findActiveBlogWithTitleLike"
resultType="Blog">
SELECT * FROM BLOG
WHERE state = ‘ACTIVE’
<if test="title != null">
AND title like #{title}
</if>
</select>
where
搭配if标签使用,当前SQL语句动态条件时使用;若子句的开头为 “AND” 或 “OR”,where 元素也会将它们去除。
<select id="findActiveBlogLike" resultType="Blog">
SELECT * FROM BLOG
<where>
<if test="state != null">
state = #{state}
</if>
<if test="title != null">
AND title like #{title}
</if>
<if test="author != null and author.name != null">
AND author_name like #{author.name}
</if>
</where>
</select>
choose when otherwise
类似Java 中switch循环语句
<select id="findActiveBlogLike"
resultType="Blog">
SELECT * FROM BLOG WHERE state = ‘ACTIVE’
<choose>
<when test="title != null">
AND title like #{title}
</when>
<when test="author != null and author.name != null">
AND author_name like #{author.name}
</when>
<otherwise>
AND featured = 1
</otherwise>
</choose>
</select>
set
set 元素会动态地在行首插入 SET 关键字,并会删掉额外的逗号
<update id="updateAuthorIfNecessary">
update Author
<set>
<if test="username != null">username=#{username},</if>
<if test="password != null">password=#{password},</if>
<if test="email != null">email=#{email},</if>
<if test="bio != null">bio=#{bio}</if>
</set>
where id=#{id}
</update>
foreach
对集合进行遍历(尤其是在构建 IN 条件语句的时候)
<select id="selectPostIn" resultType="domain.blog.Post">
SELECT *
FROM POST P
<where>
<foreach item="item" index="index" collection="list"
open="ID in (" separator="," close=")" nullable="true">
#{item}
</foreach>
</where>
</select>
缓存
一级缓存:本地缓存,默认开启
二级缓存
SQL映射文件配置
<cache
eviction="FIFO"
flushInterval="60000"
size="512"
readOnly="true"/>
- eviction(清除策略):
- LRU:最近最少使用:移除最长时间不被使用的对象。(默认)
- FIFO:先进先出:按对象进入缓存的顺序来移除它们。
- SOFT:软引用:基于垃圾回收器状态和软引用规则移除对象。
- WEAK:弱引用:更积极地基于垃圾收集器状态和弱引用规则移除对象。
- lushInterval(刷新间隔):设置为任意的正整数,以毫秒为单位,默认情况是不设置,也就是没有刷新间隔,缓存仅仅会在调用语句时刷新。
- size(引用数目):任意正整数,默认值是 1024。
- readOnly(只读): true 或 false,默认值是 false。
二级缓存是事务性的。当 SqlSession 完成并提交时,或是完成并回滚,但没有执行 flushCache=true 的 insert/delete/update 语句时,缓存会获得更新。
日志
Log4j
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
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
Log4j2
<dependency>
<groupId>org.apache.logging.log4j</groupId>
<artifactId>log4j-core</artifactId>
<version>2.x.x</version>
</dependency>
log4j2.xml配置文件
<?xml version="1.0" encoding="UTF-8"?>
<Configuration xmlns="http://logging.apache.org/log4j/2.0/config">
<Appenders>
<Console name="stdout" target="SYSTEM_OUT">
<PatternLayout pattern="%5level [%t] - %msg%n"/>
</Console>
</Appenders>
<Loggers>
<Logger name="org.mybatis.example.BlogMapper" level="trace"/>
<Root level="error" >
<AppenderRef ref="stdout"/>
</Root>
</Loggers>
</Configuration>
注解开发
替代Mapper.xml映射文件的配置,直接注解在接口方法上
- @Select
- @Insert
- @Update
- @Delete
@Param
- 方法只有一个普通参数,可以不使用@Param
- 多个参数时,推荐使用@Param
- 实体类对象,不能使用@Param