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

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

欢迎大家阅读《mysql创造 存储过程 并通过java程序调用该存储过程_mysql》,跪求各位点评，by 搞代码

喜欢 (0)赏

[搞代码]

分享 (0)