ssm框架如何调用mysql存储过程

岚岚天空 · 发表于 2024-5-26 08:38:39

1.建表

/*
Navicat MySQL Data Transfer
Source Server : localMysql
Source Server Version : 50628
Source Host : 127.0.0.1:3306
Source Database : testmysql
Target Server Type : MYSQL
Target Server Version : 50628
File Encoding : 65001
Date: 2017-06-19 09:29:34
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for t_user
-- ----------------------------
DROP TABLE IF EXISTS `t_user`;
CREATE TABLE `t_user` (
`id` bigint(11) NOT NULL AUTO_INCREMENT,
`user_name` varchar(255) DEFAULT NULL COMMENT '用户名',
`user_phone` varchar(20) DEFAULT NULL COMMENT '手机号',
`user_email` varchar(255) DEFAULT NULL COMMENT '邮箱地址',
`user_pwd` varchar(32) DEFAULT NULL COMMENT '加盐后用户密码',
`pwd_salt` varchar(6) DEFAULT NULL COMMENT 'MD5盐',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`modify_time` datetime DEFAULT NULL COMMENT '最后修改时间',
`is_delete` tinyint(4) DEFAULT NULL COMMENT '是否删除，0-未删除；1-已删除',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='用户登录表';
-- ----------------------------
-- Records of t_user
-- ----------------------------
INSERT INTO `t_user` VALUES ('1', '子了a', '13285250574', '1045221654@qq.com', '05126a423a9379d529e4ee61a212fa55', 'KJUYT5', '2016-07-15 23:38:56', '2016-07-15 23:39:09', '0');
INSERT INTO `t_user` VALUES ('2', 'bbbb', '159852505743', '1198224554@qq.com', '98bd3a1bebde01ad363d3c5a0d1e56da', '656JHU', '2016-07-15 23:39:01', '2016-07-15 23:39:13', '0');
INSERT INTO `t_user` VALUES ('3', '王尼玛', '13685250574', '1256221654@qq.com', '5470db9b63c354f6c8d628b80ae2f3c3', '89UIKQ', '2016-07-15 23:39:05', '2016-07-15 23:39:16', '0');

复制代码

2.创建存储过程

CREATE DEFINER=`root`@`localhost` PROCEDURE `UPDATE_USER`(IN `in_id` integer,IN `in_user_name` varchar(20),OUT `out_user_phone` varchar(20))
BEGIN
update t_user set user_name = in_user_name WHERE t_user.id = in_id;
select user_phone INTO out_user_phone from t_user where id = in_id;

复制代码

3.service调用

package cn.demo.service;
import cn.demo.dao.AccountDao;
import cn.demo.model.Account;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import static junit.framework.TestCase.assertEquals;
/**
* Created by Administrator on 2017/3/22.
*/
@RunWith(SpringJUnit4ClassRunner.class)
//这个是用来加载写好的配置文件，传入的值是数组形式多个配置文件如下 {"····","·······"}
@ContextConfiguration({"classpath:spring/spring-dao-config.xml"})
public class AccountServiceTest {
@Autowired
private AccountDao accountDao;
// @Test
public void getAllAccount() throws Exception {
List<Account> accountList = accountDao.getAllAccount();
System.out.println("accountList=" + accountList.toString());
}
@Test
public void testGetNamesAndItems() {
Map<String, Object> parms = new HashMap<String, Object>();
parms.put("in_id", 1);
parms.put("in_user_name", "子了");
parms.put("out_user_phone", new String());
accountDao.updateUser(parms);
assertEquals("13285250574", parms.get("out_user_phone"));
}
}

复制代码

4.dao层

package cn.demo.dao;
/**
* Created by chengcheng on 2017/6/2 0002.
*/
import cn.demo.model.Account;
import org.springframework.stereotype.Repository;
import java.util.List;
import java.util.Map;
/**
* Created by Administrator on 2017/3/22.
*/
@Repository
public interface AccountDao {
String updateUser(Map<String, Object> parms);
}

复制代码

5.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="cn.demo.dao.AccountDao">
<select id ="updateUser" parameterType= "map" statementType="CALLABLE" >
{call UPDATE_USER(
#{in_id, jdbcType=INTEGER, mode=IN},
#{in_user_name, jdbcType= VARCHAR, mode=IN},
#{out_user_phone, mode=OUT, jdbcType= VARCHAR}
)}
</select >
</mapper>

复制代码

6.接下来运行junit就可以了

如果是在直接在应用中使用,把juinit内容直接放入service就可以了
到此这篇关于ssm框架调用mysql存储过程的文章就介绍到这了,更多相关ssm框架mysql存储过程内容请搜索脚本之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持脚本之家！

来源:https://www.jb51.net/database/320631s44.htm
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】我们会及时删除侵权内容，谢谢合作！