问题描述：当用户需要传入多个参数（如：3个条件）进行查询时，用户没有输入全部的（如：输入2个）查询条件的情况下，
select * from tb_brand
and status=#{status}
and brand_name = #{brandName}
and company_name like concat("%",#{companyName},"%");
配套测试类：
@Test
public void selectByConditionTest() throws Exception{
InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
//解析配置文件
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
SqlSessionFactory sqlSessionFactory = builder.build(in);
//开启会话
SqlSession sqlSession = sqlSessionFactory.openSession();
//得到接口类代理对象
BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);
Brand brand = new Brand();
brand.setStatus(1);
brand.setcompanyName("华为");
List brands = mapper.selectByCondition(brand);
System.out.println(brands);
}
二、当主键设置自增时，需要数据插入时返回主键的键值
insert into tb_brand(id,brand_name,company_name,ordered,description,status)
values (#{id},#{brandName},#{companyName},#{ordered},#{description},#{status})
配套测试类：
@Test
public void addBrandTest() throws Exception{
InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
//解析配置文件
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
SqlSessionFactory sqlSessionFactory = builder.build(in);
//开启会话
SqlSession sqlSession = sqlSessionFactory.openSession();
//得到接口类代理对象
BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);
Brand brand = new Brand();
brand.setStatus(1);
brand.setbrandName("超星");
int row = mapper.addBrand(brand);
sqlSession.commit();
sqlSession.close();
System.out.println(brand);
}
三、批量删除
假如以获取多个id值后进行批量删除，我们首先将id值保存进数组中，用代码演示前端页面存入的数组，获取到id值为6,7,8的三条数据：
int[] id = new int[]{6,7,8};
XxxMapper.java文件中delet方法：
int deleteById(@Param("ids") int[] id);
sql映射文件：
delete from tb_brand
id in
#{id}
配套测试类：
@Test
public void deleteByIdTest() throws Exception{
InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
//解析配置文件
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
SqlSessionFactory sqlSessionFactory = builder.build(in);
//开启会话
SqlSession sqlSession = sqlSessionFactory.openSession();
//得到接口类代理对象
BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);
int[] id = new int[]{6,7,8};
int row = mapper.deleteById(id);
sqlSession.commit();
System.out.println(row);
}
四、修改全部也可以挑其中的几个进行修改
Mapper文件：
int updateAll(Brand brand);
sql映射文件：
update tb_brand set brand_name = #{brandName},company_name=#{companyName},ordered=#{ordered},description=#{description},status=#{status}
where id = #{id}
配套测试类：
@Test
public void updateBrand() throws Exception{
InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
//解析配置文件
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
SqlSessionFactory sqlSessionFactory = builder.build(in);
//开启会话
SqlSession sqlSession = sqlSessionFactory.openSession();
//得到接口类代理对象
BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);
Brand brand = new Brand();
brand.setbrandName("阿迪达斯1");
brand.setcompanyName("学习通1");
brand.setDescription("5483214");
brand.setOrdered(5);
brand.setStatus(1);
brand.setId(18);
int row = mapper.updateAll(brand);
sqlSession.commit();
System.out.println(row);
}
五、动态修改（也是可以进行其中几项的修改）
mapper.java文件：
int updateCondition(Brand brand);
sql映射文件：
update tb_brand
brand_name = #{brandName},
company_name=#{companyName},
ordered=#{ordered},
description=#{description},
status=#{status}
where id = #{id}
配套测试类：
@Test
public void updateConditionTest() throws Exception{
InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
//解析配置文件
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
SqlSessionFactory sqlSessionFactory = builder.build(in);
//开启会话
SqlSession sqlSession = sqlSessionFactory.openSession();
//得到接口类代理对象
BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);
Brand brand = new Brand();
brand.setbrandName("超星1");
brand.setcompanyName("学习通1");
brand.setId(13);
int row = mapper.updateCondition(brand);
sqlSession.commit();
System.out.println(row);
}
注解开发：
替代大部分映射配置文件的操作，简单操作可用，复杂的sql操作不建议使用。注解和sql映射文件只能写一个，不能叠加使用。
@Insert
@Select
@Update
@Delete
mapper.java文件中：
@Select("select * from tb_brand")
List selectAnnoAll();
@Select("select * from tb_brand where id = #{id}")
List selectAnnoById(int id);
@Insert("insert into tb_brand(id,brand_name,company_name,ordered,description,status) " +
"values(#{id},#{brandName},#{companyName},#{ordered},#{description},#{status})")
@SelectKey(statement="select last_insert_id()", keyProperty="id", before=false, resultType=Integer.class)
int addAnnoBrand(Brand brand);
@Delete("delete from tb_brand where id = #{id}")
int deleteAnnoBrandById(int id);
@Update("update tb_brand set id=#{id},company_name=#{companyName},ordered=#{ordered},description=#{description},status=#{status} where brand_name = #{brandName}")
int updateAnnoByBrandName(Brand brand);
其中，在@Insert注解中也可以使用注解的方式获得主键的返回值
方法一：
@SelectKey(statement="select LAST_INSERT_ID()", keyProperty="uid", before=false, resultType=Integer.class)
statement：执行的sql语句
keyProperty：将主键放到的位置
before true：在插入之前获取
before false：在插入之后获取
resultType：主键的类型
方法二： @Options(keyProperty = "uid", useGeneratedKeys = true, keyColumn = "uid") keyProperty：主键存放的位置 useGeneratedKeys true ：使用自动生成的主键 keyColumn：主键列的名字
