package com.xxx.util;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.Statement;

import java.util.Hashtable;

import java.util.Map;

import java.util.regex.Matcher;

import java.util.regex.Pattern;

public class ProcedureHelper {

	

	/*是否是開發(fā)模式*/

	static boolean DEV = false;

	static Map<String,Integer> CACHE = new Hashtable<String,Integer>(100);  

	static Pattern PATTERN_ARGS = Pattern.compile("`\\s*\\((.*)\\)\\s*begin",Pattern.DOTALL|Pattern.CASE_INSENSITIVE);

	static Pattern PATTERN_ARG = Pattern.compile("(in|out|inout)[^-,]*(,|$)",Pattern.DOTALL|Pattern.CASE_INSENSITIVE);

	private static int doWork(Connection con,String proc) throws Exception {

		Statement stmt = null;

		stmt = con.createStatement(); //3、Statement 接口需要通過Connection 接口進(jìn)行實(shí)例化操作

		ResultSet rs = stmt.executeQuery("show create procedure "+proc);

		

		if(rs.next()){

			//第三列為 存儲(chǔ)過程的代碼

			String code = rs.getString(3);

			Matcher m = PATTERN_ARGS.matcher(code);

			if(m.find()){

				String args = m.group(1);

				if(args.matches("\\s*"))

					return 0;

				else{

					Matcher m2 = PATTERN_ARG.matcher(args);

					int num = 0;

					while(m2.find()){

						//System.out.println(m2.group());

						num++;

					}

					return num;

				}

			}else{

				throw new RuntimeException("存儲(chǔ)過程: "+proc+"無法通過正則表達(dá)式獲取參數(shù)個(gè)數(shù):\n"+code);

				

			}

		}

		rs.close();

		stmt.close();

		throw new RuntimeException("沒有該存儲(chǔ)過程");

		

	}

	/**

	 * 注意， 在存儲(chǔ)過程的參數(shù)中，不能寫注釋，特別是 in xxx, 格式的注釋,不然會(huì)被錯(cuò)誤的識(shí)別

	 * @param con JDBC連接, 且不在函數(shù)內(nèi)釋放資源

	 * @param proc 調(diào)用的存儲(chǔ)過程

	 * @param args 傳遞給存儲(chǔ)過程的參數(shù)

	 * @throws 解析失敗

	 * */

	public static int getProcedureArgsNumber(Connection con,String proc) throws Exception {

		if(con == null || proc == null|| proc.equals("")){

			throw new RuntimeException("proc, con 參數(shù)不能為空");

		}

		//這里可以做緩存

		return doWork(con,proc);

	}

	public static void main(String arg[]) throws Exception{

		



		Connection con = null; //表示數(shù)據(jù)庫的連接對象  

		Class.forName("com.mysql.jdbc.Driver"); //1、使用CLASS 類加載驅(qū)動(dòng)程序  

		con = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=UTF-8","root","root"); //2、連接數(shù)據(jù)庫

		System.out.println(getProcedureArgsNumber(con,"my_procedure"));

		con.close();

	}



}