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); CREATE PROCEDURE login_ning(IN p_id int,IN p_pwd int,OUT flag int)BEGINDECLAREv_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 返回conngetUrlUserNamePassWordClassNameMySQL();conn = DriverManager.getConnection(url, username, password);// conn.setAutoCommit(false);设置自动提交为falsereturn conn;}public static Connection getConnORCALE() throws Exception {// 连接orcale// 返回conngetUrlUserNamePassWordClassNameORCALE();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 。。。argsint 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 。。。argsint 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 idst = 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// getTableDataFromOrcalest = 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("------------------------------------------------------------------------------------------------------------------------");}}登录后复制