Java JDBC查询案例如何编写

本文目录导读：

1. 基本JDBC查询示例
2. JDBC工具类封装
3. 使用JDBC工具类的查询示例
4. Maven依赖配置
5. 关键点说明

我来为你提供一个完整的Java JDBC查询案例，包含详细说明。

基本JDBC查询示例

准备工作：创建数据库表

CREATE TABLE user (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100),
    age INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO user (username, email, age) VALUES 
('张三', 'zhangsan@example.com', 25),
('李四', 'lisi@example.com', 30),
('王五', 'wangwu@example.com', 28);

完整JDBC查询代码

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class JdbcQueryExample {
    // 数据库连接信息
    private static final String URL = "jdbc:mysql://localhost:3306/test?useSSL=false&serverTimezone=UTC";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "123456";
    public static void main(String[] args) {
        // 1. 简单查询示例
        simpleQuery();
        // 2. 条件查询示例
        queryByCondition(25, 30);
        // 3. 返回List集合
        List<User> users = queryAllUsers();
        users.forEach(System.out::println);
    }
    /**
     * 1. 简单的查询所有用户
     */
    public static void simpleQuery() {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            // 1. 加载驱动（MySQL 8.0+ 可以省略这一步）
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 2. 获取连接
            conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
            // 3. 创建Statement对象
            stmt = conn.createStatement();
            // 4. 执行SQL查询
            String sql = "SELECT * FROM user";
            rs = stmt.executeQuery(sql);
            // 5. 处理结果集
            System.out.println("=== 所有用户信息 ===");
            while (rs.next()) {
                int id = rs.getInt("id");
                String username = rs.getString("username");
                String email = rs.getString("email");
                int age = rs.getInt("age");
                Timestamp createdAt = rs.getTimestamp("created_at");
                System.out.printf("ID: %d, 用户名: %s, 邮箱: %s, 年龄: %d, 创建时间: %s%n", 
                    id, username, email, age, createdAt);
            }
        } catch (ClassNotFoundException e) {
            System.err.println("数据库驱动加载失败: " + e.getMessage());
        } catch (SQLException e) {
            System.err.println("数据库操作失败: " + e.getMessage());
        } finally {
            // 6. 关闭资源
            closeResources(rs, stmt, conn);
        }
    }
    /**
     * 2. 带条件的查询（使用PreparedStatement防止SQL注入）
     */
    public static void queryByCondition(int minAge, int maxAge) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
            // 使用?作为占位符
            String sql = "SELECT * FROM user WHERE age BETWEEN ? AND ?";
            pstmt = conn.prepareStatement(sql);
            // 设置参数
            pstmt.setInt(1, minAge);
            pstmt.setInt(2, maxAge);
            // 执行查询
            rs = pstmt.executeQuery();
            System.out.println("\n=== 年龄在" + minAge + "到" + maxAge + "之间的用户 ===");
            while (rs.next()) {
                System.out.printf("用户名: %s, 年龄: %d, 邮箱: %s%n",
                    rs.getString("username"),
                    rs.getInt("age"),
                    rs.getString("email"));
            }
        } catch (SQLException e) {
            System.err.println("条件查询失败: " + e.getMessage());
        } finally {
            closeResources(rs, pstmt, conn);
        }
    }
    /**
     * 3. 查询结果封装为对象列表
     */
    public static List<User> queryAllUsers() {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<User> userList = new ArrayList<>();
        try {
            conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
            String sql = "SELECT id, username, email, age FROM user";
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            // 将结果转换为Java对象
            while (rs.next()) {
                User user = new User();
                user.setId(rs.getInt("id"));
                user.setUsername(rs.getString("username"));
                user.setEmail(rs.getString("email"));
                user.setAge(rs.getInt("age"));
                userList.add(user);
            }
            System.out.println("\n=== 查询结果List集合 ===");
        } catch (SQLException e) {
            System.err.println("查询用户列表失败: " + e.getMessage());
        } finally {
            closeResources(rs, pstmt, conn);
        }
        return userList;
    }
    /**
     * 关闭数据库资源
     */
    private static void closeResources(ResultSet rs, Statement stmt, Connection conn) {
        try {
            if (rs != null) rs.close();
            if (stmt != null) stmt.close();
            if (conn != null) conn.close();
        } catch (SQLException e) {
            System.err.println("关闭资源失败: " + e.getMessage());
        }
    }
}
/**
 * 用户实体类
 */
class User {
    private int id;
    private String username;
    private String email;
    private int age;
    // getter和setter方法
    public int getId() { return id; }
    public void setId(int id) { this.id = id; }
    public String getUsername() { return username; }
    public void setUsername(String username) { this.username = username; }
    public String getEmail() { return email; }
    public void setEmail(String email) { this.email = email; }
    public int getAge() { return age; }
    public void setAge(int age) { this.age = age; }
    @Override
    public String toString() {
        return String.format("User{id=%d, username='%s', email='%s', age=%d}", 
            id, username, email, age);
    }
}

JDBC工具类封装

import java.sql.*;
import java.util.ResourceBundle;
/**
 * JDBC工具类
 */
public class JdbcUtils {
    private static String url;
    private static String username;
    private static String password;
    private static String driver;
    // 静态代码块，加载一次配置
    static {
        try {
            // 从配置文件中读取（jdbc.properties）
            ResourceBundle bundle = ResourceBundle.getBundle("jdbc");
            driver = bundle.getString("driver");
            url = bundle.getString("url");
            username = bundle.getString("username");
            password = bundle.getString("password");
            // 加载驱动
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            throw new RuntimeException("数据库驱动加载失败", e);
        }
    }
    /**
     * 获取数据库连接
     */
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url, username, password);
    }
    /**
     * 释放资源
     */
    public static void close(Connection conn, Statement stmt, ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

jdbc.properties配置文件：

driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/test?useSSL=false&serverTimezone=UTC
username=root
password=123456

使用JDBC工具类的查询示例

public class JdbcQueryWithUtils {
    /**
     * 使用工具类查询
     */
    public static List<User> queryUsersByAge(int minAge) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<User> users = new ArrayList<>();
        try {
            // 获取连接
            conn = JdbcUtils.getConnection();
            // SQL语句
            String sql = "SELECT * FROM user WHERE age > ? ORDER BY age";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, minAge);
            // 执行查询
            rs = pstmt.executeQuery();
            // 处理结果
            while (rs.next()) {
                User user = new User();
                user.setId(rs.getInt("id"));
                user.setUsername(rs.getString("username"));
                user.setEmail(rs.getString("email"));
                user.setAge(rs.getInt("age"));
                users.add(user);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 关闭资源（不需要手动处理异常）
            JdbcUtils.close(conn, pstmt, rs);
        }
        return users;
    }
    public static void main(String[] args) {
        List<User> users = queryUsersByAge(25);
        users.forEach(System.out::println);
    }
}

Maven依赖配置

<dependencies>
    <!-- MySQL驱动 -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.33</version>
    </dependency>
</dependencies>

关键点说明

1. 使用PreparedStatement：防止SQL注入
2. 资源关闭：确保在finally块中关闭连接
3. 异常处理：捕获并处理SQLException
4. 对象封装：将数据库记录转换为Java对象
5. 使用try-with-resources（Java 7+）：

// 更简洁的方式（Java 7+）
try (Connection conn = JdbcUtils.getConnection();
     PreparedStatement pstmt = conn.prepareStatement(sql)) {
    // 执行操作
}

这个案例涵盖了JDBC查询的核心知识点,包括连接管理、查询执行、结果处理和资源释放。