从零开始手写JDBC连接数据库的详细指南

<dependency>
    <groupid>mysql</groupid>
    <artifactid>mysql-connector-java</artifactid>
    <version>8.0.33</version> <!-- 使用最新稳定版本 -->
</dependency>

implementation 'mysql:mysql-connector-java:8.0.33'

-- 创建数据库
create database if not exists demo_db character set utf8mb4 collate utf8mb4_unicode_ci;

-- 使用数据库
use demo_db;

-- 创建用户表
create table if not exists users (
    id int auto_increment primary key,
    name varchar(100) not null,
    email varchar(150) unique not null,
    created_at timestamp default current_timestamp
);

-- 插入测试数据
insert into users (name, email) values
('张三', 'zhangsan@example.com'),
('李四', 'lisi@example.com'),
('王五', 'wangwu@example.com');

import java.sql.*;

/**
 * 手写jdbc连接数据库示例
 * 演示连接mysql、查询、插入操作
 */
public class jdbcdemo {

    // 数据库连接信息 - 请根据实际情况修改
    private static final string db_url = "jdbc:mysql://localhost:3306/demo_db?usessl=false&servertimezone=utc&useunicode=true&characterencoding=utf8";
    private static final string user = "your_username"; // 替换为你的mysql用户名
    private static final string password = "your_password"; // 替换为你的mysql密码

    // jdbc驱动类名 (mysql 8.0+)
    private static final string driver_class = "com.mysql.cj.jdbc.driver";

    public static void main(string[] args) {
        connection connection = null;
        statement statement = null;
        resultset resultset = null;
        preparedstatement preparedstatement = null;

        try {
            // ========== 步骤1：加载jdbc驱动 (可选，现代jdbc通常自动加载) ==========
            // 显式加载驱动类，确保驱动被注册到drivermanager
            // 对于jdbc 4.0+ (java 6+)，通常不需要这一步，驱动会自动发现和加载
            // 但显式加载可以确保兼容性或在特定环境下工作
            class.forname(driver_class);
            system.out.println("jdbc驱动加载成功！");

            // ========== 步骤2：建立数据库连接 ==========
            system.out.println("正在连接数据库...");
            connection = drivermanager.getconnection(db_url, user, password);
            system.out.println("数据库连接成功！");

            // ========== 步骤3：创建statement对象 ==========
            statement = connection.createstatement();

            // ========== 步骤4：执行查询sql并处理结果 ==========
            system.out.println("\n--- 查询所有用户信息 ---");
            string selectsql = "select id, name, email, created_at from users";
            resultset = statement.executequery(selectsql); // executequery用于select

            // 处理resultset
            system.out.printf("%-5s %-15s %-25s %-20s%n", "id", "姓名", "邮箱", "创建时间");
            system.out.println("------------------------------------------------------------");
            while (resultset.next()) {
                int id = resultset.getint("id"); // 根据列名获取int值
                string name = resultset.getstring("name"); // 根据列名获取string值
                string email = resultset.getstring("email");
                timestamp createdat = resultset.gettimestamp("created_at");

                // 格式化输出
                system.out.printf("%-5d %-15s %-25s %-20s%n", id, name, email, createdat);
            }

            // ========== 步骤5：使用preparedstatement执行插入操作 (防止sql注入) ==========
            system.out.println("\n--- 准备插入新用户 ---");
            string insertsql = "insert into users (name, email) values (?, ?)";
            preparedstatement = connection.preparestatement(insertsql); // 使用preparedstatement

            // 设置参数值 (索引从1开始)
            preparedstatement.setstring(1, "赵六");
            preparedstatement.setstring(2, "zhaoliu@example.com");

            // 执行插入
            int rowsaffected = preparedstatement.executeupdate(); // executeupdate用于insert, update, delete
            system.out.println("插入操作完成，影响了 " + rowsaffected + " 行。");

            // ========== 步骤6：再次查询，验证插入结果 ==========
            system.out.println("\n--- 再次查询所有用户信息 (验证插入) ---");
            // 重新执行查询
            resultset.close(); // 关闭之前的resultset
            resultset = statement.executequery(selectsql);

            system.out.printf("%-5s %-15s %-25s %-20s%n", "id", "姓名", "邮箱", "创建时间");
            system.out.println("------------------------------------------------------------");
            while (resultset.next()) {
                int id = resultset.getint("id");
                string name = resultset.getstring("name");
                string email = resultset.getstring("email");
                timestamp createdat = resultset.gettimestamp("created_at");
                system.out.printf("%-5d %-15s %-25s %-20s%n", id, name, email, createdat);
            }

        } catch (classnotfoundexception e) {
            system.err.println("jdbc驱动类未找到！请检查驱动jar是否在类路径中。");
            e.printstacktrace();
        } catch (sqlexception e) {
            system.err.println("数据库操作发生错误！");
            e.printstacktrace();
        } finally {
            // ========== 步骤7：关闭资源 (非常重要！) ==========
            // 必须在finally块中关闭，确保即使发生异常也能释放资源
            // 关闭顺序：resultset -> statement/preparedstatement -> connection
            try {
                if (resultset != null) {
                    resultset.close();
                    system.out.println("resultset已关闭。");
                }
            } catch (sqlexception e) {
                system.err.println("关闭resultset时出错！");
                e.printstacktrace();
            }

            try {
                if (statement != null) {
                    statement.close();
                    system.out.println("statement已关闭。");
                }
            } catch (sqlexception e) {
                system.err.println("关闭statement时出错！");
                e.printstacktrace();
            }

            try {
                if (preparedstatement != null) {
                    preparedstatement.close();
                    system.out.println("preparedstatement已关闭。");
                }
            } catch (sqlexception e) {
                system.err.println("关闭preparedstatement时出错！");
                e.printstacktrace();
            }

            try {
                if (connection != null) {
                    connection.close();
                    system.out.println("数据库连接已关闭。");
                }
            } catch (sqlexception e) {
                system.err.println("关闭connection时出错！");
                e.printstacktrace();
            }
        }

        system.out.println("jdbc演示程序结束。");
    }
}