Getting Started with offline datasets in JDBC

If you are not using any persistence layer framework while developing the application, but using JDBC API programming directly, the longest use is the ResultSet interface for data reading, but you will find it inconvenient because ResultSet is an online dataset, You cannot disconnect a database while reading data, and you cannot close related objects until you have read the data.

In fact, Java also provides an offline dataset, which is the rowset interface and related sub-interfaces. and Sun provides a default implementation within the JDK, and also provides its own rowset implementation in a larger database drive like Oracle.

The following is the sun's default implementation to illustrate the use of offline data, the database is SQL Server 2000, and the connected database is Northwind.

import java.sql.Connection;


import Java.sql.DriverManager;


import Java.sql.ResultSet;


import java.sql.SQLException;


import java.sql.Statement;


import Javax.sql.RowSet;


import Com.sun.rowset.CachedRowSetImpl;


public class Rowsetdemo {


public static RowSet query (Connection Connection, String sql)


throws SQLException {


//Using Sun's default rowset implementation


CACHEDROWSETIMPL rowset = new Cachedrowsetimpl ();


//Query no change


Statement Statement = Connection.createstatement ();


ResultSet rs = statement.executequery (SQL);


//Here is the filling offline set


Rowset.populate (RS);


//Can be closed, cool


Rs.close ();


Statement.close ();


return rowset;


}


public static void Main (string[] args) throws Exception {


class.forname ("Com.microsoft.sqlserver.jdbc.SQLServerDriver");


String connectionurl = "jdbc:sqlserver://127.0.0.1:1433;database=northwind;


User=sa;password=huhuiyu ";


Connection Connection = drivermanager.getconnection (Connectionurl);


RowSet rs = query (connection, "SELECT * from Customers order by CustomerID;");


//Close connection is also not a matter.


Connection.close ();


//And resultset use the same.


while (Rs.next ()) {


System.out.print (rs.getstring (1) + ":");


System.out.println (rs.getstring ("CompanyName"));


}


}


} Running the above example will show the first two columns of customers data. In fact, rowset can also complete the paging function. Please see the following method.


public static RowSet query (Connection Connection, String sql, int pageSize,


int pagenumber) throws SQLException {


CACHEDROWSETIMPL rowset = new Cachedrowsetimpl ();


//If the result set can be scrolled


Statement Statement = connection.createstatement (


resultset.type_scroll_sensitive, resultset.concur_read_only);


ResultSet rs = statement.executequery (SQL);


//Set Paging size


rowset.setpagesize (pageSize);


//Calculate the start cursor position


int skip= (pageNumber-1) * pageSize + 1;


//Can be filled with


rowset.populate (RS, skip);


Rs.close ();


Statement.close ();


return rowset;


}


public static void Main (string[] args) throws Exception {


Class.forName ("Com.microsoft.sqlserver.jdbc.SQLServerDriver");


String connectionurl = "jdbc:sqlserver://127.0.0.1:1433;database=northwind;


User=sa;password=huhuiyu ";


Connection Connection = drivermanager.getconnection (Connectionurl);


//Paging query


RowSet rs = query (connection, "SELECT * from Customers order by CustomerID;", 5,2);


//Close connection is also not a matter.


Connection.close ();


//And resultset use the same.


while (Rs.next ()) {


System.out.print (rs.getstring (1) + ":");


System.out.println (rs.getstring ("CompanyName"));


}


}

If you do not use a persistence layer, then using an offline dataset can be a good solution to the problem of paging and database connectivity. Hopefully this introductory tutorial will help you.