Java calls mysql Stored Procedure instance analysis

Java calls mysql Stored Procedure instance analysis

This example describes how java calls the mysql stored procedure. Share it with you for your reference. The details are as follows:

The database test code is as follows:

1. Create a table named test

?

1 2 3 4 5

Create table test ( Field1 int not null ) TYPE = MyISAM; Insert into test (field1) values (1 );

2. delete an existing stored procedure:

?

1 2 3

-- Delete the Stored Procedure Delimiter // -- Define the end symbol Drop procedure p_test;

3. mysql Stored Procedure Definition:

?

1 2 3 4 5 6

Create procedure p_test () Begin Declare temp int; Set temp = 0; Update test set field1 = values (temp ); End

4. Call method:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158

CallableStatement cStmt = conn. prepareCall ("{call p_test ()}"); CStmt.exe cuteUpdate (); Import java. SQL .*; /** IGoder */ Public class ProcedureTest { /* The table and stored procedure are defined as follows: Delimiter // Drop table if exists test // Create table test ( Id int (11) NULL )// Drop procedure if existssp1 // Create procedure sp1 (in p int) Comment 'insert into a int value' Begin Declare v1 int; Set v1 = p; Insert into test (id) values (v1 ); End // Drop procedure if exists sp2 // Create procedure sp2 (out p int) Begin Select max (id) into p from test; End // Drop procedure if exists sp6 // Create procedure sp6 () Begin Select * from test; End // */ Public static void main (String [] args ){ // Call in (111 ); // CallOut (); CallResult (); } /** * Call a stored procedure with input parameters * @ Param in stored procedure input parametervalue */ Public static void callIn (int in ){ // Obtain the connection Connection conn = ConnectDb. getConnection (); CallableStatement cs = null; Try { // You can directly input parameters. // Cs = conn. prepareCall ("{call sp1 (1 )}"); // You can also use question marks instead. Cs = conn. prepareCall ("{call sp1 (?)} "); // Set the value of the first input parameter to 110. Cs. setInt (1, in ); Cs.exe cute (); } Catch (Exception e ){ E. printStackTrace (); } Finally { Try { If (cs! = Null ){ Cs. close (); } If (conn! = Null ){ Conn. close (); } } Catch (Exception ex ){ Ex. printStackTrace (); } } } /** * Call a stored procedure with output parameters * */ Public static void callOut (){ Connection conn = ConnectDb. getConnection (); CallableStatement cs = null; Try { Cs = conn. prepareCall ("{call sp2 (?)} "); // The type of the first parameter is Int. Cs. registerOutParameter (1, Types. INTEGER ); Cs.exe cute (); // Obtain 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 (conn! = Null ){ Conn. close (); } } Catch (Exception ex ){ Ex. printStackTrace (); } } } /** * Call the stored procedure of the output result set */ Public static void callResult (){ Connection conn = ConnectDb. getConnection (); CallableStatement cs = null; ResultSet rs = null; Try { Cs = conn. prepareCall ("{call sp6 ()}"); Rs = cs.exe cuteQuery (); // Cyclically output results 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 (); } } } } /** * Obtain the database connection class */ Import java. SQL. Connection; Import java. SQL. DriverManager; Import java. SQL. PreparedStatement; Import java. SQL. ResultSet; Import java. SQL. SQLException; Import java. SQL. Statement; Class ConnectDb { Public static Connection getConnection (){ Connection conn = null; PreparedStatement preparedstatement = null; Try { Class. forName ("org. gjt. mm. mysql. Driver"). newInstance (); String dbname = "test "; String url = "jdbc: mysql: // localhost/" + dbname + "? User = root & password = root & useUnicode = true & characterEncoding = 8859_1 "; Conn = DriverManager. getConnection (url ); } Catch (Exception e ){ E. printStackTrace (); } Return conn; } }

I hope this article will help you with java programming.