转载自 http://yoyo08.iteye.com/blog/474915
使用存储过程的好处就不说了,下面简要说一下存储过程的使用。
1. 首先,创建一个新表,用于后面Java代码中的测试。
- create table a
- (
- id int(10) not null,
- name varchar(20) not null
- )ENGINE=MyISAM DEFAULT CHARSET=utf8;
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');
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 ##
delimiter ##
4. 创建一个带有一个输出参数的存储过程。
- create procedure hello(out num int)
- begin
- select Max(id) into num from a;
- end##
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##
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 ;
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();
- }
- }
- }
- }
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