Java 数据库介绍

数据库基础

数据库概念

数据库（Database）是按照数据结构来组织、存储和管理数据的仓库，它是存储数据集合的容器，可以看做一个文件夹，里面存放着一系列有关联的数据表。

数据表（Table）是一种结构化的数据，以列和行为单位来存储的一种结构化数据，类似于Excel中的一个工作簿。

常见的数据库类型

关系型数据库管理系统（RDBMS）是目前最常见的数据库类型。常见的关系型数据库管理系统有：

• MySQL：MySQL是一种流行的开源数据库管理系统，广泛应用于Web开发等领域。它支持多线程、多用户、事务、外键等功能，提高了性能和数据安全性。

• Oracle：Oracle是一家全球领先的关系型数据库管理系统厂商，早期主要面向企业用户，但由于其先进功能和高性能，在近些年已广为应用于互联网行业中。

• SQL Server：Microsoft SQL Server是由Microsoft公司开发的关系数据库管理系统。它支持多种标准的SQL查询语言，包括T-SQL、ANSI SQL等。

• PostgreSQL：PostgreSQL是一种功能强大、高性能的开源关系型数据库管理系统，它提供ACID事务支持、查询优化器和完整性约束等特性。

• SQLite：SQLite是一种嵌入式关系数据库管理系统。由于其小巧、快速、可嵌入性强、易于使用的特点，被广泛应用于移动设备、桌面应用等场景。

数据表结构设计

数据表是数据库中的核心组成部分，因此对数据表的结构设计要非常谨慎。设计数据表时，需要考虑以下几个方面：

• 主键：每个表都应该有一个主键，可以采用自增列或GUID等方式来生成主键。

• 外键：适当使用外键可以帮助构建合理的关系型数据库结构，这有助于避免数据的不一致和不完整。

• 数据类型：根据实际情况选择适当的数据类型。例如，年龄和价格可以采用整型，姓名和地址可以采用字符串类型等。

• 索引：数据库字段加索引可以提高查询效率，需要根据实际查询情况来选择适当的字段加索引。

示例：假设我们要设计一个用户表，包含用户ID、用户名、年龄、性别、所在地等字段，可以使用如下SQL语句创建该表：

CREATE TABLE `user` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `username` VARCHAR(50) NOT NULL,
  `age` INT,
  `gender` VARCHAR(10),
  `location` VARCHAR(50)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SQL基础

SQL概述

SQL是一种用于访问和处理关系型数据库的标准化语言。它是一种声明性的语言，用户只需要描述需要的数据，而不需要指定如何从数据库中获取数据。SQL语言包括四个基本操作：SELECT（查询）、INSERT（插入）、UPDATE（更新）和DELETE（删除）。

基本的SQL语句

• SELECT语句用于从数据库中查询数据：

SELECT 列名1, 列名2, ... FROM 表名 WHERE 条件;

示例：

SELECT * FROM user;

• INSERT语句用于将数据插入到数据库表中：

INSERT INTO 表名 (列名1, 列名2, ...) VALUES (值1, 值2, ...);

示例：

INSERT INTO user (username, age) VALUES ('johndoe', 25);

• UPDATE语句用于更新数据库表中的数据：

UPDATE 表名 SET 列名 = 值 WHERE 条件;

示例：

UPDATE user SET age = 26 WHERE id = 1;

• DELETE语句用于删除数据库表中的数据：

DELETE FROM 表名 WHERE 条件;

示例：

DELETE FROM user WHERE id = 1;

数据过滤和排序

在SELECT语句中，可以使用WHERE子句对查询结果进行过滤，其中可以使用比较运算符（如=、<>、<、>等）和逻辑运算符（如AND、OR、NOT等）来构建条件。

示例：

SELECT * FROM user WHERE age >= 18 AND gender = 'male';

可以使用ORDER BY子句对查询结果进行排序，默认是升序排序。

示例：

SELECT * FROM user ORDER BY age DESC;

聚合函数

聚合函数用于对数据进行汇总计算。常见的聚合函数有：

• COUNT：用于计算某列的行数。
• SUM：用于计算某列的总和。
• AVG：用于计算某列的平均值。
• MAX：用于计算某列的最大值。
• MIN：用于计算某列的最小值。

示例：

SELECT COUNT(*) FROM user;
SELECT SUM(age) FROM user;
SELECT AVG(age) FROM user WHERE gender = 'male';
SELECT MAX(age) FROM user;
SELECT MIN(age) FROM user;

连接查询

连接查询用于联结多个数据表，通过指定表之间的关系，将多个表的数据合并在一起进行查询。

• 内连接（INNER JOIN）：只返回两个表之间满足连接条件的记录。

示例：

SELECT * FROM table1 INNER JOIN table2 ON table1.column1 = table2.column1;

• 左外连接（LEFT JOIN）：返回左表的所有记录，以及右表中满足连接条件的记录。

示例：

SELECT * FROM table1 LEFT JOIN table2 ON table1.column1 = table2.column1;

• 右外连接（RIGHT JOIN）：返回右表的所有记录，以及左表中满足连接条件的记录。

示例：

SELECT * FROM table1 RIGHT JOIN table2 ON table1.column1 = table2.column1;

以上只是基础的介绍，更深入的使用，需要结合实际情况进行不同的组合使用。

JDBC简介

Java数据库连接（JDBC）是Java语言中用于编写与各种关系型数据库进行通信的应用程序所需的API。JDBC提供了一种与数据库进行通信的标准方式，以便可以使用Java编程语言来编写与各种关系型数据库进行交互的应用程序。

JDBC基本概念

JDBC是Java语言中一个与API，它定义了Java程序如何执行与数据库进行交互的操作。JDBC提供了一种透明的方式，使开发人员可以使用Java程序连接到各种不同类型的数据库，包括MySQL、Oracle、Microsoft SQL Server等，并执行如查询、更新等操作。

JDBC API基于一组接口和类，它们定义了Java程序访问关系数据库的方式。这些接口和类封装了许多JDBC驱动程序需要使用的底层细节。JDBC提供了一种标准的方式来编写Java应用程序与各种关系型数据库进行通信。

JDBC驱动程序的类型

JDBC驱动程序是用于连接Java应用程序与不同类型的关系型数据库的程序库。JDBC驱动程序有四种类型，包括：

• JDBC-ODBC桥接器驱动程序：它通过ODBC驱动程序链接数据库，属于JDBC-ODBC桥接器。它被认为是最早的JDBC驱动程序。由于它使用的是ODBC驱动程序，因此它存在协议转换问题。

• 原生API驱动程序：它由数据库厂商提供，可以针对不同的数据库直接连接，不需要通过中间件。但是，不同的数据库API是不同的，因此使用原生API驱动程序需要学习每个数据库API的语言。

• 网络协议驱动程序：它使用中间件访问数据库，该中间件通常是由数据库厂商提供的。网络协议驱动程序是用于与Oracle和Sybase等数据库系统进行通信的常见驱动程序。

• 纯Java驱动程序：它是一个全Java编写的驱动程序，不需要中间件支持，它将所有的JDBC的操作转换为特定数据库的本地API。目前，绝大部分数据库的Java驱动程序都是使用JDBC4.0驱动程序类的纯Java程序。

JDBC应用程序基本流程

JDBC应用程序通常遵循以下步骤：

• 使用特定的JDBC驱动程序链接到数据库系统；
• 创建Statement对象，用于向数据库发送SQL命令；
• 使用Statement对象执行SQL命令；
• 处理返回的结果，并将它们用于更新Java应用程序的数据；
• 关闭Statement对象和数据库连接。

下面是使用JDBC进行查询的示例：

//加载JDBC驱动程序
Class.forName("com.mysql.jdbc.Driver");

//建立与数据库的连接
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/demo", "root", "password");

//创建SQL命令
String sql = "SELECT * FROM user WHERE age > 18";

//创建Statement对象
Statement statement = connection.createStatement();

//执行SQL命令
ResultSet resultSet = statement.executeQuery(sql);

//处理返回结果
while (resultSet.next()) {
    // 获取数据
    String name = resultSet.getString("name");
    int age = resultSet.getInt("age");
    System.out.println(name + " " + age);
}

//关闭资源
resultSet.close();
statement.close();
connection.close();

JDBC连接池

JDBC连接池是将多个数据库连接管理在一起，以便它们可以在需要时立即共享。JDBC连接池增加了应用程序的性能，因为应用程序不需要建立新的数据库连接并释放它们。相反，应用程序从连接池中获取已经建立的连接，同时将连接返回到连接池。这个过程称为连接池管理。

JDBC连接池分为两种类型：

• 内部连接池：连接池是在应用程序内部管理的，它们使用Java的一些基本类库来管理多个数据库连接。

• 外部连接池：外部连接池是由数据库服务器提供的，它们使用数据库服务器的连接池来管理数据库连接。

JDBC异常处理

JDBC API中的方法都会抛出SQLException异常，因为许多JDBC操作都可能失败。对于JDBC异常处理，我们可以使用try-catch语句来处理SQLException异常。例如：

try {
    //连接数据库
    Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/demo", "root", "password");
    //创建Statement对象
    Statement stmt = conn.createStatement();
    //执行SQL语句
    ResultSet res = stmt.executeQuery(sql);
} catch (SQLException e) {
    e.printStackTrace();
}

除了使用try-catch语句捕获异常之外，我们还可以使用throws语句将SQLException向上抛出。

public void selectRows() throws SQLException {
    //连接数据库
    Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/demo", "root", "password");
    //创建Statement对象
    Statement stmt = conn.createStatement();
    //执行SQL语句
    ResultSet res = stmt.executeQuery(sql);
}

基于JDBC的数据库连接和操作

JDBC连接数据库

JDBC连接数据库的过程分为以下几步：

1. 加载数据库驱动：通过Class.forName()方法加载数据库驱动类，例如Class.forName("com.mysql.cj.jdbc.Driver")。
2. 创建数据库连接：使用DriverManager.getConnection()方法创建与数据库的连接，需要传入数据库的URL、用户名和密码，例如Connection connection = DriverManager.getConnection(url, username, password)。
3. 开启数据库连接：通过connection对象的conn.setAutoCommit(false)方法将自动提交事务关闭，开启手动提交事务模式。

数据库操作

增加数据

使用PreparedStatement接口和SQL语句的参数占位符实现数据的插入操作：

String sql = "INSERT INTO users (name, age, email) VALUES (?, ?, ?)";
PreparedStatement statement = connection.prepareStatement(sql);
statement.setString(1, "John");
statement.setInt(2, 25);
statement.setString(3, "john@example.com");
int rowsInserted = statement.executeUpdate();

查询数据

使用PreparedStatement和ResultSet接口实现数据的查询操作：

String sql = "SELECT * FROM users WHERE age > ?";
PreparedStatement statement = connection.prepareStatement(sql);
statement.setInt(1, 20);
ResultSet resultSet = statement.executeQuery();
while (resultSet.next()) {
    String name = resultSet.getString("name");
    int age = resultSet.getInt("age");
    String email = resultSet.getString("email");
    // 处理查询结果
}

更新数据

使用PreparedStatement执行更新操作：

String sql = "UPDATE users SET age = ? WHERE name = ?";
PreparedStatement statement = connection.prepareStatement(sql);
statement.setInt(1, 30);
statement.setString(2, "John");
int rowsUpdated = statement.executeUpdate();

删除数据

使用PreparedStatement执行删除操作：

String sql = "DELETE FROM users WHERE name = ?";
PreparedStatement statement = connection.prepareStatement(sql);
statement.setString(1, "John");
int rowsDeleted = statement.executeUpdate();

关闭数据库连接

在完成所有CRUD操作后，需要关闭数据库连接以释放资源：

// 关闭结果集
resultSet.close();
// 关闭PreparedStatement
statement.close();
// 提交事务并关闭连接
connection.commit();
connection.close();

关闭连接的顺序要注意，先关闭结果集和PreparedStatement，再提交事务并关闭连接。

JDBC事务处理详解

在数据库操作中，事务处理是非常重要的。它可以保证多个操作的原子性，在一系列操作执行完成之前，数据不会被其他操作所干扰。JDBC提供了一些接口和方法，使我们可以更好地进行事务处理，保证数据的正确性和一致性。

事务介绍

事务是指一组操作被设计为一个不可分割的工作单元，事务中的操作全部成功或全部失败。事务具有以下特性：

1. 原子性（Atomicity）：事务中的一系列操作都作为一个不可分割的原子单元执行。这意味着，如果事务中的任何一条语句出现故障，整个事务将被中止，所有操作都将被回滚到它们开始执行之前的状态。

2. 一致性（Consistency）：事务执行后，数据库状态应该从一个合法状态变为另一个合法状态。这意味着事务的执行不能破坏数据库的完整性约束。

3. 隔离性（Isolation）：事务之间的操作应该是相互隔离的，事务之间的操作不能互相干扰。

4. 持久性（Durability）：一旦事务提交，其对数据库的影响应该是永久的。即使系统故障，数据库也应该能够恢复到事务提交后的状态。

在 JDBS 中，为了保证事务的特性，我们使用连接对象的 setAutoCommit() 方法将自动提交关闭，以在代码中实现事务处理。

事务处理

事务处理可以分为以下几步：

1. 开启事务：通过连接对象connection的setAutoCommit(false)方法开启事务模式。
2. 执行SQL语句：使用PreparedStatement执行相应的SQL语句。
3. 提交事务：使用connection的commit()方法提交事务。
4. 回滚事务：使用connection的rollback()方法回滚事务。

如果在操作过程中出现异常，会抛出SQLException，我们需要捕获异常并执行回滚操作，以确保数据的一致性。

实现示例

下面是一段JDBC事务处理的示例代码，用于实现数据的转账操作：

Connection connection = null;
PreparedStatement pstm1 = null;
PreparedStatement pstm2 = null;
try {
    Class.forName("com.mysql.jdbc.Driver");
    connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456");
    connection.setAutoCommit(false); // 开启事务
    String sql1 = "UPDATE account SET balance = balance - ? WHERE id = ?";
    pstm1 = connection.prepareStatement(sql1);
    pstm1.setDouble(1, amount);
    pstm1.setInt(2, fromId);
    int row1 = pstm1.executeUpdate();
    if (row1 != 1) {
        throw new SQLException("转出账户更新异常");
    }
    String sql2 = "UPDATE account SET balance = balance + ? WHERE id = ?";
    pstm2 = connection.prepareStatement(sql2);
    pstm2.setDouble(1, amount);
    pstm2.setInt(2, toId);
    int row2 = pstm2.executeUpdate();
    if (row2 != 1) {
        throw new SQLException("转入账户更新异常");
    }
    connection.commit(); // 提交事务
} catch (ClassNotFoundException | SQLException e) {
    if (connection != null) {
        connection.rollback(); // 回滚事务
    }
} finally {
    try {
        if (pstm2 != null) {
            pstm2.close();
        }
        if (pstm1 != null) {
            pstm1.close();
        }
        if (connection != null) {
            connection.close();
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

在以上代码中，我们首先开启事务，然后分别执行更新操作并判断更新的行数是否等于1。如果更新行数不等于1，则说明更新操作未能成功执行，此时需要抛出SQLException异常并进行回滚操作。如果所有更新操作都成功执行，就提交事务并关闭连接。

假设在转账的过程中，如果发生了以下任意一种情况，事务就会被回滚。

1. 当账户 A 的余额小于要转账的金额时：此时会抛出 SQLException 异常，回滚事务。

2. 当账户 B 的余额达到数据库的最大值时：此时也会抛出 SQLException 异常，回滚事务。

回滚事务时，数据表中既没有 A 账户的转出记录，也没有 B 账户的转入记录，从而保证了事务的原子性。

因此，在 JDBC 的事务处理中，我们需要通过回滚等手段确保事务的原子性。同时，隔离性的实现也非常重要，因为多个并发事务的执行可能会相互干扰。同样，在这个示例中，如果账户 A 和 B 在同一时刻被多个人同时访问并同时进行转账，就有可能会发生数据冲突，导致数据的不一致性。
JDBC 中的事务处理需要遵循事务特性，即原子性、一致性、隔离性以及持久性。只有在正确的实现这些特性的基础上，才能够进行有效的事务处理。

通过JDBC实现事务处理可以确保多个操作的原子性，保证数据的正确性和一致性。我们需要使用connection.setAutoCommit(false)方法开启事务，使用connection.commit()方法提交事务，使用connection.rollback()方法回滚事务。在操作中出现异常时需要进行异常处理和回滚操作。