java如何使用JDBC执行SQL语句 java数据库交互的实用操作指南

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.ResultSet; // For queries, though not used in insert example

public class JdbcExample {

    // 注意：实际项目中，这些配置应从配置文件读取，避免硬编码
    private static final String DB_URL = "jdbc:mysql://localhost:3306/mydatabase?useSSL=false&serverTimezone=UTC";
    private static final String USER = "root";
    private static final String PASS = "your_password"; // 替换为你的数据库密码

    public static void main(String[] args) {
        // try-with-resources 确保资源自动关闭
        try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASS)) {
            System.out.println("数据库连接成功！");

            // --- 插入数据示例 ---
            String insertSql = "INSERT INTO users (name, email) VALUES (?, ?)";
            try (PreparedStatement pstmt = conn.prepareStatement(insertSql)) {
                // 设置参数
                pstmt.setString(1, "张三");
                pstmt.setString(2, "zhangsan@example.com");

                // 执行SQL
                int rowsAffected = pstmt.executeUpdate();
                System.out.println("插入了 " + rowsAffected + " 条记录。");
            } // pstmt 会在此处自动关闭

            // --- 查询数据示例 ---
            String selectSql = "SELECT id, name, email FROM users WHERE name = ?";
            try (PreparedStatement selectPstmt = conn.prepareStatement(selectSql)) {
                selectPstmt.setString(1, "张三");
                try (ResultSet rs = selectPstmt.executeQuery()) { // ResultSet 也会自动关闭
                    while (rs.next()) {
                        System.out.println("ID: " + rs.getInt("id") + ", Name: " + rs.getString("name") + ", Email: " + rs.getString("email"));
                    }
                }
            } // selectPstmt 会在此处自动关闭

        } catch (SQLException e) {
            System.err.println("数据库操作失败: " + e.getMessage());
            // 在生产环境中，应该记录更详细的日志，而不是直接打印堆栈
            // e.printStackTrace(); 
        }
    }
}

PreparedStatement

try-with-resources

ClassNotFoundException

?useSSL=false&serverTimezone=UTC

SQLException

DriverManager.getConnection()

try-with-resources

try

AutoCloseable

// 使用 try-with-resources 自动关闭资源
try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
     PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM users WHERE id = ?")) {

    pstmt.setInt(1, 1); // 设置参数
    try (ResultSet rs = pstmt.executeQuery()) { // ResultSet 也会在 try 块结束时自动关闭
        while (rs.next()) {
            // 处理结果
            System.out.println("User ID: " + rs.getInt("id") + ", Name: " + rs.getString("name"));
        }
    } // rs 会在此处自动关闭

} catch (SQLException e) {
    System.err.println("数据库操作失败: " + e.getMessage());
    e.printStackTrace();
}

try-with-resources

finally

close()

null

Statement

PreparedStatement

Statement

Statement stmt = conn.createStatement(); stmt.executeUpdate("INSERT INTO users (name, email) VALUES ('张三', 'zhangsan@example.com')");

"SELECT * FROM users WHERE name = '" + userName + "'"

userName

' OR '1'='1

SELECT * FROM users WHERE name = '' OR '1'='1'

PreparedStatement

?

setXxx()

PreparedStatement pstmt = conn.prepareStatement("INSERT INTO users (name, email) VALUES (?, ?)"); pstmt.setString(1, "张三"); pstmt.setString(2, "zhangsan@example.com");

CREATE TABLE

PreparedStatement