目录
二、功能实现
1.添加数据
(1)编写Mapper接口
- 参数:除了id以外的所有数据
- 结果:void
//Mapper接口
void add(Brand brand);
(2)编写SQL语句:SQL映射文件
<!--映射文件-->
<!--
添加数据
-->
<insert id="add" useGeneratedKeys="true" keyProperty="id">
insert into tb_brand(brand_name, company_name, ordered, description, status)
VALUES (#{brandName}, #{companyName}, #{ordered}, #{description}, #{status})
</insert>
(3)执行方法,测试
MyBatis事务:
- openSession:默认开启事务,进行增删改查操作后需要使用sqlSession.commit();手动提交事务
- openSession(true):可以设置为自动提交事务(关闭事务)
【注意】:在数据添加成功后,需要获取插入数据库数据的主键(主键返回)
<insert useGeneratedKeys="true" keyProperty="id">
例:
<!--Mapper.xml-->
<!--
namespace:名称空间
-->
<mapper namespace="com.itheima.mapper.BrandMapper">
<!--
id:唯一标识
type:映射的类型,支持别名
-->
<resultMap id="brandResultMap" type="brand">
<!--
id:完成主键字段的映射
column:表的列明
property:实体类的属性名
result:完成一般字段的映射
-->
<result column="brand_name" property="brandName"/>
<result column="company_name" property="companyName"/>
</resultMap>
<!--
添加数据
-->
<insert id="add" useGeneratedKeys="true" keyProperty="id">
insert into tb_brand(brand_name, company_name, ordered, description, status)
VALUES (#{brandName}, #{companyName}, #{ordered}, #{description}, #{status})
</insert>
</mapper>
//Mapper接口
//添加
void add(Brand brand);
//测试类
public void testAdd() throws IOException {
//接受参数
Integer status = 1;
String companyName = "Apple";
String brandName = "iPad pro";
String description="你的下一台电脑何必是电脑";
int ordered=100;
//封装对象
Brand brand = new Brand();
brand.setStatus(status);
brand.setBrandName(brandName);
brand.setCompanyName(companyName);
brand.setDescription(description);
brand.setOrdered(ordered);
//加载核心配置文件,获取 SqlSessionFactory 对象
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//获取SqlSession对象,执行sql
SqlSession sqlSession = sqlSessionFactory.openSession(true);
//获取Mapper接口的代理对象
BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
//执行sql
brandMapper.add(brand);
//提交事务
//sqlSession.commit();
//释放资源
sqlSession.close();
}
2.修改字段(动态)
(1)编写接口方法:Mapper接口
- 参数:部分数据or所有数据,封装到对象中
- 结果:void
//Mapper接口
void update(Brand brand);
(2)编写SQL语句:SQL映射文件
<!--SQL映射文件-->
<!--
修改数据
-->
<update id="update">
update tb_brand
<set>
<if test="brandName != null and brandName !='' ">
brand_name = #{brandName},
</if>
<if test="companyName != null and companyName !=''">
company_name = #{companyName},
</if>
<if test="ordered != null">
ordered = #{ordered},
</if>
<if test="description != null and description !=''">
description = #{description},
</if>
<if test="status != null">
status = #{status},
</if>
</set>
where id = #{id};
</update>
(3)执行方法,测试
<!--Mapper.xml-->
<!--
namespace:名称空间
-->
<mapper namespace="com.itheima.mapper.BrandMapper">
<!--
id:唯一标识
type:映射的类型,支持别名
-->
<resultMap id="brandResultMap" type="brand">
<!--
id:完成主键字段的映射
column:表的列明
property:实体类的属性名
result:完成一般字段的映射
-->
<result column="brand_name" property="brandName"/>
<result column="company_name" property="companyName"/>
</resultMap>
<!--
修改数据
-->
<update id="update">
update tb_brand
<set>
<if test="brandName != null and brandName !='' ">
brand_name = #{brandName},
</if>
<if test="companyName != null and companyName !=''">
company_name = #{companyName},
</if>
<if test="ordered != null">
ordered = #{ordered},
</if>
<if test="description != null and description !=''">
description = #{description},
</if>
<if test="status != null">
status = #{status},
</if>
</set>
where id = #{id};
</update>
</mapper>
//Mapper接口
//修改
void update(Brand brand);
//测试类
public void testUpdate() throws IOException {
//接受参数
Integer status = 1;
String companyName = "Apple";
String brandName = "iPad";
String description="你的下一台电脑何必是电脑";
int ordered=200;
int id=4;
//封装对象
Brand brand = new Brand();
brand.setStatus(status);
brand.setBrandName(brandName);
brand.setCompanyName(companyName);
brand.setDescription(description);
brand.setOrdered(ordered);
brand.setId(id);
//加载核心配置文件,获取 SqlSessionFactory 对象
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//获取SqlSession对象,执行sql
SqlSession sqlSession = sqlSessionFactory.openSession(true);
//获取Mapper接口的代理对象
BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
//执行sql
brandMapper.update(brand);
//提交事务
//sqlSession.commit();
//释放资源
sqlSession.close();
}
3.删除一条数据
(1)编写接口方法:Mapper接口
- 参数:id
- 结果:void
//Mapper接口
void deleteById(@Param("id")int id);
(2)编写SQL语句:SQL映射文件
<!--SQL映射文件-->
<!--
删除一条数据
-->
<delete id="deleteById">
delete
from tb_brand
where id = #{id}
</delete>
(3)执行方法,测试
<!--Mapper.xml-->
<!--
namespace:名称空间
-->
<mapper namespace="com.itheima.mapper.BrandMapper">
<!--
id:唯一标识
type:映射的类型,支持别名
-->
<resultMap id="brandResultMap" type="brand">
<!--
id:完成主键字段的映射
column:表的列明
property:实体类的属性名
result:完成一般字段的映射
-->
<result column="brand_name" property="brandName"/>
<result column="company_name" property="companyName"/>
</resultMap>
<!--
删除一条数据
-->
<delete id="deleteById">
delete
from tb_brand
where id = #{id}
</delete>
</mapper>
//Mapper接口
//删除一条数据
void deleteById(@Param("id")int id);
//测试类
public void testDeleteById() throws IOException {
//接受参数
int id=4;
//加载核心配置文件,获取 SqlSessionFactory 对象
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//获取SqlSession对象,执行sql
SqlSession sqlSession = sqlSessionFactory.openSession(true);
//获取Mapper接口的代理对象
BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
//执行sql
brandMapper.deleteById(id);
//提交事务
//sqlSession.commit();
//释放资源
sqlSession.close();
}
4.批量删除
(1)编写接口方法:Mapper接口
- 参数:id数组
- 结果:void
//Mapper接口
void deleteByIds(@Param("ids") int ids[]);
(2)编写SQL语句:SQL映射文件
<!--SQL映射文件-->
<!--
批量删除
-->
<delete id="deleteByIds">
delete from tb_brand
where id in
<foreach collection="ids" item="id" separator="," open="(" close=")">
#{id}
</foreach>
</delete>
【注意】:
MayBatis会将数组参数,封装为一个Map集合
- 默认:array = 数组
- 使用@Param注解改变map集合的默认key的名称
(3)执行方法,测试
<!--Mapper.xml-->
<!--
namespace:名称空间
-->
<mapper namespace="com.itheima.mapper.BrandMapper">
<!--
id:唯一标识
type:映射的类型,支持别名
-->
<resultMap id="brandResultMap" type="brand">
<!--
id:完成主键字段的映射
column:表的列明
property:实体类的属性名
result:完成一般字段的映射
-->
<result column="brand_name" property="brandName"/>
<result column="company_name" property="companyName"/>
</resultMap>
<!--
批量删除
-->
<delete id="deleteByIds">
delete from tb_brand
where id in
<foreach collection="ids" item="id" separator="," open="(" close=")">
#{id}
</foreach>
</delete>
//Mapper接口
//批量删除
void deleteByIds(@Param("ids") int ids[]);
//测试类
public void testDeleteByIds() throws IOException {
//接受参数
int ids[] = {7, 8};
//加载核心配置文件,获取 SqlSessionFactory 对象
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//获取SqlSession对象,执行sql
SqlSession sqlSession = sqlSessionFactory.openSession(true);
//获取Mapper接口的代理对象
BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
//执行sql
brandMapper.deleteByIds(ids);
//提交事务
//sqlSession.commit();
//释放资源
sqlSession.close();
}
【利用注解完成】
一、使用条件
- 注解完成简单的功能
二、功能实现
- 查询:@Select
- 添加:@Insert
- 修改:@Update
- 删除:@Delete
例:
//查询
@Select(value = "select * from tb_user where id = #{id}")
public User select(int id);
//参数设置
@Select(value = "select * from tb_user where id = #{id} and username = #{username}")
public User select(@Param("id") int id, @Param("username") String username);
//结果集映射
@Select(value = "select * from tb_user where id = #{id} and username = #{username}")
@ResultMap("userResultMap")
public User select(@Param("id") int id, @Param("username") String username);
【MyBatis参数传递】
MyBatis接口方法中可以接收各类参数,MyBatis底层对于这些参数有不同的封装方式
【单个参数】
1.POJO类
直接使用,属性名和参数占位符名称一致
2.Map集合
直接使用,键名和参数占位符名称一致
3.Collection
封装为Map集合,可以使用@Param注解,替换Map集合中默认的arg键名
map.put("arg0",collection集合);
map.put("collection",collection集合);
4.List
封装为Map集合,可以使用@Param注解,替换Map集合中默认的arg键名
map.put("arg0",list集合);
map.put("collection",list集合);
map.put("list",list集合);
5.Array
封装Map集合,可以使用@Param注解,替换Map集合中默认的arg键名
map.put("arg0",数组);
map.put("array",数组);
6.其他类型
直接使用
【多个参数】
封装Map集合,可以使用@Param注解,替换Map集合中默认的arg键名
【注意】:用@Param注解来修改Map集合中默认的键名,并使用修改的后的名称来取值,可读性更高