Trail:JDBC(TM)DatabaseAccess(3)

java.sql,javax.sql,javax.naming包 ? ?默认 TYPE_FORWARD_ONLY: 结果集只能向前滚动,只能调用next(),不能重定位游标 TYPE_SCROLL_INSENSITIVE, TYPE_SCROLL_SENSITIVE:可以重定位游标 TYPE_SCROLL_INSENSITIVE:底层修改不会反映到结果集 TYPE_SCROLL_SENSIT

java.sql,javax.sql,javax.naming包

?

?默认TYPE_FORWARD_ONLY:结果集只能向前滚动,只能调用next(),不能重定位游标

TYPE_SCROLL_INSENSITIVE,TYPE_SCROLL_SENSITIVE:可以重定位游标

TYPE_SCROLL_INSENSITIVE:底层修改不会反映到结果集

TYPE_SCROLL_SENSITIVE:会实时显示真实数据

?

默认CONCUR_READ_ONLY:不能更新到底层,只是读取

CONCUR_UPDATABLE:可以更新,可以写入

?

?

stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,//可滚动,实时显示
 ResultSet.CONCUR_UPDATABLE);//可写入
 ResultSet uprs = stmt.executeQuery(
 "SELECT * FROM " + dbName + ".COFFEES");

 while (uprs.next()) {
 float f = uprs.getFloat("PRICE");
 uprs.updateFloat( "PRICE", f * percentage);//设置某列
 uprs.updateRow();//提交
 }

?

 stmt = con.createStatement(
 ResultSet.TYPE_SCROLL_SENSITIVE
 ResultSet.CONCUR_UPDATABLE);

 ResultSet uprs = stmt.executeQuery(
 "SELECT * FROM " + dbName +
 ".COFFEES");

 uprs.moveToInsertRow();//准备插入
 uprs.updateString("COF_NAME", coffeeName);
 uprs.updateInt("SUP_ID", supplierID);
 uprs.updateFloat("PRICE", price);
 uprs.updateInt("SALES", sales);
 uprs.updateInt("TOTAL", total);

 uprs.insertRow();//提交
 uprs.beforeFirst();//游标不能再指向这里

?

?

RowSet对象都是JavaBean组件

如果数据库不支持游标滚动,也不支持实时更新,可以用RowSet替代

RowSet有保持连接的和离线的两种

?

?

public void createProcedureGetSupplierOfCoffee()
 throws SQLException {

 String createProcedure = null;

 // ...

 createProcedure =
 "create procedure GET_SUPPLIER_OF_COFFEE(" +
 "IN coffeeName varchar(32), " +//in
 "OUT supplierName varchar(40)) " +//out
 "begin " +
 "select SUPPLIERS.SUP_NAME into " +
 "supplierName " +
 "from SUPPLIERS, COFFEES " +
 "where SUPPLIERS.SUP_ID = " +
 "COFFEES.SUP_ID " +
 "and coffeeName = COFFEES.COF_NAME; " +
 "select supplierName; " +
 "end";
 // ...
}

?

createProcedure =
 "create procedure RAISE_PRICE(" +
 "IN coffeeName varchar(32), " +
 "IN maximumPercentage float, " +
 "INOUT newPrice numeric(10,2)) " +//inout
 "begin " +
 "main: BEGIN " +
 "declare maximumNewPrice " +
 "numeric(10,2); " +
 "declare oldPrice numeric(10,2); " +
 "select COFFEES.PRICE into oldPrice " +
...

?

cs = this.con.prepareCall("{call GET_SUPPLIER_OF_COFFEE(?, ?)}");
cs.setString(1, coffeeNameArg);
cs.registerOutParameter(2, Types.VARCHAR);
cs.executeQuery();

String supplierName = cs.getString(2);

?

cs = this.con.prepareCall("{call RAISE_PRICE(?,?,?)}");
cs.setString(1, coffeeNameArg);
cs.setFloat(2, maximumPercentageArg);
cs.registerOutParameter(3, Types.NUMERIC);
cs.setFloat(3, newPriceArg);

cs.execute();

Because the parameter newPrice (the third parameter in the procedure

?

?