MyBatis入门增删改查

MyBatis定义

MyBatis是一款优秀的持久性框架, 使用MyBatis可以轻松实现Java程序向数据库发送SQL语句, 而且对于SQL查询回来的结果进行方便的封装

例如,如图所示: 将user对象保存到user数据库中去

操作流程示意图

操作体系示意图

ORM (Object Erlational Mapping)

就是 对象关系映射, 就是建立实体类和数据库表之间的关系

JDBC概述

JDBC： Java Database Connectivity ， Java数据库连接 ， 是Java语言中用来规范客户端程序如何来访问数据库的应用程序接口，提供了诸如查询和更新数据库中数据的方法。

环境搭建

1. 创建项目

2. 拷贝坐标 pom.xml

<dependencies>
    <!--mysql-->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.28</version>
    </dependency>
    <!--mybatis-->
    <dependency>
        <groupId>org.mybatis</groupId>
        <artifactId>mybatis</artifactId>
        <version>3.5.9</version>
    </dependency>
    <!--junit-->
    <dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
        <version>4.13.2</version>
        <scope>test</scope>
    </dependency>
    <!--lombok-->
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <version>1.18.26</version>
    </dependency>
</dependencies>

3. 创建数据库user 和表 user

create database day040_mybatis;
use day040_mybatis;

-- 用户表
create table user(
    id int unsigned primary key auto_increment comment 'ID',
    name varchar(100) comment '姓名',
    age tinyint unsigned comment '年龄',
    gender tinyint unsigned comment '性别, 1:男, 2:女',
    phone varchar(11) comment '手机号'
) comment '用户表';

insert into user(id, name, age, gender, phone) VALUES (null,'白眉鹰王',55,'1','18800000000');
insert into user(id, name, age, gender, phone) VALUES (null,'金毛狮王',45,'1','18800000001');
insert into user(id, name, age, gender, phone) VALUES (null,'青翼蝠王',38,'1','18800000002');
insert into user(id, name, age, gender, phone) VALUES (null,'紫衫龙王',42,'2','18800000003');
insert into user(id, name, age, gender, phone) VALUES (null,'光明左使',37,'1','18800000004');
insert into user(id, name, age, gender, phone) VALUES (null,'光明右使',48,'1','18800000005');

4. 编写JavaBean，添加注解User.java

lombok工具

用于简化JavaBean开发的
        @Data: 省略geter和setter方法、省略重写toString，省略重写Equals和HashCode,
        @NoArgsConstructor: 省略无参构造
        @AllArgsConstructor：省略全参构造
    lombok在idea需要插件支持的

@Data
@NoArgsConstructor
@AllArgsConstructor
public class User {
    private Integer id;
    private String name;        //姓名
    private Integer age;        //年龄
    private Integer gender;     //性别：1：男，2:女
    private String phone;       //手机号

}

5.  核心配置文件 SqlMapConfig.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>

    <settings>
        <!--在控制台输出发送的sql日志-->
        <setting name="logImpl" value="STDOUT_LOGGING"/>
    </settings>

    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>

            <!--目前只关注这部分内容,它的作用就是声明要连接的数据信息-->
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/数据库database的名字"/>
                <property name="username" value="用户名root"/>
                <property name="password" value="密码1234"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <!--声明含有sql的接口所在包-->
        <package name="创建的包名"/>
    </mappers>
</configuration>

6. 工具类 MybatisUtils.class

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;


public class MybatisUtils {

    private static SqlSessionFactory sessionFactory;

    static {
        try {
            //1 获得核心配置文件
            InputStream is = Resources.getResourceAsStream("SqlMapConfig.xml");

            //2 加载核心配置文件，获得连接工厂
            sessionFactory = new SqlSessionFactoryBuilder().build(is);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }

    }

    /**
     * 获得会话
     * @return
     */
    public static SqlSession getSqlSession() {
        SqlSession session = sessionFactory.openSession();
        return session;
    }

    /**
     * 释放资源
     * @param session
     */
    public static void close(SqlSession session) {
        if(session != null) {
            //事务打开了，需要提交
            session.commit();

            //6 释放资源
            session.close();
        }
    }
}

7. 处理Mapper接口(增删改查) userMapper.interface

我们要把sql写在接口中的方法上

package org.mapper;

import org.apache.ibatis.annotations.*;
import org.domain.User;

import java.util.List;

public interface UserMapper {

    //查询所有
    @Select("select * from user")
    List<User> selectAll();

    //根据id查询数据
    @Select("select * from user where id = #{id}")
    public User selectById(@Param("id") Integer id);

    //添加数据
    @Insert("insert into user values (null, #{name},#{age},#{gender},#{phone})")
    @Options(useGeneratedKeys = true,keyProperty = "id")
    public void insert(User user);

    //修改
    @Update("update user set name = #{name} where id = #{id}")
    void update(User user);

    // 修改所有
    @Update("update user set name = #{name}, age= #{age}, gender = #{gender}, phone = #{phone} where id = #{id}")
    void updateAll(User user);

    // 根据id删除数据
    @Delete("delete from user where id = #{id}")
    void delete(Integer id);
}

8. 测试类(增删改查) TestUserMapper.java

package mapper;

import org.apache.ibatis.session.SqlSession;
import org.domain.User;
import org.junit.Test;
import org.mapper.UserMapper;
import org.utils.MyBatisUtils;

import java.util.List;

public class TestUserMapper {
    //查询所有
    @Test
    public void selectAll(){
        //获得连接
        SqlSession sqlSession = MyBatisUtils.getSqlSession();
        //获得映射
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        List<User> userList = mapper.selectAll();
        System.out.println(userList);
    }

    //通过id查询信息
    @Test
    public void selectById(){
        // 获得连接
        SqlSession sqlSession = MyBatisUtils.getSqlSession();
        // 获得映射
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);

        mapper.selectById(1);
    }

    //增加
    @Test
    public void insert(){
        //获得连接
        SqlSession sqlSession = MyBatisUtils.getSqlSession();
        //获得映射
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);

        User user = new User(null,"李思思",21,1,"12321234532");
        mapper.insert(user);
        System.out.println(user);

        // 提交
        sqlSession.commit();

        // 释放缓存
        sqlSession.close();
    }

    //修改
    @Test
    public void update(){
        //获得连接
        SqlSession sqlSession = MyBatisUtils.getSqlSession();
        // 获得映射
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        User user = new User();
        user.setId(19);
        user.setName("dxy");
        mapper.update(user);

        // 提交
        sqlSession.commit();
        //释放
        sqlSession.close();
    }

    @Test
    public void updateAll(){
        //获得连接
        SqlSession sqlSession = MyBatisUtils.getSqlSession();
        //获得映射
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);

        User user = new User(17,"lcx",23,1,"12323456789");
        mapper.updateAll(user);
        System.out.println(user);

        // 提交
        sqlSession.commit();
        //释放
        sqlSession.close();
    }

    //根据id删除数据
    @Test
    public void delete(){
        // 获得连接
        SqlSession sqlSession = MyBatisUtils.getSqlSession();
        //获得映射
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);

        mapper.delete(19);

        // 提交
        sqlSession.commit();
        //释放
        sqlSession.close();
    }

}