Java连接Oracle数据库存储过程（用户财产变更举例）

JDK: 1.8.0_60

IDE: NetBeans 8.0.2

Oracle: 12c

要求：用户财产变更一个数目，记录下该变更，并且修改用户余额，原子操作。

数据库表创建：

CREATE TABLE "C##ORCL_ATM"."ACCOUNT" 
   (	"ID" VARCHAR2(19 CHAR), 
	"PWD" CHAR(6 CHAR), 
	"SEX" NCHAR(1), 
	"NAME" NVARCHAR2(20), 
	"BALANCE" NUMBER(*,0)
   )

CREATE TABLE "C##ORCL_ATM"."ASSETRECORD" 
   (	"CARDID" VARCHAR2(19 BYTE), 
	"NUM" NUMBER, 
	"TIME" TIMESTAMP (1) WITH LOCAL TIME ZONE
   )

编写存储过程，先对记录表进行插入，如果插入失败，标志返回变量为1；如果成功，修改用户余额数目，类似重置返回变量值。res为0标志执行成功！

CREATE OR REPLACE PROCEDURE CHANGEASSET 
(
  CARDID IN VARCHAR2  
, NUM IN NUMBER  
, RES OUT NUMBER  
) AS 
BEGIN
  insert into assetrecord(cardid,num,time)
  values(cardid,num,sysdate);
  if sql%rowcount=0 then
    rollback;
    res:=1;
    return;
  end if;
  
  update account set balance=balance+num
  where id=cardid;
  if sql%rowcount=0 then
    rollback;
    res:=1;
    return;
  end if;
  
  commit;
  res:=0;
  
END CHANGEASSET;

编写Java连接，进行读取：

public class DBCon {
    /* 连接 */
    private static Connection con = null;

    /* URL */
    private static final String dbUrl = "jdbc:oracle:thin:@localhost:1521:orcl";
    /* 用户名 */
    private static final String dbUser = "******";  //此处更换为登录名
    /* 密码 */
    private static final String dbPwd = "******";  //此处更换为密码


    /* 默认构造函数，实例化连接 */
    public DBCon() throws Exception {
        Class.forName("oracle.jdbc.driver.OracleDriver");
        con = DriverManager.getConnection(dbUrl, dbUser, dbPwd);
    }

    public static Connection getConnection() {
        return con;
    }

    public static void conClose() {
        try {
            con.close();
        } catch (SQLException ex) {
        } finally {
            con = null;
        }
    }
}

</pre><pre name="code" class="java">public class AssetHelper {

    /* 构造函数检查连接是否建立 */
    public AssetHelper() throws Exception {
        if (DBCon.getConnection() == null) {
            new DBCon();
        }
    }

    /* 改变用户财产 */
    public boolean changeAsset(Account account, int num) {
        String sql = "{ call C##ORCL_ATM.changeAsset(?,?,?) }";
        CallableStatement cs = null;
        try {
            cs = DBCon.getConnection().prepareCall(sql);
            cs.setString(1, account.getId());
            cs.setInt(2, num);
            cs.registerOutParameter(3, Types.INTEGER);
            cs.execute();
            return cs.getInt(3) == 0 ? true : false;
        } catch (SQLException ex) {
            return false;
        }
    }
}