Java JDBC基础：数据库连接与操作

概述

JDBC（Java Database Connectivity）是Java中用于连接和操作数据库的标准API。本教程介绍JDBC的基础知识和使用方法。

1. 数据库连接

import java.sql.*;

public class JDBCConnection {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/mydb";
        String username = "root";
        String password = "password";
        
        try {
            // 加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            
            // 建立连接
            Connection connection = DriverManager.getConnection(url, username, password);
            System.out.println("连接成功: " + connection);
            
            // 关闭连接
            connection.close();
            
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
    }
}

2. CRUD操作

查询

import java.sql.*;

public class JDBCQuery {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/mydb";
        
        try (Connection connection = DriverManager.getConnection(url, "root", "password");
             Statement statement = connection.createStatement();
             ResultSet resultSet = statement.executeQuery("SELECT * FROM users")) {
            
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                int age = resultSet.getInt("age");
                
                System.out.println("ID: " + id + ", 姓名: " + name + ", 年龄: " + age);
            }
            
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

插入

import java.sql.*;

public class JDBCInsert {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/mydb";
        
        try (Connection connection = DriverManager.getConnection(url, "root", "password");
             PreparedStatement preparedStatement = connection.prepareStatement(
                "INSERT INTO users (name, age) VALUES (?, ?)")) {
            
            preparedStatement.setString(1, "Alice");
            preparedStatement.setInt(2, 25);
            
            int rowsAffected = preparedStatement.executeUpdate();
            System.out.println("插入行数: " + rowsAffected);
            
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

更新和删除

import java.sql.*;

public class JDBCUpdateDelete {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/mydb";
        
        try (Connection connection = DriverManager.getConnection(url, "root", "password")) {
            
            // 更新
            try (PreparedStatement updateStmt = connection.prepareStatement(
                    "UPDATE users SET age = ? WHERE name = ?")) {
                updateStmt.setInt(1, 26);
                updateStmt.setString(2, "Alice");
                
                int rowsAffected = updateStmt.executeUpdate();
                System.out.println("更新行数: " + rowsAffected);
            }
            
            // 删除
            try (PreparedStatement deleteStmt = connection.prepareStatement(
                    "DELETE FROM users WHERE name = ?")) {
                deleteStmt.setString(1, "Alice");
                
                int rowsAffected = deleteStmt.executeUpdate();
                System.out.println("删除行数: " + rowsAffected);
            }
            
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

3. 事务管理

import java.sql.*;

public class JDBCTransaction {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/mydb";
        
        try (Connection connection = DriverManager.getConnection(url, "root", "password")) {
            
            // 禁用自动提交
            connection.setAutoCommit(false);
            
            try {
                // 执行多个操作
                try (PreparedStatement stmt1 = connection.prepareStatement(
                        "INSERT INTO users (name, age) VALUES (?, ?)")) {
                    stmt1.setString(1, "Bob");
                    stmt1.setInt(2, 30);
                    stmt1.executeUpdate();
                }
                
                try (PreparedStatement stmt2 = connection.prepareStatement(
                        "UPDATE accounts SET balance = balance - ? WHERE user_id = ?")) {
                    stmt2.setDouble(1, 1000);
                    stmt2.setInt(2, 1);
                    stmt2.executeUpdate();
                }
                
                // 提交事务
                connection.commit();
                System.out.println("事务提交成功");
                
            } catch (SQLException e) {
                // 回滚事务
                connection.rollback();
                System.out.println("事务回滚");
                e.printStackTrace();
            }
            
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

4. 连接池

import java.sql.*;
import javax.sql.DataSource;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

public class ConnectionPool {
    private static HikariDataSource dataSource;
    
    static {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl("jdbc:mysql://localhost:3306/mydb");
        config.setUsername("root");
        config.setPassword("password");
        config.setMaximumPoolSize(10);
        
        dataSource = new HikariDataSource(config);
    }
    
    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }
    
    public static void main(String[] args) {
        try (Connection connection = getConnection();
             Statement statement = connection.createStatement();
             ResultSet resultSet = statement.executeQuery("SELECT COUNT(*) FROM users")) {
            
            if (resultSet.next()) {
                System.out.println("用户数量: " + resultSet.getInt(1));
            }
            
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

5. 最佳实践

1. 使用PreparedStatement：防止SQL注入
2. 使用try-with-resources：自动关闭资源
3. 使用连接池：提高性能
4. 处理异常：妥善处理SQLException
5. 使用事务：保证数据一致性

总结

JDBC是Java中连接和操作数据库的标准API。掌握JDBC的使用方法，可以实现各种数据库操作。在实际编程中，要使用PreparedStatement防止SQL注入，使用连接池提高性能。