SpringBoot整合Mybatis-XML增删改查

零、知识介绍

xml方式能够分离SQL语句，能够进行统一管理，xml方式易懂易学。
缺点：节点麻烦，不能进行debug，肉眼不好看到错误，只能进行测试。如果是新手，常常会因为环境问题，而无法实现代码功能。

一、依赖准备

  1. <parent>
  2.   <groupId>org.springframework.boot</groupId>
  3.   <artifactId>spring-boot-starter-parent</artifactId>
  4.   <version>2.0.1.RELEASE</version>
  5.   <relativePath/> <!-- lookup parent from repository -->
  6. </parent>
  8. <properties>
  9.   <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
  10.   <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
  11.   <java.version>1.8</java.version>
  12. </properties>
  14. <dependencies>
  15.   <dependency>
  16.     <groupId>org.springframework.boot</groupId>
  17.     <artifactId>spring-boot-starter-web</artifactId>
  18.   </dependency>
  20.   <!--SpingBoot集成junit测试的起步依赖-->
  21.   <dependency>
  22.     <groupId>org.springframework.boot</groupId>
  23.     <artifactId>spring-boot-starter-test</artifactId>
  24.     <scope>test</scope>
  25.   </dependency>
  27.   <!--mybatis起步依赖-->
  28.   <dependency>
  29.     <groupId>org.mybatis.spring.boot</groupId>
  30.     <artifactId>mybatis-spring-boot-starter</artifactId>
  31.     <version>1.1.1</version>
  32.   </dependency>
  34.   <!-- MySQL连接驱动 -->
  35.   <dependency>
  36.     <groupId>mysql</groupId>
  37.     <artifactId>mysql-connector-java</artifactId>
  38.   </dependency>
  40. </dependencies>

二、配置准备

application.yml

  1. spring:
  2.   datasource:
  3.     driver-class-name: com.mysql.jdbc.Driver
  4.     url: jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf8&useSSL=false
  5.     username: root
  6.     password: 123
  7.   mvc:
  8.     view:
  9.       suffix: .jsp
  10.       prefix: /
  11.   resources:
  12.     static-locations: classpath:templates
  13. mybatis:
  14.   type-aliases-package: com.hikktn.domain
  15.   mapper-locations: classpath:mapper/*Mapper.xml
  16.   configuration:
  17.     map-underscore-to-camel-case: true #开启自动驼峰命名规则（camel case）映射
  18.     lazy-loading-enabled: true #开启延时加载开关
  19.     aggressive-lazy-loading: false #将积极加载改为消极加载（即按需加载）,默认值就是false
  20.     lazy-load-trigger-methods: "" #阻挡不相干的操作触发，实现懒加载
  21.     cache-enabled: true #打开全局缓存开关（二级环境），默认值就是true
  22. logging:
  23.   level:
  24.    com.hikktn: debug
  25. server:
  26.   port: 8080
  27.   servlet:
  28.     context-path: /

log4j.properties

  1. #---- global logging configuration
  2. #---- level: FATAL,ERROR,WARN,INFO,DEBUG
  3. #---- appender: console, file, mail
  4. ### set log levels ###
  5. log4j.rootLogger=INFO,console
  6. ### 输出到控制台 ###
  7. log4j.appender.stdout.encoding=UTF-8
  8. log4j.appender.console=org.apache.log4j.ConsoleAppender
  9. log4j.appender.console.Target=System.out
  10. log4j.appender.console.layout=org.apache.log4j.PatternLayout
  11. log4j.appender.console.layout.ConversionPattern=%d{yyyy-MM-dd HH\:mm\:ss} %5p %c{1}:%L - %m%n
  12. ### 输出到日志文件 ###
  13. #log4j.appender.file=org.apache.log4j.DailyRollingFileAppender
  14. #log4j.appender.file.File=${webapp.root}/WEB-INF/logs/platform.log
  15. #log4j.appender.file.DatePattern=_yyyyMMdd'.log'
  16. #log4j.appender.file.Append = true
  17. #log4j.appender.file.Threshold = INFO
  18. #log4j.appender.file.layout=org.apache.log4j.PatternLayout
  19. #log4j.appender.file.layout.ConversionPattern=%-d{yyyy-MM-dd HH\:mm\:ss}  [ %t\:%r ] - [ %p ]  %m%n
  20. ### 打印SQL ###
  21. #log4j.logger.com.ibatis=DEBUG
  22. #log4j.logger.com.ibatis.common.jdbc.SimpleDataSource=DEBUG
  23. #log4j.logger.com.ibatis.common.jdbc.ScriptRunner=DEBUG
  24. #log4j.logger.com.ibatis.sqlmap.engine.impl.SqlMapClientDelegate=DEBUG
  25. log4j.logger.java.sql.Connection=DEBUG
  26. log4j.logger.java.sql.Statement=DEBUG
  27. log4j.logger.java.sql.PreparedStatement=DEBUG
  28. #log4j.logger.java.sql.ResultSet=DEBUG
  29. #配置logger扫描的包路径  这样才会打印sql
  30. log4j.logger.com.hikktn.mapper=DEBUG

三、数据准备

  1. SET NAMES utf8mb4;
  2. SET FOREIGN_KEY_CHECKS = 0;
  4. -- ----------------------------
  5. -- Table structure for user
  6. -- ----------------------------
  7. DROP TABLE IF EXISTS `user`;
  8. CREATE TABLE `user`  (
  9.   `id` int(10) NOT NULL AUTO_INCREMENT,
  10.   `username` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  11.   `password` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  12.   PRIMARY KEY (`id`) USING BTREE
  13. ) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
  15. -- ----------------------------
  16. -- Records of user
  17. -- ----------------------------
  18. INSERT INTO `user` VALUES (1, 'user', 'user');
  19. INSERT INTO `user` VALUES (2, 'admin', 'admin');
  20. INSERT INTO `user` VALUES (3, 'hikktn', 'hikktn0320');
  22. SET FOREIGN_KEY_CHECKS = 1;

四、pojo准备

  1. package com.hikktn.domain;
  3. import java.io.Serializable;
  5. public class User implements Serializable {
  7.     private static final long serialVersionUID = 1L;
  9.     private Long id;
  10.     private String username;
  11.     private String password;
  13.     public Long getId() {
  14.         return id;
  15.     }
  17.     public void setId(Long id) {
  18.         this.id = id;
  19.     }
  21.     public String getUsername() {
  22.         return username;
  23.     }
  25.     public void setUsername(String username) {
  26.         this.username = username;
  27.     }
  29.     public String getPassword() {
  30.         return password;
  31.     }
  33.     public void setPassword(String password) {
  34.         this.password = password;
  35.     }
  37.     @Override
  38.     public String toString() {
  39.         return "User{" +
  40.             "id=" + id +
  41.             ", username='" + username + '\'' +
  42.             ", password='" + password + '\'' +
  43.             '}';
  44.     }
  45. }

五、mapper接口准备

  1. @Mapper
  2. @Repository
  3. public interface UserMapper {
  4.     /**
  5.     * xml方式，查询
  6.     *
  7.     * @return
  8.     */
  9.     public List<User> queryUserList();
  11.     /**
  12.     * xml方式，增加
  13.     *
  14.     * @param user
  15.     * @return
  16.     */
  17.     public int insertUserXml(User user);
  19.     /**
  20.     * xml方式，修改
  21.     *
  22.     * @param user
  23.     * @return
  24.     */
  25.     public int updateUserById(User user);
  27.     /**
  28.     * xml方式，删除
  29.     *
  30.     * @param id
  31.     * @return
  32.     */
  33.     public int deleteUserById(int id);
  34. }

六、XML增删改查准备

  1. <?xml version="1.0" encoding="utf-8" ?>
  2. <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  3.         "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
  4. <mapper namespace="com.hikktn.mapper.UserMapper">
  5.     <select id="queryUserList" resultType="User">
  6.         select * from user
  7.     </select>
  9.     <insert id="insertUserXml" parameterType="com.hikktn.domain.User" useGeneratedKeys="true" keyProperty="id">
  10.         insert into user (username,password) values(#{username,jdbcType=VARCHAR},#{password,jdbcType=VARCHAR})
  11.     </insert>
  13.     <update id="updateUserById" parameterType="com.hikktn.domain.User">
  14.         update user
  15.         <set>
  16.             <if test="username != null">
  17.                 username = #{username,jdbcType=VARCHAR},
  18.             </if>
  19.             <if test="password != null">
  20.                 password = #{password,jdbcType=VARCHAR}
  21.             </if>
  22.         </set>
  23.         where id = #{id,jdbcType=INTEGER}
  24.     </update>
  26.     <delete id="deleteUserById" parameterType="java.lang.Integer">
  27.         delete from user where id = #{id,jdbcType=INTEGER}
  28.     </delete>
  29. </mapper>

七、测试用例准备

  1. @RunWith(SpringRunner.class)
  2. @SpringBootTest(classes = SpringbootMybatisApplication.class)
  3. public class MybatisTest {
  5.     @Autowired
  6.     private UserMapper userMapper;
  7.     /**
  8.      * xml方式，查询全部
  9.      */
  10.     @Test
  11.     public void testQueryUserList() {
  12.         List<User> users = userMapper.queryUserList();
  13.         System.out.println(users);
  14.     }
  16.     /**
  17.      * xml方式，增加一条数据
  18.      */
  19.     @Test
  20.     public void testInsertUserXml() {
  21.         User user = new User();
  22.         user.setUsername("hikktn");
  23.         user.setPassword("hikktn");
  24.         int result = userMapper.insertUserXml(user);
  25.         System.out.println("插入成功，返回结果：" + result);
  26.     }
  28.     /**
  29.      * xml方式，修改一条数据
  30.      */
  31.     @Test
  32.     public void testUpdateUserById() {
  33.         Map<String, Object> map = new HashMap<>();
  34.         map.put("id", "3");
  35.         User user = userMapper.selectUserWithId(map);
  36.         user.setUsername("hikktn");
  37.         user.setPassword("654132");
  38.         int result = userMapper.updateUserById(user);
  39.         System.out.println("修改成功，返回结果：" + result);
  40.     }
  42.     /**
  43.      * xml方式，删除
  44.      */
  45.     @Test
  46.     public void testDeleteUserById() {
  47.         int result = userMapper.deleteUserById(3);
  48.         System.out.println("删除成功，返回结果：" + result);
  49.     }
  50. }

八、测试结果

查询结果

  1. 2021-08-13 15:38:54.057 DEBUG 18028 --- [           main] c.h.mapper.UserMapper.queryUserList      : ==>  Preparing: select * from user 
  2. 2021-08-13 15:38:54.075 DEBUG 18028 --- [           main] c.h.mapper.UserMapper.queryUserList      : ==> Parameters: 
  3. 2021-08-13 15:38:54.088 DEBUG 18028 --- [           main] c.h.mapper.UserMapper.queryUserList      : <==      Total: 3
  4. [User{id=1, username='user', password='user'}, User{id=2, username='admin', password='admin'}, User{id=8, username='Tom', password='tom'}]

增加结果

  1. 2021-08-13 15:41:06.496 DEBUG 10856 --- [           main] c.h.mapper.UserMapper.insertUserXml      : ==>  Preparing: insert into user (username,password) values(?,?) 
  2. 2021-08-13 15:41:06.517 DEBUG 10856 --- [           main] c.h.mapper.UserMapper.insertUserXml      : ==> Parameters: hikktn(String), hikktn(String)
  3. 2021-08-13 15:41:06.524 DEBUG 10856 --- [           main] c.h.mapper.UserMapper.insertUserXml      : <==    Updates: 1
  4. 插入成功，返回结果：1

修改结果

  1. 2021-08-13 15:42:23.129 DEBUG 16688 --- [           main] c.h.mapper.UserMapper.selectUserWithId   : ==>  Preparing: SELECT * FROM user WHERE ( id = ? ) 
  2. 2021-08-13 15:42:23.160 DEBUG 16688 --- [           main] c.h.mapper.UserMapper.selectUserWithId   : ==> Parameters: 10(String)
  3. 2021-08-13 15:42:23.177 DEBUG 16688 --- [           main] c.h.mapper.UserMapper.selectUserWithId   : <==      Total: 1
  4. 2021-08-13 15:42:23.238 DEBUG 16688 --- [           main] c.h.mapper.UserMapper.updateUserById     : ==>  Preparing: update user SET username = ?, password = ? where id = ? 
  5. 2021-08-13 15:42:23.238 DEBUG 16688 --- [           main] c.h.mapper.UserMapper.updateUserById     : ==> Parameters: hikktn(String), 654132(String), 10(Long)
  6. 2021-08-13 15:42:23.241 DEBUG 16688 --- [           main] c.h.mapper.UserMapper.updateUserById     : <==    Updates: 1
  7. 修改成功，返回结果：1

删除结果

  1. 2021-08-13 15:42:58.416 DEBUG 12048 --- [           main] c.h.mapper.UserMapper.deleteUserById     : ==>  Preparing: delete from user where id = ? 
  2. 2021-08-13 15:42:58.438 DEBUG 12048 --- [           main] c.h.mapper.UserMapper.deleteUserById     : ==> Parameters: 10(Integer)
  3. 2021-08-13 15:42:58.445 DEBUG 12048 --- [           main] c.h.mapper.UserMapper.deleteUserById     : <==    Updates: 1
  4. 删除成功，返回结果：1