反人類SQL庫-JDBC及HikariCP的使用須知
JDBC是Java程序與MySQL資料庫的溝通橋樑，但其設計在我看來十分十分十分反人類。
HikariCP是基於JDBC二次開發的高性能連接池，能夠有效(?)地減少連接的用時和資源佔用。
Hi·ka·ri [hi·ka·lē] (日語): 光，HikariCP的字面意思也就是像光那麼快的連接池(?)
這個輪子大概是日本人寫的
Github頁面: https://github.com/brettwooldridge/HikariCP
截至目前項目已有5.8k+的stars。
本文主要用於記錄這兩個庫使用過程中踩過的坑和注意事項，免得以後因為記性差一次又一次地掉進去...
連接資料庫
HikariCP提供了連接資料庫的新方法，使用方法大致如下
private void registerSQL() {
HikariDataSource ds = new HikariDataSource();
ds.setJdbcUrl("jdbc:mysql://127.0.0.1:3306/資料庫名");
ds.setUsername("用戶名");
ds.setPassword("密碼");
// HikariCP提供的優化設置
ds.addDataSourceProperty("cachePrepStmts", "true");
ds.addDataSourceProperty("prepStmtCacheSize", "250");
ds.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
try {
connection = ds.getConnection();
} catch (SQLException e) {
e.printStackTrace();
}
}
這一方法執行後，會生成一個與資料庫的連接 connection ；
接下來要執行查詢語句的話，都需要用到這個連接。
建議在初始化的時候，在核心程序內增加一個 connection getter 以便隨時調用。
注:
資料庫的查詢方面，最耗時的不是執行語句，而是建立連接。
因此連接池與資料庫連接後，即使沒有執行任何語句，連接短時間內也不會中斷。當然，如果空閑時間過長的話，連接池會自動關閉連接以節省資源。
執行語句
接下來需要用到 statement ，下面是一個例子
像是創建新紀錄這種操作，並不需要返回值，因此使用execute(String sql)即可。
// 更新列表
Statement stmt = getConnection().createStatement();
stmt.execute("INSERT INTO sign(id) VALUE(Masonic);");
如果要批量執行SQL語句的話，可以使用addBatch(String sql)方法
//批量
Statement stmt = getConnection().createStatement();
stmt.addBatch("INSERT INTO sign(id) VALUE(Masonic);");
stmt.addBatch("INSERT INTO sign(id) VALUE(9545);");
stmt.addBatch("INSERT INTO sign(id) VALUE(LeSakuya);");
當然，SQL語句里大多數情況下會含有變數，
拼接字元串或者用%這樣的方法在變數多的情況時顯得效率十分底下。
使用佔位符可以很好地解決這種問題
①可以使用PreparedStatement提供的setObject()方法對將要執行的語句進行修飾。
這種方法使用?作為佔位符。要加幾個參數就寫幾個問號，然後挨個替換變數。
String sql = "INSERT INTO sign(id, uuid) VALUE(?, ?);"
statement = Core.getConnection().prepareStatement(sql);
statement.setObject(1, p.getName());
statement.setObject(2, p.getUniqueID());
statement.execute();
但是我覺得這種方法仍然十分弱智，在變數多的情況下代碼十分冗餘，且使用addBatch()方法批量執行時，無法指定要替換哪個語句的佔位符。
②使用MessageFormat
MessageFormat可以對字元串中的佔位符{index}進行替換，下面是一個簡單的例子
String sql = "INSERT INTO {0}(`{1}`, `{2}`, `{3}`, `{4}`, `{5}`) VALUE({6}, {7}, {8}, {9}, {10})";
sql = MessageFormat.format(sql, SHEET, COL_USER_UUID, COL_USER_NAME, COL_EXPIRE, COL_TYPE, COL_RECORD, p.getUniqueId().toString(), p.getPlayerListName(), 0, "A", "[]");
statement.execute(sql);
在變數很多的情況下也能保持可讀性和簡潔性。
但是使用此方法需要注意一下單引號的使用。
SQL語句中字元串都需要使用單引號框起來，但MessageFormat會自動將字元串中的單引號刪去，解決方法是再加一層單引號，類似
{0}
查詢語句
我認為jdbc的查詢是最反人類的部分，這是業務中實際用到的代碼
String sql = "SELECT {0} FROM {1} WHERE {2} = {3} LIMIT 1;";
ResultSet value = SqlUtil.getResults(MessageFormat.format(sql, COL_EXPIRE, SHEET, COL_USER_UUID, p.getUniqueId().toString()));
語句查詢的結果以ResultSet對象返回
這個對象無法直接解析，還要使用特定方法取出結果，而且要知道取得的數據的格式
首先你要確定返回的是不是為空，默認情況下指針指的不是你想要的結果，
要使用while方法或者if方法使指針移動到結果上，然後再使用getInt(), getDouble()等方法解析，參數1指的是ColumnIndex，即列數。
while(value.next()) {
int v = value.getInt(1)
}
但是這樣做的話，如果沒有查到任何結果，直接對value使用get方法會報錯。
這個問題簡單，判斷一下是不是為空就好了。
重點來了
結果為空不等於Null，if(value == null)這種操作是不存在的
而且由於指針的原因，判斷為空的最佳方式是這樣的
Statement stmt = getConnection().createStatement();
ResultSet rs = stmt.executeQuery("SHOW TABLES LIKE sign;");
boolean empty = true;
while (rs.next()) {
// 結果不為空，要做的事情
empty = false;
}
if (empty) {
// 結果為空，要做的事情
}
很臃腫，但是可能是最優解
這個方法來自StackOverflow，看來這種問題困擾了許多人。
SQL的語句格式問題
sql語句中建議將列名使用``符號框起來，字元串用框起來，像是這樣
INSERT INTO sign(`id`, `uuid`) VALUE(Masonic, df8eeda2-24d1-39c7-916f-c1d4502d3576);
表名和列名不要重複，不然會出現一些奇怪的問題。
關於數組
有些時候我會試圖向資料庫里存儲數組，但是很遺憾，MySQL目前並不支持這種操作
網上有提到使用關係表解決此問題。
我用的是另外一種操作，MySQL支持json格式數據，將ArrayList使用Gson轉換為json格式存入表，然後取出時轉回ArrayList即可。
關於連接超時
?
This property controls the maximum number of milliseconds that a client (thats you) will wait for a connection from the pool. If this time is exceeded without a connection becoming available, a SQLException will be thrown. Lowest acceptable connection timeout is 250 ms. Default: 30000 (30 seconds)connectionTimeout
HikariCP的默認超時時間為30秒，如果連接在30秒內未執行任何查詢，將自動關閉。
一個簡單的解決方案是設定一個循環任務，每29秒執行一次無意義的查詢。
關於HikariCP
作為JDBC的優化版本，HikariCP宣稱大大提高了運行效率
我簡單地實測了一下，一個獲取簽到數據的操作，HikariCP和JDBC第一次用時都是17ms，但是第二次第三次，用時分別下降到了5ms和6ms，這應該與緩存機制有關。
具體的性能對比，可能需要更大規模的數據量才能看出區別。
鑒於JDBC的反人類程度，這篇文章可能會越來越長。
I wanna to fuck JDBC.
推薦閱讀：
