java进阶|MyBatis系列文章(一)基础性文章的思考增删改查

MyBatis是支持定制化SQL,存储过程以及高级映射的优秀的持久层框架。MyBatis避免了几乎所有的JDBC代码和手动参数以及获取结果集。MyBatis可以对配置和原生Map使用简单的XML或注解，将接口和Java的POJOS(Plain Old Java Objects，普通的Java对象)映射成数据库中的记录。

      <dependency>            <groupId>org.springframework.boot</groupId>            <artifactId>spring-boot-starter-web</artifactId>        </dependency>        <dependency>            <groupId>org.mybatis.spring.boot</groupId>            <artifactId>mybatis-spring-boot-starter</artifactId>            <version>2.1.2</version>        </dependency>
        <dependency>            <groupId>mysql</groupId>            <artifactId>mysql-connector-java</artifactId>            <scope>runtime</scope>        </dependency>        <dependency>            <groupId>org.projectlombok</groupId>            <artifactId>lombok</artifactId>            <optional>true</optional>        </dependency>

   <!--在单元测试里面执行时记得加上下面的这部分配置信息，自己将xml文件放到src下面了-->        <resources>            <resource>                <directory>src/main/java</directory>                <includes>                    <include>**/*.xml</include>                </includes>            </resource>        </resources>

<?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.cj.jdbc.Driver"/>                <property name="url" value="jdbc:mysql://localhost:3306/learn?serverTimezone=UTC"/>                <property name="username" value="root"/>                <property name="password" value="root"/>            </dataSource>        </environment>    </environments>    <mappers>        <mapper resource="com/wpw/mybatislearn/mapper/UserMapper.xml"/>    </mappers></configuration>

package com.wpw.mybatislearn.entity;
import lombok.AllArgsConstructor;import lombok.Builder;import lombok.Data;import lombok.NoArgsConstructor;import lombok.experimental.Accessors;
import java.io.Serializable;
/** * @author 用户基础类 */@AllArgsConstructor@NoArgsConstructor@Data@Builder@Accessors(chain = true)public class User implements Serializable {    private Integer id;    private String userName;    private Integer age;}

package com.wpw.mybatislearn.mapper;
import com.wpw.mybatislearn.entity.User;
import java.util.List;
/** * userMapper类，和db进行交互 * * @author wpw */public interface UserMapper {    /**     * 根据用户id查询用户信息     *     * @param id 用户id     * @return 用户user信息     */    User searchUser(Integer id);
    /**     * 获取用户列表     *     * @return userList     */    List<User> listUser();
    /**     * 保存用户信息     *     * @param user 用户信息     * @return 是否插入成功     */    Integer insert(User user);
    /**     * 更新用户信息     *     * @param user 用户信息     * @return 是否更新成功     */    Integer update(User user);
    /**     * 根据用户id删除用户信息     *     * @param id 用户id     * @return 是否删除成功     */    Integer delete(Integer id);}

<?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，namespace的值习惯上设置成包名+sql映射文件名，这样就能够保证namespace的值是唯一的 --><mapper namespace="com.wpw.mybatislearn.mapper.UserMapper">    <resultMap id="userResultMap" type="com.wpw.mybatislearn.entity.User">        <id property="id" column="id"/>        <result property="userName" column="user_name"/>        <result property="age" column="age"></result>    </resultMap>    <select id="searchUser" parameterType="int" resultMap="userResultMap">        select *        from user        where id = #{id};    </select>    <select id="listUser" resultMap="userResultMap">        select *        from user;    </select>    <insert id="insert" parameterType="com.wpw.mybatislearn.entity.User" useGeneratedKeys="true" keyProperty="id"            flushCache="true">        insert into user(user_name, age)        values (#{userName}, #{age})    </insert>    <update id="update" parameterType="com.wpw.mybatislearn.entity.User">        update user        set user_name=#{userName},            age=#{age}        where id = #{id}    </update>    <delete id="delete" parameterType="int" flushCache="true">        delete        from user        where id = #{id}    </delete></mapper>

package com.wpw.mybatislearn;
import com.wpw.mybatislearn.entity.User;import com.wpw.mybatislearn.mapper.UserMapper;import jdk.nashorn.internal.ir.annotations.Ignore;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 org.springframework.boot.test.context.SpringBootTest;
import java.io.IOException;import java.io.InputStream;import java.util.List;import java.util.Objects;
@SpringBootTestclass MybatisLearnApplicationTests {    private static final ThreadLocal<SqlSession> threadLocal = new ThreadLocal<>();
    @Test    void contextLoads() {    }
    private static SqlSessionFactory sqlSessionFactory = null;    private static InputStream inputStream = null;
    static {        try {            //加载mybatis连接数据库的配置信息，获取文件流            inputStream = Resources.getResourceAsStream("Configuration.xml");            //获取会话工厂信息            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);        } catch (IOException e) {            e.printStackTrace();        }    }
    /**     * 获取会话工厂     *     * @return 会话工厂     */    public SqlSessionFactory getSqlSessionFactory() {        return sqlSessionFactory;    }
    @Test    @Ignore    public void searchUser() {        //获取数据库会话sqlSession        SqlSession sqlSession = sqlSessionFactory.openSession();        try {            //获取代理类UserMapper            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);            User user = userMapper.searchUser(1);            //输出获取的信息            System.out.println("user = " + user);        } finally {            //释放当前会话连接            sqlSession.close();        }    }
    @Test    @Ignore    public void listUser() {        SqlSession sqlSession = sqlSessionFactory.openSession();        try {            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);            List<User> userList = userMapper.listUser();            userList.stream().filter(Objects::nonNull).forEachOrdered(x -> {                System.out.println(String.format("用户id:%d,用户名称:%s,年纪:%d", x.getId(), x.getUserName(), x.getAge()));            });            System.out.println("查询出的数据量为" + userList.size());        } finally {            //释放当前会话的连接            sqlSession.close();        }    }
    @Test    @Ignore    public void insert() {        SqlSession sqlSession = sqlSessionFactory.openSession();        try {            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);            Integer insert = userMapper.insert(User.builder().build().setUserName("admin").setAge(10));            System.out.println("insert = " + insert);            //默认不开启事务自动提交,可以在openSession()设置自动提交            sqlSession.commit();        } finally {            //释放连接            sqlSession.close();        }    }
    @Test    @Ignore    public void update() {        SqlSession sqlSession = sqlSessionFactory.openSession();        try {            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);            Integer update = userMapper.update(User.builder().build().setId(12).setUserName("admin-info2").setAge(12));            System.out.println("update = " + update);            //手动提交事务            sqlSession.commit();        } finally {            //释放连接            sqlSession.close();        }    }
    @Test    @Ignore    public void delete() {        SqlSession sqlSession = sqlSessionFactory.openSession();        try {            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);            Integer delete = userMapper.delete(13);            System.out.println("delete = " + delete);            sqlSession.commit();        } finally {            //释放连接            sqlSession.close();        }    }
    final String statementPrefix = "com.wpw.mybatislearn.mapper.UserMapper";
    @Test    @Ignore    public void searchByNamespace() {        SqlSession sqlSession = sqlSessionFactory.openSession();        try {            final String statement = statementPrefix + ".searchUser";            User user = sqlSession.selectOne(statement, 1);            System.out.println("user = " + user);        } finally {            //释放连接            sqlSession.close();        }    }
    @Test    @Ignore    public void searchByNamespaceAndName() {        SqlSession sqlSession = sqlSessionFactory.openSession();        try {            List<User> userList = sqlSession.selectList(statementPrefix + ".listUser");            userList.stream().filter(Objects::nonNull).forEach(x -> {                System.out.println(String.format("id:%d,userName:%s,age:%d", x.getId(), x.getUserName(), x.getAge()));            });        } finally {            //释放连接            sqlSession.close();        }    }
    @Test    @Ignore    public void insertByNamespace() {        SqlSession sqlSession = sqlSessionFactory.openSession();        try {            sqlSession.insert(statementPrefix + ".insert", User.builder().build().setUserName("statement").setAge(10));            //手动提交事务            sqlSession.commit();        } finally {            //释放连接            sqlSession.close();        }    }
    @Test    @Ignore    public void updateByNamespace() {        SqlSession sqlSession = sqlSessionFactory.openSession();        try {            int update = sqlSession.update(statementPrefix + ".update", User.builder().build()                    .setId(14).setUserName("statement-update").setAge(10));            System.out.println("update = " + update);            sqlSession.commit();        } finally {            //释放连接            sqlSession.close();        }    }
    @Test    public void deleteByNamespace() {        SqlSession sqlSession = sqlSessionFactory.openSession();        try {            int delete = sqlSession.delete(statementPrefix + ".delete", 14);            System.out.println("delete = " + delete);            sqlSession.commit();        } finally {            //释放连接            sqlSession.close();        }    }}

代码地址:https://github.com/myownmyway/myybatis-learn.git

create database learn;use learn;select database();show tables;create table if not exists user(    id        int auto_increment primary key not null comment '用户id',    user_name varchar(255)                   not null comment '用户名称',    age       int(3)                         not null comment '用户年纪');show index from user;explain select * from user;insert into user(user_name,age) values("admin",1);insert into user(user_name,age) values("admin",2);insert into user(user_name,age) values("admin",3);insert into user(user_name,age) values("admin",4);insert into user(user_name,age) values("admin",5);insert into user(user_name,age) values("admin",6);insert into user(user_name,age) values("admin",7);insert into user(user_name,age) values("admin",8);insert into user(user_name,age) values("admin",9);insert into user(user_name,age) values("admin",10);insert into user(user_name,age) values("admin",11);