MyBatis简介
持久层框架,用于简化JDBC开发
持久层:负责将数据保存到数据库
JavaEE三层架构:
表现层:页面展示
业务层:逻辑处理
持久层:数据持久化
官网:https://mybatis.org/mybatis-3/zh/index.html
JDBC缺点:
- 硬编码
- 操作繁琐
MyBatis简化:免除了几乎所有的 JDBC 代码以及设置参数和获取结果集的工作
MyBatis快速入门
- 创建表,添加数据
- 创建模块,导入坐标
<!--导入MyBatis的依赖-->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.2.2</version>
</dependency>
<!--导入MySQL驱动-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.27</version>
</dependency>
- 编写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>
<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:///user?useSSL=false"/>
<property name="username" value="root"/>
<property name="password" value=""/>
</dataSource>
</environment>
</environments>
<mappers>
<!--加载sql映射文件-->
<mapper resource="UserMapper.xml"/>
</mappers>
</configuration>
- 编写sql映射文件
<!--TableMapper.xml-->
<!--UserMapper.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">
<!--namespace:名称空间-->
<mapper namespace="test">
<select id="selectAll" resultType="com.ziping.pojo.User">
select * from user;
</select>
</mapper>
- 编码
5.1 定义pojo类
5.2 加载核心配置文件,获取SqlSessionFactory对象public class User { private int id; private String name; private int age; public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public int getAge() { return age; } public void setAge(int age) { this.age = age; } @Override public String toString() { return "User [" + "id=" + id + ", name=" + name + ", age=" + age + "]"; } }
5.3 获取SqlSession对象,执行sql语句
5.4 释放资源//Mybatis快速入门代码 public class MybatisDemo { public static void main(String[] args) throws IOException { //1. 加载Mybatis核心配置文件,获取SqlSessionFactory String resource = "mybatis-config.xml"; InputStream inputStream = Resources.getResourceAsStream(resource); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); //2. 获取SqlSession对象,用它来执行sql SqlSession sqlSession = sqlSessionFactory.openSession(); //3. 执行sql //名称空间.id List<User> users = sqlSession.selectList("test.selectAll" ); //4. 遍历结果 System.out.println(users); //5. 关闭资源 sqlSession.close(); } }
Mapper代理开发
规则:
1. 定义与sql映射文件同名的Mapper接口,并且将Mapper接口和sql映射文件放置在同一目录下
2. 设置sql映射文件的namespace属性为Mapper接口全限定名<!--com/ziping/mapper/UserMapper.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"> <!--namespace:名称空间--> <!--全限定名--> <mapper namespace="com.ziping.mapper.UserMapper"> <select id="selectAll" resultType="com.ziping.pojo.User"> select * from user; </select> </mapper>
3. 在Mapper接口中定义方法,方法名就是sql映射文件中sql语句的id,并保持参数类型和返回值类型一致
//com.ziping.mapper.UserMapper public interface UserMapper { //方法名与上id相同 List<User> selectAll(); }
4. 编码
4.1 通过SqlSession的getMapper方法获取Mapper接口的代理对象
4.2 调用对应的方法完成sql的执行//com.ziping.MybatisDemo2 public class MybatisDemo2 { public static void main(String[] args) throws IOException { //1. 加载Mybatis核心配置文件,获取SqlSessionFactory String resource = "mybatis-config.xml"; InputStream inputStream = Resources.getResourceAsStream(resource); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); //2. 获取SqlSession对象,用它来执行sql SqlSession sqlSession = sqlSessionFactory.openSession(); //3.1 获取UserMapper接口的代理对象 UserMapper userMapper = sqlSession.getMapper(UserMapper.class); List<User> users = userMapper.selectAll(); //4. 遍历结果 System.out.println(users); //5. 关闭资源 sqlSession.close(); } }
<!--
细节:如果Mapper接口名称和sql映射文件名称相同,并在同一目录下,则可以使用包扫描的方式简化sql映射文件的加载
-->
<!--mybatis-config.xml-->
<mappers>
<!--传统方法加载sql映射文件-->
<mapper resource="com/ziping/mapper/UserMapper.xml"/>
<!--Mapper代理方式加载sql映射文件-->
<package name="com.ziping.mapper"/>
</mappers>
MyBatis核心配置文件
配置各个标签时:需要遵守前后顺序
配置文件完成增删改查
User表
基本步骤:
1. 编写接口方法:Mapper接口
2. 编写sql语句
3. 执行方法,测试
//User类
public class User {
private Integer id;
private String name;
private Integer age;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
@Override
public String toString() {
return "User [id=" + id + ", name=" + name + ", age=" + age + "]";
}
}
查询
1. 查询所有数据
//UserMapper.java
//Mapper接口方法
public interface UserMapper {
public List<User> selectAll();
}
<!--UserMapper.xml-->
<mapper namespace="com.ziping.mapper.UserMapper">
<!--statement-->
<select id="selectAll" resultType="com.ziping.pojo.User">
select * from user;
</select>
<!--
数据库的字段名称和实体类的属性名称不一样,则不能自动封装数据,需要手动指定
1. 起别名:对不一样的列名起别名,让列名和实体类的属性名一致(不方便)
<select id="selectAll" resultType="com.ziping.pojo.User">
select id as id, name as name, age as age from user;
</select>
或者(略微方便,不灵活)
sql片段
<sql id="user_col">
select id as id, name as name, age as age from user
</sql>
<select id="selectAll" resultType="com.ziping.pojo.User">
select
<include refid="user_col"/>
from user;
</select>
2. resultMap:
//id:唯一标识 type:映射的类型
<resultMap id="userResultMap" type="com.ziping.pojo.User">
//id标签只用于主键字段的映射
<id column="id" property="id"></id>
//result标签用于一般字段的映射
<result column="age" property="age"></result>
</resultMap>
<select id="selectAll" resultMap="userResultMap">
select * from user;
</select>
-->
</mapper>
//MybatisTest.java
@Test
public void testSelectAll() throws IOException {
//1. 获取SqlSessionFactory对象
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//2. 获取SqlSession对象
SqlSession sqlSession = sqlSessionFactory.openSession();
//3. 获取Mapper接口的代理对象
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
//4. 执行查询所有方法
List<User> users = userMapper.selectAll();
System.out.println(users);
//5. 释放资源
sqlSession.close();
}
2. 查看详情
//UserMapper.java
//Mapper接口方法
public interface UserMapper {
public User selectById(int id);
}
<!--UserMapper.xml-->
<mapper namespace="com.ziping.mapper.UserMapper">
<!--statement-->
<select id="selectById" parameterType="int" resultType="com.ziping.pojo.User">
select * from user where id = #{id};
</select>
<!--
*参数占位符
#{} :将其替换为?,防止sql注入
${} :拼sql,存在sql注入问题;拼表名、列名只能使用${}
*参数类型
parameterType可以省略
*特殊字符处理(<)
1. 转义字符(< 转义为 <)
2. CDATA区(
<![CDATA[
<
]]>
)
-->
</mapper>
//MybatisTest.java
@Test
public void testSelectById() throws IOException {
int id = 1;
//1. 获取SqlSessionFactory对象
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//2. 获取SqlSession对象
SqlSession sqlSession = sqlSessionFactory.openSession();
//3. 获取Mapper接口的代理对象
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
//4. 执行查询方法
User user = userMapper.selectById(id);
System.out.println(user);
//5. 释放资源
sqlSession.close();
}
3. 条件查询
3.1 多条件查询
//UserMapper.java
//Mapper接口方法
public interface UserMapper {
//1# 散装参数
//使用@Param("")来指定参数占位符名称
List<User> selectByCondition(@Param("name")String name, @Param("age")Integer age);
//2# 对象参数
//参数属于同一个对象,并且参数占位符名称与对象属性名称一致
List<User> selectByCondition(User user);
//3# Map集合参数
//Map集合键的名称与参数占位符名称一致
List<User> selectByCondition(Map map);
}
<!--UserMapper.xml-->
<mapper namespace="com.ziping.mapper.UserMapper">
<!--statement-->
<select id="selectByCondition" resultType="com.ziping.pojo.User">
select * from user where
name like #{name}
and age = #{age}
</select>
</mapper>
//MybatisTest.java
@Test
public void testSelectByCondition() throws IOException {
//接收参数
String name = "zi";
Integer age = 21;
//处理参数
name = "%" + name + "%";
//2# 将参数封装为对象
User user = new User();
user.setName(name);
user.setAge(age);
//3# 将参数封装为Map集合
Map map = new HashMap();
map.put("name", name);
map.put("age", age);
//1. 获取SqlSessionFactory对象
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//2. 获取SqlSession对象
SqlSession sqlSession = sqlSessionFactory.openSession();
//3. 获取Mapper接口的代理对象
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
//4. 执行查询所有方法
//1#
List<User> users = userMapper.selectByCondition(name, age);
System.out.println(users);
//2#
users = userMapper.selectByCondition(user);
System.out.println(users);
//3#
users = userMapper.selectByCondition(map);
System.out.println(users);
//5. 释放资源
sqlSession.close();
}
3.2 动态条件查询
<!--多条件的动态条件查询-->
<!--UserMapper.xml-->
<mapper namespace="com.ziping.mapper.UserMapper">
<!--statement-->
<select id="selectByCondition" resultType="com.ziping.pojo.User">
select * from user where
<!--字符串判断-->
<!--if条件语句 test逻辑表达式-->
<if test="name != null and name != ''">
name like #{name}
</if>
<if test="age != null">
and age = #{age}
</if>
</select>
</mapper>
<!--存在语法问题-->
<!--解决方案一:手动解决-->
<select id="selectByCondition" resultType="com.ziping.pojo.User">
select * from user where 1 = 1
<if test="name != null and name != ''">
and name like #{name}
</if>
<if test="age != null">
and age = #{age}
</if>
</select>
<!--解决方案二:where标签-->
<select id="selectByCondition" resultType="com.ziping.pojo.User">
select * from user
<where>
<if test="name != null and name != ''">
and name like #{name}
</if>
<if test="age != null">
and age = #{age}
</if>
</where>
</select>
<!--单条件的动态条件查询-->
<!--UserMapper.xml-->
<mapper namespace="com.ziping.mapper.UserMapper">
<!--statement-->
<select id="selectByCondition" resultType="com.ziping.pojo.User">
select * from user where
<choose> <!--相当于switch-->
<when test="name != null"> <!--相当于case-->
name like #{name}
</when>
<when test="age != null">
age = #{age}
</when>
</choose>
</select>
</mapper>
<!--存在语法问题-->
<!--解决方案一:otherwise标签-->
<select id="selectByCondition" resultType="com.ziping.pojo.User">
select * from user where
<choose> <!--相当于switch-->
<when test="name != null"> <!--相当于case-->
name like #{name}
</when>
<when test="age != null">
age = #{age}
</when>
<otherwise> <!--相当于default-->
1 = 1
</otherwise>
</choose>
</select>
<!--解决方案二:where标签-->
<select id="selectByCondition" resultType="com.ziping.pojo.User">
select * from user
<where>
<choose> <!--相当于switch-->
<when test="name != null"> <!--相当于case-->
name like #{name}
</when>
<when test="age != null">
age = #{age}
</when>
<otherwise> <!--相当于default-->
1 = 1
</otherwise>
</choose>
</where>
</select>
添加
//UserMapper.java
//Mapper接口方法
public interface UserMapper {
void add(User user);
}
<!--UserMapper.xml-->
<mapper namespace="com.ziping.mapper.UserMapper">
<!--statement-->
<insert id="add">
insert into user(name,age)
values(#{name},#{age});
</insert>
</mapper>
<!--插入后id值不会赋值给对象属性-->
<!--改进:-->
<!--keyProperty:主键对应的属性名称-->
<insert id="add" useGeneratedKeys="true" keyProperty="id">
insert into user(name,age)
values(#{name},#{age});
</insert>
//MybatisTest.java
@Test
public void testAdd() throws IOException {
//接收参数
String name = "Tom";
Integer age = 21;
//将数据封装为对象
User user = new User();
user.setName(name);
user.setAge(age);
//1. 获取SqlSessionFactory对象
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//2. 获取SqlSession对象
SqlSession sqlSession = sqlSessionFactory.openSession();
//3. 获取Mapper接口的代理对象
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
//4. 执行查询所有方法
userMapper.add(user);
//提交事务
sqlSession.commit();
//5. 释放资源
sqlSession.close();
}
//Mybatis默认事务自动提交为false
/*
方法一:手动提交事务
sqlSession.commit();
方法二:将自动提交设置为true
SqlSession sqlSession = sqlSessionFactory.openSession(true);
*/
修改
1. 修改全部字段
//UserMapper.java
//Mapper接口方法
public interface UserMapper {
void update(User user);
}
<!--UserMapper.xml-->
<mapper namespace="com.ziping.mapper.UserMapper">
<!--statement-->
<update id="update">
update user set
name=#{name},
age=#{age}
where id=#{id};
</update>
</mapper>
//MybatisTest.java
@Test
public void testUpdate() throws IOException {
//接收参数
int id = 5;
String name = "ZZZ";
Integer age = 21;
//将数据封装为对象
User user = new User();
user.setName(name);
user.setAge(age);
user.setId(id);
//1. 获取SqlSessionFactory对象
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//2. 获取SqlSession对象
SqlSession sqlSession = sqlSessionFactory.openSession(true);
//3. 获取Mapper接口的代理对象
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
//4. 执行查询所有方法
int update = userMapper.update(user);
System.out.println(update);
//5. 释放资源
sqlSession.close();
}
2. 修改动态字段
<!--UserMapper.xml-->
<mapper namespace="com.ziping.mapper.UserMapper">
<!--statement-->
<update id="update">
update user
<set>
<if test="name != null and name != ''">
name=#{name},
</if>
<if test="age != null">
age=#{age}
</if>
</set>
where id=#{id};
</update>
</mapper>
<!--set标签可以用于解决语法问题-->
删除
1. 单条删除
//UserMapper.java
//Mapper接口方法
public interface UserMapper {
void deleteById(Integer id);
}
<!--UserMapper.xml-->
<mapper namespace="com.ziping.mapper.UserMapper">
<!--statement-->
<delete id="deleteById">
delete from user
where id=#{id};
</delete>
</mapper>
//MybatisTest.java
@Test
public void testUpdate() throws IOException {
//接收参数
int id = 7;
//1. 获取SqlSessionFactory对象
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//2. 获取SqlSession对象
SqlSession sqlSession = sqlSessionFactory.openSession(true);
//3. 获取Mapper接口的代理对象
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
//4. 执行查询所有方法
userMapper.deleteById(id);
//5. 释放资源
sqlSession.close();
}
2. 批量删除
//UserMapper.java
//Mapper接口方法
public interface UserMapper {
void deleteByIds(@Param("ids") int[] ids);
}
<!--UserMapper.xml-->
<mapper namespace="com.ziping.mapper.UserMapper">
<!--statement-->
<delete id="deleteByIds">
delete from user
where id in
<!--
Mybatis会将数组参数封装为Map集合
*默认:key为array
*void deleteByIds(@Param("ids") int[] ids);改默认key为ids
-->
<foreach collection="ids" item="id" separator="," open="(" close=")">
#{id}
</foreach>
;
</delete>
</mapper>
//MybatisTest.java
@Test
public void testUpdate() throws IOException {
//接收参数
int id = 7;
//1. 获取SqlSessionFactory对象
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//2. 获取SqlSession对象
SqlSession sqlSession = sqlSessionFactory.openSession(true);
//3. 获取Mapper接口的代理对象
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
//4. 执行查询所有方法
userMapper.deleteById(id);
//5. 释放资源
sqlSession.close();
}
MyBatis参数传递
多个参数
将参数封装为Map集合
map.put(“arg0”,参数值1);map.put(“arg1”,参数值2);
或 map.put(“param1”,参数值1);map.put(“param2”,参数值2);//用@Param("")注解来替换Map集合中默认的arg键名,不替换param键名 List<User> selectByCondition(@Param("name")String name, @Param("age")Integer age);
单个参数
1. POJO类
直接使用,属性名 和 参数占位符名称一致
2. Map集合
直接使用,键名 和 参数占位符名称一致
3. Collection
封装为Map集合: map.put("arg0", collection集合);
map.put("collection", collection集合);
可以用@Param("")注解来替换Map集合中默认的arg键名
4. List
封装为Map集合: map.put("arg0", collection集合);
map.put("collection", collection集合);
map.put("list", list集合);
可以用@Param("")注解来替换Map集合中默认的arg键名
5. Array
封装为Map集合: map.put("arg0", 数组);
map.put("array", 数组);
可以用@Param("")注解来替换Map集合中默认的arg键名
6. 其他类型
直接使用 如:int id
注解完成增删改查
简单的sql语句建议用注解
//查询:
@Select("select * from user where id = #{id}")
public User selectById(int id);
//添加:
@Insert("")
//修改:
@Update("")
//删除:
@Delete("")