mysql创建 存储过程 并通过java程序调用该存储过程
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); create procedure login_ning(in p_id int,in p_pwd int,out flag int)begindeclare v_pwd int; select pwd into v_pwd from users_ning where id = p_id; if v_pwd = p_pwd then set 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 stmt.setobject(i, obj[i - 1]);// 数组下标从0开始 } if (size for (int k = size + 1; 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 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 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 stmt.setobject(i, args[i - 1]);// 数组下标从0开始 } if (size for (int k = size + 1; 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 string str = rsmd.getcolumnname(i); strs[i - 1] = str; system.out.print(str + \t); } return strs; } public static void getcolumndatafrommysql(string sql) throws exception {// 输出表中的数据 conn = dbutils.getconnmysql(); getcolumndata(sql); } public static void getcolumndatafromorcalel(string sql) throws exception {// 输出表中的数据 conn = dbutils.getconnorcale(); getcolumndata(sql); } public static void getcolumndata(string sql) throws exception {// 输出表中的数据 st = conn.createstatement(); rs = st.executequery(sql); resultsetmetadata rsmd = rs.getmetadata(); system.out .println(\n------------------------------------------------------------------------------------------------------------------------); while (rs.next()) { for (int i = 1; i system.out.print(rs.getstring(i) + \t); } system.out.println(); } system.out .println(------------------------------------------------------------------------------------------------------------------------); } public static void gettabledatafromorcale(string sql) throws exception {// 输出表的列名 // 和表中的全部数据 conn = dbutils.getconnorcale(); gettabledata(sql); } public static void gettabledatafrommysql(string sql) throws exception {// 输出表的列名 // 和表中的全部数据 conn = dbutils.getconnmysql(); gettabledata(sql); } private static void gettabledata(string sql) throws sqlexception { // gettabledatafrommysql // gettabledatafromorcale 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 string str = rsmd.getcolumnname(i); strs[i - 1] = str; system.out.print(str + \t); } system.out .println(\n------------------------------------------------------------------------------------------------------------------------); while (rs.next()) { for (int i = 1; i system.out.print(rs.getstring(i) + \t); } system.out.println(); } system.out .println(------------------------------------------------------------------------------------------------------------------------); }}