Please enable JavaScript.
Coggle requires JavaScript to display documents.
JDBC, JDBC簡化 - Coggle Diagram
JDBC
- 連線
1.URL 2. Connection 3. close()-
Connection建立資料庫連線
Connection con = null;
( :arrow_right_hook: 藉由Connection產生的方法務必關閉 )
con = DriverManager.getConnection
(String URL, String USER, String PASSWORD);
close();
藉由Connection產生的方法
務必執行,:arrow_right:故寫在finally裡
:arrow_right:所以例外處理用try catch
finally{
if(con != null) {
try{
con.close();
} catch(SQLException e) {
e.printStackTrace
}
Statement介面
靜態SQL指令
Statement creatStatement();
Statement createStatement()建立&取得Statement物件
Statement stmt = null;
( :arrow_right_hook: 藉由Connection產生的方法務必關閉 )
stmt = con.createStatement();
Select JDBC指令
ResultSet executeQuery(SQL指令)
ResultSet rs = null;
( :arrow_right_hook: 藉由Connection產生的方法務必關閉 )
rs = stmt.executeQuery(SQL指令語法);
ResultSet 游標移動概念
回傳boolean
true:游標指向某列資料
false:游標沒有指向資料
:arrow_right: while無窮迴圈+搭配next()
while(rs.next()) {
TYPE 欄位名稱 = rs.getTYPE("欄位名稱");
int deptno = rs.getInt("DEPTNO");
}
-
-
Insert & Update & Delete JDBC指令
int executeUpdate(SQL指令)
回傳成功新增的筆數:arrow_right:int
int count = stmt.executeUpdate(SQL指令語法);
SQL指令 Insert
"INSERT INTO DEPARTMENT (DEPTNO, DNAME, LOC) VALUES (50, ''資訊部, '桃園中壢')
PreparedStatement (Statement子介面)
動態SQL JDBC指令
PreparedStatement pstmt = null;
( :arrow_right_hook: 藉由Connection產生的方法務必關閉 )
pstmt = con.prepareStatement(SQL);
SQL指令
未知&動態改變,用?表達
SQL = "INSERT INTO DEPARTMENT (DEPTNO, DNAME, LOC) VALUES (?, ?, ?)";
PreparedStatement動態SQL JDBC指令
1. ResultSet executeQuery();
2. int executeUpdate();
()無須傳入SQL指令參數
動態新增需要再JDBC set入資訊
pstmt.setTYPE(1索引值,欄位名稱);
pstmt.executeUpdate();
-
JDBC簡化
- 靜態Statement
stmt = con.createStatement();
查詢 ->箭頭-> ResultSet
stmt = con.createStatement();
rs = stmt.executeQuery(SQL);
while(rs.next()) {
int deptno = rs.getInt("DEPTNO");
System.out.println("DEPTNO = " + deptno);
}
查詢 -> ResultSet
pstmt = con.prepareStatement(SQL);
rs = pstmt.executeQuery();
while(rs.next()) {
dept.setDeptno(rs.getInt("DEPTNO"));
}
新增,修改,刪除
stmt = con.createStatement();
int count = stmt.executeUpdate(SQL);
System.out.println(count+"筆資料新增成功");
新增,修改,刪除
pstmt = con.prepareStatement(SQL);
pstmt.setInt(1,deptno); //??
pstmt.executeUpdate();
-
新增,修改,刪除
pstmt = con.prepareStatement(SQL);
pstmt.setInt(1,deptno);
pstmt.executeUpdate();
- 載入驅動
Class.forName(DRIVER);
- 建立連線
con = DriverManager.getConnection(URL, USER, PASSWORD);
-