使用存储过程的好处就不说了,下面简要说一下存储过程的使用。
1. 首先,创建一个新表,用于后面Java代码中的测试。
create table a
(
id int(10) not null,
name varchar(20) not null
)ENGINE=MyISAM DEFAULT CHARSET=utf8;
2. 向表中插入一些数据。
insert into a ( `id`, `name` ) values ('1', 'best');
insert into a ( `id`, `name` ) values ('2', 'great');
insert into a ( `id`, `name` ) values ('3', 'china');
insert into a ( `id`, `name` ) values ('4', 'beijing');
3. 修改MySQL中的默认换行符";"为'##'。
delimiter ##
4. 创建一个带有一个输出参数的存储过程。
create procedure hello(out num int)
begin
select Max(id) into num from a;
end##
5. 创建一个带有两个输出参数的存储过程。
create procedure hello2(out num int, out str varchar(20))
begin
select Max(id) into num from a;
select name from a where a.id = num into str;
end##
6. 将MySQL的换行符修改回";"。
delimiter ;
7. Java程序中的调用。
package cn.lifx.util.procedure;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Types;
public class Test
{
String url = "jdbc:mysql://127.0.0.1:3306/test";
String name = "root";
String password = "china";
public static void main(String[] args)
{
Test test = new Test();
test.proc();
test.proc2();
}
public Connection getConnection()
{
Connection con = null;
try
{
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection(url, name, password);
}catch(Exception e){
e.printStackTrace();
}
return con;
}
public void proc()
{
Connection conn = getConnection();
CallableStatement stmt = null;
try
{
stmt = conn.prepareCall("{call hello(?)}");
stmt.registerOutParameter(1, Types.INTEGER);
stmt.execute();
int i = stmt.getInt(1);
System.out.println(i);
}catch(Exception e){
e.printStackTrace();
}finally
{
try
{
stmt.close();
conn.close();
}catch (Exception e) {
e.printStackTrace();
}
}
}
public void proc2()
{
Connection conn = getConnection();
CallableStatement stmt = null;
try
{
stmt = conn.prepareCall("{call hello2(?, ?)}");
stmt.registerOutParameter(1, Types.INTEGER);
stmt.registerOutParameter(2, Types.VARCHAR);
stmt.execute();
int i = stmt.getInt(1);
String str = stmt.getString(2);
System.out.println(i + " " + str);
}catch(Exception e){
e.printStackTrace();
}finally
{
try
{
stmt.close();
conn.close();
}catch (Exception e) {
e.printStackTrace();
}
}
}
}
8. 输出结果为:
4
4 beijing
下面的链接是这些过程在MySQL的CMD命令行中的运行过程。
http://dl.iteye.com/upload/picture/pic/44745/31622578-8500-3019-b6f2-dc3858cba641.jpg
http://dl.iteye.com/upload/picture/pic/44747/e3e5cc97-1ab3-363b-aacc-6850b71c48d7.jpg