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)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 <= 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 {// 输出表中的数据 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 <= rsmd.getColumnCount(); 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 <= rsmd.getColumnCount(); 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 <= rsmd.getColumnCount(); i++) { System.out.print(rs.getString(i) + "/t"); } System.out.println(); } System.out .println("------------------------------------------------------------------------------------------------------------------------"); }}