Java JDBC基础详解

什么是JDBC

JDBC（Java Database Connectivity）是Java访问数据库的标准API，它定义了Java程序与数据库交互的接口。

连接数据库

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

public class DatabaseConnection {
    private static final String URL = "jdbc:mysql://localhost:3306/mydb";
    private static final String USER = "root";
    private static final String PASSWORD = "123456";

    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(URL, USER, PASSWORD);
    }

    public static void main(String[] args) {
        try (Connection conn = getConnection()) {
            System.out.println("数据库连接成功");
            System.out.println("数据库产品名: " + conn.getMetaData().getDatabaseProductName());
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

执行SQL语句

Statement

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

public class StatementDemo {
    public static void main(String[] args) {
        String createSQL = "CREATE TABLE IF NOT EXISTS users (" +
            "id INT PRIMARY KEY AUTO_INCREMENT," +
            "name VARCHAR(50) NOT NULL," +
            "email VARCHAR(100)," +
            "age INT" +
            ")";

        try (Connection conn = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/mydb", "root", "123456");
             Statement stmt = conn.createStatement()) {

            stmt.executeUpdate(createSQL);
            System.out.println("表创建成功");

            String insertSQL = "INSERT INTO users (name, email, age) VALUES " +
                "('张三', 'zhangsan@example.com', 25)," +
                "('李四', 'lisi@example.com', 30)," +
                "('王五', 'wangwu@example.com', 28)";

            int rows = stmt.executeUpdate(insertSQL);
            System.out.println("插入行数: " + rows);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

PreparedStatement

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

public class PreparedStatementDemo {
    public static void main(String[] args) {
        String insertSQL = "INSERT INTO users (name, email, age) VALUES (?, ?, ?)";

        try (Connection conn = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/mydb", "root", "123456");
             PreparedStatement pstmt = conn.prepareStatement(insertSQL)) {

            pstmt.setString(1, "赵六");
            pstmt.setString(2, "zhaoliu@example.com");
            pstmt.setInt(3, 35);
            pstmt.executeUpdate();
            System.out.println("插入成功");

            String selectSQL = "SELECT * FROM users WHERE age > ?";
            pstmt.setInt(1, 25);
            var rs = pstmt.executeQuery();

            while (rs.next()) {
                System.out.printf("ID: %d, 姓名: %s, 年龄: %d%n",
                    rs.getInt("id"), rs.getString("name"), rs.getInt("age"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

处理结果集

import java.sql.*;

public class ResultSetDemo {
    public static void main(String[] args) {
        String sql = "SELECT * FROM users";

        try (Connection conn = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/mydb", "root", "123456");
             Statement stmt = conn.createStatement(
                 ResultSet.TYPE_SCROLL_INSENSITIVE,
                 ResultSet.CONCUR_READ_ONLY);
             ResultSet rs = stmt.executeQuery(sql)) {

            System.out.println("结果集信息:");
            System.out.println("列数: " + rs.getMetaData().getColumnCount());
            System.out.println("第一列名: " + rs.getMetaData().getColumnName(1));

            rs.last();
            System.out.println("最后一行: " + rs.getString("name"));

            rs.first();
            System.out.println("第一行: " + rs.getString("name"));

            rs.beforeFirst();
            while (rs.next()) {
                System.out.printf("用户: %s, 邮箱: %s%n",
                    rs.getString("name"), rs.getString("email"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

事务管理

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

public class TransactionDemo {
    public static void main(String[] args) {
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/mydb", "root", "123456");
            conn.setAutoCommit(false);

            Statement stmt = conn.createStatement();
            stmt.executeUpdate("UPDATE users SET age = age + 1 WHERE id = 1");
            stmt.executeUpdate("UPDATE users SET age = age - 1 WHERE id = 2");

            conn.commit();
            System.out.println("事务提交成功");
        } catch (SQLException e) {
            if (conn != null) {
                try {
                    conn.rollback();
                    System.out.println("事务回滚");
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
            e.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.setAutoCommit(true);
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

批量操作

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

public class BatchDemo {
    public static void main(String[] args) {
        String sql = "INSERT INTO users (name, email, age) VALUES (?, ?, ?)";

        try (Connection conn = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/mydb", "root", "123456");
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            conn.setAutoCommit(false);

            for (int i = 0; i < 100; i++) {
                pstmt.setString(1, "用户" + i);
                pstmt.setString(2, "user" + i + "@example.com");
                pstmt.setInt(3, 20 + (i % 30));
                pstmt.addBatch();

                if (i % 10 == 0) {
                    pstmt.executeBatch();
                }
            }

            pstmt.executeBatch();
            conn.commit();
            System.out.println("批量插入完成");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

JDBC最佳实践

1. 使用PreparedStatement防止SQL注入
2. 使用try-with-resources管理资源
3. 合理使用事务保证数据一致性
4. 批量操作提高性能
5. 使用连接池管理数据库连接

总结

JDBC是Java数据库编程的基础。掌握数据库连接、SQL执行和事务管理，能帮助你构建可靠的数据库应用。