本筆記基於2019年9月的尚硅谷JDBC核心技术(新版jdbc)

https://www.bilibili.com/video/BV1eJ411c7rf

JDBC

JDBC，全稱Java Database Connectivity
為java用來統一管理連接資料庫的接口
JDBC挺短的，且為JavaWeb的前置知識，我就歸到同一個筆記分類了

教學用資料庫

他官方的有編碼問題，這個是修正版的，用工具自己導入

PS1.DataGrip的導入常常出問題，針對導入最好用別款GUI

PS2.少了一個BLOB學到那邊自己插入即可

-- --------------------------------------------------------
-- 主机:                           127.0.0.1
-- 服务器版本:                        8.0.22 - MySQL Community Server - GPL
-- 服务器操作系统:                      Win64
-- HeidiSQL 版本:                  11.1.0.6116
-- --------------------------------------------------------

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!50503 SET NAMES utf8mb4 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;


-- 导出 jdbc_learn 的数据库结构
DROP DATABASE IF EXISTS `jdbc_learn`;
CREATE DATABASE IF NOT EXISTS `jdbc_learn` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
USE `jdbc_learn`;

-- 导出  表 jdbc_learn.customers 结构
DROP TABLE IF EXISTS `customers`;
CREATE TABLE IF NOT EXISTS `customers` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(15) DEFAULT NULL,
  `email` varchar(20) DEFAULT NULL,
  `birth` date DEFAULT NULL,
  `photo` mediumblob,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- 正在导出表  jdbc_learn.customers 的数据：~0 rows (大约)
/*!40000 ALTER TABLE `customers` DISABLE KEYS */;
INSERT INTO `customers` (`id`, `name`, `email`, `birth`, `photo`) VALUES
	(1, '汪峰', 'wf@126.com', '2010-02-02', NULL),
	(2, '王菲', 'wangf@163.com', '1988-12-26', NULL),
	(3, '林志玲', 'linzl@gmail.com', '1984-06-12', NULL),
	(4, '汤唯', 'tangw@sina.com', '1986-06-13', NULL),
	(5, '成龙', 'Jackey@gmai.com', '1955-07-14', NULL),
	(6, '迪丽热巴', 'reba@163.com', '1983-05-17', NULL),
	(7, '刘亦菲', 'liuyifei@qq.com', '1991-11-14', NULL),
	(8, '陈道明', 'bdf@126.com', '2014-01-17', NULL),
	(10, '周杰伦', 'zhoujl@sina.com', '1979-11-15', NULL),
	(12, '黎明', 'LiM@126.com', '1998-09-08', NULL),
	(13, '张学友', 'zhangxy@126.com', '1998-12-21', NULL),
	(16, '朱茵', 'zhuyin@126.com', '2014-01-16', NULL),
	(18, '贝多芬', 'beidf@126.com', '2014-01-17', NULL);
/*!40000 ALTER TABLE `customers` ENABLE KEYS */;

-- 导出  表 jdbc_learn.examstudent 结构
DROP TABLE IF EXISTS `examstudent`;
CREATE TABLE IF NOT EXISTS `examstudent` (
  `FlowID` int NOT NULL AUTO_INCREMENT,
  `Type` int DEFAULT NULL,
  `IDCard` varchar(18) DEFAULT NULL,
  `ExamCard` varchar(15) DEFAULT NULL,
  `StudentName` varchar(20) DEFAULT NULL,
  `Location` varchar(20) DEFAULT NULL,
  `Grade` int DEFAULT NULL,
  PRIMARY KEY (`FlowID`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=gb2312;

-- 正在导出表  jdbc_learn.examstudent 的数据：~0 rows (大约)
/*!40000 ALTER TABLE `examstudent` DISABLE KEYS */;
INSERT INTO `examstudent` (`FlowID`, `Type`, `IDCard`, `ExamCard`, `StudentName`, `Location`, `Grade`) VALUES
	(1, 4, '412824195263214584', '200523164754000', '张锋', '郑州', 85),
	(2, 4, '222224195263214584', '200523164754001', '孙朋', '大连', 56),
	(3, 6, '342824195263214584', '200523164754002', '刘明', '沈阳', 72),
	(4, 6, '100824195263214584', '200523164754003', '赵虎', '哈尔滨', 95),
	(5, 4, '454524195263214584', '200523164754004', '杨丽', '北京', 64),
	(6, 4, '854524195263214584', '200523164754005', '王小红', '太原', 60);
/*!40000 ALTER TABLE `examstudent` ENABLE KEYS */;

-- 导出  表 jdbc_learn.order 结构
DROP TABLE IF EXISTS `order`;
CREATE TABLE IF NOT EXISTS `order` (
  `order_id` int NOT NULL AUTO_INCREMENT,
  `order_name` varchar(20) DEFAULT NULL,
  `order_date` date DEFAULT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- 正在导出表  jdbc_learn.order 的数据：~0 rows (大约)
/*!40000 ALTER TABLE `order` DISABLE KEYS */;
INSERT INTO `order` (`order_id`, `order_name`, `order_date`) VALUES
	(1, 'AA', '2010-03-04'),
	(2, 'BB', '2000-02-01'),
	(4, 'GG', '1994-06-28');
/*!40000 ALTER TABLE `order` ENABLE KEYS */;

-- 导出  表 jdbc_learn.user 结构
DROP TABLE IF EXISTS `user`;
CREATE TABLE IF NOT EXISTS `user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(10) NOT NULL,
  `password` varchar(15) NOT NULL DEFAULT '123456',
  `address` varchar(25) DEFAULT NULL,
  `phone` varchar(15) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- 正在导出表  jdbc_learn.user 的数据：~5 rows (大约)
/*!40000 ALTER TABLE `user` DISABLE KEYS */;
INSERT INTO `user` (`id`, `name`, `password`, `address`, `phone`) VALUES
	(1, '章子怡', 'qwerty', 'Beijing', '13788658672'),
	(2, '郭富城', 'abc123', 'HongKong', '15678909898'),
	(3, '林志玲', '654321', 'Taiwan', '18612124565'),
	(4, '梁静茹', '987654367', 'malaixiya', '18912340998'),
	(5, 'LadyGaGa', '123456', 'America', '13012386565');
/*!40000 ALTER TABLE `user` ENABLE KEYS */;

-- 导出  表 jdbc_learn.user_table 结构
DROP TABLE IF EXISTS `user_table`;
CREATE TABLE IF NOT EXISTS `user_table` (
  `user` varchar(20) DEFAULT NULL,
  `password` varchar(20) DEFAULT NULL,
  `balance` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- 正在导出表  jdbc_learn.user_table 的数据：~4 rows (大约)
/*!40000 ALTER TABLE `user_table` DISABLE KEYS */;
INSERT INTO `user_table` (`user`, `password`, `balance`) VALUES
	('AA', '123456', 1000),
	('BB', '654321', 1000),
	('CC', 'abcd', 2000),
	('DD', 'abcder', 3000);
/*!40000 ALTER TABLE `user_table` ENABLE KEYS */;

/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

連接至資料庫

從java透過JDBC連接到資料庫需要3個要素:驅動、網址、帳號密碼
範例如下，接著一項一項說明:

Class.forName("com.mysql.cj.jdbc.Driver"); // mysql 8.0
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc_learn?user=root&password=1234");

驅動Driver

硬體層面的支援

在程式中不需要直接去訪問實現了Driver介面的類，而是由驅動程式管理器類(java.sql.DriverManager)去調用這些Driver實現
需要手動下載驅動並導入jar包，之後透過靜態方法Class.forname("驅動名")載入

- Oracle的驅動:oracle.jdbc.driver.OracleDriver
- mysql 5的驅動:com.mysql.jdbc.Driver
- mysql 8的驅動:com.mysql.cj.jdbc.Driver

網址URL

宣告實際要連的資料庫位置

JDBC URL的標準由三部分組成，例如:jdbc:子協議:子名稱

協議:JDBC URL中的協議總是jdbc
子協議:子協定用於標識一個資料庫驅動程式
子名稱:一種標識資料庫的方法。子名稱可以依不同的子協議而變化，用子名稱的目的是為了定位資料庫提供足夠的資訊。包含主機名稱(對應服務端的ip位址)，埠號，資料庫名

幾種常用資料庫的 JDBC URL

MySQL的連接URL編寫方式:

jdbc:mysql://主機名稱稱:mysql服務埠號/資料庫名稱?參數=值&參數=值

jdbc:mysql://localhost:3306/atguigu

jdbc:mysql://localhost:3306/atguigu?useUnicode=true&characterEncoding=utf8

jdbc:mysql://localhost:3306/atguigu?user=root&password=123456

// 8.0以後還要加時區，不然可能報錯
jdbc:mysql://localhost:3306/DBname?characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2B8

Oracle 9i的連接URL編寫方式:

jdbc:oracle:thin:@主機名稱稱:oracle服務埠號:資料庫名稱

jdbc:oracle:thin:@localhost:1521:atguigu

SQLServer的連接URL編寫方式:

jdbc:sqlserver://主機名稱稱:sqlserver服務埠號:DatabaseName=資料庫名稱

jdbc:sqlserver://localhost:1433:DatabaseName=atguigu

關於時區

指的是mysql server的時區，可以用指令查看

show variables like '%time_zone%';

如果沒有特別設定過，返回值應該是空跟SYSTEM
即mysql server自動使用該電腦的系統時區，入鄉隨俗的意思

為了防止錯誤，我們可以手動設定，在MySQL目錄下找到my.ini
在[mysqld]下添加一行 default-time-zone='+08:00'

重啟服務然後使用指令
set global time_zone='+8:00';

這樣mysql server的時區就鎖定在+8了，

接著回到JDBC的URL部分，設定:
&serverTimezone=Asia/Shanghai
或
&serverTimezone=GMT%2B8
 -- %2B是+的意思

帳號密碼

連到資料庫了當然要輸入帳號密碼

user,password可以用"屬性名=屬性值“方式告訴資料庫
可以調用 DriverManager 類的 getConnection() 方法建立到資料庫的連接
通常我們將這些配置寫到jdbc.properties文件檔，達成解偶方便擴展與維護
範例:

@Test
public  void testConnection() throws Exception {
//1.載入設定檔
InputStream is = ConnectionTest.class.getClassLoader().getResourceAsStream("jdbc.properties");
Properties pros = new Properties();
pros.load(is);

//2.讀取配置資訊
String user = pros.getProperty("user");
String password = pros.getProperty("password");
String url = pros.getProperty("url");
String driverClass = pros.getProperty("driverClass");

//3.載入驅動
Class.forName(driverClass);

//4.獲取連接
Connection conn = DriverManager.getConnection(url,user,password);
System.out.println(conn);

//5. 永遠別忘記關閉
    conn.close();
}

配置文件jdbc.properties

user=root
password=1234
url=jdbc:mysql://localhost:3306/jdbc_learn
driverClass=com.mysql.cj.jdbc.Driver

封裝成一個自訂的JDBCUtils類方便練習使用

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class JDBCUtils {
    public static Connection getConnection() throws Exception {
        // 造流載入配置文件
        InputStream resourceAsStream = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
        Properties pros = new Properties();
        pros.load(resourceAsStream);

        // 讀取出對應的值
        String user = pros.getProperty("user");
        String password = pros.getProperty("password");
        String url = pros.getProperty("url");
        String driverClass = pros.getProperty("driverClass");

        // 驅動
        Class.forName(driverClass);

        // 連接
        Connection connection = DriverManager.getConnection(url, user, password);

        return connection;
    }
    // 關閉資源
    public static void closeResource(Connection conn, Statement ps) {
        try {
            assert conn != null;
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            assert ps != null;
            ps.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

操作資料

在 java.sql 包中有 3 個介面分別定義了對資料庫的調用的不同方式:
- Statement:用於執行靜態 SQL 語句並返回它所生成結果的物件
  - 有sql注入風險，通常不用
- PreparedStatement:SQL 語句被預編譯並存儲在此物件中，可以使用此物件多次高效地執行該語句
- CallableStatement:用於執行 SQL 存儲過程

PreparedStatement

其實這些操作實際開發在框架都封裝好了，不過從基底了解總是好的

Prepared顧名思義，預編好了SQL語句，參數的部分用?問號佔位
相當於在DBMS緩存了該語句，執行效率更高
由於預編時會把單引號'轉義成\'，所以可以防止用單引號截斷產生歧意的SQL注入
- 實際上仍有一個漏洞就是%符號並不會被轉義，可能跟like查詢構成一種拖垮效能的大量查詢惡意注入

增刪改

連接資料庫獲取Connection物件
編寫sql語句
調用prepareStatement(sql)方法，獲取prepareStatement實例
使用setObject()依序填充佔位符，注意從1開始
執行executeUpdate()方法
關閉資源

// 舉例
Connection conn = JDBCUtils.getConnection();
String sql = "update customers set name = ? where id = ?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setObject(1, "Tommy");
ps.setObject(2, "2");
ps.executeUpdate();
JDBCUtils.closeResource(conn, ps);

注意點

Java中的sql語句我們通常用String去裝
如果用了()記得前後要對應好
而結尾的;不需要加，但加了大部分情況也不會報錯
- 使用Batched操作Statement時最尾的若有;會出錯

查詢

由於查詢有返回值，需要有容器來裝結果，利用反射造這個容器

連接資料庫獲取Connection物件
編寫sql語句
調用prepareStatement(sql)方法，獲取prepareStatement實例
使用setObject()依序填充佔位符，注意從1開始
執行executeQuery()方法，得到返回的結果集ResultSet
從結果集調用getMetaData()方法，獲取結果集元資料ResultSetMetaData
從元資料調用get方法獲取資料筆數與列的別名columnCount、columnLabel
透過反射創造物件並賦予對應的值
關閉資源

// 舉例
@Test
public void testList() {
    String sql = "select id customerId,name,email from customers where id < ?";
    List<Customers> list = getForList(Customers.class, sql, 5);
    list.forEach(System.out::println);
}

public <T> List<T> getForList(Class<T> tClass, String sql, Object... args) {
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet resultSet = null;
    try {
        // 連線
        conn = JDBCUtils.getConnection();
        ps = conn.prepareStatement(sql);
        // 填入佔位符
        for (int i = 0; i < args.length; i++) {
            ps.setObject(i + 1, args[i]); // 注意sql的index從1開始
        }
        // 開查
        resultSet = ps.executeQuery();
        // 從結果集得出元資料
        ResultSetMetaData metaData = resultSet.getMetaData();
        // 查總共有幾列資料
        int columnCount = metaData.getColumnCount();

        // 造一個用來接收最後結果的list
        ArrayList<T> list = new ArrayList<>();
        // 開始把從資料庫來的結果集賦到接收的ORM類
        while (resultSet.next()) {
            T t = tClass.newInstance();
            for (int i = 0; i < columnCount; i++) {
                // 這個for循環類似印表機，開始橫向掃描，例如第一筆資料"張三"
                // 他的學號是OOO，地址是XXX，電話是...

                // 查列名，豎的，注意是取列的別名
                String columnLabel = metaData.getColumnLabel(i + 1);
                // 取該筆資料的值
                Object value = resultSet.getObject(i + 1);
                // 用反射賦值
                Field field = tClass.getDeclaredField(columnLabel); // 從列名找到對應類的屬性名
                field.setAccessible(true); // 暴力獲取權限
                field.set(t, value); // 反射賦值給物件t
            }
            list.add(t); // 一筆資料完整了，貼進去最終要返回的集合
            // 回到上面while (resultSet.next()，如果有下一筆"李四"繼續
        }
        return list; // 返回整張表
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        assert conn != null;
        JDBCUtils.closeResource(conn, ps);
        try {
            assert resultSet != null;
            resultSet.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    return null;
}

用來接收的類

public class Customers {
    private int customerId;
    private String name;
    private String email;
///  ... 構造器，toString方法等等

ORM的概念

英文叫 Object Relational Mapping，翻譯成中文為物件關聯對
把關聯式資料庫中的一筆資料，對應成一個類的物件
簡單來說是這樣的對應:

MySQL	Java
一張表Table	一個自訂類Class
表中的欄位(列名)	類中的屬性
一筆資料	一個物件

Java與SQL對應的資料類型

Java	SQL
boolean	BIT
byte	TINYINT
short	SMALLINT
int	INTEGER
long	BIGINT
String	CHAR,VARCHAR,LONGVARCHAR
byte array	BINARY , VAR BINARY
java.sql.Date	DATE
java.sql.Time	TIME
java.sql.Timestamp	TIMESTAMP

ResultSetMetaData的常用方法

可用於獲取關於 ResultSet 物件中列的類型和屬性資訊的物件

getColumnName(int column):獲取指定列的名稱
getColumnLabel(int column):獲取指定列的別名
getColumnCount():返回當前 ResultSet 物件中的列數。
getColumnTypeName(int column):檢索指定列的資料庫特定的類型名稱。
getColumnDisplaySize(int column):指示指定列的最大標準寬度，以字元為單位
isNullable(int column):指示指定列中的值是否可以為 null
isAutoIncrement(int column):指示是否自動為指定列進行編號，這樣這些列仍然是唯讀的

操作BLOB類型

把檔案編成二進位存到資料庫中，實際開發沒人會這樣存，了解即可

造一個流去接收或儲存
使用setBlob()方法傳檔案至資料庫
以resultSet使用getBlob(列標籤)接收成blob檔案

// 存，範例
Connection conn = JDBCUtils.getConnection();
PreparedStatement ps = conn.prepareStatement("select photo from customers where id=?;");
FileInputStream is = new FileInputStream("G:\\Java\\code\\guigu\\JDBC\\src\\400.png");
ps.setBlob(1, is);
System.out.println(ps.executeUpdate());

// 讀，範例
Connection conn = JDBCUtils.getConnection();
PreparedStatement ps = conn.prepareStatement("select photo from customers where id=?;");
ps.setObject(1, 1);
ResultSet resultSet = ps.executeQuery();
if (resultSet.next()) {
    Blob photo = resultSet.getBlob("photo");
    // 造一個流去裝Blob
    InputStream bs = photo.getBinaryStream();
    FileOutputStream os = new FileOutputStream("G:\\Java\\code\\guigu\\JDBC\\src\\new2.png");
    // 複製到檔案輸出流
    byte[] buffer = new byte[1024];
    int len;
    while ((len = bs.read(buffer)) != -1) {
        os.write(buffer, 0, len);
    }
    conn.close();
    bs.close();
    os.close();
}

注意resultset的遊標的起始位置是第一行之前，如果沒有next()一下就會出現如下異常:Before start of result set
- 確定只會有一個結果可以用if判斷一下，否則還是老實while
另外要注意mysql中blob類型的大小問題
- tinyblob:僅255個字元
- blob:最大限制到65KB
- mediumblob:限制到16MB
- longblob:可達4GB
有時候明明大小在限制內，卻還是報錯，要到my.ini下修改環境變數max_allowed_packet=

批量插入

用緩存、手動控制提交來增加效率

addBatch(String):添加需要批量處理的SQL語句或是參數
executeBatch():執行批量處理語句
clearBatch():清空緩存的數據

// 範例
Class.forName("com.mysql.cj.jdbc.Driver");
// 連線時增加允許批次處理的參數"&rewriteBatchedStatements=true"
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc_learn" +
        "?user=root&password=1234&rewriteBatchedStatements=true");
// 設定不要自動提交
conn.setAutoCommit(false);
PreparedStatement ps = conn.prepareStatement("insert into goods (name) value (?)");
for (int i = 1; i < 10000; i++) {
    ps.setString(1, "name_" + i);
    // 積攢sql命令
    ps.addBatch();
    if (i % 500 == 0) {
        // 每500筆執行一次並清空
        ps.executeBatch();
        ps.clearBatch();
    }
}
conn.commit();

driver版本需要5.1.13以上才能使用rewriteBatchedStatements=true

小結

連接資料庫三要素:驅動、URL、帳號密碼
PreparedStatement的好處:預編譯、可批次處理效率高，有轉義''單引號防sql注入，可操作blob類型
操作資料的步驟:
- 連接資料庫、寫sql語句、獲取PreparedStatement實例、填佔位符…
  - 增刪改:executeUpdate、關閉連接
  - 查:executeQuery返回結果集、從結果集取元數據、.next()取出資料、反射賦值、關閉連接

上次修改於 2021-12-29