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);//注册

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 <= size; i++) {	stmt.setObject(i, obj[i - 1]);// 数组下标从0开始	}	if (size < num) {	for (int k = size + 1; k <= num; k++) {	stmt.setObject(k, null);// 数组下标从0开始	}	}	rs = stmt.executeQuery();	return rs;	}	public static void doInsert(String sql) throws SQLException {// 传入 sql 语句	// 实现插入操作	st = conn.createStatement();	st.execute(sql);	}	public static void doInsert(String sql, Object... args) throws Exception {// 传入参数	// 利用	// PreparedStatement	// 实现插入	// 传入的参数是任意多个 因为有Object 。。args	int size = args.length;// 获得 Object ...obj 传过来的参数的个数	stmt = conn.prepareStatement(sql);	for (int i = 1; i <= size; i++) {	stmt.setObject(i, args[i - 1]);// 数组下标从0开始	}	stmt.execute();	}	public static int doUpdate(String sql) throws Exception {// 传入 sql 实现更新操作	st = conn.createStatement();	int num = st.executeUpdate(sql);	return num;	}	public static void doUpdate(String sql, Object... obj) throws Exception {	// 传入参数 利用 PreparedStatement实现更新	// 传入的参数是任意多个 因为有Object 。。args	int size = obj.length;// 获得 Object ...obj 传过来的参数的个数	stmt = conn.prepareStatement(sql);	for (int i = 1; i <= size; i++) {	stmt.setObject(i, obj[i - 1]);// 数组下标从0开始	}	stmt.executeUpdate(sql);	}	public static boolean doDeleteById(String tableName, int id)	throws SQLException {// 删除记录 by id	st = conn.createStatement();	boolean b = st.execute("delete from " + tableName + " where id=" + id	+ "");	return b;	}	public static boolean doDeleteByAll(String sql, Object... args)	throws SQLException {// 删除记录 可以按任何关键字	sql = sql.replaceAll(";", "");	sql = sql.trim();	stmt = conn.prepareStatement(sql);	String[] strs = sql.split("//?");// 将sql 以? 非开	int num = strs.length;// 得到?的个数	int size = args.length;	for (int i = 1; i <= size; i++) {	stmt.setObject(i, args[i - 1]);// 数组下标从0开始	}	if (size < num) {	for (int k = size + 1; k <= num; k++) {	stmt.setObject(k, null);// 数组下标从0开始	}	}	boolean b = stmt.execute();	return b;	}	public static void getMetaDate() throws Exception {// 获取数据库元素数据	conn = DBUtils.getConnORCALE();	DatabaseMetaData dmd = conn.getMetaData();	System.out.println(dmd.getDatabaseMajorVersion());	System.out.println(dmd.getDatabaseProductName());	System.out.println(dmd.getDatabaseProductVersion());	System.out.println(dmd.getDatabaseMinorVersion());	}	public static String[] getColumnNamesFromMySQL(String sql) throws Exception {	conn = DBUtils.getConnMySQL();	return getColumnName(sql);	}	public static String[] getColumnNamesFromOrcale(String sql)	throws Exception {	conn = DBUtils.getConnORCALE();	return getColumnName(sql);	}	private static String[] getColumnName(String sql) throws Exception {// 返回表中所有的列名	conn = DBUtils.getConnORCALE();	st = conn.createStatement();	rs = st.executeQuery(sql);	ResultSetMetaData rsmd = rs.getMetaData();	int num = rsmd.getColumnCount();	System.out.println("ColumnCount=" + num);	String[] strs = new String[num];	// 显示列名	for (int i = 1; i <= rsmd.getColumnCount(); i++) {	String str = rsmd.getColumnName(i);	strs[i - 1] = str;	System.out.print(str + "/t");	}	return strs;	}	public static void getColumnDataFromMySQL(String sql) throws Exception {//