欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

mysql创造 存储过程 并通过java程序调用该存储过程

程序员文章站 2022-04-20 19:01:36
...

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 in

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;
  }

}




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