The example of this article tells the method of Java calling mysql storage procedures. Share it for everyone for your reference. The specifics are as follows:
The test code of the database is as follows:
1. New Table Test
Create Table Test (Field1 Int Not Null) Type = MyISAM; Insert Into Test (Field1) Values (1);
2. Delete the existing storage procedures:
-Wely delete the storage process Delimiter //- Define the end symbol Drop Procedure P_Test;
3. MYSQL storage procedure definition:
Create Procedure P_Test () BegindeClare Temp int; set test = 0; Update test set field1 = value (test); end
4. Calling method:
CALLESTATEMENT CSTMT = const.preparecall ("{Call P_Test ()}"); EST { /* Table and storage procedures are defined as follows: Delimiter // Drop Table If Exist Test // Create Table Test (ID Int (11) NULL) // Drop Procedure If ExistSSP1 // Create Procedure SP1 (in P int) Comment 'Insert INT VALUE' B B Egin Declare v1 int; set; set v1 = P; Insert INTO TEST (ID) value (v1); End // Drop Procedure if Exist SP2 // Create Procedure SP2 (OUT P Int) Begin Select Max (ID) Into P FROM TEST; END // OP Procedure if Exist SP6 // Create Procedure SP6 () Begin Select * From Test; END // */Public Static Void Main (String [] Args) {// callin (111); // callout (); callresult () ;} / ** * Call the storage procedure with input parameters * @Param in Stored Procedure Input Parametervalue */Public Static Void Callin (int in) {// Get connecting connection conn = Connectdb.getConn ection (); CallableStatement cs = null; try { // You can directly pass the parameters // cs = conn.preparecall ("{call sp1 (1)}"); // You can also use a question mark instead of cs = const.preparecall ("{call sp (?)}"); // Set the value of the first input parameter to 110 cs.setint (1, in); cs.execute ();} Catch (Exception E) {e.printstacktrace ();} Finally {try {if (cs! = = = null) {cs.close ();} if (con! = null) {conn.close ();}} catch (exception ex) {ex.printstacktrace (); Storage procedures */ public static void callout () {connection conn = connectdb.getConnection (); callStatement CS = null; TRY {cs = con.preparecall ("{CA ll sp2 (?)} "); // First The type of a parameter is int CS.RegisteroutParameter (1, Types.integer); cs.execute (); // Get the first value int i = cs.getint (1); System.out.println (i);} Catch (Exception E) {e.printstacktrace ();} Finally {try {if (cs! = null) {cs.close ();} if (con! = null) {conn.close ();}}} Exception EX) {ex.printstacktrace ();}} / *** Call the storage procedure of the output result set* / Public Static void callresult () {connection conn = connectdb.getConnection (); Callstatement CS = NULL; ResultSet RS = null; try {cs = conn.preparecall ("{call sp6 ()}"); rs = cs.executequry (); // The cyclic output result while (rs.next ()) {System.out.println (RS. getString (1));}} Catch (Exception E) {e.printstacktrace ();} Finally {try {if (rs! = null) {rs.close ();} if (cs! = null) {cs. Close ();} if (conn! = null) {conn.close ();}} Catch (Exception EX) {ex.printstacktrace ();}}} /***Get the class of the database connection* /Import Java .sql.connection; Import Java.sql.DriverManager; Import Java.sql.preparedStatement; Import Java.sql.ResultSet; Import java.sql.Statement; Class Connectdb {Public Static Connection GetConnection () {{ Connection conn = null; PreparedStatement PreparedStatement = NULL; TRY {Class.Forname ("Org.gjt.mm.mysql.driver"). ";;; G" "=" jdbc: MySQL: // Localhost/"+dbname+"? User = root & password = root & useunicode = true & characterencoding = 8859_1 "; );} Catch (Exception E) {e.printstacktrace ();} Return conn;}}It is hoped that this article is helpful to everyone's Java program design.