文章目录
一、搭建MyBatis
1. 创建maven工程
配置打包方式、导入jar包
<?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.long.learn</groupId>
<artifactId>mybatis_demo1</artifactId>
<version>1.0-SNAPSHOT</version>
<packaging>jar</packaging>
<dependencies> <!-- Mybatis核心 -->
<!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.6</version>
</dependency>
<!-- https://mvnrepository.com/artifact/junit/junit -->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>test</scope>
</dependency>
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.21</version>
</dependency>
</dependencies>
<properties>
<maven.compiler.source>8</maven.compiler.source>
<maven.compiler.target>8</maven.compiler.target>
</properties>
</project>
2. 创建mybatis的核心配置文件
通常命名为mybatis-config.xml,非强制性。
其中environments
标签中包含了事务管理和连接池
的配置信息。
mappers
包含了 SQL 代码和映射定义信息
。
<?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.cj.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/mybatis"/>
<property name="username" value="root"/>
<property name="password" value="root"/>
</dataSource>
</environment>
</environments>
<!--引入映射文件-->
<mappers>
<mapper resource="org/mybatis/example/BlogMapper.xml"/>
</mappers>
</configuration>
3. 创建mapper接口
- 首先创建实体类:
package com.atguigu.mybatis.pojo;
public class User {
private Integer id;
private String username;
private String password;
private Integer age;
private String sex;
private String email;
public User() {
}
public User(Integer id, String username, String password, Integer age, String sex, String email) {
this.id = id;
this.username = username;
this.password = password;
this.age = age;
this.sex = sex;
this.email = email;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", password='" + password + '\'' +
", age=" + age +
", sex='" + sex + '\'' +
", email='" + email + '\'' +
'}';
}
}
- 再创建mapper接口,mapper接口类似于dao,但是不需要实现实现类实现接口。
package com.atguigu.mybatis.mapper;
public interface UserMapper {
int insertUser();
}
- 在resources目录中创建mappers目录,在其中创建Mapper.xml文件与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">
<!--1、namespace中输入对用接口的全类名-->
<mapper namespace="com.atguigu.mybatis.mapper.UserMapper">
<!--2、id中输入对应的方法名-->
<insert id="insertUser">
insert into t_user values(null,'admin','123456',23,'男','123456@qq.com');
</insert>
</mapper>
- 在配置文件中引入映射文件
<?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.cj.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/mybatis"/>
<property name="username" value="root"/>
<property name="password" value="root"/>
</dataSource>
</environment>
</environments>
<!--3、引入映射文件-->
<mappers>
<mapper resource="mappers/UserMapper.xml"/>
</mappers>
</configuration>
目录结构如下:
4. 测试接口
package com.atguigu.mybatis.test;
import com.atguigu.mybatis.mapper.UserMapper;
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 org.junit.Test;
import java.io.IOException;
import java.io.InputStream;
public class UserMapperTest {
@Test
public void testInsertUser() throws IOException {
//以流的形式获取配置文件
InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
//获取SqlSessionFactory对象
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//获取SqlSession对象,当参数为true时,表示开启自动提交事务
SqlSession sqlSession = sqlSessionFactory.openSession(true);
//通过SqlSession对象返回Mapper接口的实例
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
int result = mapper.insertUser();
//提交事务
// sqlSession.commit();
System.out.println("影响行数:"+result);
}
}
5. 加入日志功能(log4j)
- 导入依赖
<!-- log4j日志 -->
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
- resource目录下创建配置文件log4j.xml
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE log4j:configuration SYSTEM "log4j.dtd">
<log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/">
<appender name="STDOUT" class="org.apache.log4j.ConsoleAppender">
<param name="Encoding" value="UTF-8"/>
<layout class="org.apache.log4j.PatternLayout">
<param name="ConversionPattern" value="%-5p %d{MM-dd HH:mm:ss,SSS} %m (%F:%L) \n"/>
</layout>
</appender>
<logger name="java.sql">
<level value="debug"/>
</logger>
<logger name="org.apache.ibatis">
<level value="info"/>
</logger>
<root>
<!--FATAL(致命)>ERROR(错误)>WARN(警告)>INFO(信息)>DEBUG(调试)-->
<level value="debug"/>
<appender-ref ref="STDOUT"/>
</root>
</log4j:configuration>
二、实现增删改查
<?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 namespace="com.atguigu.mybatis.mapper.UserMapper">
<!--id中输入对应的方法名-->
<!--int insertUser();-->
<insert id="insertUser">
insert into t_user
values (null, 'admin', '123456', 23, '男', '123456@qq.com');
</insert>
<!-- void updateUser();-->
<update id="updateUser">
update t_user
set username='张三'
where id = 1;
</update>
<!--void deleteUser()-->
<delete id="deleteUser">
delete
from t_user
where id = 1;
</delete>
<!--查询必须设置resultType或resultMap
resultType:默认映射关系(自动将表中的字段和javabean中的属性一一对应,如果有就赋值,没有就舍弃,适用于字段名与属性名一致的情况)
resultMap:自定义映射关系(自定义字段和属性之间的映射关系)
-->
<!-- User queryUserById()-->
<select id="queryUserById" resultType="com.atguigu.mybatis.pojo.User">
select *
from t_user
where id = 2;
</select>
<!--List<User> queryAllUser()-->
<select id="queryAllUser" resultType="User">/*因为mybatis配置文件中设置了typeAliases标签,所以该resultType可以写别名*/
select *
from t_user;
</select>
</mapper>
三、配置文件解析
资源文件(resources下):
jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/mybatis?serverTimezone=UTC
jdbc.username=root
jdbc.password=root
配置文件(resources下):
<?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?,settings?,typeAliases?,typeHandlers?,objectFactory?,objectWrapperFactory?,
reflectorFactory?,plugins?,environments?,databaseIdProvider?,mappers?
-->
<!--引入配置文件-->
<properties resource="jdbc.properties"/>
<!--设置类型别名-->
<typeAliases>
<typeAlias type="com.atguigu.mybatis.pojo.User" alias="User"/><!--alias为别名,如果不写默认为类名(User),且不区分大小写-->
<package name="com.atguigu.mybatis.pojo"/><!--为某个包下所有的类型设置默认的别名,即类名且不区分大小写-->
</typeAliases>
<!--事务与连接信息的配置-->
<environments default="development"><!--environments可以设置使用哪个数据库-->
<environment id="development">
<transactionManager type="JDBC"/><!--transactionManager表示使用哪种事务规则
JDBC:表示使用原生的JDBC原生事务管理方式,需要手动进行提交与或回滚
MANAGED:被管理,例如spring
-->
<dataSource type="POOLED"><!--配置数据源
POOLED:使用数据库连接池缓存数据库连接
UNPOOLED:不使用数据库连接池
JNDI:使用上下文中的数据源
-->
<property name="driver" value="com.mysql.cj.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/mybatis?serverTimezone=UTC"/>
<property name="username" value="root"/>
<property name="password" value="root"/>
</dataSource>
</environment>
<environment id="test">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${jdbc.driver}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</dataSource>
</environment>
</environments>
<!--引入映射文件-->
<mappers>
<!-- <mapper resource="mappers/UserMapper.xml"/>-->
<!--
以包为单位引入映射文件:
要求:
1、mapper接口要和映射文件的名字一致
2、mapper接口所在的包路径和映射文件的包路径要一致
注:在resources中创建包时,用/分割不是.
-->
<package name="com.atguigu.mybatis.mapper"/>
</mappers>
</configuration>
四、Mybatis获取参数值的三种参数
- ${}:字符串拼接(可能造成sql注入问题)
- #{}:占位符赋值
1. mapper接口方法的参数为单个字面量类型
可以通过${}
和#{}
以任意名称获取参数值,一般直接用参数名,需要注意${}字符串拼接需要加上单引号
。
也可以使用@Param来自定义参数名,详情在2.3。
<?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.atguigu.mybatis.mapper.ParameterMapper">
<!--List<User> getUserByUsername(String username);-->
<select id="getUserByUsername" resultType="User">
<!--方式一: select * from t_user where username = '${username}'-->
<!--方式二-->
select * from t_user where username= #{username};
</select>
</mapper>
2. mapper接口方法的参数有多个时
2.1 默认map集合
默认mybatis会将这些参数放在一个map集合中以两种方式进行存储:
- 以arg0,arg1…为键,参数为值
- 以param1,param2…为键,参数为值
<?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.atguigu.mybatis.mapper.ParameterMapper">
<!--User login(String username,String password);-->
<select id="login" resultType="user">
<!--方式一: select * from t_user where username='${arg0}' and password='${arg1}';-->
<!--方式二: select * from t_user where username=#{arg0} and password=#{arg1};-->
<!--方式三: select * from t_user where username='${param1}' and password='${param2}';-->
<!--方式四:-->
select * from t_user where username=#{param1} and password=#{param2}
</select>
</mapper>
2.2 自定义map集合
也可以手动对将这些参数放在一个map中存储,键名可以自定义,只需要以#{键名}或 键名的形式访问即可,但是需要注意 {键名}的形式访问即可,但是需要注意 键名的形式访问即可,但是需要注意{键名}需要加单引号。
<?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.atguigu.mybatis.mapper.ParameterMapper">
<!-- User loginForMap(Map<String,Object> map);-->
<select id="loginForMap" resultType="user">
select * from t_user where username=#{username} and password=#{password};
</select>
</mapper>
测试:
package com.atguigu.mybatis.mapper;
import com.atguigu.mybatis.pojo.User;
import com.atguigu.mybatis.utils.SqlSessionUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class TestParameterMapper {
@Test
public void testLoginForMap() {
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
ParameterMapper mapper = sqlSession.getMapper(ParameterMapper.class);
Map<String,Object> map = new HashMap<>();
//map自定义键名,在sql语句中直接使用
map.put("username","admin");
map.put("password","123456");
User user = mapper.loginForMap(map);
System.out.println(user);
}
}
2.3 使用@Param注解自定义map键名(推荐)
- 以@Param注解的值为键
- 以Param1,Param2…为键
<?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.atguigu.mybatis.mapper.ParameterMapper">
<!--User loginForParam(@Param("username") String username, @Param("password") String password);-->
<select id="loginForParam" resultType="user">
<!--方式一: select * from t_user where username='${username}' and password='${password}';-->
<!--方式二: select * from t_user where username=#{username} and password=#{password};-->
<!--方式三: select * from t_user where username='${param1}' and password='${param2}';-->
<!--方式四: select * from t_user where username=#{param1} and password=#{param2}-->
<!--推荐方式二:-->
select * from t_user where username=#{username} and password=#{password};
</select>
</mapper>
3. 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">
<mapper namespace="com.atguigu.mybatis.mapper.ParameterMapper">
<!-- int insertUser(User user);-->
<insert id="insertUser">
<!--方式一:insert into t_user
values (null, #{username}, #{password}, #{age}, #{sex}, #{email});-->
<!--方式二:-->
insert into t_user
values (null, '${username}', '${password}', '${age}', '${sex}', '${email}');
</insert>
</mapper>
五、Mybatis的查询功能
1. 查询实体类对象
- 若查询出的数据只有一条,可以通过
实体类
对象或者集合
接收 - 若查询出的数据有多条,一定
不能通过实体类对象接收
,只能以List集合
来接收,此时会抛异常TooManyResultsException
<?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.atguigu.mybatis.mapper.SelectMapper">
<!-- User getUserById(@Param("id") Integer id);-->
<select id="getUserById" resultType="User">
select * from t_user where id=#{id}
</select>
<!--List<User> getAllUser();-->
<select id="getAllUser" resultType="User">
select * from t_user
</select>
</mapper>
2. 查询单行单列的值
返回Java.lang.Integet、Java.lang.String等类型时,Mybatis已经取好了别名,别名就是为Integet、String等忽略大小写。
<?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.atguigu.mybatis.mapper.SelectMapper">
<!-- Integer getCount();-->
<select id="getCount" resultType="int">
select count(*) from t_user
</select>
</mapper>
3. 查询map集合
当只要查询一行数据时,直接返回一个map集合即可,一般在没有对应实体类的时候使用。
<?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.atguigu.mybatis.mapper.SelectMapper">
<!--Map<String,Object> getUserByIdToMap(@Param("id") Integer id); 查找用户,用Map进行封装-->
<select id="getUserByIdToMap" resultType="Map">
select * from t_user where id=#{id}
</select>
</mapper>
当要返回多行数据时,可以用List封装Map或者在方法上标注@MapKet()注解
。
Mapper接口:
package com.atguigu.mybatis.mapper;
import com.atguigu.mybatis.pojo.User;
import org.apache.ibatis.annotations.MapKey;
import org.apache.ibatis.annotations.Param;
import java.util.List;
import java.util.Map;
public interface SelectMapper {
/**
* 查询所有用户数据
* @return
*/
List<Map<String,Object>> getAllUserToMap1();
/*
[User{id=2, username='admin', password='123456', age=23, sex='男', email='123456@qq.com'},
User{id=3, username='1', password='123456', age=23, sex='男', email='123456@qq.com'},
User{id=4, username='2', password='123456', age=23, sex='男', email='123456@qq.com'},
User{id=5, username='3', password='123456', age=23, sex='男', email='123456@qq.com'},
User{id=8, username='张三', password='123456', age=13, sex='男', email='123@qq.com'},
User{id=9, username='lisi', password='123456', age=13, sex='男', email='123@qq.com'}]
*/
/**
* 查询所有用户信息
* @return
*/
@MapKey("id")//用唯一的键id标识Map,Map中按id为键,以每条记录转换的Map对象为值进行存储
Map<String,Object> getAllUserToMap2();
/*
{2=User{id=2, username='admin', password='123456', age=23, sex='男', email='123456@qq.com'},
3=User{id=3, username='1', password='123456', age=23, sex='男', email='123456@qq.com'},
4=User{id=4, username='2', password='123456', age=23, sex='男', email='123456@qq.com'},
5=User{id=5, username='3', password='123456', age=23, sex='男', email='123456@qq.com'},
8=User{id=8, username='张三', password='123456', age=13, sex='男', email='123@qq.com'},
9=User{id=9, username='lisi', password='123456', age=13, sex='男', email='123@qq.com'}}
*/
}
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.atguigu.mybatis.mapper.SelectMapper">
<!--List<Map<String,Object>> getAllUserToMap1();-->
<select id="getAllUserToMap1" resultType="User">
select * from t_user
</select>
<!--Map<String,Object> getAllUserToMap2();-->
<select id="getAllUserToMap2" resultType="User">
select * from t_user
</select>
</mapper>
六、特殊SQL的执行
1. 模糊查询
<?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.atguigu.mybatis.mapper.SqlMapper">
<!--List<User> getUserByLikeUsername(@Param("username") String username); 根据用户名模糊查询找用户-->
<select id="getUserByLikeUsername" resultType="User">
<!--方式一: select * from t_user where username like '%${username}%'-->
<!--方式二:select * from t_user where username like concat('%',#{username},'%')-->
<!--方式三:推荐-->
select * from t_user where username like "%"#{username}"%"
</select>
</mapper>
2. 批量删除
<?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.atguigu.mybatis.mapper.SqlMapper">
<!--Integer deleteMore(@Param("ids") String ids); 批量删除-->
<delete id="deleteMore">
<!--不能用#{},因为会自动加上'',sql格式错误-->
delete from t_user where id in(${ids})
</delete>
</mapper>
3. 动态设置表名
<?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.atguigu.mybatis.mapper.SqlMapper">
<!--List<User> getParamByTableName(@Param("tableName") String tableName); 查询指定表中的数据-->
<select id="getParamByTableName" resultType="User">
<!--不能用#{},因为会自动加上'',sql格式错误-->
select * from ${tableName}
</select>
</mapper>
4. 插入数据时获取自增的主键
在插入数据时,自增的主键时不需要赋值的,插入以后自增的主键未知,如果还需要对此数据进行操作将会很麻烦,用这个方法可以获得自增的主键,方便后续进行操作。
<?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.atguigu.mybatis.mapper.SqlMapper">
<!--Integer insertUserGetId(User user); 插入数据时,获取自增的id装配到User对象中-->
<!--
useGeneratedKeys:使用生成的主键
keyProperty:将主键的值赋值给id属性
-->
<insert id="insertUserGetId" useGeneratedKeys="true" keyProperty="id">
insert into t_user values(null,#{username},#{password},#{age},#{sex},#{email})
</insert>
</mapper>
测试:
package com.atguigu.mybatis.mapper;
import com.atguigu.mybatis.pojo.User;
import com.atguigu.mybatis.utils.SqlSessionUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.List;
public class TestSqlMapper {
@Test
public void testInsertUserGetId() {
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
SqlMapper mapper = sqlSession.getMapper(SqlMapper.class);
User user = new User(null,"王五","123",12,"男","123@qq.com");
System.out.println(user);//User{id=null, username='王五', password='123', age=12, sex='男', email='123@qq.com'}
mapper.insertUserGetId(user);
System.out.println(user);//User{id=10, username='王五', password='123', age=12, sex='男', email='123@qq.com'}
}
}
七、解决属性名与字段名不一致的问题
如下表为一个dept(部门)对应多个emp(员工)
员工表:
部门表:
JavaBean:
Emp:
package com.atguigu.mybatis.pojo;
public class Emp {
private Integer eid;
private String empName;
private Integer age;
private String sex;
private String email;
public Emp() {
}
public Emp(Integer eid, String empName, Integer age, String sex, String email) {
this.eid = eid;
this.empName = empName;
this.age = age;
this.sex = sex;
this.email = email;
}
public Integer getEid() {
return eid;
}
public void setEid(Integer eid) {
this.eid = eid;
}
public String getEmpName() {
return empName;
}
public void setEmpName(String empName) {
this.empName = empName;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
@Override
public String toString() {
return "Emp{" +
"eid=" + eid +
", empName='" + empName + '\'' +
", age=" + age +
", sex='" + sex + '\'' +
", email='" + email + '\'' +
'}';
}
}
Dept:
package com.atguigu.mybatis.pojo;
public class Dept {
private Integer did;
private String deptName;
public Dept() {
}
public Dept(Integer did, String deptName) {
this.did = did;
this.deptName = deptName;
}
public Integer getDid() {
return did;
}
public void setDid(Integer did) {
this.did = did;
}
public String getDeptName() {
return deptName;
}
public void setDeptName(String deptName) {
this.deptName = deptName;
}
@Override
public String toString() {
return "Dept{" +
"did=" + did +
", deptName='" + deptName + '\'' +
'}';
}
}
不难发现,数据库中的字段名和JavaBean中有些属性名是不一样的,比如数据库中字段名的emp_name对应的属性名是empName,此时就会造成Emp对象的EmpName属性无法赋值的情况,解决方法如下:
1. 给字段起别名
<?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.atguigu.mybatis.mapper.EmpMapper">
<!-- List<Emp> getAllEmp();-->
<select id="getAllEmp" resultType="emp">
<!--方式一:给字段起别名-->
select eid,emp_name empName,age,sex,email from t_emp
</select>
</mapper>
2. 设置mybatis的全局配置
mybatis配置文件,加入<settings>标签,开启下划线转驼峰命名。
<?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"/>
<!--设置mybatis的全局配置-->
<settings>
<!--mapUnderscoreToCamelCase意思为下划线变驼峰
规则为去除下划线,将下划线后一个字母变为大写
-->
<setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>
<typeAliases>
<package name="com.atguigu.mybatis.pojo"/>
</typeAliases>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${jdbc.driver}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</dataSource>
</environment>
</environments>
<mappers>
<package name="com.atguigu.mybatis.mapper"/>
</mappers>
</configuration>
此时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.atguigu.mybatis.mapper.EmpMapper">
<!-- List<Emp> getAllEmp();-->
<select id="getAllEmp" resultType="emp">
select eid,emp_name,age,sex,email from t_emp
</select>
</mapper>
3. 通过resultMap进行映射
<?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.atguigu.mybatis.mapper.EmpMapper">
<!--type:设置映射关系中的实体类类型-->
<resultMap id="empResultMap" type="Emp">
<!--property:属性名,必须是type实体类中的属性名
column:字段名,必须是sql语句查询出来的字段名
-->
<!--id用户设置主键,唯一标识,不能重复-->
<id property="eid" column="eid"/>
<!--result用于设置普通字段-->
<result property="empName" column="emp_name"/>
<result property="age" column="age"/>
<result property="sex" column="sex"/>
<result property="email" column="email"/>
</resultMap>
<!--List<Emp> getAllEmpForResultMap();-->
<select id="getAllEmpForResultMap" resultMap="empResultMap">
select eid,emp_name,age,sex,email from t_emp
</select>
</mapper>
八、处理多对一的映射问题
JavaBean:
Emp:
package com.atguigu.mybatis.pojo;
public class Emp {
private Integer eid;
private String empName;
private Integer age;
private String sex;
private String email;
private Dept dept;
public Emp(Integer eid, String empName, Integer age, String sex, String email, Dept dept) {
this.eid = eid;
this.empName = empName;
this.age = age;
this.sex = sex;
this.email = email;
this.dept = dept;
}
@Override
public String toString() {
return "Emp{" +
"eid=" + eid +
", empName='" + empName + '\'' +
", age=" + age +
", sex='" + sex + '\'' +
", email='" + email + '\'' +
", dept=" + dept +
'}';
}
public Dept getDept() {
return dept;
}
public void setDept(Dept dept) {
this.dept = dept;
}
public Emp() {
}
public Integer getEid() {
return eid;
}
public void setEid(Integer eid) {
this.eid = eid;
}
public String getEmpName() {
return empName;
}
public void setEmpName(String empName) {
this.empName = empName;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
}
Dept:
package com.atguigu.mybatis.pojo;
import java.util.List;
public class Dept {
private Integer did;
private String deptName;
public Dept() {
}
public Dept(Integer did, String deptName) {
this.did = did;
this.deptName = deptName;
}
public Integer getDid() {
return did;
}
public void setDid(Integer did) {
this.did = did;
}
public String getDeptName() {
return deptName;
}
public void setDeptName(String deptName) {
this.deptName = deptName;
}
@Override
public String toString() {
return "Dept{" +
"did=" + did +
", deptName='" + deptName + '\'' +
'}';
}
}
1. 通过级联属性赋值
<?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.atguigu.mybatis.mapper.EmpMapper">
<resultMap id="empAndDeptResultMap" type="Emp">
<id property="eid" column="eid"/>
<result property="empName" column="emp_name"/>
<result property="age" column="age"/>
<result property="sex" column="sex"/>
<result property="email" column="email"/>
<!--级联赋值-->
<result property="dept.did" column="did"/>
<result property="dept.deptName" column="dept_name"/>
</resultMap>
<!--Emp getEmpByIdOne(@Param("id") Integer id); 根据id查询员工信息-->
<select id="getEmpByIdOne" resultMap="empAndDeptResultMap">
select *
from t_emp
left join t_dept on t_emp.did = t_dept.did
where eid = #{id}
</select>
</mapper>
2. 通过association标签
<?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">
<resultMap id="empAndDeptResultMapTwo" type="Emp">
<id property="eid" column="eid"/>
<result property="empName" column="emp_name"/>
<result property="age" column="age"/>
<result property="sex" column="sex"/>
<result property="email" column="email"/>
<!--属性dept对应实体类Dept-->
<association property="dept" javaType="Dept">
<id property="did" column="did"/>
<result property="deptName" column="dept_name"/>
</association>
</resultMap>
<!--Emp getEmpByIdOne(@Param("id") Integer id);-->
<select id="getEmpByIdOne" resultMap="empAndDeptResultMapTwo">
select *
from t_emp
left join t_dept on t_emp.did = t_dept.did
where eid = #{id}
</select>
</mapper>
3. 分步查询(常用)
Emp:
Mapper接口:
package com.atguigu.mybatis.mapper;
import com.atguigu.mybatis.pojo.Emp;
import org.apache.ibatis.annotations.Param;
import java.util.List;
public interface EmpMapper {
/**
* 通过分步查询员工以及员工所对应的部门信息
* 分步第一步:根据eid查询员工信息
* @return
*/
Emp getEmpAndDeptByStepOne(Integer eid);
}
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.atguigu.mybatis.mapper.EmpMapper">
<resultMap id="empAndDeptStepResultMap" type="Emp">
<id property="eid" column="eid"/>
<result property="empName" column="emp_name"/>
<result property="age" column="age"/>
<result property="sex" column="sex"/>
<result property="email" column="email"/>
<!--
以column为条件去select中执行sql并将结果赋值给property属性
column:分步查询的条件
select:设置分步查询的sql的唯一标识
-->
<association property="dept" column="did" select="com.atguigu.mybatis.mapper.DeptMapper.getEmpAndDeptByStepTwo">
</association>
</resultMap>
<!--Emp getEmpAndDeptByStepOne(Integer eid);-->
<select id="getEmpAndDeptByStepOne" resultMap="empAndDeptStepResultMap">
select *
from t_emp
where eid = #{eid}
</select>
</mapper>
Dept:
Mapper接口:
package com.atguigu.mybatis.mapper;
import com.atguigu.mybatis.pojo.Dept;
public interface DeptMapper {
/**
* 分步查询第二步,根据did查询出Dept对象
* @param did
* @return
*/
Dept getEmpAndDeptByStepTwo(Integer did);
}
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.atguigu.mybatis.mapper.DeptMapper">
<resultMap id="deptResultMap" type="Dept">
<id property="did" column="did"/>
<result property="deptName" column="dept_name"/>
</resultMap>
<!-- Dept getEmpAndDeptByStepTwo(Integer did);-->
<select id="getEmpAndDeptByStepTwo" resultMap="deptResultMap">
select * from t_dept where did = #{did}
</select>
</mapper>
3.1 分步查询的延迟加载
分步查询的优点:可以实现延迟加载,但是必须在核心配置文件中设置全局配置信息:
lazyLoadingEnabled
:延迟加载的全局开关。当开启时,所有关联对象都会延迟加载
aggressiveLazyLoading
:当开启时,任何方法的调用都会加载该对象的所有属性。 否则,每个属性会按需加载
此时就可以实现按需加载,获取的数据是什么,就只会执行相应的sql。此时可通过association和collection中的fetchType属性设置当前的分步查询是否使用延迟加载,fetchType=“lazy(延迟加载)|eager(立即加载)”
开启方式一:通过mybatis配置文件,开启全局延迟加载
<!--设置mybatis的全局配置-->
<settings>
<setting name="mapUnderscoreToCamelCase" value="true"/>
<!--开启全局延迟加载-->
<setting name="lazyLoadingEnabled" value="true"/>
</settings>
开启方式二:通过fetchType标签,开启全局延迟加载后,想单独控制控制是否开启延迟加载就可以设置,fetchType=“eager(立即加载)”,没有开启全局延迟加载时,也可使用fetchType="lazy(延迟加载)"来打开延迟加载。
<resultMap id="empAndDeptStepResultMap" type="Emp">
<id property="eid" column="eid"/>
<result property="empName" column="emp_name"/>
<result property="age" column="age"/>
<result property="sex" column="sex"/>
<result property="email" column="email"/>
<!--
column:分步查询的条件
select:设置分步查询的sql的唯一表示
-->
<association property="dept" column="did"
select="com.atguigu.mybatis.mapper.DeptMapper.getEmpAndDeptByStepTwo"
fetchType="lazy"><!--开启延迟加载-->
</association>
</resultMap>
效果:
开启延迟加载前:当我们没有用到dept时,也会执行完所有查询步骤
@Test
public void testGetEmpAndDeptByStep() {
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
EmpMapper mapper = sqlSession.getMapper(EmpMapper.class);
Emp emp = mapper.getEmpAndDeptByStepOne(1);
System.out.println(emp.getEmpName());
}
/*
DEBUG 09-18 10:02:00,140 ==> Preparing: select * from t_emp where eid = ? (BaseJdbcLogger.java:137)
DEBUG 09-18 10:02:00,160 ==> Parameters: 1(Integer) (BaseJdbcLogger.java:137)
DEBUG 09-18 10:02:00,180 ====> Preparing: select * from t_dept where did = ? (BaseJdbcLogger.java:137)
DEBUG 09-18 10:02:00,180 ====> Parameters: 1(Integer) (BaseJdbcLogger.java:137)
DEBUG 09-18 10:02:00,180 <==== Total: 1 (BaseJdbcLogger.java:137)
DEBUG 09-18 10:02:00,180 <== Total: 1 (BaseJdbcLogger.java:137)
张三
*/
开启延迟加载后:当我们没有用到dept时,将不会执行查询dept的步骤。
@Test
public void testGetEmpAndDeptByStep() {
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
EmpMapper mapper = sqlSession.getMapper(EmpMapper.class);
Emp emp = mapper.getEmpAndDeptByStepOne(1);
System.out.println(emp.getEmpName());
}
/*
DEBUG 09-18 10:03:44,343 ==> Preparing: select * from t_emp where eid = ? (BaseJdbcLogger.java:137)
DEBUG 09-18 10:03:44,363 ==> Parameters: 1(Integer) (BaseJdbcLogger.java:137)
DEBUG 09-18 10:03:44,413 <== Total: 1 (BaseJdbcLogger.java:137)
张三
*/
等用到dept的时候才会执行查询dept的步骤
@Test
public void testGetEmpAndDeptByStep() {
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
EmpMapper mapper = sqlSession.getMapper(EmpMapper.class);
Emp emp = mapper.getEmpAndDeptByStepOne(1);
System.out.println(emp.getEmpName());
System.out.println(emp.getDept());
}
/*
DEBUG 09-18 10:05:03,264 ==> Preparing: select * from t_emp where eid = ? (BaseJdbcLogger.java:137)
DEBUG 09-18 10:05:03,284 ==> Parameters: 1(Integer) (BaseJdbcLogger.java:137)
DEBUG 09-18 10:05:03,334 <== Total: 1 (BaseJdbcLogger.java:137)
张三
DEBUG 09-18 10:05:03,334 ==> Preparing: select * from t_dept where did = ? (BaseJdbcLogger.java:137)
DEBUG 09-18 10:05:03,334 ==> Parameters: 1(Integer) (BaseJdbcLogger.java:137)
DEBUG 09-18 10:05:03,334 <== Total: 1 (BaseJdbcLogger.java:137)
Dept{did=1, deptName=' 开发部门'}
*/
九、处理一对多的映射问题
JavaBean:
Dept:
package com.atguigu.mybatis.pojo;
import java.util.List;
public class Dept {
private Integer did;
private String deptName;
private List<Emp> emps;
public Dept(Integer did, String deptName, List<Emp> emps) {
this.did = did;
this.deptName = deptName;
this.emps = emps;
}
public Dept() {
}
public Dept(Integer did, String deptName) {
this.did = did;
this.deptName = deptName;
}
@Override
public String toString() {
return "Dept{" +
"did=" + did +
", deptName='" + deptName + '\'' +
", emps=" + emps +
'}';
}
public List<Emp> getEmps() {
return emps;
}
public void setEmps(List<Emp> emps) {
this.emps = emps;
}
public Integer getDid() {
return did;
}
public void setDid(Integer did) {
this.did = did;
}
public String getDeptName() {
return deptName;
}
public void setDeptName(String deptName) {
this.deptName = deptName;
}
}
Emp结构与表结构跟标题八一致。
1. 通过collection标签
<?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.atguigu.mybatis.mapper.DeptMapper">
<resultMap id="deptResultMap" type="Dept">
<id property="did" column="did"/>
<result property="deptName" column="dept_name"/>
<!--collection是专门处理集合的标签
ofType表示集合中的类型
-->
<collection property="emps" ofType="Emp">
<id property="eid" column="eid"/>
<result property="empName" column="emp_name"/>
<result property="age" column="age"/>
<result property="sex" column="sex"/>
<result property="email" column="email"/>
</collection>
</resultMap>
<!--Dept getDeptAndEmp();-->
<select id="getDeptAndEmp" resultMap="deptResultMap">
SELECT
*
FROM
t_dept d
LEFT JOIN t_emp e ON d.did = e.did
WHERE
d.did = #{did}
</select>
</mapper>
2. 分步查询
DeptMapper接口:
package com.atguigu.mybatis.mapper;
import com.atguigu.mybatis.pojo.Dept;
import org.apache.ibatis.annotations.Param;
public interface DeptMapper {
/**
* 通过分步处理获取部门和员工数据第一步
* @return
*/
Dept getDeptAndEMpStepOne();
}
DeptMapper.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.atguigu.mybatis.mapper.DeptMapper">
<resultMap id="deptStepResultMap" type="Dept">
<id property="did" column="did"/>
<result property="deptName" column="dept_name"/>
<!--以did参数,开启延迟加载,分步二调用getDeptAndEMpStepTwo赋值给epms-->
<association property="emps" column="did"
fetchType="lazy"
select="com.atguigu.mybatis.mapper.EmpMapper.getDeptAndEMpStepTwo">
</association>
</resultMap>
<!--Dept getDeptAndEMpStepOne();-->
<select id="getDeptAndEMpStepOne" resultMap="deptStepResultMap">
SELECT
*
FROM
t_dept
WHERE
did = #{did};
</select>
</mapper>
EmpMapper接口:
package com.atguigu.mybatis.mapper;
import com.atguigu.mybatis.pojo.Emp;
import org.apache.ibatis.annotations.Param;
import java.util.List;
public interface EmpMapper {
/**
* 分步处理查询部门与员工信息第二步
* @return
*/
Emp getDeptAndEMpStepTwo(@Param("did") Integer did);
}
EmpMapper.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.atguigu.mybatis.mapper.EmpMapper">
<!--type:设置映射关系中的实体类类型-->
<resultMap id="empResultMap" type="Emp">
<!--property:属性名,必须是type实体类中的属性名
column:字段名,必须是sql语句查询出来的字段名
-->
<!--id用户设置主键,唯一标识,不能重复-->
<id property="eid" column="eid"/>
<!--result用于设置普通字段-->
<result property="empName" column="emp_name"/>
<result property="age" column="age"/>
<result property="sex" column="sex"/>
<result property="email" column="email"/>
</resultMap>
<!--Emp getDeptAndEMpStepTwo(@Param("did") Integer did);-->
<select id="getDeptAndEMpStepTwo" resultMap="empResultMap">
SELECT
*
FROM
t_emp
WHERE
did = #{did}
</select>
</mapper>
十、动态SQL
Mybatis框架的动态SQL技术是一种根据特定条件动态拼装SQL语句的功能,它存在的意义是为了解决拼接SQL语句字符串时的痛点问题。
1. if
<?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.atguigu.mybatis.mapper.DynamicSqlMapper">
<!--Emp getEmpByCondition(Emp emp);-->
<select id="getEmpByCondition" resultType="Emp">
select * from t_emp where 1=1 <!--1=1是为了在某些条件不成立时不出现语法错误-->
<if test="empName != null and empName != ''">
and emp_name = #{empName}
</if>
<if test="age != null and age != ''">
and age = #{age}
</if>
<if test="sex != null and sex != ''">
and sex = #{sex}
</if>
<if test="email != null and email != ''">
and email = #{email}
</if>
</select>
</mapper>
2. where
一般配合if标签使用
-
若where标签中的if条件都不满足,则where标签没有任何功能,即不会添加where关键字
-
若where标签中的if条件满足,则where标签会自动添加where关键字,并将条件最前方多余的and去掉
注意:where标签不能去掉条件最后多余的and
<?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.atguigu.mybatis.mapper.DynamicSqlMapper">
<!--Emp getEmpByCondition(Emp emp);-->
<select id="getEmpByCondition" resultType="Emp">
select * from t_emp
<where>
<if test="empName != null and empName != ''">
and emp_name = #{empName}
</if>
<if test="age != null and age != ''">
and age = #{age}
</if>
<if test="sex != null and sex != ''">
and sex = #{sex}
</if>
<if test="email != null and email != ''">
and email = #{email}
</if>
</where>
</select>
</mapper>
3. trim
trim用于去掉或添加标签中的内容
常用属性:
prefix:在trim标签中的内容的前面添加某些内容
prefixOverrides:在trim标签中的内容的前面去掉某些内容
suffix:在trim标签中的内容的后面添加某些内容
suffixOverrides:在trim标签中的内容的后面去掉某些内容
<?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.atguigu.mybatis.mapper.DynamicSqlMapper">
<select id="getEmpByCondition" resultType="Emp">
select * from t_emp
<!--如果trim中有内容,会在前面添加where,且会去掉最后一个成立的if标签中的and或or
如果没有内容将不会显示任何内容,与where标签类似
-->
<trim prefix="where" suffixOverrides="and|or">
<if test="empName != null and empName != ''">
emp_name = #{empName} and
</if>
<if test="age != null and age != ''">
age = #{age} and
</if>
<if test="sex != null and sex != ''">
sex = #{sex} and
</if>
<if test="email != null and email != ''">
email = #{email} and
</if>
</trim>
</select>
</mapper>
4. choose、when、otherwise
相当于if…else if…else,when至少要有一个,otherwise最多只能有一个。
<?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.atguigu.mybatis.mapper.DynamicSqlMapper">
<!--List<Emp> getEmpByChoose(Emp emp); 根据某个条件查询员工信息-->
<select id="getEmpByChoose" resultType="emp">
select * from t_emp
<where>
<!--当某个条件成立时执行该sql,如果都不成立则执行otherwise-->
<choose>
<when test="empName != null and empName != ''">
emp_name=#{empName}
</when>
<when test="age != null and age != ''">
age=#{age}
</when>
<when test="sex != null and sex != ''">
sex=#{sex}
</when>
<when test="email != null and email != ''">
email=#{email}
</when>
<otherwise>
did=1
</otherwise>
</choose>
</where>
</select>
</mapper>
5. foreach
主要用于批量操作
属性:
collection:设置要循环的数组或集合
item:表示集合或数组中的每一个数据
separator:设置循环体之间的分隔符
open:设置foreach标签中的内容的开始符
close:设置foreach标签中的内容的结束符
5.1 实现批量删除
方式一:
<!--int deleteEmpByIds(@Param("eids") Integer[] eids); 根据id批量删除员工-->
<delete id="deleteEmpByIds">
delete from t_emp where eid in
<!--集合为eids,每个元素为eid,以(开始,以)结束,,每个元素以,分隔-->
<foreach collection="eids" item="eid" open="(" close=")" separator=",">
#{eid}
</foreach>
</delete>
方式二:
<!--int deleteEmpByIds(@Param("eids") Integer[] eids); 根据id批量删除员工-->
<delete id="deleteEmpByIds">
delete from t_emp where
<foreach collection="eids" item="eid" separator="or">
eid = #{eid}
</foreach>
</delete>
5.2 实现批量添加
<!--int insertMoreEmps(@Param("emps") List<Emp> emps); 批量插入员工信息-->
<insert id="insertMoreEmps">
insert into t_emp
values
<foreach collection="emps" item="emp" separator=",">
(null,#{emp.empName},#{emp.age},#{emp.sex},#{emp.email},null)
</foreach>
</insert>
6. sql
<!--sql片段-->
<sql id="empColumns">eid,emp_name,age,sex,email</sql>
<!--include标签引入片段-->
<select id="test" resultType="emp">
select <include refid="empColumns"></include> from t_emp
</select>
十一、Mybatis的缓存
1. Mybatis的一级缓存
一级缓存是SqlSession级别的,通过同一个SqlSession查询的数据会被缓存,下次查询相同的数据,就会从缓存中直接获取,不会从数据库重新访问
使一级缓存失效的四种情况:
- 不同的SqlSession对应不同的一级缓存
- 同一个SqlSession但是查询条件不同
- 同一个SqlSession两次查询期间执行了任何一次增删改操作
- 同一个SqlSession两次查询期间手动清空了缓存
2. Mybatis的二级缓存
二级缓存是SqlSessionFactory级别,通过同一个SqlSessionFactory创建的SqlSession查询的结果会被缓存;此后若再次执行相同的查询语句,结果就会从缓存中获取
二级缓存开启的条件:
- 在核心配置文件中,设置全局配置属性cacheEnabled=“true”,默认为true,不需要设置
- 在映射文件中设置标签<cache />
- 二级缓存必须在SqlSession关闭或提交之后有效
- 查询的数据所转换的实体类类型必须实现序列化的接口
使二级缓存失效的情况:
两次查询之间执行了任意的增删改,会使一级和二级缓存同时失效
3. 二级缓存的相关配置
在mapper配置文件中添加的cache标签可以设置一些属性:
- eviction属性:缓存回收策略
LRU(Least Recently Used) – 最近最少使用的:移除最长时间不被使用的对象。
FIFO(First in First out) – 先进先出:按对象进入缓存的顺序来移除它们。
SOFT – 软引用:移除基于垃圾回收器状态和软引用规则的对象。
WEAK – 弱引用:更积极地移除基于垃圾收集器状态和弱引用规则的对象。
默认的是 LRU。
-
flushInterval属性:刷新间隔,单位毫秒,默认情况是不设置,也就是没有刷新间隔,缓存仅仅调用增删改的语句时刷新
-
size属性:引用数目,正整数代表缓存最多可以存储多少个对象,太大容易导致内存溢出
-
readOnly属性:只读,true/false
true:只读缓存;会给所有调用者返回缓存对象的相同实例。因此这些对象不能被修改。这提供了很重要的性能优势。
false:读写缓存;会返回缓存对象的拷贝(通过序列化)。这会慢一些,但是安全,因此默认是false。
4. Mybatis缓存查询的顺序
- 先查询二级缓存,因为二级缓存中可能会有其他程序已经查出来的数据,可以拿来直接使用。
- 如果二级缓存没有命中,再查询一级缓存
- 如果一级缓存也没有命中,则查询数据库
- SqlSession关闭之后,一级缓存中的数据会写入二级缓存
5. 整合第三方缓存EHCache
5.1 添加依赖
<!-- Mybatis EHCache整合包 -->
<dependency>
<groupId>org.mybatis.caches</groupId>
<artifactId>mybatis-ehcache</artifactId>
<version>1.2.1</version>
</dependency>
<!-- slf4j日志门面的一个具体实现 -->
<dependency>
<groupId>ch.qos.logback</groupId>
<artifactId>logback-classic</artifactId>
<version>1.2.3</version>
</dependency>
jar包功能:
5.2 创建EHCache的配置文件ehcache.xml
<?xml version="1.0" encoding="utf-8" ?>
<ehcache xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="../config/ehcache.xsd"> <!-- 磁盘保存路径 -->
<diskStore path="D:\atguigu\ehcache"/>
<defaultCache maxElementsInMemory="1000" maxElementsOnDisk="10000000" eternal="false" overflowToDisk="true"
timeToIdleSeconds="120" timeToLiveSeconds="120" diskExpiryThreadIntervalSeconds="120"
memoryStoreEvictionPolicy="LRU"></defaultCache>
</ehcache>
5.3 设置二级缓存的类型:
<cache type="org.mybatis.caches.ehcache.EhcacheCache"/>
5.4 加入logback日志
存在SLF4J时,作为简易日志的log4j将失效,此时我们需要借助SLF4J的具体实现logback来打印日志。创建logback的配置文件logback.xml
<?xml version="1.0" encoding="UTF-8"?>
<configuration debug="true"> <!-- 指定日志输出的位置 -->
<appender name="STDOUT" class="ch.qos.logback.core.ConsoleAppender">
<encoder> <!-- 日志输出的格式 -->
<!-- 按照顺序分别是:时间、日志级别、线程名称、打印日志的类、日志主体内容、换行 -->
<pattern>[%d{HH:mm:ss.SSS}] [%-5level] [%thread] [%logger] [%msg]%n</pattern>
</encoder>
</appender>
<!-- 设置全局日志级别。日志级别按顺序分别是:DEBUG、INFO、WARN、ERROR -->
<!-- 指定任何一个日志级别都只打印当前级别和后面级别的日志。 -->
<root level="DEBUG"> <!-- 指定打印日志的appender,这里通过“STDOUT”引用了前面配置的appender -->
<appender-ref ref="STDOUT"/>
</root>
<!-- 根据特殊需求指定局部日志级别 -->
<logger name="com.atguigu.crowd.mapper" level="DEBUG"/>
</configuration>
5.5 EHCache配置文件说明
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0cGOnSwc-1663576831156)(E:\笔记\mybatis\搭建MyBatis.assets\image-20220918193802601.png)]
十二、Mybatis逆向工程
正向工程:先创建Java实体类,由框架负责根据实体类生成数据库表。Hibernate是支持正向工程的。
逆向工程:先创建数据库表,由框架负责根据数据库表,反向生成如下资源:
-
Java实体类
-
Mapper接口
-
Mapper映射文件
1. 创建逆向工程的步骤
1.1 添加依赖和插件
<?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.atguigu.mybatis</groupId>
<artifactId>mybatis-MBG</artifactId>
<version>1.0-SNAPSHOT</version>
<packaging>jar</packaging>
<properties>
<maven.compiler.source>8</maven.compiler.source>
<maven.compiler.target>8</maven.compiler.target>
</properties>
<!-- 依赖MyBatis核心包 -->
<dependencies>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.7</version>
</dependency>
</dependencies>
<!-- 控制Maven在构建过程中相关配置 -->
<build>
<!-- 构建过程中用到的插件 -->
<plugins>
<!-- 具体插件,逆向工程的操作是以构建过程中插件形式出现的 -->
<plugin>
<groupId>org.mybatis.generator</groupId>
<artifactId>mybatis-generator-maven-plugin</artifactId>
<version>1.3.0</version>
<!-- 插件的依赖 -->
<dependencies>
<!-- 逆向工程的核心依赖 -->
<dependency>
<groupId>org.mybatis.generator</groupId>
<artifactId>mybatis-generator-core</artifactId>
<version>1.3.2</version>
</dependency>
<!-- 数据库连接池 -->
<dependency>
<groupId>com.mchange</groupId>
<artifactId>c3p0</artifactId>
<version>0.9.2</version>
</dependency>
<!-- MySQL驱动 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.16</version>
</dependency>
</dependencies>
</plugin>
</plugins>
</build>
</project>
1.2 创建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>
<properties resource="jdbc.properties"/>
<!--设置mybatis的全局配置-->
<settings>
<!--mapUnderscoreToCamelCase意思为下划线变驼峰
规则为去除下划线,将下划线后一个字母变为大写
-->
<setting name="mapUnderscoreToCamelCase" value="true"/>
<!--开启全局延迟加载-->
<setting name="lazyLoadingEnabled" value="true"/>
</settings>
<typeAliases>
<package name="com.atguigu.mybatis.pojo"/>
</typeAliases>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${jdbc.driver}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</dataSource>
</environment>
</environments>
<mappers>
<package name="com.atguigu.mybatis.mapper"/>
</mappers>
</configuration>
1.3 创建逆向工程的配置文件generatorConfig.xml
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE generatorConfiguration PUBLIC
"-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
"http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">
<generatorConfiguration>
<!-- targetRuntime: 执行生成的逆向工程的版本
MyBatis3Simple: 生成基本的CRUD(清新简洁版)
MyBatis3: 生成带条件的CRUD(奢华尊享版) -->
<context id="DB2Tables" targetRuntime="MyBatis3Simple">
<!-- 数据库的连接信息 -->
<jdbcConnection driverClass="com.mysql.cj.jdbc.Driver" connectionURL="jdbc:mysql://localhost:3306/mybatis?serverTimezone=UTC"
userId="root" password="root"></jdbcConnection>
<!-- javaBean的生成策略-->
<javaModelGenerator targetPackage="com.atguigu.mybatis.pojo" targetProject=".\src\main\java">
<!--是否使用子包,开启时com.atguigu.mybatis.pojo会变成一层一层的目录
不开启则默认com.atguigu.mybatis.pojo就是目录名字,不会创建多层目录-->
<property name="enableSubPackages" value="true"/>
<!--去除字段名两边的空格-->
<property name="trimStrings" value="true"/>
</javaModelGenerator>
<!-- SQL映射文件的生成策略 -->
<sqlMapGenerator targetPackage="com.atguigu.mybatis.mapper" targetProject=".\src\main\resources">
<property name="enableSubPackages" value="true"/>
</sqlMapGenerator>
<!-- Mapper接口的生成策略 -->
<javaClientGenerator type="XMLMAPPER" targetPackage="com.atguigu.mybatis.mapper"
targetProject=".\src\main\java">
<property name="enableSubPackages" value="true"/>
</javaClientGenerator>
<!-- 逆向分析的表 -->
<!-- tableName设置为*号,可以对应所有表,此时不写domainObjectName -->
<!-- domainObjectName属性指定生成出来的实体类的类名 -->
<table tableName="t_emp" domainObjectName="Emp"/>
<table tableName="t_dept" domainObjectName="Dept"/>
</context>
</generatorConfiguration>
1.4 执行MBG插件的generate目标
此时将会自动生成JavaBean、Mapper接口、Mapper.xml以及一些增删改查方法(主要看用的是targetRuntime=""是什么版本,决定会会生成什么代码)
2. QBC查询测试
package com.atguigu.mybatis;
import com.atguigu.mybatis.mapper.EmpMapper;
import com.atguigu.mybatis.pojo.Emp;
import com.atguigu.mybatis.pojo.EmpExample;
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 org.junit.Test;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
public class TestEmpMapper {
@Test
public void testSelect(){
try {
InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
SqlSession sqlSession = sqlSessionFactory.openSession(true);
EmpMapper empMapper = sqlSession.getMapper(EmpMapper.class);
//查询所有数据
List<Emp> emps = empMapper.selectByExample(null);
emps.forEach(System.out::println);
//根据条件查询
EmpExample empExample = new EmpExample();
empExample.createCriteria().andAgeBetween(1,15).andEmpNameEqualTo("张三");
empExample.or().andSexEqualTo("男");
List<Emp> emps1 = empMapper.selectByExample(empExample);
System.out.println("****************************************");
emps1.forEach(System.out::println);
//选择性修改:如果属性内容为null,则不会对其指定的字段进行修改
empMapper.updateByPrimaryKeySelective(new Emp(1,"admin",1,null,"456@qq.com",1));
} catch (IOException e) {
e.printStackTrace();
}
}
}
十三、分页插件
1. 分页插件的配置
1.1 添加依赖
<!--mybatis分页插件-->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>5.2.0</version>
</dependency>
1.2 在Mybtis配置文件中配置插件
<plugins>
<!--设置分页插件-->
<plugin interceptor="com.github.pagehelper.PageInterceptor"></plugin>
</plugins>
2. 分页插件的使用
- 在查询功能之前使用PageHelper.startPage(int pageNum, int pageSize)开启分页功能
pageNum:当前页的页码
pageSize:每页显示的条数
- 在查询获取list集合之后,使用PageInfo pageInfo = new PageInfo<>(List list, int navigatePages)获取分页相关数据
list:分页之后的数据
navigatePages:导航分页的页码
- 分页相关数据
PageInfo{
pageNum=8, pageSize=4, size=2, startRow=29, endRow=30, total=30, pages=8,
list=Page{count=true, pageNum=8, pageSize=4, startRow=28, endRow=32, total=30,
pages=8, reasonable=false, pageSizeZero=false},
prePage=7, nextPage=0, isFirstPage=false, isLastPage=true, hasPreviousPage=true,
hasNextPage=false, navigatePages=5, navigateFirstPage4, navigateLastPage8,
navigatepageNums=[4, 5, 6, 7, 8]
}
常用数据:
pageNum:当前页的页码
pageSize:每页显示的条数
size:当前页显示的真实条数
total:总记录数
pages:总页数
prePage:上一页的页码
nextPage:下一页的页码
isFirstPage/isLastPage:是否为第一页/最后一页
hasPreviousPage/hasNextPage:是否存在上一页/下一页
navigatePages:导航分页的页码数
navigatepageNums:导航分页的页码,[1,2,3,4,5]
代码演示:
public void testPageHelper(){
InputStream in = null;
try {
in = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
SqlSession sqlSession = sqlSessionFactory.openSession(true);
EmpMapper empMapper = sqlSession.getMapper(EmpMapper.class);
//开启分页功能
PageHelper.startPage(5,4);
List<Emp> emps = empMapper.selectByExample(null);
//navigate为展示的页码数量,比如5个就是 能看到1 2 3 4 5页
PageInfo<Emp> empPageInfo = new PageInfo<>(emps, 5);
System.out.println(empPageInfo);
emps.forEach(System.out::println);
} catch (IOException e) {
e.printStackTrace();
}
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
# 十三、分页插件
## 1. 分页插件的配置
### 1.1 添加依赖
```xml
<!--mybatis分页插件-->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>5.2.0</version>
</dependency>
1.2 在Mybtis配置文件中配置插件
<plugins>
<!--设置分页插件-->
<plugin interceptor="com.github.pagehelper.PageInterceptor"></plugin>
</plugins>
2. 分页插件的使用
- 在查询功能之前使用PageHelper.startPage(int pageNum, int pageSize)开启分页功能
pageNum:当前页的页码
pageSize:每页显示的条数
- 在查询获取list集合之后,使用PageInfo pageInfo = new PageInfo<>(List list, int navigatePages)获取分页相关数据
list:分页之后的数据
navigatePages:导航分页的页码
- 分页相关数据
PageInfo{
pageNum=8, pageSize=4, size=2, startRow=29, endRow=30, total=30, pages=8,
list=Page{count=true, pageNum=8, pageSize=4, startRow=28, endRow=32, total=30,
pages=8, reasonable=false, pageSizeZero=false},
prePage=7, nextPage=0, isFirstPage=false, isLastPage=true, hasPreviousPage=true,
hasNextPage=false, navigatePages=5, navigateFirstPage4, navigateLastPage8,
navigatepageNums=[4, 5, 6, 7, 8]
}
常用数据:
pageNum:当前页的页码
pageSize:每页显示的条数
size:当前页显示的真实条数
total:总记录数
pages:总页数
prePage:上一页的页码
nextPage:下一页的页码
isFirstPage/isLastPage:是否为第一页/最后一页
hasPreviousPage/hasNextPage:是否存在上一页/下一页
navigatePages:导航分页的页码数
navigatepageNums:导航分页的页码,[1,2,3,4,5]
代码演示:
public void testPageHelper(){
InputStream in = null;
try {
in = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
SqlSession sqlSession = sqlSessionFactory.openSession(true);
EmpMapper empMapper = sqlSession.getMapper(EmpMapper.class);
//开启分页功能
PageHelper.startPage(5,4);
List<Emp> emps = empMapper.selectByExample(null);
//navigate为展示的页码数量,比如5个就是 能看到1 2 3 4 5页
PageInfo<Emp> empPageInfo = new PageInfo<>(emps, 5);
System.out.println(empPageInfo);
emps.forEach(System.out::println);
} catch (IOException e) {
e.printStackTrace();
}
}