-
RowSet继承自ResultSet接口,包括JdbcRowSet, CachedRowSet, FilteredRowSet, JoinRowSet和WebRowSet,其中后四个都是离线的,无需与数据库实时相连;
-
RowSet默认是可滚动、可更新、可序列化的结果集;
-
RowSet能够将底层数据库中的数据放到内存中操作,降低数据库服务器负载,这一点是向C#学习的;
1.RowSetFactory与RowSet
- RowSet的生成和操作方法:
/*
1.java7新增了RowSetProvider类和RowSetFaactory接口,RSP负责创建RSF;
2.RowSetFactory提供了如下方法来创建RowSet实例:
1.CachedRowSet createCachedRowSet();
2.FilteredRowSet createFilteredRowSet();
3.JdbcRowSet createJdbcRowSet();
4.JoinRowSet createJoinRowSet();
5.WebRowSet createWebRowSet();
*/
/*
3.为RowSet对象设置数据登录信息的方法:
setUrl(String url);
setUsername(String name);
setPassword(String password);
setCommand(String sql);
execute();
*/
import java.util.*;
import java.io.*;
import java.sql.*;
import javax.sql.rowset.*;
public class RowSetFactoryTest
{
private String driver;
private String url;
private String user;
private String pass;
public void initParam(String paramFile) throws Exception
{
//初始化数据库连接信息
var props = new Properties();
props.load(new FileInputStream(paramFile));
driver = props.getProperty("driver");
url = props.getProperty("url");
user = props.getProperty("user");
pass = props.getProperty("pass");
}
public void update(String sql) throws Exception
{
//加载驱动;
Class.forName(driver);
//创建RowSetFactory
RowSetFactory factory = RowSetProvider.newFactory();
try (
//利用RowSetFactory创建JdbcRowSet对象
JdbcRowSet jdbcRs = factory.createJdbcRowSet())
{
//给JdbcRS设置登录信息;
jdbcRs.setUrl(url);
jdbcRs.setUsername(user);
jdbcRs.setPassword(pass);
//给JdbcRS设置sql语句
jdbcRs.setCommand(sql);
//执行sql
jdbcRs.execute();
jdbcRs.afterLast(); //将数据集指针定在最后一位
//向前滚动数据集
while (jdbcRs.previous())
{
System.out.println(jdbcRs.getString(1)
+ "\t" + jdbcRs.getString(2)
+ "\t" + jdbcRs.getString(3));
if (jdbcRs.getInt("student_id") == 3)
{
//修改指定id行数据
jdbcRs.updateString("student_name", "孙悟空");
jdbcRs.updateRow();
}
}
}
}
public static void main(String[] args) throws Exception
{
var jt = new RowSetFactoryTest();
jt.initParam("mysql.ini");
jt.update("select * from student_table");
}
}
2.离线RowSet
- 在使用ResultSet的时代,数据一旦获取就要进行处理,否则当Conn关闭时,RS中的数据将会丢失;
- 离线的RowSet能够将底层数据读到内存中,封装成RowSet对象,RowSet对象则可以被当做JavaBean使用,既安全又简单;
import java.util.*;
import java.io.*;
import java.sql.*;
import javax.sql.*;
import javax.sql.rowset.*;
public class CachedRowSetTest
{
private static String driver;
private static String url;
private static String user;
private static String pass;
public void initParam(String paramFile) throws Exception
{
var props = new Properties();
props.load(new FileInputStream(paramFile));
driver = props.getProperty("driver");
url = props.getProperty("url");
user = props.getProperty("user");
pass = props.getProperty("pass");
}
public CachedRowSet query(String sql) throws Exception
{
Class.forName(driver);
Connection conn = DriverManager.getConnection(url, user, pass);
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
RowSetFactory factory = RowSetProvider.newFactory();
CachedRowSet cachedRs = factory.createCachedRowSet();
cachedRs.populate(rs); //.populate(Result set)方法装填RS给CRS使用
rs.close();
stmt.close();
conn.close();
return cachedRs;
}
public static void main(String[] args) throws Exception
{
var ct = new CachedRowSetTest();
ct.initParam("mysql.ini");
CachedRowSet rs = ct.query("select * from student_table");
rs.afterLast();
while (rs.previous())
{
System.out.println(rs.getString(1)
+ "\t" + rs.getString(2)
+ "\t" + rs.getString(3));
if (rs.getInt("student_id") == 3)
{
rs.updateString("student_name", "AYG");
rs.updateRow();
}
}
//重新获取数据库连接
Connection conn = DriverManager.getConnection(url, user, pass);
conn.setAutoCommit(false);
//把对RowSet所做的修改同步到底层数据库;
rs.acceptChanges(conn);
}
}
3.离线RowSet查询分页
- 如果SQL查询的返回记录内容过大,则会占用大量的内存,在某些极端情况下,他甚至会直接导致内存溢出;
- 为了解决这个问题,CachedRowSet提供了分页功能,一次只装载某几条内容;
/*
CachedRowSet提供了如下方法来控制分页:
populate(ResultSet rs, int startRow) 从rs的第startRow条开始装填;
setPageSize(int pageSize) 设置CachedRowSet每次返回多少条记录
previousPage() 在底层RS可用的情况下,让CachedRS读取上一页记录;
nextPage() 在底层RS可用的情况下,让CachedRS读取下一页记录;
*/
import java.util.*;
import java.io.*;
import java.sql.*;
import javax.sql.*;
import javax.sql.rowset.*;
public class CachedRowSetPage
{
private String driver;
private String url;
private String user;
private String pass;
public void initParam(String paramFile) throws Exception
{
var props = new Properties();
props.load(new FileInputStream(paramFile));
driver = props.getProperty("driver");
url = props.getProperty("url");
user = props.getProperty("user");
pass = props.getProperty("pass");
}
public CachedRowSet query(String sql, int pageSize,
int page) throws Exception
{
Class.forName(driver);
try (
Connection conn = DriverManager.getConnection(url, user, pass);
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql))
{
RowSetFactory factory = RowSetProvider.newFactory();
CachedRowSet cachedRs = factory.createCachedRowSet();
cachedRs.setPageSize(pageSize);
cachedRs.populate(rs, (page - 1) * pageSize + 1);
return cachedRs;
}
}
public static void main(String[] args) throws Exception
{
var cp = new CachedRowSetPage();
cp.initParam("mysql.ini");
CachedRowSet rs = cp.query("select * from student_table", 3, 2); // ��
while (rs.next())
{
System.out.println(rs.getString(1)
+ "\t" + rs.getString(2)
+ "\t" + rs.getString(3));
}
}
}