如何使用CachedRowSet进行SQL查询？

Question

我正在尝试编写一个使用CachedRowSet进行SQl查询的方法。示例代码如下(前面定义了用户和密码)，

public CachedRowSet getContentsOfCoffeesTable( Connection connection ) throws SQLException {

    CachedRowSet crs = null;

    try {

        crs = new CachedRowSetImpl();

        crs.setType(ResultSet.TYPE_SCROLL_INSENSITIVE);
        crs.setConcurrency(ResultSet.CONCUR_UPDATABLE);
        crs.setUsername(user);
        crs.setPassword(password);


        crs.setCommand("select COF_NAME, SUP_ID, PRICE, SALES, TOTAL from COFFEES");
        crs.execute();


    } catch (SQLException e) {

        e.printStackTrace();
    }
    return crs;
}

当我试图使用示例代码读取数据时，我一无所获。

CachedRowSet myValues =  getContentsOfCoffeesTable( myConn );
while( myValues != null && myValues.next() ){

     // get the values of 2nd column of the table 
     System.out.println( myValues.getString(2) );
}

咖啡桌已经有人了。如何改进代码？谢谢。

Answer 1

我找到了如何使用CachedRowSet进行SQL查询的解决方案，并对该方法进行了如下更改：

public CachedRowSet getContentsOfCoffeesTable(Connection mycConn)
    throws SQLException {

CachedRowSet crs = null;
ResultSet resultSet = null;
Statement stmt = null;
String sql = "select COF_NAME, SUP_ID, PRICE, SALES, TOTAL from COFFEES";

try {

    stmt = myConn.createStatement();
    resultSet = stmt.executeQuery(sql);

    crs = new CachedRowSetImpl();
    crs.populate(resultSet);

}

catch (Exception e) {

    e.printStackTrace();
}

return crs;
 }

然后，可以打印以下值，

// I get the Connection myConn here 
//  then pass the connection info to the method and get myValues 
// prit myValues for column index of 2 

CachedRowSet myValues =  getContentsOfCoffeesTable( myConn );
while( myValues != null && myValues.next() ){

     // get the values of 2nd column of the table 
     System.out.println( myValues.getString(2) );
}

Answer 2

我找到了另一种方法。在最初的帖子中，网址信息未按以下方式提供，

cr.setUrl(url);

其中网址的价值，

url = "jdbc:mysql://localhost:3306/myDemo"

该方法如下，

private CachedRowSet getContentsOfCoffeesTable(Connection con)
        throws SQLException {

    CachedRowSet cr = null;

    try {

        cr = new CachedRowSetImpl();
        cr.setType(ResultSet.TYPE_SCROLL_INSENSITIVE);
        cr.setConcurrency(ResultSet.CONCUR_UPDATABLE);
        cr.setUsername(username);
        cr.setPassword(password);
        cr.setUrl(url);

        cr.setCommand("select COF_NAME, SUP_ID, PRICE, SALES, TOTAL from COFFEES");
        cr.execute();

    }

    catch (Exception ex) {

        ex.printStackTrace();
    }

    return cr;
}