Java call MySQL stored procedure instance analysis

This article describes the Java method for invoking the MySQL stored procedure. Share to everyone for your reference. Specifically as follows:

The test code for the database is as follows:

1. New Table Test

?

1 2 3 4 5

CREATE TABLE test (field1 int not null) Type=myisam; INSERT into Test (field1) values (1);

2. Delete existing stored procedures:

?

1 2 3

--Delete Stored procedure delimiter//--Define closing 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. Calling 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 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 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 The 96 97 98 99 100 101 102 103 104 105 106 107 108 109 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-1 143 144 145 146 147 148 149, 151 152 153 154 156 157 158

CallableStatement cstmt = Conn.preparecall ("{Call P_test ()}"); Cstmt.executeupdate (); Import java.sql.*; /** Igoder/public class Proceduretest {/* tables and stored procedures are defined as follows: Delimiter//DROP table if exists test//CREATE table test (ID Int (one) NULL)/drop procedure if EXISTSSP1//CREATE PROCEDURE SP1 (in P int) Comment ' INSERT into a int value ' Begin de Clare V1 int; Set v1 = p; INSERT into test (ID) values (v1); End//drop procedure if exists SP2//CREATE PROCEDURE SP2 (out p int) to 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) {//callin ();//callout (); Callresult ();}/** * Call stored procedures with input parameters * @param In stored procedure input ParameterValue */public static void Callin (int in) {//get connection Connection conn = Connectdb.getconn Ection (); CallableStatement cs = null; try {//can be passed directly into the parameter//cs = Conn.preparecall ("{Call SP1 (1)}");//You can use a question mark instead of CS = Conn.preparecall ("{Call SP1 (?)}");Set the value of the first input parameter to Cs.setint (1, in); Cs.execute (); catch (Exception e) {e.printstacktrace ();} finally {try {if (cs!= null) {Cs.close ();} if (conn!= null) {Conn.close ( ); } catch (Exception ex) {ex.printstacktrace ();}} /** * Calls a stored procedure with an output parameter */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.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 (conn!= null) {Conn.close ( ); } catch (Exception ex) {ex.printstacktrace ();}} /** * Calls 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.executequery ();//Loop output while (Rs.next ()) {System.out.println (rs.get String (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 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 your Java programming.