MyBatis在映射文件中CRUD，注解的方式CRUD，API方式CRUD

本文链接：https://blog.youkuaiyun.com/weixin_45930636/article/details/104408949

一、MyBatils 在映射文件中编写sql

1.建库，建表编写对应实体类

//创建表
CREATE TABLE `tbl_cat` (
  `id` INT(10) NOT NULL AUTO_INCREMENT,
  `catName` VARCHAR(20) DEFAULT NULL,
  `age` INT(11) DEFAULT NULL,
  `birth` DATE DEFAULT NULL,
  PRIMARY KEY (`id`)
);
 
INSERT INTO tbl_cat(catName,age,birth) VALUES('whiteCat',7,CURRENT_DATE());
INSERT INTO tbl_cat(catName,age,birth) VALUES('blackCat',5,CURRENT_DATE());
INSERT INTO tbl_cat(catName,age,birth) VALUES('yellowCat',3,CURRENT_DATE());`tbl_cat`

//创建实体类
package cn.yhy.entity;

import java.util.Date;

public class Cat {
    private Integer id;
    private String  catName;
    private int     age;
    private Date    birth;

    public Cat() {}

    public Integer getId()
    {
        return id;
    }

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

    public String getCatName()
    {
        return catName;
    }

    public void setCatName(String catName)
    {
        this.catName = catName;
    }

    public int getAge()
    {
        return age;
    }

    public void setAge(int age)
    {
        this.age = age;
    }

    public Date getBirth()
    {
        return birth;
    }

    public void setBirth(Date birth)
    {
        this.birth = birth;
    }

    @Override
    public String toString()
    {
        return "Cat [id=" + id + ", catName=" + catName + ", age=" + age
                + ", birth=" + birth + "]";
    }
}

2.编写CRUD接口 xxxMapper

public interface CatMapper {
    /**
     * 查询根据id和姓名
     * @param cat
     * @return
     */
    public Cat getCat(Cat cat);

    /**
     * 模糊查询
     * @param name
     * @return
     */
    public List<Cat> getDim(String name);

    /**
     * 添加
     * @param cat
     * @return
     */
    public int getInsert(Cat cat);

    /**
     * 修改
     * @param cat
     */
    public int getUpdate(Cat cat);

    /**
     * 删除
     * @param id
     * @return
     */
    public int getDelete(int id);
}

3.创建sql的映射文件 xxxMapper

<?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="cn.yhy.mapper.CatMapper">
<!--通过id和姓名查询-->
    <select id="getCat" parameterType="Cat" resultType="Cat">
        select * from tbl_cat where id=#{id} and catName=#{catName}
    </select>
<!--模糊查询的第一种方式-->
<!--    <select id="getDim" parameterType="Cat" resultType="Cat">-->
<!--        select * from tbl_cat where catName like #{catName}-->
<!--    </select>-->
<!--模糊查询的第二种方式-->
    <select id="getDim" parameterType="Cat" resultType="Cat">
         select * from tbl_cat where catName like '%${value}%'
    </select>
<!--添加信息-->
    <insert id="getInsert" parameterType="Cat">
        insert into tbl_cat(catName,age,birth) values (#{catName},#{age},#{birth})
    </insert>
<!--修改信息-->
    <update id="getUpdate" parameterType="Cat">
        update tbl_cat set catName=#{catName} where id=#{id}
    </update>
<!--删除-->
    <delete id="getDelete" parameterType="Cat">
        delete from tbl_cat where id=#{id}
    </delete>
</mapper>

4.在myBatis-config.xml中注册映射文件

	<mappers>
        <mapper resource="static/CatMapper.xml"/>
        <!--写入编写sql的xml文件，注意路径问题-->
        <mapper resource="static/PersonMapper.xml"/>
        <!--注解方式-->
        <mapper class="cn.yhy.mapper.PersonMapperAnnotation"></mapper>
        <!--Cat 注解方式增删改查-->
        <mapper class="cn.yhy.mapper.CatMapperAnnotation"></mapper>
    </mappers>

5.测试类测试通过session.getMapper(CatMapperAnnotation.class);

public class TestCatMapper {
    static SqlSessionFactory sqlSessionFactory=null;
    static {
        String resource = "mybatis-config.xml";
        InputStream inputStream = null;
        try {
            inputStream = Resources.getResourceAsStream(resource);
        } catch (IOException e) {
            e.printStackTrace();
        }
        sqlSessionFactory=new SqlSessionFactoryBuilder().build(inputStream);
    }

    public static void main(String[] args) {

        SqlSession session=sqlSessionFactory.openSession();
        try  {

            CatMapper mapper = session.getMapper(CatMapper.class);
            //根据id 和 name查询数据
//            Cat cat=new Cat();
//            cat.setId(2);
//            cat.setCatName("blackCat");
//            Cat cat1 = mapper.getCat(cat);
//            System.out.println(cat1);


            //模糊查询的第一种方式
//            List<Cat> dim = mapper.getDim("%w%");
//            for (Cat cat : dim) {
//                System.out.println(cat);
//            }


            //模糊查询的第二种方式
//            List<Cat> w = mapper.getDim("w");
//            for (Cat cat : w) {
//                System.out.println(cat);
//            }


            //添加信息
//            Cat cat=new Cat(null,"redCat",6,new Date());
//            int insert = mapper.getInsert(cat);
//            System.out.println(insert);


            //修改信息
//            Cat cat=new Cat(3,"greenCat",0,null);
//            int update = mapper.getUpdate(cat);
//            System.out.println(update);

            //删除信息
            int delete = mapper.getDelete(3);
            System.out.println(delete);
            session.commit();
        }finally {
            session.close();
        }
    }

二、MyBatis以注解的方式增删改查

1.建库建表

2.编写CRUD接口 xxxMapper

public interface CatMapperAnnotation {
    /**
     * 查询根据id和姓名
     * @param cat
     * @return
     */
    @Select("select * from tbl_cat where id=#{id} and catName=#{catName}")
    public Cat getCat(Cat cat);

    /**
     * 模糊查询
     * @param name
     * @return
     */
    //模糊查询的第一种方式
    //@Select("select * from tbl_cat where catName like #{catName}")
    //模糊查询的第二种方式
    @Select("select * from tbl_cat where catName like '%${value}%'")
    public List<Cat> getDim(String name);

    /**
     * 添加
     * @param cat
     * @return
     */
    @Insert("insert into tbl_cat(catName,age,birth) values (#{catName},#{age},#{birth})")
    public int getInsert(Cat cat);

    /**
     * 修改
     * @param cat
     */
    @Update("update tbl_cat set catName=#{catName} where id=#{id}")
    public int getUpdate(Cat cat);

    /**
     * 删除
     * @param id
     * @return
     */
    @Delete("delete from tbl_cat where id=#{id}")
    public int getDelete(int id);
}

3.在myBatis-config.xml中注册该接口

	<mappers>
        <mapper resource="static/CatMapper.xml"/>
        <!--写入编写sql的xml文件，注意路径问题-->
        <mapper resource="static/PersonMapper.xml"/>
        <!--注解方式-->
        <mapper class="cn.yhy.mapper.PersonMapperAnnotation"></mapper>
        <!--Cat 注解方式增删改查-->
        <mapper class="cn.yhy.mapper.CatMapperAnnotation"></mapper>
    </mappers>

4.在测试类中测试

public class TestCatAnnotation {
    static SqlSessionFactory sqlSessionFactory=null;
    static {
        String resource = "mybatis-config.xml";
        InputStream inputStream = null;
        try {
            inputStream = Resources.getResourceAsStream(resource);
        } catch (IOException e) {
            e.printStackTrace();
        }
        sqlSessionFactory=new SqlSessionFactoryBuilder().build(inputStream);
    }

    public static void main(String[] args) {

        SqlSession session=sqlSessionFactory.openSession();
        try  {

            CatMapperAnnotation mapper = session.getMapper(CatMapperAnnotation.class);

            //注解的方式查询  根据id和name
//            Cat cat = new Cat(4,"redCat",0,null);
//            Cat cat1 = mapper.getCat(cat);
//            System.out.println(cat1);


            //注解的第一种方式模糊查询
            //List<Cat> w = mapper.getDim("%w%");

            //注解的第二种方式模糊查询
//            List<Cat> w = mapper.getDim("w");
//            for (Cat cat : w) {
//                System.out.println(cat);
//            }


            //注解的方式  增加信息
//            Cat cat=new Cat(null,"blueCat",9,new Date());
//            int insert = mapper.getInsert(cat);
//            System.out.println(insert);


            //注解的方式 修改信息
//            Cat cat=new Cat(1,"pink",0,null);
//            int update = mapper.getUpdate(cat);
//            System.out.println(update);

            //注解的方式  删除信息
            int delete = mapper.getDelete(4);
            System.out.println(delete);
            session.commit();
        }finally {
            session.close();
        }
    }

三、MyBatis以API的方式增删改查

1.建表编写实体类和普通的一样

2.创建接口编写CRUD功能和普通的一样

3.映射文件编写sql 和普通的一样

4.myBatis-config.xml文件中映射sql文件和普通的一样

5.测试类测试结果直接使用session自带的增删改查进行处理

public class TestCatMapperAPI {
    static SqlSessionFactory sqlSessionFactory=null;
    static {
        String resource = "mybatis-config.xml";
        InputStream inputStream = null;
        try {
            inputStream = Resources.getResourceAsStream(resource);
        } catch (IOException e) {
            e.printStackTrace();
        }
        sqlSessionFactory=new SqlSessionFactoryBuilder().build(inputStream);
    }

    public static void main(String[] args) {

        SqlSession session=sqlSessionFactory.openSession();
        try  {
            //查询所有
            List<Cat> list = session.selectList("cn.yhy.mapper.CatMapperAPI.getCatAll");
            for (Cat cat : list) {
                System.out.println(cat);
            }

            //API的方式模糊查询
//            List<Cat> list = session.selectList("cn.yhy.mapper.CatMapperAPI.getDim", "b");
//            System.out.println(list);


            //API的方式根据id and name查询
//            Cat cat = new Cat();
//            cat.setId(1);
//            cat.setCatName("pink");
//            System.out.println(session.selectList("cn.yhy.mapper.CatMapperAPI.getCat", cat));


            //API的方式 增加
//            Cat cat=new Cat(null,"redCat",5,new Date());
//            System.out.println(session.insert("cn.yhy.mapper.CatMapperAPI.getInsert",cat));


            //API的方式 修改
//            Cat cat=new Cat(1,"grow",0,null);
//            System.out.println(session.update("cn.yhy.mapper.CatMapperAPI.getUpdate",cat));


            //API的方式 删除
//            System.out.println(session.update("cn.yhy.mapper.CatMapperAPI.getDelete",6));
            session.commit();
        }finally {
            session.close();
        }
    }