Mysql creates a stored procedure and calls it through a java program _ MySQL

Mysql creates a stored procedure and calls it through a java program

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) BEGINDECLAREv_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; /*** test the jdbc api call process * @ author tarena **/public class ProcedureDemo2 {/*** @ param args * @ throws Exception */public static void main (String [] args) throws Exception {System. out. println (login (123,123 4);}/*** call process to implement the logon function * @ param id Candidate id * @ param pwd test password * @ return if success: 1; if the password is incorrect: 0; if no User:-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); // pass the input parameter stmt. setInt (1, id); stmt. setInt (2, pwd); // register the output parameter. The data type of the third placeholder is integer stmt. registerOutParameter (3, Types. INTEGER); // ***** // stmt.exe cute () during execution; // Obtain the output parameter flag = stmt after execution. 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. propertie S; 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 {// Connect to mysql and return conngetUrlUserNamePassWordClassNameMySQL (); conn = DriverManager. get Connection (url, username, password); // conn. setAutoCommit (false); set automatic submission to falsereturn conn;} public static Connection getConnORCALE () throws Exception {// Connect to orcale // return conngetUrlUserNamePassWordClassNameORCALE (); conn = DriverManager. getConnection (url, username, password); // conn. setAutoCommit (false); return conn;} private static void getUrlUserNamePassWordClassNameORCALE () throws Exception {// obtain from the resource file Orcale username password url and other information 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 {// Obtain information such as mysql username password url from the resource file 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 {// read the content of the resource file Properties pro = new Properties (); pro. load (new FileInputStream (fil E); 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 {// close all 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 {// use id to query the result st = conn. createStatement (); rs = st.exe cuteQuery ("select * from" + tableName + "where id =" + id + ""); return rs ;} public static ResultSet getByAll (String SQL, Object... obj) throws Exception {// keyword can be used to query any SQL = SQL keyword. replaceAll (";", ""); SQL = SQL. trim (); stmt = conn. prepareStatement (SQL); String [] strs = SQL. split ("//? "); // Set SQL? Non-open int num = strs. length; // get? Int size = obj. length; for (int I = 1; I <= size; I ++) {stmt. setObject (I, obj [I-1]); // array subscript starts from 0} if (size <num) {for (int k = size + 1; k <= num; k ++) {stmt. setObject (k, null); // array subscript starts from 0} rs = stmt.exe cuteQuery (); return rs;} public static void doInsert (String SQL) throws SQLException {// input an SQL statement // implement the insert operation st = conn.createStatement();st.exe cute (SQL);} public static void doInsert (String SQL, Objec T... args) throws Exception {// input parameters // use // PreparedStatement // to insert // any number of input parameters because there are objects... Argsint size = args. length; // Get Object... number of parameters passed by obj stmt = conn. prepareStatement (SQL); for (int I = 1; I <= size; I ++) {stmt. setObject (I, args [I-1]); // The number of groups marked from 0 start stmt.exe cute ();} public static int doUpdate (String SQL) throws Exception {// input SQL to implement the update operation st = conn. createStatement (); int num = st.exe cuteUpdate (SQL); return num;} public static void doUpdate (String SQL, Object... obj) throws Exception {/ /Use PreparedStatement to update input parameters. // any number of input parameters are subject to Object... Argsint size = obj. length; // Get Object... number of parameters passed by obj stmt = conn. prepareStatement (SQL); for (int I = 1; I <= size; I ++) {stmt. setObject (I, obj [I-1]); // The number of groups of lower marks starting from 0: stmt.exe cuteUpdate (SQL);} public static boolean doDeleteById (String tableName, int id) throws SQLException {// delete record by idst = conn. createStatement (); boolean B = st.exe cute ("delete from" + tableName + "where id =" + id + ""); return B;} publi C static boolean doDeleteByAll (String SQL, Object... args) throws SQLException {// you can delete records by any keyword SQL = SQL. replaceAll (";", ""); SQL = SQL. trim (); stmt = conn. prepareStatement (SQL); String [] strs = SQL. split ("//? "); // Set SQL? Non-open int num = strs. length; // get? Int size = args. length; for (int I = 1; I <= size; I ++) {stmt. setObject (I, args [I-1]); // array subscript starts from 0} if (size <num) {for (int k = size + 1; k <= num; k ++) {stmt. setObject (k, null); // array subscript starts from 0} boolean B = stmt.exe cute (); return B;} public static void getMetaDate () throws Exception {// Get database element data 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 {// return all column names in the table. conn = DBUtils. getConnORCALE (); st = conn. createStatement (); rs = st.exe cuteQuery (SQL); ResultSetMetaData rsmd = rs. getMetaData (); int num = rsmd. getColumnCount (); System. out. println ("ColumnCount =" + num); String [] strs = new String [num]; // display the column name 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 {// data conn = DBUtils in the output table. getConnMySQL (); getColumnData (SQL);} public static void getColumnDataFromORCALEL (String SQL) throws Exception {// data conn = DBUtils in the output table. getConnORCALE (); getColumnData (SQL);} public static void getColumnData (String SQL) throws Exception {// data st = conn in the output table. createStatement (); rs = st.exe cuteQuery (SQL); ResultSetMetaData rsmd = rs. getMetaData (); System. out. println ("/n records"); while (rs. next () {for (int I = 1; I <= rsmd. getColumnCount (); I ++) {System. out. print (rs. getString (I) + "/t");} System. out. println ();} System. out. println ("inline");} public static void getTableDataFromOrcale (String SQL) throws Exception {// name of the output table column // and all data in the table conn = DBUtils. getConnORCALE (); getTableData (SQL);} public static void getTableDataFromMysql (String SQL) throws Exception {// name of the output table column // and all data in the table conn = DBUtils. getConnMySQL (); getTableData (SQL);} private static void getTableData (String SQL) throws SQLException {// getTableDataFromMysql // getTableDataFromOrcalest = conn. createStatement (); rs = st.exe cuteQuery (SQL); ResultSetMetaData rsmd = rs. getMetaData (); int num = rsmd. getColumnCount (); System. out. println ("ColumnCount =" + num); String [] strs = new String [num]; // display the column name 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 records"); while (rs. next () {for (int I = 1; I <= rsmd. getColumnCount (); I ++) {System. out. print (rs. getString (I) + "/t");} System. out. println ();} System. out. println ("success ("------------------------------------------------------------------------------------------------------------------------");}}