mysql 创建存储过程 java程序调用该存储过程

亦凉 2022-02-04 00:59 345阅读 0赞

CREATE TABLE SCOTT.USERS_NING 
       (	ID NUMBER, 
    	PWD NUMBER
       );
     
    insert into users_ning values(1,1234);
    insert into users_ning values(123,1234);
      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 
    			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("------------------------------------------------------------------------------------------------------------------------");
    	}
    }