一、SQL记录 

-- 使用test数据库
USE test
 
-- 创建t_user数据表
CREATE TABLE t_user(id INT PRIMARY KEY,username VARCHAR(20),age INT)
 
-- 插入数据
INSERT INTO t_user VALUES(1,'root',21);
INSERT INTO t_user VALUES(2,'admin',18);
INSERT INTO t_user VALUES(3,'zhangsan',29);
INSERT INTO t_user VALUES(4,'lisi',10);
INSERT INTO t_user VALUES(5,'wangwu',15);
INSERT INTO t_user VALUES(6,'zhaoliu',35);
 
-- 创建存储过程proc_1:
DELIMITER $$
CREATE PROCEDURE proc_1()
BEGIN
 SELECT * FROM t_user;
END $$
DELIMITER ; 
 
-- 创建存储过程proc_2
DELIMITER $$
CREATE PROCEDURE proc_2(IN a INT)
BEGIN
 SELECT * FROM t_user WHERE age > a;
END $$
DELIMITER ; 
 
-- 调用存储过程proc_2
CALL proc_2(30)
 
 
-- 创建存储过程proc_3
DELIMITER $$
CREATE PROCEDURE proc_3(IN p_name VARCHAR(20), IN p_age INT)
BEGIN
 SELECT * FROM t_user WHERE username=p_name AND age = p_age;
END $$
DELIMITER ; 
 
-- 调用存储过程proc_3
CALL proc_3('zhangsan',29)
 
-- 创建存储过程proc_4
DELIMITER $$
CREATE PROCEDURE proc_4( IN l_name VARCHAR(20), IN i_start INT, IN p_size INT)
BEGIN
 SELECT username AS '姓名',age AS '芳龄' FROM t_user WHERE username LIKE CONCAT('%',l_name,'%') LIMIT i_start,p_size;
END $$
DELIMITER ; 
 
-- 查询
SELECT username AS '姓名',age AS '芳龄' FROM t_user WHERE username LIKE '%%' LIMIT 0,4;
 
-- 调用存储过程proc_4
CALL proc_4('',0,5)
 
-- 创建存储过程proc_5
DELIMITER $$
CREATE PROCEDURE proc_5()
BEGIN
 SELECT SUBSTRING(username,1,10) AS 'name', age AS '年龄' FROM t_user;
END $$
DELIMITER ; 
 
-- 调用存储过程proc_5
CALL proc_5()
 
-- 创建存储过程proc_6
DELIMITER $$
CREATE PROCEDURE proc_6()
BEGIN
 SELECT SUBSTRING(username,1,2) AS 'name' FROM t_user;
 SELECT age AS '年龄' FROM t_user;
END $$
DELIMITER ; 
 
-- 调用存储过程proc_5
CALL proc_6()
 
 
-- 删除存储过程
DROP PROCEDURE proc_5
 
-- 查看存储过程
SHOW PROCEDURE STATUS
 
-- 查看proc_2构建语句 
SHOW CREATE PROCEDURE proc_2

二、JDBC记录

            
            // 获取所有存储过程及其参数和参数的数据类型

            Class.forName(driver);
            conn = DriverManager.getConnection(url, username, password);
            DatabaseMetaData metaData = conn.getMetaData();
            procRs = metaData.getProcedures(null, null, null);
            StoredProcDTO storedProcDTO = null;
            // 遍历存储过程
            while (procRs.next()) {
                ResultSet colRs = null;
                try {
                    String procedureName = procRs.getString("PROCEDURE_NAME");
                    colRs = metaData.getProcedureColumns(null, null, procedureName, null);
                    // 获取存储过程中的参数
                    while (colRs.next()) {
                        String columnName = colRs.getString("COLUMN_NAME");
                        String typeName = colRs.getString("TYPE_NAME");
                    }
                }catch (SQLException e){
                    e.printStackTrace();
                }finally {
                    JdbcUtil.close(colRs);
                }
            }

            
            // 执行存储过程，并返回数据集字段及其类型
            Class.forName(proc.getDriver());
            conn = DriverManager.getConnection(url,user,password);
            String sql = preProcSql(proc);
            cs = conn.prepareCall(sql);
            preProcParms(proc, cs);
            rs = cs.executeQuery();
            while (rs.next()) {
                // 获取此存储过程查询所设计到的列名
                if (fieldList.size() == 0) {
                    ResultSetMetaData metaData = rs.getMetaData();
                    int columnCount = metaData.getColumnCount();
                    for (int i = 1; i <= columnCount; i++) {
                        // 字段名
                        String code = metaData.getColumnName(i);
                        // 字段别名
                        String name = metaData.getColumnLabel(i);
                        // 字段类型
                        String type = metaData.getColumnTypeName(i);
                    }
                }
                Object[] objects = new Object[fieldList.size()];
                for (int i = 0; i < fieldList.size(); i++) {
                    String value = rs.getString(fieldList.get(i).getName());
                    objects[i] = value;
                }
            }

    /**
     * 准备存储过程的sql
     *
     * @param proc
     * @return
     */
    private String preProcSql(StoredProcDTO proc) throws SQLException {

        if (proc == null) {
            return "";
        }

        String sql = "call " + proc.getProcedureName() + "(";

        if (proc.getColumnList() != null && proc.getColumnList().size() > 0) {
            for (int i = 0; i < proc.getColumnList().size(); i++) {
                sql += "?,";
            }
            // 去掉最后一个,
            sql = sql.substring(0, sql.length() - 1);
        }

        return sql + ")";
    }

    /**
     * 准备存储过程参数
     *
     * @param proc
     * @param cs
     * @throws SQLException
     */
    private void preProcParms(StoredProcDTO proc, CallableStatement cs) throws SQLException {
        for (int i = 1; i <= proc.getColumnList().size(); i++) {
            ProcColumn procColumn = proc.getColumnList().get(i - 1);

            switch (procColumn.getType().toLowerCase()) {
                case "int":
                    cs.setInt(i, Integer.parseInt(StrUtil.isEmpty(procColumn.getValue()) ? "0" : procColumn.getValue()));
                    break;
                case "varchar":
                    cs.setString(i, StrUtil.isEmpty(procColumn.getValue()) ? "" : procColumn.getValue());
                    break;
                case "double":
                    cs.setDouble(i, Double.parseDouble(StrUtil.isEmpty(procColumn.getValue()) ? "0.0" : procColumn.getValue()));
                    break;
                case "date":
                    String value = StrUtil.isEmpty(procColumn.getValue()) ? "1970-01-01" : procColumn.getValue();
                    DateTime dateTime = DateUtil.parse(value);
                    cs.setDate(i, new java.sql.Date(dateTime.getTime()));
                    break;
                default:
                    break;
            }
        }
    }

// 两个实体类
public class StoredProcDTO {

    private String driver;

    private String url;

    private String username;

    private String password;

    private String procedureName;

    private List<ProcColumn> columnList;

    public StoredProcDTO() {
    }

    public StoredProcDTO(String procedureName) {
        this.procedureName = procedureName;
    }
}


public class ProcColumn implements Serializable {

    private static final long serialVersionUID = 1L;

    private String name;
    private String type;
    private String value;

    public ProcColumn() {
    }


    public ProcColumn(String name, String type) {
        this.name = name;
        this.type = type;
    }
}