JDBC(Java Database Connectivity)代表Java編程語言與數據庫連接的標准API,然而JDBC只是接口,JDBC驅動才是真正的接口實現,沒有驅動無法完成數據庫連接. 每個數據庫廠商都有自己的驅動,用來連接自己公司的數據庫(如Oricle, MySQL, DB2, MS SQLServer).
下面我們以MySQL為例,JDBC編程大致步驟如下:
![\](https://www.aspphp.online/shujuku/UploadFiles_3118/201701/2017012011300090.png "\")
/**
* @author jifang
* @since 16/2/18 上午9:02.
*/
public class SQLClient {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
/* 加載數據庫驅動 */
Class.forName("com.mysql.jdbc.Driver");
/* 通過 DriverManager 獲取數據庫連接 */
Connection connection = DriverManager.getConnection("jdbc:mysql://host:port/database", "user", "password");
/* 通過 Connection 創建 Statement */
Statement statement = connection.createStatement();
/* 通過 Statement 執行SQL */
ResultSet users = statement.executeQuery("SELECT * FROM user");
/* 操作 ResultSet 結果集 */
int columnCount = users.getMetaData().getColumnCount();
while (users.next()) {
for (int i = 1; i <= columnCount; ++i) {
System.out.printf("%s\t", users.getObject(i));
}
System.out.println();
}
/* 回收數據庫資源(推薦使用Java1.7提供的 可以自動關閉資源的try) */
users.close();
statement.close();
connection.close();
}
}
注意: 需要在pom.xml中添加如下MySQL驅動:
mysql
mysql-connector-java
5.1.36
注: ResultSet參數columnIndex索引從1開始,而不是0!
ConnectionMangerDriverMangerJDBC規定: 驅動類在被加載時,需要主動把自己注冊到DriverManger中:
com.mysql.jdbc.Driver
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編碼;
Connectionjava.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文檔.
ConnectionManger由於獲取Connection的步驟單一,每次可能只是加載的參數不同,因此我們可以將獲取Connection的操作封裝成一個方法,並使其從配置文件中加載配置:
配置文件形式
## Data Source
mysql.driver.class=com.mysql.jdbc.Driver
mysql.url=jdbc:mysql://host:port/database
mysql.user=admin
mysql.password=admin
ConnectionManger
/**
* @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);
}
}
}
SQLUtil
/**
* @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)提供.
數據庫連接池的常用參數如下:
數據庫初始連接數; 連接池最大連接數; 連接池最小連接數; 連接池每次增加的容量; C3P0Tomcat默認使用的是DBCP連接池,但相比之下,C3P0則比DBCP更勝一籌(Hibernate推薦使用C3P0),C3P0不僅可以自動清理不再使用的Connection, 還可以自動清理Statement/ResultSet, 使用C3P0需要在pom.xml中添加如下依賴:
com.mchange
c3p0
0.9.5.2
com.mchange
mchange-commons-java
0.2.11
ConnectionManger
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,其放在類路徑下:
jdbc:mysql://host:port/database
com.mysql.jdbc.Driver
user
password
5
10
3
20
jdbc:mysql://host:port/common
com.mysql.jdbc.Driver
user
password
5
10
3
20
這樣, 我們在創建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主頁.
HikariCPHikariCP是另一款高性能/”零開銷”/高品質的數據庫連接池,據測試,其性能優於C3P0(詳細信息可參考號稱性能最好的JDBC連接池:HikariCP),但國內HikariCP資料不多,其項目主頁為https://github.com/brettwooldridge/HikariCP,使用HikariCP需要在pom.xml中添加如下依賴:
com.zaxxer
HikariCP
2.4.0
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=1000
SQL執行Statementjava.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自動關閉.
executeUpdateStatement使用executeUpdate方法執行DDL/DML(不包含select)語句:執行DDL該方法返回0; 執行DML返回受影響的記錄數.
DDL
@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);
}
}
DML
@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);
}
}
executeexecute方法幾乎可以執行任何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.SQLUtil
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);
}
}
}
client
@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");
}
}
PreparedStatementPreparedStatement是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
CallableStatement在數據庫中創建一個簡單的存儲過程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對象的元數據信息.
ResultSetjava.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)方法來獲得指定類型值.或使用
可更新/滾動的ResultSet以默認方式打開的ResultSet是不可更新的,獲得可更新的ResultSet,需要在創建Statement/PreparedStatement時傳入如下兩個參數:
resultSetType: 控制ResultSet可移動方向 ResultSet.TYPE_FORWARD_ONLYThe constant indicating the type for a ResultSet object whose cursor may move only forward.ResultSet.TYPE_SCROLL_INSENSITIVEThe 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_SENSITIVEThe constant indicating the type for a ResultSet object that is scrollable and generally sensitive to changes to the data that underlies the ResultSet.resultSetConcurrency: 控制ResultSet的讀/寫並發類型 ResultSet.CONCUR_READ_ONLYThe constant indicating the concurrency mode for a ResultSet object that may NOT be updated.ResultSet.CONCUR_UPDATABLEThe 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()來提交修改.
SQLClient
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) {
}
}
}
SQLUtil
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);
}
}
ResultSetMetaDataResultSet提供了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.analyseResult
@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了.
RowSetjavax.sql.RowSet繼承自ResultSet, RowSet的子接口有CachedRowSet, FilteredRowSet, JdbcRowSet, JoinRowSet, WebRowSet, 其中只有JdbcRowSet需要保持與數據庫的連接, 其他都是離線RowSet.
![\](https://www.aspphp.online/shujuku/UploadFiles_3118/201701/2017012011300118.png "\")
與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.JdbcRowSetClient
/**
* @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操作封裝成一個方法:
SQLUtil
public static RowSet initRowSet(RowSet set, Properties config) {
try {
Class.forName(config.getProperty("mysql.driver.class"));
set.setUrl(config.getProperty("mysql.url"));
set.setUsername(config.getProperty("mysql.user"));
set.setPassword(config.getProperty("mysql.password"));
return set;
} catch (ClassNotFoundException | SQLException e) {
throw new RuntimeException(e);
}
}
離線RowSet前面查詢得到ResultSet後必須立即處理,否則一旦Connection連接關閉,再去讀/寫ResultSet就會引發異常.而離線RowSet會直接將數據讀入內存,封裝成RowSet對象,CachedRowSet是所有離線RowSet的父接口,提供了如下實用方法:
方法描述void populate(ResultSet data)Populates this CachedRowSet object with data from the given ResultSet object.void acceptChanges()Propagates row update, insert and delete changes made to this CachedRowSet object to the underlying data source.void acceptChanges(Connection con)Propagates all row update, insert and delete changes to the data source backing this CachedRowSet object using the specified Connection object to establish a connection to the data source.CachedRowSetClient
/**
* @author jifang
* @since 16/2/19 上午10:32.
*/
public class CachedRowSetClient {
private CachedRowSet query(String config, String sql) {
/*Connection/Statement/ResultSet會自動關閉*/
try (
Connection connection = ConnectionManger.getConnectionHikari(config);
Statement statement = connection.createStatement();
ResultSet rs = statement.executeQuery(sql)
) {
CachedRowSet rowSet = RowSetProvider.newFactory().createCachedRowSet();
rowSet.populate(rs);
return rowSet;
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
@Test
public void client() throws SQLException {
CachedRowSet set = query("common.properties", "select * from t_ddl");
// 此時RowSet已離線
while (set.next()) {
System.out.printf("%s\t%s\t%s%n", set.getInt(1), set.getString(2), set.getString(3));
if (set.getInt(1) == 3) {
set.updateString(3, "new3_password3_3");
set.updateRow();
}
}
// 重新獲得連接
Connection connection = ConnectionManger.getConnectionHikari("common.properties");
connection.setAutoCommit(false);
// 把對RowSet所做的修改同步到數據庫
set.acceptChanges(connection);
}
}
離線RowSet分頁由於CachedRowSet會將底層數據庫數據直接裝載到內存,因此如果SQL查詢返回數據過大,可能會導致內存溢出.為了解決這個問題,CachedRowSet提供了分頁功能: 一次只裝載ResultSet的部分記錄,這樣可以避免CachedRowSet占用內存過大.
方法描述void populate(ResultSet rs, int startRow)Populates this CachedRowSet object with data from the given ResultSet object.void setPageSize(int size)Sets the CachedRowSet object’s page-size.boolean nextPage()Increments the current page of the CachedRowSet.boolean previousPage()Decrements the current page of the CachedRowSet.CachedRowSetClient
public class CachedRowSetClient {
@Test
public void cachedRowSetPaging() throws SQLException {
int page = 4;
int size = 10;
try (
ResultSet rs = ConnectionManger.getConnectionHikari("common.properties")
.createStatement()
.executeQuery("SELECT * FROM t_ddl ORDER BY id")
) {
CachedRowSet rowSet = RowSetProvider.newFactory().createCachedRowSet();
rowSet.populate(rs, (page - 1) * size + 1);
rowSet.setPageSize(size);
while (rowSet.nextPage()) {
rowSet.next();
displayRowSet(rowSet, 3);
}
}
}
private void displayRowSet(RowSet set, int column) {
try {
for (int i = 1; i <= column; ++i) {
System.out.printf("%s\t", set.getString(i));
}
System.out.println();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
事務事務是由一步/幾步數據庫操作序列組成的邏輯執行單元, 這些操作要麼全部執行, 要麼全部不執行.
注: MySQL事務功能需要有InnoDB存儲引擎的支持, 詳見MySQL存儲引擎InnoDB與Myisam的主要區別.
ACID特性原子性(A: Atomicity): 事務是不可再分的最小邏輯執行體; 一致性(C: Consistency): 事務執行的結果, 必須使數據庫從一個一致性狀態, 變為另一個一致性狀態. 隔離性(I: Isolation): 各個事務的執行互不干擾, 任意一個事務的內部操作對其他並發事務都是隔離的(並發執行的事務之間不能看到對方的中間狀態,不能互相影響) 持續性(D: Durability): 持續性也稱持久性(Persistence), 指事務一旦提交, 對數據所做的任何改變都要記錄到永久存儲器(通常指物理數據庫). Commit/Rollback當事務所包含的全部操作都成功執行後提交事務,使操作永久生效,事務提交有兩種方式:
1). 顯式提交: 使用commit;
2). 自動提交: 執行DDL/DCL語句或程序正常退出;
當事務所包含的任意一個操作執行失敗後應該回滾事務, 使該事務中所做的修改全部失效, 事務回滾也有兩種方式:
1). 顯式回滾: 使用rollback;
2). 自動回滾: 系統錯誤或強行退出.
注意: 同一事務中所有的操作,都必須使用同一個Connection.
JDBC支持JDBC對事務的支持由Connection提供, Connection默認打開自動提交,即關閉事務,SQL語句一旦執行, 便會立即提交數據庫,永久生效,無法對其進行回滾操作,因此需要關閉自動提交功能.
首先創建一張表用於測試
CREATE TABLE `account` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(45) NOT NULL,
`money` decimal(10,0) unsigned zerofill NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name_UNIQUE` (`name`),
UNIQUE KEY `id_UNIQUE` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=UTF8;
插入兩條測試數據
INSERT INTO `account` (`name`, `money`) VALUES ('feiqing', '7800');
INSERT INTO `account` (`name`, `money`) VALUES ('xiaofang', '7800');
No Transaction
/**
* @author jifang
* @since 16/2/19 下午5:02.
*/
public class TransactionClient {
private Connection connection = ConnectionManger.getConnection("common.properties");
@Test
public void noTransaction() throws SQLException {
try (
PreparedStatement minusSM = connection.prepareStatement("UPDATE `account` SET `money`=(`money` - ?) WHERE `name`=?");
PreparedStatement addSM = connection.prepareStatement("UPDATE `account` SET `money`=(`money` + ?) WHERE `name`=?")
) {
// 從feiqing賬戶轉出
minusSM.setBigDecimal(1, new BigDecimal(100));
minusSM.setString(2, "feiqing");
minusSM.execute();
// 中途拋出異常, 會導致兩賬戶前後不一致
if (true){
throw new RuntimeException("no-transaction");
}
// 轉入xiaofang賬戶
addSM.setBigDecimal(1, new BigDecimal(100));
addSM.setString(2, "xiaofang");
addSM.execute();
}
}
@After
public void tearDown() {
try {
connection.close();
} catch (SQLException e) {
}
}
}
By Transaction
@Test
public void byTransaction() throws SQLException {
boolean autoCommitFlag = connection.getAutoCommit();
// 關閉自動提交, 開啟事務
connection.setAutoCommit(false);
try (
PreparedStatement minusSM = connection.prepareStatement("UPDATE `account` SET `money`=(`money` - ?) WHERE `name`=?");
PreparedStatement addSM = connection.prepareStatement("UPDATE `account` SET `money`=(`money` + ?) WHERE `name`=?")
) {
// 從feiqing賬戶轉出
minusSM.setBigDecimal(1, new BigDecimal(100));
minusSM.setString(2, "feiqing");
minusSM.execute();
// 中途拋出異常: rollback
if (true) {
throw new RuntimeException("no-transaction");
}
// 轉入xiaofang賬戶
addSM.setBigDecimal(1, new BigDecimal(100));
addSM.setString(2, "xiaofang");
addSM.execute();
connection.commit();
} catch (Throwable e) {
connection.rollback();
throw new RuntimeException(e);
} finally {
connection.setAutoCommit(autoCommitFlag);
}
}
注意: 當Connection遇到一個未處理的SQLException時, 程序將會非正常退出,事務也會自動回滾;但如果程序捕獲了該異常, 則需要在異常處理塊中顯式地回滾事務.
隔離級別在相同數據環境下,使用相同輸入,執行相同操作,根據不同的隔離級別,會導致不同的結果.不同的事務隔離級別能夠解決的數據並發問題的能力是不同的, 由弱到強分為以下四級:
隔離級別描述釋義read uncommitted讀未提交數據不符合原子性,稱為”髒讀”, 在實際業務中不用.read commited讀已提交數據(Oracle)事務執行中,讀不到另一個事務正在進行的操作,但可以讀到另一個事務結束的操作.repeatable read可重復讀(MySQL)在事務執行中,所有信息都來自事務開始的那一瞬間的信息,不受其他已提交事務的影響.serializeable串行化所有的事務按順序執行,也就沒有了沖突的可能.隔離級別最高,但事務相互等待時間太長,性能最差,少用.MySQL設置事務隔離級別:
set session transaction isolation level [read uncommitted | read committed | repeatable read |serializable]
查看當前事務隔離級別:
select @@tx_isolation
JDBC設置隔離級別
connection.setTransactionIsolation(int level)
level可為以下值:
1). Connection.TRANSACTION_READ_UNCOMMITTED
2). Connection.TRANSACTION_READ_COMMITTED
3). Connection.TRANSACTION_REPEATABLE_READ
4). Connection.TRANSACTION_SERIALIZABLE
附: 事務並發讀問題
1. 髒讀(dirty read):讀到另一個事務的未提交的數據,即讀取到了髒數據(read commited級別可解決).
2. 不可重復讀(unrepeatable read):對同一記錄的兩次讀取不一致,因為另一事務對該記錄做了修改(repeatable read級別可解決)
3. 幻讀/虛讀(phantom read):對同一張表的兩次查詢不一致,因為另一事務插入了一條記錄(repeatable read級別可解決)不可重復讀和幻讀的區別:
不可重復讀是讀取到了另一事務的更新; 幻讀是讀取到了另一事務的插入(MySQL中無法測試到幻讀,效果與不可重復讀一致);
其他關於並發事務問題可參考<數據庫事務並發帶來的問題>
批處理
多條SQL語句被當做同一批操作同時執行.
調用Statement對象的addBatch(String sql)方法將多條SQL語句收集起來, 然後調用executeBatch()同時執行.
為了讓批量操作可以正確進行, 必須把批處理視為單個事務, 如果在執行過程中失敗, 則讓事務回滾到批處理開始前的狀態.
public class SQLClient {
private Connection connection = null;
private Random random = new Random();
@Before
public void setUp() {
connection = ConnectionManger.getConnectionHikari("common.properties");
}
@Test
public void updateBatch() throws SQLException {
List sqlList = Lists.newArrayListWithCapacity(10);
for (int i = 0; i < 10; ++i) {
sqlList.add("INSERT INTO user(name, password) VALUES('student" + i + "','" + encodeByMd5(random.nextInt() + "") + "')");
}
int[] results = update(connection, sqlList);
for (int result : results) {
System.out.printf("%d ", result);
}
}
private int[] update(Connection connection, List sqlList) {
boolean autoCommitFlag = false;
try {
autoCommitFlag = connection.getAutoCommit();
// 關閉自動提交, 打開事務
connection.setAutoCommit(false);
// 收集SQL語句
Statement statement = connection.createStatement();
for (String sql : sqlList) {
statement.addBatch(sql);
}
// 批量執行 & 提交事務
int[] result = statement.executeBatch();
connection.commit();
return result;
} catch (SQLException e) {
try {
connection.rollback();
} catch (SQLException ignored) {
}
throw new RuntimeException(e);
} finally {
try {
connection.setAutoCommit(autoCommitFlag);
} catch (SQLException ignored) {
}
}
}
private String encodeByMd5(String input) {
try {
MessageDigest md5 = MessageDigest.getInstance("MD5");
BASE64Encoder base64Encoder = new BASE64Encoder();
return base64Encoder.encode(md5.digest(input.getBytes("utf-8")));
} catch (NoSuchAlgorithmException | UnsupportedEncodingException e) {
throw new RuntimeException(e);
}
}
@After
public void tearDown() {
try {
connection.close();
} catch (SQLException ignored) {
}
}
}
注:
1). 對於批處理,也可以使用PreparedStatement,建議使用Statement,因為PreparedStatement的預編譯空間有限,當數據量過大時,可能會引起內存溢出.
2). MySQL默認也沒有打開批處理功能,需要在URL中設置rewriteBatchedStatements=true參數打開.
DbUtilscommons-dbutils是Apache Commons組件中的一員,提供了對JDBC的簡單封裝,以簡化JDBC編程;使用dbutils需要在pom.xml中添加如下依賴:
commons-dbutils
commons-dbutils
1.6
dbutils的常用類/接口如下:
DbUtils: 提供了一系列的實用靜態方法(如:close()); ResultSetHandler: 提供對結果集ResultSet與JavaBean等的轉換; QueryRunner:
update()(執行insert/update/delete) query()(執行select) batch()(批處理). QueryRunner更新常用的update方法簽名如下:
int update(String sql, Object... params);
int update(Connection conn, String sql, Object... params);
/**
* @author jifang
* @since 16/2/20 上午10:25.
*/
public class QueryRunnerClient {
@Test
public void update() throws SQLException {
QueryRunner runner = new QueryRunner(ConnectionManger.getDataSourceHikari("common.properties"));
String sql = "INSERT INTO t_ddl(username, password) VALUES(?, ?)";
runner.update(sql, "fq", "fq_password");
}
}
第二種方式需要提供Connection, 這樣多次調用update可以共用一個Connection, 因此調用該方法可以支持事務;
QueryRunner查詢QueryRunner常用的query方法簽名如下:
T query(String sql, ResultSetHandler rsh, Object... params);
T query(Connection conn, String sql, ResultSetHandler rsh, Object... params);
query()方法會通過sql語句和params參數查詢出ResultSet，然後通過ResultSetHandler將ResultSet轉換成對應的JavaBean返回.
public class QueryRunnerClient {
// ...
@Test
public void select() throws SQLException {
QueryRunner runner = new QueryRunner();
String sql = "SELECT * FROM t_ddl WHERE id = ?";
TDDL result = runner.query(ConnectionManger.getConnectionHikari("common.properties"), sql, rsh, 7);
System.out.println(result);
}
private ResultSetHandler rsh = new ResultSetHandler() {
@Override
public TDDL handle(ResultSet rs) throws SQLException {
TDDL tddl = new TDDL();
if (rs.next()) {
tddl.setId(rs.getInt(1));
tddl.setUsername(rs.getString(2));
tddl.setPassword(rs.getString(3));
}
return tddl;
}
};
private static class TDDL {
private Integer id;
private String username;
private String password;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
@Override
public String toString() {
return "TDDL{" +
"id=" + id +
", username='" + username + '\'' +
", password='" + password + '\'' +
'}';
}
}
}
ResultSetHandler在上例中, 我們使用自定的ResultSetHandler將ResultSet轉換成JavaBean, 但實際上dbutils默認已經提供了很多定義良好的Handler實現:
BeanHandler : 單行處理器,將ResultSet轉換成JavaBean; BeanListHandler : 多行處理器,將ResultSet轉換成List; MapHandler : 單行處理器,將ResultSet轉換成Map, 列名為鍵; MapListHandler : 多行處理器,將ResultSet轉換成List>; ScalarHandler : 單行單列處理器,將ResultSet轉換成Object(如保存SELECT COUNT(*) FROM t_ddl). ColumnListHandler : 多行單列處理器,將ResultSet轉換成List 
