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)
BEGIN
DECLARE	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;
  }

}

</pre><pre name="code" class="java">

</pre><pre name="code" class="java">

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 {
<span style="white-space:pre">	</span>static Connection conn = null;
<span style="white-space:pre">	</span>static PreparedStatement stmt = null;
<span style="white-space:pre">	</span>static ResultSet rs = null;
<span style="white-space:pre">	</span>static Statement st = null;
<span style="white-space:pre">	</span>static String username = null;
<span style="white-space:pre">	</span>static String password = null;
<span style="white-space:pre">	</span>static String url = null;
<span style="white-space:pre">	</span>static String driverName = null;


<span style="white-space:pre">	</span>public static Connection getConnMySQL() throws Exception {// 連接mysql 返回conn
<span style="white-space:pre">		</span>getUrlUserNamePassWordClassNameMySQL();
<span style="white-space:pre">		</span>conn = DriverManager.getConnection(url, username, password);
<span style="white-space:pre">		</span>// conn.setAutoCommit(false);設置自動提交為false
<span style="white-space:pre">		</span>return conn;
<span style="white-space:pre">	</span>}


<span style="white-space:pre">	</span>public static Connection getConnORCALE() throws Exception {// 連接orcale
<span style="white-space:pre">																</span>// 返回conn
<span style="white-space:pre">		</span>getUrlUserNamePassWordClassNameORCALE();
<span style="white-space:pre">		</span>conn = DriverManager.getConnection(url, username, password);
<span style="white-space:pre">		</span>// conn.setAutoCommit(false);
<span style="white-space:pre">		</span>return conn;
<span style="white-space:pre">	</span>}


<span style="white-space:pre">	</span>private static void getUrlUserNamePassWordClassNameORCALE()
<span style="white-space:pre">			</span>throws Exception {
<span style="white-space:pre">		</span>// 從資源文件 獲取 orcale的username password url等信息
<span style="white-space:pre">		</span>Properties pro = new Properties();
<span style="white-space:pre">		</span>File path = new File("src/all.properties");
<span style="white-space:pre">		</span>pro.load(new FileInputStream(path));
<span style="white-space:pre">		</span>String paths = pro.getProperty("filepath");
<span style="white-space:pre">		</span>File file = new File(paths + "orcale.properties");
<span style="white-space:pre">		</span>getFromProperties(file);


<span style="white-space:pre">	</span>}


<span style="white-space:pre">	</span>public static void getUrlUserNamePassWordClassNameMySQL() throws Exception {
<span style="white-space:pre">		</span>// 從資源文件 獲取mysql的username password url等信息
<span style="white-space:pre">		</span>Properties pro = new Properties();
<span style="white-space:pre">		</span>File path = new File("src/all.properties");
<span style="white-space:pre">		</span>pro.load(new FileInputStream(path));
<span style="white-space:pre">		</span>String paths = pro.getProperty("filepath");
<span style="white-space:pre">		</span>File file = new File(paths + "mysql.properties");
<span style="white-space:pre">		</span>getFromProperties(file);
<span style="white-space:pre">	</span>}


<span style="white-space:pre">	</span>public static void getFromProperties(File file) throws IOException,
<span style="white-space:pre">			</span>FileNotFoundException, ClassNotFoundException {// 讀資源文件的內容
<span style="white-space:pre">		</span>Properties pro = new Properties();
<span style="white-space:pre">		</span>pro.load(new FileInputStream(file));
<span style="white-space:pre">		</span>username = pro.getProperty("username");
<span style="white-space:pre">		</span>password = pro.getProperty("password");
<span style="white-space:pre">		</span>url = pro.getProperty("url");
<span style="white-space:pre">		</span>driverName = pro.getProperty("driverName");
<span style="white-space:pre">		</span>Class.forName(driverName);
<span style="white-space:pre">	</span>}


<span style="white-space:pre">	</span>public static void dbClose() throws Exception {// 關閉所有
<span style="white-space:pre">		</span>if (rs != null)
<span style="white-space:pre">			</span>rs.close();
<span style="white-space:pre">		</span>if (st != null)
<span style="white-space:pre">			</span>st.close();
<span style="white-space:pre">		</span>if (stmt != null)
<span style="white-space:pre">			</span>stmt.close();
<span style="white-space:pre">		</span>if (conn != null)
<span style="white-space:pre">			</span>conn.close();
<span style="white-space:pre">	</span>}


<span style="white-space:pre">	</span>public static ResultSet getById(String tableName, int id) throws Exception {// 用id來查詢結果
<span style="white-space:pre">		</span>st = conn.createStatement();
<span style="white-space:pre">		</span>rs = st.executeQuery("select * from " + tableName + "  where id=" + id
<span style="white-space:pre">				</span>+ " ");
<span style="white-space:pre">		</span>return rs;
<span style="white-space:pre">	</span>}


<span style="white-space:pre">	</span>public static ResultSet getByAll(String sql, Object... obj)
<span style="white-space:pre">			</span>throws Exception {// 用關鍵字 實現查詢 關鍵字額可以任意
<span style="white-space:pre">		</span>sql = sql.replaceAll(";", "");
<span style="white-space:pre">		</span>sql = sql.trim();
<span style="white-space:pre">		</span>stmt = conn.prepareStatement(sql);
<span style="white-space:pre">		</span>String[] strs = sql.split("\\?");// 將sql 以? 非開
<span style="white-space:pre">		</span>int num = strs.length;// 得到?的個數
<span style="white-space:pre">		</span>int size = obj.length;
<span style="white-space:pre">		</span>for (int i = 1; i <= size; i++) {
<span style="white-space:pre">			</span>stmt.setObject(i, obj[i - 1]);// 數組下標從0開始
<span style="white-space:pre">		</span>}
<span style="white-space:pre">		</span>if (size < num) {
<span style="white-space:pre">			</span>for (int k = size + 1; k <= num; k++) {
<span style="white-space:pre">				</span>stmt.setObject(k, null);// 數組下標從0開始
<span style="white-space:pre">			</span>}
<span style="white-space:pre">		</span>}
<span style="white-space:pre">		</span>rs = stmt.executeQuery();
<span style="white-space:pre">		</span>return rs;
<span style="white-space:pre">	</span>}


<span style="white-space:pre">	</span>public static void doInsert(String sql) throws SQLException {// 傳入 sql 語句
<span style="white-space:pre">																	</span>// 實現插入操作
<span style="white-space:pre">		</span>st = conn.createStatement();
<span style="white-space:pre">		</span>st.execute(sql);
<span style="white-space:pre">	</span>}


<span style="white-space:pre">	</span>public static void doInsert(String sql, Object... args) throws Exception {// 傳入參數
<span style="white-space:pre">																				</span>// 利用
<span style="white-space:pre">																				</span>// PreparedStatement
<span style="white-space:pre">																				</span>// 實現插入
<span style="white-space:pre">		</span>// 傳入的參數是任意多個 因為有Object 。。。args
<span style="white-space:pre">		</span>int size = args.length;// 獲得 Object ...obj 傳過來的參數的個數
<span style="white-space:pre">		</span>stmt = conn.prepareStatement(sql);
<span style="white-space:pre">		</span>for (int i = 1; i <= size; i++) {
<span style="white-space:pre">			</span>stmt.setObject(i, args[i - 1]);// 數組下標從0開始
<span style="white-space:pre">		</span>}
<span style="white-space:pre">		</span>stmt.execute();
<span style="white-space:pre">	</span>}


<span style="white-space:pre">	</span>public static int doUpdate(String sql) throws Exception {// 傳入 sql 實現更新操作
<span style="white-space:pre">		</span>st = conn.createStatement();
<span style="white-space:pre">		</span>int num = st.executeUpdate(sql);
<span style="white-space:pre">		</span>return num;
<span style="white-space:pre">	</span>}


<span style="white-space:pre">	</span>public static void doUpdate(String sql, Object... obj) throws Exception {
<span style="white-space:pre">		</span>// 傳入參數 利用 PreparedStatement實現更新
<span style="white-space:pre">		</span>// 傳入的參數是任意多個 因為有Object 。。。args
<span style="white-space:pre">		</span>int size = obj.length;// 獲得 Object ...obj 傳過來的參數的個數
<span style="white-space:pre">		</span>stmt = conn.prepareStatement(sql);
<span style="white-space:pre">		</span>for (int i = 1; i <= size; i++) {
<span style="white-space:pre">			</span>stmt.setObject(i, obj[i - 1]);// 數組下標從0開始
<span style="white-space:pre">		</span>}
<span style="white-space:pre">		</span>stmt.executeUpdate(sql);
<span style="white-space:pre">	</span>}


<span style="white-space:pre">	</span>public static boolean doDeleteById(String tableName, int id)
<span style="white-space:pre">			</span>throws SQLException {// 刪除記錄 by id
<span style="white-space:pre">		</span>st = conn.createStatement();
<span style="white-space:pre">		</span>boolean b = st.execute("delete from " + tableName + " where id=" + id
<span style="white-space:pre">				</span>+ "");
<span style="white-space:pre">		</span>return b;
<span style="white-space:pre">	</span>}


<span style="white-space:pre">	</span>public static boolean doDeleteByAll(String sql, Object... args)
<span style="white-space:pre">			</span>throws SQLException {// 刪除記錄 可以按任何關鍵字
<span style="white-space:pre">		</span>sql = sql.replaceAll(";", "");
<span style="white-space:pre">		</span>sql = sql.trim();
<span style="white-space:pre">		</span>stmt = conn.prepareStatement(sql);
<span style="white-space:pre">		</span>String[] strs = sql.split("\\?");// 將sql 以? 非開
<span style="white-space:pre">		</span>int num = strs.length;// 得到?的個數
<span style="white-space:pre">		</span>int size = args.length;
<span style="white-space:pre">		</span>for (int i = 1; i <= size; i++) {
<span style="white-space:pre">			</span>stmt.setObject(i, args[i - 1]);// 數組下標從0開始
<span style="white-space:pre">		</span>}
<span style="white-space:pre">		</span>if (size < num) {
<span style="white-space:pre">			</span>for (int k = size + 1; k <= num; k++) {
<span style="white-space:pre">				</span>stmt.setObject(k, null);// 數組下標從0開始
<span style="white-space:pre">			</span>}
<span style="white-space:pre">		</span>}
<span style="white-space:pre">		</span>boolean b = stmt.execute();
<span style="white-space:pre">		</span>return b;
<span style="white-space:pre">	</span>}


<span style="white-space:pre">	</span>public static void getMetaDate() throws Exception {// 獲取數據庫元素數據
<span style="white-space:pre">		</span>conn = DBUtils.getConnORCALE();
<span style="white-space:pre">		</span>DatabaseMetaData dmd = conn.getMetaData();
<span style="white-space:pre">		</span>System.out.println(dmd.getDatabaseMajorVersion());
<span style="white-space:pre">		</span>System.out.println(dmd.getDatabaseProductName());
<span style="white-space:pre">		</span>System.out.println(dmd.getDatabaseProductVersion());
<span style="white-space:pre">		</span>System.out.println(dmd.getDatabaseMinorVersion());
<span style="white-space:pre">	</span>}


<span style="white-space:pre">	</span>public static String[] getColumnNamesFromMySQL(String sql) throws Exception {
<span style="white-space:pre">		</span>conn = DBUtils.getConnMySQL();
<span style="white-space:pre">		</span>return getColumnName(sql);


<span style="white-space:pre">	</span>}


<span style="white-space:pre">	</span>public static String[] getColumnNamesFromOrcale(String sql)
<span style="white-space:pre">			</span>throws Exception {
<span style="white-space:pre">		</span>conn = DBUtils.getConnORCALE();
<span style="white-space:pre">		</span>return getColumnName(sql);


<span style="white-space:pre">	</span>}


<span style="white-space:pre">	</span>private static String[] getColumnName(String sql) throws Exception {// 返回表中所有的列名
<span style="white-space:pre">		</span>conn = DBUtils.getConnORCALE();
<span style="white-space:pre">		</span>st = conn.createStatement();
<span style="white-space:pre">		</span>rs = st.executeQuery(sql);
<span style="white-space:pre">		</span>ResultSetMetaData rsmd = rs.getMetaData();
<span style="white-space:pre">		</span>int num = rsmd.getColumnCount();
<span style="white-space:pre">		</span>System.out.println("ColumnCount=" + num);
<span style="white-space:pre">		</span>String[] strs = new String[num];
<span style="white-space:pre">		</span>// 顯示列名
<span style="white-space:pre">		</span>for (int i = 1; i <= rsmd.getColumnCount(); i++) {
<span style="white-space:pre">			</span>String str = rsmd.getColumnName(i);
<span style="white-space:pre">			</span>strs[i - 1] = str;
<span style="white-space:pre">			</span>System.out.print(str + "\t");
<span style="white-space:pre">		</span>}
<span style="white-space:pre">		</span>return strs;
<span style="white-space:pre">	</span>}


<span style="white-space:pre">	</span>public static void getColumnDataFromMySQL(String sql) throws Exception {// 輸出表中的數據
<span style="white-space:pre">		</span>conn = DBUtils.getConnMySQL();
<span style="white-space:pre">		</span>getColumnData(sql);
<span style="white-space:pre">	</span>}


<span style="white-space:pre">	</span>public static void getColumnDataFromORCALEL(String sql) throws Exception {// 輸出表中的數據
<span style="white-space:pre">		</span>conn = DBUtils.getConnORCALE();
<span style="white-space:pre">		</span>getColumnData(sql);
<span style="white-space:pre">	</span>}


<span style="white-space:pre">	</span>public static void getColumnData(String sql) throws Exception {// 輸出表中的數據
<span style="white-space:pre">		</span>st = conn.createStatement();
<span style="white-space:pre">		</span>rs = st.executeQuery(sql);
<span style="white-space:pre">		</span>ResultSetMetaData rsmd = rs.getMetaData();
<span style="white-space:pre">		</span>System.out
<span style="white-space:pre">				</span>.println("\n------------------------------------------------------------------------------------------------------------------------");
<span style="white-space:pre">		</span>while (rs.next()) {
<span style="white-space:pre">			</span>for (int i = 1; i <= rsmd.getColumnCount(); i++) {
<span style="white-space:pre">				</span>System.out.print(rs.getString(i) + "\t");
<span style="white-space:pre">			</span>}
<span style="white-space:pre">			</span>System.out.println();
<span style="white-space:pre">		</span>}
<span style="white-space:pre">		</span>System.out
<span style="white-space:pre">				</span>.println("------------------------------------------------------------------------------------------------------------------------");


<span style="white-space:pre">	</span>}


<span style="white-space:pre">	</span>public static void getTableDataFromOrcale(String sql) throws Exception {// 輸出表的列名
<span style="white-space:pre">																			</span>// 和表中的全部數據
<span style="white-space:pre">		</span>conn = DBUtils.getConnORCALE();
<span style="white-space:pre">		</span>getTableData(sql);


<span style="white-space:pre">	</span>}


<span style="white-space:pre">	</span>public static void getTableDataFromMysql(String sql) throws Exception {// 輸出表的列名
<span style="white-space:pre">																			</span>// 和表中的全部數據
<span style="white-space:pre">		</span>conn = DBUtils.getConnMySQL();
<span style="white-space:pre">		</span>getTableData(sql);


<span style="white-space:pre">	</span>}


<span style="white-space:pre">	</span>private static void getTableData(String sql) throws SQLException {
<span style="white-space:pre">		</span>// getTableDataFromMysql
<span style="white-space:pre">		</span>// getTableDataFromOrcale
<span style="white-space:pre">		</span>st = conn.createStatement();
<span style="white-space:pre">		</span>rs = st.executeQuery(sql);
<span style="white-space:pre">		</span>ResultSetMetaData rsmd = rs.getMetaData();
<span style="white-space:pre">		</span>int num = rsmd.getColumnCount();
<span style="white-space:pre">		</span>System.out.println("ColumnCount=" + num);
<span style="white-space:pre">		</span>String[] strs = new String[num];
<span style="white-space:pre">		</span>// 顯示列名
<span style="white-space:pre">		</span>for (int i = 1; i <= rsmd.getColumnCount(); i++) {
<span style="white-space:pre">			</span>String str = rsmd.getColumnName(i);
<span style="white-space:pre">			</span>strs[i - 1] = str;
<span style="white-space:pre">			</span>System.out.print(str + "\t");
<span style="white-space:pre">		</span>}
<span style="white-space:pre">		</span>System.out
<span style="white-space:pre">				</span>.println("\n------------------------------------------------------------------------------------------------------------------------");
<span style="white-space:pre">		</span>while (rs.next()) {
<span style="white-space:pre">			</span>for (int i = 1; i <= rsmd.getColumnCount(); i++) {
<span style="white-space:pre">				</span>System.out.print(rs.getString(i) + "\t");
<span style="white-space:pre">			</span>}
<span style="white-space:pre">			</span>System.out.println();
<span style="white-space:pre">		</span>}
<span style="white-space:pre">		</span>System.out
<span style="white-space:pre">				</span>.println("------------------------------------------------------------------------------------------------------------------------");
<span style="white-space:pre">	</span>}
}