oracle变更java协议,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;

}

}

}

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;

}

}

}