Trail: JDBC(TM) Database Access(3)

原创于 2014-12-04 18:27:38 发布 · 400 阅读

0 ·

CC 4.0 BY-SA版权

本文探讨了Java中SQL操作的几种结果集类型及其使用方式，包括如何通过Statement对象进行查询，并利用ResultSet对象进行数据更新及插入操作。同时介绍了RowSet作为替代方案，以及创建存储过程的方法。

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