一小时迅速入门Mybatis之增删查改篇

一、说明

这二篇涉及到映射Java实体类、面向接口编写Mybatis、增删改查示例

怎么引入jar包,怎么配置数据库看上一篇哦~

二、开搞
2.1 数据库表

上一篇好像丢了数据库创建语句

-- 主键自增
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT, 
  `name` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `salary` decimal(10, 2) NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- 插入测试数据
-- ----------------------------
INSERT INTO `test` VALUES (1, '小明', 30000.00);
2.1 创建实体类
package entity;

import java.math.BigDecimal;

/**
 * @author 发现更多精彩  关注公众号:木子的昼夜编程
 * 一个生活在互联网底层,做着增删改查的码农,不谙世事的造作
 * @create 2021-08-25 22:05
 */
public class TestEntity {
    private  Long id;
    private String name;
    private BigDecimal salary;

    public Long getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public BigDecimal getSalary() {
        return salary;
    }

    public void setSalary(BigDecimal salary) {
        this.salary = salary;
    }

    @Override
    public String toString() {
        return "TestEntity{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", salary=" + salary +
                '}';
    }
}
2.2 创建接口
package dao;

import entity.TestEntity;

import java.util.List;

/**
 * @author 发现更多精彩  关注公众号:木子的昼夜编程  分享一个生活在互联网底层做着增删改查的码农的感悟与学习
 * @create 2021-08-25 22:07
 */
public interface TestMapper {
    // 新增
    void save(TestEntity testEntity);

    // 修改
    void update(TestEntity testEntity);

    // 删除 这里就一个参数 所以不用@Param 也不用Map 自定义实体类等
    void delete(Long id);

    // 根据主键查询
    TestEntity get(Long id);

    // 查询所有数据
    List<TestEntity> list();

    // 根据名称模糊查询
    List<TestEntity> listByNameLike(String name);
}
2.3 创建XML

mybatis-config.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//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.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&amp;characterEncoding=utf-8&amp;useSSL=false&amp;serverTimezone=GMT%2B8"/>
                <property name="username" value="root"/>
                <property name="password" value="123456"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="TestMapper.xml"/>
    </mappers>
</configuration>

TestMapper.xml

<?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="dao.TestMapper">
    <!--增加-->
    <insert id="save" >
        INSERT INTO `test`( `name`, `salary`) VALUE (#{name}, #{salary});
    </insert>

    <!--删除-->
    <delete id="delete">
        delete from test where id = #{id}
    </delete>

    <!--根据主键查询-->
    <select id="get" resultType="entity.TestEntity">
        select * from test where id = #{id}
    </select>

    <!--查询所有数据-->
    <select id="list"  resultType="entity.TestEntity">
        select * from test
    </select>

    <!--根据名称模糊查询-->
    <select id="listByNameLike" resultType="entity.TestEntity">
        select * from test  where name like CONCAT('%',#{name},'%')
    </select>

    <update id="update">
        update test set name =#{name}, salary = #{salary} where id = #{id}
    </update>
</mapper>
2.5 测试类
  1. 先看一下数据库数据

    BC388FCD-D277-68E2-E0D1-9BF705A4239A.png

  2. 新增数据

    import dao.TestMapper;
    import entity.TestEntity;
    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.InputStream;
    import java.math.BigDecimal;
    import java.util.List;
    import java.util.Map;
    
    /**
     * @author 发现更多精彩  关注公众号:木子的昼夜编程
     * 一个生活在互联网底层,做着增删改查的码农,不谙世事的造作
     * @create 2021-08-25 21:26
     */
    public class TestMain {
        public static void main(String[] args) throws Exception {
            String resource = "mybatis-config.xml";
            InputStream inputStream = Resources.getResourceAsStream(resource);
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
            try (SqlSession session = sqlSessionFactory.openSession()) {
                // 通过sesson获取Mapper 这个Mapper会编程Mybatis的代理Mapper
                TestMapper mapper = session.getMapper(TestMapper.class);
                System.out.println(mapper);
                // 1. 插入数据
                TestEntity entity = new TestEntity();
                entity.setName("小月鸟");
                entity.setSalary(new BigDecimal(50000));
                mapper.save(entity);
                TestEntity entity02 = new TestEntity();
                entity02.setName("小强01");
                entity02.setSalary(new BigDecimal(50000));
                mapper.save(entity02);
                TestEntity entity03 = new TestEntity();
                entity03.setName("小强02");
                entity03.setSalary(new BigDecimal(50000));
                mapper.save(entity03);
                // 手动提交
                session.commit();
            }
        }
    }

    执行完查看数据库数据:

293C5921-4EF3-CA92-DA62-1E249BE5EF51.png

  1. 根据Id 查询数据

    import dao.TestMapper;
    import entity.TestEntity;
    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.InputStream;
    
    /**
     * @author 发现更多精彩  关注公众号:木子的昼夜编程
     * 一个生活在互联网底层,做着增删改查的码农,不谙世事的造作
     * @create 2021-08-25 21:26
     */
    public class TestMain {
        public static void main(String[] args) throws Exception {
            String resource = "mybatis-config.xml";
            InputStream inputStream = Resources.getResourceAsStream(resource);
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
            try (SqlSession session = sqlSessionFactory.openSession()) {
                // 通过sesson获取Mapper 这个Mapper会编程Mybatis的代理Mapper
                TestMapper mapper = session.getMapper(TestMapper.class);
                System.out.println(mapper);
                // 1. 根据Id 查询数据
                TestEntity testEntity = mapper.get(1L);
                System.out.println("查询数据为:"+testEntity);
            }
        }
    }

    输出结果:

A6E93120-2CB1-7FC9-6801-40C73D91778D.png

  1. 更新数据

    把”小月鸟“ 工资改成40000

    import dao.TestMapper;
    import entity.TestEntity;
    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.InputStream;
    import java.math.BigDecimal;
    
    /**
     * @author 发现更多精彩  关注公众号:木子的昼夜编程
     * 一个生活在互联网底层,做着增删改查的码农,不谙世事的造作
     * @create 2021-08-25 21:26
     */
    public class TestMain {
        public static void main(String[] args) throws Exception {
            String resource = "mybatis-config.xml";
            InputStream inputStream = Resources.getResourceAsStream(resource);
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
            try (SqlSession session = sqlSessionFactory.openSession()) {
                // 通过sesson获取Mapper 这个Mapper会编程Mybatis的代理Mapper
                TestMapper mapper = session.getMapper(TestMapper.class);
                System.out.println(mapper);
                // 更新
                TestEntity entityUpdate = new TestEntity();
                entityUpdate.setId(40L);
                entityUpdate.setName("小月鸟");
                entityUpdate.setSalary(new BigDecimal(40000));
                mapper.update(entityUpdate);
                session.commit();
            }
        }
    }

    执行完查看数据库数据:

48012BEF-8C1E-C79A-7611-D47591415680.png

  1. 查询全部数据

    import dao.TestMapper;
    import entity.TestEntity;
    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.InputStream;
    import java.util.List;
    
    /**
     * @author 发现更多精彩  关注公众号:木子的昼夜编程
     * 一个生活在互联网底层,做着增删改查的码农,不谙世事的造作
     * @create 2021-08-25 21:26
     */
    public class TestMain {
        public static void main(String[] args) throws Exception {
            String resource = "mybatis-config.xml";
            InputStream inputStream = Resources.getResourceAsStream(resource);
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
            try (SqlSession session = sqlSessionFactory.openSession()) {
                // 通过sesson获取Mapper 这个Mapper会编程Mybatis的代理Mapper
                TestMapper mapper = session.getMapper(TestMapper.class);
                System.out.println(mapper);
                // 查询列表
                List<TestEntity> list = mapper.list();
                if (list.size() >0) {
                    for (int i = 0; i < list.size(); i++) {
                        System.out.println(list.get(i));
                    }
                }
            }
        }
    }

    输出结果:

    CF398034-6144-C0D0-4483-534BD96E823E.png

  2. 根据名称查询数据

    import dao.TestMapper;
    import entity.TestEntity;
    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.InputStream;
    import java.util.List;
    
    /**
     * @author 发现更多精彩  关注公众号:木子的昼夜编程
     * 一个生活在互联网底层,做着增删改查的码农,不谙世事的造作
     * @create 2021-08-25 21:26
     */
    public class TestMain {
        public static void main(String[] args) throws Exception {
            String resource = "mybatis-config.xml";
            InputStream inputStream = Resources.getResourceAsStream(resource);
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
            try (SqlSession session = sqlSessionFactory.openSession()) {
                // 通过sesson获取Mapper 这个Mapper会编程Mybatis的代理Mapper
                TestMapper mapper = session.getMapper(TestMapper.class);
                System.out.println(mapper);
                // 根据名称模糊查询列表
                List<TestEntity> list = mapper.listByNameLike("强");
                if (list.size() >0) {
                    for (int i = 0; i < list.size(); i++) {
                        System.out.println(list.get(i));
                    }
                }
            }
        }
    }

    输出结果:

0C00AE1A-C235-2727-2282-4729C1F09F76.png

  1. 删除数据

    import dao.TestMapper;
    import entity.TestEntity;
    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.InputStream;
    import java.util.List;
    
    /**
     * @author 发现更多精彩  关注公众号:木子的昼夜编程
     * 一个生活在互联网底层,做着增删改查的码农,不谙世事的造作
     * @create 2021-08-25 21:26
     */
    public class TestMain {
        public static void main(String[] args) throws Exception {
            String resource = "mybatis-config.xml";
            InputStream inputStream = Resources.getResourceAsStream(resource);
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
            try (SqlSession session = sqlSessionFactory.openSession()) {
                // 通过sesson获取Mapper 这个Mapper会编程Mybatis的代理Mapper
                TestMapper mapper = session.getMapper(TestMapper.class);
                System.out.println(mapper);
                // 删除数据
                mapper.delete(1L);
    			session.commit();
            }
        }
    }

    执行完查看数据库数据:

    5367C97B-C3D5-9CDA-5384-B0DD69165EC9.png

项目结构:

028B12F4-9F45-9562-2B21-4C408638C5E0.png

2.6 唠唠
  1. 刚开始新增没有成功 是因为没有手动commit (改变数据库数据都需要commit)

    现在大部分项目都是结合SpringBoot 事务都交给Spring管理了都不需要自己手动commit了

  2. 实体类没有用别名 直接用的全类路径 实际项目中有用别名的有用全类路径的

  3. 新增的时候没有返回自增主键的值 实际项目可能会用到这个值

    1. 更新的时候写死的字段 实际项目可能会根据不同的值进行不同列的更新

    下篇预告:

    1. 实体类用别名
  4. 新增返回自增主键的值

    1. 多个参数的使用
    2. 动态Sql的常用标签
    3. 聊一聊 insert delete select update标签

补充一个知识点(应该不用深入研究):

SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

SqlSessionFactoryBuilder有好几种方式创建SqlSessionFactory

除了使用xml还可以纯Java代码创建

收藏 (0)
评论列表
正在载入评论列表...
我是有底线的
为您推荐
    暂时没有数据