本文通过一个详细的示例展示了如何使用MybatisX插件进行数据库的品牌数据操作，包括查询、添加、修改和删除功能。详细讲解了接口定义、Mapper XML配置以及测试用例，涵盖了多条件动态查询和单条件查询。此外，还介绍了MybatisX插件的主要功能，如XML和接口方法的快速跳转，以及动态SQL的实现，提升了开发效率。
l 完成品牌数据的增删改查操作
§ 要完成的功能列表清单：
□ 查询
® 查询所有数据
® 查看详情
® 条件查询
□ 添加
□ 修改
® 修改全部字段
® 修改动态字段
□ 删除
® 删除一个
® 批量删除
准备环境：
§ 数据库表tb_brand
drop table if exists 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
§ 测试用例
§ 安装MybatisX插件
□ MybatisX是一款基于IDEA的快速开发插件，为效率而生。
□ 主要功能：
® XML和接口方法相互跳转
® 根据接口方法生成statrment
□ 安装：
接口：BrandMapper.java
import com.itheima.Pojo.Brand;
import org.apache.ibatis.annotations.Param;
import java.util.List;
import java.util.Map;
public interface BrandMapper {
/**
* 查询所有
* @return
*/
List<Brand> selectAll();
/**
* 查看详情：根据id查询
*/
Brand selectById(int id);
//使用${}占位符时要加入注解
// Brand selectById(@Param("id") int id);
/**
*
*查询-多条件查询
* *参数接收
* 1.散装参数：如果方法有多个参数，需要@Param("SQL参数占位符名称")
* 2.Brand对象参数：对象的属性名称要和参数占位符名称一致
* 3.map集合参数：map集合的键名称要和参数占位符名称保持一致
*
* @param status
* @param companyName
* @param brandName
* @return
*/
//参数包含所有的查询条件
//当存在多个参数时，方法有多个参数，需要@Param注解标注一下，参数需要传递给谁
/*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);
/**
* 单条件动态查询
* @param brand
* @return
*/
List<Brand> selectByConditionSingle(Brand brand);
/**
* 添加
*/
void add(Brand brand);
/**
* 修改
*/
int update(Brand brand);
/**
* 根据id删除一行
*/
int deleteById(int id);
/**
* 根据id批量删除
*/
int deleteByIds(@Param("ids")int[] ids);
}
BrandMapper.xml配置文件
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--
namespace:名称空间
-->
<mapper namespace="com.itheima.mapper.BrandMapper">
<!--
数据库表的字段名称 和 实体类的属性名称不一样，则不能自动封装
*起别名：给不一样的列名起别名，让列名和实体类的属性名一样
*缺点：每次查询都要定义一次别名
*<使用sql片段可以解决>
*缺点：不灵活
*resultMap：
1.定义<resultMap>标签
2.在<select>标签中，使用resultMap属性替换resultType
-->
<resultMap id="brandResultMap" type="Brand">
<!--
id:完成主键字段的映射
column：表的列名
property：实体类的属性名
result:完成一般字段的映射
column：表的列名
property：实体类的属性名
-->
<result column="brand_name" property="brandName"/>
<result column="company_name" property="companyName"/>
</resultMap>
<!--查询所有-->
<select id="selectAll" resultMap="brandResultMap">
select *
from tb_brand;
</select>
<!--根据id查看详情-->
<!--
*参数占位符：
1.#{}: 会将其替换成?
2.${}: 拼sql，会存在sql注入问题
3.使用时机
*参数传递的时候：#{}
*表名或者列名不固定的情况下：${}
*参数类型：parameterType可以省略
*特殊字符处理：
1.转义字符(<)转义"<"符号
2.CDATA区
-->
<select id="selectById" resultMap="brandResultMap">
select *
from tb_brand where id = #{id};
</select>
<!--<select id="selectById" resultMap="brandResultMap">
select *
-- from tb_brand where id < #{id};
<![CDATA[
<
]]>
</select>-->
<!--
查询-多条件查询
-->
<!--<select id="selectByCondition" resultMap="brandResultMap">
select *
from tb_brand
where status = #{status}
and company_name like #{companyName}
and brand_name like #{brandName};
</select>-->
<!--
动态条件查询，动态SQL
*if 条件判断：
*test：逻辑表达式
*有个问题就是当第一个条件不成立并且后面条件成立时sql语句就变成了where后面加and xxx
*解决方案：
1.恒等式，在where后面加一个恒等式，并且在第一个条件的执行语句前加and
2.<where>替换掉sql中的where关键字
-->
<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>
</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>
<otherwise> <!--相当于default-->
1=1
</otherwise>
</choose>
</select>
<!--
添加
-->
<insert id="add" useGeneratedKeys="true" keyProperty="id">
insert into tb_brand(brand_name, company_name, ordered, description, status)
value (#{brandName}, #{companyName}, #{ordered}, #{description}, #{status});
</insert>
<!--
修改
-->
<!--<update id="update">
update tb_brand
set brand_name = #{brandName},
company_name = #{companyName},
ordered = #{ordered},
description = #{description},
status = #{status}
where id = #{id};
</update>-->
<!--
动态修改
-->
<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>
<!--
批量删除
-->
<delete id="deleteByIds">
delete from tb_brand
where id in
<foreach collection="ids" item="id" separator="," open="(" close=")">
#{id}
</foreach>
</delete>
<!--
sql片段
-->
<!--<sql id="brand_column">id, brand_name as brandName, company_name as companyName, ordered, description, status</sql>
<select id="selectAll" resultType="Brand">
select
<include refid="brand_column"/>
from tb_brand;
</select>
<!–statement–>
<select id="selectAll" resultType="Brand">
select *
from tb_brand;
</select>-->
</mapper>
测试代码：
import com.itheima.Pojo.Brand;
import com.itheima.mapper.BrandMapper;
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.InputStream;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class MybatisTest {
/**
* 测试练习：查询所有数据
*
* @throws Exception
*/
@Test
public void testSelectAll() throws Exception {
//1.加载mybatis核心配置文件，获取SqlSessionFactory对象
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//2.获取SqlSession对象，用它执行sql
SqlSession sqlSession = sqlSessionFactory.openSession();
//3.获取接口代理对象
BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
//4.执行方法即执行sql语句
List<Brand> brands = brandMapper.selectAll();
System.out.println(brands);
//5.释放资源
sqlSession.close();
}
/**
* 测试练习：查看详情，根据id查询
*
* @throws Exception
*/
@Test
public void testSelectById() throws Exception {
//定义局部变量接收参数
int id = 1;
//1.加载mybatis核心配置文件，获取SqlSessionFactory对象
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//2.获取SqlSession对象，用它执行sql
SqlSession sqlSession = sqlSessionFactory.openSession();
//3.获取接口代理对象
BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
//4.执行方法即执行sql语句
Brand brand = brandMapper.selectById(id);
System.out.println(brand);
//5.释放资源
sqlSession.close();
}
/**
* 查询-多条件查询
*
* @throws Exception
*/
@Test
public void testSelectByCondition() throws Exception {
//定义局部变量接收参数
int status = 1;
String companyName = "华为";
String brandName = "华为";
//处理参数
//采用模糊查询需要对输入的参数进行处理
companyName = "%" + companyName + "%";
brandName = "%" + brandName + "%";
//封装对象
/*Brand brand = new Brand();
brand.setStatus(status);
brand.setCompanyName(companyName);
brand.setBrandName(brandName);*/
//创建map集合，将map集合作为参数传入
Map<Object, Object> map = new HashMap<Object, Object>();
map.put("status", status);
// map.put("companyName", companyName);
// map.put("brandName", brandName);
//1.加载mybatis核心配置文件，获取SqlSessionFactory对象
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//2.获取SqlSession对象，用它执行sql
SqlSession sqlSession = sqlSessionFactory.openSession();
//3.获取接口代理对象
BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
//4.执行方法即执行sql语句
//散装参数
//List<Brand> brands = brandMapper.selectByCondition(status, companyName, brandName);
//Brand对象参数
//List<Brand> brands = brandMapper.selectByCondition(brand);
//Map集合参数
List<Brand> brands = brandMapper.selectByCondition(map);
System.out.println(brands);
//5.释放资源
sqlSession.close();
}
/**
* 查询-单条件查询
*
* @throws Exception
*/
@Test
public void testSelectByConditionSingle() throws Exception {
//定义局部变量接收参数
int status = 1;
String companyName = "华为";
String brandName = "华为";
//处理参数
//采用模糊查询需要对输入的参数进行处理
companyName = "%" + companyName + "%";
brandName = "%" + brandName + "%";
//封装对象
Brand brand = new Brand();
brand.setStatus(status);
brand.setCompanyName(companyName);
brand.setBrandName(brandName);
//1.加载mybatis核心配置文件，获取SqlSessionFactory对象
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//2.获取SqlSession对象，用它执行sql
SqlSession sqlSession = sqlSessionFactory.openSession();
//3.获取接口代理对象
BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
//4.执行方法即执行sql语句
//散装参数
//List<Brand> brands = brandMapper.selectByCondition(status, companyName, brandName);
//Brand对象参数
List<Brand> brands = brandMapper.selectByConditionSingle(brand);
System.out.println(brands);
//5.释放资源
sqlSession.close();
}
/**
* 添加
*
* @throws Exception
*/
@Test
public void testAdd() throws Exception {
//定义局部变量接收参数
int status = 1;
String companyName = "香飘飘食品有限公司";
String brandName = "香飘飘";
int ordered = 100;
String description = "一年销量绕地球三圈";
// //处理参数
// //采用模糊查询需要对输入的参数进行处理
// companyName = "%" + companyName + "%";
// brandName = "%" + brandName + "%";
//封装对象
Brand brand = new Brand();
brand.setBrandName(brandName);
brand.setCompanyName(companyName);
brand.setOrdered(ordered);
brand.setDescription(description);
brand.setStatus(status);
//1.加载mybatis核心配置文件，获取SqlSessionFactory对象
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//2.获取SqlSession对象，用它执行sql
SqlSession sqlSession = sqlSessionFactory.openSession(true);
//3.获取接口代理对象
BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
//4.执行方法即执行sql语句
brandMapper.add(brand);
Integer i = brand.getId();
System.out.println(i);
//提交事务
// sqlSession.commit(true);
//5.释放资源
sqlSession.close();
}
/**
* 修改
*
* @throws Exception
*/
@Test
public void testUpdate() throws Exception {
//定义局部变量接收参数
int id = 11;
int status = 1;
String companyName = "香飘飘食品有限公司";
String brandName = "香飘飘";
int ordered = 200;
String description = "香飘飘超好喝，一年销量绕地球三圈";
// //处理参数
// //采用模糊查询需要对输入的参数进行处理
// companyName = "%" + companyName + "%";
// brandName = "%" + brandName + "%";
//封装对象
Brand brand = new Brand();
brand.setId(id);
// brand.setBrandName(brandName);
// brand.setCompanyName(companyName);
brand.setOrdered(ordered);
// brand.setDescription(description);
// brand.setStatus(status);
//1.加载mybatis核心配置文件，获取SqlSessionFactory对象
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//2.获取SqlSession对象，用它执行sql
SqlSession sqlSession = sqlSessionFactory.openSession(true);
//3.获取接口代理对象
BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
//4.执行方法即执行sql语句
int count = brandMapper.update(brand);
System.out.println(count);
//提交事务
// sqlSession.commit(true);
//5.释放资源
sqlSession.close();
}
/**
* 根据id删除
*
* @throws Exception
*/
@Test
public void testDeleteById() throws Exception {
//定义局部变量接收参数
int id = 11;
//1.加载mybatis核心配置文件，获取SqlSessionFactory对象
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//2.获取SqlSession对象，用它执行sql
SqlSession sqlSession = sqlSessionFactory.openSession(true);
//3.获取接口代理对象
BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
//4.执行方法即执行sql语句
int count = brandMapper.deleteById(id);
System.out.println(count);
//提交事务
// sqlSession.commit(true);
//5.释放资源
sqlSession.close();
}
/**
* 根据id批量删除
*
* @throws Exception
*/
@Test
public void testDeleteByIds() throws Exception {
//定义局部变量接收参数
int[] ids = {1, 2};
//1.加载mybatis核心配置文件，获取SqlSessionFactory对象
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//2.获取SqlSession对象，用它执行sql
SqlSession sqlSession = sqlSessionFactory.openSession(true);
//3.获取接口代理对象
BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
//4.执行方法即执行sql语句
int count = brandMapper.deleteByIds(ids);
System.out.println(count);
//提交事务
// sqlSession.commit(true);
//5.释放资源
sqlSession.close();
}
}
扫一扫
1543
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
