devbox
不正经
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

SpringBoot整合Mybatis注解动态SQL增删改查_springboot通过注解修改mybatis sql

作者：不正经 | 2024-05-16 03:25:55

赞

踩

springboot通过注解修改mybatis sql

零、知识介绍

动态SQL其实就是硬编码，不用xml方式，不用注解方式，可以更加灵活多变的使用java代码进行判断，业务逻辑处理生成一个SQL。
硬编码的执行速度比xml方式快，并且可以进行debug盘查错误，较全注解方式更加灵活，并且可读性高，缺点可能就是不流行，不能和xml接口一起使用，mybatis 3.4以上支持 多参数，3.4以下，只能使用map传值。
因为是生成字符串SQL，依然存在SQL注入问题，所以必须使用 " username = #{username}"，不能使用 " username = " + username 方式

一、依赖准备

<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.0.1.RELEASE</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>

	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
		<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
		<java.version>1.8</java.version>
	</properties>

	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>

		<!--SpingBoot集成junit测试的起步依赖-->
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>

		<!--mybatis起步依赖-->
		<dependency>
			<groupId>org.mybatis.spring.boot</groupId>
			<artifactId>mybatis-spring-boot-starter</artifactId>
			<version>1.1.1</version>
		</dependency>

		<!-- MySQL连接驱动 -->
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
		</dependency>
		
	</dependencies>

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40

二、配置准备

application.yml

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


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29

log4j.properties

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

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30

三、数据准备

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `password` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES (1, 'user', 'user');
INSERT INTO `user` VALUES (2, 'admin', 'admin');
INSERT INTO `user` VALUES (3, 'hikktn', 'hikktn0320');

SET FOREIGN_KEY_CHECKS = 1;

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22

四、pojo准备

package com.hikktn.domain;

import java.io.Serializable;

public class User implements Serializable {

    private static final long serialVersionUID = 1L;

    private Long id;
    private String username;
    private String password;

    public Long getId() {
        return id;
    }

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

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                '}';
    }
}


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46

五、mapper接口准备

@Mapper
@Repository
public interface UserMapper {
    /**
	 * 动态查询
	 *
	 * @param param
	 * @return
	 */
	@SelectProvider(type = UserDynaSqlProviderBuilder.class, method = "buildSelectUserWithParam")
	public List<User> selectUserInfoWithParam(Map<String, Object> param);

	/**
	 * 动态增加,并自动获取自增主键
	 *
	 * @param user
	 * @return
	 */
	@InsertProvider(type = UserDynaSqlProviderBuilder.class, method = "buildInsertUser")
	@Options(useGeneratedKeys = true, keyColumn = "id", keyProperty = "id")
	public int insertUser(User user);

	/**
	 * 动态根据id查询
	 *
	 * @param param
	 * @return
	 */
	 @Results({
			@Result(column = "id",property = "id"),
			@Result(column = "username",property = "username"),
			@Result(column = "password",property = "password")
	})
	@SelectProvider(type = UserDynaSqlProviderBuilder.class, method = "buildSelectUserWithParam")
	public User selectUserWithId(Map<String, Object> param);

	/**
	 * 动态修改
	 *
	 * @param user
	 */
	@UpdateProvider(type = UserDynaSqlProviderBuilder.class, method = "buildUpdateUser")
	public int updateUser(User user);

	/**
	 * 动态删除
	 *
	 * @param param
	 */
	@DeleteProvider(type = UserDynaSqlProviderBuilder.class, method = "buildDeleteUser")
	public int deleteUser(Map<String, Object> param);
}

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52

六、动态SQL准备

public class UserDynaSqlProviderBuilder {

	/**
	 * 动态查询
	 *
	 * @param param
	 * @return
	 */
	public String buildSelectUserWithParam(Map<String, Object> param) {
		return new SQL() {
			{
				SELECT("*");
				FROM("user");
				if (param.get("id") != null) {
					WHERE(" id = #{id} ");
				}
				if (param.get("username") != null) {
					WHERE(" username = #{username} ");
				}
				if (param.get("password") != null) {
					WHERE(" password = #{password} ");
				}
			}
		}.toString();
	}

	/**
	 * 动态增加
	 *
	 * @param user
	 * @return
	 */
	public String buildInsertUser(User user) {
		return new SQL() {
			{
				INSERT_INTO("user");
				if (user.getUsername() != null) {
					VALUES("username", "#{username}");
				}
				if (user.getPassword() != null) {
					VALUES("password", "#{password}");
				}
			}
		}.toString();
	}

	/**
	 * 动态修改
	 *
	 * @param user
	 * @return
	 */
	public String buildUpdateUser(User user) {
		return new SQL() {
			{
				UPDATE("user");
				if (user.getUsername() != null) {
					SET("username = #{username}");
				}
				if (user.getPassword() != null) {
					SET("password = #{password}");
				}
				WHERE(" id = #{id} ");
			}
		}.toString();
	}

	/**
	 * 动态删除
	 * @param param
	 * @return
	 */
	public String buildDeleteUser(Map<String, Object> param) {
		return new SQL() {
			{
				DELETE_FROM("user");
				if (param.get("id") != null) {
					WHERE(" id = #{id} ");
				}
				if (param.get("username") != null) {
					WHERE(" username = #{username}");
				}
			}
		}.toString();
	}
}


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59
  • 60
  • 61
  • 62
  • 63
  • 64
  • 65
  • 66
  • 67
  • 68
  • 69
  • 70
  • 71
  • 72
  • 73
  • 74
  • 75
  • 76
  • 77
  • 78
  • 79
  • 80
  • 81
  • 82
  • 83
  • 84
  • 85
  • 86
  • 87

七、测试用例准备

@RunWith(SpringRunner.class)
@SpringBootTest(classes = SpringbootMybatisApplication.class)
public class MybatisTest {
	@Autowired
	private UserMapper userMapper;
	/**
	 * 动态查询
	 */
	@Test
	public void testSelectUser() {
		Map<String, Object> map = new HashMap<>();
		map.put("id", "1");
		map.put("username", "user");
		map.put("password", "user");
		List<User> users = userMapper.selectUserInfoWithParam(map);
		System.out.println(users);
	}

	/**
	 * 动态增加
	 */
	@Test
	public void testInsertUser() {
		User user = new User();
		user.setUsername("hikktn");
		user.setPassword("123456");
		int result = userMapper.insertUser(user);
		System.out.println("插入成功，返回结果：" + result);
	}

	/**
	 * 动态修改
	 */
	@Test
	public void testUpdateUser() {
		Map<String, Object> map = new HashMap<>();
		map.put("id", "3");
		User user = userMapper.selectUserWithId(map);
		user.setUsername("hikktn");
		user.setPassword("hikktn0320");
		userMapper.updateUser(user);
	}

	/**
	 * 动态删除
	 */
	@Test
	public void testDeleteUser() {
		Map<String, Object> param = new HashMap<String, Object>();
		param.put("username", "hikktn");
		userMapper.deleteUser(param);
	}
}

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53

八、测试结果

查询结果
2021-08-13 11:56:46.283 DEBUG 400 --- [           main] c.h.m.U.selectUserInfoWithParam          : ==>  Preparing: SELECT * FROM user WHERE ( id = ? AND username = ? AND password = ? ) 
2021-08-13 11:56:46.299 DEBUG 400 --- [           main] c.h.m.U.selectUserInfoWithParam          : ==> Parameters: 1(String), user(String), user(String)
2021-08-13 11:56:46.322 DEBUG 400 --- [           main] c.h.m.U.selectUserInfoWithParam          : <==      Total: 1
[User{id=1, username='user', password='user'}]

  • 1
  • 2
  • 3
  • 4
增加结果
2021-08-13 12:12:03.155 DEBUG 796 --- [           main] com.hikktn.mapper.UserMapper.insertUser  : ==>  Preparing: INSERT INTO user (username, password) VALUES (?, ?) 
2021-08-13 12:12:03.175 DEBUG 796 --- [           main] com.hikktn.mapper.UserMapper.insertUser  : ==> Parameters: hikktn(String), 123456(String)
2021-08-13 12:12:03.181 DEBUG 796 --- [           main] com.hikktn.mapper.UserMapper.insertUser  : <==    Updates: 1
插入成功，返回结果：1

  • 1
  • 2
  • 3
  • 4
修改结果
2021-08-13 14:25:46.440 DEBUG 11172 --- [           main] c.h.mapper.UserMapper.selectUserWithId   : ==>  Preparing: SELECT * FROM user WHERE ( id = ? ) 
2021-08-13 14:25:46.458 DEBUG 11172 --- [           main] c.h.mapper.UserMapper.selectUserWithId   : ==> Parameters: 3(String)
2021-08-13 14:25:46.492 DEBUG 11172 --- [           main] c.h.mapper.UserMapper.selectUserWithId   : <==      Total: 1
2021-08-13 14:25:46.499 DEBUG 11172 --- [           main] com.hikktn.mapper.UserMapper.updateUser  : ==>  Preparing: UPDATE user SET username = ?, password = ? WHERE ( id = ? ) 
2021-08-13 14:25:46.499 DEBUG 11172 --- [           main] com.hikktn.mapper.UserMapper.updateUser  : ==> Parameters: hikktn(String), hikktn0320(String), 3(Long)
2021-08-13 14:25:46.516 DEBUG 11172 --- [           main] com.hikktn.mapper.UserMapper.updateUser  : <==    Updates: 1

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
删除结果
2021-08-13 14:26:49.140 DEBUG 14636 --- [           main] com.hikktn.mapper.UserMapper.deleteUser  : ==>  Preparing: DELETE FROM user WHERE ( username = ?) 
2021-08-13 14:26:49.160 DEBUG 14636 --- [           main] com.hikktn.mapper.UserMapper.deleteUser  : ==> Parameters: hikktn(String)
2021-08-13 14:26:49.170 DEBUG 14636 --- [           main] com.hikktn.mapper.UserMapper.deleteUser  : <==    Updates: 5

  • 1
  • 2
  • 3
声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/不正经/article/detail/576590
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号