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

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)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 {// 传入参数																				// 利用																				// PreparedStatemen本文来源[email protected]搞@^&代*@码)网5t																				// 实现插入		// 传入的参数是任意多个 因为有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("------------------------------------------------------------------------------------------------------------------------");	}}

喜欢 (0)赏

[搞代码]

分享 (0)