Mybatis中的sql练习
1.配置文件实现CURD
1.1环境准备
数据库表(tb_brand)以及数据准备
-- 删除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);
实体类Brand
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 Brand() {
}
public Brand(Integer id, String brandName, String companyName, Integer ordered, String description, Integer status) {
this.id = id;
this.brandName = brandName;
this.companyName = companyName;
this.ordered = ordered;
this.description = description;
this.status = 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;
}
@Override
public String toString() {
return "Brand{" +
"id=" + id +
", brandName='" + brandName + ''' +
", companyName='" + companyName + ''' +
", ordered=" + ordered +
", description='" + description + ''' +
", status=" + status +
'}';
}
}
1.2查询所有数据
编写mapper接口
List<Brand> selectAll();
编写SQL语句
<select id="selectAll" resultType="Brand">
select * from tb_brand
</select>
编写测试方法
private SqlSessionFactory sqlSessionFactory;
@Before
public void before() throws IOException {
//1. 获取SqlSessionFactory
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
}
@Test
public void testSelectAll() throws IOException {
//2. 获取SqlSession对象
SqlSession sqlSession = sqlSessionFactory.openSession();
//3. 获取Mapper接口的代理对象
BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);
//4. 执行方法
List<Brand> brands = mapper.selectAll();
System.out.println(brands);
//5. 释放资源
sqlSession.close();
}
1.3查询详情(根据id查询)
编写mapper接口
Brand selectById(int id)
编写SQL语句
<select id="selectById" resultType="Brand">
select * from tb_brand where id = #{id}
</select>
编写测试方法
@Test
public void testSelectById() throws IOException {
//2. 获取SqlSession对象
SqlSession sqlSession = sqlSessionFactory.openSession();
//3. 获取Mapper接口的代理对象
BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);
//4. 执行方法
Brand brand = mapper.selectById(2);
System.out.println(brand);
//5. 释放资源
sqlSession.close();
}
1.4多条件查询
编写mapper接口
List<Brand> selectByCond(Brand brand);
编写SQL语句
<!--多条件模糊查询-->
<select id="selectByCond" resultType="Brand">
select *
from tb_brand
<where>
<if test="brandName != null and brandName !=''">
and brand_name like concat('%', #{brandName}, '%')
</if>
<if test="companyName != null and companyName !=''">
and company_name like concat('%', #{companyName}, '%')
</if>
</where>
</select>
if,通过test属性中的表达式判断标签中的内容是否有效(是否会拼接到sql中)
where,
若where标签中有条件成立,会自动生成where关键字;
会自动将where标签中内容前多余的and去掉,但是其中内容后多余的and无法去掉
若where标签中没有任何一个条件成立,则where没有任何功能
trim,
prifix、suffix:在标签中内容前面或者后面添加指定内容
prifixOverrides、suffixOverrides:在标签中内容前面或者后面去掉指定内容
@Test
public void testSelectByCond(){
// 1: 获取核心对象
SqlSession session = sqlSessionFactory.openSession();
// 2: 获取接口对象
BrandMapper mapper = session.getMapper(BrandMapper.class);
// 3: 调用方法,查所有
Brand b = new Brand(null,null,null,null,null,null);
List<Brand> brands = mapper.selectByCond(b);
System.out.println(brands);
// 4: 提交事务
session.commit();
session.close();
}
1.5多选一的单条件模糊查询
编写mapper接口
List<Brand> selectBySingle(Brand brand);
编写SQL语句
<!--多选1的单条件查询-->
<select id="selectBySingle" resultType="Brand">
select *
from tb_brand
<where>
<choose>
<when test="brandName != null and brandName !=''">
brand_name like concat('%', #{brandName}, '%')
</when>
<when test="companyName != null and companyName !=''">
company_name like concat('%', #{companyName}, '%')
</when>
</choose>
</where>
</select>
编写测试
@Test
public void testSelectBySingle(){
// 1: 获取核心对象
SqlSession session = sqlSessionFactory.openSession();
// 2: 获取接口对象
BrandMapper mapper = session.getMapper(BrandMapper.class);
// 3: 调用方法,查所有
Brand b = new Brand(null,null,"公司",null,null,null);
List<Brand> brands = mapper.selectBySingle(b);
System.out.println(brands);
// 4: 提交事务
session.commit();
session.close();
}
1.6添加数据后的主键返回
编写mapper接口
void insertBrand(Brand b);
编写SQL语句
<!--添加品牌信息的方法-->
<insert id="insertBrand" useGeneratedKeys="true" keyProperty="id">
insert into tb_brand
values (null,#{brandName},#{companyName},#{ordered},#{description},#{status})
</insert>
测试
@Test
public void testInsert(){
// 1: 获取核心对象
SqlSession session = sqlSessionFactory.openSession();
// 2: 获取接口对象
BrandMapper mapper = session.getMapper(BrandMapper.class);
// 3: 调用方法,查所有
Brand b = new Brand(null,"小辣椒222","小辣椒公司222",22,"辣的不要不要的",1);
mapper.insertBrand(b);
// 4: 提交事务
session.commit();
System.out.println(b+"-------------------------------------------------------");
session.close();
}
1.7动态修改
编写mapper接口
void updateBrandByCond(Brand b);
编写SQL语句
<!--根据数据是否有效,动态修改数据库的信息-->
<update id="updateBrandByCond">
update tb_brand
<set>
<if test="brandName != null and brandName != ''">
brand_name=#{brandName},
</if>
<if test="companyName != null and companyName != ''">
company_name=#{companyName},
</if>
</set>
where id=#{id}
</update>
测试
@Test
public void testUpdateByCond(){
// 1: 获取核心对象
SqlSession session = sqlSessionFactory.openSession();
// 2: 获取接口对象
BrandMapper mapper = session.getMapper(BrandMapper.class);
// 3: 调用方法,查所有
Brand b = new Brand(6,"","打蜡机",22,"辣的不要不要的",1);
mapper.updateBrandByCond(b);
// 4: 提交事务
session.commit();
session.close();
}
1.8批量添加
编写mapper接口
void addMore(@Param("brands") List<Brand> brands);
编写SQL语句
<insert id="addMore">
insert into tb_brand values
<foreach collection="brands" separator="," item="brand">
(null,#{brand.brandName},#{brand.companyName},#{brand.ordered},#{brand.description},#{brand.status})
</foreach>
</insert>
测试
@Test
public void testAddMore() throws IOException {
//2. 获取SqlSession对象
SqlSession sqlSession = sqlSessionFactory.openSession();
//3. 获取Mapper接口的代理对象
BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);
//4. 执行方法
Brand brand = new Brand(null, "大王", "123", 33, "你好好", 1);
Brand brand1 = new Brand(null, "大王1", "123", 33, "你好好1", 1);
Brand brand2 = new Brand(null, "大王2", "123", 33, "你好好2", 1);
List<Brand> brands = Arrays.asList(brand, brand1, brand2);
mapper.addMore(brands);
System.out.println("===========================");
//一定要记得提交事务
sqlSession.commit();
//5. 释放资源
sqlSession.close();
}
1.9根据id删除一个数据
编写mapper接口
void delById(int id);
编写SQL语句
<!--根据id删除一个数据-->
<delete id="delById">
delete
from tb_brand
where id=#{id};
</delete>
测试
@Test
public void testdelById(){
// 1: 获取核心对象
SqlSession session = sqlSessionFactory.openSession();
// 2: 获取接口对象
BrandMapper mapper = session.getMapper(BrandMapper.class);
// 3: 调用方法,查所有
mapper.delById(6);
// 4: 提交事务
session.commit();
session.close();
}
1.10根据数组删除多个数据
编写mapper接口
void delByIds(@Param("ids") int[] ids);
编写SQL语句
<!--根据数组中的多个id,批量删除数据-->
<delete id="delByIds">
delete
from tb_brand
where id in
<foreach collection="ids" item="id" close=")" open="(" separator=",">
#{id}
</foreach>
</delete>
foreach,
collection:设置要循环的数组或集合
item:用一个字符串表时数据或者集合中的每一个数据
separator:设置每次循环的数据之间的分隔符
open:循环的所有内容以什么开始
close:循环的所有内容以什么结束
测试
@Test
public void testdelByIds(){
// 1: 获取核心对象
SqlSession session = sqlSessionFactory.openSession();
// 2: 获取接口对象
BrandMapper mapper = session.getMapper(BrandMapper.class);
// 3: 调用方法
int[] ids = {4,5};
mapper.delByIds(ids);
// 4: 提交事务
session.commit();
session.close();
}