开发: C++知识库 Java知识库 JavaScript Python PHP知识库人工智能区块链大数据移动开发嵌入式开发工具数据结构与算法开发测试游戏开发网络协议系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑笔记本显卡显示器固态硬盘硬盘耳机手机 iphone vivo oppo 小米华为单反装机图拉丁

-> Java知识库 -> Mybatis基于xml配置开发笔记 -> 正文阅读

[Java知识库]Mybatis基于xml配置开发笔记

作者:AccountDao_625

创建项目并导入jar包位置

创建Maven项目

在这里插入图片描述

导入jar包位置

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.rookieInstitute</groupId>
    <artifactId>mybatis_xml</artifactId>
    <version>1.0-SNAPSHOT</version>
    <packaging>jar</packaging>

    <dependencies>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.4.5</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.6</version>
        </dependency>
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.12</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.10</version>
        </dependency>
    </dependencies>
</project>

编写mysql数据表和配置文件

编写mysql数据表

user表结构
数据表结构
user表里的数据
表里数据
account表结构

account表数据

role表结构

role表数据

user_role表结构
user_role表结构描述
user_role表数据

配置文件

jdbcConfig.properties

存放数据连接的数据

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/自己建立的数据表
jdbc.username=自己写
jdbc.password=自己写

log4j.properties

# Set root category priority to INFO and its only appender to CONSOLE.
#log4j.rootCategory=INFO, CONSOLE            debug   info   warn error fatal
log4j.rootCategory=debug, CONSOLE, LOGFILE

# Set the enterprise logger category to FATAL and its only appender to CONSOLE.
log4j.logger.org.apache.axis.enterprise=FATAL, CONSOLE

# CONSOLE is set to be a ConsoleAppender using a PatternLayout.
log4j.appender.CONSOLE=org.apache.log4j.ConsoleAppender
log4j.appender.CONSOLE.layout=org.apache.log4j.PatternLayout
log4j.appender.CONSOLE.layout.ConversionPattern=%d{ISO8601} %-6r [%15.15t] %-5p %30.30c %x - %m\n

# LOGFILE is set to be a File appender using a PatternLayout.
log4j.appender.LOGFILE=org.apache.log4j.FileAppender
log4j.appender.LOGFILE.File=d:\axis.log
log4j.appender.LOGFILE.Append=true
log4j.appender.LOGFILE.layout=org.apache.log4j.PatternLayout
log4j.appender.LOGFILE.layout.ConversionPattern=%d{ISO8601} %-6r [%15.15t] %-5p %30.30c %x - %m\n

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>
    <!--配置properties
    可以在标签内部配置连接数据库的信息。也可以通过属性引用外部配置文件信息
    resource属性:
    用于指定配置文件的位置，是按照类路径的写法来写，并且必须存在于类路径下。
    url属性：
    是要求按照Url的写法来写地址
    URL:同意资源定位符。唯一标识一个资源的位置
    写法：
    http://localhost:8080/mybatisServer/demo1Servlet
    协议    主机      端口 URI
    URI:统一资源标识符。在应用中可以唯一定位一个资源-->
    <properties url="file:///D:/Java/mybatis_xml/src/main/resources/jdbcConfig.properties" ></properties>

    <!--使用typeAliases配置别名，它只能配置domain中类的别名-->
    <typeAliases>
        <!--用于指定要配置别名的包,当指定后该包下的实体类都会注册别名，并且类名就是别名，不在区分大小写-->
        <package name="com.rookieInstitute.domain"/>
    </typeAliases>
    <!--配置环境-->
    <environments default="mysql">
        <!--配置mysql环境-->
        <environment id="mysql">
            <!--配置事务-->
            <transactionManager type="JDBC"></transactionManager>
            <!--配置连接池-->
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc.driver}"/>
                <property name="url" value="${jdbc.url}"/>
                <property name="username" value="${jdbc.username}"/>
                <property name="password" value="${jdbc.password}"/>
            </dataSource>
        </environment>
    </environments>
    <!--映射文件的位置-->
    <mappers>
        <!--package用于指定dao接口所在的包，当指定完之后就不需要再写mapper以及resource或者classl-->
        <package name="com.rookieInstitute.dao"/>
    </mappers>
</configuration>

CRUD的基础操作

查询所有

UserDao中添加：

	/**
     * @Description: 查询所有
     * @Param: []
     * @return: java.util.List<com.rookieInstitute.domain.User>
     * @Author: Mr.King
     * @Date: 2021/9/18
     */
    List<User> findAll();

UserDao.xml中添加：

	<!--查询所有-->
    <select id="findAll" resultMap="userMap" >
        select * from USER ;
    </select>

测试代码：

	/**
     * @Description: 测试查询所有
     * @Param: []
     * @return: void
     * @Author: Mr.King
     * @Date: 2021/9/18
     */
    @Test
    public void testFindAll(){
        //执行查询所有方法
        List<User> users = userDao.findAll();
        for (User user : users) {
            System.out.println(user);
        }
    }

结果展示:

2021-09-18 16:01:30,821 188    [           main] DEBUG ansaction.jdbc.JdbcTransaction  - Opening JDBC Connection
2021-09-18 16:01:31,008 375    [           main] DEBUG source.pooled.PooledDataSource  - Created connection 643290333.
2021-09-18 16:01:31,008 375    [           main] DEBUG ansaction.jdbc.JdbcTransaction  - Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@2657d4dd]
2021-09-18 16:01:31,008 375    [           main] DEBUG eInstitute.dao.UserDao.findAll  - ==>  Preparing: select * from USER ; 
2021-09-18 16:01:31,055 422    [           main] DEBUG eInstitute.dao.UserDao.findAll  - ==> Parameters: 
2021-09-18 16:01:31,086 453    [           main] DEBUG eInstitute.dao.UserDao.findAll  - <==      Total: 9
User{username='Tonny', userId=41, userAddress='USA', userSex='男', userBirthday=Tue Feb 27 17:47:08 CST 2018}
User{username='小二王', userId=42, userAddress='China', userSex='女', userBirthday=Fri Mar 02 15:09:37 CST 2018}
User{username='小二王', userId=43, userAddress='China', userSex='女', userBirthday=Sun Mar 04 11:34:34 CST 2018}
User{username='金大神', userId=45, userAddress='China', userSex='男', userBirthday=Sun Mar 04 12:04:06 CST 2018}
User{username='老王', userId=46, userAddress='China', userSex='男', userBirthday=Wed Mar 07 17:37:26 CST 2018}
User{username='海绵宝宝', userId=48, userAddress='China', userSex='女', userBirthday=Thu Mar 08 11:44:00 CST 2018}
User{username='Anny', userId=59, userAddress='USA', userSex='女', userBirthday=Tue Aug 31 19:40:44 CST 2021}
User{username='Tom', userId=62, userAddress='USA', userSex='男', userBirthday=Tue Aug 31 20:16:32 CST 2021}
User{username='Ben', userId=65, userAddress='USA', userSex='男', userBirthday=Fri Sep 03 18:51:20 CST 2021}
2021-09-18 16:01:31,086 453    [           main] DEBUG ansaction.jdbc.JdbcTransaction  - Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@2657d4dd]
2021-09-18 16:01:31,086 453    [           main] DEBUG ansaction.jdbc.JdbcTransaction  - Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@2657d4dd]
2021-09-18 16:01:31,086 453    [           main] DEBUG source.pooled.PooledDataSource  - Returned connection 643290333 to pool.

根据id查询用户

UserDao中添加：

	/**
     * @Description: 根据id查询用户
     * @Param: [id]
     * @return: com.rookieInstitute.domain.User
     * @Author: Mr.King
     * @Date: 2021/9/18
     */
    User findUserById(Integer id);

UserDao.xml中添加：

	<!--根据id查询用户-->
    <select id="findUserById" resultMap="userMap" parameterType="int">
        select * from user where id=#{id};
    </select>

测试代码：

	/**
     * @Description: 测试根据id查询用户
     * @Param: []
     * @return: void
     * @Author: Mr.King
     * @Date: 2021/9/18
     */
    @Test
    public void testUserById(){
        //执行根据id查询用户方法
        User user = userDao.findUserById(45);
        System.out.println(user);
    }

结果展示:

2021-09-18 16:03:25,833 172    [           main] DEBUG ansaction.jdbc.JdbcTransaction  - Opening JDBC Connection
2021-09-18 16:03:26,005 344    [           main] DEBUG source.pooled.PooledDataSource  - Created connection 1899600175.
2021-09-18 16:03:26,005 344    [           main] DEBUG ansaction.jdbc.JdbcTransaction  - Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@7139992f]
2021-09-18 16:03:26,005 344    [           main] DEBUG itute.dao.UserDao.findUserById  - ==>  Preparing: select * from user where id=?; 
2021-09-18 16:03:26,036 375    [           main] DEBUG itute.dao.UserDao.findUserById  - ==> Parameters: 45(Integer)
2021-09-18 16:03:26,052 391    [           main] DEBUG itute.dao.UserDao.findUserById  - <==      Total: 1
User{username='金大神', userId=45, userAddress='China', userSex='男', userBirthday=Sun Mar 04 12:04:06 CST 2018}
2021-09-18 16:03:26,052 391    [           main] DEBUG ansaction.jdbc.JdbcTransaction  - Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@7139992f]
2021-09-18 16:03:26,052 391    [           main] DEBUG ansaction.jdbc.JdbcTransaction  - Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@7139992f]
2021-09-18 16:03:26,052 391    [           main] DEBUG source.pooled.PooledDataSource  - Returned connection 1899600175 to pool.

保存用户

UserDao中添加：

	/**
     * @Description: 保存用户
     * @Param: [user]
     * @return: void
     * @Author: Mr.King
     * @Date: 2021/9/18
     */
    void saveUser(User user);

UserDao.xml中添加：

	<!--保存用户-->
    <insert id="saveUser" parameterType="com.rookieInstitute.domain.User">
        insert into user(username,sex,address,birthday) values(#{username},#{userSex},#{userAddress},#{userBirthday});
    </insert>

测试代码：

	/**
     * @Description: 测试保存用户
     * @Param: []
     * @return: void
     * @Author: Mr.King
     * @Date: 2021/9/18
     */
    @Test
    public void testSaveUser(){
        //执行保存用户方法
        User user=new User();
        user.setUsername("saveOne");
        user.setUserSex("男");
        user.setUserAddress("jialidun");
        user.setUserBirthday(new Date());
        userDao.saveUser(user);

        testFindAll();
    }

结果展示:

2021-09-18 16:04:52,111 172    [           main] DEBUG ansaction.jdbc.JdbcTransaction  - Opening JDBC Connection
2021-09-18 16:04:52,267 328    [           main] DEBUG source.pooled.PooledDataSource  - Created connection 554348863.
2021-09-18 16:04:52,267 328    [           main] DEBUG ansaction.jdbc.JdbcTransaction  - Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@210ab13f]
2021-09-18 16:04:52,267 328    [           main] DEBUG Institute.dao.UserDao.saveUser  - ==>  Preparing: insert into user(username,sex,address,birthday) values(?,?,?,?); 
2021-09-18 16:04:52,298 359    [           main] DEBUG Institute.dao.UserDao.saveUser  - ==> Parameters: saveOne(String), 男(String), jialidun(String), 2021-09-18 16:04:52.095(Timestamp)
2021-09-18 16:04:52,298 359    [           main] DEBUG Institute.dao.UserDao.saveUser  - <==    Updates: 1
2021-09-18 16:04:52,298 359    [           main] DEBUG eInstitute.dao.UserDao.findAll  - ==>  Preparing: select * from USER ; 
2021-09-18 16:04:52,298 359    [           main] DEBUG eInstitute.dao.UserDao.findAll  - ==> Parameters: 
2021-09-18 16:04:52,314 375    [           main] DEBUG eInstitute.dao.UserDao.findAll  - <==      Total: 10
User{username='Tonny', userId=41, userAddress='USA', userSex='男', userBirthday=Tue Feb 27 17:47:08 CST 2018}
User{username='小二王', userId=42, userAddress='China', userSex='女', userBirthday=Fri Mar 02 15:09:37 CST 2018}
User{username='小二王', userId=43, userAddress='China', userSex='女', userBirthday=Sun Mar 04 11:34:34 CST 2018}
User{username='金大神', userId=45, userAddress='China', userSex='男', userBirthday=Sun Mar 04 12:04:06 CST 2018}
User{username='老王', userId=46, userAddress='China', userSex='男', userBirthday=Wed Mar 07 17:37:26 CST 2018}
User{username='海绵宝宝', userId=48, userAddress='China', userSex='女', userBirthday=Thu Mar 08 11:44:00 CST 2018}
User{username='Anny', userId=59, userAddress='USA', userSex='女', userBirthday=Tue Aug 31 19:40:44 CST 2021}
User{username='Tom', userId=62, userAddress='USA', userSex='男', userBirthday=Tue Aug 31 20:16:32 CST 2021}
User{username='Ben', userId=65, userAddress='USA', userSex='男', userBirthday=Fri Sep 03 18:51:20 CST 2021}
User{username='saveOne', userId=67, userAddress='jialidun', userSex='男', userBirthday=Sat Sep 18 16:04:52 CST 2021}
2021-09-18 16:04:52,314 375    [           main] DEBUG ansaction.jdbc.JdbcTransaction  - Committing JDBC Connection [com.mysql.jdbc.JDBC4Connection@210ab13f]
2021-09-18 16:04:52,330 391    [           main] DEBUG ansaction.jdbc.JdbcTransaction  - Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@210ab13f]
2021-09-18 16:04:52,330 391    [           main] DEBUG ansaction.jdbc.JdbcTransaction  - Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@210ab13f]
2021-09-18 16:04:52,330 391    [           main] DEBUG source.pooled.PooledDataSource  - Returned connection 554348863 to pool.

更新用户信息

UserDao中添加：

	/**
     * @Description: 更新用户信息
     * @Param: [user]
     * @return: void
     * @Author: Mr.King
     * @Date: 2021/9/18
     */
    void updateUser(User user);

UserDao.xml中添加：

	<!--更新用户信息-->
    <update id="updateUser" parameterType="com.rookieInstitute.domain.User">
        update user set username=#{username},address=#{userAddress},sex=#{userSex},birthday=#{userBirthday} where id=#{userId};
    </update>

测试代码：

 	/**
     * @Description: 测试更新用户信息
     * @Param: []
     * @return: void
     * @Author: Mr.King
     * @Date: 2021/9/18
     */
    @Test
    public void testUpdateUser(){
        //执行更新用户信息方法
        User user=new User();
        user.setUserId(66);
        user.setUsername("saveOne1");
        user.setUserSex("男");
        user.setUserAddress("jialidun");
        user.setUserBirthday(new Date());
        userDao.updateUser(user);

        testFindAll();
    }

结果展示:

2021-09-18 16:08:54,202 172    [           main] DEBUG ansaction.jdbc.JdbcTransaction  - Opening JDBC Connection
2021-09-18 16:08:54,374 344    [           main] DEBUG source.pooled.PooledDataSource  - Created connection 554348863.
2021-09-18 16:08:54,374 344    [           main] DEBUG ansaction.jdbc.JdbcTransaction  - Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@210ab13f]
2021-09-18 16:08:54,374 344    [           main] DEBUG stitute.dao.UserDao.updateUser  - ==>  Preparing: update user set username=?,address=?,sex=?,birthday=? where id=?; 
2021-09-18 16:08:54,421 391    [           main] DEBUG stitute.dao.UserDao.updateUser  - ==> Parameters: saveOne1(String), jialidun(String), 男(String), 2021-09-18 16:08:54.202(Timestamp), 67(Integer)
2021-09-18 16:08:54,421 391    [           main] DEBUG stitute.dao.UserDao.updateUser  - <==    Updates: 1
2021-09-18 16:08:54,421 391    [           main] DEBUG eInstitute.dao.UserDao.findAll  - ==>  Preparing: select * from USER ; 
2021-09-18 16:08:54,421 391    [           main] DEBUG eInstitute.dao.UserDao.findAll  - ==> Parameters: 
2021-09-18 16:08:54,437 407    [           main] DEBUG eInstitute.dao.UserDao.findAll  - <==      Total: 10
User{username='Tonny', userId=41, userAddress='USA', userSex='男', userBirthday=Tue Feb 27 17:47:08 CST 2018}
User{username='小二王', userId=42, userAddress='China', userSex='女', userBirthday=Fri Mar 02 15:09:37 CST 2018}
User{username='小二王', userId=43, userAddress='China', userSex='女', userBirthday=Sun Mar 04 11:34:34 CST 2018}
User{username='金大神', userId=45, userAddress='China', userSex='男', userBirthday=Sun Mar 04 12:04:06 CST 2018}
User{username='老王', userId=46, userAddress='China', userSex='男', userBirthday=Wed Mar 07 17:37:26 CST 2018}
User{username='海绵宝宝', userId=48, userAddress='China', userSex='女', userBirthday=Thu Mar 08 11:44:00 CST 2018}
User{username='Anny', userId=59, userAddress='USA', userSex='女', userBirthday=Tue Aug 31 19:40:44 CST 2021}
User{username='Tom', userId=62, userAddress='USA', userSex='男', userBirthday=Tue Aug 31 20:16:32 CST 2021}
User{username='Ben', userId=65, userAddress='USA', userSex='男', userBirthday=Fri Sep 03 18:51:20 CST 2021}
User{username='saveOne1', userId=67, userAddress='jialidun', userSex='男', userBirthday=Sat Sep 18 16:08:54 CST 2021}
2021-09-18 16:08:54,452 422    [           main] DEBUG ansaction.jdbc.JdbcTransaction  - Committing JDBC Connection [com.mysql.jdbc.JDBC4Connection@210ab13f]
2021-09-18 16:08:54,452 422    [           main] DEBUG ansaction.jdbc.JdbcTransaction  - Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@210ab13f]
2021-09-18 16:08:54,452 422    [           main] DEBUG ansaction.jdbc.JdbcTransaction  - Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@210ab13f]
2021-09-18 16:08:54,452 422    [           main] DEBUG source.pooled.PooledDataSource  - Returned connection 554348863 to pool.

根据id删除用户

UserDao中添加：

	/**
     * @Description: 根据id删除用户
     * @Param: [id]
     * @return: void
     * @Author: Mr.King
     * @Date: 2021/9/18
     */
    void deleteUserById(Integer id);

UserDao.xml中添加：

	<!--根据id删除用户-->
    <delete id="deleteUserById" parameterType="int">
        delete from user where id=#{id};
    </delete>

测试代码：

	/**
     * @Description: 测试通过id删除用户
     * @Param: []
     * @return: void
     * @Author: Mr.King
     * @Date: 2021/9/18
     */
    @Test
    public void testDeleteUserById(){
        //执行通过id删除用户方法
        userDao.deleteUserById(66);

        testFindAll();
    }

结果展示:

2021-09-18 16:09:58,051 177    [           main] DEBUG ansaction.jdbc.JdbcTransaction  - Opening JDBC Connection
2021-09-18 16:09:58,207 333    [           main] DEBUG source.pooled.PooledDataSource  - Created connection 554348863.
2021-09-18 16:09:58,207 333    [           main] DEBUG ansaction.jdbc.JdbcTransaction  - Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@210ab13f]
2021-09-18 16:09:58,223 349    [           main] DEBUG ute.dao.UserDao.deleteUserById  - ==>  Preparing: delete from user where id=?; 
2021-09-18 16:09:58,238 364    [           main] DEBUG ute.dao.UserDao.deleteUserById  - ==> Parameters: 66(Integer)
2021-09-18 16:09:58,238 364    [           main] DEBUG ute.dao.UserDao.deleteUserById  - <==    Updates: 0
2021-09-18 16:09:58,238 364    [           main] DEBUG eInstitute.dao.UserDao.findAll  - ==>  Preparing: select * from USER ; 
2021-09-18 16:09:58,238 364    [           main] DEBUG eInstitute.dao.UserDao.findAll  - ==> Parameters: 
2021-09-18 16:09:58,254 380    [           main] DEBUG eInstitute.dao.UserDao.findAll  - <==      Total: 10
User{username='Tonny', userId=41, userAddress='USA', userSex='男', userBirthday=Tue Feb 27 17:47:08 CST 2018}
User{username='小二王', userId=42, userAddress='China', userSex='女', userBirthday=Fri Mar 02 15:09:37 CST 2018}
User{username='小二王', userId=43, userAddress='China', userSex='女', userBirthday=Sun Mar 04 11:34:34 CST 2018}
User{username='金大神', userId=45, userAddress='China', userSex='男', userBirthday=Sun Mar 04 12:04:06 CST 2018}
User{username='老王', userId=46, userAddress='China', userSex='男', userBirthday=Wed Mar 07 17:37:26 CST 2018}
User{username='海绵宝宝', userId=48, userAddress='China', userSex='女', userBirthday=Thu Mar 08 11:44:00 CST 2018}
User{username='Anny', userId=59, userAddress='USA', userSex='女', userBirthday=Tue Aug 31 19:40:44 CST 2021}
User{username='Tom', userId=62, userAddress='USA', userSex='男', userBirthday=Tue Aug 31 20:16:32 CST 2021}
User{username='Ben', userId=65, userAddress='USA', userSex='男', userBirthday=Fri Sep 03 18:51:20 CST 2021}
User{username='saveOne1', userId=67, userAddress='jialidun', userSex='男', userBirthday=Sat Sep 18 16:08:54 CST 2021}
2021-09-18 16:09:58,254 380    [           main] DEBUG ansaction.jdbc.JdbcTransaction  - Committing JDBC Connection [com.mysql.jdbc.JDBC4Connection@210ab13f]
2021-09-18 16:09:58,254 380    [           main] DEBUG ansaction.jdbc.JdbcTransaction  - Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@210ab13f]
2021-09-18 16:09:58,254 380    [           main] DEBUG ansaction.jdbc.JdbcTransaction  - Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@210ab13f]
2021-09-18 16:09:58,254 380    [           main] DEBUG source.pooled.PooledDataSource  - Returned connection 554348863 to pool.

模糊查询

UserDao中添加：

	/**
     * @Description: 模糊查询
     * @Param: [username]
     * @return: java.util.List<com.rookieInstitute.domain.User>
     * @Author: Mr.King
     * @Date: 2021/9/18
     */
    List<User> findByUsername(String username);

UserDao.xml中添加：

	<!--模糊查询-->
    <select id="findByUsername" resultMap="userMap" parameterType="String">
        select * from user where username like #{username};
    </select>

测试代码：

	 /**
     * @Description: 测试模糊查询
     * @Param: []
     * @return: void
     * @Author: Mr.King
     * @Date: 2021/9/18
     */
    @Test
    public void testFindByName(){
        //执行模糊查询方法
        List<User> users = userDao.findByUsername("%王%");

        for (User user : users) {
            System.out.println(user);
        }
    }

结果展示:

2021-09-18 16:11:23,645 187    [           main] DEBUG ansaction.jdbc.JdbcTransaction  - Opening JDBC Connection
2021-09-18 16:11:23,802 344    [           main] DEBUG source.pooled.PooledDataSource  - Created connection 1899600175.
2021-09-18 16:11:23,802 344    [           main] DEBUG ansaction.jdbc.JdbcTransaction  - Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@7139992f]
2021-09-18 16:11:23,802 344    [           main] DEBUG ute.dao.UserDao.findByUsername  - ==>  Preparing: select * from user where username like ?; 
2021-09-18 16:11:23,833 375    [           main] DEBUG ute.dao.UserDao.findByUsername  - ==> Parameters: %王%(String)
2021-09-18 16:11:23,849 391    [           main] DEBUG ute.dao.UserDao.findByUsername  - <==      Total: 3
User{username='小二王', userId=42, userAddress='China', userSex='女', userBirthday=Fri Mar 02 15:09:37 CST 2018}
User{username='小二王', userId=43, userAddress='China', userSex='女', userBirthday=Sun Mar 04 11:34:34 CST 2018}
User{username='老王', userId=46, userAddress='China', userSex='男', userBirthday=Wed Mar 07 17:37:26 CST 2018}
2021-09-18 16:11:23,849 391    [           main] DEBUG ansaction.jdbc.JdbcTransaction  - Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@7139992f]
2021-09-18 16:11:23,849 391    [           main] DEBUG ansaction.jdbc.JdbcTransaction  - Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@7139992f]
2021-09-18 16:11:23,849 391    [           main] DEBUG source.pooled.PooledDataSource  - Returned connection 1899600175 to pool.

查询用户数目

UserDao中添加：

	/**
     * @Description: 查询用户数量
     * @Param: []
     * @return: int
     * @Author: Mr.King
     * @Date: 2021/9/18
     */
    int findTotal();

UserDao.xml中添加：

	<!--查询用户数目-->
    <select id="findTotal" resultType="int">
        select count(*) from user;
    </select>

测试代码：

	/**
     * @Description: 测试查询用户数目
     * @Param: []
     * @return: void
     * @Author: Mr.King
     * @Date: 2021/9/18
     */
    @Test
    public void testFindTotal(){
        //执行查询用户数目方法
        int num=userDao.findTotal();

        System.out.println("用户数目为："+num);
    }

结果展示:

2021-09-18 16:12:34,943 188    [           main] DEBUG ansaction.jdbc.JdbcTransaction  - Opening JDBC Connection
2021-09-18 16:12:35,130 375    [           main] DEBUG source.pooled.PooledDataSource  - Created connection 643290333.
2021-09-18 16:12:35,130 375    [           main] DEBUG ansaction.jdbc.JdbcTransaction  - Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@2657d4dd]
2021-09-18 16:12:35,130 375    [           main] DEBUG nstitute.dao.UserDao.findTotal  - ==>  Preparing: select count(*) from user; 
2021-09-18 16:12:35,161 406    [           main] DEBUG nstitute.dao.UserDao.findTotal  - ==> Parameters: 
2021-09-18 16:12:35,193 438    [           main] DEBUG nstitute.dao.UserDao.findTotal  - <==      Total: 1
用户数目为：10
2021-09-18 16:12:35,193 438    [           main] DEBUG ansaction.jdbc.JdbcTransaction  - Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@2657d4dd]
2021-09-18 16:12:35,193 438    [           main] DEBUG ansaction.jdbc.JdbcTransaction  - Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@2657d4dd]
2021-09-18 16:12:35,193 438    [           main] DEBUG source.pooled.PooledDataSource  - Returned connection 643290333 to pool.

复杂关系操作

一对一

创建Account类，一个Account对象只可以对应一个User对象

Account

package com.rookieInstitute.domain;

import java.io.Serializable;

/**
 * @program: mybatis_xml
 * @description:
 * @author: Mr.King
 * @create: 2021-09-18 17:25
 **/

public class Account implements Serializable {
    private Integer id;
    private Integer uid;
    private Double money;

    //一对一,一个账户只可以赋予一个用户
    private User user;

    public User getUser() {
        return user;
    }

    public void setUser(User user) {
        this.user = user;
    }

    public Integer getId() {
        return id;
    }

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

    public Integer getUid() {
        return uid;
    }

    public void setUid(Integer uid) {
        this.uid = uid;
    }

    public Double getMoney() {
        return money;
    }

    public void setMoney(Double money) {
        this.money = money;
    }

    @Override
    public String toString() {
        return "Account{" +
                "id=" + id +
                ", uid=" + uid +
                ", money=" + money +
                '}';
    }
}

AccountDao

package com.rookieInstitute.dao;

import com.rookieInstitute.domain.Account;

import java.util.List;

/**
 * @program: mybatis_xml
 * @description:
 * @author: Mr.King
 * @create: 2021-09-18 17:32
 **/

public interface AccountDao {
    /**
     * @Description: 一对一查询
     * @Param: []
     * @return: java.util.List<com.rookieInstitute.domain.Account>
     * @Author: Mr.King
     * @Date: 2021/9/18
     */
    public List<Account> findAllAccount();
}

AccountDao.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.rookieInstitute.dao.AccountDao">
    <!--定义封装account和user的result-->
    <resultMap id="accountUserMap" type="account">
        <id property="id" column="aid"></id>
        <result property="uid" column="uid"></result>
        <result property="money" column="money"></result>
        <!--一对一的关系映射，配置封装user的内容-->
        <association property="user" column="uid" javaType="user">
            <id property="userId" column="id"></id>
            <result property="username" column="username"></result>
            <result property="userAddress" column="address"></result>
            <result property="userSex" column="sex"></result>
            <result property="userBirthday" column="birthday"></result>
        </association>
    </resultMap>

    <!--一对一查询-->
    <select id="findAllAccount" resultMap="accountUserMap" >
        select u.*,a.aid as aid,a.uid,a.money from account a,user u where u.id=a.uid;
    </select>
</mapper>

测试代码：

    /**
     * @Description: 测试一对一查询
     * @Param: []
     * @return: void
     * @Author: Mr.King
     * @Date: 2021/9/18
     */
    @Test
    public void testFindAllAccount(){
        //执行一对一查询方法
        List<Account> accounts=accountDao.findAllAccount();
        for (Account account : accounts) {
            System.out.println(account);
            System.out.println(account.getUser());
        }
    }

测试结果:

2021-09-18 17:54:05,403 203    [           main] DEBUG ansaction.jdbc.JdbcTransaction  - Opening JDBC Connection
2021-09-18 17:54:05,575 375    [           main] DEBUG source.pooled.PooledDataSource  - Created connection 1552326679.
2021-09-18 17:54:05,575 375    [           main] DEBUG ansaction.jdbc.JdbcTransaction  - Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@5c86a017]
2021-09-18 17:54:05,575 375    [           main] DEBUG .dao.AccountDao.findAllAccount  - ==>  Preparing: select u.*,a.aid as aid,a.uid,a.money from account a,user u where u.id=a.uid; 
2021-09-18 17:54:05,590 390    [           main] DEBUG .dao.AccountDao.findAllAccount  - ==> Parameters: 
2021-09-18 17:54:05,621 421    [           main] DEBUG .dao.AccountDao.findAllAccount  - <==      Total: 3
Account{id=1, uid=41, money=1000.0}
User{username='Tonny', userId=41, userAddress='USA', userSex='男', userBirthday=Tue Feb 27 17:47:08 CST 2018}
Account{id=2, uid=45, money=1000.0}
User{username='金大神', userId=45, userAddress='China', userSex='男', userBirthday=Sun Mar 04 12:04:06 CST 2018}
Account{id=3, uid=41, money=2000.0}
User{username='Tonny', userId=41, userAddress='USA', userSex='男', userBirthday=Tue Feb 27 17:47:08 CST 2018}
2021-09-18 17:54:05,621 421    [           main] DEBUG ansaction.jdbc.JdbcTransaction  - Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@5c86a017]
2021-09-18 17:54:05,621 421    [           main] DEBUG ansaction.jdbc.JdbcTransaction  - Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@5c86a017]
2021-09-18 17:54:05,621 421    [           main] DEBUG source.pooled.PooledDataSource  - Returned connection 1552326679 to pool.

一对多

创建Role类，一个Role对象可以对应多个User对象

Role

package com.rookieInstitute.domain;

import java.util.List;

/**
 * @program: mybatis_xml
 * @description:
 * @author: Mr.King
 * @create: 2021-09-18 16:14
 **/

public class Role {
    private int id;
    private String roleName;
    private String roleDesc;

    //一对多的关系映射，一个角色可以赋予多个用户
    private List<User> users;

    public List<User> getUsers() {
        return users;
    }

    public void setUsers(List<User> users) {
        this.users = users;
    }

    public int getId() {
        return id;
    }

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

    public String getRoleName() {
        return roleName;
    }

    public void setRoleName(String roleName) {
        this.roleName = roleName;
    }

    public String getRoleDesc() {
        return roleDesc;
    }

    public void setRoleDesc(String roleDesc) {
        this.roleDesc = roleDesc;
    }

    @Override
    public String toString() {
        return "Role{" +
                "id=" + id +
                ", roleName='" + roleName + '\'' +
                ", roleDesc='" + roleDesc + '\'' +
                '}';
    }
}

RoleDao

package com.rookieInstitute.dao;

import com.rookieInstitute.domain.Role;

import java.util.List;

/**
 * @program: mybatis_xml
 * @description:
 * @author: Mr.King
 * @create: 2021-09-18 17:23
 **/

public interface RoleDao {
    /**
     * @Description: 查询所有同时包含用户名和地址
     * @Param: []
     * @return: java.util.List<com.rookieInstitute.domain.Role>
     * @Author: Mr.King
     * @Date: 2021/9/18
     */
    List<Role> findAll();
}

RoleDao.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.rookieInstitute.dao.RoleDao">
    <!--定义role表的resultMap-->
    <resultMap id="roleMap" type="role">
        <id property="id" column="rid"></id>
        <result property="roleName" column="role_name"></result>
        <result property="roleDesc" column="role_desc"></result>

        <collection property="users" ofType="user">
            <id property="userId" column="id"></id>
            <result property="username" column="username"></result>
            <result property="userSex" column="sex"></result>
            <result property="userAddress" column="address"></result>
            <result property="userBirthday" column="birthday"></result>
        </collection>
    </resultMap>

    <!--查询所有-->
    <select id="findAll" resultMap="roleMap">
        SELECT u.*,r.id AS rid,r.role_name,r.role_desc FROM role r
         LEFT OUTER JOIN user_role ur ON r.id=ur.rid
         LEFT OUTER JOIN USER u ON u.id=ur.uid;
    </select>

</mapper>

测试代码：

 /**
     * @Description: 查询所有
     * @Param: []
     * @return: void
     * @Author: Mr.King
     * @Date: 2021/9/18
     */
    @Test
    public void testFindAllAccountUser(){
        List<Role> roles=roleDao.findAll();
        for (Role role : roles) {
            System.out.println("-------每个用户的信息---------");
            System.out.println(role);
            System.out.println(role.getUsers());
        }
    }

测试结果：

2021-09-18 17:59:30,667 203    [           main] DEBUG ansaction.jdbc.JdbcTransaction  - Opening JDBC Connection
2021-09-18 17:59:30,820 356    [           main] DEBUG source.pooled.PooledDataSource  - Created connection 1552326679.
2021-09-18 17:59:30,820 356    [           main] DEBUG ansaction.jdbc.JdbcTransaction  - Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@5c86a017]
2021-09-18 17:59:30,836 372    [           main] DEBUG eInstitute.dao.RoleDao.findAll  - ==>  Preparing: SELECT u.*,r.id AS rid,r.role_name,r.role_desc FROM role r LEFT OUTER JOIN user_role ur ON r.id=ur.rid LEFT OUTER JOIN USER u ON u.id=ur.uid; 
2021-09-18 17:59:30,851 387    [           main] DEBUG eInstitute.dao.RoleDao.findAll  - ==> Parameters: 
2021-09-18 17:59:30,873 409    [           main] DEBUG eInstitute.dao.RoleDao.findAll  - <==      Total: 4
-------每个用户的信息---------
Role{id=1, roleName='院长', roleDesc='管理整个学院'}
[User{username='Tonny', userId=41, userAddress='USA', userSex='男', userBirthday=Tue Feb 27 17:47:08 CST 2018}, User{username='金大神', userId=45, userAddress='China', userSex='男', userBirthday=Sun Mar 04 12:04:06 CST 2018}]
-------每个用户的信息---------
Role{id=2, roleName='总裁', roleDesc='管理整个公司'}
[User{username='Tonny', userId=41, userAddress='USA', userSex='男', userBirthday=Tue Feb 27 17:47:08 CST 2018}]
-------每个用户的信息---------
Role{id=3, roleName='校长', roleDesc='管理整个学校'}
[]
2021-09-18 17:59:30,889 425    [           main] DEBUG ansaction.jdbc.JdbcTransaction  - Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@5c86a017]
2021-09-18 17:59:30,890 426    [           main] DEBUG ansaction.jdbc.JdbcTransaction  - Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@5c86a017]
2021-09-18 17:59:30,890 426    [           main] DEBUG source.pooled.PooledDataSource  - Returned connection 1552326679 to pool.