一、連接資料庫 1. 步驟 2. 獲取Driver的方式通過new新建：Driver driver = new com.mysql.jdbc.Driver(); 通過反射創建類的實例：Driver driver = (Driver)Class.forName(driverClass).newI ...

一、連接資料庫

1. 步驟

//1.創建一個Driver實現類的對象
Driver driver = new com.mysql.jdbc.Driver();//註意拋異常

//2.準備 url 和 info
String url = "jdbc:mysql://localhost:3306/test";
//Oracle:"jdbc:oracle:thin:@localhost:1512:sid"
//SQLServer:"jdbc:microsoft:sqlserver//localhost:1433:DatabaseName=sid"
//MySql:"jdbc:mysql://localhost:3306/sid"

Properties info = new Properties();
info.put("user", "root");
info.put("password","1230");

//3.獲取連接
Connection connection = driver.connect(url, info);//註意拋異常

2. 獲取Driver的方式

通過new新建：Driver driver = new com.mysql.jdbc.Driver();
通過反射創建類的實例：Driver driver = (Driver)Class.forName(driverClass).newInstance();　　//driverClass為全類名，即 com.mysql.jdbc.Driver

通過DriverManager獲取資料庫連接（載入資料庫驅動程式即註冊驅動）：DriverManager.registerDriver(Class.forName(driverClass).newInstance()); 可簡化為 Class.forName(driverClass); 可載入多個驅動

//載入資料庫驅動程式 (可載入多個驅動程式)（註冊驅動）
//DriverManager.registerDriver(Class.forName(driverClass).newInstance());
Class.forName(driverClass);
Class.forName(driverClass2);

//通過getConnection方法獲得連接
//getConnection(String url, String user, String password) throws SQLException
Connection connection = DriverManager.getConnection(jdbcUrl, user, password);

3. 提取方法來獲得Connection

實現方式：把URL、賬號、密碼等信息放入 jdbc.properties 文件，通過反射獲取

public class Utils {
    public Connection getConnection() throws Exception {
        String driverClass = null;
        String jdbcUrl = null;
        String user = null;
        String password = null;

        // 讀取類路徑下的jdbc.properties文件
        InputStream in = getClass().getClassLoader().getResourceAsStream("jdbc.properties");
        Properties properties = new Properties();
        properties.load(in);
        driverClass = properties.getProperty("driver");
        jdbcUrl = properties.getProperty("jdbcUrl");
        user = properties.getProperty("user");
        password = properties.getProperty("password");

/*        // 通過反射創建類的實例
        Driver driver = (Driver) Class.forName(driverClass).newInstance();

        Properties info = new Properties();
        info.put("user", user);
        info.put("password", password);
        Connection connection = driver.connect(jdbcUrl, info);*/
        
        Class.forName(driverClass);
        Connection connection = DriverManager.getConnection(jdbcUrl, user, password);

        return connection;
    }

}

二、資料庫操作

前提：連接資料庫的前提下，才能進行操作

1. 執行更新操作（插入、修改、刪除）

//1.獲得連接
Connection conn = driver.connect(url, info);
String sql="...";     //sql語句

//2.獲取statement對象
Statement statement = conn.createStatement();

//3.執行
statement.executeUpdate(sql);

//4.關閉
statement.close();
conn.close();

2. 查詢操作

//1.通過連接獲得statement對象
Statement statement = conn.createStatement();
String sql = "...";

//2.執行
ResultSet rs = statement.executeQuery(sql);

//3.遍歷輸出
while(rs.next()){
     int id = rs.getInt(1);
     String name = rs.getString("name");
}

//4.關閉
rs.close();
statement.close();
conn.close();

3. 給SQL語句設置參數——PreparedStatement

父類：Statement

//1.通過連接獲得preparedStatement對象
String sql = "insert into customers (name, email, birth) values(?,?,?)";
preparedStatement = conn.prepareStatement(sql);　　//與無參的Statement對象不同，獲得對象時就得傳入sql語句，執行語句時不用再傳入sql語句

//2.設置參數
preparedStatement.setString(1, "zhangsan");
preparedStatement.setString(2, "[email protected]");
preparedStatement.setDate(3, new Date(new java.util.Date().getTime()));

//3.執行
preparedStatement.executeUpdate();

//4.獲得ResultSet
ResultSet rs = preparedStatement.getResultSet();

獲取插入記錄的主鍵值

實現方式：使用重載的preparedStatement(sql, flag)來生成preparedStatement對象

preparedStatement = conn.preparedStatement(sql, Statement.RETURN_GENERATED_KEYS);

//生成的結果集里只有一列，即主鍵編號
ResultSet rs = preparedStatement.getGeneratedKeys();
if(rs.next()){

    //獲得主鍵
    String generatedKey = rs.getObject(1);

}

4. 調用函數&存儲過程——CallableStatement

父類：Statement

// 1. 通過 Connection 對象的 prepareCall()方法創建一個 CallableStatement 對象的實例。
//sql字元串指明如何調用存儲過程：?=call name(param, ..., param) 或 callname(param, ..., param)
String sql = "{?= call sum_salary(?, ?)}";
CallableStatement callableStatement = connection.prepareCall(sql);

// 2. 通過 CallableStatement 對象的 reisterOutParameter() 方法註冊 OUT 參數。
callableStatement.registerOutParameter(1, Types.NUMERIC);
callableStatement.registerOutParameter(3, Types.NUMERIC);
            
// 3. 通過 CallableStatement 對象的 setXxx() 方法設定 IN 或 IN OUT 參數。
//若想將參數預設值設為null, 可以使用 setNull() 方法。
callableStatement.setInt(2, 80);
            
// 4. 通過 CallableStatement 對象的 execute() 方法執行存儲過程。
callableStatement.execute();
            
// 5. 如果所調用的是帶返回參數的存儲過程, 還需要通過 CallableStatement 對象的 getXxx() 方法獲取其返回值。
double sumSalary = callableStatement.getDouble(1);
long empCount = callableStatement.getLong(3);

三、JDBC元數據

1. ResultSetMetaData

方法簽名：ResultSetMetaData getMetaData() //通過 resultSet.getMetaData() 方法獲得 ResultSetMetaData 對象

int getColumnCount() //查詢結果集中的列數

String getColumnName(int colum) //獲取查詢結果集中指定的列別名，從1開始

String getColumnLabel(int colum) //獲取查詢結果集中指定的列別名，從1開始

    //在資料庫中查詢記錄，轉換成與表相對應的實體類對象
    public <T> T get(Class<T> clazz, String sql, Object... args) throws Exception {
        T entity = null;

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        //1.獲得connection
        connection = getConnection();
        
        //2.設置參數
        preparedStatement = connection.prepareStatement(sql);
        for (int i = 0; i < args.length; i++) {
            preparedStatement.setObject(i + 1, args[i]);
        }
        
        //3.執行，並獲得resultSet
        resultSet = preparedStatement.executeQuery();

        //4.通過resultSet獲得resultSetMetaData
        ResultSetMetaData resultSetMetaData = resultSet.getMetaData();

        Map<String, Object> values = new HashMap<>();
        if (resultSet.next()) {
            //5.把結果放入哈希表中
            for (int i = 0; i < resultSetMetaData.getColumnCount(); i++) {
                String columnLabel = resultSetMetaData.getColumnLabel(i + 1);
                Object columnValue = resultSet.getObject(i + 1);

                values.put(columnLabel, columnValue);
            }
            
            //6.為相應實體類對象設置屬性值
            if(values.size() > 0){
                entity = clazz.newInstance();
                for(Map.Entry<String, Object> entry: values.entrySet()){
                    String fieldName = entry.getKey();
                    Object value = entry.getValue();
                    
                    BeanUtils.setProperty(entity, fieldName, value);//需要使用beanutils工具包
                }
            }
        }
        return entity;
    }

2. DatabaseMetaData

方法簽名：DatabaseMetaData getMetaData() //通過 connection.getMetaData() 方法獲得

作用：可以得到資料庫本身的一些信息

DatabaseMetaData data = connection.getMetaData();
            //1. 得到資料庫的版本號
int version = data.getDatabaseMajorVersion();
            
//2. 得到連接到資料庫的用戶名
String user = data.getUserName();
            
//3. 得到 MySQL 中有哪些資料庫
ResultSet resultSet = data.getCatalogs();

四、處理Blob大對象數據

LOB：大對象，存儲大量數據的一種數據類型。

BLOB：二進位大對象。

1. 讀取 Blob 數據

實現方式：使用 getBlob 方法讀取到 Blob 對象

調用 Blob 的 getBinaryStream() 方法得到輸入流。再使用 IO 操作即可。

...
resultSet = preparedStatement.executeQuery();
          
if(resultSet.next()){
    int id = resultSet.getInt(1);
    String name = resultSet.getString(2);
    String email = resultSet.getString(3);
                
    //1.獲得Blob對象
    Blob picture = resultSet.getBlob(5);
    
    //2.獲得輸入流
    InputStream in = picture.getBinaryStream();
    
    //3.輸出到flower.jpg
    OutputStream out = new FileOutputStream("flower.jpg");
    byte [] buffer = new byte[1024];
    int len = 0;
    while((len = in.read(buffer)) != -1){
        out.write(buffer, 0, len);
    }
                
    in.close();
    out.close();
}

2. 寫入Blob數據

作用：實現將本地圖片寫入資料庫

String sql = "INSERT INTO customers(name, email, birth, picture) VALUES(?,?,?,?)";
//通過連接 獲得preparedStatement對象
preparedStatement = connection.prepareStatement(sql);

//設置參數
preparedStatement.setString(1, "ABCDE");
preparedStatement.setString(2, "[email protected]");
preparedStatement.setDate(3, new Date(new java.util.Date().getTime()));

//1.創建輸入流
InputStream inputStream = new FileInputStream("Hydrangeas.jpg");
//2.設置Blob參數
preparedStatement.setBlob(4, inputStream);
            
preparedStatement.executeUpdate();

五、事務

1. 方法

//取消預設提交後才能進行事務

connection.setAutoCommit(false);　　//取消預設提交並開始事務

//提交事務

connection.commit();

//回滾

connection.rollback();

2. 資料庫的隔離級別

READ UNCOMMITTED　　：讀未提交數據，允許事務讀取未被其他事務提交的變更，臟讀、不可重覆讀、幻讀都會出現。
READ COMMITTED　　：讀已提交數據，只允許事務讀取已經被其他事務提交的變更。可避免臟讀，不可重覆讀和幻讀仍然存在。
REPEATABLE READ　　：可重覆讀，確保事務可以多次從一個欄位中讀取相同的值，在這個事務持續期間，禁止其他事務對這個欄位進行更新。可避免臟讀和不可重覆讀，幻讀問題仍然存在。
SERIALIZABLE　　　　：串列化，確保事務可以從一個表中讀取相同的行，在這個事務持續期間，禁止其他事務對該表執行操作，所有併發問題都可避免，但性能低下。

通過Connection的setTransactionIsolation來設置事務的隔離級別。

例：connection.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);

註：mysql的預設隔離級別是REPEATABLE READ。

六、批量處理

方法簽名：preparedStatement.addBatch();　　 //加入Batch

preparedStatement.executeBatch();　　//批量處理

preparedStatement.clearBatch();　　 //清除當前的積累量

//批量插入十萬條數據
sql = "INSERT INTO customers VALUES(?,?,?)";
preparedStatement = connection.prepareStatement(sql);
Date date = new Date(new java.util.Date().getTime());
for(int i = 0; i < 100000; i++){
    preparedStatement.setInt(1, i + 1);
    preparedStatement.setString(2, "name_" + i);
    preparedStatement.setDate(3, date);
                
    //"積攢" SQL 
    preparedStatement.addBatch();　　//Statement寫法：statement.addBatch(String sql);
                
    //當 "積攢" 到一定程度, 就統一的執行一次. 並且清空先前 "積攢" 的 SQL
    if((i + 1) % 300 == 0){
        preparedStatement.executeBatch();
        preparedStatement.clearBatch();
    }
}
            
//若總條數不是批量數值的整數倍, 則還需要再額外的執行一次. 
if(100000 % 300 != 0){
    preparedStatement.executeBatch();
    preparedStatement.clearBatch();
}

七、資料庫連接池

必要性

傳統連接使用DriverManager獲取，再驗證用戶名密碼，執行完畢再斷開連接。資料庫的連接資源沒有得到很好的重覆利用。

資料庫連接池負責分配、管理和釋放資料庫連接，它允許應用程式重覆使用一個現有的資料庫連接，而不是重新建立一個。

資料庫連接池是通過實現DataSource介面來實現的。

1. DBCP

需要2 個jar 包：

dbcp
pool

註：dbcp依賴於 pool

final BasicDataSource dataSource = new BasicDataSource();
        
//1. 為數據源實例指定必須的屬性
dataSource.setUsername("root");
dataSource.setPassword("1230");
dataSource.setUrl("jdbc:mysql://localhost:3306/test");
dataSource.setDriverClassName("com.mysql.jdbc.Driver");
        
//2. 指定數據源的一些可選的屬性
//1). 指定資料庫連接池中初始化連接數的個數
dataSource.setInitialSize(5);
        
//2). 指定最大的連接數: 同一時刻可以同時向資料庫申請的連接數
dataSource.setMaxActive(5);
        
//3). 指定小連接數: 在資料庫連接池中保存的最少的空閑連接的數量 
dataSource.setMinIdle(2);
        
//4).等待資料庫連接池分配連接的最長時間. 單位為毫秒. 超出該時間將拋出異常. 
dataSource.setMaxWait(1000 * 5);
        
//3. 從數據源中獲取資料庫連接
Connection connection = dataSource.getConnection();

通過DataSourceFactory創建連接池（DataSource），這樣可以直接從配置文件中獲取信息

//從Properties文件里獲得數據
Properties properties = new Properties();
InputStream inStream = DBCPTest.class.getClassLoader().getResourceAsStream("dbcp.properties");
properties.load(inStream);

//使用工廠創建連接池
DataSource dataSource = BasicDataSourceFactory.createDataSource(properties);

2. C3P0

@Test
public void testC3P0() throws Exception{
    ComboPooledDataSource cpds = new ComboPooledDataSource();
    cpds.setDriverClass( "com.mysql.jdbc.Driver" ); //loads the jdbc driver            
    cpds.setJdbcUrl( "jdbc:mysql://localhost:3306/test" );
    cpds.setUser("root");
    cpds.setPassword("1230");
        
    System.out.println(cpds.getConnection()); 
}

通過載入配置文件來獲得信息

DataSource dataSource = new ComboPooledDataSource("helloc3p0");

dataSource.getConnection();

<!-- 配置文件helloc3p0.xml -->
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
    <named-config name="helloc3p0">
        
        <!-- 指定連接數據源的基本屬性 -->
        <property name="user">root</property>
        <property name="password">1230</property>
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql:///atguigu</property>
        
        <!-- 若資料庫中連接數不足時, 一次向資料庫伺服器申請多少個連接 -->
        <property name="acquireIncrement">5</property>
        <!-- 初始化資料庫連接池時連接的數量 -->
        <property name="initialPoolSize">5</property>
        <!-- 資料庫連接池中的最小的資料庫連接數 -->
        <property name="minPoolSize">5</property>
        <!-- 資料庫連接池中的最大的資料庫連接數 -->
        <property name="maxPoolSize">10</property>

        <!-- C3P0 資料庫連接池可以維護的 Statement 的個數 -->
        <property name="maxStatements">20</property>
        <!-- 每個連接同時可以使用的 Statement 對象的個數 -->
        <property name="maxStatementsPerConnection">5</property>
    </named-config>
</c3p0-config>

八、DBUtils

註：dbutils只是對底層的封裝

QueryRunner可能會有線程安全問題，逐一不要創建多個實例

1. update方法

//1.創建QueryRunner實現類
QueryRunner queryRunner = new QueryRunner();
String sql = "DELETE FROM customers WHERE id IN (?,?)";

//2.使用update方法
queryRunner.update(connection, sql, 12, 13);

2. query方法

方法簽名：queryRunner.query(Connection connection, String sql, ResultSetHandler rs);

使用：在實現ResultSetHandler介面的類的handle方法中編寫對結果集的處理方法。

Object object = queryRunner.query(conn, sql, new ResultSetHandler() {
    @Override
    public Object handle(ResultSet rs) throws SQLException {
        List<Customer> customers = new ArrayList<>();

        while (rs.next()) {
            int id = rs.getInt(1);
            String name = rs.getString(2);
            String email = rs.getString(3);
            Date birth = rs.getDate(4);

            Customer customer = new Customer(id, name, email, birth);
            customers.add(customer);
        }
        //也是query方法的返回值
        return customers;
    }
});

以下Handler類實現了ResultSetHandler介面

1）BeanHandler

功能：返回查詢的第一條記錄對應的那個實體類對象

原理：BeanHandler類的handle方法中通過反射創建傳入的Class類對應的類的對象，並用set方法為該對象設置屬性，然後返回該對象。

註意：查詢語句中的列名要和實體類的屬性相對應，若不對應則需要使用別名如下name：

String sql = "SELECT id, name customerName, email, birth FROM customers WHERE id >= ?";

// 1. 創建 QueryRunner 對象
QueryRunner queryRunner = new QueryRunner();

// 2. 調用query方法
Object object = queryRunner.query(conn, sql, new BeanHandler<>(Customer.class)，5);

2）BeanListHandler

作用：可以通過查詢記錄獲得實體類對象集合

若可以查詢到記錄，則返回傳入的Class對象對應的對象集合。

String sql = "SELECT id, name customerName, email, birth FROM customers";
QueryRunner queryRunner = new QueryRunner();

// 返回List<Customer> 對象
Object object = queryRunner.query(conn, sql, new BeanListHandler<>(Customer.class));

3）MapHandler

作用：返回查詢的第一條記錄對應的Map對象鍵：列名（非別名）；值：列的值

QueryRunner queryRunner = new QueryRunner();
String sql = "SELECT id, name customerName, email, birth FROM customers WHERE id = ?";

Map<String, Object> map = queryRunner.query(connection, sql, new MapHandler(), 4);

4）MapListHandler

作用：返回查詢的所有記錄對應的Map對象的集合

QueryRunner queryRunner = new QueryRunner();
String sql = "SELECT id, name, email, birth FROM customers";

List<Map<String, Object>> mapList = queryRunner.query(connection, sql, new MapListHandler());

5）ScalarHandler

作用：返回結果集中的第一列對應的數據

QueryRunner queryRunner = new QueryRunner();
String sql = "SELECT name FROM customers WHERE id = ?";

JDBC相關知識