星期二, 2月 07, 2012

[Java] JDBC ResultSet/RowSet 取得查詢結果的總列數

常常需要得到查詢結果返回的總列數,
Java提供以下三種解法:



1.Using sql
使用SQL COUNT方法達到


// Get a record count with the SQL Statement
Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery("SELECT COUNT(*) AS rowcount FROM
emp");
rs.next();

// Get the rowcount column value.
int ResultCount = rs.getInt(rowcount) ;
rs.close() ;

2.Using JDBC Scrollable ResultSet:
需注意需將rs.last()一定要執行才有辦法透過rs.getRow()取到總列數,
如果要取重新retrieve資料集可使用rs.beforeFirst()回去!!
..............

sqlString = "SELECT * FROM emp";

// Create a scrollable ResultSet.
stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
rs = stmt.executeQuery(sqlString);

// Point to the last row in resultset.
rs.last();

// Get the row position which is also the number of rows in the ResultSet.
int rowcount = rs.getRow();

System.out.println("Total rows for the query: "+rowcount);


// Reposition at the beginning of the ResultSet to take up rs.next() call.
rs.beforeFirst();
................

3.Using Oracle JDBC Cached RowSet

.........................
ResultSet rs = null;
........................

// Create and initialize Cached RowSet object.
OracleCachedRowSet ocrs = new OracleCachedRowSet();

// Create a string that has the SQL statement that gets all the records.
String sqlString = "SELECT empno FROM emp";


// Create a statement, resultset objects.
stmt = conn.createStatement();
rs = stmt.executeQuery(sqlString);

// Populate the Cached RowSet using the above Resultset.
ocrs.populate(rs);

// Point to the last row in Cached RowSet.
ocrs.last();

// Get the row position which is also the number of rows in the Cached
// RowSet.
int rowcount = ocrs.getRow();

System.out.println("Total rows for the query using Cached RowSet: "+
rowcount);

// Close the Cached Rowset object.

if (ocrs != null)
ocrs.close();


Reference:
How to return the row count of a query using JDBC ResultSet/RowSet

沒有留言:

張貼留言

留個話吧:)

其他你感興趣的文章

Related Posts with Thumbnails