mybatis操作数据库

mybatis介绍#

mybatis不会对应用程序或者数据库的现有设计强加任何影响。 sql写在xml里，便于统一管理和优化。通过sql语句可以满足操作数据库的所有需求。

mybatis结构#

操作步骤#

编写数据库对于的实体类entity#

@Getter
@Setter
@ToString
public class User {
    private Integer id;
    private String name;
    private String position;
    private LocalDateTime create_time;
    private LocalDateTime modify_time;
    private Integer status;
}

    <dependencies>
<!--      junit5坐标-->
        <dependency>
            <groupId>org.junit.jupiter</groupId>
            <artifactId>junit-jupiter-api</artifactId>
            <version>5.8.2</version>
            <scope>test</scope>
        </dependency>
<!--       mysql坐标-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.28</version>
        </dependency>
<!--        快速构建get set toString方法-->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.24</version>
        </dependency>
<!--        mybatis坐标 -->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.9</version>
        </dependency>

    </dependencies>

编写mybatis核心配置文件sqlMapConfig.xml#

具体内容参考mybatis官网：https://mybatis.org/mybatis-3/zh/getting-started.html

<?xml version="1.0" encoding="UTF-8" ?>
        <!DOCTYPE configuration
                PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
                "https://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <environments default="dev">
<!--       environment：环境   可配置多套环境 -->
        <environment id="dev">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/testdb2?characterEncoding=UTF-8&amp;useUnicode=true&amp;useSSL=false&amp;serverTimezone=GMT"/>
                <property name="username" value="root"/>
                <property name="password" value="123456"/>
            </dataSource>
        </environment>
        
        <environment id="test">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/testdb2?characterEncoding=UTF-8&amp;useUnicode=true&amp;useSSL=false&amp;serverTimezone=GMT"/>
                <property name="username" value="root"/>
                <property name="password" value="123456"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
<!--        可配置多套数据来源 -->
        <mapper resource="UserMapper.xml"/>
        <mapper resource="UserMapper2.xml"/>
    </mappers>
</configuration>

编写实体类映射文件UserMapper.xml#

<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">

<!--namespace：命名空间
id：sql语句的id    sql唯一标识user.findAll
resultType: 对于的实体类全路径
-->
<mapper namespace="user">
    <select id="findAll" resultType="com.wn.entity.User">
        select * from user
    </select>

    <select id="findOne" parameterType="int" resultType="com.wn.entity.User">
        select * from user where id=#{id}
    </select>

    <insert id="addData" parameterType="com.wn.entity.User">
        insert into user(name,position,create_time,modify_time)
        values(#{name},#{position},#{create_time},#{modify_time})
    </insert>

    <update id="updateData" parameterType="com.wn.entity.User">
        update user set position=#{position} where name=#{name}
    </update>

    <delete id="deleteData" parameterType="String">
        delete from user where name=#{String}
    </delete>
</mapper>

编写操作类#

package com.wn.entity;

import jdk.internal.util.xml.impl.Input;
import lombok.val;
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.jupiter.api.Test;

import java.io.IOException;
import java.io.InputStream;
import java.time.LocalDateTime;
import java.util.List;


class UserTest {
    @Test
    void selectTest() throws IOException {
        //加载核心配置文件
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        //获取sqlSessionFactory工厂对象
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        //获取sqlSession会话
        SqlSession sqlSession = sqlSessionFactory.openSession();
        //执行查询全部数据sql
//        List<User> userList = sqlSession.selectList("user.findAll");
//        List<User> userList = sqlSession.selectList("user2.findOne");
        //执行条件查询sql
        List<User> userList = sqlSession.selectList("user.findOne", 1);

        //打印
        userList.forEach(System.out::println);
        //关闭sqlSession会话
        sqlSession.close();
    }
    @Test
    void insertTest() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        //插入数据
        User user = new User();
        user.setName("测试插入");
        user.setPosition("测试");
        user.setCreate_time(LocalDateTime.now());
        user.setModify_time(LocalDateTime.now());

        int insertUser = sqlSession.insert("user.addData",user);
        sqlSession.commit();

        System.out.println(insertUser);
        sqlSession.close();
    }
    @Test
    void updateTest() throws IOException {
        InputStream inputStream=Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        //更新数据
        User user=new User();
        user.setName("老张");
        user.setPosition("财务");

        int updateUser = sqlSession.update("user.updateData", user);
        sqlSession.commit();

        System.out.println(updateUser);
        sqlSession.close();
    }
    @Test
    void deleteTest() throws IOException {
        InputStream inputStream=Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        //指定name用户名删除数据
        int deleteUser = sqlSession.update("user.deleteData", "测试插入");
        sqlSession.commit();

        System.out.println(deleteUser);
        sqlSession.close();
    }


}