每日记录（八）2023.02.28
今天是个好日子，
条件查询
- 多条件查询
编写接口方法:Mapper接口
*参数：所有条件查询
*结果：List<Brand>
2.编写SQl语句:SQl映射文件
3.执行方法，测试
BrandMapper.Java
//条件查询，多条件查询
/*
* 参数接受
* *1.散装参数：如果方法中又多个参数，需要使用@Parameter（SQL参数占位符）
* *2.对象参数：对象的属性名的名称和参数占位符一致
* 3.map集合参数
*
* *
* */
//散装参数
// List<Brand>selectByCondition(@Param("status") int status,@Param("companyName") String companyName,@Param("brandName") String brandName);
// 实体类封装参数
//List<Brand>selectByCondition(Brand brand);
//map集合
List<Brand>selectByCondition(Map map);
MaybatisDemo.java
//*散装参数
int status=1;
String companyName = "华为";
String brandName = "华为";
//处理参数
companyName= "%" + companyName + "%";
brandName= "%" +brandName + "%";
//------------------------------------------------------------------
//封装对象
/* Brand brand = new Brand();
brand.setStatus(status);
brand.setBrandName(brandName);
brand.setCompanyName(companyName);*/
Map map=new HashMap();
map.put("status" , status);
map.put("companyName" , companyName);
map.put("brandName" , brandName);
//-------------------之下------------------//
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//2、获取sql.....对象，用来执行sql
SqlSession sqlSession = sqlSessionFactory.openSession();
//执行sql语句
BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);
//------------------------------之上不用变--------------------//
//List<Brand> brands = mapper.selectAll();
// for(Brand u:brands)
//{
// System.out.println(u);
// }
// Brand brand=mapper.selectById(id);
//参数
//List<Brand> brands = mapper.selectByCondition(status, companyName, brandName);
//封装
//List<Brand> brands = mapper.selectByCondition(brand);
//map集合
List<Brand> brands = mapper.selectByCondition(map);
System.out.println(brands);
//4、释放资源
sqlSession.close();
SQL语句
<!--
条件查询
-->
<select id="selectByCondition" resultMap="brandResultMap">
select *
from tb_brand
where
status = #{status}
and company_name like #{companyName}
and brand_name like #{brandName}
</select>
多条件动态查询
<select id="selectByCondition" resultMap="brandResultMap">
select *
from tb_brand
<!-- where 1 = 1-->
<where>
<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>
</where>
If：用于判断参数是否有值
动态条件查询
*if:条件判断
*test：逻辑表达式
*问题：
*恒等式 1=1
*<where> 替换where关键字
*
单条件动态查询
从多个条件中选择一个
Choose(when otherwise):选择，类似于java中的switch
SQL
<!-- <select id="selectByConditionSingle" resultMap="brandResultMap">
select *
from tb_brand
where
<choose><!–相当于switch–>
<when test="status != null"><!–相当于case–>
status=#{status}
</when>
<when test="companyName != null and companyName != ''"><!–相当于case–>
company_name like #{companyName}
</when>
<when test="brandName != null and brandName != ''"><!–相当于case–>
brand_name like #{brandName}
</when>
<otherwise>
1=1
</otherwise>
</choose>
</select>-->
<select id="selectByConditionSingle" resultMap="brandResultMap">
select *
from tb_brand
<where>
<choose><!--相当于switch-->
<when test="status != null"><!--相当于case-->
status=#{status}
</when>
<when test="companyName != null and companyName != ''"><!--相当于case-->
company_name like #{companyName}
</when>
<when test="brandName != null and brandName != ''"><!--相当于case-->
brand_name like #{brandName}
</when>
</choose>
</where>
</select>
Demo
Brand brand = new Brand();
// brand.setStatus(status);
brand.setBrandName(brandName);
//brand.setCompanyName(companyName);
List<Brand> brands = mapper.selectByConditionSingle(brand);
System.out.println(brands);
添加
编写接口方法：Mapper接口
参数：除了id
结果：void
编写SQL语句：SQL映射文件
<insert id="add">
insert into tb_brand (brand_name, company_name, ordered, description,status)
values (#{brandName},#{companyName},#{ordered},#{description},#{status})
</insert>
执行方法，测试
Mabytis事务
opensession()：默认开启事务，进行增删改操作后需要使用sqlSession.commit();手动提交。
opensession(true)：可设置为自动提交（关闭事务）。
int status=1;
String companyName = "苹果公司";
String brandName = "苹果";
int ordered = 100;
String description = "手机中的战斗机";
//------------------------------------------------------------------
//封装对象
Brand brand = new Brand();
brand.setStatus(status);
brand.setBrandName(brandName);
brand.setCompanyName(companyName);
brand.setDescription(description);
brand.setOrdered(ordered);
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//2、获取sql.....对象，用来执行sql
// SqlSession sqlSession = sqlSessionFactory.openSession();
SqlSession sqlSession = sqlSessionFactory.openSession(true);
//执行sql语句
BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);
mapper.add(brand);
// 提交事务
sqlSession.commit();
//4、释放资源
sqlSession.close();
主键返回
<insert useGeneratedKeys="true" keyProperty="id">
<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>
Integer id = brand.getId();
System.out.println(id);
修改
修改全部字段
没修改的，会被设置成null
<update id="update">
update tb_brand
set brand_name = #{brandName},
company_name = #{companyName},
ordered = #{ordered},
description = #{description},
status = #{status}
where id=#{id};
</update>
Mapper
int update(Brand brand);
测试
int count = mapper.update(brand);
System.out.println(count);
修改动态字段
<update id="update">
update tb_brand
<set>
<if test="companyName != null and companyName != '' ">
company_name = #{companyName},
</if>
<if test="brandName != null and brandName != '' ">
brand_name = #{brandName},
</if>
<if test="description != null and description != '' ">
description = #{description},
</if>
<if test="ordered != null ">
ordered = #{ordered},
</if>
<if test="status != null ">
status = #{status}
</if>
</set>
where id=#{id};
</update>
删除
删除一个
Void deleteById(id);
<delete id="deleteById">
delete from tb_brand where id=#{id};
</delete>
mapper.deleteById(id);
// 提交事务
sqlSession.commit();
删除多个
void deleteByIds(@Param("ids") int[] ids);
<!--
*mybatis会将数组参数，封装为一个Map集合。
*默认为 array=数组
*使用@param注解改变map集合默认key的名称
-->
<delete id="deleteByIds">
delete from tb_brand where id
in(
<foreach collection="ids" item="id" separator=",">
#{id}
</foreach>
);
</delete>
mapper.deleteByIds(id);
浙公网安备 33010602011771号