Mybatis配置文件的增删改查功能
查询—条件查询
//resources里面的org.example.mapper中的BrandMapper.xml文件
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--
namespace:命名空间,对sql语句进行隔离,方便管理
-->
<mapper namespace="org.example.mapper.BrandMapper">
<!-- 数据库表的字段名称 和实体类得到属性名称 不一样,则不能自动封装数据
* 起别名:对不一样的;列名起别名,让别名和实体类的属性名一样
缺点:每次查询都要定义一次别名
*:sql片段 缺点:不灵活
*resultMapper:
--><resultMap id="brandResult" type="org.example.pojo.Brand"><id column="id" property="id"/><result column="brand_name" property="brandName"/><result column="company_name" property="companyName"/><result column="ordered" property="ordered"/><result column="description" property="description"/><result column="status" property="status"/></resultMap><select id="selectAll" resultMap="brandResult">select * from tb_brand</select><!--*参数占位符:
1.#{}:会将其替换为?,为了防止SQL注入
2.${}:拼sql。会存在SQL注入问题
3.使用时机:*参数传递的时候:#{}
*表名或者列名不固定的情况下:${}会存在Sql注入问题
*参数类型:paramType:指定参数类型,可以不写,默认为null,会自动识别类型 *--><select id="selectById" resultMap="brandResult">select * from tb_brand where id = #{id}</select><!--条件查询-->
<!-- <select id="selectByCondition" parameterType="org.example.pojo.Brand" resultMap="brandResult">-->
<!-- select * from tb_brand-->
<!-- where status = #{status}-->
<!-- and company_name like #{companyName}-->
<!-- and brand_name like #{brandName}-->
<!-- </select>--><!-- 动态条件查询
*if:条件判断
*test:逻辑表达式
问题:
*恒等式
*<where>替换where关键字--><select id="selectByCondition" parameterType="map" resultMap="brandResult">
select * from tb_brand
where 1=1<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></select><!-- choose相当于switch when相当于case -->
<!-- <select id="selectByConditionSingle" resultMap="brandResult" >-->
<!-- select * from tb_brand-->
<!-- where-->
<!-- <choose>-->
<!-- <when test="status!=null">-->
<!-- status = #{status}-->
<!-- </when>-->
<!-- <when test="companyName!=null and companyName!=''">-->
<!-- company_name like #{companyName}-->
<!-- </when>-->
<!-- <when test="brandName!=null and brandName!=''">-->
<!-- brand_name like #{brandName}-->
<!-- </when>-->
<!-- <otherwise>-->
<!-- 1=1-->
<!-- </otherwise>-->
<!-- </choose>-->
<!-- </select>--><select id="selectByConditionSingle" resultMap="brandResult" >select * from tb_brand<where><choose><when test="status!=null">status = #{status}</when><when test="companyName!=null and companyName!=''">company_name like #{companyName}</when><when test="brandName!=null and brandName!=''">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="status!=null">status = #{status},</if><if test="ordered!=null">ordered = #{ordered},</if><if test="description!=null">description = #{description},</if><if test="brandName!=null">brand_name = #{brandName},</if><if test="companyName!=null">company_name = #{companyName},</if></set>where id = #{id}</update><delete id="deleteById">delete from tb_brand where id = #{id}</delete>
<!-- mybatis会将数组参数,封装为一个Map集合,以键值对形式存在,键为:array--><delete id="deleteByIds">delete from tb_brand where idin(<foreach collection="ids" item="id" >#{id}</foreach>)</delete></mapper>//resoures中的文件的mybatis-config.xml文件<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configurationPUBLIC "-//mybatis.org//DTD Config 3.0//EN""http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration><environments default="development"><environment id="development"><transactionManager type="JDBC"/><dataSource type="POOLED">
<!-- 数据库的连接信息--><property name="driver" value="com.mysql.cj.jdbc.Driver"/><property name="url" value="jdbc:mysql://127.0.0.1:3306/mybatis?serverTimezone=UTC"/><property name="username" value="root"/><property name="password" value="123456"/></dataSource></environment></environments><mappers>
<!-- 加载sql映射文件--><mapper resource="org/example/mapper/BrandMapper.xml"/>
<!-- <package name="org.example.mapper"/>--></mappers>
</configuration>//src/main/java/org/example/mapper中的BrandMapper接口public interface BrandMapper {/*** 查询所有品牌*/
public List<Brand> selectAll();
/*** 查看详情:根据id查询*/
public Brand selectById(int id);/*** 条件查询* *参数接受:* 1.散装参数: 如果方法中有多个参数,需要使用@Param* 2.对象参数:对象的属性名称要和参数名称一致* 3.map参数:参数使用map.get(key)获取值* @param status* @param companyName* @param brandName* @return*/
//List<Brand> selectByCondition(
// @Param("status")int status,
// @Param("companyName")String companyName,
// @Param("brandName")String brandName);//public 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);/*** 删除*/void deleteById(int id);/*** 批量删除* @param ids*/void deleteByIds(@Param("ids") int[] ids);
}//测试文件代码块
public class MyBatisTest {@Testpublic void testSelectAll() throws IOException {// 1.创建SqlSessionFactoryBuilder对象String resource = "mybatis-config.xml";InputStream inputStream = Resources.getResourceAsStream(resource);SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);//2.获取SqlSession对象,用它来执行sqlSqlSession sqlSession = sqlSessionFactory.openSession();//3.获取Mapper接口对象BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);//4.执行方法System.out.println(mapper.selectAll());//5.释放资源sqlSession.close();}@Testpublic void testSelectById() throws IOException {int id = 1;// 1.创建SqlSessionFactoryBuilder对象String resource = "mybatis-config.xml";InputStream inputStream = Resources.getResourceAsStream(resource);SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);//2.获取SqlSession对象,用它来执行sqlSqlSession sqlSession = sqlSessionFactory.openSession();//3.获取Mapper接口对象BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);//4.执行方法System.out.println(mapper.selectById(id));//5.释放资源sqlSession.close();}@Testpublic 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);// map封装Map map = new HashMap();map.put("status",status);map.put("companyName",companyName);map.put("brandName",brandName);// 1.创建SqlSessionFactoryBuilder对象String resource = "mybatis-config.xml";InputStream inputStream = Resources.getResourceAsStream(resource);SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);//2.获取SqlSession对象,用它来执行sqlSqlSession sqlSession = sqlSessionFactory.openSession();//3.获取Mapper接口对象BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);//4.执行方法// List<Brand> brands = mapper.selectByCondition(status, companyName, brandName);
// List<Brand> brands = mapper.selectByCondition(brand);List<Brand> brands = mapper.selectByCondition(map);System.out.println(brands);//5.释放资源sqlSession.close();}@Testpublic void testSelectByConditionSingle() throws IOException {int status =0 ;String companyName = "小米科技有限公司";String brandName = "小米";// 处理数据companyName = "%" + companyName + "%";brandName = "%" + brandName + "%";
// 封装对象Brand brand = new Brand();brand.setStatus(status);
// brand.setCompanyName(companyName);
// brand.setBrandName(brandName);// 1.创建SqlSessionFactoryBuilder对象String resource = "mybatis-config.xml";InputStream inputStream = Resources.getResourceAsStream(resource);SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);//2.获取SqlSession对象,用它来执行sqlSqlSession sqlSession = sqlSessionFactory.openSession();//3.获取Mapper接口对象BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);//4.执行方法// List<Brand> brands = mapper.selectByCondition(status, companyName, brandName);
// List<Brand> brands = mapper.selectByCondition(brand);List<Brand> brands = mapper.selectByConditionSingle(brand);System.out.println(brands);//5.释放资源sqlSession.close();}@Testpublic void testAdd() throws IOException {int status =1 ;String companyName = "波导手机";String brandName = "波导";String description = "手机中的战斗机";int ordered = 100;
// 封装对象Brand brand = new Brand();brand.setStatus(status);brand.setCompanyName(companyName);brand.setBrandName(brandName);brand.setDescription(description);brand.setOrdered(ordered);// 1.创建SqlSessionFactoryBuilder对象String resource = "mybatis-config.xml";InputStream inputStream = Resources.getResourceAsStream(resource);SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);//2.获取SqlSession对象,用它来执行sqlSqlSession sqlSession = sqlSessionFactory.openSession();//3.获取Mapper接口对象BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);//4.执行方法mapper.add(brand);//提交事务sqlSession.commit();//5.释放资源sqlSession.close();}@Testpublic void testUpdate() throws IOException {int status =1 ;String companyName = "波导手机";String brandName = "波导";String description = "波导手机,手机中的战斗机";int ordered = 200;int id = 5;
// 封装对象Brand brand = new Brand();brand.setStatus(status);brand.setCompanyName(companyName);brand.setBrandName(brandName);brand.setDescription(description);brand.setOrdered(ordered);brand.setId(id);// 1.创建SqlSessionFactoryBuilder对象String resource = "mybatis-config.xml";InputStream inputStream = Resources.getResourceAsStream(resource);SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);//2.获取SqlSession对象,用它来执行sqlSqlSession sqlSession = sqlSessionFactory.openSession();//3.获取Mapper接口对象BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);//4.执行方法int count = mapper.update(brand);System.out.println(count);//提交事务sqlSession.commit();//5.释放资源sqlSession.close();}@Testpublic void testDeleteById() throws IOException {int id = 5;// 1.创建SqlSessionFactoryBuilder对象String resource = "mybatis-config.xml";InputStream inputStream = Resources.getResourceAsStream(resource);SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);//2.获取SqlSession对象,用它来执行sqlSqlSession sqlSession = sqlSessionFactory.openSession();//3.获取Mapper接口对象BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);//4.执行方法mapper.deleteById(id);//提交事务sqlSession.commit();//5.释放资源sqlSession.close();}@Testpublic void testDeleteByIds() throws IOException {int [] ids = {5,6};// 1.创建SqlSessionFactoryBuilder对象String resource = "mybatis-config.xml";InputStream inputStream = Resources.getResourceAsStream(resource);SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);//2.获取SqlSession对象,用它来执行sqlSqlSession sqlSession = sqlSessionFactory.openSession();//3.获取Mapper接口对象BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);//4.执行方法mapper.deleteByIds(ids);//提交事务sqlSession.commit();//5.释放资源sqlSession.close();}
就比如说我们查询当前表格 tb_brand表格中
select * from tb_name 进行条件查询
where
当前状态:启用 status =?
and
企业名称 company_name like ?
and
品牌名称
brand_name like?
1.编写接口方法:Mapper接口
参数:所有查询条件
结果:List<Brand>
2.编写SQL语句:SQL映射文件
3.执行方法,测试三个不同的接受方式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);<select id ="selectByCondition" resultMap="brandResultMap">
select*
from tb_brand
where
status=#{status}
and company_name like #{companyName}
and brand_name like #{brnadName}
</select>
SQL语句设置多个参数有几种方式?
1.散装参数:需要使用@Param(“SQL中的参数占位符名称”)
2.实体类封装参数
只需要保证SQL中的参数名和实体类属性名称对应上,即可设置成功
3.map集合
只需要保证SQL中的参数名和map集合的键名称对应上,即可设置成功
查询-多条件-动态条件查询
SQL语句随着用户的输入或外部条件的变化而变化,我们称为动态SQL
<!-- 动态条件查询
*if:条件判断
*test:逻辑表达式
问题:
*恒等式
*<where>替换where关键字--><select id="selectByCondition" parameterType="map" resultMap="brandResult">
select * from tb_brand
where 1=1<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></select>//测试文件
查询-单条件-动态条件查询
从多个条件中选择一个
choose(when,otherwise):选择,类似于java中的switch语句
<!-- choose相当于switch when相当于case -->
<!-- <select id="selectByConditionSingle" resultMap="brandResult" >-->
<!-- select * from tb_brand-->
<!-- where-->
<!-- <choose>-->
<!-- <when test="status!=null">-->
<!-- status = #{status}-->
<!-- </when>-->
<!-- <when test="companyName!=null and companyName!=''">-->
<!-- company_name like #{companyName}-->
<!-- </when>-->
<!-- <when test="brandName!=null and brandName!=''">-->
<!-- brand_name like #{brandName}-->
<!-- </when>-->
<!-- <otherwise>-->
<!-- 1=1-->
<!-- </otherwise>-->
<!-- </choose>-->
<!-- </select>--><select id="selectByConditionSingle" resultMap="brandResult" >select * from tb_brand<where><choose><when test="status!=null">status = #{status}</when><when test="companyName!=null and companyName!=''">company_name like #{companyName}</when><when test="brandName!=null and brandName!=''">brand_name like #{brandName}</when></choose></where></select>
添加
1.编写接口方法:Mapper接口 void add(Brand brand)
参数:除了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>
3.执行方法,测试
MyBatis事务:
openSession():默认开启事务,进行增删改查操作后需要使用sqlSession.commit();手动提交事务
openSession(true):可以设置为自动提交事务(关闭事务)
添加-主键返回
在数据添加成功后,需要获取插入数据库数据的主键的值
比如:添加订单和订单项
1.添加订单
2.添加订单项,订单项中需要设置所属订单的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>
返回添加数据的主键
<insert useGenneratedKeys="true" KeyProperty="id">
修改
修改-修改全部字段
方法顺序还是一样的:
1.编写接口方法:Mapper接口
参数:所有数据
结果:void
编写SQL语句:SQL映射文件
执行方法,测试
void update(Brand brand)<update id = "update">
update tb_brand
set brand_name = #{brandName}.
company_name = #{companyName}.
ordered=#{ordered},
description=#{description},
status=#{status}
where id = #{id};
</update>
修改-修改动态字段
有些数据我们不需要全部的修改,所以就存在单个修改,为了防止其null值出现,有标签,标签
<update id="update">update tb_brand<set><if test="status!=null">status = #{status},</if><if test="ordered!=null">ordered = #{ordered},</if><if test="description!=null">description = #{description},</if><if test="brandName!=null">brand_name = #{brandName},</if><if test="companyName!=null">company_name = #{companyName},</if></set>where id = #{id}</update>
删除
删除一个
void deleteById(int id);<delete id="deleteById">delete from tb_brand where id = #{id}</delete>
批量删除
void deleteByIds(@Param("ids") int[] ids);<!-- mybatis会将数组参数,封装为一个Map集合,以键值对形式存在,键为:array--><delete id="deleteByIds">delete from tb_brand where idin(<foreach collection="ids" item="id" >#{id}</foreach>)
参数封装
MyBatis提供了ParamNameResolver类进行参数封装
建议:将来都使用@Param注解来修改Map集合中默认的键名,并使用修改后的名称来获取值,这样可读性更高
注解完成增删改查
使用注解开发会比配置文件开发更加方便
@Select("select*from tb_user where id =#{id}")
public User selectById(int id)
查询:@Select
添加:@Insert
修改:@Update
删除:@Delete
注解完成简单功能
配置文件完成复杂功能
使用注解来映射简单语句会使代码显得更加简洁,但对于稍微复杂与一点的语句,Java注解不仅力不从心,还会让你本来就复杂的SQL语句更加混乱不堪。因此,如果你需要做一些复杂的操作,最好用XML来映射语句
id = #{id}
#### 批量删除```java
void deleteByIds(@Param("ids") int[] ids);<!-- mybatis会将数组参数,封装为一个Map集合,以键值对形式存在,键为:array--><delete id="deleteByIds">delete from tb_brand where idin(<foreach collection="ids" item="id" >#{id}</foreach>)
参数封装
[外链图片转存中…(img-ieMEGw2e-1731576949906)]
MyBatis提供了ParamNameResolver类进行参数封装
建议:将来都使用@Param注解来修改Map集合中默认的键名,并使用修改后的名称来获取值,这样可读性更高
注解完成增删改查
使用注解开发会比配置文件开发更加方便
@Select("select*from tb_user where id =#{id}")
public User selectById(int id)
查询:@Select
添加:@Insert
修改:@Update
删除:@Delete
注解完成简单功能
配置文件完成复杂功能
使用注解来映射简单语句会使代码显得更加简洁,但对于稍微复杂与一点的语句,Java注解不仅力不从心,还会让你本来就复杂的SQL语句更加混乱不堪。因此,如果你需要做一些复杂的操作,最好用XML来映射语句
选择何种方式类配置影视,以及认为是否因该要统一映射语句定义的形式,完成取决于你和你的团队。换句话说,永远不要拘泥于一种方式。你可以很轻松的基于注解和XML的语句映射方式间自由移植和切换。