Java调用MySQL存储过程（含调用带定义输出参数的存储过程）

---

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

    }

}