接口文件和对应的mapper.xml文件

UsersMapper.java接口文件

package com.example.mapper;

import com.example.pojo.User;

import java.util.List;

/**
 * 数据访问层的接口，定义对数据库完成的CRUD的操作
 */
public interface UsersMapper {
    //获取全部用户信息
    List<User> getAll();

    //根据用户主键查取用户信息
    User getById(int id);

    //根据用户名模糊查询用户信息
    List<User> getByName(String name);

    //向用户表中增加用户信息
    int insert(User user);

    //根据id更新用户信息
    int update(User user);

    //根据id删除用户信息
    int delete(int id);
}

UsersMapper.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="com.example.mapper.UsersMapper">
    <!--
        //获取全部用户信息
        List<Users> getAll();
    -->
    <select id="getAll" resultType="user">
        select
                id, username, birthday, sex, address
        from
            users
    </select>

    <!--
        //根据用户主键查取用户信息
        User getById();
    -->
    <select id="getById" resultType="user" parameterType="int">
        select
            id, username, birthday, sex, address
        from
            users
        where
            id=#{id}
    </select>

    <!--
        //根据用户名模糊查询用户信息
        List<User> getByName(String name);
    -->
    <select id="getByName" parameterType="string" resultType="user">
        select
            id, username, birthday, sex, address
        from
            users
        where username like '%${name}%'
    </select>

   <!--
        //向用户表中增加用户信息
        int insert(User user);
   -->
    <insert id="insert" parameterType="user">
        insert into
                users(username, birthday, sex, address)
        values(#{userName}, #{birthday}, #{sex}, #{address})
    </insert>


    <!--
        //根据id更新用户信息
        int update(User user);
    -->
    <update id="update" parameterType="user">
        update
            users
        set
            username=#{userName}, birthday=#{birthday}, sex=#{sex}, address=#{address}
        where
            id=#{id}
    </update>


    <!--
        //根据id删除用户信息
        int delete(int id);
    -->
    <delete id="delete" parameterType="int">
        delete from
                users
        where
            id=#{id}
    </delete>
</mapper>

mapper.xml文件注册的优化

• 单一注册

    <!--注册mapper.xml文件-->
    <mappers>
		<mapper class="com.example.mapper.UsersMapper"/>
    </mappers>

• 批量注册

    <!--注册mapper.xml文件-->
    <mappers>
        <!-- 
			当com.example.mapper文件夹下有许多映射文件时，批量注册
 		-->
        <package name="com.example.mapper"/>
    </mappers>

测试代码

package com.example.mapper;

import com.example.pojo.User;
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.After;
import org.junit.Before;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

public class TestUsersMapper {

    //时间刷
    SimpleDateFormat date = new SimpleDateFormat("yyyy-MM-dd");

    //SqlSession对象
    SqlSession sqlSession;

    //mybatis动态代理对象
    UsersMapper usersMapper;

    //获取SqlSession
    @Before
    public void getSqlSession() throws IOException {
        //读取核心配置文件
        InputStream in = Resources.getResourceAsStream("SqlMapConfig.xml");
        //创建SqlSessionFactory对象
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
        //获取SqlSession
        sqlSession = factory.openSession();
        //获取mybatis动态代理对象
        usersMapper = sqlSession.getMapper(UsersMapper.class);
    }

    //归还SqlSession
    @After
    public void closeSession(){
        sqlSession.close();
    }

    @Test
    public void testGetAll(){
        //调用代理的功能
        List<User> users = usersMapper.getAll();
        //输出查询结果
        users.forEach(System.out::println);
    }

    @Test
    public void testGetById(){
        //调用代理功能
        User user = usersMapper.getById(2);
        //输出查询结果
        System.out.println(user);
    }

    @Test
    public void testGetByName(){
        //调用代理功能
        List<User> users = usersMapper.getByName("小");
        //输出查询结果
        users.forEach(System.out::println);
    }

    @Test
    public void testInsert() throws ParseException {
        //时间字符串
        String dateStr = "2001-03-14";
        int num = usersMapper.insert(new User("小昕", date.parse(dateStr), "女", "忻州"));
        sqlSession.commit();
        if (num == 1) {
            System.out.println("插入成功!");
        }else{
            System.out.println("插入失败!");
        }
    }

    @Test
    public void testUpdate() throws ParseException {
        String dateStr = "2002-8-23";
        int num = usersMapper.update(new User(1, "荷包蛋", date.parse(dateStr), "女", "黑河"));
        sqlSession.commit();
        if (num == 1) {
            System.out.println("更新成功!");
        }else{
            System.out.println("更新失败!");
        }
    }

    @Test
    public void testDelete(){
        int num = usersMapper.delete(4);
        sqlSession.commit();
        if (num == 1) {
            System.out.println("删除成功!");
        }else{
            System.out.println("删除失败!");
        }
    }
}

输出结果示例

1.查询所有用户信息

Checking to see if class com.example.pojo.Users matches criteria [is assignable to Object]
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
Opening JDBC Connection
Created connection 1548946718.
Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@5c530d1e]
==>  Preparing: select id, username, birthday, sex, address from users
==> Parameters: 
<==    Columns: id, username, birthday, sex, address
<==        Row: 1, 小涵, 2000-09-10, 2, 黑河市
<==        Row: 2, 小王, 2001-07-12, 1, 芜湖市
<==        Row: 3, 小张, 1999-02-22, 1, 长沙
<==        Row: 4, 小吴, 2002-11-19, 1, 成都
<==        Row: 5, 段, 2001-03-10, 1, 太原
<==        Row: 6, 范成群, 2002-01-19, 1, 鲅鱼圈
<==        Row: 7, 学委, 2001-05-13, 2, 平顶山市
<==      Total: 7
Users{id=1, userName='小涵', birthday=Sun Sep 10 00:00:00 CST 2000, sex='2', address='黑河市'}
Users{id=2, userName='小王', birthday=Thu Jul 12 00:00:00 CST 2001, sex='1', address='芜湖市'}
Users{id=3, userName='小张', birthday=Mon Feb 22 00:00:00 CST 1999, sex='1', address='长沙'}
Users{id=4, userName='小吴', birthday=Tue Nov 19 00:00:00 CST 2002, sex='1', address='成都'}
Users{id=5, userName='段', birthday=Sat Mar 10 00:00:00 CST 2001, sex='1', address='太原'}
Users{id=6, userName='范成群', birthday=Sat Jan 19 00:00:00 CST 2002, sex='1', address='鲅鱼圈'}
Users{id=7, userName='学委', birthday=Sun May 13 00:00:00 CST 2001, sex='2', address='平顶山市'}
Resetting autocommit to true on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@5c530d1e]
Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@5c530d1e]
Returned connection 1548946718 to pool.
    
Process finished with exit code 0

2.根据用户id删除用户信息

Checking to see if class com.example.pojo.User matches criteria [is assignable to Object]
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
Opening JDBC Connection
Created connection 472991420.
Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@1c3146bc]
==>  Preparing: delete from users where id=?
==> Parameters: 4(Integer)
<==    Updates: 1
Committing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@1c3146bc]
删除成功!
Resetting autocommit to true on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@1c3146bc]
Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@1c3146bc]
Returned connection 472991420 to pool.

Process finished with exit code 0

3.根据用户id更改用户信息

Checking to see if class com.example.pojo.User matches criteria [is assignable to Object]
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
Opening JDBC Connection
Created connection 1985938863.
Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@765f05af]
==>  Preparing: update users set username=?, birthday=?, sex=?, address=? where id=?
==> Parameters: 荷包蛋(String), 2002-08-23 00:00:00.0(Timestamp), 女(String), 黑河(String), 1(Integer)
<==    Updates: 1
Committing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@765f05af]
更新成功!
Resetting autocommit to true on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@765f05af]
Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@765f05af]
Returned connection 1985938863 to pool.

Process finished with exit code 0

4.添加用户信息

Checking to see if class com.example.pojo.User matches criteria [is assignable to Object]
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
Opening JDBC Connection
Created connection 1178290888.
Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@463b4ac8]
==>  Preparing: insert into users(username, birthday, sex, address) values(?, ?, ?, ?)
==> Parameters: 小昕(String), 2001-03-14 00:00:00.0(Timestamp), 女(String), 忻州(String)
<==    Updates: 1
Committing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@463b4ac8]
插入成功!
Resetting autocommit to true on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@463b4ac8]
Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@463b4ac8]
Returned connection 1178290888 to pool.

Process finished with exit code 0