零、知识介绍
存储过程就是将非常常用的一些SQL,其中能够进行各种SQL处理,返回一个结果值,特定的SQL语句。形成一个方法,在其他SQL中调用,可用于一些日志处理,多表前置增加修改等。
缺点:升级很繁重。
一、依赖准备
<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;
存储过程准备
查询全部存储过程
CREATE DEFINER=`root`@`localhost` PROCEDURE `select_user`()
BEGIN
#Routine body goes here...
SELECT * FROM `user`;
END
根据id查询存储过程
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_user_by_id`(IN uid INT)
BEGIN
#Routine body goes here...
-- DECLARE t_password VARCHAR(50); -- 定义变量
SELECT * from user where id = uid;
-- select t_password;
END
增加存储过程
CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_user`(OUT uid INT,IN name VARCHAR(50),IN pwd VARCHAR(50))
BEGIN
#Routine body goes here...
insert into user (username,`password`) VALUES(name,pwd);
select id from user where username = name;
END
修改存储过程
CREATE DEFINER=`root`@`localhost` PROCEDURE `update_user`(IN uid INT,IN name VARCHAR(50),IN pwd VARCHAR(50))
BEGIN
#Routine body goes here...
update user set username = name , password = pwd where id = uid;
END
删除存储过程
CREATE DEFINER=`root`@`localhost` PROCEDURE `delete_user_by_id`(IN uid INT)
BEGIN
#Routine body goes here...
DECLARE cnt INT DEFAULT 0;
set @cnt = (SELECT count(0) from user where id = uid);
IF (@cnt is not null) THEN
DELETE from user where id = uid;
END IF;
-- SELECT @cnt;
END
四、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 {
/**
* 查询所有数据,调用存储过程
*
* @return
*/
@Select("{call select_user()}")
@Options(statementType = StatementType.CALLABLE)
public List<User> selectUserList();
/**
* 根据id查询一条数据,存储过程
*
* @param param
* @return
*/
@Select("{call get_user_by_id(#{id,mode=IN,jdbcType=INTEGER})}")
@Options(statementType = StatementType.CALLABLE)
public User getUser(Map<String, Object> param);
/**
* 新增一条数据,存储过程
*
* @param user
*/
@Insert("{call insert_user(#{id,mode=OUT,jdbcType=INTEGER}," + "#{username,mode=IN,jdbcType=VARCHAR}," +
"#{password,mode=IN,jdbcType=VARCHAR})}")
@Options(statementType = StatementType.CALLABLE)
void saveUser(User user);
/**
* 修改,存储过程
*
* @param user
*/
@Update("{call update_user(#{id,mode=IN},#{username,mode=IN},#{password,mode=IN})}")
@Options(statementType = StatementType.CALLABLE)
void modifyUser(User user);
/**
* 删除,存储过程
*
* @param user
*/
@Delete("{call delete_user_by_id(#{id,mode=IN})}")
@Options(statementType = StatementType.CALLABLE)
void removeUser(User user);
}
六、测试用例准备
@RunWith(SpringRunner.class)
@SpringBootTest(classes = SpringbootMybatisApplication.class)
public class MybatisTest {
@Autowired
private UserMapper userMapper;
/**
* 动态删除
*/
@Test
public void testDeleteUser() {
Map<String, Object> param = new HashMap<String, Object>();
param.put("username", "hikktn");
userMapper.deleteUser(param);
}
/**
* 存储过程 查询
*/
@Test
public void testSelectUserList() {
List<User> users = userMapper.selectUserList();
System.out.println(users);
}
/**
* 存储过程 根据id查询
*/
@Test
public void testGetUser() {
Map<String, Object> param = new HashMap<>();
param.put("id", 2);
User user = userMapper.getUser(param);
System.out.println(user);
}
/**
* 存储过程 增加
*/
@Test
public void testInsertUserCell(){
User user=new User();
user.setUsername("Tom");
user.setPassword("tom");
userMapper.saveUser(user);
}
/**
* 存储过程 修改
*/
@Test
public void testUpdateUserCall(){
User user =new User();
user.setId(4L);
user.setUsername("Tom");
user.setPassword("123");
userMapper.modifyUser(user);
}
/**
* 存储过程 删除
*/
@Test
public void testRemoveUser(){
User user =new User();
user.setId(4L);
userMapper.removeUser(user);
}
}
七、测试结果
查询全部结果
2021-08-13 15:05:09.056 DEBUG 10812 --- [ main] c.h.mapper.UserMapper.selectUserList : ==> Preparing: {call select_user()}
2021-08-13 15:05:09.128 DEBUG 10812 --- [ main] c.h.mapper.UserMapper.selectUserList : ==> Parameters:
2021-08-13 15:05:09.153 DEBUG 10812 --- [ main] c.h.mapper.UserMapper.selectUserList : <== Total: 2
2021-08-13 15:05:09.154 DEBUG 10812 --- [ main] c.h.mapper.UserMapper.selectUserList : <== Updates: 0
[User{id=1, username='user', password='user'}, User{id=2, username='admin', password='admin'}]
根据id查询
2021-08-13 15:06:36.444 DEBUG 9504 --- [ main] com.hikktn.mapper.UserMapper.getUser : ==> Preparing: {call get_user_by_id(?)}
2021-08-13 15:06:36.492 DEBUG 9504 --- [ main] com.hikktn.mapper.UserMapper.getUser : ==> Parameters: 2(Integer)
2021-08-13 15:06:36.509 DEBUG 9504 --- [ main] com.hikktn.mapper.UserMapper.getUser : <== Total: 1
2021-08-13 15:06:36.509 DEBUG 9504 --- [ main] com.hikktn.mapper.UserMapper.getUser : <== Updates: 0
User{id=2, username='admin', password='admin'}
增加结果
2021-08-13 15:07:10.417 DEBUG 14868 --- [ main] com.hikktn.mapper.UserMapper.saveUser : ==> Preparing: {call insert_user(?,?,?)}
2021-08-13 15:07:10.459 DEBUG 14868 --- [ main] com.hikktn.mapper.UserMapper.saveUser : ==> Parameters: Tom(String), tom(String)
修改结果
2021-08-13 15:07:54.388 DEBUG 3064 --- [ main] com.hikktn.mapper.UserMapper.modifyUser : ==> Preparing: {call update_user(?,?,?)}
2021-08-13 15:07:54.433 DEBUG 3064 --- [ main] com.hikktn.mapper.UserMapper.modifyUser : ==> Parameters: 4(Long), Tom(String), 123(String)
2021-08-13 15:07:54.436 DEBUG 3064 --- [ main] com.hikktn.mapper.UserMapper.modifyUser : <== Updates: 0
删除结果
2021-08-13 15:08:11.678 DEBUG 7008 --- [ main] com.hikktn.mapper.UserMapper.removeUser : ==> Preparing: {call delete_user_by_id(?)}
2021-08-13 15:08:11.727 DEBUG 7008 --- [ main] com.hikktn.mapper.UserMapper.removeUser : ==> Parameters: 4(Long)
2021-08-13 15:08:11.731 DEBUG 7008 --- [ main] com.hikktn.mapper.UserMapper.removeUser : <== Updates: 0