JDBC从零到实战：连接MySQL实现增删改查与事务连接池

接到这个标题的时候我就想起自己刚入行那会儿第一次拿到“Java连接数据库实现增删改查”这个任务心里是真的发怵。那时候我连JDBC和ODBC都分不清更别说PreparedStatement为什么比Statement安全、连接串里的参数到底哪个漏不得。后来在项目里踩了无数次坑回头再看这块内容其实就六个步骤、四个SQL动作、一堆细枝末节的规矩。这篇就把我从零到能写明白的整个路子捋出来从建表到工具类从CRUD到事务和连接池再到那些你在编译器里不一定看得出来的坑一次讲透。零基础能照着做出来有点基础的也能补上几个一直模糊的知识点。1. JDBC整体思路拆解为什么所有Java框架都绕不开它1.1 JDBC到底干了什么事Java要操作数据库靠的是JDBCJava Database Connectivity它是一套接口规范。真正干活的驱动是各数据库厂商实现的比如MySQL的驱动就是mysql-connector-java这个包。你在代码里写的Class.forName、DriverManager.getConnection、PreparedStatement全部是在跟这套接口打交道。JDBC的完整流程拆开看是六个步骤加载驱动让Java知道我要连什么数据库获取连接建立Java程序与数据库之间的会话通道创建语句对象准备要执行的SQL执行SQL增删改用executeUpdate查询用executeQuery处理结果查出来的数据装进ResultSet增删改看受影响行数释放资源连接、语句、结果集都要关闭。这六个步骤就像你去银行办事带着身份证进门是加载驱动取号排队是建立连接填业务单是编写SQL柜员受理是执行语句签收回执是处理结果办完离开是释放资源。哪一步断了整个流程就卡住。1.2 为什么要先学JDBC而不是一上来用框架现在干活基本都用MyBatis、Hibernate、Spring Data JPA这些框架有人就觉得JDBC过时了。但框架再漂亮底层还是JDBC只是把重复代码封装掉了。我见过同事在MyBatis里配不好DataSource报错后一脸懵因为他不知道连接串参数的含义、不知道驱动类名为什么会变。这些知识恰恰是JDBC这一层才能给你的。所以这篇的定位很明确面向零基础入门的人也面向那些会用框架但总感觉根基不稳的人。你只需要一个能跑起来的Java环境、一个MySQL数据库不用引入任何框架先把最原生的JDBC吃透。后面再看框架文档很多原本死记的东西自然就通了。1.3 环境与版本怎么选我建议的本地环境组合JDK 1.8或以上太旧的版本有些语法和驱动行为对不上MySQL 5.7或8.0两个版本在驱动类名和连接串上有一点差异会在后面单独讲Maven作为依赖管理工具不是必须但强烈推荐IDEA或Eclipse都行选一个趁手的就好关键是能看debug。组件推荐版本说明JDK1.8学习期1.8完全够用MySQL5.7 / 8.08.0需要用新驱动类名mysql-connector-java5.1.49配5.7旧库 / 8.0.33配8.0库版本别乱配我用的是MySQL 8.0配8.0.33驱动后面所有示例都按这个组合来讲。如果你还是5.7把驱动类名换成com.mysql.jdbc.Driver就行。2. 数据库准备与工程初始化2.1 建库建表从一条DDL开始先在你本地的MySQL里建一个库和一张用户表。不用太复杂能体现增删改查就行。CREATE DATABASE IF NOT EXISTS db_user_demo DEFAULT CHARACTER SET utf8mb4; USE db_user_demo; CREATE TABLE t_user ( id INT AUTO_INCREMENT PRIMARY KEY COMMENT 主键自增, username VARCHAR(50) NOT NULL UNIQUE COMMENT 用户名唯一, password VARCHAR(100) NOT NULL COMMENT 密码, email VARCHAR(100) DEFAULT NULL COMMENT 邮箱, create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间 ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;有两个细节说一下。字符集选utf8mb4不是utf8因为utf8在MySQL里最多存3字节遇到emoji这种特殊字符会报错utf8mb4是完整的UTF-8编码。存储引擎用InnoDB因为它支持事务和行级锁后面试事务控制的时候还要靠它。2.2 Maven工程与依赖坐标如果你用Maven在pom.xml里加依赖dependency groupIdmysql/groupId artifactIdmysql-connector-java/artifactId version8.0.33/version /dependency不习惯Maven的人也没关系去下载对应版本的jar包放到项目的lib目录下然后在IDE里把它Add as Library。两种方式的本质是一样的让Java在编译和运行时能找到驱动类。区别只是一个交给Maven管理一个自己手动管。2.3 数据库连接配置参数含义必须搞清楚我不建议把URL、用户名、密码直接硬编码在Java类里因为换个环境就要改代码容易误改。用一个配置文件db.properties放在src/main/resources下jdbc.drivercom.mysql.cj.jdbc.Driver jdbc.urljdbc:mysql://localhost:3306/db_user_demo?useUnicodetruecharacterEncodingutf8useSSLfalseserverTimezoneAsia/ShanghaiallowPublicKeyRetrievaltrue jdbc.usernameroot jdbc.password你的密码这段URL是新手最容易抄错的地方每个参数我都解释一下useUnicodetrue和characterEncodingutf8保证中文写入读取不乱码useSSLfalse本地开发不开SSL省去证书的麻烦serverTimezoneAsia/ShanghaiMySQL 8.0对时区要求变严了不设会报服务器时区错误allowPublicKeyRetrievaltrue解决8.0驱动连接时的公钥检索限制问题后面排查章节细说。2.4 编写数据库工具类JDBC的样板代码很多所以第一步就是把获取连接和释放资源封装成一个工具类后面每个操作都复用。public class DBUtils { private static String driver; private static String url; private static String username; private static String password; static { try { InputStream in DBUtils.class.getClassLoader() .getResourceAsStream(db.properties); Properties props new Properties(); props.load(in); driver props.getProperty(jdbc.driver); url props.getProperty(jdbc.url); username props.getProperty(jdbc.username); password props.getProperty(jdbc.password); Class.forName(driver); } catch (Exception e) { throw new ExceptionInInitializerError(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 ignored) {} } if (stmt ! null) { try { stmt.close(); } catch (SQLException ignored) {} } if (conn ! null) { try { conn.close(); } catch (SQLException ignored) {} } } }这里用的是静态代码块类第一次加载的时候就会读取配置并注册驱动整个生命周期里只做一次。资源关闭顺序必须先关ResultSet再关Statement最后关Connection。反过来的话先关了连接语句和结果集可能因为这个连接已经被归还或断开而无法正常关闭还会留下内存泄漏。3. 核心增删改查实战从SQL到代码逐行落地3.1 新增用户第一次接触PreparedStatement先写一个最原始的版本让大家知道为什么不能这么写String sql INSERT INTO t_user(username, password, email) VALUES( username , password , email ); Statement stmt conn.createStatement(); stmt.executeUpdate(sql);字符串拼接SQL最大问题是SQL注入。比如password传一个123 OR 11拼出来的SQL就变成了INSERT INTO t_user(username, password, email) VALUES(admin, 123 OR 11, xx.com)条件恒真这条SQL就能做很多预期外的事。所以实际开发一律用PreparedStatement用?占位符先把SQL骨架发给数据库再把参数单独传进去数据库自己会处理转义和边界。public int insertUser(User user) { String sql INSERT INTO t_user(username, password, email) VALUES(?,?,?); try (Connection conn DBUtils.getConnection(); PreparedStatement ps conn.prepareStatement(sql)) { ps.setString(1, user.getUsername()); ps.setString(2, user.getPassword()); ps.setString(3, user.getEmail()); return ps.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); return 0; } }这里我用的是try-with-resourcesJDK 1.7以后不用手动close的写法代码简洁很多而且即使发生异常也会自动关资源。executeUpdate返回的是受影响的行数插入成功一般是1。3.2 删除用户单条删除加批量删除单条删除逻辑最简单public int deleteById(int id) { String sql DELETE FROM t_user WHERE id?; try (Connection conn DBUtils.getConnection(); PreparedStatement ps conn.prepareStatement(sql)) { ps.setInt(1, id); return ps.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); return 0; } }返回0说明没有这条记录调用方可以根据这个值判断删除是否真的生效。批量删除有两种姿势。一种是用循环调单条删除方法好处是代码简单坏处是每删一条都要走一次网络往返数据量大时性能差。另一种是用addBatch和executeBatchpublic int batchDelete(ListInteger ids) { String sql DELETE FROM t_user WHERE id?; try (Connection conn DBUtils.getConnection(); PreparedStatement ps conn.prepareStatement(sql)) { for (Integer id : ids) { ps.setInt(1, id); ps.addBatch(); } int[] rows ps.executeBatch(); // rows数组里每项对应一条SQL的执行结果通常大于等于0表示成功 return rows.length; } catch (SQLException e) { e.printStackTrace(); return 0; } }真正大批量执行时把多条SQL攒在一起发给数据库能省掉大量的交互时间。我第一次用executeBatch是在一个数据导入需求里几千条记录一条一条插要几分钟改成批处理直接降到十几秒。3.3 修改用户动态SQL拼接的坑修改用户有个经典场景前端可能只传了某几个字段不是每次都有完整数据。比如只改邮箱不要因为username和password没传就把它俩更新成空字符串。所以SQL要按条件拼接。public int updateUser(User user) { StringBuilder sql new StringBuilder(UPDATE t_user SET ); ListObject params new ArrayList(); if (user.getUsername() ! null !user.getUsername().isEmpty()) { sql.append(username?, ); params.add(user.getUsername()); } if (user.getPassword() ! null !user.getPassword().isEmpty()) { sql.append(password?, ); params.add(user.getPassword()); } if (user.getEmail() ! null !user.getEmail().isEmpty()) { sql.append(email?, ); params.add(user.getEmail()); } // 如果所有字段都为空直接返回或者抛异常 if (params.isEmpty()) { return 0; } // 去掉最后一个逗号和空格 sql.setLength(sql.length() - 2); sql.append( WHERE id?); try (Connection conn DBUtils.getConnection(); PreparedStatement ps conn.prepareStatement(sql.toString())) { for (int i 0; i params.size(); i) { ps.setObject(i 1, params.get(i)); } ps.setInt(params.size() 1, user.getId()); return ps.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); return 0; } }这地方我栽过一次一开始忘了用setLength把尾巴的逗号去掉拼出来的SQL是UPDATE t_user SET username?, password?,直接语法报错。另外参数索引要认真数前面有几个实体参数ID就排在后面几位这个是动态SQL最容易对不齐的地方。我把所有参数先放List里再统一用setObject填充索引错乱的问题少多了。3.4 查询用户单条、列表、模糊搜索先看按ID查单条public User getUserById(int id) { String sql SELECT id, username, password, email, create_time FROM t_user WHERE id?; try (Connection conn DBUtils.getConnection(); PreparedStatement ps conn.prepareStatement(sql)) { ps.setInt(1, id); try (ResultSet rs ps.executeQuery()) { if (rs.next()) { return mapRowToUser(rs); } } } catch (SQLException e) { e.printStackTrace(); } return null; }查列表就是把while循环走完把所有行封装成对象放进集合public ListUser listAll() { String sql SELECT id, username, password, email, create_time FROM t_user; ListUser list new ArrayList(); try (Connection conn DBUtils.getConnection(); PreparedStatement ps conn.prepareStatement(sql); ResultSet rs ps.executeQuery()) { while (rs.next()) { list.add(mapRowToUser(rs)); } } catch (SQLException e) { e.printStackTrace(); } return list; }封装成一个映射方法避免每个查询里都get一遍private User mapRowToUser(ResultSet rs) throws SQLException { User u new User(); u.setId(rs.getInt(id)); u.setUsername(rs.getString(username)); u.setPassword(rs.getString(password)); u.setEmail(rs.getString(email)); u.setCreateTime(rs.getTimestamp(create_time)); return u; }模糊搜索和前面稍微不一样参数本身要加上%public ListUser searchByKeyword(String keyword) { String sql SELECT id, username, password, email, create_time FROM t_user WHERE username LIKE ? OR email LIKE ?; ListUser list new ArrayList(); try (Connection conn DBUtils.getConnection(); PreparedStatement ps conn.prepareStatement(sql)) { String like % keyword %; ps.setString(1, like); ps.setString(2, like); try (ResultSet rs ps.executeQuery()) { while (rs.next()) { list.add(mapRowToUser(rs)); } } } catch (SQLException e) { e.printStackTrace(); } return list; }这里有个很多新手会犯的错误用Statement加LIKE % keyword %来拼一是不安全二是keyword里带单引号就崩了。PreparedStatement帮你在传参的时候处理掉了这些边界问题。3.5 实体类User怎么写public class User { private Integer id; private String username; private String password; private String email; private LocalDateTime createTime; // getter/setter省略toString建议加上方便打印调试 }字段和表字段一一对应Java里的驼峰createTime对应数据库里的下划线create_time。如果你自己写映射记住这个对应规律如果以后用MyBatis它的map-underscore-to-camel-case会自动帮你在配置里做转换但要清楚现在这一层里是你手动处理的。4. 进阶必看事务、连接池与分层设计4.1 事务控制转账失败这个经典场景CRUD单条都成功了但合在一起可能出事故。最常见例子是转账A扣100B加100。如果第一步执行成功第二步因为网络中断失败那钱就凭空消失了。解决办法就是事务。Java里控制事务三步conn.setAutoCommit(false); // 关闭自动提交 try { // 执行多条update conn.commit(); } catch (Exception e) { conn.rollback(); } finally { conn.setAutoCommit(true); // 恢复自动提交方便连接复用 // 关闭资源 }关键点在于一个事务里的所有SQL必须使用同一个Connection对象不能每条SQL都调一次DBUtils.getConnection()。因为每条连接默认是独立会话各自自动提交事务边界根本管不住。所以写事务的时候通常是把conn作为参数往DAO方法里传或者直接用ThreadLocal把连接绑到当前线程这是很多框架底层干的事情。4.2 连接池生产环境为什么要换掉DriverManager每次getConnection都意味着一次TCP握手、认证、建立会话用完再断开。如果业务量稍微大一点频繁创建连接的成本会拖垮应用。这时候就需要连接池提前创建一批连接放着用完不销毁由池子统一管理和分配。连接池有很多选择老牌的Druid、HikariCP、C3P0。Spring Boot 2.x默认是HikariCP因为它快、轻量。示例如下HikariConfig config new HikariConfig(); config.setJdbcUrl(jdbc:mysql://localhost:3306/db_user_demo?useUnicodetruecharacterEncodingutf8useSSLfalseserverTimezoneAsia/Shanghai); config.setUsername(root); config.setPassword(你的密码); config.setMaximumPoolSize(10); config.setMinimumIdle(2); HikariDataSource dataSource new HikariDataSource(config); Connection conn dataSource.getConnection();为什么学JDBC阶段还要手写DriverManager因为你要通过手写知道连接是怎么来的、资源为什么要还、URL里哪些参数是干什么的。理解了这些用连接池不过是换个获取连接的方式心智负担小很多。如果直接跳框架遇到连接超时、连接泄漏问题你根本无从下手。我现在面试新人问连接池参数含义能答清楚的寥寥无几就是因为他们只用了框架的一行配置。4.3 代码分层别把SQL裸写在入口方法里小demo可以一个类写完但后续需求一多就难受了。合理的做法是拆成三层entity实体类对应表结构dao数据访问层只做SQL操作service业务逻辑层处理校验、事务、组合多个DAO操作入口main方法或后续的Controller只负责调用service。给你一个简化的service示例public class UserService { private UserDao userDao new UserDao(); public boolean register(User user) { // 业务校验用户名不能重复 if (userDao.getByUsername(user.getUsername()) ! null) { return false; } // 密码加密这类处理放这里不放DAO里 return userDao.insertUser(user) 0; } }这样拆分的好处是改SQL去DAO里看改业务规则只在Service里动入口处不会堆一堆SQL。我见过有些项目把几十行JDBC代码写在main方法里后来要加个业务字段改起来像在雷区里走路。5. 常见问题与排查技巧实录这一节是真的能帮你省时间的都是我实际遇到或者帮同事排查过的问题。报错现象根因解决办法ClassNotFoundException: com.mysql.cj.jdbc.Driver缺驱动jar包或类名写错确认pom里有依赖确认类名是cj后缀还是老版DriverClassNotFoundException: com.mysql.jdbc.Driver用的是MySQL 8驱动但类名写成老版8.0驱动类名为com.mysql.cj.jdbc.Driver或者干脆删掉Class.forName靠SPI自动注册报错serverTimezoneMySQL 8.0对时区敏感连接串加serverTimezoneAsia/ShanghaiAccess denied for user rootlocalhost用户名或密码错或者没远程访问权限检查properties里的用户名密码确认数据库账号有权限Communications link failure数据库没启动、端口不对、网络不通先ping一下端口mysql -u root -p能否本地连上中文插入后变成问号连接串没有characterEncodingutf8或库表字符集不对连接串、数据库、表、列四层的字符集统一utf8mb4Public Key Retrieval is not allowedMySQL 8密码插件为caching_sha2_password连接串加allowPublicKeyRetrievaltrueYou have an error in your SQL syntax动态SQL拼接多了逗号、少了空格先打印SQL在Navicat里验证确认无误再放回代码ResultSet closed关闭连接后又取ResultSet数据遍历完ResultSet再关连接或把数据先取出放到对象里5.1 三个踩中的坑和对应经验第一个坑是版本匹配。我刚开始学的时候下载了一个老版驱动连MySQL 8直接报认证插件不兼容。记住驱动版本尽量和数据库大版本匹配别拿5.x驱动连8.x库。第二个坑是动态SQL的调试。拼SQL这种东西肉眼很难看出来哪里多了空格或逗号。我的习惯是在执行前打印最终SQLSystem.out.println(Final SQL: sql.toString()); System.out.println(Params: params);然后把SQL原文粘到Navicat里跑一遍。SQL能跑通问题就在参数传递SQL跑不通毫无疑问是拼接逻辑有错。第三个坑是连接资源泄漏。忘了关闭Connection测试环境跑一个下午就报连接数耗尽。用try-with-resources能杜绝一半问题另一半在于工具类的close方法要写得健壮判空加上保证异常时也能关。5.2 一个调试顺序的总结我的调试顺序永远是先确认SQL本身有没有问题再怀疑JDBC代码。在数据库客户端里执行一遍同样的SQL确认结果正常再回来看代码里的占位符、参数类型、索引。很多新手一报错就开debug结果看了半天发现是SQL少了一个空格。另外printStackTrace只是方便控制台看正式项目请换成日志框架否则问题只能在你本地复现时才能定位。一点个人体会留在这篇的结尾写这篇的时候我回想自己第一次独立完成用户表CRUD的那个下午。代码写了两百多行把六个步骤背得滚瓜烂熟但真正理解还是在被各种报错折磨之后。如果你现在也正在为某个驱动包、某个时区参数、某个动态SQL头疼别急这些东西踩过一次就成肌肉记忆了。JDBC是整个Java数据访问体系的地基扎实学一遍后面学MyBatis、Spring Data都会顺畅很多。最后建议你把文中每个例子都亲手敲一遍尤其动态更新和批处理只看不练等于白看代码这种东西手上有感觉了才算是自己的。