JDBC基础操作动手实践

一、JDBC核心概念

1.1 什么是JDBC

JDBC（Java Database Connectivity）是Java数据库连接的标准接口，它提供了一套统一的API，允许Java程序连接和操作各种关系型数据库。

JDBC三大作用：

统一标准：为所有数据库提供统一的访问方式

屏蔽差异：只需学习一套API，无需关心底层数据库差异

简化开发：通过简单的接口调用完成数据库操作

1.2 JDBC六部曲

步骤1：注册驱动

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

步骤2：获取连接

String url = "jdbc:mysql://localhost:3306/数据库名?参数"
String username = "root"
String password = "123456"

Connection conn = DriverManager.getConnection(url,username,password);

步骤3：创建Statement/PreparedStatement

//创建sql执行对象
Statement stmt = conn.createStatement();

//或使用更安全的PreparedStatement
PreparedStatement pstmt = conn.prepareStatement(sql);

步骤4：执行SQL

//查询
ResultSet rs = stmt.executeQuery(sql);

//更新（增删改）
int rows = stmt.executeUpdate(sql);

步骤5：处理结果

//遍历结果集
while(rs.next()){
    String name = rs.getString("column_name");
    int id = rs.getInt("id");
}

步骤6：关闭资源

//必须按顺序关闭：ResultSet → Statement → Connection
rs.close();
stmt.close();
conn.close();

二、IDEA创建JDBC项目实践

2.1 环境准备

创建Maven项目

添加MySQL驱动依赖

创建数据库和表

2.2 项目结构

src/main/java/com/hospital/
├── entity/
│   └── Department.java
├── dao/
│   └── DepartmentDao.java
├── util/
│   └── JdbcUtil.java
└── MainApp.java

2.3 实体类 - Department.java

package com.hospital.entity;

import lombok.Data;

@Data
public class Department {
    private Integer id;
    private String name;
    private String description;
    private Integer status;

    public Department(){}

    public Department(String name,String description){
        this.name = name;
        this.description = description;
        this.status = 1;
    }

    @Override
    public String toString(){
        return String.format("科室ID: %d, 名称: %s, 描述: %s, 状态: %s",
                id, name, description, status == 1 ? "启用" : "停用");
    }
}

2.4 JDBC 工具类 - JdbcUtil.java

package com.hospital.util;

import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

//jdbc工具类
//负责数据库连接和资源关闭
public class JdbcUtil {

    //数据库连接信息
    private static String url;
    private static String username;
    private static String password;

    //静态代码块：在类加载时执行一次
    static {
        try {
            //1.加载配置文件
            Properties props = new Properties();
            InputStream is = JdbcUtil.class.getClassLoader().getResourceAsStream("jdbc.properties");

            if(is != null){
                props.load(is);
            }else {
                //没有配置文件，使用默认值
                props.setProperty("jdbc.url","jdbc:mysql://localhost:3306/hospital_db?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8&allowPublicKeyRetrieval=true");
                props.setProperty("jdbc.username","root");
                props.setProperty("jdbc.password","123456");
            }

//            2.读取配置
            url = props.getProperty("jdbc.url");
            username = props.getProperty("jdbc.username");
            password = props.getProperty("jdbc.password");

//            3.注册驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            System.out.println("Mysql驱动加载成功");

        } catch (Exception e) {
            throw new RuntimeException("加载数据库配置失败",e);
        }
    }

    //获取数据库连接
    public static Connection getConnection(){
        try {
            return DriverManager.getConnection(url,username,password);
        } catch (SQLException e) {
            throw new RuntimeException("获取数据库连接失败",e);
        }
    }

    //关闭数据库资源
    public static void close(ResultSet rs, Statement stmt,Connection conn){
        try {
            if(rs != null)
                rs.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        try {
            if(stmt != null)
                stmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        try {
            if(conn != null)
                conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void close(Statement stmt,Connection conn){
        close(null,stmt,conn);
    }

    public static void close(Connection conn){
        close(null,null,conn);
    }
}

2.5 配置文件 - jdbc.properties

jdbc.url=jdbc:mysql://localhost:3306/hospital_db?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8&allowPublicKeyRetrieval=true
jdbc.username=root
jdbc.password=123456

2.6 DAO层 - DepartmentDao.java

package com.hospital.dao;

import com.hospital.entity.Department;
import com.hospital.util.JdbcUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

//科室数据访问层，实现CRUD操作
public class DepartmentDao {

    //添加科室
    public boolean insert(Department dept){
        Connection conn = null;
        PreparedStatement pstmt = null;

        try{
            //获取连接
            conn = JdbcUtil.getConnection();

            //准备sql（使用占位符？）
            String sql = "insert into department(name,description,status) values(?,?,?)";

            //创建PreparedStatement对象
            pstmt = conn.prepareStatement(sql);

            //设置参数
            pstmt.setString(1,dept.getName());
            pstmt.setString(2,dept.getDescription());
            pstmt.setInt(3,dept.getStatus());

            //执行sql
            int rows = pstmt.executeUpdate();

            return rows > 0;
        } catch(SQLException e){
            e.printStackTrace();
            return false;
        }finally {
            //关闭资源
            JdbcUtil.close(pstmt,conn);
        }
    }

    //根据id删除科室
    public boolean deleteById(Integer id){
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            //获取连接
            conn = JdbcUtil.getConnection();
            //sql
            String sql = "delete from department where id = ?";
            //创建preparedStatement对象
            pstmt = conn.prepareStatement(sql);
            //设置参数
            pstmt.setInt(1,id);
            //执行SQL
            int rows = pstmt.executeUpdate();

            return rows > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        } finally {
            JdbcUtil.close(pstmt,conn);
        }
    }

    //更新科室信息
    public boolean update(Department dept){
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = JdbcUtil.getConnection();
            String sql = "update department set name = ?,description = ?,status = ? where id = ?";
            pstmt = conn.prepareStatement(sql);

            pstmt.setString(1,dept.getName());
            pstmt.setString(2,dept.getDescription());
            pstmt.setInt(3,dept.getStatus());
            pstmt.setInt(4,dept.getId());

            int rows = pstmt.executeUpdate();
            return rows > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        } finally {
            JdbcUtil.close(pstmt,conn);
        }
    }

    //根据Id查询科室
    public Department findById(Integer id){
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        Department dept = null;

        try {
            conn = JdbcUtil.getConnection();
            String sql = "select id,name,description,status from department where id = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,id);

            rs = pstmt.executeQuery();

            if(rs.next()) {
                dept = new Department();
                dept.setId(rs.getInt("id"));
                dept.setName(rs.getString("name"));
                dept.setDescription(rs.getString("description"));
                dept.setStatus(rs.getInt("status"));
            }
            return dept;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        } finally {
            JdbcUtil.close(rs,pstmt,conn);
        }
    }

    //查询所有科室
    public List<Department> findAll(){
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        List<Department> list = new ArrayList<>();

        try {
            conn = JdbcUtil.getConnection();
            //创建Statement对象，适合固定sql
            stmt = conn.createStatement();
            String sql = "select id,name,description,status from department";

            rs = stmt.executeQuery(sql);
            while(rs.next()){
                Department dept = new Department();
                dept.setId(rs.getInt("id"));
                dept.setName(rs.getString("name"));
                dept.setDescription(rs.getString("description"));
                dept.setStatus(rs.getInt("status"));

                list.add(dept);
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
            return list;
        } finally {
            JdbcUtil.close(rs,stmt,conn);
        }
    }

    //统计科室数量
    public int count(){
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;

        try {
            conn = JdbcUtil.getConnection();
            stmt = conn.createStatement();
            String sql = "select count(*) from department";

            rs = stmt.executeQuery(sql);

            if(rs.next()){
                return rs.getInt(1);
            }
            return 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        } finally {
            JdbcUtil.close(rs,stmt,conn);
        }
    }
}

2.7 主程序 - MainApp.java

package com.hospital;

//主程序 - 演示JDBC操作

import com.hospital.dao.DepartmentDao;
import com.hospital.entity.Department;

import java.util.List;
import java.util.Scanner;

public class MainApp {
    private static final DepartmentDao deptDao = new DepartmentDao();
    private static final Scanner scanner = new Scanner(System.in);

    public static void main(String[] args) {
        System.out.println("========== 医院科室管理系统 ==========");

        boolean isRunning = true;
        while (isRunning){
            System.out.println("\n========== 菜单 ==========");
            System.out.println("1. 添加科室");
            System.out.println("2. 删除科室");
            System.out.println("3. 更新科室");
            System.out.println("4. 根据ID查询");
            System.out.println("5. 查询所有科室");
            System.out.println("6. 分页查询");
            System.out.println("7. 统计科室数量");
            System.out.println("8. 测试数据库连接");
            System.out.println("0. 退出系统");
            System.out.print("请选择操作(0-8): ");
            String choice = scanner.nextLine();

            switch (choice){
                case "1":
                    addDepartment();
                    break;
                case "2":
                    deleteDepartment();
                    break;
                case "3":
                    updateDepartment();
                    break;
                case "4":
                    findById();
                    break;
                case "5":
                    findAll();
                    break;
                case "7":
                    countDepartments();
                    break;
                case "8":
                    testConnection();
                    break;
                case "0":
                    System.out.println("感谢使用，再见！");
                    isRunning = false;
                    break;
                default:
                    System.out.println("无效的选择，请重新输入！");
            }
        }
        scanner.close();
    }

    private static void addDepartment(){
        System.out.println("\n===添加科室===");
        System.out.print("科室名称：");
        String name = scanner.nextLine();
        System.out.print("科室描述：");
        String description = scanner.nextLine();

        Department dept = new Department(name,description);
        if(deptDao.insert(dept)){
            System.out.println("科室添加成功");
        } else {
            System.out.println("科室添加失败");
        }
    }

    private static void deleteDepartment(){
        System.out.println("\n=== 删除科室 ===");
        System.out.print("请输入要删除的科室ID: ");
        try {
            int id = Integer.parseInt(scanner.nextLine());
            System.out.print("确认删除科室id为" + id + "的记录吗？（y/n）:");
            String confirm = scanner.nextLine();

            if(confirm.equalsIgnoreCase("y")){
                boolean success = deptDao.deleteById(id);
                if(success){
                    System.out.println("科室删除成功！");
                } else {
                    System.out.println("科室删除失败，可能ID不存在！");
                }
            } else {
                System.out.println("已取消删除操作。");
            }
        } catch (NumberFormatException e) {
            System.out.println("请输入有效的数字id!");
        }
    }

    private static void updateDepartment(){
        System.out.println("\n=== 更新科室 ===");

        try {
            System.out.print("请输入要更新的科室ID: ");
            int id = Integer.parseInt(scanner.nextLine());

            Department dept = deptDao.findById(id);
            if(dept == null){
                System.out.println("科室不存在");
                return;
            }

            System.out.println("当前信息：" + dept);
            System.out.println("\n请输入新信息（留空表示不修改）");

            System.out.print("科室名称 [" + dept.getName() + "]: ");
            String name = scanner.nextLine();
            if(!name.isEmpty())
                dept.setName(name);

            System.out.print("科室描述 [" + dept.getDescription() + "]: ");
            String description = scanner.nextLine();
            if (!description.isEmpty())
                dept.setDescription(description);

            System.out.print("状态(1-启用, 0-停用) [" + dept.getStatus() + "]: ");
            String status = scanner.nextLine();
            if(!status.isEmpty())
                dept.setStatus(Integer.parseInt(status));

            boolean success = deptDao.update(dept);
            if (success) {
                System.out.println("科室更新成功！");
            } else {
                System.out.println("科室更新失败！");
            }
        } catch (NumberFormatException e) {
            System.out.println("请输入有效的数字！");
        }
    }

    private static void findById(){
        System.out.println("\n=== 根据ID查询 ===");
        System.out.print("请输入科室ID: ");

        try {
            int id = Integer.parseInt(scanner.nextLine());
            Department dept = deptDao.findById(id);

            if (dept != null) {
                System.out.println("查询结果:");
                System.out.println("  " + dept);
            } else {
                System.out.println("未找到ID为 " + id + " 的科室");
            }
        } catch (NumberFormatException e) {
            System.out.println("请输入有效的数字ID！");
        }
    }

    private static void findAll(){
        System.out.println("\n=== 所有科室列表 ===");

        List<Department> depts = deptDao.findAll();

        if(depts.isEmpty()){
            System.out.println("暂无科室数据。");
        }else {
            System.out.println("找到 " + depts.size() + " 个科室:");
            for (Department dept : depts) {
                System.out.println("  " + dept);
            }
        }
    }

    private static void countDepartments(){
        int count = deptDao.count();
        System.out.println("\n科室总数: " + count);
    }

    private static void testConnection(){
        System.out.println("\n=== 测试数据库连接 ===");

        try {
            //使用工具类测试连接
            com.hospital.util.JdbcUtil.getConnection();
            System.out.println("✅ 数据库连接成功！");
        } catch (Exception e){
            System.out.println("❌ 数据库连接失败！");
            e.printStackTrace();
        }
    }
}