2022.1.17-18 Javaweb Mybatis快速入门完成增删改查

2022.1.17-18 Javaweb Mybatis快速入门完成增删改查

Mybatis
Mybatis是持久层框架，简化JDBC开发。

create database mybatis;
use mybatis;

drop table if exists tb_user;

create table tb_user(
	id int primary key auto_increment,
	username varchar(20),
	password varchar(20),
	gender char(1),
	addr varchar(30)
);



INSERT INTO tb_user VALUES (1, 'zhangsan', '123', '男', '北京');
INSERT INTO tb_user VALUES (2, '李四', '234', '女', '天津');
INSERT INTO tb_user VALUES (3, '王五', '11', '男', '西安');

pom.xml

    4.0.0

    org.example
    mybatis-demo
    1.0-SNAPSHOT
    
    
        org.mybatis
        mybatis
        3.5.5
    
        
            mysql
            mysql-connector-java
            5.1.46
        
        
            junit
            junit
            4.13
            test
        

        
        
            org.slf4j
            slf4j-api
            1.7.20
        
        
        
            ch.qos.logback
            logback-classic
            1.2.3
        
        
        
            ch.qos.logback
            logback-core
            1.2.3
        
    

mybatis-config.xml

UserMapper.xml

    
        select * from tb_user;
    


 
User.java
 
package com.itest.pojo;


public class User {
    private Integer id;
    private String username;
    private String password;
    private String gender;
    private String addr;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getGender() {
        return gender;
    }

    public void setGender(String gender) {
        this.gender = gender;
    }

    public String getAddr() {
        return addr;
    }

    public void setAddr(String addr) {
        this.addr = addr;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + ''' +
                ", password='" + password + ''' +
                ", gender='" + gender + ''' +
                ", addr='" + addr + ''' +
                '}';
    }
}

 
MyBatisDemo.java
 
package com.itest.pojo;





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 java.io.IOException;
import java.io.InputStream;
import java.util.List;



public class MyBatisDemo {
    public static void main(String[] args) throws IOException {
        //加载核心配置文件，获取sqlsessionFactory
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        //获取sqlsession对象，用它来执行sql
        SqlSession sqlSession = sqlSessionFactory.openSession();

        //执行sql
        List users = sqlSession.selectList("test.selectAll");
        System.out.println(users);

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

 
Mapper代理开发
 
 
即可用  简化。
 
Mybatis核心文件配置
 enviroment :配置数据库连接环境信息，可以配置多个environment，通过default属性切换不同的environment。
 
 配置各个标签时，需要遵守前后顺序。
 
配置文件完成增删改查
 sql语句
 
-- 删除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', 1);


SELECT * FROM tb_brand;
 
Brand类
 
package com.itest.pojo;



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

 
查询所有数据
 
 定义实体类Brand.java
 
package com.itest.pojo;



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

 
在Mapper中定义BrandMapper.java
 
package com.itest.mapper;

import com.itest.pojo.Brand;
import java.util.List;

public interface BrandMapper {

    public List selectAll();

}

 
在resources里创建BrandMapper.xml
 







    
        select * from tb_brand;


    

最后在test中新建MyBatisTest.java

package com.itest.pojo;





import com.itest.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 java.io.IOException;
import java.io.InputStream;
import java.util.List;



public class MyBatisDemo {
    public static void main(String[] args) throws IOException {
        //加载核心配置文件，获取sqlsessionFactory
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        //获取sqlsession对象，用它来执行sql
        SqlSession sqlSession = sqlSessionFactory.openSession();

        //获取Mapper接口的代理对象
        BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);

        //执行方法
        List brands = brandMapper.selectAll();
        System.out.println(brands);

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

结果显示如下：

有字段显示为null。因为Java中驼峰命名与数据库名称不一致。
解决方案有以下两种：

1.对不一样的列名起别名，让别名和实体类的属性一样。

    
        select id,brand_name as brandName,company_name as companyName,ordered,description,status
        from tb_brand;
    
 
结果：
 
 
如果觉得每次写很多列名很麻烦 ，可以用sql片段方式来引用。代码如下：
 
    
        id,brand_name as brandName,company_name as companyName,ordered,description,status
    
    
        select
             
        from tb_brand;
    

2.用resultmap来解决

    
        
        
    

    
        select *
        from tb_brand;
    
 
主键用,其他用
 
查询-查看详情
 
 
BrandMapper.java中定义方法
 
package com.itest.mapper;
import com.itest.pojo.Brand;
import java.util.List;
public interface BrandMapper {
    public Brand selectById(int id);
}
 
BrandMapper.xml中写SQL语句
 





    
        
        
    

    
        select *
        from tb_brand;
    
    
        select * from tb_brand where id = #{id};
    


 
在MyBatisTest.java中测试
 
package com.itest.test;

import com.itest.mapper.BrandMapper;
import com.itest.mapper.UserMapper;
import com.itest.pojo.Brand;
import com.itest.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.List;


public class MyBatisTest {
    @Test
    public void selectById() throws IOException {
        //模拟接收参数
        int id =1;
        //获取SqlsessionFactory
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        //获取Sqlsession对象
        SqlSession sqlSession = sqlSessionFactory.openSession();

        BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);

        Brand brand = brandMapper.selectById(id);

        System.out.println(brand);
        //释放资源
        sqlSession.close();
    }
}

 
得到结果：
 
 
 
 
·注意：参数占位符：
 1.#{ } :会将其替换为？，为了防止SQL注入
 2.${ } :会存在SQL注入问题 
 使用参数传递的时候统一使用#{ }
 
 
参数类型可以省略，不省略如下：
 

    select * from tb_brand where id = #{id};

XML中<会报错，解决方法有两种
1.转义字符  如<的转义字符为<     
2.CDATA区  如小于号如下：
    

条件查询-多条件查询

1.散装参数格式：注意@Param（" xxx"）中xxx需要对应占位符名称 status = #{ status }
2.如果都来自同一对象，则 ListselectByCondition(Brand brand); 这样 status = #{ status } 就会到对象brand中去匹配属性。
3.封装成map，map键的名称与占位符保持一致，ListselectByCondition(Map map);

代码实现:
1.
BrandMapper.xml代码

    
            select * from tb_brand
            where status = #{status}
                and brand_name like #{brandName}
                and company_name like #{companyName}
    
 
BrandMapper.java代码
 
public interface BrandMapper {
    List  selectByCondition(@Param("status") int status, @Param("companyName") String companyName,@Param("brandName") String brandName);
}

 
MyBatisTest.java测试核心代码
 
		//模拟接收参数
        int status =1;
        String brandName = "华为";
        String companyName = "华为";

        //处理参数
        brandName ="%"+brandName+"%";
        companyName = "%"+companyName+"%";
		···

  List brands =  brandMapper.selectByCondition(status,companyName,brandName);
  System.out.println(brands);
 
运行结果
 
 2.
 BrandMapper.xml代码不需要修改
 
    
            select * from tb_brand
            where status = #{status}
                and brand_name like #{brandName}
                and company_name like #{companyName}
    

BrandMapper.java

public interface BrandMapper {
    List selectByCondition(Brand brand);
    }

MyBatisTest.java

 //模拟接收参数
        int status =1;
        String brandName = "华为";
        String companyName = "华为";
        //处理参数
        brandName ="%"+brandName+"%";
        companyName = "%"+companyName+"%";
        //封装对象
        Brand brand = new Brand();
        brand.setStatus(status);
        brand.setBrandName(companyName);
        brand.setCompanyName(brandName);
        ···
         List brands =  brandMapper.selectByCondition(brand);

        System.out.println(brands);

结果

3.
BrandMapper.xml中不需要修改

    
            select * from tb_brand
            where status = #{status}
                and brand_name like #{brandName}
                and company_name like #{companyName}
    
 
BrandMapper.java
 
public interface BrandMapper {
    List selectByCondition(Map map);
    }
 
MyBatisTest.java核心代码
 
		//模拟接收参数
        int status =1;
        String brandName = "华为";
        String companyName = "华为";
        //处理参数
        brandName ="%"+brandName+"%";
        companyName = "%"+companyName+"%";
        //map 封装对象
        Map map = new HashMap();
        map.put("status",status);
        map.put("brandName",brandName);
        map.put("companyName",companyName);
        ···
        List brands =  brandMapper.selectByCondition(map);
        System.out.println(brands);
 
运行结果
 
 
总结多条件查询
 
 动态SQL
 针对上面多条件查询，无法满足仅填一个条件时完成正确查询结果。对此，mybatis提供强大的动态sql支撑。
 ·if
 ·choose(when,otherwise)
 ·trim(where,set)
 ·foreach
 
通过标签来改造sql，代码如下
 
    
            select * from tb_brand
            where
              
                  status = #{status}
              
              
                  and brand_name like #{brandName}
              
              
                  and company_name like #{companyName}
              
    

其中Sting类型 判断条件是：不等于空，也不等于空字符串

新问题：如果status为空 则 后面语句格式不对 报错

org.apache.ibatis.exceptions.PersistenceException: ### Error
querying database. Cause:
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an
error in your SQL syntax

改进方法1：恒等式  统一加and

    
            select * from tb_brand
            where  1=1
              
                  and status = #{status}
              
              
                  and brand_name like #{brandName}
              
              
                  and company_name like #{companyName}
              
    
 
改进方法二：mybatis标签替换where关键字
 
    
            select * from tb_brand
       
              
                  and status = #{status}
              
              
                  and brand_name like #{brandName}
              
              
                  and company_name like #{companyName}
              
       
    

单条件-动态条件查询
BrandMapper.java

public interface BrandMapper {
    List selectSingleCondition(Brand brand);
    }

BrandMapper.xml

    
        select * from tb_brand
        
           
               
                    status=#{status}
               
               
                    brand_name  like  #{brandName}
               
               
                    company_name  like #{companyName}
               
           
        
    
 
MyBatisTest.java
 
 //模拟接收参数
        int status =1;
        String brandName = "华为";
        String companyName = "华为";
        //处理参数
        brandName ="%"+brandName+"%";
        companyName = "%"+companyName+"%";
        //封装对象
        Brand brand = new Brand();
        // brand.setStatus(status);
        brand.setBrandName(companyName);
        //brand.setCompanyName(brandName);
        ···
           List brands1 = brandMapper.selectSingleCondition(brand);
        System.out.println(brands1);
 
添加（增、删、改都需要提交事务 ，不然无法修改数据库信息）
 
 BrandMapper.java
 
public interface BrandMapper {
    void add(Brand brand);
    }
 
BrandMapper.xml
 
    
        insert into tb_brand (brand_name,company_name,ordered,description,status)
        values (#{brandName},#{companyName},#{ordered},#{description},#{status});
    
 
MyBatisTest.java
 
    @Test
    public void add() throws IOException {
        //模拟接收参数
        String brandName = "小米手机";
        String companyName = "小米";
        int ordered =100;
        String description ="为发烧而生";
        int status =1;
        //封装参数
        Brand brand = new Brand();
        brand.setBrandName(brandName);
        brand.setCompanyName(companyName);
        brand.setOrdered(ordered);
        brand.setDescription(description);
        brand.setStatus(status);
        //获取SqlsessionFactory
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        //获取Sqlsession对象
        SqlSession sqlSession = sqlSessionFactory.openSession();
        BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
        //执行方法
        brandMapper.add(brand);
        //提交事务
        sqlSession.commit();
        //释放资源
        sqlSession.close();
    }

 
添加-主键返回
 
 若要在新增完成后获取主键id ，在insert标签中定义两个量:
 
 修改全部字段
 
 BrandMapper.java
 
public interface BrandMapper {
    int update(Brand brand);
}
 
BrandMapper.xml
 
    
        update tb_brand
        set
        brand_name = #{brandName},
        company_name = #{companyName},
        ordered = #{ordered},
        description = #{description},
        status = #{status}
        where id = #{id};
    
 
MyBatisTest.java
 
 @Test
    public void update() throws IOException {
        //模拟接收参数
        String brandName = "ooo";
        String companyName = "oo";
        int ordered =100;
        String description ="1111111";
        int status =1;
        int id =5;
        //封装参数
        Brand brand = new Brand();
        brand.setBrandName(brandName);
        brand.setCompanyName(companyName);
        brand.setOrdered(ordered);
        brand.setDescription(description);
        brand.setStatus(status);
        brand.setId(id);
        //获取SqlsessionFactory
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        //获取Sqlsession对象
        SqlSession sqlSession = sqlSessionFactory.openSession();
        BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
        //执行方法
        int count = brandMapper.update(brand);
        System.out.println(count);
        //提交事务
        sqlSession.commit();
        //释放资源
        sqlSession.close();
    }
 
修改动态字段
 
 利用标签可以避免出错；
 即在BrandMapper.xml中修改为：
 
    
        update tb_brand
      
        
            brand_name = #{brandName},
        
        
            company_name = #{companyName},
        
        
            ordered = #{ordered},
        
        
            description = #{description},
        
        
            status = #{status}
        
      
        where id = #{id};
    
 
删除
 BrandMapper.java
 
public interface BrandMapper {
    void deleteById(int id);
}
 
BrandMapper.xml
 
    
        delete from tb_brand where id =#{id};
    
 
MyBatisTest.java
 
@Test
    public void deleteById() throws IOException {
        //模拟接收参数
        int id =5;
        //获取SqlsessionFactory
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        //获取Sqlsession对象
        SqlSession sqlSession = sqlSessionFactory.openSession();
        BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
        //执行方法
        brandMapper.deleteById(id);
        //提交事务
        sqlSession.commit();
        //释放资源
        sqlSession.close();
    }
}
 
批量删除
 会把选中的ID封装成一个数组，在根据数组的值把对应id全部删除。
 
 
 
 

 利用foreach来遍历集合，其中collection表明指定便利的数组，item指定便利的元素
 
 
mybatis中会将数组参数，封装为一个Map集合。
 *默认：array = 数组 *在.java中使用@Param注解来改变map集合的默认key的名称
 如void deleteByIds（@Param("ids")"ids"）int [] ids);
 则.xml中可以使用：
 
代码如下：
 BrandMapper.java
 
public interface BrandMapper {
    void deleteByIds(@Param("ids") int[] ids);
}
 
BrandMapper.xml
 
    
        delete from tb_brand
        where id in
        
            #{id}
        
    
 
MyBatisTest.java
 
    @Test
    public void deleteByIds() throws IOException {
        //模拟接收参数
        int[] ids = {6,7};
        //获取SqlsessionFactory
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        //获取Sqlsession对象
        SqlSession sqlSession = sqlSessionFactory.openSession();
        BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
        //执行方法
        brandMapper.deleteByIds(ids);
        //提交事务
        sqlSession.commit();
        //释放资源
        sqlSession.close();
    }
 
MyBatis参数传递
 
 
 注解开发
 
 代码如下：
 BrandMapper.java
 
@Select("select * from tb_brand where id = #{id}")
Brand selectById(int id);
 
MyBatisTest.java
 
    @Test
    public void selecttest() throws IOException {
        //模拟接收参数
        int id=1;
        //获取SqlsessionFactory
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        //获取Sqlsession对象
        SqlSession sqlSession = sqlSessionFactory.openSession();
        BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
        //执行方法
        Brand brand = new Brand();
        brand= brandMapper.selectById(id);
        System.out.println(brand);
        //释放资源
        sqlSession.close();
    }
 
总结：简单的用注解开发可以不用xml配置，但是复杂的业务还是需要用xml配置
欢迎分享，转载请注明来源：内存溢出
原文地址:https://54852.com/zaji/5707658.html
华为参数获取对象接收
赞
(0)
打赏
微信扫一扫
支付宝扫一扫
解决夫妻两地分居一级用户组
00

生成海报
java笔记7----java中的方法、递归
上一篇
2022-12-17
移动开发-flutter环境的搭建

下一篇2022-12-17

发表评论

请登录后评论...
登录后才能评论
提交
评论列表（0条）

	



















保存