时间：2021-07-01 10:21:17 帮助过：4人阅读
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.36</version>
</dependency>注:
ResultSet参数columnIndex索引从1开始,而不是0!
JDBC规定: 驱动类在被加载时,需要主动把自己注册到DriverManger中:
public class Driver extends NonRegisteringDriver implements java.sql.Driver {
//
// Register ourselves with the DriverManager
//
static {
try {
java.sql.DriverManager.registerDriver(new Driver());
} catch (SQLException E) {
throw new RuntimeException("Can‘t register driver!");
}
}
/**
* Construct a new driver and register it with DriverManager
*
* @throws SQLException
* if a database error occurs.
*/
public Driver() throws SQLException {
// Required for Class.forName().newInstance()
}
}代码显示:只要去加载com.mysql.jdbc.Driver类那么就会执行static块, 从而把com.mysql.jdbc.Driver注册到DriverManager中.
java.sql.DriverManager是用于管理JDBC驱动的服务类,其主要功能是获取Connection对象:
1. static Connection getConnection(String url, Properties info)
2. static Connection getConnection(String url, String user, String password)
另: 还可以在获取
Connection的URL中设置参数,如: jdbc:mysql://host:port/database?useUnicode=true&characterEncoding=UTF8
useUnicode=true&characterEncoding=UTF8指定连接数据库的过程中使用Unicode字符集/UTF-8编码;
java.sql.Connection代表数据库连接,每个Connection代表一个物理连接会话, 该接口提供如下创建Statement的方法, 只有获取Statement之后才可执行SQL语句:
| 方法 | 描述 |
|---|---|
Statement createStatement() |
Creates a Statement object for sending SQL statements to the database. |
PreparedStatement prepareStatement(String sql) |
Creates a PreparedStatement object for sending parameterized SQL statements to the database. |
CallableStatement prepareCall(String sql) |
Creates a CallableStatement object for calling database stored procedures. |
其中Connection还提供了如下控制事务/保存点的方法:
| 方法 | 描述 |
|---|---|
Savepoint setSavepoint(String name) |
Creates a savepoint with the given name in the current transaction and returns the new Savepoint object that represents it. |
void setTransactionIsolation(int level) |
Attempts to change the transaction isolation level(事务隔离级别) for this Connection object to the one given. |
void setAutoCommit(boolean autoCommit) |
Sets this connection’s auto-commit mode to the given state. |
void rollback() |
Undoes all changes made in the current transaction and releases any database locks currently held by this Connection object. |
void rollback(Savepoint savepoint) |
Undoes all changes made after the given Savepoint object was set. |
void commit() |
Makes all changes made since the previous commit/rollback permanent and releases any database locks currently held by this Connection object. |
以上方法还存在不同的重载形式, 详细可参考JDK文档.
由于获取Connection的步骤单一,每次可能只是加载的参数不同,因此我们可以将获取Connection的操作封装成一个方法,并使其从配置文件中加载配置:
## Data Source
mysql.driver.class=com.mysql.jdbc.Driver
mysql.url=jdbc:mysql://host:port/database
mysql.user=admin
mysql.password=admin/**
* @author jifang
* @since 16/2/19 上午10:40.
*/
public class ConnectionManger {
/*获取原生Connection*/
public static Connection getConnection(String file) {
Properties config = SQLUtil.loadConfig(file);
try {
Class.forName(config.getProperty("mysql.driver.class"));
String url = config.getProperty("mysql.url");
String username = config.getProperty("mysql.user");
String password = config.getProperty("mysql.password");
return DriverManager.getConnection(url, username, password);
} catch (SQLException | ClassNotFoundException e) {
throw new RuntimeException(e);
}
}
}/**
* @author jifang
* @since 16/2/18 上午8:24.
*/
public class SQLUtil {
/**
* 加载.properties配置文件
*
* @param file
* @return
*/
public static Properties loadConfig(String file) {
Properties properties = new Properties();
try {
properties.load(ClassLoader.getSystemResourceAsStream(file));
return properties;
} catch (IOException e) {
throw new RuntimeException(e);
}
}
}前面通过DriverManger获得Connection, 一个Connection对应一个实际的物理连接,每次操作都需要打开物理连接, 使用完后立即关闭;这样频繁的打开/关闭连接会造成不必要的数据库系统性能消耗.
数据库连接池提供的解决方案是:当应用启动时,主动建立足够的数据库连接,并将这些连接组织成连接池,每次请求连接时,无须重新打开连接,而是从池中取出已有连接,使用完后并不实际关闭连接,而是归还给池.
JDBC数据库连接池使用javax.sql.DataSource表示, DataSource只是一个接口, 其实现通常由服务器提供商(如WebLogic, WebShere)或开源组织(如DBCP,C3P0和HikariCP)提供.
Tomcat默认使用的是DBCP连接池,但相比之下,C3P0则比DBCP更胜一筹(Hibernate推荐使用C3P0),C3P0不仅可以自动清理不再使用的Connection, 还可以自动清理Statement/ResultSet, 使用C3P0需要在pom.xml中添加如下依赖:
<dependency>
<groupId>com.mchange</groupId>
<artifactId>c3p0</artifactId>
<version>0.9.5.2</version>
</dependency>
<dependency>
<groupId>com.mchange</groupId>
<artifactId>mchange-commons-java</artifactId>
<version>0.2.11</version>
</dependency>public class ConnectionManger {
/*双重检测锁保证DataSource单例*/
private static DataSource dataSource;
/*获取DataSource*/
public static DataSource getDataSourceC3P0(String file) {
if (dataSource == null) {
synchronized (ConnectionManger.class) {
if (dataSource == null) {
Properties config = SQLUtil.loadConfig(file);
try {
ComboPooledDataSource source = new ComboPooledDataSource();
source.setDriverClass(config.getProperty("mysql.driver.class"));
source.setJdbcUrl(config.getProperty("mysql.url"));
source.setUser(config.getProperty("mysql.user"));
source.setPassword(config.getProperty("mysql.password"));
// 设置连接池最大连接数
source.setMaxPoolSize(Integer.valueOf(config.getProperty("pool.max.size")));
// 设置连接池最小连接数
source.setMinPoolSize(Integer.valueOf(config.getProperty("pool.min.size")));
// 设置连接池初始连接数
source.setInitialPoolSize(Integer.valueOf(config.getProperty("pool.init.size")));
// 设置连接每次增量
source.setAcquireIncrement(Integer.valueOf(config.getProperty("pool.acquire.increment")));
// 设置连接池的缓存Statement的最大数
source.setMaxStatements(Integer.valueOf(config.getProperty("pool.max.statements")));
// 设置最大空闲时间
source.setMaxIdleTime(Integer.valueOf(config.getProperty("pool.max.idle_time")));
dataSource = source;
} catch (PropertyVetoException e) {
throw new RuntimeException(e);
}
}
}
}
return dataSource;
}
/*获取Connection*/
public static Connection getConnectionC3P0(String file) {
return getConnection(getDataSourceC3P0(file));
}
public static Connection getConnection(DataSource dataSource) {
try {
return dataSource.getConnection();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
// ...
}C3P0还可以使用配置文件来初始化连接池(配置文件可以是properties/XML, 在此仅介绍XML),C3P0配置文件名必须为c3p0-config.xml,其放在类路径下:
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<default-config>
<property name="jdbcUrl">jdbc:mysql://host:port/database</property>
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="user">user</property>
<property name="password">password</property>
<property name="acquireIncrement">5</property>
<property name="initialPoolSize">10</property>
<property name="minPoolSize">3</property>
<property name="maxPoolSize">20</property>
</default-config>
<named-config name="mysql-config">
<property name="jdbcUrl">jdbc:mysql://host:port/common</property>
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="user">user</property>
<property name="password">password</property>
<property name="acquireIncrement">5</property>
<property name="initialPoolSize">10</property>
<property name="minPoolSize">3</property>
<property name="maxPoolSize">20</property>
</named-config>
</c3p0-config>这样, 我们在创建ComboPooledDataSource时就默认加载配置文件中的配置, 无须手动配置:
public static DataSource getDataSourceC3P0(String file) {
if (dataSource == null) {
synchronized (ConnectionManger.class) {
if (dataSource == null) {
dataSource = new ComboPooledDataSource();
}
}
}
return dataSource;
}C3P0配置文件可以配置多个连接信息, 并为每个配置命名, 这样可以方便的通过配置名称来切换配置信息:
public static DataSource getDataSourceC3P0(String file) {
if (dataSource == null) {
synchronized (ConnectionManger.class) {
if (dataSource == null) {
dataSource = new ComboPooledDataSource("mysql-config");
}
}
}
return dataSource;
}其他关于C3P0的详细内容, 可参考C3P0主页.
HikariCP是另一款高性能/”零开销”/高品质的数据库连接池,据测试,其性能优于C3P0(详细信息可参考号称性能最好的JDBC连接池:HikariCP),但国内HikariCP资料不多,其项目主页为https://github.com/brettwooldridge/HikariCP,使用HikariCP需要在pom.xml中添加如下依赖:
<dependency>
<groupId>com.zaxxer</groupId>
<artifactId>HikariCP</artifactId>
<version>2.4.0</version>
</dependency>HikariCP用方法获取Connection的方法与C3P0大同小异:
public static DataSource getDataSourceHikari(String file) {
if (dataSource == null) {
synchronized (ConnectionManger.class) {
if (dataSource == null) {
Properties properties = SQLUtil.loadConfig(file);
HikariConfig config = new HikariConfig();
config.setDriverClassName(properties.getProperty("mysql.driver.class"));
config.setJdbcUrl(properties.getProperty("mysql.url"));
config.setUsername(properties.getProperty("mysql.user"));
config.setPassword(properties.getProperty("mysql.password"));
// 设置连接池最大连接数
config.setMaximumPoolSize(Integer.valueOf(properties.getProperty("pool.max.size")));
// 设置连接池最少连接数
config.setMinimumIdle(Integer.valueOf(properties.getProperty("pool.min.size")));
// 设置最大空闲时间
config.setIdleTimeout(Integer.valueOf(properties.getProperty("pool.max.idle_time")));
// 设置连接最长寿命
config.setMaxLifetime(Integer.valueOf(properties.getProperty("pool.max.life_time")));
dataSource = new HikariDataSource(config);
}
}
}
return dataSource;
}
public static Connection getConnectionHikari(String file) {
return getConnection(getDataSourceHikari(file));
}附:
1. ConnectionManger与SQLUtil完整代码地址;
2. properties文件形式如下:
## Data Source
mysql.driver.class=com.mysql.jdbc.Driver
mysql.url=jdbc:mysql://host:port/database
mysql.user=user
mysql.password=password
pool.max.size=20
pool.min.size=3
pool.init.size=10
pool.max.statements=180
pool.max.idle_time=60
pool.max.life_time=1000java.sql.Statement可用于执行DDL/DML/DCL语句:
| 方法 | 描述 |
|---|---|
boolean execute(String sql) |
Executes the given SQL statement, which may return multiple results. |
ResultSet executeQuery(String sql) |
Executes the given SQL statement, which returns a single ResultSet object. |
int executeUpdate(String sql) |
Executes the given SQL statement, which may be an INSERT, UPDATE, or DELETE statement or an SQL statement that returns nothing, such as an SQL DDL statement. |
int[] executeBatch() |
Submits a batch of commands to the database for execution and if all commands execute successfully, returns an array of update counts. |
Java 1.7还新增了closeOnCompletion()方法,当所有依赖于当前Statement的ResultSet关闭时,该Statement自动关闭.
Statement使用executeUpdate方法执行DDL/DML(不包含select)语句:执行DDL该方法返回0; 执行DML返回受影响的记录数.
@Test
public void ddlClient() throws SQLException {
try (
Connection connection = ConnectionManger.getConnectionHikari("common.properties");
Statement statement = connection.createStatement()
) {
int res = statement.executeUpdate("CREATE TABLE t_ddl(" +
"id INT auto_increment PRIMARY KEY, " +
"username VARCHAR(64) NOT NULL, " +
"password VARCHAR (36) NOT NULL " +
")");
System.out.println(res);
}
}@Test
public void dmlClient() throws SQLException {
try (
Connection connection = ConnectionManger.getConnectionHikari("common.properties");
Statement statement = connection.createStatement()
) {
int res = statement.executeUpdate("INSERT INTO " +
"t_ddl(username, password) " +
"SELECT name, password FROM user");
System.out.println(res);
}
}execute方法几乎可以执行任何SQL语句,但较为繁琐(除非不清楚SQL语句类型,否则不要使用execute方法).该方法返回值为boolean,代表执行该SQL语句是否返回ResultSet,然后Statement提供了如下方法来获取SQL执行的结果:
| 方法 | 描述 |
|---|---|
ResultSet getResultSet() |
Retrieves the current result as a ResultSet object. |
int getUpdateCount() |
Retrieves the current result as an update count; if the result is a ResultSet object or there are no more results, -1 is returned. |
public class SQLUtil {
// ...
public static void executeSQL(Statement statement, String sql) {
try {
// 如果含有ResultSet
if (statement.execute(sql)) {
ResultSet rs = statement.getResultSet();
ResultSetMetaData meta = rs.getMetaData();
int columnCount = meta.getColumnCount();
for (int i = 1; i <= columnCount; ++i) {
System.out.printf("%s\t", meta.getColumnName(i));
}
System.out.println();
while (rs.next()) {
for (int i = 1; i <= columnCount; ++i) {
System.out.printf("%s\t", rs.getObject(i));
}
System.out.println();
}
} else {
System.out.printf("该SQL语句共影响%d条记录%n", statement.getUpdateCount());
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}@Test
public void executeClient() throws SQLException {
try(
Connection connection = SQLUtil.getConnection("common.properties");
Statement statement = connection.createStatement()
){
SQLUtil.executeSQL(statement, "UPDATE t_ddl SET username = ‘feiqing‘");
SQLUtil.executeSQL(statement, "SELECT * FROM t_ddl");
}
}PreparedStatement是Statement的子接口, 它可以预编译SQL语句,编译后的SQL模板被存储在PreparedStatement对象中,每次使用时首先为SQL模板设值,然后执行该语句(因此使用PreparedStatement效率更高).
创建PreparedStatement需要使用Connection的prepareStatement(String sql)方法,该方法需要传入SQL模板,可以包含占位符参数:
PreparedStatement statement = connection.prepareStatement("INSERT INTO t_ddl(username, password) VALUES (?, ?)")PreparedStatement也提供了excute等方法来执行SQL语句, 只是这些方法无须传入参数, 因为SQL语句已经存储在PreparedStatement对象中.
由于执行SQL前需要为SQL模板传入参数值,PreparedStatement提供了一系列的setXxx(int parameterIndex, X x)方法;另外,如果不清楚SQL模板各参数的类型,可以使用setObject(int parameterIndex, Object x)方法传入参数, 由PreparedStatement来负责类型转换.
@Test
public void comparisonPrepared() throws SQLException {
Connection connection = null;
try {
connection = SQLUtil.getConnection("common.properties");
long start = System.currentTimeMillis();
try (Statement statement = connection.createStatement()) {
for (int i = 0; i < 1000; ++i) {
statement.executeUpdate("INSERT INTO t_ddl(username, password) VALUES (‘name" + i + "‘,‘password" + i + "‘)");
}
}
long mid = System.currentTimeMillis();
try (PreparedStatement statement = connection.prepareStatement("INSERT INTO t_ddl(username, password) VALUES (?, ?)")) {
for (int i = 0; i < 1000; ++i) {
statement.setString(1, "name" + i);
statement.setObject(2, "password" + i);
statement.execute();
}
}
long end = System.currentTimeMillis();
System.out.printf("Statement: %d%n", mid - start);
System.out.printf("Prepared: %d%n", end - mid);
} finally {
try {
assert connection != null;
connection.close();
} catch (SQLException e) {
}
}
}注意: SQL语句的占位符参数只能代替普通值, 不能代替表名/列名等数据库对象, 更不能代替INSERT/SELECT等关键字.
使用PreparedStatement还有另外一个优点:使用PreparedStatement无须拼接SQL字符串,因此可以防止SQL注入(关于SQL注入的问题可参考SQL Injection, 现代的ORM框架都解决了该问题).
注:
1. 默认使用PreparedStatement是没有开启预编译功能的,需要在URL中给出useServerPrepStmts=true参数来开启此功能;
2. 当使用不同的PreparedStatement对象来执行相同SQL语句时,还是会出现编译两次的现象,这是因为驱动没有缓存编译后的函数key,导致二次编译.如果希望缓存编译后的函数key,那么就要设置cachePrepStmts=true参数.
3. 另外, 还可以设置预编译缓存的大小:cachePrepStmts=true&prepStmtCacheSize=50&prepStmtCacheSqlLimit=300`
jdbc:mysql://host:port/database?useServerPrepStmts=true&cachePrepStmts=true&prepStmtCacheSize=50&prepStmtCacheSqlLimit=300
在数据库中创建一个简单的存储过程add_pro:
mysql> delimiter //
mysql> CREATE PROCEDURE add_pro(a INT, b INT, OUT sum INT)
-> BEGIN
-> SET sum = a + b;
-> END
-> //
mysql> delimiter ;
delimiter //会将SQL语句的结束符改为//, 这样就可以在创建存储过程时使用;作为分隔符. MySQL默认使用;作为SQL结束符.
调用存储过程需要使用CallableStatement,可以通过Connection的prepareCall()方法来创建,创建时需要传入调用存储过程的SQL语句,形式为:
{CALL procedure_name(?, ?, ?)}存储过程的参数既有入参,也有回参; 入参可通过setXxx(int parameterIndex/String parameterName, X x)方法传入;回参可以通过调用registerOutParameter(int parameterIndex, int sqlType)来注册, 经过上面步骤, 就可以调用execute()方法来调用该存储过程, 执行结束, 则可通过getXxx(int parameterIndex/String parameterName)方法来获取指定回参的值:
@Test
public void callableClient() throws SQLException {
try (
Connection connection = SQLUtil.getConnection("common.properties");
CallableStatement statement = connection.prepareCall("{CALL add_pro(?, ?, ?)}")
) {
// statement.setInt("a", 1);
statement.setInt(1, 11);
// statement.setInt("b", 2);
statement.setInt(2, 22);
// 注册CallableStatement回参
statement.registerOutParameter(3, Types.INTEGER);
// 执行存储过程
statement.execute();
// statement.getInt(3);
System.out.printf("存储过程执行结果为: %d%n", statement.getInt("sum"));
}
}JDBC使用ResultSet封装查询结果,然后通过ResultSet的记录指针来读取/更新记录.并提供了ResultSetMetaDate来获得ResultSet对象的元数据信息.
java.sql.ResultSet是结果集对象,可以通过列索引/列名来读/写数据, 它提供了如下常用方法来移动记录指针:
| 方法 | 描述 |
|---|---|
boolean next() |
Moves the cursor froward one row from its current position. |
boolean previous() |
Moves the cursor to the previous row in this ResultSet object. |
boolean first() |
Moves the cursor to the first row in this ResultSet object. |
boolean last() |
Moves the cursor to the last row in this ResultSet object. |
void beforeFirst() |
Moves the cursor to the front of this ResultSet object, just before the first row. |
void afterLast() |
Moves the cursor to the end of this ResultSet object, just after the last row. |
boolean absolute(int row) |
Moves the cursor to the given row number in this ResultSet object. |
boolean relative(int rows) |
Moves the cursor a relative number of rows, either positive or negative. |
当把记录指针定位到指定行后, ResultSet可通过getXxx(int columnIndex/String columnLabel)方法来获得指定类型值.或使用<T> T getObject(int columnIndex/String columnLabel, Class<T> type)来获取任意类型值.
以默认方式打开的ResultSet是不可更新的,获得可更新的ResultSet,需要在创建Statement/PreparedStatement时传入如下两个参数:
ResultSet可移动方向ResultSet.TYPE_FORWARD_ONLY |
The constant indicating the type for a ResultSet object whose cursor may move only forward. |
|---|---|
ResultSet.TYPE_SCROLL_INSENSITIVE |
The constant indicating the type for a ResultSet object that is scrollable but generally not sensitive to changes to the data that underlies the ResultSet. |
ResultSet.TYPE_SCROLL_SENSITIVE |
The constant indicating the type for a ResultSet object that is scrollable and generally sensitive to changes to the data that underlies the ResultSet. |
ResultSet的读/写并发类型ResultSet.CONCUR_READ_ONLY |
The constant indicating the concurrency mode for a ResultSet object that may NOT be updated. |
|---|---|
ResultSet.CONCUR_UPDATABLE |
The constant indicating the concurrency mode for a ResultSet object that may be updated. |
另外可更新的结果集还必须满足如下条件:
1) 所有数据都来自一个表; 2)选出的数据集必须包含主键列;
这样, 获取的ResultSet就是可更新/可滚动的, 程序可通过调用ResultSet的updateXxx(int columnIndex/String columnLabel, X x)方法来修改记录指针所指向的值, 最后调用updateRow()来提交修改.
public class SQLClient {
private Connection connection = null;
@Before
public void setUp() {
connection = ConnectionManger.getConnectionHikari("common.properties");
}
@Test
public void updateResultSet() throws SQLException {
// 创建可更新,底层数据敏感的Statement
try (
PreparedStatement statement = connection.prepareStatement("SELECT * FROM t_ddl where id IN(?, ?)",
ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE)
) {
statement.setInt(1, 19);
statement.setInt(2, 89);
ResultSet rs = statement.executeQuery();
while (rs.next()) {
System.out.printf("%s\t%s\t%s\t%n", rs.getInt(1), rs.getString(2), rs.getString(3));
if (rs.getString("username").equals("name18")) {
rs.updateString("username", "new_name_18");
rs.updateRow();
} else if (rs.getString("username").equals("name88")) {
rs.updateString("username", "new_name_88");
rs.updateRow();
}
}
SQLUtil.displayResultSet(rs, 3);
}
}
@After
public void tearDown() {
try {
connection.close();
} catch (SQLException ignored) {
}
}
}public static void displayResultSet(ResultSet result, int column) {
try {
result.beforeFirst();
while (result.next()) {
for (int i = 1; i <= column; ++i) {
System.out.printf("%s\t", result.getObject(i));
}
System.out.printf("%s%n", result.getObject(column));
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}ResultSet提供了getMetaData()方法来获取ResultSetMetaData以分析关于ResultSet的描述信息(前面我们已经使用ResultSetMetaData来获取结果集的列数以及列名):
| ResultSetMetaData方法 | 描述 |
|---|---|
int getColumnCount() |
Returns the number of columns in this ResultSet object. |
String getColumnLabel(int column) |
Gets the designated column’s suggested title for use in printouts and displays. |
String getColumnName(int column) |
Get the designated column’s name. |
int getColumnType(int column) |
Retrieves the designated column’s SQL type. |
String getColumnTypeName(int column) |
Retrieves the designated column’s database-specific type name. |
boolean isAutoIncrement(int column) |
Indicates whether the designated column is automatically numbered. |
@Test
public void analyseResult() throws SQLException {
try (
PreparedStatement statement = connection.prepareStatement("SELECT * FROM t_ddl")
) {
ResultSetMetaData meta = statement.executeQuery().getMetaData();
for (int i = 1; i <= meta.getColumnCount(); ++i) {
System.out.printf("label: %s, name: %s, type: %s%n", meta.getColumnLabel(i), meta.getColumnName(i), meta.getColumnTypeName(i));
}
}
}注: 因为获取
ResultSetMetaData只能通过ResultSet的getMetaData()方法,所以使用ResultSetMetaData就需要一定的运行时开销;因此如果在编码过程中已经知道列数/列名/类型等信息, 就没有再用ResultSetMetaData了.
javax.sql.RowSet继承自ResultSet, RowSet的子接口有CachedRowSet, FilteredRowSet, JdbcRowSet, JoinRowSet, WebRowSet, 其中只有JdbcRowSet需要保持与数据库的连接, 其他都是离线RowSet.
与ResultSet相比, RowSet默认就是可滚动/可更新/可序列化的结果集,因此可以作为JavaBean使用(比如在网络上传输,用于同步两端数据).
而对于离线RowSet, 程序在创建RowSet时已把数据从底层数据库读取到了内存,因此可以充分发挥内存的优势,降低数据库Server的负载,提高性能.
RowSet接口提供了如下常用方法:
| 方法 | 描述 |
|---|---|
void setUrl(String url) |
Sets the URL this RowSet object will use when it uses the DriverManager to create a connection. |
void setUsername(String name) |
Sets the username property for this RowSet object to the given String. |
void setPassword(String password) |
Sets the database password for this RowSet object to the given String. |
void setCommand(String cmd) |
Sets this RowSet object’s command property to the given SQL query. |
void setXxx(String parameterName/int parameterIndex, X x) |
|
void execute() |
Fills this RowSet object with data. |
Java 1.7为RowSet提供了RowSetProvider与RowSetFactory工具, RowSetProvider负载创建RowSetFactory, RowSetFactory提供如下方法创建RowSet实例:
| 方法 | 描述 |
|---|---|
CachedRowSet createCachedRowSet() |
Creates a new instance of a CachedRowSet. |
FilteredRowSet createFilteredRowSet() |
Creates a new instance of a FilteredRowSet. |
JdbcRowSet createJdbcRowSet() |
Creates a new instance of a JdbcRowSet. |
JoinRowSet createJoinRowSet() |
Creates a new instance of a JoinRowSet. |
WebRowSet createWebRowSet() |
Creates a new instance of a WebRowSet. |
/**
* @author jifang
* @since 16/2/19 上午9:55.
*/
public class JdbcRowSetClient {
private JdbcRowSet set;
@Before
public void setUp() throws IOException, SQLException, ClassNotFoundException {
Properties config = SQLUtil.loadConfig("common.properties");
Class.forName(config.getProperty("mysql.driver.class"));
set = RowSetProvider.newFactory().createJdbcRowSet();
set.setUrl(config.getProperty("mysql.url"));
set.setUsername(config.getProperty("mysql.user"));
set.setPassword(config.getProperty("mysql.password"));
}
@Test
public void select() throws SQLException {
set.setCommand("select * from t_ddl");
set.execute();
// 反向迭代
set.afterLast();
while (set.previous()) {
System.out.printf("%d\t%s\t%s%n", set.getInt(1), set.getString(2), set.getString(3));
if (set.getInt(1) == 187) {
set.updateString("username", "new_188_name");
set.updateRow();
}
}
}
@After
public void tearDown() {
try {
set.close();
} catch (SQLException e) {
}
}
}可将初始化RowSet操作封装成一个方法:
public static RowSet initRowSet(RowSet set, Properties