一 整合
由于本人的码云太多太乱了,于是决定一个一个的整合到一个springboot项目里面。
以整合功能
spring-boot,FusionChart,thymeleaf,vue,ShardingJdbc,mybatis-generator,微信分享授权,drools,spring-security,spring-jpa,webjars,Aspect,drools-drt,rabbitmq,zookeeper,mongodb
这次就来整合下java 调用mysql存储过程
二 存储过程
DROP PROCEDURE IF EXISTS `pro_num_user`;
delimiter ;;
CREATE PROCEDURE `pro_num_user`(IN user_name varchar(10) ,OUT count_num INT)
READS SQL DATA
BEGIN
SELECT COUNT(*) INTO count_num FROM tab_user WHERE 'name'=user_name;
END
;;
delimiter ;
三 代码
public classProcedure {public static final String DRIVER_CLASS = "com.mysql.jdbc.Driver";public static final String URL = "jdbc:mysql://127.0.0.1:3306/jpa";public static final String USERNAME = "root";public static final String PASSWORD = "123456789";public static void main(String[] args) throwsException {
Class.forName(DRIVER_CLASS);
Connection connection=DriverManager.getConnection(URL, USERNAME, PASSWORD);
String sql= "{CALL pro_num_user(?,?)}"; //调用存储过程
CallableStatement cstm = connection.prepareCall(sql); //实例化对象cstm
cstm.setString(1, "name"); //存储过程输入参数
cstm.registerOutParameter(2, Types.INTEGER); //设置返回值类型 即返回值
cstm.execute(); //执行存储过程
System.out.println(cstm.getInt(2));
cstm.close();
connection.close();
}
}
四 sql语句
create table tab_user (
id INT NOT NULL AUTO_INCREMENT,
user_name VARCHAR(100) NOT NULL,
PRIMARY KEY ( id )
);
DROP PROCEDURE IF EXISTS `pro_num_user`;
delimiter ;;
CREATE PROCEDURE `pro_num_user`(IN _name varchar(10) ,OUT count_num INT)
READS SQL DATA
BEGIN
SELECT COUNT(*) INTO count_num FROM tab_user WHERE user_name=_name;
END
;;
delimiter ;
五 参数定义
IN:参数的值必须在调用存储过程时指定,在存储过程中修改该参数的值不能被返回,为默认值
OUT:该值可在存储过程内部被改变,并可返回
INOUT:调用时指定,并且可被改变和返回
六 总结
代码没什么难点,算是复习了下存储进程的知识点