• MyBatis完成增删改查案例(详细代码)

    :maple_leaf:查询

    :fire:解决属性名和字段名不一致的问题

    数据库表的字段名和实体类的属性名不一致，则不能自动封装数据。

    • 1.起别名 缺点：每次查询都要定义一次别名
    • 2.sql片段 缺点：不灵活
    • 3.resultMap 1.定义resultMap标签 2.在select标签中，使用resultMap属性替换resultType替换。
    
   

    
    

   
    
        
        
    
    
复制代码

    :fire:模糊查询

    • java代码执行的时候，传递通配符% %
    //接收参数
        int status=1;
        String companyName="华为";
        String brandName="华为";
       //处理参数
        companyName="%"+companyName+"%";
        brandName="%"+brandName+"%";
复制代码
    • 在sql拼接中使用通配符！
    
复制代码

    :fire:特殊字符处理

    在通过id查询数据时，查询条件是id小于某个数时。

    • 转义字符
    • CDATA区
    select * from tb_brand
         where id
         
         #{id};
复制代码

    :fire:参数占位符

    • ${}：拼sql,存在sql注入问题
    • #{}：会将其替换为？，防止sql注入

    :fire:参数接收

    • 1，散装参数：如果方法中有多个参数，需要使用@param("sql参数占位符名称")
    List selectByCondition(@Param("status") int status,@Param("companyName") String companyName,@Param("brandName") String brandName);
复制代码
    • 2.实体类封装参数
    List selectByCondition(Brand brand);
复制代码
    • 3.Map集合
    List selectByCondition(Map map);
复制代码

    :fire:动态条件查询

    状态，公司名称，品牌名称，任意一个就可以查询数据

    • 1.恒等式
    
复制代码
    • 2. 替换where关键字
    
复制代码

    :fire:单条件动态条件查询

    


    
复制代码

    :maple_leaf:添加

    :fire:添加全部数据

    
    insert into tb_brand(brand_name,company_name,ordered,description,status)
    values (#{brandName},#{companyName},#{ordered},#{description},#{status});
    
复制代码

    :fire:返回添加数据的主键

    
    insert into tb_brand(brand_name,company_name,ordered,description,status)
    values (#{brandName},#{companyName},#{ordered},#{description},#{status});
    
复制代码

    :maple_leaf:修改

    :fire:修改全部数据

    根据id修改全部数据：

    
        update tb_brand set  brand_name=#{brandName},company_name=#{companyName},ordered=#{ordered},description=#{description},status=#{status}
        where id=#{id};
    
复制代码

    :fire:修改动态字段

    根据id修改可以修改任意个字段：

    
        update tb_brand
        
        
            brand_name=#{brandName},
        
        
            company_name=#{companyName},
        
        
            ordered=#{ordered},
        
        
            description=#{description},
        
        
            status=#{status}
        
        
        where id=#{id};
    
复制代码

    :maple_leaf:删除

    :fire:通过id删除数据

    
        delete from tb_brand where id=#{id};
    
复制代码

    :fire:通过id进行批量删除

    mybatis会将数组参数，封装成一个Map集合 1.默认：array=数组 2.使用@param注解改变map集合的默认key的名称

    
        delete from tb_brand where id
        in
            
            
                #{id}
            
            ;
    
复制代码

    :maple_leaf:MyBatis事务

    注意**增删改需要提交事务！**
复制代码
    1. openSession():默认开启事务，进行增删改操作后，需要使用sqlSession.commit(); 手动提交事务。
    //4.提交事务
        sqlSession.commit();
复制代码

    2.openSession(true);可以设置为自动提交事务和关闭事务。

    SqlSession sqlSession = sqlSessionFactory.openSession(true);
复制代码

    :maple_leaf:增删改查案例分析

    :fire:数据库

    创建数据库：mybatis 设计表：

    往表里添加数据。

    :fire:pom.xml:

    

    4.0.0

    com.jkj
    maven-demo
    1.0-SNAPSHOT
    
    
        
        
            mysql
            mysql-connector-java
            5.1.47
        
        
        
            org.mybatis
            mybatis
            3.5.6
        
        
        
            junit
            junit
            4.12
        
    

复制代码

    :fire:brand实体类：

    package com.jkj.pojo;

public class Brand {
    private Integer id;
    private String brandName;
    private String companyName;
    private Integer ordered;
    private String description;
    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 +
                '}';
    }
}

复制代码

    :fire:BrandMapper:

    package com.jkj.Mapper;

import com.jkj.pojo.Brand;
import com.jkj.pojo.User;
import org.apache.ibatis.annotations.Param;

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

public interface BrandMapper {
   //查询所有
   List selectAll();
   //查询详情，通过id查询
   Brand selectById(int id);

   /*条件查询
         参数接收
       1，散装参数：如果方法中有多个参数，需要使用@param("sql参数占位符名称")
       2.实体类封装参数
       3.Map集合
       */
   //List selectByCondition(@Param("status") int status,@Param("companyName") String companyName,@Param("brandName") String brandName);
   //List selectByCondition(Brand brand);
   List selectByCondition(Map map);
   //单条件动态条件查询
   List selectByConditionSingle(Brand brand);

   //添加
   void add(Brand brand);
   //修改全部字段
   int update(Brand brand);
   //通过id删除数据
   void deleteById(int id);
   //批量删除
   void deleteByIds(@Param("ids") int[] ids);


}

复制代码

    :fire:mybatis-config.xml配置文件：

    



    
    
        
    
    
        
            
            
                
                
                
                
            
        
    
    
    
        
        
        
    


复制代码

    :fire:BrandMapper.xml:

    


    
    

   
   

    
    

   
    
        
        
    
    
    
    
    

    
    

    
    
    
    
   
   

  
    
    
    
    

    
    

    
    
    insert into tb_brand(brand_name,company_name,ordered,description,status)
    values (#{brandName},#{companyName},#{ordered},#{description},#{status});
    

    
    
    
    
        update tb_brand
        
        
            brand_name=#{brandName},
        
        
            company_name=#{companyName},
        
        
            ordered=#{ordered},
        
        
            description=#{description},
        
        
            status=#{status}
        
        
        where id=#{id};
    
    
    
        delete from tb_brand where id=#{id};
    

    
    
    
        delete from tb_brand where id
        in
            
            
                #{id}
            
            ;
    



复制代码

    :fire:测试类：

    package com.jkj.test;

import com.jkj.Mapper.BrandMapper;
import com.jkj.Mapper.UserMapper;
import com.jkj.pojo.Brand;
import com.jkj.pojo.User;
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 {
    @Test
    public void selectAll() throws IOException {
        //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();
        BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);
        //3.执行sql
        List brands = mapper.selectAll();
        System.out.println(brands);
        //4.释放资源
        sqlSession.close();

       /* [Brand{id=1, brandName='华为', companyName='华为有限技术公司', ordered=100, description='华为牛逼', status=1},
       Brand{id=2, brandName='小米', companyName='小米有限技术公司', ordered=50, description='小米不孬', status=1},
       Brand{id=3, brandName='荣耀', companyName='荣耀有限技术公司', ordered=20, description='荣耀很中', status=1}]*/
    }
    @Test
    public void selectById() throws IOException {
        //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();
        BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);
        //3.执行sql
        Brand brand = mapper.selectById(2);
        System.out.println(brand);
        //4.释放资源
        sqlSession.close();
        /*Brand{id=1, brandName='华为', companyName='华为有限技术公司', ordered=100, description='华为牛逼', status=1}*/
    }
    @Test
    public void selectByCondition() 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.加载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();
        BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);
        //3.执行sql
        //List brands = mapper.selectByCondition(status, companyName, brandName);
        //List brands = mapper.selectByCondition(brand);
        List brands = mapper.selectByCondition(map);
        System.out.println(brands);
        //4.释放资源
        sqlSession.close();
        /*[Brand{id=1, brandName='华为', companyName='华为有限技术公司', ordered=100, description='华为牛逼', status=1}]*/

    }
    @Test
    public void selectByConditionSingle() 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);
        //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();
        BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);
        //3.执行sql
        List brands = mapper.selectByConditionSingle(brand);
        System.out.println(brands);
        //4.释放资源
        sqlSession.close();
    }
    @Test
    public void add() throws IOException {
        //接收参数
        int status=0;
        String companyName="今麦郎";
        String brandName="今麦郎";
        int ordered=200;
        String description="今麦郎可以";
        //封装对象
        Brand brand = new Brand();
        brand.setStatus(status);
        brand.setCompanyName(companyName);
        brand.setBrandName(brandName);
        brand.setOrdered(ordered);
        brand.setDescription(description);
        //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();
        BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);
        //3.执行sql
        mapper.add(brand);
        //4.提交事务
        sqlSession.commit();
        //5.释放资源
        sqlSession.close();
    }
    @Test
    public void add2() throws IOException {
        //接收参数
        int status=1;
        String companyName="农夫山泉";
        String brandName="农夫山泉";
        int ordered=200;
        String description="农夫山泉真甜";
        //封装对象
        Brand brand = new Brand();
        brand.setStatus(status);
        brand.setCompanyName(companyName);
        brand.setBrandName(brandName);
        brand.setOrdered(ordered);
        brand.setDescription(description);
        //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();
        BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);
        //3.执行sql
        mapper.add(brand);
        //获取添加数据的主键
        Integer id = brand.getId();
        System.out.println(id);  //6
        //4.提交事务
        sqlSession.commit();
        //5.释放资源
        sqlSession.close();
    }
    @Test
    public void TestUpdate() throws IOException {
        //接收参数
        int status=1;
        String companyName="农夫山泉plus";
        String brandName="农夫山泉plus";
        int ordered=200;
        String description="农夫山泉甜甜甜";
        int id=5;
        //封装对象
        Brand brand = new Brand();
        brand.setStatus(status);
        brand.setCompanyName(companyName);
        brand.setBrandName(brandName);
        brand.setOrdered(ordered);
        brand.setDescription(description);
        brand.setId(id);
        //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);
        BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);
        //3.执行sql
        int count = mapper.update(brand);
        System.out.println(count); //受影响的行数
        //4.提交事务
        sqlSession.commit();
        //5.释放资源
        sqlSession.close();
    }
    @Test
    public void TestUpdate1() throws IOException {
        //接收参数
        int status=1;
        String companyName="农夫山泉plus";
        String brandName="农夫山泉plus";
        int ordered=200;
        String description="我只是大自然的搬运工";
        int id=5;
        //封装对象
        Brand brand = new Brand();
       /* brand.setStatus(status);
        brand.setCompanyName(companyName);
        brand.setBrandName(brandName);
        brand.setOrdered(ordered);*/
        brand.setDescription(description);
        brand.setId(id);
        //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);
        BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);
        //3.执行sql
        int count = mapper.update(brand);
        System.out.println(count); //受影响的行数
        //4.提交事务
        sqlSession.commit();
        //5.释放资源
        sqlSession.close();
    }
    @Test
    public void TestDeleteById() throws IOException {

        int id=5;
        //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);
        BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);
        //3.执行sql
        mapper.deleteById(id);
        //4.提交事务
        sqlSession.commit();
        //5.释放资源
        sqlSession.close();
    }
    @Test
    public void TestDeleteByIds() throws IOException {

        int [] ids={4,5,6};
        //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);
        BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);
        //3.执行sql
        mapper.deleteByIds(ids);
        //4.提交事务
        sqlSession.commit();
        //5.释放资源
        sqlSession.close();
    }
    @Test
    public void TestSelectAll() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        //2.获取sqlSession,执行sql
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserMapper mapper = (UserMapper) sqlSession.getMapper(UserMapper.class);
        //3.执行sql
        List users = mapper.selectAll();
        System.out.println(users);
        //4.释放资源
        sqlSession.close();
//[User{id=1, username='小马哥', password='111', gender='男', addr='北京'},
// User{id=2, username='小飞侠', password='222', gender='男', addr='上海'},
// User{id=3, username='马奎斯', password='333', gender='女', addr='西班牙'}]

    }
}

复制代码

     

  • 相关阅读:
    kafka搭建，演示
    SQLite 常用功能整合
    React教程之 React 中的高阶组件 (HOC) 简介
    红蓝对抗 网络安全 网络安全红蓝对抗演练
    java169-DatagramPacket 数据报包类
    金融市场数据至上：QuestDB 为您的数据提供最优解 | 开源日报 No.81
    JavaEE进阶（1）Java EE 简述（Java EE 发展历程、什么是Web开发? Web网站的工作流程、什么是框架？Java EE 框架学习概览）
    java基于springboot+vue动物诊所综合管理系统
    dockerfile 搭建lnmp+wordpress，docker-compose搭建lnmp+wordpress
    【PowerQuery】Excel 自动刷新PowerQuery连接
  • 原文地址：https://blog.csdn.net/YYniannian/article/details/126176009