JDK 8 + MySQL 8 简易演示

$\text {JDBC}$ （ $\text {Java Database Connectivity}$ ）是 $\text {Java API}$ ，用于连接和执行与数据库的交互，是 $\text {Java}$ 应用程序与数据库之间的桥梁，允许开发者在 $\text {Java}$ 应用中执行SQL语句以查询和更新数据。 $\text {JDBC}$ 为多种关系数据库提供了统一的访问方式，使得 $\text {Java}$ 程序能够跨数据库平台工作，而无需为每种数据库编写特定的代码。

加载 $\text {JDBC}$ 驱动

1	Class.forName("com.mysql.cj.jdbc.Driver");

创建连接

1⃣getConnection(String url)

1 2	String url = "jdbc:mysql://localhost:3306/myDatabase?user=myUsername&myPassword"; Connection conn = DriverManager.getConnection(url);

2⃣getConnection(String url, Properties info)

String url = "jdbc:mysql://localhost:3306/myDatabase";
Properties props = new Properties();
props.setProperty("user", "myUsername");
props.setProperty("password", "myPassword");
Connection conn = DriverManager.getConnection(url, props);

3⃣getConnection(String url, String user, String password)

String url = "jdbc:mysql://localhost:3306/myDatabase";
String user = "myUsername";
String password = "myPassword";
Connection conn = DriverManager.getConnection(url, user, password);

创建 $\text {Statement}$

1	Statement stmt = connection.createStatement();

执行 $\text {SQL}$

executeQuery(String sql)：执行返回单个 ResultSet 对象的 $\text {SQL}$ 语句，通常用于 $\text {SELECT}$ 操作。
executeUpdate(String sql)：用于执行 $\text {INSERT}$ 、 $\text {UPDATE}$ 或 $\text {DELETE}$ 语句，或者不返回任何内容的 $\text {SQL}$ 语句。
execute(String sql)：可用于执行任意的 $\text {SQL}$ 语句，返回一个布尔值，表示返回的是 ResultSet 对象还是更新计数。

1 2	String sql = "SELECT * FROM Customers;"; ResultSet rs = stmt.executeQuery(sql);

处理结果

while (rs.next()) {
            String customerId = rs.getString("CustomerID");
            String companyName = rs.getString("CompanyName");
            System.out.println("Customer ID: " + customerId + ", Company Name: " + companyName);
        }

关闭资源

1
2
3

rs.close();
stmt.close();
conn.close();

⭐ $\text {NorthwindCustomers.java}$

import java.sql.*;

public class NorthwindCustomers {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        // 1. 加载 JDBC 驱动
        Class.forName("com.mysql.cj.jdbc.Driver");

        // 2. 创建连接
        String url = "jdbc:mysql://localhost:3306/northwind?user=root&password=root";
        Connection conn = DriverManager.getConnection(url);

        // 3. 创建 Statement
        Statement stmt = conn.createStatement();

        // 4. 执行 SQL
        String sql = "SELECT * FROM Customers;";
        ResultSet rs = stmt.executeQuery(sql);

        // 5. 处理结果
        while (rs.next()) {
            String customerId = rs.getString("CustomerID");
            String customerName = rs.getString("CustomerName");
            System.out.println("Customer ID: " + customerId + ", CustomerName: " + customerName);
        }

        // 6. 关闭资源
        rs.close();
        stmt.close();
        conn.close();
    }
}

Customer ID: 1, CustomerName: Alfreds Futterkiste
Customer ID: 2, CustomerName: Ana Trujillo Emparedados y helados
Customer ID: 3, CustomerName: Antonio Moreno Taquería
Customer ID: 4, CustomerName: Around the Horn
Customer ID: 5, CustomerName: Berglunds snabbköp
Customer ID: 6, CustomerName: Blauer See Delikatessen
Customer ID: 7, CustomerName: Blondel père et fils
Customer ID: 8, CustomerName: Bólido Comidas preparadas
Customer ID: 9, CustomerName: Bon app’’
Customer ID: 10, CustomerName: Bottom-Dollar Marketse

…

Statement vs PreparedStatement

加载 $\text {JDBC}$ 驱动

同上 ⬆

创建连接

同上 ⬆

创建 PreparedStatement 对象

指定一个 $\text {SQL}$ 语句模板，其中包含了一个或多个参数占位符（通常是 ?）。

1 2	String sql = "SELECT * FROM Customers WHERE Country = ?;"; PreparedStatement pstmt = conn.prepareStatement(sql);

设置 $\text {SQL}$ 参数

方法	描述	数据类型示例
`setInt`	设置 `int` 类型的参数值	`int`
`setLong`	设置 `long` 类型的参数值	`long`
`setDouble`	设置 `double` 类型的参数值	`double`
`setFloat`	设置 `float` 类型的参数值	`float`
`setShort`	设置 `short` 类型的参数值	`short`
`setByte`	设置 `byte` 类型的参数值	`byte`
`setBoolean`	设置 `boolean` 类型的参数值	`boolean`
`setString`	设置 `String` 类型的参数值	`String`
`setDate`	设置 `java.sql.Date` 类型的参数值	`java.sql.Date`
`setTime`	设置 `java.sql.Time` 类型的参数值	`java.sql.Time`
`setTimestamp`	设置 `java.sql.Timestamp` 类型的参数值	`java.sql.Timestamp`
`setObject`	设置任意类型的参数值	`Object`
`setNull`	设置参数值为 SQL `NULL`	-
`setBlob`	设置 `java.sql.Blob` 类型的参数值	`java.sql.Blob`
`setClob`	设置 `java.sql.Clob` 类型的参数值	`java.sql.Clob`
`setArray`	设置 `java.sql.Array` 类型的参数值	`java.sql.Array`
`setBinaryStream`	设置二进制流作为参数值	`InputStream` (二进制流)
`setCharacterStream`	设置字符流作为参数值	`Reader` (字符流)
`setAsciiStream`	设置 ASCII 流作为参数值	`InputStream` (ASCII 流)

1	pstmt.setString(1, "Germany");

执行 $\text {SQL}$

1	ResultSet rs = pstmt.executeQuery();

处理结果

while (rs.next()) {
            String customerId = rs.getString("CustomerID");
            String customerName = rs.getString("CustomerName");
            System.out.println("Customer ID: " + customerId + ", CustomerName: " + customerName);
        }

关闭资源

1
2
3

rs.close();
pstmt.close();
conn.close();

⭐ $\text {NorthwindCustomersPreparedStatement.java}$

import java.sql.*;

public class NorthwindCustomersPreparedStatement {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        // 1. 加载 JDBC 驱动
        Class.forName("com.mysql.cj.jdbc.Driver");

        // 2. 创建连接
        String url = "jdbc:mysql://localhost:3306/northwind?user=root&password=root";
        Connection conn = DriverManager.getConnection(url);

        // 3. 创建 PreparedStatement 对象
        String sql = "SELECT * FROM Customers WHERE Country = ?;";
        PreparedStatement pstmt = conn.prepareStatement(sql);

        // 4. 设置 SQL 参数
        pstmt.setString(1, "Germany");

        // 5. 执行 SQL
        ResultSet rs = pstmt.executeQuery();

        // 6. 处理结果
        while (rs.next()) {
            String customerId = rs.getString("CustomerID");
            String customerName = rs.getString("CustomerName");
            System.out.println("Customer ID: " + customerId + ", CustomerName: " + customerName);
        }

        // 7. 关闭资源
        rs.close();
        pstmt.close();
        conn.close();
    }
}

Customer ID: 1, CustomerName: Alfreds Futterkiste
Customer ID: 6, CustomerName: Blauer See Delikatessen
Customer ID: 17, CustomerName: Drachenblut Delikatessend
Customer ID: 25, CustomerName: Frankenversand
Customer ID: 39, CustomerName: Königlich Essen
Customer ID: 44, CustomerName: Lehmanns Marktstand
Customer ID: 52, CustomerName: Morgenstern Gesundkost
Customer ID: 56, CustomerName: Ottilies Käseladen
Customer ID: 63, CustomerName: QUICK-Stop
Customer ID: 79, CustomerName: Toms Spezialitäten
Customer ID: 86, CustomerName: Die Wandernde Kuh

CRUD

🌟 $\text {Customer.java}$

public class Customer {
    private String customerName;
    private String contactName;
    private String address;
    private String city;
    private String postalCode;
    private String country;

    // Constructor
    public Customer(String customerName, String contactName, String address, String city, String postalCode, String country) {
        this.customerName = customerName;
        this.contactName = contactName;
        this.address = address;
        this.city = city;
        this.postalCode = postalCode;
        this.country = country;
    }

    // Getters and setters for each field
    // ...

    @Override
    public String toString() {
        return "Customer{" +
                "customerName='" + customerName + '\'' +
                ", contactName='" + contactName + '\'' +
                ", address='" + address + '\'' +
                ", city='" + city + '\'' +
                ", postalCode='" + postalCode + '\'' +
                ", country='" + country + '\'' +
                '}';
    }
}

⭐ $\text {NorthwindCustomersPreparedStatementCRUD.java}$

private static final String URL = "jdbc:mysql://localhost:3306/northwind";
private static final String USERNAME = "root";
private static final String PASSWORD = "root";

private Connection getConnection() throws SQLException {
	return DriverManager.getConnection(URL, USERNAME, PASSWORD);
}

$\text {Insert}$

@Test
public void testInsert() {
	// INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country) VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway');
	String sql = "INSERT INTO Customers (CustomerName, ContactName, Address, City,PostalCode, Country) VALUES (?, ?, ?, ?, ?, ?)";
	try (Connection conn = getConnection(); PreparedStatement pstmt = conn.prepareStatement(sql)) {
		pstmt.setString(1, "Cardinal");
		pstmt.setString(2, "Tom B. Erichsen");
		pstmt.setString(3, "Skagen 21");
		pstmt.setString(4, "Stavanger");
		pstmt.setString(5, "4006");
		pstmt.setString(6, "Norway");
		pstmt.executeUpdate();
	} catch (SQLException e) {
		e.printStackTrace();
	}
}

$\text {Update}$

@Test
public void testUpdate() {
    // UPDATE Customers SET ContactName = 'Alfred Schmidt', City = 'Frankfurt' WHERE CustomerID = 92;
    String sql = "UPDATE Customers SET ContactName = ?, City = ? WHERE CustomerID = ?";
    try (Connection conn = getConnection(); PreparedStatement pstmt = conn.prepareStatement(sql)) {
        pstmt.setString(1, "Alfred Schmidt");
        pstmt.setString(2, "Frankfurt");
        pstmt.setInt(3, 92);
        pstmt.executeUpdate();
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

$\text {Delete}$

@Test
public void testDelete() {
    // DELETE FROM Customers WHERE CustomerName='Cardinal';
    String sql = "DELETE FROM Customers WHERE CustomerName= ?";
    try (Connection conn = getConnection(); PreparedStatement pstmt = conn.prepareStatement(sql)) {
        pstmt.setString(1, "Cardinal");
        pstmt.executeUpdate();
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

$\text {SelectALL}$

@Test
public void testSelect() {
    List<Customer> customers = new ArrayList<>();
    String sql = "SELECT CustomerName, ContactName, Address, City, PostalCode, Country FROM Customers";
    try (Connection conn = getConnection(); PreparedStatement pstmt = conn.prepareStatement(sql); ResultSet rs = pstmt.executeQuery()) {
        while (rs.next()) {
            Customer customer = new Customer(
                    rs.getString("CustomerName"),
                    rs.getString("ContactName"),
                    rs.getString("Address"),
                    rs.getString("City"),
                    rs.getString("PostalCode"),
                    rs.getString("Country")
            );
            customers.add(customer);
        }
        customers.forEach(System.out::println);
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

Customer{customerName=‘Alfreds Futterkiste’, contactName=‘Maria Anders’, address=‘Obere Str. 57’, city=‘Berlin’, postalCode=‘12209’, country=‘Germany’}
Customer{customerName=‘Ana Trujillo Emparedados y helados’, contactName=‘Ana Trujillo’, address=‘Avda. de la Constitución 2222’, city=‘México D.F.’, postalCode=‘5021’, country=‘Mexico’}
Customer{customerName=‘Antonio Moreno Taquería’, contactName=‘Antonio Moreno’, address=‘Mataderos 2312’, city=‘México D.F.’, postalCode=‘5023’, country=‘Mexico’}
Customer{customerName=‘Around the Horn’, contactName=‘Thomas Hardy’, address=‘120 Hanover Sq.’, city=‘London’, postalCode=‘WA1 1DP’, country=‘UK’}
Customer{customerName=‘Berglunds snabbköp’, contactName=‘Christina Berglund’, address=‘Berguvsvägen 8’, city=‘Luleå’, postalCode=‘S-958 22’, country=‘Sweden’}

主键回显

在 $\text {JDBC}$ 中，如果想在插入记录后获取该记录的自动生成的主键，可以使用 PreparedStatement 的 getGeneratedKeys() 方法。为了使 getGeneratedKeys() 方法有效，需要在创建 PreparedStatement 对象时指定 Statement.RETURN_GENERATED_KEYS 作为参数。

@Test
public void testInsertAndGetKey() {
    String sql = "INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country) VALUES (?, ?, ?, ?, ?, ?)";
    try (Connection conn = getConnection(); PreparedStatement pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
        pstmt.setString(1, "Cardinal");
        pstmt.setString(2, "Tom B. Erichsen");
        pstmt.setString(3, "Skagen 21");
        pstmt.setString(4, "Stavanger");
        pstmt.setString(5, "4006");
        pstmt.setString(6, "Norway");
        int affectedRows = pstmt.executeUpdate();
        if (affectedRows > 0) {
            try (ResultSet generatedKeys = pstmt.getGeneratedKeys()) {
                if (generatedKeys.next()) {
                    int generatedKey = generatedKeys.getInt(1);
                    System.out.println("Inserted record's ID: " + generatedKey);
                } else {
                    throw new SQLException("Creating user failed, no ID obtained.");
                }
            }
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

批量插入

在 $\text {JDBC}$ 中进行批量插入，可以使用 PreparedStatement 的批处理能力，这通常通过 addBatch() 和 executeBatch() 方法实现。这种方法可以显著提高大量插入操作的效率，因为减少了与数据库的通信次数。

@Test
public void testBatchInsert() {
    String sql = "INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country) VALUES (?, ?, ?, ?, ?, ?)";
    try (Connection conn = getConnection(); PreparedStatement pstmt = conn.prepareStatement(sql)) {
        List<Customer> customers = Arrays.asList(
                new Customer("Customer1", "Contact1", "Address1", "City1", "PostalCode1", "Country1"),
                new Customer("Customer2", "Contact2", "Address2", "City2", "PostalCode2", "Country2"),
                // ... 添加更多的客户
                new Customer("Customer99", "Contact99", "Address99", "City99", "PostalCode99", "Country99")
        );
        for (Customer customer : customers) {
            pstmt.setString(1, customer.getCustomerName());
            pstmt.setString(2, customer.getContactName());
            pstmt.setString(3, customer.getAddress());
            pstmt.setString(4, customer.getCity());
            pstmt.setString(5, customer.getPostalCode());
            pstmt.setString(6, customer.getCountry());
            pstmt.addBatch();
        }
        int[] affectedRecords = pstmt.executeBatch();
        for (int affectedRecord : affectedRecords) {
            System.out.println("Affected records: " + affectedRecord);
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

事务实现

CREATE DATABASE BankDatabase;

USE BankDatabase;

CREATE TABLE BankAccount (
    AccountNumber INT AUTO_INCREMENT PRIMARY KEY,
    AccountHolderName VARCHAR(100),
    Balance DECIMAL(10, 2)
);

INSERT INTO BankAccount (AccountHolderName, Balance)
VALUES ('John Doe', 5000.00),('Jane Smith', 7500.00);

🌟 $\text {BankAccouont.java}$

public class BankAccount {
    private String accountHolderName;
    private double balance;

    // Constructor
    public BankAccount(String accountHolderName, double balance) {
        this.accountHolderName = accountHolderName;
        this.balance = balance;
    }

    public String getAccountHolderName() {
        return accountHolderName;
    }

    public void setAccountHolderName(String accountHolderName) {
        this.accountHolderName = accountHolderName;
    }

    public double getBalance() {
        return balance;
    }

    public void setBalance(double balance) {
        this.balance = balance;
    }
}

$\text {BankAccountDAO.java}$

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class BankAccountDAO {
    private Connection connection;

    public Connection getConnection() {
        return connection;
    }

    public void setConnection(Connection connection) {
        this.connection = connection;
    }

    /**
     * 减少账户余额
     *
     * @param accountNumber
     * @param amount
     * @return
     */
    public boolean decreaseBalance(int accountNumber, double amount) {
        PreparedStatement pstmt = null;
        try {
            String sql = "UPDATE BankAccount SET balance = balance - ? WHERE accountNumber = ?";
            pstmt = connection.prepareStatement(sql);
            pstmt.setDouble(1, amount);
            pstmt.setInt(2, accountNumber);
            int rowsUpdated = pstmt.executeUpdate();
            return rowsUpdated > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        } finally {
            if (pstmt != null) {
                try {
                    pstmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * 增加账户余额
     *
     * @param accountNumber
     * @param amount
     * @return
     */
    public boolean increaseBalance(int accountNumber, double amount) {
        PreparedStatement pstmt = null;
        try {
            String sql = "UPDATE BankAccount SET balance = balance + ? WHERE accountNumber = ?";
            pstmt = connection.prepareStatement(sql);
            pstmt.setDouble(1, amount);
            pstmt.setInt(2, accountNumber);
            int rowsUpdated = pstmt.executeUpdate();
            return rowsUpdated > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        } finally {
            if (pstmt != null) {
                try {
                    pstmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

$\text {DEMO:BankAccountService.java}$

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class BankAccountService {
    private BankAccountDAO bankAccountDAO;
    
    public BankAccountService(BankAccountDAO bankAccountDAO) {
        this.bankAccountDAO = bankAccountDAO;
    }

    /**
     * 转账业务
     * @param fromAccountNumber
     * @param toAccountNumber
     * @param amount
     * @return
     */
    public boolean transferMoney(int fromAccountNumber, int toAccountNumber, double amount) {
        Connection connection = null;
        boolean transferSuccess = false;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/BankDatabase", "root", "root");
            connection.setAutoCommit(false);
            bankAccountDAO.setConnection(connection);
            bankAccountDAO.decreaseBalance(fromAccountNumber, amount);
            bankAccountDAO.increaseBalance(toAccountNumber, amount);
            connection.commit();
            transferSuccess = true;
        } catch (Exception e) {
            if (connection != null) {
                try {
                    connection.rollback();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
            e.printStackTrace();
        } finally {
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return transferSuccess;
    }
}

$\text {BankAccountMain.java}$

public class BankAccountMain {
    public static void main(String[] args) {
        int fromAccountNumber = 1; // 源账户号码
        int toAccountNumber = 2; // 目标账户号码
        double amount = 100.00; // 转账金额

        BankAccountDAO bankAccountDAO = new BankAccountDAO();
        BankAccountService bankAccountService = new BankAccountService(bankAccountDAO);

        boolean isSuccess = bankAccountService.transferMoney(fromAccountNumber, toAccountNumber, amount);

        if (isSuccess) {
            System.out.println("转账成功！");
        } else {
            System.out.println("转账失败！");
        }
    }
}

转账成功！

Druid

$\text {Utils.java}$

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;

public class Utils {
    private static DataSource dataSource = null;

    private static ThreadLocal<Connection> tl = new ThreadLocal<>();

    static {
        Properties properties = new Properties();
        InputStream fis = Utils.class.getClassLoader().getResourceAsStream("druid.properties");

        try {
            properties.load(fis);
        } catch (IOException e) {
            e.printStackTrace();
        }

        try {
            dataSource = DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }

        try {
            fis.close();
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 获取数据池中的数据库连接
     *
     * @return
     */
    public static Connection getConnection() {
        Connection connection = tl.get();
        if (connection == null) {
            try {
                connection = dataSource.getConnection();
                tl.set(connection);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return connection;
    }


    /**
     * 释放数据池中的数据库连接
     */
    public static void freeConnection() {
        Connection connection = tl.get();
        if (connection != null) {
            try {
                connection.setAutoCommit(true); // 设置事务自动提交
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                tl.remove();
            }
        }
    }
}

$\text {BankAccountDAODruid.java}$

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class BankAccountDAODruid {

    /**
     * 减少账户余额
     *
     * @param accountNumber
     * @param amount
     * @return
     */
    public boolean decreaseBalance(int accountNumber, double amount) {
        Connection connection = Utils.getConnection();
        PreparedStatement pstmt = null;
        try {
            String sql = "UPDATE BankAccount SET balance = balance - ? WHERE accountNumber = ?";
            pstmt = connection.prepareStatement(sql);
            pstmt.setDouble(1, amount);
            pstmt.setInt(2, accountNumber);
            int rowsUpdated = pstmt.executeUpdate();
            return rowsUpdated > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        } finally {
            if (pstmt != null) {
                try {
                    pstmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * 增加账户余额
     *
     * @param accountNumber
     * @param amount
     * @return
     */
    public boolean increaseBalance(int accountNumber, double amount) {
        Connection connection = Utils.getConnection();
        PreparedStatement pstmt = null;
        try {
            String sql = "UPDATE BankAccount SET balance = balance + ? WHERE accountNumber = ?";
            pstmt = connection.prepareStatement(sql);
            pstmt.setDouble(1, amount);
            pstmt.setInt(2, accountNumber);
            int rowsUpdated = pstmt.executeUpdate();
            return rowsUpdated > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        } finally {
            if (pstmt != null) {
                try {
                    pstmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

$\text {BankAccountServiceDruid.java}$

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class BankAccountServiceDruid {
    private BankAccountDAODruid bankAccountDAODruid;

    public BankAccountServiceDruid(BankAccountDAODruid bankAccountDAODruid) {
        this.bankAccountDAODruid = bankAccountDAODruid;
    }

    /**
     * 转账业务
     *
     * @param fromAccountNumber
     * @param toAccountNumber
     * @param amount
     * @return
     */
    public boolean transferMoney(int fromAccountNumber, int toAccountNumber, double amount) {
        Connection connection = Utils.getConnection();
        boolean transferSuccess = false;
        try {
            connection.setAutoCommit(false);
            bankAccountDAODruid.decreaseBalance(fromAccountNumber, amount);
            bankAccountDAODruid.increaseBalance(toAccountNumber, amount);
            connection.commit();
            transferSuccess = true;
        } catch (Exception e) {
            if (connection != null) {
                try {
                    connection.rollback();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
            e.printStackTrace();
        } finally {
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return transferSuccess;
    }
}

$\text {BankAccountMainDruid.java}$

public class BankAccountMainDruid {
    public static void main(String[] args) {
        int fromAccountNumber = 1; // 源账户号码
        int toAccountNumber = 2; // 目标账户号码
        double amount = 100.00; // 转账金额

        BankAccountDAODruid bankAccountDAODruid=new BankAccountDAODruid();

        BankAccountServiceDruid bankAccountServiceDruid = new BankAccountServiceDruid(bankAccountDAODruid);

        boolean isSuccess = bankAccountServiceDruid.transferMoney(fromAccountNumber, toAccountNumber, amount);

        if (isSuccess) {
            System.out.println("转账成功！");
        } else {
            System.out.println("转账失败！");
        }
    }
}

BaseDao

$\text {BaseDao}$

import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class BaseDao {
    /**
     * SQL 更新操作，例如插入、更新、删除记录
     * @param sql
     * @param params
     * @return
     * @throws SQLException
     */
    public int executeUpdate(String sql, Object... params) throws SQLException {
        Connection connection = Utils.getConnection();
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        if (params != null && params.length != 0) {
            for (int i = 1; i <= params.length; i++) {
                preparedStatement.setObject(i, params[i - 1]);
            }
        }
        int rows = preparedStatement.executeUpdate();
        preparedStatement.close();
        if (connection.getAutoCommit()) {
            Utils.freeConnection();
        }
        return rows;
    }

    /**
     * SQL 查询操作
     * @param c
     * @param sql
     * @param parameters
     * @return
     * @param <T>
     * @throws SQLException
     * @throws IllegalAccessException
     * @throws InstantiationException
     * @throws NoSuchFieldException
     */
    public <T> List<T> executeQuery(Class<T> c, String sql, Object... parameters) throws SQLException, IllegalAccessException, InstantiationException, NoSuchFieldException {
        Connection connection = Utils.getConnection();
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        if (parameters != null && parameters.length != 0) {
            for (int i = 1; i <= parameters.length; i++) {
                preparedStatement.setObject(i, parameters[i - 1]);
            }
        }
        ResultSet resultSet = preparedStatement.executeQuery();
        List<T> list = new ArrayList<>();
        ResultSetMetaData metaData = resultSet.getMetaData();
        int columnCount = metaData.getColumnCount();
        while (resultSet.next()) {
            T t = c.newInstance();
            for (int i = 1; i <= columnCount; i++) {
                Object value = resultSet.getObject(i);
                String columnLabel = metaData.getColumnLabel(i);
                Field field = c.getDeclaredField(columnLabel);
                field.setAccessible(true);
                field.set(t, value);
            }
            list.add(t);
        }
        resultSet.close();
        preparedStatement.close();
        if (connection.getAutoCommit()) {
            Utils.freeConnection();
        }
        return list;
    }
}

$\text {Insert}$

@Test
public void testInsert() {
    String sql = "INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country) VALUES (?, ?, ?, ?, ?, ?)";
    try {
        int rows = executeUpdate(sql, "Cardinal", "Tom B. Erichsen", "Skagen 21", "Stavanger", "4006", "Norway");
        if (rows > 0) {
            System.out.println("Insert successful. Rows inserted: " + rows);
        } else {
            System.out.println("Insert failed.");
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

Insert successful. Rows inserted: 1

$\text {Update}$

@Test
public void testUpdate() {
    String sql = "UPDATE Customers SET ContactName = ?, City = ? WHERE CustomerID = ?";
    try {
        int rows = executeUpdate(sql, "Alfred Schmidt", "Frankfurt", 92);
        if (rows > 0) {
            System.out.println("Update successful. Rows updated: " + rows);
        } else {
            System.out.println("Update failed.");
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

Update successful. Rows updated: 1

$\text {Delete}$

@Test
public void testDelete() {
    String sql = "DELETE FROM Customers WHERE CustomerName= ?";
    try {
        int rows = executeUpdate(sql, "Cardinal");
        if (rows > 0) {
            System.out.println("Delete successful. Rows deleted: " + rows);
        } else {
            System.out.println("Delete failed.");
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

Delete successful. Rows deleted: 1

$\text {SelectAll}$

@Test
public void testSelect() {
    // SELECT CustomerName, ContactName, Address, City, PostalCode, Country FROM Customers;
    String sql = "SELECT CustomerName AS customerName, ContactName AS contactName, Address AS address, City AS city, PostalCode AS postalCode, Country AS country FROM Customers";
    try {
        List<Customer> customers = executeQuery(Customer.class, sql);
        for (Customer customer : customers) {
            System.out.println(customer);
        }
    } catch (SQLException | IllegalAccessException | InstantiationException | NoSuchFieldException e) {
        e.printStackTrace();
    }
}

Customer{customerName=‘Alfreds Futterkiste’, contactName=‘Maria Anders’, address=‘Obere Str. 57’, city=‘Berlin’, postalCode=‘12209’, country=‘Germany’}
Customer{customerName=‘Ana Trujillo Emparedados y helados’, contactName=‘Ana Trujillo’, address=‘Avda. de la Constitución 2222’, city=‘México D.F.’, postalCode=‘5021’, country=‘Mexico’}
Customer{customerName=‘Antonio Moreno Taquería’, contactName=‘Antonio Moreno’, address=‘Mataderos 2312’, city=‘México D.F.’, postalCode=‘5023’, country=‘Mexico’}
Customer{customerName=‘Around the Horn’, contactName=‘Thomas Hardy’, address=‘120 Hanover Sq.’, city=‘London’, postalCode=‘WA1 1DP’, country=‘UK’}
Customer{customerName=‘Berglunds snabbköp’, contactName=‘Christina Berglund’, address=‘Berguvsvägen 8’, city=‘Luleå’, postalCode=‘S-958 22’, country=‘Sweden’}

…