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>

二、配置准备

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: /

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

三、数据准备

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;

四、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 + '\'' +
                '}';
    }
}

五、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);
}

六、动态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();
	}
}

七、测试用例准备

@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);
	}
}

八、测试结果

查询结果
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'}]
增加结果
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
修改结果
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
删除结果
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
    点赞
  • 3
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

hikktn

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值