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 <= 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("------------------------------------------------------------------------------------------------------------------------"); } }

下载本文