文章目录
1.准备工作
文档:
Mybatis文档
1.依赖:
<dependencies>
<!--Mybatis驱动-->
<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.5.2</version>
</dependency>
<!--Junit-->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>test</scope>
</dependency>
</dependencies>
2.mybatis核心配置文件:
<?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>
3.配置maven 资源过滤(加在pom.xml中):
<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>
否则无法读取配置文件
4.编写Mybatis工具类
每个基于 MyBatis的应用都是以一个SqlSessionFactory 的实例为核心的。SqlSessionFactory的实例可以通过SqlSessionFactoryBuilder获得。而SqlSessionFactoryBuilder则可以从XML配置文件或一个预先配置的 Configuration实例来构建出SqlSessionFactory 实例。
public class MybatisUtils {
private static SqlSessionFactory sqlSessionFactory = null;
static{
//获取sqlSessionFactory对象
try{
String resource = "org/mybatis/example/mybatis-config.xml";
InputStream inputStream = getResourceAsStream(resource);
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
}catch (IOException e){
e.printStackTrace();
}
}
/*
既然有了 SqlSessionFactory,顾名思义,我们可以从中获得 SqlSession 的实例。
SqlSession 提供了在数据库执行 SQL 命令所需的所有方法。你可以通过 SqlSession 实例来直接执行已映射的 SQL 语句。
*/
public static SqlSession getSqlSession(){
return sqlSessionFactory.openSession();
}
}
-
SqlSessionFactoryBuilder
这个类可以被实例化、使用和丢弃,一旦创建了 SqlSessionFactory,就不再需要它了。 因此 SqlSessionFactoryBuilder 实例的最佳作用域是方法作用域(局部变量)。 -
SqlSessionFactory
SqlSessionFactory 一旦被创建就应该在应用的运行期间一直存在,没有任何理由丢弃它或重新创建另一个实例。单例模式。应用作用域(全局)。 -
SqlSession
SqlSession 的实例不是线程安全的,因此是不能被共享的,所以它的最佳的作用域是请求或方法作用域。最好放在finally中关闭。
2.简单实例
1.CRUD
<?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绑定一个对应的Dao/Mapper接口-->
<mapper namespace="com.yang.dao.UserMapper">
<select id="getUserList" resultType="com.yang.pojo.User">
select * from mybatis.user
</select>
<select id="getUserById" resultType="com.yang.pojo.User" parameterType="int">
select * from mybatis.user where id = #{id}
</select>
<insert id="addUser" parameterType="com.yang.pojo.User" >
insert into mybatis.user(id,name,pwd) value(#{id},#{name},#{pwd})
</insert>
<delete id="deleteUser" parameterType="int" >
delete from mybatis.user where id = #{id}
</delete>
<update id="updateUser" parameterType="com.yang.pojo.User" >
update mybatis.user set name = #{name},pwd = #{pwd} where id = #{id}
</update>
</mapper>
insert,update,delete标签没有resultType属性
注意编码问题UTF-8和GBK
测试代码:
package com.yang.dao;
import com.yang.pojo.User;
import com.yang.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.List;
public class UserMapperTest {
@Test
public void test(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
//方法一
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
List<User> userList = userMapper.getUserList();
/*方法二
List<User> userList = sqlSession.selectList("com.yang.dao.UserDao.getUserList");
*/
for(User user:userList){
System.out.println(user);
}
sqlSession.close();
}
@Test
public void testgetUserById(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User user = mapper.getUserById(1);
System.out.println(user);
}
@Test
public void testaddUser(){
User user1 = new User(11,"lcy","2333");
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
mapper.addUser(user1);
sqlSession.commit();
sqlSession.close();
}
@Test
public void testdeleteUser(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
mapper.deleteUser(4);
sqlSession.commit();
sqlSession.close();
}
@Test
public void testupdateUser(){
User user1 = new User(1,"yht","123");
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
mapper.updateUser(user1);
sqlSession.commit();
sqlSession.close();
}
}
2.使用Map传参
当增加或者修改表中值时,可传入对象。但当对象属性过多时且没有无参构造器时,new 对象过于繁琐。可能这时会想到,可以直接传入对应参数,但此时我们不能知道都有哪些值,对应就是不知道一共有几个参数。这样的方法是不利于复用的,所以使用Map解决这样的问题。
map传递参数,直接使用key取出,如下,其中userid,username,userpwd为map中的Key
<insert id="addUserByMap" parameterType="map">
insert into mybatis.user(id,name,pwd) values(#{userid},#{username},#{userpwd})
</insert>
对象传递参数,直接使用属性取出,如下,其中id,name,pwd为User的属性
<insert id="addUser" parameterType="com.yang.pojo.User" >
insert into mybatis.user(id,name,pwd) value(#{id},#{name},#{pwd})
</insert>
只有一个基本类型参数直接在sql中取出,如下,其中id为接口声明方法中的参数,多个使用Map或注解
<select id="getUserById" resultType="com.yang.pojo.User" parameterType="int">
select * from mybatis.user where id = #{id}
</select>
测试:
@Test
public void testaddUserByMap(){
Map<String, Object> map = new HashMap<>();
map.put("userid",6);
map.put("username","yg");
map.put("userpwd","123");
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
mapper.addUserByMap(map);
sqlSession.commit();
sqlSession.close();
}
3.模糊查询:
/*接口方法
List<User> getUserLike(String value);
*/
<select id="getUserLike" resultType="com.yang.pojo.User">
select * from mybatis.user where name like #{value}
</select>
/*sql注入风险
<select id="getUserLike" resultType="com.yang.pojo.User">
select * from mybatis.user where name like "%"#{value}"%"
</select>
*/
测试:
@Test
public void testgetUserLike(){
String value = "%y%";
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<User> users = mapper.getUserLike(value);
for(User user:users){
System.out.println(user);
}
sqlSession.close();
}
3.配置文件解析
- 环境变量(environments)
MyBatis 可以配置成适应多种环境,尽管可以配置多个环境,但每个SqlSessionFactory实例只能选择一种环境。
<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>
- 事务管理器(transactionManager)
在 MyBatis 中有两种类型的事务管理器(也就是 type="[JDBC|MANAGED]")
- JDBC – 这个配置直接使用了 JDBC 的提交和回滚设施,它依赖从数据源获得的连接来管理事务作用域。
- MANAGED 这个配置几乎没做什么,默认情况下它会关闭连接。然而一些容器并不希望连接被关闭,
- 数据源(dataSource)
有三种内建的数据源类型(也就是 type="[UNPOOLED|POOLED|JNDI]")
默认POOLED
- properties
- 可以引入外部配置文件
- 内部可以增加属性配置
- 外部配置文件优先级更高
db.properties:
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/mybatis?useSSl=true&useUnicode=true&characterEncoding=UTF-8
username=root
password=123456
<properties resource="db.properties"/>
<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>
- 类型别名(typeAliases)
类型别名可为 Java 类型设置一个缩写名字。它仅用于XML配置,意在降低冗余的全限定类名书写。例如:
<typeAliases>
<package name="domain.blog"/>
</typeAliases>
或
<typeAliases>
<typeAlias alias="Author" type="domain.blog.Author"/>
<typeAlias alias="Blog" type="domain.blog.Blog"/>
<typeAlias alias="Comment" type="domain.blog.Comment"/>
<typeAlias alias="Post" type="domain.blog.Post"/>
<typeAlias alias="Section" type="domain.blog.Section"/>
<typeAlias alias="Tag" type="domain.blog.Tag"/>
</typeAliases>
实例:
<typeAliases>
<typeAlias type="com.yang.pojo.User" alias = "user"/>
</typeAliases>
或
<typeAliases>
<!--
<typeAlias type="com.yang.pojo.User" alias = "user"/>
-->
<package name="com.yang.pojo"/>
</typeAliases>
每一个在包 domain.blog 中的 Java Bean,在没有注解的情况下,会使用 Bean 的首字母小写的非限定类名来作为它的别名。
@Alias("user")
public class User(){
}
- 映射器(mappers)
方法一:(推荐)
<!-- 使用相对于类路径的资源引用 -->
<mappers>
<mapper resource="org/mybatis/builder/AuthorMapper.xml"/>
<mapper resource="org/mybatis/builder/BlogMapper.xml"/>
<mapper resource="org/mybatis/builder/PostMapper.xml"/>
</mappers>
方法二:
<mappers>
<mapper class="org.mybatis.builder.AuthorMapper"/>
<mapper class="org.mybatis.builder.BlogMapper"/>
<mapper class="org.mybatis.builder.PostMapper"/>
</mappers>
方法三:
<mappers>
<package name="org.mybatis.builder"/>
</mappers>
方法二和三,接口和配置文件要在同一包下,还要同名
4.关于数据库字段名和对象属性名不一致的问题
问题描述:
schams:mybatis
table:user(id,name,pwd)
User{
id
name
password
}
result:
User{id=1, name='yht', pwd='null'}
问题分析:
sql语句:
select id,name,pwd from mybatis.user where id = #{id}
我认为是因为从表中查出的结果和对象属性无法对应,所以password = null。
解决方法:
方法一:
<select id="getUserById" resultType="user" parameterType="int">
select id,name,pwd as password from mybatis.user where id = #{id}
</select>
方法二:
<resultMap id="UserMap" type="user">
<result column="id" property="id"/>
<result column="name" property="name"/>
<result column="pwd" property="password"/>
</resultMap>
上述不需要手动声明映射的原因是,在这些情况下,MyBatis 会在幕后自动创建一个ResultMap,再根据属性名来映射列到 JavaBean 的属性上。
5.日志
- STDOUT_LOGGING
<settings>
<setting name="logImpl" value="STDOUT_LOGGING"/>
</settings>
显示:
Created connection 1583159071.
Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@5e5d171f]
==> Preparing: select id,name,pwd from mybatis.user where id = ?
==> Parameters: 1(Integer)
<== Columns: id, name, pwd
<== Row: 1, yht, 123
<== Total: 1
User{id=1, name='yht', password='123'}
- Log4J
- 导入依赖
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
- 配置信息
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/yang.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
- 创建对象,调用方法
static Logger logger = Logger.getLogger(UserMapperTest.class);
括号内为调用此方法的类的反射对象
6.分页
- sql语句 limit
select * from user limit 0,2
limit 第一个参数为startIndex,第二个为pageSize
。若只赋予一个参数,则为[0,parameter].
实例:
<select id="getUserByPage" resultMap="UserMap" parameterType="map">
select * from mybatis.user limit #{startIndex},#{pageSize}
</select>
测试:
@Test
public void testgetUserByPage(){
Map<String, Object> map = new HashMap<>();
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
map.put("startIndex",0);
map.put("pageSize",2);
List<User> users = mapper.getUserByPage(map);
for(User user:users)
System.out.println(user);
sqlSession.close();
}
- RowRounds
实例:
select id="getUserByRowBounds" resultMap="UserMap">
select * from mybatis.user
</select>
测试:
@Test
public void testgetUserByPage(){
Map<String, Object> map = new HashMap<>();
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
map.put("startIndex",0);
map.put("pageSize",2);
List<User> users = mapper.getUserByPage(map);
for(User user:users)
System.out.println(user);
sqlSession.close();
}
7.使用注解
实例:
@Select("select * from user")
List<User> getUserList();
绑定接口:
<mappers>
<mapper class="com.yang.dao.UserMapper"/>
</mappers>
测试:
@Test
public void testGetUserList(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<User> userList = mapper.getUserList();
for(User user:userList){
System.out.println(user);
}
sqlSession.close();
}
存在的问题:
User{id=1, name='yht', password='null'}
User{id=2, name='xl', password='null'}
User{id=3, name='ls', password='null'}
User{id=6, name='yg', password='null'}
User{id=10, name='qsy', password='null'}
使用xml时,可以用resultmap来解决这个问题。
其他CRUD实例:
public interface UserMapper {
@Select("select * from user")
List<User> getUserList();
@Insert("insert into user(id,name,pwd) value(#{id},#{name},#{password})")
int addUser(User user);
@Delete("delete from user where id = #{uid}")
int deleteUser(@Param("uid") int id);
@Update("update mybatis.user set name = #{name},pwd = #{password} where id = #{id}")
int updateUser(User user);
@Select("select * from user where id = #{uid} and name = #{uname}")
User getUserByIdAndName(@Param("uid") int id,@Param("uname") String name);
}
测试:
@Test
public void testaddUser(){
User user = new User();
user.setId(19);
user.setName("twenty");
user.setPassword("twenty");
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
mapper.addUser(user);
sqlSession.commit();
sqlSession.close();
}
@Test
public void testdeleteUser(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
mapper.deleteUser(19);
sqlSession.commit();
sqlSession.close();
}
@Test
public void updateUser(){
User user = new User();
user.setId(20);
user.setName("Twenty");
user.setPassword("Twenty");
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
mapper.updateUser(user);
sqlSession.commit();
sqlSession.close();
}
@Test
public void testgetUserByIdAndName(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User yht = mapper.getUserByIdAndName(1, "yht");
System.out.println(yht);
sqlSession.close();
}
典型错误:当有两个基本类型的参数时,没有试用版@Para(""),则会产生错误如下:
Error querying database. Cause: org.apache.ibatis.binding.BindingException: Parameter ‘id’ not found. Available parameters are [arg1, arg0, param1, param2]
关于Lombok插件的使用
- 依赖
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.22</version>
</dependency>
- 注解
@Data
@AllArgsConstructor
@NoArgsConstructor
8. 多对一 & 一对多
1. 多对一
环境
public class Student {
private int id;
private String name;
private Teacher teacher;
}
public class Teacher {
private int id;
private String name;
}
方法一:按照查询嵌套处理
<select id="getStuTeaInfo" resultMap="stutea">
select * from mybatis.student
</select>
<select id="getTeacherList" resultType="teacher">
select * from mybatis.teacher where id = #{id}
</select>
<resultMap id="stutea" type="student">
<result property="id" column="id"/>
<result property="name" column="name"/>
<association property="teacher" column="tid" javaType="teacher" select="getTeacherList"/>
</resultMap>
测试:
public void testgetStuTeaInfo(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
List<Student> stuTeaInfo = mapper.getStuTeaInfo();
for (Student student : stuTeaInfo) {
System.out.println(student);
}
sqlSession.close();
}
结果:
Student(id=1, name=zs, teacher=Teacher(id=1, name=yht))
Student(id=2, name=小红, teacher=Teacher(id=1, name=yht))
Student(id=3, name=小张, teacher=Teacher(id=1, name=yht))
Student(id=4, name=小李, teacher=Teacher(id=1, name=yht))
Student(id=5, name=小王, teacher=Teacher(id=1, name=yht))
补充:
在这里有个疑问,留作以后解答
- namesapce作用?如果namespace仅为实现其中的某一个接口的方法,那为什么其他接口的方法在这里也可以实现?(同时我们发现例如在Studentmapper.xml中虽对TeacherMapper的方法有实现,但是当使用TeacherMapper对象调用这个方法时是无效的。)
方法二:按照结果嵌套处理
<select id="getStuTeaInfo2" resultMap="stutea2">
select s.id sname,s.name sname,t.name tname
from student s,teacher t
where t.id = s.tid
</select>
<resultMap id="stutea2" type="student">
<result property="sid" column="id"/>
<result property="sname" column="name"/>
<association property="teacher" javaType="teacher">
<result property="name" column="tname"/>
</association>
</resultMap>
测试:
@Test
public void testgetStuTeaInfo2(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
List<Student> stuTeaInfo = mapper.getStuTeaInfo2();
for (Student student : stuTeaInfo) {
System.out.println(student);
}
sqlSession.close();
}
结果:
Student(id=0, name=null, teacher=Teacher(id=0, name=yht))
Student(id=0, name=null, teacher=Teacher(id=0, name=yht))
Student(id=0, name=null, teacher=Teacher(id=0, name=yht))
Student(id=0, name=null, teacher=Teacher(id=0, name=yht))
Student(id=0, name=null, teacher=Teacher(id=0, name=yht))
id = 0,是因为查询的结果中没有teacher id字段
2.一对多
环境:
public class Student {
private int id;
private String name;
private int tid;
}
public class Teacher {
private int id;
private String name;
private List<Student> students;
}
方法一:按照结果嵌套处理
<select id="getTeaStuInfoBytid" resultMap="teastu" parameterType="int">
select t.id tid, s.id sid , s.name sname, t.name tname
from teacher t, student s
where s.tid = t.id and t.id = #{id}
</select>
<resultMap id="teastu" 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>
测试:
@Test
public void testgetTeaStuInfoBytid(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
List<Teacher> teaStuInfoBytid = mapper.getTeaStuInfoBytid(1);
for (Teacher teacher : teaStuInfoBytid) {
System.out.println(teacher);
}
sqlSession.close();
}
结果:
Teacher(id=1, name=yht, students=[Student(id=1, name=zs, tid=1), Student(id=2, name=小红, tid=1), Student(id=3, name=小张, tid=1), Student(id=4, name=小李, tid=1), Student(id=5, name=小王, tid=1)])
方法二:按照查询嵌套处理
9.动态sql
- if元素
<select id="queryBlogIf" parameterType="map" resultType="blog">
select * from mybatis.blog
<where>
<if test="title != null">
title = #{title}
</if>
<if test="author != null">
and author = #{author}
</if>
</where>
</select>
测试:
@Test
public void testqueryBlogIf(){
Map<String, String> map = new HashMap<>();
SqlSession sqlSession = MybatisUtils.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
map.put("title","数据结构");
map.put("author","yangguang");
List<Blog> blogs = mapper.queryBlogIf(map);
for (Blog blog : blogs) {
System.out.println(blog);
}
sqlSession.close();
}
其中 title 和 author 为map中的key.
2. where元素
where 元素只会在子元素返回任何内容的情况下才插入 “WHERE” 子句。而且,若子句的开头为 “AND” 或 “OR”,where 元素也会将它们去除。
- choose元素(类似于switch)
<select id="queryBlogChoose" parameterType="map" resultType="blog">
select * from mybatis.blog
<where>
<choose>
<when test="title != null">
and title = #{title}
</when>
<when test="author != null">
and author = #{author}
</when>
<otherwise>
and views = #{views}
</otherwise>
</choose>
</where>
</select>
测试:
@Test
public void testqueryBlogChoose(){
HashMap map = new HashMap<>();
SqlSession sqlSession = MybatisUtils.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
map.put("title","数据结构");
map.put("author","yangguang");
map.put("views",9999);
List<Blog> blogs = mapper.queryBlogChoose(map);
for (Blog blog : blogs) {
System.out.println(blog);
}
sqlSession.close();
}
- set元素
<update id="updateBlog" parameterType="blog">
update mybatis.blog
<set>
<if test="author != null">
author = #{author},
</if>
<if test="title != null">
title = #{title},
</if>
</set>
where id = #{id}
</update>
测试:
@Test
public void testupdateBlog(){
Blog blog = new Blog();
blog.setId("1");
blog.setAuthor("yg");
blog.setTitle("数据结构");
SqlSession sqlSession = MybatisUtils.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
mapper.updateBlog(blog);
sqlSession.close();
}
- trim定制前后缀覆盖
例如:
where元素可以描述为:
<trim prefix = "where" prefixOverrides = "AND|OR">
</trim>
set元素可以描述为:
<trim prefix = "set" suffixOverrides = ",">
</trim>
- foreach元素
<select id="queryBlogForEach" parameterType="map" resultType="Blog">
select * from mybatis.blog
<where>
<foreach collection="ids" item="id1" open="and (" close=")" separator="or">
id = #{id1}
</foreach>
</where>
</select>
如上,collection为map中的key ids,item只是用来下面传值.
注意 and ( 有空格.
测试:
@Test
public void testqueryBlogForEach(){
HashMap map = new HashMap<>();
ArrayList<Integer> arrlist = new ArrayList<>();
arrlist.add(1);
arrlist.add(2);
map.put("ids",arrlist);
SqlSession sqlSession = MybatisUtils.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
List<Blog> blogs = mapper.queryBlogForEach(map);
for (Blog blog : blogs) {
System.out.println(blog);
}
sqlSession.close();
}
动态sql还是sql语句,只不过是在sql的层面上去执行逻辑代码.即根据不同的情况生成不同的sql语句.
10.缓存
1. 一级缓存
映射语句文件中的所有 select 语句的结果将会被缓存。
映射语句文件中的所有 insert、update 和 delete 语句会刷新缓存。
缓存会使用最近最少使用算法(LRU, Least Recently Used)算法来清除不需要的缓存。
缓存不会定时进行刷新(也就是说,没有刷新间隔)。
实例:
@Test
public void testgetUserById(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User user = mapper.getUserById(1);
User user1 = new User();
user1.setId(2);
user1.setName("xule");
user1.setPwd("1123");
mapper.updateUser(user1);
User user2 = mapper.getUserById(1);
if(user == user2){
System.out.println("true");
}else{
System.out.println("false");
}
sqlSession.close();
}
结果:
Preparing: select * from mybatis.user where id = ?
==> Parameters: 1(Integer)
<== Columns: id, name, pwd
<== Row: 1, yht, 123
<== Total: 1
==> Preparing: update mybatis.user set name = ? ,pwd = ? where id = ?
==> Parameters: xule(String), 1123(String), 2(Integer)
<== Updates: 1
==> Preparing: select * from mybatis.user where id = ?
==> Parameters: 1(Integer)
<== Columns: id, name, pwd
<== Row: 1, yht, 123
<== Total: 1
false
2.二级缓存
在mybatis中使用二级缓存时候就必须需要将实体类序列化
@Test
public void testgetUserById(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User user = mapper.getUserById(1);
sqlSession.close();
SqlSession sqlSession1 = MybatisUtils.getSqlSession();
UserMapper mapper1 = sqlSession1.getMapper(UserMapper.class);
User user1 = mapper1.getUserById(1);
System.out.println(user == user1);
sqlSession1.close();
}
结果:
==> Preparing: select * from mybatis.user where id = ?
==> Parameters: 1(Integer)
<== Columns: id, name, pwd
<== Row: 1, yht, 123
<== Total: 1
缓存顺序:
2 -> 1 -> 查数据库表