目录
Mybatis 针对 CURD 操作都提供了对应的注解，已经做到见名知意。如下：
查询 ：@Select
添加 ：@Insert
修改 ：@Update
删除 ：@Delete
接下来我们做一个案例来使用 Mybatis 的注解开发
针对上述的需要，Mybatis对动态SQL有很强大的支撑：
if
choose (when, otherwise)
trim (where, set)
foreach
单条件查询
如上图所示，在查询时只能选择 品牌名称、当前状态、企业名称 这三个条件中的一个，但是用户到底选择哪儿一个，我们并不能确定。这种就属于单个条件的动态SQL语句。
这种需求需要使用到 choose（when，otherwise）标签 实现， 而 choose 标签类似于Java 中的switch语句。
通过一个案例来使用这些标签
数据库表（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', 实体类 Brand
在 com.itheima.pojo 包下创建 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;
//省略 setter and getter。自己写时要补全这部分代码
}编写测试用例
测试代码需要在 test/java 目录下创建包及测试用例。项目结构如下：
package com.itheima.test;
public class MybatisTest {
}
下载MybatisX插件
MybatisX 是一款基于 IDEA 的快速开发插件，为效率而生。
主要功能
XML映射配置文件 和 接口方法 间相互跳转
根据接口方法生成 statement
安装方式
重启一下
红色头绳的表示映射配置文件，蓝色头绳的表示mapper接口。在mapper接口点击红色头绳的小鸟图标会自动跳转到对应的映射配置文件，在映射配置文件中点击蓝色头绳的小鸟图标会自动跳转到对应的mapper接口。也可以在mapper接口中定义方法，自动生成映射配置文件中的 statement
查询所有数据思路
编写接口方法：Mapper接口
参数：无
查询所有数据功能是不需要根据任何条件进行查询的，所以此方法不需要参数。
结果：List<Brand>
我们会将查询出来的每一条数据封装成一个
Brand对象，而多条数据封装多个Brand对象，需要将这些对象封装到List集合中返回。执行方法、测试
创建名为 BrandMapper.java接口
在 com.itheima.mapper 包写创建名为 BrandMapper 的接口。并在该接口中定义 List<Brand> selectAll() 方法。
package com.itheima.mapper;
import com.itheima.pojo.Brand;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import java.util.List;
import java.util.Map;
public interface BrandMapper {
//查询所有的方法
@Select("select * from tb_brand")
public List<Brand> selectAll();
//按照id查找信息
Brand selectById(int id);
//有条件查询三种方法
List<Brand> selectByCondition(@Param("status") int status,
@Param("companyName") String companyName,
@Param("brandName") String brandName);
// List<Brand> selectByCondition(Brand brand);
List<Brand> selectByCondition(Map map);
//单条件查询
List<Brand> selectByConditionSingle(Brand brand);
//添加功能·
void add(Brand brand);
//动态修改
int update(Brand brand);
//删除一条记录
void deleteById(int id);
//批量删除
void deleteByIds(@Param("ids")int[] ids);
}
编写测试方法MybatisTest 类
package com.itheima.test;
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 org.junit.Test;
import java.io.IOException;
import java.io.InputStream;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class MybatisTest {
//1.查询所有
@Test
public void testSelecctAll() throws IOException {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
List<Brand> brands = brandMapper.selectAll();
System.out.println(brands);
sqlSession.close();
}
//2.查询详细
@Test
public void testSelecctByIdl() throws IOException {
int id = 1;
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
//改变sql语句
Brand brand = brandMapper.selectById(id);
System.out.println(brand);
sqlSession.close();
}
//3.多条件查询
@Test
public void testselectByCondition() throws IOException {
int status = 1;
String companyName = "华为";
String brandName = "华为";
//数据处理
companyName = "%" + companyName + "%";
brandName = "%" + brandName + "%";
/* Brand brand = new Brand();
brand.setStatus(status);
brand.setCompanyName(companyName);
brand.setBrandName(brandName);*/
//List<Brand> brands = brandMapper.selectByCondition(brand);
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
//方式三 ：接口方法参数是 map集合对象 方式调用的方法
Map map = new HashMap();
// map.put("status" , status);
map.put("companyName", companyName);
// map.put("brandName" , brandName);
//改变sql语句
List<Brand> brands = brandMapper.selectByCondition(map);
System.out.println(brands);
// List<Brand> brands = brandMapper.selectByCondition(status, companyName, brandName);
System.out.println(brands);
sqlSession.close();
}
//4.单条件查询
@Test
public void testselectByConditionSingle() throws IOException {
int status = 1;
String companyName = "华为";
String brandName = "华为";
//数据处理
companyName = "%" + companyName + "%";
brandName = "%" + brandName + "%";
Brand brand = new Brand();
brand.setStatus(status);
// brand.setCompanyName(companyName);
// brand.setBrandName(brandName);
//List<Brand> brands = brandMapper.selectByCondition(brand);
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
//改变sql语句
List<Brand> brands = brandMapper.selectByConditionSingle(brand);
System.out.println(brands);
sqlSession.close();
}
//5.普通添加功能
@Test
public void testadd() throws IOException {
int status = 1;
String companyName = "刘德华品牌手机";
String brandName = "红米";
String description = "号的中国制造";
int order = 100;
//数据处理
// companyName="%"+companyName+"%";
// brandName="%"+brandName+"%";
Brand brand = new Brand();
brand.setStatus(status);
brand.setCompanyName(companyName);
brand.setBrandName(brandName);
brand.setDescription(description);
brand.setOrdered(order);
//List<Brand> brands = brandMapper.selectByCondition(brand);
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession(true);//代表提交事务
BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
//改变sql语句
brandMapper.add(brand);
Integer id = brand.getId();
System.out.println(id);
//提交事务
// sqlSession.commit();
//关闭资源
sqlSession.close();
}
//6.动态修改功能
@Test
public void testupdate() throws IOException {
int status = 1;
String companyName = "kk";
String brandName = "kk";
String description = "号的中国制造";
int order = 100;
int id=5;
//数据处理
// companyName="%"+companyName+"%";
// brandName="%"+brandName+"%";
Brand brand = new Brand();
brand.setStatus(status);
brand.setCompanyName(companyName);
brand.setBrandName(brandName);
// brand.setDescription(description);
// brand.setOrdered(order);
brand.setId(id);
//List<Brand> brands = brandMapper.selectByCondition(brand);
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession(true);//代表提交事务
BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
//改变sql语句
int count = brandMapper.update(brand);
System.out.println(count);
//提交事务
// sqlSession.commit();
//关闭资源
sqlSession.close();
}
//删除一条记录
@Test
public void testdeleteById() throws IOException {
int id=5;
//数据处理
// companyName="%"+companyName+"%";
// brandName="%"+brandName+"%";
//List<Brand> brands = brandMapper.selectByCondition(brand);
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession(true);//代表提交事务
BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
//改变sql语句
brandMapper.deleteById(id);
//提交事务
// sqlSession.commit();
//关闭资源
sqlSession.close();
}
//批量删除
@Test
public void testdeleteByIds() throws IOException {
int[] ids={6,7,8};
//数据处理
// companyName="%"+companyName+"%";
// brandName="%"+brandName+"%";
//List<Brand> brands = brandMapper.selectByCondition(brand);
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession(true);//代表提交事务
BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
//改变sql语句
brandMapper.deleteByIds(ids);
//提交事务
// sqlSession.commit();
//关闭资源
sqlSession.close();
}
}
编写BrandMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--mapper配置文件-->
<!-- namespace指的是命名空间
resultType=写上的是poio类的位置
-->
<!--statement-->
<mapper namespace="com.itheima.mapper.BrandMapper">
<!--resultMapper用来起别名，这样就可以用来替换不正确的命名-->
<resultMap id="brandresultMapper" type="brand">
<result column="brand_Name" property="brandName"/>
<result column="company_Name" property="companyName"/>
</resultMap>
<select id="selectAll" resultMap="brandresultMapper">
select * from tb_brand;
<!-- select *from tb_brand;-->
</select>
<!-- 2. 查询2-->
<!-- mybatis提供了两种参数占位符：
* #{} ：执行SQL时，会将 #{} 占位符替换为？，将来自动设置参数值。从上述例子可以看出使用#{} 底层使用的是 `PreparedStatement`
* ${} ：拼接SQL。底层使用的是 `Statement`，会存在SQL注入问题。如下图将 映射配置文件中的 #{} 替换成 ${} 来看效果-->
<select id="selectById" resultMap="brandresultMapper">
select *
from tb_brand
where id = #{id};
</select>
<!--多条件动态查询-->
<select id="selectByCondition" resultMap="brandresultMapper">
select *
from tb_brand
<!--#用<where></where>标签1解决随机输入问题-->
<where>
<if test="status!=null">
and status = #{status}
</if>
<if test="company_name!=null and company_name !='' ">
and company_name like #{companyName}
</if>
<if test="brandName!=null and brandName!='' ">
and brand_name like #{brandName}
</if>
</where>
</select>
<!-- 单条件动态查询-->
<select id="selectByConditionSingle" resultMap="brandresultMapper">
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>
<!-- 添加功能（注意主键返回）-->
<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>
<!-- 修改功能-->
<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>
<!-- 删除一条数据-->
<delete id="deleteById">
delete
from tb_brand
where id = #{id};
</delete>
<!-- //批量化删除数据void deleteByIds(@Param("ids")int[] ids);-->
<delete id="deleteByIds">
delete
from tb_brand
where id in
<foreach collection="ids" item="id" separator="," open="(" close=")">
#{id}
</foreach>
;
</delete>
</mapper>运行结果
数据库记录也删除成功
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
