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
CREATE TABLE Test (
field1 int not null
)
Type=myisam;
INSERT into Test (field1) values (1);
2. Delete existing stored procedures:
--Delete stored procedure
delimiter//--Define closing symbol
drop procedure p_test;
3. mysql stored procedure definition:
CREATE PROCEDURE p_test ()
begin
Declare temp int;
Set temp = 0;
Update Test Set field1 = VALUES (temp);
End
4. Calling method:
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 DECLARE 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 (111);
CallOut ();
Callresult (); /** * Calls stored procedures with input parameters * @param in stored procedure input parametervalue/public static void Callin (int in) {//
Get connection Connection conn = Connectdb.getconnection ();
CallableStatement cs = null; try {//can be passed directly to the parameter//cs = conN.preparecall ("{Call SP1 (1)}");
You can also 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.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 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.