了解JDBC
?JDBC Intro
JDBC stands for Java Database Connectivity. Java與各種數據庫連接的標準Java api.
The JDBC library includes APIs for each of the tasks commonly associated with database usage:
? Making a connection to a database
? Creating SQL or MySQL statements
? Executing that SQL or MySQL queries in the database
? Viewing & Modifying the resulting records
?
jdbc API由以下兩個package構成：
- java.sql
- javax.sql
Package java.sql Description
Provides the API for accessing and processing data stored in a data source (usually a relational database) using the JavaTM?programming language. This API includes a framework whereby different drivers can be installed dynamically to access different data sources. Although the JDBCTM?API is mainly geared to passing SQL statements to a database, it provides for reading and writing data from any data source with a tabular format. The reader/writer facility, available through the?javax.sql.RowSet?group of interfaces, can be customized to use and update data from a spread sheet, flat file, or any other tabular data source.Package javax.sql Description
Provides the API for server side data source access and processing from the JavaTM?programming language. This package supplements the?java.sql?package and, as of the version 1.4 release, is included in the Java Platform, Standard Edition (Java SETM). It remains an essential part of the Java Platform, Enterprise Edition (Java EETM).The?javax.sql?package provides for the following:
Applications use the?DataSource?and?RowSet?APIs directly, but the connection pooling and distributed transaction APIs are used internally by the middle-tier infrastructure.
?
JDBC API主要包括（斜體代表接口，需驅動程序提供者來具體實現）：
- DriverManager：負責加載各種不同驅動程序（Driver），并根據不同的請求，向調用者返回相應的數據庫連接（Connection）。
- Driver：驅動程序，會將自身加載到DriverManager中去，并處理相應的請求并返回相應的數據庫連接（Connection）。
- Connection：數據庫連接，負責進行與數據庫間的通訊，SQL執行以及事務處理都是在某個特定Connection環境中進行的?？梢援a生用以執行SQL的Statement。
- Statement：用以執行SQL查詢和更新（針對靜態SQL語句和單次執行）。
- PreparedStatement：用以執行包含動態參數的SQL查詢和更新（在服務器端編譯，允許重復執行以提高效率）。
- CallableStatement：用以調用數據庫中的存儲過程。
- SQLException：代表在數據庫連接的建立和關閉和SQL語句的執行過程中發生了例外情況（即錯誤）。
?
指定driver的方法(old)：
Class.forName("com.mysql.jdbc.Driver"); // method 1new com.mysql.jdbc.Driver() ; //method 2, mysql as an exmple, 創建driver對象，加載數據庫驅動System.setProperty("jdbc.drivers","com.mysql.jdbc.Driver"); // method 3
注意JDBC 4.0 已不需要手動注冊driver, JDBC文檔原文：
Applications no longer need to explictly load JDBC drivers using?Class.forName(). Existing programs which currently load JDBC drivers using?Class.forName()?will continue to work without modification.
JDBC 4.0 Drivers must include the file?META-INF/services/java.sql.Driver
?JDBC4.0 Example
mysql-connector-java:
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java --><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><version>6.0.4</version></dependency> View CodeCode Example
Driver: mysql-connector-java
1 /** 2 * Created by artificerPi on 2016/2/23. 3 * JDBC Program, create database 4 * 5 */ 6 7 // STEP 1. Import required packages 8 import java.sql.*; 9 10 public class JDBCExample { 11 // JDBC driver name and database URL 12 static final String JDBC_DRIVER="com.mysql.jdbc.Driver"; 13 static final String DB_URL="jdbc:mysql://localhost"; 14 15 // Database credentials 16 static final String USER ="root"; 17 static final String PASS="passw0rd"; 18 19 public static void main(String[] args){ 20 Connection conn = null; 21 Statement stmt = null; 22 23 try{ 24 //STEP2 : Register JDBC driver 25 Class.forName(JDBC_DRIVER); // load driver class 26 27 //STEP3 : Open a connection 28 System.out.println("Connecting to database..."); 29 conn = DriverManager.getConnection(DB_URL,USER,PASS); 30 31 // STEP 4: Execute a query 32 System.out.println("Creating statement ..."); 33 stmt = conn.createStatement(); 34 35 String sql; 36 sql="CREATE DATABASE STUDENTS"; 37 stmt.executeUpdate(sql); 38 System.out.println("Database created successfully..."); 39 }catch(SQLException se){ 40 //Handle errors for JDBC 41 se.printStackTrace(); 42 }catch (Exception e){ 43 // Handle errors for Class.forName 44 e.printStackTrace(); 45 }finally { 46 //STEP5: finally block used to close resources 47 try{ 48 if (stmt != null) 49 stmt.close(); 50 }catch (SQLException se2){// nothing we can do 51 }try{ 52 if(conn!=null) 53 conn.close(); 54 }catch (SQLException se){ 55 se.printStackTrace(); 56 }// end finally try 57 }// end try 58 System.out.println("GoodBye!"); 59 }//end main 60 }// end FirstExample?
使用PreparedStatement
1 PreparedStatement ps = null; 2 ResultSet rs = null; 3 try { 4 ps = conn.prepareStatement("SELECT i.*, j.* FROM Omega i, Zappa j WHERE i = ? AND j = ?"); 5 // 使用問號作為參數的標示 6 7 // 進行參數設置 8 // 與大部分Java API中下標的使用方法不同，字段的下標從1開始，1代表第一個問號 9 // 當然，還有其他很多針對不同類型的類似的PreparedStatement.setXXX()方法 10 ps.setString(1, "Poor Yorick"); 11 ps.setInt(2, 8008); 12 13 // 結果集 14 rs = ps.executeQuery(); 15 while (rs.next()) { 16 int numColumns = rs.getMetaData().getColumnCount(); 17 for (int i = 1; i <= numColumns; i++) { 18 // 與大部分Java API中下標的使用方法不同，字段的下標從1開始 19 // 當然，還有其他很多的方式（ResultSet.getXXX()）獲取數據 20 System.out.println("COLUMN " + i + " = " + rs.getObject(i)); 21 } 22 } 23 } catch (SQLException e) { 24 // 異常處理 25 } finally { // 使用finally進行資源釋放 26 try { 27 rs.close(); 28 ps.close(); 29 } catch (SQLException e) { 30 } // 異常處理：忽略close()時的錯誤 31 }?
?
事務 Example
1 boolean autoCommitDefault = conn.getAutoCommit(); 2 try { 3 conn.setAutoCommit(false); 4 5 /* 在此基于有事務控制的conn執行你的代碼 */ 6 7 conn.commit(); 8 } catch (Throwable e) { 9 try { 10 conn.rollback(); 11 } catch (Throwable ignore) { 12 } 13 throw e; 14 } finally { 15 try { 16 conn.setAutoCommit(autoCommitDefault); 17 } catch (Throwable ignore) { 18 } 19 } View Code?
JDBC Cycle
?
?
參考：
http://www.tutorialspoint.com/jdbc/
https://docs.oracle.com/javase/8/docs/technotes/guides/jdbc/
https://zh.wikipedia.org/wiki/Java%E6%95%B0%E6%8D%AE%E5%BA%93%E8%BF%9E%E6%8E%A5
https://www.ntu.edu.sg/home/ehchua/programming/java/JDBC_Basic.html
轉載于:https://www.cnblogs.com/7explore-share/p/5927802.html
總結
- 上一篇： hdu-4811 Ball
- 下一篇： LYDSY模拟赛day2 Divisor