JDBC是Java連接數(shù)據(jù)庫(kù)的標(biāo)準(zhǔn)魏保，為了兼容大部分?jǐn)?shù)據(jù)庫(kù)澡为，Java提出了JDBC標(biāo)準(zhǔn)漂坏，通過(guò)這個(gè)標(biāo)準(zhǔn)，讓各個(gè)數(shù)據(jù)庫(kù)提供實(shí)現(xiàn)支持媒至，這樣實(shí)現(xiàn)一處編碼顶别，處處運(yùn)行的Java特性。
習(xí)慣了ORM框架拒啰，卻忘記了原本的JDBC驯绎，所以我覺(jué)得有必要復(fù)習(xí)來(lái)夯實(shí)一下基礎(chǔ)。
0x00 JDBC 歷史
JDBC是Sun公司為了能夠讓SQL訪(fǎng)問(wèn)統(tǒng)一的一套純JAVA API設(shè)計(jì)的一套接口谋旦，這種接口是遵循了微軟的ODBC API模式剩失。其驅(qū)動(dòng)實(shí)現(xiàn)是各家數(shù)據(jù)庫(kù)供應(yīng)商編寫(xiě)的，通過(guò)JDBC API可以通過(guò)驅(qū)動(dòng)實(shí)現(xiàn)數(shù)據(jù)庫(kù)通信蛤织。
0x01 鏈接數(shù)據(jù)庫(kù)回顧
基本W(wǎng)eb常用的數(shù)據(jù)庫(kù)都是有供Java鏈接的驅(qū)動(dòng)赴叹，
那么如何使用JDBC鸿染？
寫(xiě)個(gè)Demo
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;
/**
* Created by zing on 2017/3/7.
*/
public class JDBCDemo {
private void testJDBC() throws ClassNotFoundException, IOException, SQLException {
//注冊(cè)驅(qū)動(dòng)
Class.forName("com.mysql.jdbc.Driver");
//JDBC使用類(lèi)似URL的數(shù)據(jù)源描述
String url = "jdbc:mysql://localhost:3306/demo";//忽略
//但是我們一般不會(huì)直接這樣寫(xiě)死指蚜。而是使用配置來(lái)描述數(shù)據(jù)源，用戶(hù)名涨椒，密碼
Properties props = new Properties();
FileInputStream propertiesFile = new FileInputStream("JDBC.properties");
props.load(propertiesFile);
propertiesFile.close();
String DriverStr = props.getProperty("jdbc.Driver");
String urlStr = props.getProperty("jdbc.url");
String userName = props.getProperty("jdbc.name");
String passcode = props.getProperty("jdbc.passworld");
//打開(kāi)數(shù)據(jù)庫(kù)鏈接
Connection connection = DriverManager.getConnection(urlStr,userName,passcode);
//執(zhí)行SQL
Statement sta = connection.createStatement();
//executeUpdate可以返回?cái)?shù)據(jù)庫(kù)更新的行數(shù)
int efactRow = sta.executeUpdate("UPDATE USER SET Permition = 'admin' WHERE username = 'Zing'");
//executeQuery可以返回一個(gè)查詢(xún)的結(jié)果集摊鸡，這個(gè)集合的迭代器略有不同Iterator,沒(méi)有hasNext方法，初始是蚕冬，指針在數(shù)據(jù)前免猾，必須調(diào)用next方法才能讀取第一行數(shù)據(jù)
ResultSet resultSet = sta.executeQuery("SELECT * FROM USER ;");
while (resultSet.next()){
//當(dāng)前行獲取第一欄的值，具體類(lèi)型需要看數(shù)據(jù)庫(kù)實(shí)現(xiàn)
resultSet.getString(1);
}
//關(guān)閉語(yǔ)句
sta.close();
//關(guān)閉結(jié)果集
resultSet.close();
//關(guān)閉數(shù)據(jù)庫(kù)連接
connection.close();
/*
一般情況下囤热，關(guān)閉的操作會(huì)放在catch語(yǔ)句的finally塊中猎提，catch處理數(shù)據(jù)庫(kù)異常，finally來(lái)關(guān)閉連接
*/
}
}
上面寫(xiě)的是大雜燴旁蔼，一般會(huì)將獲取連接抽取成一個(gè)方法锨苏，異常也會(huì)捕獲，并在try/catch/finally中的finally塊中棺聊，關(guān)閉數(shù)據(jù)庫(kù)連接伞租。
API用法可以看看java.sql.Connection，java.sql.Statement限佩，java.sql.ResultSet葵诈，這樣裸弦，基本的操作就可以了然了。
boolean execute(String sql) throws SQLException;這個(gè)方法可以執(zhí)行任何SQL作喘，返回執(zhí)行是否成功理疙。
0x02 預(yù)編譯SQL
PrepareStatement，一個(gè)可以讓數(shù)據(jù)庫(kù)預(yù)編譯SQL的API泞坦。
并不是所有的SQL都是寫(xiě)死的沪斟，例如：
SELECT * FROM UserAccount Where Name =
根據(jù)名稱(chēng)來(lái)查找用戶(hù)，這里的名字自然是用戶(hù)自己定義的暇矫，如果用Statement主之，則應(yīng)該這么寫(xiě)
public void findUserByName(String name){
Statement sta = connection.createStatement();
String findByName = "SELECT * FROM USER WHERE Name=' "+name+" ';";
ResultSet resultSet = sta.executeQuery(findByName);
}
如果將name交給普通用戶(hù)來(lái)輸入，則沒(méi)什么問(wèn)題李根，但是 如果交給黑客槽奕，name他會(huì)輸入 小明' OR '1' = '1,這樣語(yǔ)句拼接后就會(huì)變成
SELECT * FROM USER WHERE Name=' 小明' OR '1' = '1';
這一句就會(huì)把數(shù)據(jù)庫(kù)所有的用戶(hù)全部查出來(lái)了，很?chē)?yán)重的注入漏洞房轿，基本就會(huì)被脫庫(kù)了粤攒。
所以Java JDBC定義的預(yù)編譯SQL的API。
上例子：
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;
public class JDBCDemo {
private void testJDBC() {
Connection connection = null;
Statement sta = null;
ResultSet resultSet = null;
PreparedStatement preSta = null;
try {
connection = getConnection();
//執(zhí)行SQL
String findByName = "SELECT * FROM USER WHERE Name=?;";
preSta = connection.prepareStatement(findByName);
preSta.setString(1,"Zing");
resultSet = preSta.executeQuery();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
/*
一般情況下囱持，關(guān)閉的操作會(huì)放在catch語(yǔ)句的finally塊中夯接，catch處理數(shù)據(jù)庫(kù)異常，finally來(lái)關(guān)閉連接
*/
//關(guān)閉語(yǔ)句
try {
preSta.close();
//關(guān)閉結(jié)果集
resultSet.close();
//關(guān)閉數(shù)據(jù)庫(kù)連接
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public Connection getConnection() throws ClassNotFoundException, IOException, SQLException {
//注冊(cè)驅(qū)動(dòng)
Class.forName("com.mysql.jdbc.Driver");
//JDBC使用類(lèi)似URL的數(shù)據(jù)源描述
//但是我們一般不會(huì)直接這樣寫(xiě)死纷妆。而是使用配置來(lái)描述數(shù)據(jù)源盔几，用戶(hù)名，密碼
Properties props = new Properties();
FileInputStream propertiesFile = new FileInputStream("JDBC.properties");
props.load(propertiesFile);
propertiesFile.close();
String DriverStr = props.getProperty("jdbc.Driver");
String urlStr = props.getProperty("jdbc.url");
String userName = props.getProperty("jdbc.name");
String passcode = props.getProperty("jdbc.passworld");
return DriverManager.getConnection(urlStr, userName, passcode);
}
}
順便重構(gòu)了之前的代碼掩幢。
我們用 ?占位逊拍，留下可變參數(shù)的位置，后來(lái)再用setString(int parameterIndex, String x)這個(gè)方法將數(shù)據(jù)填充進(jìn)SQL际邻，這樣芯丧，如果參數(shù)含有SQL關(guān)鍵字時(shí)，就不能通過(guò)編譯世曾，查不到結(jié)果缨恒。可以避免SQL注入轮听。
preSta.setString(1,"Zing");表示骗露，在第一個(gè)?處設(shè)置參數(shù)為Zing
當(dāng)然參數(shù)是數(shù)字，日期時(shí)蕊程，可以使用椒袍，
void setDouble(int parameterIndex, double x) throws SQLException
setDate(int parameterIndex, java.sql.Date x) throws SQLException;
等方法，根據(jù)不同類(lèi)型設(shè)置參數(shù)藻茂。
0x03 數(shù)據(jù)庫(kù)類(lèi)型與轉(zhuǎn)義
數(shù)據(jù)庫(kù)類(lèi)型和Java類(lèi)型是有一點(diǎn)不一樣的驹暑，但是JDBC定義了其中的大部分類(lèi)型玫恳，這里不一一列舉
JDBC中的轉(zhuǎn)義是為了讓Java訪(fǎng)問(wèn)數(shù)據(jù)庫(kù)時(shí)优俘，得到普遍的支持京办。一般用于下列特性
- 時(shí)間日期的字面常亮
- 標(biāo)量函數(shù)調(diào)用
- 存儲(chǔ)過(guò)程調(diào)用
- 外連接查詢(xún)
- LIKE子句中轉(zhuǎn)義字符
數(shù)據(jù)庫(kù)的日期轉(zhuǎn)換成Java的日期，是通過(guò)ISO8601標(biāo)準(zhǔn)衡定并相互轉(zhuǎn)換的
d表示DATE帆焕、t表示TIME惭婿、ts表示TIMESTANP
{d '2017-01-22'}
{t '19:30:29'}
{ts '2017-01-22 19:30:29.989'}
標(biāo)量函數(shù)是獲取一個(gè)數(shù)值的函數(shù)，一般調(diào)用時(shí)嵌入標(biāo)準(zhǔn)函數(shù)名和參數(shù)叶雹，這個(gè)很少見(jiàn)到有人使用的财饥，就不舉例了。
存儲(chǔ)過(guò)程折晦，是數(shù)據(jù)庫(kù)自建的存儲(chǔ)方式钥星，不同的數(shù)據(jù)庫(kù)存儲(chǔ)過(guò)程基本不一樣，要調(diào)用存儲(chǔ)過(guò)程满着，需要用call來(lái)進(jìn)行轉(zhuǎn)義
{call PROC01(?,?)}
{call PROC02}
如果你不明白什么存儲(chǔ)過(guò)程谦炒，可以看看數(shù)據(jù)庫(kù)相關(guān)的資料。
外連接风喇，就是Outter Join宁改，借用核心卷II中的例子
SELECT * FROM {oj Books LEFT OUTER JOIN Publishers ON Books.Publish_ID = Publishers.Publish_ID }
這條語(yǔ)句表示查詢(xún)找不到出版商的書(shū)，相反如果是RIGHT OUTER JOIN則會(huì)查詢(xún)出沒(méi)有出版書(shū)的出版商魂莫，如果需要查到全部还蹲，則用FULL OUTER JOIN
。這里用轉(zhuǎn)義是因?yàn)橛行?shù)據(jù)庫(kù)實(shí)現(xiàn)不太統(tǒng)一豁鲤。
Like子句轉(zhuǎn)義秽誊，是因?yàn)橄聞澗€(xiàn)和百分號(hào)在Like條件里是特殊的含義鲸沮，需要用轉(zhuǎn)義來(lái)表示
SELECT * FROM User WHERE Name LIKE %!_%ming {escape '!'}
{escape '!'}表示將琳骡！定義為轉(zhuǎn)義符號(hào)，!_表示字面量下劃線(xiàn)
0x04 事務(wù)
為了保證數(shù)據(jù)和業(yè)務(wù)邏輯的完整性讼溺，我們可以將一系列的SQL語(yǔ)句構(gòu)建成一個(gè)事物楣号，當(dāng)所有語(yǔ)句都順利執(zhí)行的時(shí)候，事務(wù)可以被提交怒坯。但是如果中途被阻礙炫狱，則數(shù)據(jù)會(huì)被回滾，將數(shù)據(jù)恢復(fù)成執(zhí)行前的樣子剔猿。
首先需要關(guān)閉數(shù)據(jù)庫(kù)自動(dòng)提交
connection.setAutoCommit(false);
然后根據(jù)實(shí)際業(yè)務(wù)執(zhí)行多條UPDATE INSERT DELETE語(yǔ)句
statement.executeUpdate("SQL1");
statement.executeUpdate("SQL2");
statement.executeUpdate("SQL3");
當(dāng)所有語(yǔ)句順利執(zhí)行后视译，調(diào)用
connection.commit();
如果遇到異常或錯(cuò)誤归敬，則可以調(diào)用
connection.rollback();
其中JDBC支持事務(wù)保存點(diǎn)和批量更新
保存點(diǎn)：將事務(wù)的某一階段設(shè)置為保存點(diǎn)后酷含，可以控制回滾時(shí)鄙早，恢復(fù)到這個(gè)保存點(diǎn)的數(shù)據(jù)。從而更加精確的控制回滾操作
批量更新就是將大量數(shù)據(jù)一次性存入椅亚，或修改大量數(shù)據(jù)時(shí)使用的限番。兩個(gè)??：
statement.executeUpdate("SQL1");
Savepoint step1 = connection.setSavepoint();
statement.executeUpdate("SQL2");
if(something==false){
connection.rollback(step1);
}
String updateSQL = "……";
statement.addBatch(updateSQL);
while(needUpdate){
command = "……"+"updateSQL2"
statement.addBatch(updateSQL);
}
//批量執(zhí)行
int effectRows = statement.executeBatch();
批量執(zhí)行中一定不能有查詢(xún)語(yǔ)句，否則會(huì)拋出異常呀舔。
0x05 文件查詢(xún)和存入數(shù)據(jù)庫(kù)
不建議這么搞弥虐，數(shù)據(jù)庫(kù)存入太多大文件會(huì)導(dǎo)致數(shù)據(jù)庫(kù)龐大，備份和恢復(fù)的成本將增加媚赖。
在數(shù)據(jù)庫(kù)中霜瘪，二進(jìn)制大對(duì)象稱(chēng)為Blob，字符型大對(duì)象為Clob
這里演示一下查詢(xún)和存儲(chǔ)
//讀取
PreparedStatement preparedStatement01 = connection.prepareStatement("SELECT picture FROM PictureTab WHERE picName=?;");
preparedStatement01.setString(1,"superman");
ResultSet rs = preparedStatement01.executeQuery();
if(rs.next()){
Blob picBlob = rs.getBlob(1);
Image pic = ImageIO.read(picBlob.getBinaryStream());
}
//存儲(chǔ)
Blob pictureBlob = connection.createBlob();
int offset = 0;
OutputStream outStram = pictureBlob.setBinaryStream(offset);
ImageIO.write(pictureBlob,"PNG",outStram);
PreparedStatement preparedStatement02 = connection.prepareStatement("INSERT INTO PictureTab VALUE (?,?);");
preparedStatement02.setString(1, "SuperMan");
preparedStatement02.setBlob(2,pictureBlob);
preparedStatement02.executeUpdate();
0x06 其他一些概念
- 元數(shù)據(jù)：數(shù)據(jù)庫(kù)的結(jié)構(gòu)和表信息等描述數(shù)據(jù)庫(kù)結(jié)構(gòu)和組成部分的數(shù)據(jù)
- 多結(jié)果集：一次查詢(xún)惧磺，使用多個(gè)Select SQL語(yǔ)句是粥庄，會(huì)得到一個(gè)多結(jié)果集
- 可滾動(dòng)結(jié)果集：可以向前，向后查詢(xún)的結(jié)果集豺妓，之前的只能用Next向后查詢(xún)惜互，使
Statement stat = Connection.createStatement(ResultSet.TYPE_SCROLL_INSENSTIVE , ResultSet.CONCUR_READ_ONLY )
在獲取結(jié)果集的時(shí)候，會(huì)變成一個(gè)可滾動(dòng)集琳拭。
- 獲取數(shù)據(jù)庫(kù)生成鍵值
statemwnt.getGeneratedKeys(); - 行集 RowSet接口繼承了ResultSet训堆，但不需要長(zhǎng)時(shí)間占用數(shù)據(jù)庫(kù)鏈接。
love&peace
我的博客：https://micorochio.github.io/
轉(zhuǎn)載請(qǐng)注明出處:https://micorochio.github.io/2017/03/10/basic-of-java-JDBC/白嘁。
