Mybatis增删改查案例笔记。

爬坑的笨人

已于 2022-07-21 16:32:12 修改

阅读量601

点赞数

文章标签： mybatis java 数据库

于 2022-07-21 15:56:40 首次发布

本文链接：https://blog.csdn.net/m0_57944688/article/details/125894538

版权

准备

建表

创建Brand类

package com.itheima.pojo;

/**
 * 品牌
 *
 * alt + 鼠标左键：整列编辑
 *
 * 在实体类中，基本数据类型建议使用其对应的包装类型
 */

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 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 +
                '}';
    }
}

创建Maven，导入依赖，配置mybatis相关的xml文件，详情参考另一篇。

记得更改config.xml中的sql映射地址，更改xxxxMapper.xml中的namespace和resultType。

MybatisX插件使用

可以将Mapper接口与sql映射链接，在接口中编写方法时，使用快捷键快速生成sql语句。

BrandMapper

查找

查询所有

public void testSelectAll() throws Exception{
        //1.获取sql session factory
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        //2.获取sql session对象
        SqlSession sqlSession = sqlSessionFactory.openSession();

        //3.获取Mapper接口的代理对象
        BrandMapper brandmapper = sqlSession.getMapper(BrandMapper.class);
        //4.执行sql返回集合
        List<Brand> brands = brandmapper.selectAll();
        System.out.println(brands);
        //5.释放资源
        sqlSession.close();
    }

查询结果会因为数据库列名和类中属性命名不同，出现查询结果为null。

通过resultMap解决这个问题，resultMap替换掉resultType

此时的BrandMapper.xml，<id>可以完成对主键的映射

<?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">


<mapper namespace="com.itheima.mapper.BrandMapper">

     <resultMap id="brandresultmap" type="Brand">
         <result column="brand_name" property="brandName"/>
         <result column="company_name" property="companyName"/>
     </resultMap>
    <!--唯一标志名+++++封装类型-->
    <select id="selectAll" resultMap="brandresultmap">
        select * from tb_brand;
    </select>
</mapper>

查看详情

每个品牌有详细信息，前端点击后可以查看进一步的详细信息。

在Mybatis中不用？接受参数，如上图所示。

#{}执行中会被替换成为？,能防止sql注入；还有一种${}

xml中特殊字符处理

1.转义字符 2.CDATA区

BrandMapper接口新增

package com.itheima.mapper;

import com.itheima.pojo.Brand;

import java.util.List;

public interface BrandMapper {
    List<Brand> selectAll();
    Brand selectByIdBrand(int i);

}

测试方法

   public void testSelectById() throws Exception{
        //0.接收参数
       int id=1;

        //1.获取sql session factory
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        //2.获取sql session对象
        SqlSession sqlSession = sqlSessionFactory.openSession();

        //3.获取Mapper接口的代理对象
        BrandMapper brandmapper = sqlSession.getMapper(BrandMapper.class);
        //4.接收参数，执行sql返回结果
        Brand brand = brandmapper.selectByIdBrand(id);
        System.out.println(brand);

        //5.释放资源
        sqlSession.close();
    }

}

条件查询

多条件查询

有bug，必须三个条件全输入才有正确结果

1.散装查询

接口

package com.itheima.mapper;

import com.itheima.pojo.Brand;
import org.apache.ibatis.annotations.Param;

import java.util.List;
import java.util.Map;

public interface BrandMapper {
    
    //多条件三种封装方法
    //1.散装：@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);


}

方法

  public void testSelectByCondition() throws Exception{
        //0.接收参数
        int id=1;
        String companyName="华为";
        String brandName="华为";
         companyName="%"+"华为"+"%";
         brandName="%"+"华为"+"%";

        //1.获取sql session factory
        String resource = "mybatis-config.xml";

        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        //2.获取sql session对象
        SqlSession sqlSession = sqlSessionFactory.openSession();

        //3.获取Mapper接口的代理对象
        BrandMapper brandmapper = sqlSession.getMapper(BrandMapper.class);
        //4.接收参数，执行sql返回结果
        List<Brand> brands = brandmapper.selectByCondition(id,companyName,brandName);
        System.out.println(brands);

        //5.释放资源
        sqlSession.close();
    }

}

2.对象参数

接口

package com.itheima.mapper;

import com.itheima.pojo.Brand;
import org.apache.ibatis.annotations.Param;

import java.util.List;
import java.util.Map;

public interface BrandMapper {
   // List<Brand> selectAll();
    //Brand selectByIdBrand(int i);
    //多条件三种封装方法
    //1.散装：@Param对应参数占位符
   // List<Brand> selectByCondition(@Param("status") int status, @Param("companyName") String
           // companyName, @Param("brandName") String brandName);

    //2.对象参数：对象的属性名称要和参数占位符名称一致
   List<Brand> selectByCondition(Brand brand);
    //List<Brand> selectByCondition(Map map);


}

方法

增加了封装的过程，接收参数地方进行更改

 public void testSelectByCondition() throws Exception{
        //0.接收参数
        int status=1;
        String companyName="华为";
        String brandName="华为";
         companyName="%"+"华为"+"%";
         brandName="%"+"华为"+"%";
         //封装对象
        Brand brand=new Brand();
        brand.setStatus(status);
        brand.setCompanyName(companyName);
        brand.setBrandName(brandName);

        //1.获取sql session factory
        String resource = "mybatis-config.xml";

        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        //2.获取sql session对象
        SqlSession sqlSession = sqlSessionFactory.openSession();

        //3.获取Mapper接口的代理对象
        BrandMapper brandmapper = sqlSession.getMapper(BrandMapper.class);
        //4.接收参数，执行sql返回结果
        //List<Brand> brands = brandmapper.selectByCondition(status,companyName,brandName);
        List<Brand> brands = brandmapper.selectByCondition(brand);
        System.out.println(brands);

        //5.释放资源
        sqlSession.close();
    }

3.map集合

接口

package com.itheima.mapper;

import com.itheima.pojo.Brand;
import org.apache.ibatis.annotations.Param;

import java.util.List;
import java.util.Map;

public interface BrandMapper {
   // List<Brand> selectAll();
    //Brand selectByIdBrand(int i);
    //多条件三种封装方法
    //1.散装：@Param对应参数占位符
   // List<Brand> selectByCondition(@Param("status") int status, @Param("companyName") String
           // companyName, @Param("brandName") String brandName);

    //2.对象参数：对象的属性名称要和参数占位符名称一致
   //List<Brand> selectByCondition(Brand brand);
    List<Brand> selectByCondition(Map map);


}

方法

 public void testSelectByCondition() throws Exception{
        //0.接收参数
        int status=1;
        String companyName="华为";
        String brandName="华为";
         companyName="%"+"华为"+"%";
         brandName="%"+"华为"+"%";
         //封装对象
       /* Brand brand=new Brand();
        brand.setStatus(status);
        brand.setCompanyName(companyName);
        brand.setBrandName(brandName);*/
        Map map=new HashMap<>();
        map.put("status",status);
        map.put("companyName",companyName);
        map.put("brandName",brandName);

        //1.获取sql session factory
        String resource = "mybatis-config.xml";

        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        //2.获取sql session对象
        SqlSession sqlSession = sqlSessionFactory.openSession();

        //3.获取Mapper接口的代理对象
        BrandMapper brandmapper = sqlSession.getMapper(BrandMapper.class);
        //4.接收参数，执行sql返回结果
        //List<Brand> brands = brandmapper.selectByCondition(status,companyName,brandName);
        List<Brand> brands = brandmapper.selectByCondition(map);
        System.out.println(brands);

        //5.释放资源
        sqlSession.close();
    }

动态多条件查询

动态sql

<?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">


<mapper namespace="com.itheima.mapper.BrandMapper">

     <resultMap id="brandresultmap" type="Brand">
         <result column="brand_name" property="brandName"/>
         <result column="company_name" property="companyName"/>
     </resultMap>

    <select id="selectByCondition" resultMap="brandresultmap">
        select *
        from tb_brand
        <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>

</mapper>

动态单条件查询

<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>
</choose>
</where>
</select>

方法

 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. 获取SqlSessionFactory
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//2. 获取SqlSession对象
        SqlSession sqlSession = sqlSessionFactory.openSession();
//3. 获取Mapper接口的代理对象
        BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
//4. 执行方法
        List<Brand> brands = brandMapper.selectByConditionSingle(brand);
        System.out.println(brands);
//5. 释放资源
        sqlSession.close();
}

增加

普通增加

接口

void add(Brand brand);

sql语句

<insert id="add">
insert into tb_brand (brand_name, company_name, ordered, description, status)
values (#{brandName}, #{companyName}, #{ordered}, #{description}, #{status});
</insert>

方法

事务要进行提交

@Test
public 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. 获取SqlSessionFactory
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//2. 获取SqlSession对象
SqlSession sqlSession = sqlSessionFactory.openSession();
//SqlSession sqlSession = sqlSessionFactory.openSession(true); //设置自动提交事务，这种情况不需
要手动提交事务了
//3. 获取Mapper接口的代理对象
BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
//4. 执行方法
brandMapper.add(brand);
//提交事务
sqlSession.commit();
//5. 释放资源
sqlSession.close();
}

主键返回

<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>

修改

接口

void update(Brand brand);

sql

<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>

方法

public void testUpdate() throws IOException {
//接收参数
int status = 0;
String companyName = "波导手机";
String brandName = "波导";
String description = "波导手机,手机中的战斗机";
int ordered = 200;
int id = 6;
//封装对象
Brand brand = new Brand();
brand.setStatus(status);
// brand.setCompanyName(companyName);
// brand.setBrandName(brandName);
// brand.setDescription(description);
// brand.setOrdered(ordered);
brand.setId(id);
//1. 获取SqlSessionFactory
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//2. 获取SqlSession对象
SqlSession sqlSession = sqlSessionFactory.openSession();
//SqlSession sqlSession = sqlSessionFactory.openSession(true);
//3. 获取Mapper接口的代理对象
BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
//4. 执行方法
int count = brandMapper.update(brand);
System.out.println(count);
//提交事务
sqlSession.commit();
//5. 释放资源
sqlSession.close();
}

删除

单个删除

接口

void deleteById(int id);

sql

<delete id="deleteById">
delete from tb_brand where id = #{id};
</delete>

方法

public void testDeleteById() throws IOException {
//接收参数
int id = 6;
//1. 获取SqlSessionFactory
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//2. 获取SqlSession对象
SqlSession sqlSession = sqlSessionFactory.openSession();
//SqlSession sqlSession = sqlSessionFactory.openSession(true);
//3. 获取Mapper接口的代理对象
BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
//4. 执行方法
brandMapper.deleteById(id);
//提交事务
sqlSession.commit();
//5. 释放资源
sqlSession.close();
}

批量删除

爬坑的笨人

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Mybatis增删改查案例笔记。

建表创建Brand类创建Maven，导入依赖，配置mybatis相关的xml文件，详情参考另一篇。记得更改config.xml中的sql映射地址，更改xxxxMapper.xml中的namespace和resultType。可以将Mapper接口与sql映射链接，在接口中编写方法时，使用快捷键快速生成sql语句。............
复制链接

扫一扫