在Java中如何调用存储过程

在Java中如何调用存储过程。

数据库中存储过程一般可以分为以下几类：
1：有参数无返回值
2：有参数有返回值
3：返回结果集
account表结构如下：
1：开户存储过程：
Create proc newAccount
@account varchar(30),
@balance money
As
Insert into account values(@account,@balance)
Go
用Java 调用上述存储过程的代码如下：
package newer.test;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.io.*;
import newer.db.DBConnection;
import newer.pojo.Account;
public class TestProcedure {
private Connection con = null;
public boolean createAccount(Account account){
boolean flag = false;
try {
con = DBConnection.getConnection();
CallableStatement cstmt =con.prepareCall("{call newAccount(?,?)}");
cstmt.setString(1, account.getAccount());
cstmt.setFloat(2, account.getBalance());
if(!cstmt.execute()){
flag = true;
}
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
return flag;
}
public static void main(String[] args){
BufferedReader br = new BufferedReader(new InputStreamReader(System.in));
System.out.print("请输入新的账号：");
Account newAcc = new Account();
try{
newAcc.setAccount(br.readLine());
System.out.print("请输入存款金额：");
newAcc.setBalance(Float.parseFloat(br.readLine()));
if(new TestProcedure().createAccount(newAcc)){
System.out.println("开户成功！");
System.out.println(newAcc);
}else{
System.out.println("服务器忙，请稍后再试！谢谢！");
}
} catch (Exception e){
e.printStackTrace();
}
}
}
2：查询余额存储过程：
Create proc lookBalance
@account varchar(30),
@balance money output
As
Select @balance = balance from account where account = @account
Return @balance
Go
用Java 调用上述存储过程的代码如下：
package newer.test;
import java.io.BufferedReader;
import java.io.InputStreamReader;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Types;
import newer.db.DBConnection;
import newer.pojo.Account;
public class TestProcedureWithOne {
private Connection con = null;
public Account queryBalance(Account account) {
try {
con = DBConnection.getConnection();
CallableStatement cstmt = con
.prepareCall("{call lookBalance(?,?)}");
cstmt.setString(1, account.getAccount());//设置输入参数
cstmt.registerOutParameter(2, Types.DECIMAL);//注册输出类型的变量
cstmt.execute();
float balance = cstmt.getFloat(2); //获取存储过程的返回值
account.setBalance(balance);
cstmt.close();
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
return account;
}
public static void main(String[] args) {
BufferedReader br = new BufferedReader(new InputStreamReader(System.in));
System.out.print("请输入要查询余额的账号：");
Account qryAcc = new Account();
try {
qryAcc.setAccount(br.readLine());
System.out.println(new TestProcedureWithOne().queryBalance(qryAcc));
} catch (Exception e) {
e.printStackTrace();
}
}
}
3：返回结果集的存储过程：
Create proc getAccounts
As
Select * from account
Go
package newer.test;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import newer.db.DBConnection;
import newer.pojo.Account;
public class TestProcedureWithRess {
private Connection con = null;
private ResultSet rs = null;
public List queryAccount() {
List al = new ArrayList();
try {
con = DBConnection.getConnection();
CallableStatement cstmt = con
.prepareCall("{call getAccounts}");
cstmt.execute();
rs = cstmt.getResultSet();
while(rs.next()){
Account ac = new Account();
ac.setAccount(rs.getString(1));
ac.setBalance(rs.getFloat(2));
al.add(ac);
}
cstmt.close();
con.close();
return al;
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
public static void main(String[] args){
TestProcedureWithRess tw = new TestProcedureWithRess();
List list= tw.queryAccount();
Iterator itr = list.iterator();
while(itr.hasNext()){
System.out.println(itr.next());
}
}
}。