1、存储过程
（1）test_store_process5
# =================== test_store_process5 ==================================================================
CREATE DEFINER=`root`@`%` PROCEDURE `test_store_process5`(in param1 int(11))
BEGIN
/**
* 使用方法： call test_store_process5(23)
**/
select name from user where age=param1;
END
输出结果：
（2）test_store_process4
# ================== test_store_process4 ===================================================================
CREATE DEFINER=`root`@`%` PROCEDURE `test_store_process4`(in param1 int(11))
BEGIN
/**
* 使用方法： call test_store_process4(1,0)
**/
SELECT
(@i:=@i+1) as id,
domain as name,
NAME_NO as res
from spfl_model_area_list_tmp t,(select @i:=0) as it;
END
输出结果：
（3）test_store_process6（定义输出参数out param2）
# ================== test_store_process6 ===================================================================
CREATE DEFINER=`root`@`%` PROCEDURE `test_store_process6`(in param1 int(11),out param2 varchar(20))
BEGIN
/**
* 使用方法： call test_store_process6(15,@res)
* 可以（随便）放一个变量@outp代替输出变量来完成调用过程，这里使用@res
**/
SELECT
1 as id,
domain as name,
NAME_NO as res
from spfl_model_area_list_tmp t where t.`ORDER`=param1;
END
输出结果：
2、Java调用（重点看call3Test()中调用带定义输出参数的存储过程）
package cn.xiyou.test;
import cn.xiyou.utils.JDBCUtilForMysql;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import java.sql.*;
@RunWith(SpringRunner.class)
@SpringBootTest
public class TestDemo {
/**
* 测试返回一个值
*/
@Test
public void call1Test() {
String sql = "{call test_store_process5(?)}";
Connection conn = null;
CallableStatement call = null;
ResultSet rs = null;
try {
conn = JDBCUtilForMysql.getConnection();
call = conn.prepareCall(sql);
call.setInt(1, 23);
rs = call.executeQuery();
while(rs.next()){
String name = rs.getString("name");
System.out.println("【】name="+name); //【】name=张三
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
// 释放连接
JDBCUtilForMysql.release(rs, call, conn);
}
}
/**
* 测试返回多条数据
*/
@Test
public void call2Test() {
String sql = "{call test_store_process4(?)}";
Connection conn = null;
CallableStatement call = null;
ResultSet rs = null;
try {
conn = JDBCUtilForMysql.getConnection();
call = conn.prepareCall(sql);
call.setInt(1, 1);
rs = call.executeQuery();
while(rs.next()){
String id = rs.getString("id");
String name = rs.getString("name");
String res = rs.getString("res");
System.out.println("【】id="+id+",name="+name+",age="+res);
}
// 【】id=1,name=战术,age=1
// 【】id=2,name=战略,age=2
// 【】id=3,name=航天器,age=4
// 【】id=4,name=运载,age=8
// 【】id=5,name=战略&战术,age=3
// 【】id=6,name=航天器&战术,age=5
// 【】id=7,name=运载&战术,age=9
// 【】id=8,name=战略&航天器,age=6
// 【】id=9,name=战略&运载,age=10
// 【】id=10,name=运载&航天器,age=12
// 【】id=11,name=战略&航天器&战术,age=7
// 【】id=12,name=战略&运载&战术,age=11
// 【】id=13,name=运载&航天器&战术,age=13
// 【】id=14,name=战略&运载&航天器,age=14
// 【】id=15,name=战略&运载&航天器&战术,age=15
} catch (SQLException e) {
e.printStackTrace();
} finally {
// 释放连接
JDBCUtilForMysql.release(rs, call, conn);
}
}
/**
* 测试调用存储过程带输出参数的情况（输出可以任意多个值）
*/
@Test
public void call3Test() {
String sql = "{call test_store_process6(?,?)}";
Connection conn = null;
CallableStatement call = null;
ResultSet rs = null;
try {
conn = JDBCUtilForMysql.getConnection();
call = conn.prepareCall(sql);
// 设置输入参数
call.setInt(1, 15);
// 设置输出参数
call.registerOutParameter(2,Types.VARCHAR);//【重点】
rs = call.executeQuery();
while(rs.next()){
String id = rs.getString("id");
String name = rs.getString("name");
String res = rs.getString("res");
System.out.println("【】id="+id+",name="+name+",age="+res);
// 【】id=1,name=战略&运载&航天器&战术,age=15
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
// 释放连接
JDBCUtilForMysql.release(rs, call, conn);
}
}
}
3、工具类
package cn.xiyou.utils;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
/**
* JDBC工具类
* @author: zhangxiaohu
* @date: 2021/5/23 17:47
* @Description:
*/
public class JDBCUtilForMysql {
private static String driver = "com.mysql.jdbc.Driver";
private static String url = "jdbc:mysql://localhost:3306/succez?serverTimezone=UTC";
private static String username = "root";
private static String password = "1234";
/*
public static void main(String[] args) throws Exception {
Connection con = getConnection();
String sql = "select * from product where pid = ? or pname = ?";
PreparedStatement st = con.prepareStatement(sql);
st.setString(1, "p006");
st.setString(2, "联想");
ResultSet rs = st.executeQuery();
while(rs.next()){
System.out.println(rs.getString(2));
//System.out.println(rs.getString(1));
//System.out.println(rs.getLong(1));
}
release(rs, st, con);
}*/
/**
* 获取连接
* @return
*/
public static Connection getConnection() {
Connection conn = null;
try {
Class.forName(driver);
conn = DriverManager.getConnection(url, username, password);
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
/**
* 释放资源
* @param rs
* @param st
* @param conn
*/
public static void release(ResultSet rs, PreparedStatement st, Connection conn) {
try {
if (rs != null) {
rs.close();
}
} catch (Exception e) {
throw new RuntimeException(e);
} finally {
try {
if (st != null) {
st.close();
}
} catch (Exception e) {
throw new RuntimeException(e);
} finally {
try {
if (conn != null) {
conn.close();
}
} catch (Exception e) {
throw new RuntimeException(e);
}
}
}
}
}
