mysql创建 存储过程 并通过java程序调用该存储过程_MySQL

create table users_ning(id primary key auto_increment,pwd int); insert into users_ning values(id,1234);insert into users_ning values(id,12345); insert into users_ning values(id,12); insert into users_ning values(id,123);CREATEPROCEDURE login_ning(IN p_id int,IN p_pwd int,OUT flag int)BEGINDECLARE	v_pwd int;select pwd INTO v_pwd from users_ningwhere id = p_id; if v_pwd = p_pwd thenset flag:=1;else select v_pwd;set flag := 0;end if;END package demo20130528;import java.sql.*;import demo20130526.DBUtils;/** * 测试JDBC API调用过程 * @author tarena * */public class ProcedureDemo2 {/** * @param args * @throws Exception*/public static void main(String[] args) throws Exception {System.out.println(login(123, 1234));}/** * 调用过程,实现登录功能 * @param id 考生id * @param pwd 考试密码 * @return if成功:1; if密码错:0; if没有用户:-1 * @throws Exception*/public static int login(int id, int pwd) throws Exception{int flag = -1;String sql = "{call login_ning(?,?,?)}";//*****Connection conn = DBUtils.getConnMySQL();CallableStatement stmt = null;try{stmt = conn.prepareCall(sql);//传递输入参数stmt.setInt(1, id);stmt.setInt(2, pwd);//注册输出参数,第三个占位符的数据类型是整型stmt.registerOutParameter(3, Types.INTEGER);//*****//执行过程stmt.execute();//获得过程执行后的输出参数flag = stmt.getInt(3);//*****}catch(Exception e){e.printStackTrace();}finally{stmt.close();DBUtils.dbClose();}return flag;}}

package demo20130526;import java.io.File;import java.io.FileInputStream;import java.io.FileNotFoundException;import java.io.IOException;import java.sql.Connection;import java.sql.DatabaseMetaData;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.ResultSetMetaData;import java.sql.SQLException;import java.sql.Statement;import java.util.Properties;public class DBUtils {	static Connection conn = null;	static PreparedStatement stmt = null;	static ResultSet rs = null;	static Statement st = null;	static String username = null;	static String password = null;	static String url = null;	static String driverName = null;	public static Connection getConnMySQL() throws Exception {// 连接mysql 返回conn		getUrlUserNamePassWordClassNameMySQL();		conn = DriverManager.getConnection(url, username, password);		// conn.setAutoCommit(false);设置自动提交为false		return conn;	}	public static Connection getConnORCALE() throws Exception {// 连接orcale																// 返回conn		getUrlUserNamePassWordClassNameORCALE();		conn = DriverManager.getConnection(url, username, password);		// conn.setAutoCommit(false);		return conn;	}	private static void getUrlUserNamePassWordClassNameORCALE()			throws Exception {		// 从资源文件 获取 orcale的username password url等信息		Properties pro = new Properties();		File path = new File("src/all.properties");		pro.load(new FileInputStream(path));		String paths = pro.getProperty("filepath");		File file = new File(paths + "orcale.properties");		getFromProperties(file);	}	public static void getUrlUserNamePassWordClassNameMySQL() throws Exception {		// 从资源文件 获取mysql的username password url等信息		Properties pro = new Properties();		File path = new File("src/all.properties");		pro.load(new FileInputStream(path));		String paths = pro.getProperty("filepath");		File file = new File(paths + "mysql.properties");		getFromProperties(file);	}	public static void getFromProperties(File file) throws IOException,			FileNotFoundException, ClassNotFoundException {// 读资源文件的内容		Properties pro = new Properties();		pro.load(new FileInputStream(file));		username = pro.getProperty("username");		password = pro.getProperty("password");		url = pro.getProperty("url");		driverName = pro.getProperty("driverName");		Class.forName(driverName);	}	public static void dbClose() throws Exception {// 关闭所有		if (rs != null)			rs.close();		if (st != null)			st.close();		if (stmt != null)			stmt.close();		if (conn != null)			conn.close();	}	public static ResultSet getById(String tableName, int id) throws Exception {// 用id来查询结果		st = conn.createStatement();		rs = st.executeQuery("select * from " + tableName + "where id=" + id				+ " ");		return rs;	}	public static ResultSet getByAll(String sql, Object... obj)			throws Exception {// 用关键字 实现查询 关键字额可以任意		sql = sql.replaceAll(";", "");		sql = sql.trim();		stmt = conn.prepareStatement(sql);		String[] strs = sql.split("//?");// 将sql 以? 非开		int num = strs.length;// 得到?的个数		int size = obj.length;		for (int i = 1; i