JDBC的概述

JDBC的全称是：Java DataBase Connectivity（Java数据库连接）
是一种用于执行sql语句的Java API，可以为多种关系型数据库提供统一的访问。

没有JDBC之前，Java操作数据库需要通过不同的驱动。
有了JDBC之后，Java操作数据库只需通过统一的JDBC标准即可。
image.png

使用JDBC前的准备工作：
第一步：右击项目名称，New/Directory/lib
第二步：在lib文件中存放mysql的连接文件（下载地址如下）

https://mvnrepository.com/artifact/mysql/mysql-connector-java/8.0.11

第三步：关联mysql的连接文件到项目中
在IntelliJ IDEA 中选择File->Project Structure->Modules->Dependencies 点击下面的小加号，选择JARs or directories选择你要添加的外部jar包。

JDBC的API

模板案例

  1. package com.song.jdbc;
  3. import org.junit.Test;
  5. import java.sql.*;
  7. public class Demo1 {
  8.     @Test
  9.     public void test() {
  10.         try {
  11.             // 1. 注册驱动
  13.             // DriverManager.registerDriver(new Driver());
  14.             // 结果：不建议使用
  15.             // 理由：因为在加载Driver类的时候就会注册驱动，如果再手动注册一遍，会造成重复注册
  17.             Class.forName("com.mysql.cj.jdbc.Driver");
  18.             // 结果：建议使用
  19.             // 理由：意思是根据类名加载类的实例对象，不会造成重复注册
  21.             // 2. 获得连接
  22.             Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbctest", "root", "???");
  23.             // url说明：jdbc(协议) mysql(子协议) localhost(主机名) 3306(端口号)
  24.             // url简写：jdbc:mysql///jdbc  <= 仅限于本地sql
  26.             // 3.1 创建执行sql的对象
  27.             Statement statement = connection.createStatement();
  28.             // 3.2 执行sql
  29.             String sql = "select * from user";
  30.             ResultSet resultSet = statement.executeQuery(sql);
  31.             while (resultSet.next()) {
  32.                 int uid = resultSet.getInt("uid");
  33.                 String username = resultSet.getString("username");
  34.                 String password = resultSet.getString("password");
  35.                 String name = resultSet.getString("name");
  37.                 System.out.println(uid + " " + username + " " + password + " " + name);
  38.             }
  40.             // 释放资源
  41.             resultSet.close();
  42.             statement.close();
  43.             connection.close();
  44.         } catch (Exception e) {
  45.             e.printStackTrace();
  46.         }
  47.     }
  48. }

DriverManager

驱动管理类

作用一：注册驱动
实际开发中注册驱动会使用如下方式，因为之前的方式会导致驱动注册两次

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

作用二：获得连接
url说明：jdbc(协议) mysql(子协议) localhost(主机名) 3306(端口号)
url简写：jdbc:mysql///jdbc <= 仅限于本地sql

  1. Connection connection = DriverManager.getConnection(String url, String username, String password)

Connection

连接对象类

作用一：创建执行sql语句的对象
Statement createStatement() : 执行sql语句，有sql注入的漏洞存在
PreparedStatement preparedStatement(String sql) : 预编译sql语句，解决sql注入的漏洞
callableStatement prepareCall(String sql) : 执行sql存储过程

作用二：进行事务的管理
setAutoCommit(boolean autoCommit) : 设置事务是否自动提交
commit() : 事务提交
rollback() : 事务回滚

Statement

sql执行类

作用一：执行sql语句
boolean execute(String sql) : 执行sql，执行select返回true，否则返回false
ResultSet executeQuery(String sql) : 执行sql中的select语句
int executeUpdate(String sql) : 执行sql中的insert/update/delete语句

作用二：执行批处理操作
addBatch(String sql) : 添加到批处理
executeBatch() : 执行批处理
clearBatch() : 清空批处理

ResultSet

结果集类，其实就是查询语句(select)的执行结果的封装

主要作用：获取查询结果
next() : 向前移动一行光标，并判断是否有数据
getXXX() : 针对不同类型获取数据

资源释放

JDBC程序运行完后，切记要释放程序在运行过程中，创建的那些与数据库进行交互的对象，这些对象通常是ResultSet，Statement和Connection对象。

特别是Connection对象，它是非常稀有的资源，用完后必须马上释放，如果Connection不能及时，正确的关闭，极易导致系统宕机。Connection的使用原则是尽量晚创建，早释放。

MySQL5中文参考手册.chm/26.连接器/26.3 之 finally部分

  1. package com.song.jdbc;
  3. import org.junit.Test;
  5. import java.sql.*;
  7. public class Demo1 {
  8.     @Test
  9.     public void test() {
  10.         ResultSet resultSet = null;
  11.         Statement statement = null;
  12.         Connection connection = null;
  14.         try {
  15.             // 1. 注册驱动
  16.             Class.forName("com.mysql.cj.jdbc.Driver");
  18.             // 2. 获得连接
  19.             connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbctest", "root", "???");
  21.             // 3.1 创建执行sql的对象
  22.             statement = connection.createStatement();
  23.             // 3.2 执行sql
  24.             String sql = "select * from user";
  25.             resultSet = statement.executeQuery(sql);
  26.             while (resultSet.next()) {
  27.                 int uid = resultSet.getInt("uid");
  28.                 String username = resultSet.getString("username");
  29.                 String password = resultSet.getString("password");
  30.                 String name = resultSet.getString("name");
  32.                 System.out.println(uid + " " + username + " " + password + " " + name);
  33.             }
  34.         } catch (Exception e) {
  35.             e.printStackTrace();
  36.         } finally {
  37.             // 4. 释放资源
  38.             if (resultSet != null) {
  39.                 try {
  40.                     resultSet.close();
  41.                 } catch (SQLException e) {
  42.                     e.printStackTrace();
  43.                 }
  44.                 resultSet = null;
  45.             }
  47.             if (statement != null) {
  48.                 try {
  49.                     statement.close();
  50.                 } catch (SQLException e) {
  51.                     e.printStackTrace();
  52.                 }
  53.                 statement = null;
  54.             }
  56.             if (connection != null) {
  57.                 try {
  58.                     connection.close();
  59.                 } catch (SQLException e) {
  60.                     e.printStackTrace();
  61.                 }
  62.                 connection = null;
  63.             }
  64.         }
  65.     }
  66. }

JDBC的增删改查操作

增删改用executeUpdate，查询用executeQuery

新增记录

  1. package com.song.jdbc;
  3. import org.junit.Test;
  5. import java.sql.*;
  7. public class Demo2 {
  8.     @Test
  9.     public void test() {
  10.         Statement statement = null;
  11.         Connection connection = null;
  13.         try {
  14.             // 1. 注册驱动
  15.             Class.forName("com.mysql.cj.jdbc.Driver");
  17.             // 2. 获得连接
  18.             connection = DriverManager.getConnection("jdbc:mysql:///jdbctest", "root", "???");
  20.             // 3.1 创建执行sql的对象
  21.             statement = connection.createStatement();
  23.             // 3.2 执行sql
  24.             String sql = "insert into user values (null, 'ccc', '333', '王五')";
  25.             int i = statement.executeUpdate(sql);
  26.             if (i > 0) {
  27.                 System.out.println("保存成功！");
  28.             }
  29.         } catch (Exception e) {
  30.             e.printStackTrace();
  31.         } finally {
  32.             // 4. 释放资源
  33.             if (statement != null) {
  34.                 try {
  35.                     statement.close();
  36.                 } catch (SQLException e) {
  37.                     e.printStackTrace();
  38.                 }
  39.                 statement = null;
  40.             }
  42.             if (connection != null) {
  43.                 try {
  44.                     connection.close();
  45.                 } catch (SQLException e) {
  46.                     e.printStackTrace();
  47.                 }
  48.                 connection = null;
  49.             }
  50.         }
  51.     }
  52. }

修改记录

  1. package com.song.jdbc;
  3. import org.junit.Test;
  5. import java.sql.Connection;
  6. import java.sql.DriverManager;
  7. import java.sql.SQLException;
  8. import java.sql.Statement;
  10. public class Demo3 {
  11.     @Test
  12.     public void test() {
  13.         Statement statement = null;
  14.         Connection connection = null;
  16.         try {
  17.             // 1. 注册驱动
  18.             Class.forName("com.mysql.cj.jdbc.Driver");
  20.             // 2. 获得连接
  21.             connection = DriverManager.getConnection("jdbc:mysql:///jdbctest", "root", "???");
  23.             // 3.1 创建执行sql的对象
  24.             statement = connection.createStatement();
  26.             // 3.2 执行sql
  27.             String sql = "update user set username = 'ddd', password='444',name='赵六' where uid = 3";
  28.             int i = statement.executeUpdate(sql);
  29.             if (i > 0) {
  30.                 System.out.println("修改成功！");
  31.             }
  32.         } catch (Exception e) {
  33.             e.printStackTrace();
  34.         } finally {
  35.             // 4. 释放资源
  36.             if (statement != null) {
  37.                 try {
  38.                     statement.close();
  39.                 } catch (SQLException e) {
  40.                     e.printStackTrace();
  41.                 }
  42.                 statement = null;
  43.             }
  45.             if (connection != null) {
  46.                 try {
  47.                     connection.close();
  48.                 } catch (SQLException e) {
  49.                     e.printStackTrace();
  50.                 }
  51.                 connection = null;
  52.             }
  53.         }
  54.     }
  55. }

删除记录

  1. package com.song.jdbc;
  3. import org.junit.Test;
  5. import java.sql.Connection;
  6. import java.sql.DriverManager;
  7. import java.sql.SQLException;
  8. import java.sql.Statement;
  10. public class Demo4 {
  11.     @Test
  12.     public void test() {
  13.         Statement statement = null;
  14.         Connection connection = null;
  16.         try {
  17.             // 1. 注册驱动
  18.             Class.forName("com.mysql.cj.jdbc.Driver");
  20.             // 2. 获得连接
  21.             connection = DriverManager.getConnection("jdbc:mysql:///jdbctest", "root", "???");
  23.             // 3.1 创建执行sql的对象
  24.             statement = connection.createStatement();
  26.             // 3.2 执行sql
  27.             String sql = "delete from user where uid = 3";
  28.             int i = statement.executeUpdate(sql);
  29.             if (i > 0) {
  30.                 System.out.println("删除成功！");
  31.             }
  32.         } catch (Exception e) {
  33.             e.printStackTrace();
  34.         } finally {
  35.             // 4. 释放资源
  36.             if (statement != null) {
  37.                 try {
  38.                     statement.close();
  39.                 } catch (SQLException e) {
  40.                     e.printStackTrace();
  41.                 }
  42.                 statement = null;
  43.             }
  45.             if (connection != null) {
  46.                 try {
  47.                     connection.close();
  48.                 } catch (SQLException e) {
  49.                     e.printStackTrace();
  50.                 }
  51.                 connection = null;
  52.             }
  53.         }
  54.     }
  55. }

查询记录

  1. package com.song.jdbc;
  3. import org.junit.Test;
  5. import java.sql.*;
  7. public class Demo5 {
  8.     @Test
  9.     public void test() {
  10.         ResultSet resultSet = null;
  11.         Statement statement = null;
  12.         Connection connection = null;
  14.         try {
  15.             // 1. 注册驱动
  16.             Class.forName("com.mysql.cj.jdbc.Driver");
  18.             // 2. 获得连接
  19.             connection = DriverManager.getConnection("jdbc:mysql:///jdbctest", "root", "???");
  21.             // 3.1 创建执行sql的对象
  22.             statement = connection.createStatement();
  23.             // 3.2 执行sql
  24.             String sql = "select * from user";
  25.             resultSet = statement.executeQuery(sql);
  26.             while (resultSet.next()) {
  27.                 int uid = resultSet.getInt("uid");
  28.                 String username = resultSet.getString("username");
  29.                 String password = resultSet.getString("password");
  30.                 String name = resultSet.getString("name");
  32.                 System.out.println(uid + " " + username + " " + password + " " + name);
  33.             }
  34.         } catch (Exception e) {
  35.             e.printStackTrace();
  36.         } finally {
  37.             // 4. 释放资源
  38.             if (resultSet != null) {
  39.                 try {
  40.                     resultSet.close();
  41.                 } catch (SQLException e) {
  42.                     e.printStackTrace();
  43.                 }
  44.                 resultSet = null;
  45.             }
  47.             if (statement != null) {
  48.                 try {
  49.                     statement.close();
  50.                 } catch (SQLException e) {
  51.                     e.printStackTrace();
  52.                 }
  53.                 statement = null;
  54.             }
  56.             if (connection != null) {
  57.                 try {
  58.                     connection.close();
  59.                 } catch (SQLException e) {
  60.                     e.printStackTrace();
  61.                 }
  62.                 connection = null;
  63.             }
  64.         }
  65.     }
  66. }
  1. package com.song.jdbc;
  3. import org.junit.Test;
  5. import java.sql.*;
  7. public class Demo5 {
  8.     @Test
  9.     public void test() {
  10.         ResultSet resultSet = null;
  11.         Statement statement = null;
  12.         Connection connection = null;
  14.         try {
  15.             // 1. 注册驱动
  16.             Class.forName("com.mysql.cj.jdbc.Driver");
  18.             // 2. 获得连接
  19.             connection = DriverManager.getConnection("jdbc:mysql:///jdbctest", "root", "???");
  21.             // 3.1 创建执行sql的对象
  22.             statement = connection.createStatement();
  23.             // 3.2 执行sql
  24.             String sql = "select * from user where uid = 2";
  25.             resultSet = statement.executeQuery(sql);
  26.             if (resultSet.next()) {
  27.                 int uid = resultSet.getInt("uid");
  28.                 String username = resultSet.getString("username");
  29.                 String password = resultSet.getString("password");
  30.                 String name = resultSet.getString("name");
  32.                 System.out.println(uid + " " + username + " " + password + " " + name);
  33.             }
  34.         } catch (Exception e) {
  35.             e.printStackTrace();
  36.         } finally {
  37.             // 4. 释放资源
  38.             if (resultSet != null) {
  39.                 try {
  40.                     resultSet.close();
  41.                 } catch (SQLException e) {
  42.                     e.printStackTrace();
  43.                 }
  44.                 resultSet = null;
  45.             }
  47.             if (statement != null) {
  48.                 try {
  49.                     statement.close();
  50.                 } catch (SQLException e) {
  51.                     e.printStackTrace();
  52.                 }
  53.                 statement = null;
  54.             }
  56.             if (connection != null) {
  57.                 try {
  58.                     connection.close();
  59.                 } catch (SQLException e) {
  60.                     e.printStackTrace();
  61.                 }
  62.                 connection = null;
  63.             }
  64.         }
  65.     }
  66. }

JDBC的工具类

src/jdbc.properties
#右击src文件夹，New/file/jdbc.properties

  1. driverClass = com.mysql.cj.jdbc.Driver
  2. url = jdbc:mysql:///jdbctest
  3. username = root;
  4. password = ???

src/com.song.jdbc.utils

  1. package com.song.jdbc.utils;
  3. import java.io.IOException;
  4. import java.io.InputStream;
  5. import java.sql.*;
  6. import java.util.Properties;
  8. public class JDBCUtils {
  9.     private static final String driverClass;
  10.     private static final String url;
  11.     private static final String username;
  12.     private static final String password;
  14.     static {
  15.         // 加载属性文件并解析
  16.         Properties props = new Properties();
  18.         // 如何获得属性文件的输入流？
  19.         // 通常情况下使用类的加载器的方式进行获取
  20.         InputStream inputStream = JDBCUtils.class.getClassLoader().getResourceAsStream("jdbc.properties");
  21.         try {
  22.             props.load(inputStream);
  23.         } catch (IOException e) {
  24.             e.printStackTrace();
  25.         }
  27.         driverClass = props.getProperty("driverClass");
  28.         url = props.getProperty("url");
  29.         username = props.getProperty("username");
  30.         password = props.getProperty("password");
  31.     }
  33.     // 注册驱动的方法
  34.     public static void loadDriver() throws ClassNotFoundException {
  35.         Class.forName(driverClass);
  36.     }
  38.     // 获得连接的方法
  39.     public static Connection getConnection() throws Exception {
  40.         loadDriver();
  41.         Connection connection = DriverManager.getConnection(url, username, password);
  42.         return connection;
  43.     }
  45.     // 资源释放
  46.     public static void release(Statement statement, Connection connection) {
  47.         if (statement != null) {
  48.             try {
  49.                 statement.close();
  50.             } catch (SQLException e) {
  51.                 e.printStackTrace();
  52.             }
  53.             statement = null;
  54.         }
  56.         if (connection != null) {
  57.             try {
  58.                 connection.close();
  59.             } catch (SQLException e) {
  60.                 e.printStackTrace();
  61.             }
  62.             connection = null;
  63.         }
  64.     }
  66.     public static void release(ResultSet resultSet, Statement statement, Connection connection) {
  67.         if (resultSet != null) {
  68.             try {
  69.                 resultSet.close();
  70.             } catch (SQLException e) {
  71.                 e.printStackTrace();
  72.             }
  73.             resultSet = null;
  74.         }
  76.         if (statement != null) {
  77.             try {
  78.                 statement.close();
  79.             } catch (SQLException e) {
  80.                 e.printStackTrace();
  81.             }
  82.             statement = null;
  83.         }
  85.         if (connection != null) {
  86.             try {
  87.                 connection.close();
  88.             } catch (SQLException e) {
  89.                 e.printStackTrace();
  90.             }
  91.             connection = null;
  92.         }
  93.     }
  94. }

src/com.song.jdbc.demo

  1. package com.song.jdbc.demo;
  3. import com.song.jdbc.utils.JDBCUtils;
  4. import org.junit.Test;
  6. import java.sql.*;
  8. public class Demo6 {
  9.     @Test
  10.     public void test() {
  11.         ResultSet resultSet = null;
  12.         Statement statement = null;
  13.         Connection connection = null;
  15.         try {
  16.             // 获得连接
  17.             connection = JDBCUtils.getConnection();
  19.             // 创建执行sql的对象
  20.             statement = connection.createStatement();
  22.             // 执行sql
  23.             String sql = "select * from user where uid = 2";
  24.             resultSet = statement.executeQuery(sql);
  25.             if (resultSet.next()) {
  26.                 int uid = resultSet.getInt("uid");
  27.                 String username = resultSet.getString("username");
  28.                 String password = resultSet.getString("password");
  29.                 String name = resultSet.getString("name");
  31.                 System.out.println(uid + " " + username + " " + password + " " + name);
  32.             }
  33.         } catch (Exception e) {
  34.             e.printStackTrace();
  35.         } finally {
  36.             JDBCUtils.release(resultSet, statement, connection);
  37.         }
  38.     }
  39. }

JDBC的SQL注入防御

PreparedStatement是Statement的子接口，它的实例对象可以通过调用Connection.preparedStatenebt(sql)方法获得，相对于Statement对象而言：

  • PreparedStatement可以避免SQL注入的问题
  • Statement会使数据库频繁变异SQL，可能造成数据库缓存区溢出。PreparedStatement可对SQL进行预编译，从而提高数据库的执行效率
  • 并且PreparedStatement对于sql中的参数，允许使用占位符的形式进行替换，简化sql语句的编写
  1. public static void login(String username, String password) {
  2.     ResultSet resultSet = null;
  3.     PreparedStatement preparedStatement = null;
  4.     Connection connection = null;
  6.     try {
  7.         // 获得连接
  8.         connection = JDBCUtils.getConnection();
  10.         // 编写sql
  11.         String sql = "select * from user where username = ? and password = ?";
  13.         // 预处理sql
  14.         preparedStatement = connection.prepareStatement(sql);
  16.         // 设置参数
  17.         preparedStatement.setString(1, username);
  18.         preparedStatement.setString(1, password);
  20.         // 执行sql
  21.         resultSet = preparedStatement.executeQuery();
  22.         if (resultSet.next()) {
  23.             System.out.println("登录成功！");
  24.         }else{
  25.             System.out.println("登录失败！");
  26.         }
  27.     } catch (Exception e) {
  28.         e.printStackTrace();
  29.     } finally {
  30.         JDBCUtils.release(resultSet, preparedStatement, connection);
  31.     }
  32. }

PreparedStatement

新增记录

  1. package com.song.jdbc.demo;
  3. import com.song.jdbc.utils.JDBCUtils;
  4. import org.junit.Test;
  6. import java.sql.Connection;
  7. import java.sql.PreparedStatement;
  8. import java.sql.ResultSet;
  10. public class Demo8 {
  11.     @Test
  12.     public void test() {
  13.         PreparedStatement preparedStatement = null;
  14.         Connection connection = null;
  16.         try {
  17.             // 获得连接
  18.             connection = JDBCUtils.getConnection();
  20.             // 编写sql
  21.             String sql = "insert into user values (null, ?, ?, ?)";
  23.             // 预处理sql
  24.             preparedStatement = connection.prepareStatement(sql);
  26.             // 设置参数
  27.             preparedStatement.setString(1, "ccc");
  28.             preparedStatement.setString(2, "333");
  29.             preparedStatement.setString(3, "王五");
  31.             // 执行sql
  32.             int num = preparedStatement.executeUpdate();
  33.             if (num >0) {
  34.                 System.out.println("新增成功！");
  35.             }
  36.         } catch (Exception e) {
  37.             e.printStackTrace();
  38.         } finally {
  39.             JDBCUtils.release(preparedStatement, connection);
  40.         }
  41.     }
  42. }

修改记录

  1. package com.song.jdbc.demo;
  3. import com.song.jdbc.utils.JDBCUtils;
  4. import org.junit.Test;
  6. import java.sql.Connection;
  7. import java.sql.PreparedStatement;
  8. import java.sql.ResultSet;
  10. public class Demo8 {
  11.     @Test
  12.     public void test() {
  13.         PreparedStatement preparedStatement = null;
  14.         Connection connection = null;
  16.         try {
  17.             // 获得连接
  18.             connection = JDBCUtils.getConnection();
  20.             // 编写sql
  21.             String sql = "update user set username = ?, password = ?, name = ? where uid =?";
  23.             // 预处理sql
  24.             preparedStatement = connection.prepareStatement(sql);
  26.             // 设置参数
  27.             preparedStatement.setString(1, "ddd");
  28.             preparedStatement.setString(2, "444");
  29.             preparedStatement.setString(3, "赵六");
  30.             preparedStatement.setString(4, "4");
  32.             // 执行sql
  33.             int num = preparedStatement.executeUpdate();
  34.             if (num >0) {
  35.                 System.out.println("修改成功！");
  36.             }
  37.         } catch (Exception e) {
  38.             e.printStackTrace();
  39.         } finally {
  40.             JDBCUtils.release(preparedStatement, connection);
  41.         }
  42.     }
  43. }

删除记录

  1. package com.song.jdbc.demo;
  3. import com.song.jdbc.utils.JDBCUtils;
  4. import org.junit.Test;
  6. import java.sql.Connection;
  7. import java.sql.PreparedStatement;
  9. public class Demo9 {
  10.     @Test
  11.     public void test() {
  12.         PreparedStatement preparedStatement = null;
  13.         Connection connection = null;
  15.         try {
  16.             // 获得连接
  17.             connection = JDBCUtils.getConnection();
  19.             // 编写sql
  20.             String sql = "delete from user where uid = ?";
  22.             // 预处理sql
  23.             preparedStatement = connection.prepareStatement(sql);
  25.             // 设置参数
  26.             preparedStatement.setString(1, "4");
  28.             // 执行sql
  29.             int num = preparedStatement.executeUpdate();
  30.             if (num >0) {
  31.                 System.out.println("删除成功！");
  32.             }
  33.         } catch (Exception e) {
  34.             e.printStackTrace();
  35.         } finally {
  36.             JDBCUtils.release(preparedStatement, connection);
  37.         }
  38.     }
  39. }

查询记录

  1. package com.song.jdbc.demo;
  3. import com.song.jdbc.utils.JDBCUtils;
  4. import org.junit.Test;
  6. import java.sql.*;
  8. public class Demo7 {
  9.     @Test
  10.     public void test() {
  11.         ResultSet resultSet = null;
  12.         PreparedStatement preparedStatement = null;
  13.         Connection connection = null;
  15.         try {
  16.             // 获得连接
  17.             connection = JDBCUtils.getConnection();
  19.             // 编写sql
  20.             String sql = "select * from user where uid = ?";
  22.             // 预处理sql
  23.             preparedStatement = connection.prepareStatement(sql);
  25.             // 设置参数
  26.             preparedStatement.setString(1, "2");
  28.             // 执行sql
  29.             resultSet = preparedStatement.executeQuery();
  30.             if (resultSet.next()) {
  31.                 int uid = resultSet.getInt("uid");
  32.                 String username = resultSet.getString("username");
  33.                 String password = resultSet.getString("password");
  34.                 String name = resultSet.getString("name");
  36.                 System.out.println(uid + " " + username + " " + password + " " + name);
  37.             }
  38.         } catch (Exception e) {
  39.             e.printStackTrace();
  40.         } finally {
  41.             JDBCUtils.release(resultSet, preparedStatement, connection);
  42.         }
  43.     }
  44. }

连接池C3P0

连接池是创建和管理一个连接的缓冲池的技术，这些连接准备好被任何需要它们的线程使用。

image.png

image.png

第一步：在lib文件中存放C3P0的jar包（以下网页的资料里有）
https://class.imooc.com/course/721
第二步：引入C3P0连接池到项目中
在IntelliJ IDEA 中选择File->Project Structure->Modules->Dependencies 点击下面的小加号，选择JARs or directories选择你要添加的外部jar包。

使用1：
#以下代码有个问题，每次执行Demo10的时候，就会创建一个连接池对象，这样是很浪费的。应该抽取出连接方法成static，这样就将只会创建一个连接池对象了，请看使用2。

  1. package com.song.jdbc.demo;
  3. import com.mchange.v2.c3p0.ComboPooledDataSource;
  4. import com.song.jdbc.utils.JDBCUtils;
  5. import org.junit.Test;
  7. import java.sql.*;
  9. public class Demo10 {
  10.     @Test
  11.     public void test() {
  12.         ResultSet resultSet = null;
  13.         PreparedStatement preparedStatement = null;
  14.         Connection connection = null;
  16.         try {
  17.             // 创建连接池
  18.             ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();
  20.             // 设置连接池的参数
  21.             comboPooledDataSource.setDriverClass("com.mysql.cj.jdbc.Driver");
  22.             comboPooledDataSource.setJdbcUrl("jdbc:mysql:///jdbctest");
  23.             comboPooledDataSource.setUser("root");
  24.             comboPooledDataSource.setPassword("???");
  25.             comboPooledDataSource.setMaxPoolSize(20);
  26.             comboPooledDataSource.setInitialPoolSize(3);
  28.             // 获得连接
  29.             connection = comboPooledDataSource.getConnection();
  31.             // 编写sql
  32.             String sql = "select * from user";
  34.             // 预处理sql
  35.             preparedStatement = connection.prepareStatement(sql);
  37.             // 执行sql
  38.             resultSet = preparedStatement.executeQuery();
  39.             while (resultSet.next()) {
  40.                 int uid = resultSet.getInt("uid");
  41.                 String username = resultSet.getString("username");
  42.                 String password = resultSet.getString("password");
  43.                 String name = resultSet.getString("name");
  45.                 System.out.println(uid + " " + username + " " + password + " " + name);
  46.             }
  47.         } catch (Exception e) {
  48.             e.printStackTrace();
  49.         } finally {
  50.             JDBCUtils.release(resultSet, preparedStatement, connection);
  51.         }
  52.     }
  53. }

使用2：配置化

  1. package com.song.jdbc.demo;
  3. import com.mchange.v2.c3p0.ComboPooledDataSource;
  4. import com.song.jdbc.utils.JDBCUtils2;
  5. import org.junit.Test;
  7. import java.sql.Connection;
  8. import java.sql.PreparedStatement;
  9. import java.sql.ResultSet;
  11. public class Demo11 {
  12.     @Test
  13.     public void test() {
  14.         ResultSet resultSet = null;
  15.         PreparedStatement preparedStatement = null;
  16.         Connection connection = null;
  18.         try {
  19.             // 获得连接
  20.             connection = JDBCUtils2.getConnection();
  22.             // 编写sql
  23.             String sql = "select * from user";
  25.             // 预处理sql
  26.             preparedStatement = connection.prepareStatement(sql);
  28.             // 执行sql
  29.             resultSet = preparedStatement.executeQuery();
  30.             while (resultSet.next()) {
  31.                 int uid = resultSet.getInt("uid");
  32.                 String username = resultSet.getString("username");
  33.                 String password = resultSet.getString("password");
  34.                 String name = resultSet.getString("name");
  36.                 System.out.println(uid + " " + username + " " + password + " " + name);
  37.             }
  38.         } catch (Exception e) {
  39.             e.printStackTrace();
  40.         } finally {
  41.             JDBCUtils2.release(resultSet, preparedStatement, connection);
  42.         }
  43.     }
  44. }

配置文件：src/c3p0-config.xml

  1. <?xml version="1.0" encoding="UTF-8"?>
  2. <c3p0-config>
  4.   <default-config>
  5.     <property name="driverClass">com.mysql.cj.jdbc.Driver</property>
  6.     <property name="jdbcUrl">jdbc:mysql:///jdbctest</property>
  7.     <property name="user">root</property>
  8.     <property name="password">???</property>
  9.     <property name="initialPoolSize">5</property>
  10.     <property name="maxPoolSize">20</property>
  11.   </default-config>
  13. </c3p0-config>

工具类：src/com.song.jdbc/utils/JDBCUtils2：

  1. package com.song.jdbc.utils;
  3. import com.mchange.v2.c3p0.ComboPooledDataSource;
  5. import java.sql.*;
  7. public class JDBCUtils2 {
  8.     private static final ComboPooledDataSource dataSource = new ComboPooledDataSource();
  10.     // 获得连接的方法
  11.     public static Connection getConnection() throws Exception {
  12.         Connection connection = dataSource.getConnection();
  13.         return connection;
  14.     }
  16.     // 资源释放
  17.     public static void release(Statement statement, Connection connection) {
  18.         if (statement != null) {
  19.             try {
  20.                 statement.close();
  21.             } catch (SQLException e) {
  22.                 e.printStackTrace();
  23.             }
  24.             statement = null;
  25.         }
  27.         if (connection != null) {
  28.             try {
  29.                 connection.close();
  30.             } catch (SQLException e) {
  31.                 e.printStackTrace();
  32.             }
  33.             connection = null;
  34.         }
  35.     }
  37.     public static void release(ResultSet resultSet, Statement statement, Connection connection) {
  38.         if (resultSet != null) {
  39.             try {
  40.                 resultSet.close();
  41.             } catch (SQLException e) {
  42.                 e.printStackTrace();
  43.             }
  44.             resultSet = null;
  45.         }
  47.         if (statement != null) {
  48.             try {
  49.                 statement.close();
  50.             } catch (SQLException e) {
  51.                 e.printStackTrace();
  52.             }
  53.             statement = null;
  54.         }
  56.         if (connection != null) {
  57.             try {
  58.                 connection.close();
  59.             } catch (SQLException e) {
  60.                 e.printStackTrace();
  61.             }
  62.             connection = null;
  63.         }
  64.     }
  65. }