Mybatis案例-商品的增删改查

JDBC完成： https://blog.csdn.net/meini32/article/details/131981238

1. aim

要完成的功能列表清单:
1. 查询

• 查询所有数据
• 查看详情
• 条件查询

2. 添加
3. 修改

• 修改全部字段
• 修改动态字段

4. 删除

• 删除一个
• 批量删除

2.环境准备

1. 数据库表tb_brand
2. 实体类Brand
3. 测试用例
4. 安装MyBatis×插件

数据库表：

-- 删除tb_brand表
drop table if exists tb_brand;
-- 创建tb_brand表
create table tb_brand
(-- id 主键id           int primary key auto_increment,-- 品牌名称brand_name   varchar(20),-- 企业名称company_name varchar(20),-- 排序字段ordered      int,-- 描述信息description  varchar(100),-- 状态：0：禁用  1：启用status       int
);
-- 添加数据
insert into tb_brand (brand_name, company_name, ordered, description, status)
values ('三只松鼠', '三只松鼠股份有限公司', 5, '好吃不上火', 0),('华为', '华为技术有限公司', 100, '华为致力于把数字世界带入每个人、每个家庭、每个组织，构建万物互联的智能世界', 1),('小米', '小米科技有限公司', 50, 'are you ok', 1);SELECT * FROM tb_brand;

实体Brand类

package com.itheima.pojo;public class Brand {// id 主键private Integer id;// 品牌名称private String brandName;// 企业名称private String companyName;// 排序字段private Integer ordered;// 描述信息private String description;// 状态：0：禁用  1：启用private Integer status;public Integer getId() {return id;}public void setId(Integer id) {this.id = id;}public String getBrandName() {return brandName;}public void setBrandName(String brandName) {this.brandName = brandName;}public String getCompanyName() {return companyName;}public void setCompanyName(String companyName) {this.companyName = companyName;}public Integer getOrdered() {return ordered;}public void setOrdered(Integer ordered) {this.ordered = ordered;}public String getDescription() {return description;}public void setDescription(String description) {this.description = description;}public Integer getStatus() {return status;}public void setStatus(Integer status) {this.status = status;}@Overridepublic String toString() {return "Brand{" +"id=" + id +", brandName='" + brandName + '\'' +", companyName='" + companyName + '\'' +", ordered=" + ordered +", description='" + description + '\'' +", status=" + status +'}';}
}

测试用例

MybatisX插件

功能

• XML和接口方法互相跳转
• 根据接口方法生成statement

3.查询

步骤

• 编写接口：Mapper接口
• 编写sql语句：sql映射文件
• 执行方法，测试

3.1 查所有

注意：数据库表的字段名称和―实体类的属性名称不一样，则不能自动封装数据

方法1：起别名


方法2：映射

1．定义标签
2．在标签中，使用resultMap属性替换resultType属性

3.2 查看详情

查看某一条数据的详情信息

1.编写mapper接口

2.定义接受参数

3. 编写sql语句

<select id="selectById" resultMap="brandResultMap">select * from tb_brand where id = #{id};</select>

4.执行sql

参数占位符
1．#{}:会将其替换为 ?，为了防止SQL注入 2．${}:拼sql。会存在SQL注入问题

3.3 条件查询

3.3.1 Mybatics如何接收参数？

1.单个参数

可以直接将参数作为方法的参数进行传递。在 SQL 语句中可以通过 #{paramName} 的形式引用该参数。

// Java 代码
public interface UserMapper {User getUserById(int id);
}<!-- Mapper XML -->
<select id="getUserById" resultType="User">SELECT * FROM user WHERE id = #{id}
</select>

2.使用 @Param 注解来指定参数的名称：

可以使用 @Param 注解来指定参数的名称，然后在 SQL 语句中通过该名称引用参数。

// Java 代码
public interface UserMapper {User getUserByIdAndName(@Param("id") int id, @Param("name") String name);
}<!-- Mapper XML -->
<select id="getUserByIdAndName" resultType="User">SELECT * FROM user WHERE id = #{id} AND name = #{name}
</select>

3.使用 Map 传递参数：

可以使用 Map 类型的参数传递多个参数。在 SQL 语句中通过键来引用对应的值：

// Java 代码
public interface UserMapper {User getUserByMap(Map<String, Object> params);
}<!-- Mapper XML -->
<select id="getUserByMap" resultType="User">SELECT * FROM user WHERE id = #{id} AND name = #{name}
</select>

4.使用对象传递参数：

可以使用自定义的对象作为参数，MyBatis 会自动将对象的属性与 SQL 语句中的参数进行映射。例如：

// Java 代码
public class UserQuery {private int id;private String name;// 省略 getter 和 setter 方法
}public interface UserMapper {User getUserByQuery(UserQuery query);
}<!-- Mapper XML -->
<select id="getUserByQuery" resultType="User">SELECT * FROM user WHERE id = #{id} AND name = #{name}
</select>

3.3.2 多条件查询

步骤

1. 编写多条件查询的接口（mapper）；
2. 添加到映射文件里
3. 编写sql语句
4. 执行，测试

1.定义接口

public interface BrandMapper {List<Brand> selectAll();Brand selectById(int id);  //通过id查看商品详情//使用 @Param 注解来指定参数的名称List<Brand> selectByMutiCondition(@Param("status")int status,@Param("companyName")String companyName,@Param("brandName")String brandName);//使用对象来指定参数List<Brand> selectByMutiCondition(Brand brand);  //使用map来指定参数List<Brand> selectByMutiCondition(HashMap map);}

2.添加到映射文件里

<!--
namespace:名称空间
--><mapper namespace="com.itheima.mapper.BrandMapper"><resultMap id="brandResultMap" type="brand"><result column="brand_name" property="brandName"/><result column="company_name" property="companyName"/></resultMap><!--    多条件查询--><select id="selectByMutiCondition" resultMap="brandResultMap">SELECT * FROM tb_brandWHERE status = #{status} AND company_name LIKE #{companyName} AND brand_name LIKE #{brandName};</select></mapper>

3.执行，测试

//多条件查询-使用参数查询
public class MyBatisTest3 {public static void main(String[] args) throws IOException {int s = 0;String cn = "三只松鼠股份有限公司";String bn = "三只松鼠";//1.加载核心文件，获取SqlSessionFactoryInputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);//2.获取对应的SqlSession对象,用来执行ssqlSqlSession sqlSession = sqlSessionFactory.openSession();//3.执行sqlBrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);List<Brand> brand = brandMapper.selectByMutiCondition(s,cn,bn);System.out.println(brand);//4.释放资源sqlSession.close();}
}

//多条件查询-使用对象
public class MyBatisTest3 {public static void main(String[] args) throws IOException {int s = 0;String cn = "三只松鼠股份有限公司";String bn = "三只松鼠";Brand brand1 = new Brand();brand1.setStatus(s);brand1.setBrandName(bn);brand1.setCompanyName(cn);//1.加载核心文件，获取SqlSessionFactoryInputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);//2.获取对应的SqlSession对象,用来执行ssqlSqlSession sqlSession = sqlSessionFactory.openSession();//3.执行sqlBrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);List<Brand> brand = brandMapper.selectByMutiCondition(brand1);System.out.println(brand);//4.释放资源sqlSession.close();}
}

//多条件查询-使用Map
public class MyBatisTest3 {public static void main(String[] args) throws IOException {int s = 0;String cn = "三只松鼠股份有限公司";String bn = "三只松鼠";HashMap map = new HashMap();map.put("status",s);map.put("companyName",cn);map.put("brandName",bn);//1.加载核心文件，获取SqlSessionFactoryInputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);//2.获取对应的SqlSession对象,用来执行ssqlSqlSession sqlSession = sqlSessionFactory.openSession();//3.执行sqlBrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);List<Brand> brand = brandMapper.selectByMutiCondition(map);System.out.println(brand);//4.释放资源sqlSession.close();}
}

结果：

3.3.3 动态条件查询

动态条件查询是指根据不同的条件组合，构建动态的 SQL 查询语句。
方法

• 使用 <if 元素
• 使用 <choose, <when, <otherwise 元素：
• 使用 <trim, <where, <set元素

使用if
<select id="getUserByCondition" resultType="User">SELECT * FROM userWHERE 1=1<if test="id != null">AND id = #{id}</if><if test="name != null">AND name = #{name}</if>
</select>

使用 choose, when, otherwise 元素
<select id="getUserByCondition" resultType="User">SELECT * FROM userWHERE 1=1<choose><when test="id != null">AND id = #{id}</when><when test="name != null">AND name = #{name}</when><otherwise>AND status = 'ACTIVE'</otherwise></choose>
</select>

使用 trim, where, set元素
<update id="updateUser" parameterType="User">UPDATE user<set><if test="name != null">name = #{name},</if><if test="age != null">age = #{age},</if></set>WHERE id = #{id}
</update>

1.编写接口

List<Brand> selectByMutiConditionActivate(Brand brand);

2.编写动态查询映射文件

<!--    动态查询--><select id="selectByMutiConditionActivate" resultMap="brandResultMap">SELECT * FROM tb_brandWHERE 1=1<if test="status != null">AND status = #{status}</if><if test="companyName != null and companyName!='' ">AND company_name LIKE #{companyName} </if><if test="brandName != null and brandName!='' ">AND brand_name LIKE #{brandName} </if></select>

3.测试

import com.itheima.mapper.BrandMapper;
import com.itheima.pojo.Brand;
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 java.io.IOException;
import java.io.InputStream;
import java.util.HashMap;
import java.util.List;public class MyBatisTest4 {public static void main(String[] args) throws IOException {
//        int s = 1;String cn = "三只松鼠股份有限公司";
//        String bn = "三只松鼠";Brand brand1 = new Brand();
//        brand1.setStatus(s);
//        brand1.setBrandName(bn);brand1.setCompanyName(cn);//1.加载核心文件，获取SqlSessionFactoryInputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);//2.获取对应的SqlSession对象,用来执行ssqlSqlSession sqlSession = sqlSessionFactory.openSession();//3.执行sqlBrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);List<Brand> brand = brandMapper.selectByMutiConditionActivate(brand1);System.out.println(brand);//4.释放资源sqlSession.close();}
}

4.结果

3.3.4 单条件查询

MyBatis 单条件动态查询是指根据单个条件的存在与否，动态地构建 SQL
查询语句。根据不同的条件值，选择是否添加该条件到查询语句中，从而实现根据单个条件进行灵活查询的功能。
使用 <choose，《when> 和 《otherwise> 元素可以实现条件选择逻辑，根据不同的条件选择其中一个分支进行处理

<select id="getUserByCondition" resultType="User">SELECT * FROM userWHERE 1=1<choose><when test="name != null">AND name = #{name}</when><when test="age != null">AND age = #{age}</when><otherwise>AND 1=1</otherwise></choose>
</select>

4.添加

步骤

1. 编写接口方法
2. 编写sql语句，添加映射文件
3. 执行方法，测试

MyBatis事务:

• openSession（）: 默认开启事务，进行增删改操作后需要使用 sqlSession.commit（）;
• 手动提交事务openSession(true): 可以设置为自动提交事务 (关闭事务)

1.编写接口

public interface BrandMapper {//添加void addBrand(Brand brand);
}

2.编辑映射文件

<!--
namespace:名称空间
--><mapper namespace="com.itheima.mapper.BrandMapper"><resultMap id="brandResultMap" type="brand"><result column="brand_name" property="brandName"/><result column="company_name" property="companyName"/></resultMap><insert id="addBrand">insert into tb_brand (brand_name, company_name, ordered,description, status)values (#{brandName},#{companyName},#{ordered},#{description},#{status});</insert></mapper>

3.测试 结果

//添加
public class MybatisTest5 {public static void main(String[] args) throws IOException {String bn = "百度";String cn = "百度公司";int od = 18;String ds = "百度一下你就知道";int st =0;Brand brand = new Brand();brand.setBrandName(bn);brand.setStatus(st);brand.setCompanyName(cn);brand.setDescription(ds);brand.setOrdered(od);//1.加载核心文件，获取SqlSessionFactoryInputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);//2.获取对应的SqlSession对象,用来执行ssqlSqlSession sqlSession = sqlSessionFactory.openSession();//3.执行sqlBrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);brandMapper.addBrand(brand);//4.提交事务sqlSession.commit();//5.关闭sqlSession.close();}
}

主键返回

是什么？

在数据添加成功后，需要获取插入数据库数据的主键的值
比如: 添加订单和订单项
1.添加订单
2.添加订单项，订单项中需要设置所属订单的id

原因：执行完id没有绑定到对象上

解决：MyBatis 框架，可以使用 useGeneratedKeys 和 keyProperty
属性来自动获取生成的主键值，并将其设置到相应的属性上。

<insert id="addBrand" useGeneratedKeys="true" keyProperty="id">insert into tb_brand (brand_name, company_name, ordered,description, status)values (#{brandName},#{companyName},#{ordered},#{description},#{status});</insert>

5.修改

sql语句

UPDATE 表名 SET 列名1=值1,列名2=值2,... [ WHERE 条件];

mybatis标签

<update id="updateOrder" parameterType="Order">UPDATE ordersSET order_number = #{orderNumber}, order_date = #{orderDate}WHERE id = #{id}
</update>

5.1 修改全部字段

public interface BrandMapper {//修改void updateBrand(Brand brand);
}

<update id="updateBrand">update tb_brandset brand_name=#{brandName},company_name=#{companyName},ordered=#{ordered},description=#{description},status=#{status}where id = #{id};</update>

//返回修改全部字段
public class MybatisTest7 {public static void main(String[] args) throws IOException {String bn = "快手";String cn = "fasthand公司";int od = 666;String ds = "老铁加油哇";int st =1;int id = 7;Brand brand = new Brand();brand.setId(id);brand.setBrandName(bn);brand.setStatus(st);brand.setCompanyName(cn);brand.setDescription(ds);brand.setOrdered(od);//1.加载核心文件，获取SqlSessionFactoryInputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);//2.获取对应的SqlSession对象,用来执行ssqlSqlSession sqlSession = sqlSessionFactory.openSession();//3.执行sqlBrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);brandMapper.updateBrand(brand);//4.提交事务sqlSession.commit();//5.关闭sqlSession.close();}
}

5.2 修改动态字段

在 MyBatis 中，你可以使用 元素来实现动态修改字段。 元素根据条件的成立与否，决定是否包含某字段的修改语句片段。

void updateBrandActivate(Brand brand);

<update id="updateBrandActivate">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 and ordered!='' ">ordered=#{ordered}</if><if test="description != null and description!='' ">description=#{description}</if><if test="status != null and status!='' ">status=#{status}</if></set>where id = #{id};</update>

//返回动态修改字段
public class MybatisTest8 {public static void main(String[] args) throws IOException {String bn = "快手";String cn = "fasthand公司";int od = 666;String ds = "先穿裤子在穿鞋，先当孙子再当爷，记住这句话！";int st =0;int id = 7;Brand brand = new Brand();brand.setId(id);
//        brand.setBrandName(bn);brand.setStatus(st);
//        brand.setCompanyName(cn);brand.setDescription(ds);
//        brand.setOrdered(od);//1.加载核心文件，获取SqlSessionFactoryInputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);//2.获取对应的SqlSession对象,用来执行ssqlSqlSession sqlSession = sqlSessionFactory.openSession();//3.执行sqlBrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);brandMapper.updateBrandActivate(brand);//4.提交事务sqlSession.commit();//5.关闭sqlSession.close();}
}

6.删除

sql语句

DELETE FROM 表名 [WHERE 条件] ;

6.1 删除1个

void deleteOne(int id);

<delete id="deleteOne">delete from tb_brand where id = #{id};</delete>

//删除1public class MybatisTest9 {public static void main(String[] args) throws IOException {int id = 6;//1.加载核心文件，获取SqlSessionFactoryInputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);//2.获取对应的SqlSession对象,用来执行ssqlSqlSession sqlSession = sqlSessionFactory.openSession();//3.执行sqlBrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);brandMapper.deleteOne(id);//4.提交事务sqlSession.commit();//5.关闭sqlSession.close();}
}

6.2 批量删除

void deleteMuti(@Param("ids")int[] ids);

<delete id="deleteMuti">delete from tb_brandwhere idin(<foreach collection="ids" item="id" separator=",">#{id}</foreach>);</delete>

//删除1public class MybatisTest9 {public static void main(String[] args) throws IOException {int[] ids = {1,5,7};//1.加载核心文件，获取SqlSessionFactoryInputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);//2.获取对应的SqlSession对象,用来执行ssqlSqlSession sqlSession = sqlSessionFactory.openSession();//3.执行sqlBrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);brandMapper.deleteMuti(ids);//4.提交事务sqlSession.commit();//5.关闭sqlSession.close();}
}