Java之JDBC_jdbc依赖-CSDN博客

本文链接：https://blog.csdn.net/zhangzengxiu/article/details/126634523

JDBC

概念

JDBC（Java DataBase Connectivity java数据库连接）是一种用于执行SQL语句的Java API，可以为多种关系型数据库提供统一访问，它是由一组用Java语言编写的类和接口组成的。

本质

java官方提供的一套规范(接口)。用于帮助开发人员快速实现不同关系型数据库的连接！

快速入门

入门案例

JDBC入门Demo

数据库准备

-- 创建测试数据库
CREATE DATABASE IF NOT EXISTS jdbc;

-- 使用数据库
USE jdbc;

-- 创建测试表
CREATE TABLE IF NOT EXISTS user(
id INT PRIMARY KEY AUTO_INCREMENT  COMMENT '主键',
`name` VARCHAR(20) NOT NULL COMMENT '姓名'
);

-- 插入测试数据
INSERT INTO user VALUES(NULL,'zhanngsan'),(NULL,'lisi');

-- 查询测试数据
SELECT
	*
FROM
	user;

pom依赖

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>JDBCDemo</groupId>
    <artifactId>JDBCDemo</artifactId>
    <version>1.0-SNAPSHOT</version>

    <dependencies>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.27</version>
        </dependency>
    </dependencies>

</project>

java代码

package com.test;

import java.sql.*;

/**
 * @author zhangzengxiu
 * @date 2022/8/27
 */
public class JDBCDemo01 {

    public static void main(String[] args) throws Exception {
        //1.注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2.获取连接
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc", "root", "root");
        //3.获取执行者对象
        Statement statement = connection.createStatement();
        //4.执行SQL语句，返回结果
        String sql = "SELECT * FROM user";
        ResultSet resultSet = statement.executeQuery(sql);
        //5.结果处理
        while (resultSet.next()) {
            System.out.println(resultSet.getInt("id") + " " + resultSet.getString("name"));
        }
        //6.资源释放
        resultSet.close();
        statement.close();
        connection.close();
    }
}

功能类详解

DriverManager

① 注册驱动

注册给定的驱动程序：static void registerDriver(Driver driver);

写代码使用：Class.forName(“com.mysql.jdbc.Driver”);

在com.mysql.jdbc.Driver类中存在静态代码块

Driver源代码：

//
// Source code recreated from a .class file by IntelliJ IDEA
// (powered by Fernflower decompiler)
//

package com.mysql.jdbc;

import java.sql.SQLException;

public class Driver extends com.mysql.cj.jdbc.Driver {
    public Driver() throws SQLException {
    }

    static {
        System.err.println("Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.");
    }
}

//
// Source code recreated from a .class file by IntelliJ IDEA
// (powered by Fernflower decompiler)
//

package com.mysql.jdbc;

import java.sql.DriverManager;
import java.sql.SQLException;

public class Driver extends NonRegisteringDriver implements java.sql.Driver {
    public Driver() throws SQLException {
    }

    static {
        try {
            DriverManager.registerDriver(new Driver());
        } catch (SQLException var1) {
            throw new RuntimeException("Can't register driver!");
        }
    }
}

因为静态代码块已经实现注册驱动，所以不需要单独通过registerDriver来注册驱动。否则会导致重复注册驱动。

可以通过 Class.forName(“com.mysql.jdbc.Driver”); 来进行驱动注册，从而初始化静态代码块。

注意：

我们不需要通过DriverManager调用静态方法registerDriver()，因为只要Driver类被使用，则会执行其静态代码块完成注册驱动
mysql5之后可以省略注册驱动的步骤。在jar包中，存在一个java.sql.Driver配置文件，文件中指定了com.mysql.jdbc.Driver

java.sql.Driver配置文件

com.mysql.cj.jdbc.Driver

② 获取数据库连接

获取数据库连接对象：static Connection getConnection(String url, String user, String password);

返回值：Connection 数据库连接对象

参数

url：指定连接的路径。语法：jdbc:mysql://ip地址(域名):端口号/数据库名称

user：用户名

password：密码

Connection

与特定的数据的连接（会话）。执行SQL语句并在连接的上下文中返回结果。

① 获取执行者对象

获取普通执行者对象：Statement createStatement();

获取预编译执行者对象：PreparedStatement prepareStatement(String sql);

② 管理事务

开启事务：setAutoCommit(boolean autoCommit); 参数为false，则开启事务。

提交事务：commit();

回滚事务：rollback();

③ 释放资源

立即将数据库连接对象释放：void close();

Statement

① 执行DML语句：int executeUpdate(String sql);

返回值int：返回影响的行数。

参数sql：可以执行insert、update、delete语句。

② 执行DQL语句：ResultSet executeQuery(String sql);

返回值ResultSet：封装查询的结果。

参数sql：可以执行select语句。

③ 释放资源

立即将执行者对象释放：void close();

ResultSet

① 判断结果集中是否还有数据：boolean next();

有数据返回true，并将索引向下移动一行。

没有数据返回false。

② 获取结果集中的数据：XXX getXxx(“列名”);

XXX代表数据类型(要获取某列数据，这一列的数据类型)。

例如：String getString(“name”); int getInt(“age”);

③ 释放资源

立即将结果集对象释放：void close();

JDBC案例CRUD

环境

数据库环境准备

-- 插入测试数据
INSERT INTO student VALUES
(NULL,'张三',23,'1999-09-23'),
(NULL,'李四',24,'1998-08-1'),
(NULL,'王五',25,'1996-06-06'),
(NULL,'赵六',26,'1994-10-20');

-- 查询
SELECT
	*
FROM
	student;

java代码

pom依赖

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>JDBCDemo</groupId>
    <artifactId>JDBCDemo</artifactId>
    <version>1.0-SNAPSHOT</version>

    <dependencies>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.27</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.22</version>
        </dependency>
    </dependencies>

</project>

java代码

package com.test01.demo;

import com.test01.pojos.Student;
import org.junit.Test;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

/**
 * @author zhangzengxiu
 * @date 2022/8/27
 */
public class JDBCTest {

    /**
     * 查询全部
     */
    @Test
    public void testFindAll() {
        ResultSet resultSet = null;
        Statement statement = null;
        Connection connection = null;
        try {
            //注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            //获取连接
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc", "root", "root");
            //获取执行者对象
            statement = connection.createStatement();
            //执行SQL，返回结果
            String sql = "SELECT * FROM student";
            resultSet = statement.executeQuery(sql);
            ArrayList<Student> list = new ArrayList<Student>();
            //结果处理
            while (resultSet.next()) {
                Integer sid = resultSet.getInt("sid");
                String name = resultSet.getString("name");
                Integer age = resultSet.getInt("age");
                Date birthday = resultSet.getDate("birthday");
                Student student = new Student(sid, name, age, birthday);
                list.add(student);
            }
            for (Student student : list) {
                System.out.println(student);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //资源释放
            if (resultSet != null) {
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * 根据sid查询学生
     */
    @Test
    public void testFindStuBySid() {
        //模拟传入sid
        Integer stuId = 1;
        ResultSet resultSet = null;
        Statement statement = null;
        Connection connection = null;
        Student student = null;
        try {
            //注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            //获取连接
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc", "root", "root");
            //获取执行者对象
            statement = connection.createStatement();
            //执行SQL，返回结果
            String sql = "SELECT * FROM student WHERE sid ='" + stuId + "'";
            resultSet = statement.executeQuery(sql);
            //结果处理
            while (resultSet.next()) {
                Integer sid = resultSet.getInt("sid");
                String name = resultSet.getString("name");
                Integer age = resultSet.getInt("age");
                Date birthday = resultSet.getDate("birthday");
                student = new Student(sid, name, age, birthday);
            }
            System.out.println(student);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //资源释放
            if (resultSet != null) {
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * 新增学生
     */
    @Test
    public void testInsertStu() {
        //模拟传入student
        Student student = new Student();
        student.setName("马七");
        student.setAge(27);
        student.setBirthday(new Date());
        Statement statement = null;
        Connection connection = null;
        try {
            //注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            //获取连接
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc", "root", "root");
            //获取执行者对象
            statement = connection.createStatement();
            //执行SQL，返回结果
            String birthday = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(student.getBirthday());
            String sql = "INSERT INTO student(name,age,birthday) VALUES ('" + student.getName() + "','" + student.getAge() + "','" + birthday + "')";
            //返回影响行数
            int rows = statement.executeUpdate(sql);
            //结果处理
            System.out.println("影响行数=" + rows);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //资源释放
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * 修改学生
     */
    @Test
    public void testUpdateStu() {
        //模拟传入student
        Student student = new Student();
        student.setSid(5);
        student.setName("马七");
        student.setAge(37);
        student.setBirthday(new Date());
        Statement statement = null;
        Connection connection = null;
        try {
            //注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            //获取连接
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc", "root", "root");
            //获取执行者对象
            statement = connection.createStatement();
            //执行SQL，返回结果
            String birthday = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(student.getBirthday());
            String sql = "UPDATE student SET name='" + student.getName() + "',age='" + student.getAge() + "',birthday='" + birthday + "' WHERE sid = '" + student.getSid() + "'";
            //返回影响行数
            int rows = statement.executeUpdate(sql);
            //结果处理
            System.out.println("影响行数=" + rows);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //资源释放
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * 删除学生
     */
    @Test
    public void testDelStu() {
        //模拟传入sid
        Integer stuId = 5;
        Statement statement = null;
        Connection connection = null;
        try {
            //注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            //获取连接
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc", "root", "root");
            //获取执行者对象
            statement = connection.createStatement();
            //执行SQL，返回结果
            String sql = "DELETE FROM student WHERE sid='" + stuId + " '";
            //返回影响行数
            int rows = statement.executeUpdate(sql);
            //结果处理
            System.out.println("影响行数=" + rows);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //资源释放
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

JDBC工具类

配置文件

jdbc.driverClass=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/jdbc
jdbc.username=root
jdbc.password=root

工具类

package com.test01.utils;

import java.io.BufferedReader;
import java.io.FileInputStream;
import java.io.InputStreamReader;
import java.sql.*;
import java.util.Properties;

/**
 * @author zhangzengxiu
 * @date 2022/8/28
 */
public class JDBCUtils {

    private static String driverClass;

    private static String url;

    private static String username;

    private static String password;

    private JDBCUtils() {

    }

    static {
        try {
            load();
            Class.forName(driverClass);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 加载配置文件属性
     *
     * @throws Exception
     */
    private static void load() throws Exception {
        Properties properties = new Properties();
        BufferedReader bufferedReader = new BufferedReader(new InputStreamReader(new FileInputStream("src/main/resources/jdbc.properties")));
        properties.load(bufferedReader);
        driverClass = (String) properties.get("jdbc.driverClass");
        url = (String) properties.get("jdbc.url");
        username = (String) properties.get("jdbc.username");
        password = (String) properties.get("jdbc.password");
    }

    /**
     * 获取连接
     *
     * @return
     */
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url, username, password);
    }

    /**
     * 释放资源
     *
     * @param resultSet
     * @param statement
     * @param connection
     */
    public static void close(ResultSet resultSet, Statement statement, Connection connection) {
        //资源释放
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 释放资源
     *
     * @param statement
     * @param connection
     */
    public static void close(Statement statement, Connection connection) {
        //资源释放
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

修改后的java代码

package com.test01.demo;

import com.test01.pojos.Student;
import com.test01.utils.JDBCUtils;
import org.junit.Test;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

/**
 * @author zhangzengxiu
 * @date 2022/8/27
 */
public class JDBCTest2 {

    /**
     * 查询全部
     */
    @Test
    public void testFindAll() {
        ResultSet resultSet = null;
        Statement statement = null;
        Connection connection = null;
        try {
            connection = JDBCUtils.getConnection();
            //获取执行者对象
            statement = connection.createStatement();
            //执行SQL，返回结果
            String sql = "SELECT * FROM student";
            resultSet = statement.executeQuery(sql);
            ArrayList<Student> list = new ArrayList<Student>();
            //结果处理
            while (resultSet.next()) {
                Integer sid = resultSet.getInt("sid");
                String name = resultSet.getString("name");
                Integer age = resultSet.getInt("age");
                Date birthday = resultSet.getDate("birthday");
                Student student = new Student(sid, name, age, birthday);
                list.add(student);
            }
            for (Student student : list) {
                System.out.println(student);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //资源释放
            JDBCUtils.close(resultSet, statement, connection);
        }
    }
}

SQL注入攻击

SQL注入攻击就是利用sql语句的漏洞来对系统进行攻击

示例

核心代码

	/**
     * 根据username和password查询学生
     */
    @Test
    public void testFindStuBySid() {
        //模拟传入用户名密码
        String username="zhangsan";
        String password="xxx'or '1' = '1'";
        Integer stuId = 1;
        ResultSet resultSet = null;
        Statement statement = null;
        Connection connection = null;
        Student student = null;
        try {
            connection = JDBCUtils.getConnection();
            //获取执行者对象
            statement = connection.createStatement();
            //执行SQL，返回结果
           String sql = "SELECT * FROM student WHERE username ='" + username + "' AND password ='"+password+"'";
            resultSet = statement.executeQuery(sql);
            //结果处理
            while (resultSet.next()) {
                Integer sid = resultSet.getInt("sid");
                String name = resultSet.getString("name");
                Integer age = resultSet.getInt("age");
                Date birthday = resultSet.getDate("birthday");
                student = new Student(sid, name, age, birthday);
            }
            System.out.println(student);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //资源释放
            JDBCUtils.close(resultSet, statement, connection);
        }
    }

当输入正确的用户名和密码时：查询成功！

当输入用户名和错误的SQL注入攻击的密码时：SQL会变为：

SELECT * FROM student WHERE username=‘张三’ AND password=24 OR ‘1’ = ‘1’

解决

PreparedStatement 预编译执行者对象

在执行sql语句之前，将sql语句进行提前编译。明确sql语句的格式后，就不会改变了。剩余的内容都会认为是参数！

SQL语句中的参数使用?作为占位符
为?占位符赋值的方法：setXxx(参数1,参数2);

Xxx代表：数据类型

参数1：?的位置编号(编号从1开始)

参数2：?的实际参数
```
String sql="DELETE FROM user WHERE username=?";
pstm = connection.prepareStatement(sql);
pstm.setString(1,"zhangsan");
```
执行SQL语句

执行insert、update、delete语句：int executeUpdate();

执行select语句：ResultSet executeQuery();

示例

	/**
     * 根据username和password查询学生
     */
    @Test
    public void testFindUser() {
        //模拟传入sid
        String username = "zhangsan";
        String password = "xxxx' OR '1'='1'";
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;
        Connection connection = null;
        Student student = null;
        try {
            connection = JDBCUtils.getConnection();
            //获取执行者对象
            String sql = "SELECT * FROM student WHERE username =? AND password=?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, username);
            preparedStatement.setString(2, password);
            preparedStatement.executeQuery();
            //结果处理
            while (resultSet.next()) {
                Integer sid = resultSet.getInt("sid");
                String name = resultSet.getString("name");
                Integer age = resultSet.getInt("age");
                Date birthday = resultSet.getDate("birthday");
                student = new Student(sid, name, age, birthday);
            }
            System.out.println(student);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //资源释放
            JDBCUtils.close(resultSet, preparedStatement, connection);
        }
    }
}

核心代码

使用PreparedStatement 预编译执行者对象

Connection connection = JDBCUtils.getConnection();
String sql = "SELECT * FROM student WHERE username =? AND password=?";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, username);
preparedStatement.setString(2, password);
preparedStatement.executeQuery();

JDBC事务管理

JDBC管理事务

管理事务的功能类：Connection

开启事务：setAutoCommit(boolean autoCommit); 参数为false，则开启事务。

提交事务：commit();

回滚事务：rollback();

注意：事务的管理需要在业务层实现，因为dao层的功能要给很多模块提供功能的支撑，而有些模块是不需要事务的。

示例

核心代码

	/**
     * 新增学生
     */
    @Test
    public void testInsertStu() {
        //模拟传入student
        Student student = new Student();
        student.setName("马七");
        student.setAge(27);
        student.setBirthday(new Date());
        PreparedStatement statement = null;
        Connection connection = null;
        try {
            connection = JDBCUtils.getConnection();
            //开启事务
            connection.setAutoCommit(false);
            String birthday = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(student.getBirthday());
            String sql = "INSERT INTO student(name,age,birthday) VALUES ( ? , ? , ? )";
            statement = connection.prepareStatement(sql);
            statement.setString(1, student.getName());
            statement.setInt(2, student.getAge());
            statement.setString(3, birthday);
            //返回影响行数
            int rows = statement.executeUpdate();
            //结果处理
            System.out.println("影响行数=" + rows);
            //提交事务
            connection.commit();
        } catch (Exception e) {
            e.printStackTrace();
            //回滚事务
            if (connection != null) {
                try {
                    connection.rollback();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
        } finally {
            //资源释放
            JDBCUtils.close(statement, connection);
        }
    }

数据库连接池

背景

数据库连接的背景

数据库连接是一种关键的、有限的、昂贵的资源，这一点在多用户的网页应用程序中体现得尤为突出
对数据库连接的管理能显著影响到整个应用程序的性能指标，数据库连接池正是针对这个问题提出来的

数据库连接池

数据库连接池负责分配、管理和释放数据库连接，它允许应用程序重复使用一个现有的数据库连接，而不是再重新建立一个。这项技术能明显提高对数据库操作的性能

c3p0连接池

c3p0-config.xml

<c3p0-config>
  <!-- 使用默认的配置读取连接池对象 -->
  <default-config>
  	<!--  连接参数 -->
    <property name="driverClass">com.mysql.jdbc.Driver</property>
    <property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbc</property>
    <property name="user">root</property>
    <property name="password">root</property>
    
    <!-- 连接池参数 -->
    <!--初始化的连接数量-->
    <property name="initialPoolSize">5</property>
    <!--最大连接数量-->
    <property name="maxPoolSize">10</property>
    <!--超时时间-->
    <property name="checkoutTimeout">3000</property>
  </default-config>

  <named-config name="otherc3p0"> 
    <!--  连接参数 -->
    <property name="driverClass">com.mysql.jdbc.Driver</property>
    <property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbc</property>
    <property name="user">root</property>
    <property name="password">root</property>
    
    <!-- 连接池参数 -->
    <property name="initialPoolSize">5</property>
    <property name="maxPoolSize">8</property>
    <property name="checkoutTimeout">1000</property>
  </named-config>
</c3p0-config>

备注：

如果是非maven项目，则该配置文件需要放在src目录下；

如果是maven项目，则该文件放在resources目录下。否则报错！！！！！！！！！！！！

报错：

Disconnected from the target VM, address: '127.0.0.1:3790', transport: 'socket'
Exception in thread "main" java.sql.SQLException: Connections could not be acquired from the underlying database!
	at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:106)
	at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:529)
	at com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource.getConnection(AbstractPoolBackedDataSource.java:128)
	at com.test02.C3P0Test.main(C3P0Test.java:22)
Caused by: com.mchange.v2.resourcepool.CannotAcquireResourceException: A ResourcePool could not acquire a resource from its primary factory or source.
	at com.mchange.v2.resourcepool.BasicResourcePool.awaitAvailable(BasicResourcePool.java:1319)
	at com.mchange.v2.resourcepool.BasicResourcePool.prelimCheckoutResource(BasicResourcePool.java:557)
	at com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource(BasicResourcePool.java:477)
	at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:525)
	... 2 more

报错的原因有很多：

1、c3p0-config.xml文件名不对

2、文件位置不对

3、配置的数据库信息有问题

4、版本依赖问题（mysql-connector-java这个依赖版本与自己的数据库版本一致性问题）

pom依赖

 <dependencies>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.47</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.22</version>
        </dependency>
        <dependency>
            <groupId>c3p0</groupId>
            <artifactId>c3p0</artifactId>
            <version>0.9.1.2</version>
        </dependency>
        <dependency>
            <groupId>com.mchange</groupId>
            <artifactId>mchange-commons-java</artifactId>
            <version>0.2.19</version>
        </dependency>
    </dependencies>

java代码

package com.test02;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.test01.pojos.Student;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Date;

/**
 * @author zhangzengxiu
 * @date 2022/8/30
 */
public class C3P0Test {
    public static void main(String[] args) throws Exception {
        //1.创建c3p0数据库连接池对象。空参构造会使用默认的配置
        DataSource dataSource = new ComboPooledDataSource();
        //2.获取数据库连接
        Connection connection = dataSource.getConnection();
        String sql="SELECT * FROM student";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        ResultSet resultSet = preparedStatement.executeQuery();
        ArrayList<Student> list = new ArrayList<Student>();
        //结果处理
        while (resultSet.next()) {
            Integer sid = resultSet.getInt("sid");
            String name = resultSet.getString("name");
            Integer age = resultSet.getInt("age");
            Date birthday = resultSet.getDate("birthday");
            Student student = new Student(sid, name, age, birthday);
            list.add(student);
        }
        for (Student student : list) {
            System.out.println(student);
        }
        resultSet.close();
        connection.close();
        preparedStatement.close();

    }
}

Druid连接池

pom文件

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>JDBCDemo</groupId>
    <artifactId>JDBCDemo</artifactId>
    <version>1.0-SNAPSHOT</version>

    <dependencies>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.47</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.22</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.22</version>
        </dependency>
    </dependencies>

</project>

配置文件

文件名自定义

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbc
username=root
password=root
# 初始化连接数量
initialSize=5
# 最大连接数量
maxActive=10
# 超时时间
maxWait=3000

java代码

package com.test03;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.test01.pojos.Student;

import javax.sql.DataSource;
import java.io.BufferedReader;
import java.io.FileInputStream;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Date;
import java.util.Properties;

/**
 * @author zhangzengxiu
 * @date 2022/8/31
 */
public class DruidTest {
    public static void main(String[] args) throws Exception {
        //加载配置文件
        Properties properties = new Properties();
        BufferedReader bufferedReader = new BufferedReader(new InputStreamReader(new FileInputStream("src/main/resources/druid.properties")));
        properties.load(bufferedReader);
        //通过Druid连接工厂获取数据库连接池对象
        DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
        //获取数据库连接
        Connection connection = dataSource.getConnection();
        String sql = "SELECT * FROM student";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        ResultSet resultSet = preparedStatement.executeQuery();
        ArrayList<Student> list = new ArrayList<Student>();
        //结果处理
        while (resultSet.next()) {
            Integer sid = resultSet.getInt("sid");
            String name = resultSet.getString("name");
            Integer age = resultSet.getInt("age");
            Date birthday = resultSet.getDate("birthday");
            Student student = new Student(sid, name, age, birthday);
            list.add(student);
        }
        for (Student student : list) {
            System.out.println(student);
        }

        resultSet.close();
        connection.close();
        preparedStatement.close();
        bufferedReader.close();

    }
}

Druid工具类

/*
    数据库数据源的工具类
 */
public class DataSourceUtils {
    //1.私有构造方法
    private DataSourceUtils() {
    }

    //2.声明数据源变量
    private static DataSource dataSource;

    //3.提供静态代码块，完成配置文件的加载和获取数据库连接池对象
    static {
        try {
            //完成配置文件的加载
            Properties properties = new Properties();
        BufferedReader bufferedReader = new BufferedReader(new InputStreamReader(new FileInputStream("src/main/resources/druid.properties")));
        properties.load(bufferedReader);

            //获取数据库连接池对象
            dataSource = DruidDataSourceFactory.createDataSource(prop);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //4.提供一个获取数据库连接的方法
    public static Connection getConnection() {
        Connection con = null;
        try {
            con = dataSource.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return con;
    }

    //5.提供一个获取数据库连接池对象的方法
    public static DataSource getDataSource() {
        return dataSource;
    }

    //6.释放资源
    public static void close(Connection con, Statement stat, ResultSet rs) {

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


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

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

    }

    public static void close(Connection con, Statement stat) {
        close(con, stat, null);
    }
}

JDBC框架

框架背景

定义必要的信息、获取数据库的连接、释放资源都是重复的代码，而我们最终的核心功能仅仅只是执行一条sql 语句，所以我们可以抽取出一个 JDBC 模板类，来封装一些方法(update、query)，专门帮我们执行增删改查的 sql 语句。将之前那些重复的操作，都抽取到模板类中的方法里，就能大大简化我们的使用步骤！

源信息

DataBaseMetaData ：数据库的源信息

java.sql.DataBaseMetaData 封装了整个数据库的综合信息。

例如：

String getDatabaseProductName()：获取数据库产品的名称

int getDatabaseProductVersion()：获取数据库产品的版本号

ParameterMetaData ：参数的源信息

java.sql.ParameterMetaData 封装的是预编译执行者对象中每个参数的类型和属性，这个对象可以通过预

编译执行者对象中的 getParameterMetaData() 方法来获取

核心功能： int getParameterCount() 用于获取 sql 语句中参数的个数

ResultSetMetaData：结果集的源信息

java.sql.ResultSetMetaData：封装的是结果集对象中列的类型和属性，这个对象可以通过结果集对象中的

getMetaData()方法来获取

核心功能：int getColumnCount() 用于获取列的总数，String getColumnName(int i) 用于获取列名

Java代码手写JDBC框架

package com.test04;


import com.alibaba.druid.util.StringUtils;
import com.test03.DataSourceUtils;
import com.test05.handler.ResultSetHandler;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.ParameterMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;


/**
 * @author zhangzengxiu
 * @date 2022/9/1
 */
public class JDBCTemplate {

    /**
     * 数据源
     */
    private DataSource dataSource;

    /**
     * 连接对象
     */
    private Connection connection;

    /**
     * 执行者对象
     */
    private PreparedStatement preparedStatement;

    /**
     * 结果集
     */
    private ResultSet resultSet;

    public JDBCTemplate(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    /**
     * 增删改类型操作
     *
     * @param sql     要执行的SQL语句
     * @param objects 参数，可变数组
     * @return
     */
    public int update(String sql, Object... objects) {
        int result = 0;
        try {
            if (StringUtils.isEmpty(sql)) {
                throw new RuntimeException("sql is null");
            }
            //获取连接
            connection = dataSource.getConnection();
            //获取预编译执行者对象
            preparedStatement = connection.prepareStatement(sql);
            //获取参数个数
            int parameterCount = preparedStatement.getParameterMetaData().getParameterCount();
            //判断参数个数与SQL的占位符个数是否匹配
            if (parameterCount != objects.length) {
                throw new RuntimeException("参数个数不匹配");
            }
            //为SQL占位符赋值
            for (int i = 0; i < objects.length; i++) {
                //索引从0开始，所以i+1是
                preparedStatement.setObject(i + 1, objects[i]);
            }
            //执行SQL
            result = preparedStatement.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //释放资源
            DataSourceUtils.close(connection, preparedStatement);
        }
        //返回结果
        return result;
    }

    /**
     * 查询方法：用于将聚合函数的查询结果进行返回
     *
     * @param sql
     * @param rsh
     * @param objs
     * @return
     */
    public Long queryForScalar(String sql, ResultSetHandler<Long> rsh, Object... objs) {
        Long value = null;

        try {
            //通过数据源获取一个数据库连接
            connection = dataSource.getConnection();

            //通过数据库连接对象获取执行者对象，并对sql语句进行预编译
            preparedStatement = connection.prepareStatement(sql);

            //通过执行者对象获取参数的源信息对象
            ParameterMetaData parameterMetaData = preparedStatement.getParameterMetaData();
            //通过参数源信息对象获取参数的个数
            int count = parameterMetaData.getParameterCount();

            //判断参数数量是否一致
            if (count != objs.length) {
                throw new RuntimeException("参数个数不匹配");
            }

            //为sql语句占位符赋值
            for (int i = 0; i < objs.length; i++) {
                preparedStatement.setObject(i + 1, objs[i]);
            }

            //执行sql语句并接收结果
            resultSet = preparedStatement.executeQuery();

            //通过ScalarHandler方式对结果进行处理
            value = rsh.handler(resultSet);

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //释放资源
            DataSourceUtils.close(connection, preparedStatement, resultSet);
        }

        //返回结果
        return value;
    }

    /**
     * 查询方法：用于将多条记录封装成自定义对象并添加到集合返回
     *
     * @param sql
     * @param rsh
     * @param objs
     * @param <T>
     * @return
     */
    public <T> List<T> queryForList(String sql, ResultSetHandler<T> rsh, Object... objs) {
        List<T> list = new ArrayList<>();

        try {
            //通过数据源获取一个数据库连接
            connection = dataSource.getConnection();
            //通过数据库连接对象获取执行者对象，并对sql语句进行预编译
            preparedStatement = connection.prepareStatement(sql);
            //通过执行者对象获取参数的源信息对象
            ParameterMetaData parameterMetaData = preparedStatement.getParameterMetaData();
            //通过参数源信息对象获取参数的个数
            int count = parameterMetaData.getParameterCount();
            //判断参数数量是否一致
            if (count != objs.length) {
                throw new RuntimeException("参数个数不匹配");
            }
            //为sql语句占位符赋值
            for (int i = 0; i < objs.length; i++) {
                preparedStatement.setObject(i + 1, objs[i]);
            }
            //执行sql语句并接收结果
            resultSet = preparedStatement.executeQuery();
            //通过BeanListHandler方式对结果进行处理
            list = rsh.handler(resultSet);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //释放资源
            DataSourceUtils.close(connection, preparedStatement, resultSet);
        }
        //返回结果
        return list;
    }

    /**
     * 查询方法：用于将一条记录封装成自定义对象并返回
     *
     * @param sql
     * @param rsh
     * @param objs
     * @param <T>
     * @return
     */
    public <T> T queryForObject(String sql, ResultSetHandler<T> rsh, Object... objs) {
        T obj = null;

        try {
            //通过数据源获取一个数据库连接
            connection = dataSource.getConnection();
            //通过数据库连接对象获取执行者对象，并对sql语句进行预编译
            preparedStatement = connection.prepareStatement(sql);
            //通过执行者对象获取参数的源信息对象
            ParameterMetaData parameterMetaData = preparedStatement.getParameterMetaData();
            //通过参数源信息对象获取参数的个数
            int count = parameterMetaData.getParameterCount();
            //判断参数数量是否一致
            if (count != objs.length) {
                throw new RuntimeException("参数个数不匹配");
            }
            //为sql语句占位符赋值
            for (int i = 0; i < objs.length; i++) {
                preparedStatement.setObject(i + 1, objs[i]);
            }
            //执行sql语句并接收结果
            resultSet = preparedStatement.executeQuery();
            //通过BeanHandler方式对结果进行处理
            obj = rsh.handler(resultSet);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //释放资源
            DataSourceUtils.close(connection, preparedStatement, resultSet);
        }
        //返回结果
        return obj;
    }

}

查询框架代码

package com.test05.handler;

import java.sql.ResultSet;
/*
    用于处理结果集方式的接口
 */
public interface ResultSetHandler<T> {
    <T> T handler(ResultSet rs);
}

package com.test05.handler;

import java.beans.PropertyDescriptor;
import java.lang.reflect.Method;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;

/*
    实现类1：用于将查询到的一条记录，封装为Student对象并返回
 */
//1.定义一个类，实现ResultSetHandler接口
public class BeanHandler<T> implements ResultSetHandler<T>{
    //2.定义Class对象类型变量
    private Class<T> beanClass;

    //3.通过有参构造为变量赋值
    public BeanHandler(Class<T> beanClass) {
        this.beanClass = beanClass;
    }

    //4.重写handler方法。用于将一条记录封装到自定义对象中
    @Override
    public T handler(ResultSet rs) {
        //5.声明自定义对象类型
        T bean = null;
        try {
            //6.创建传递参数的对象，为自定义对象赋值
            bean = beanClass.newInstance();
            //7.判断结果集中是否有数据
            if(rs.next()) {
                //8.通过结果集对象获取结果集源信息的对象
                ResultSetMetaData metaData = rs.getMetaData();
                //9.通过结果集源信息对象获取列数
                int count = metaData.getColumnCount();
                //10.通过循环遍历列数
                for(int i = 1; i <= count; i++) {
                    //11.通过结果集源信息对象获取列名
                    String columnName = metaData.getColumnName(i);
                    //12.通过列名获取该列的数据
                    Object value = rs.getObject(columnName);
                    //13.创建属性描述器对象，将获取到的值通过该对象的set方法进行赋值
                    PropertyDescriptor pd = new PropertyDescriptor(columnName.toLowerCase(),beanClass);
                    //获取set方法
                    Method writeMethod = pd.getWriteMethod();
                    //执行set方法，给成员变量赋值
                    writeMethod.invoke(bean,value);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        //14.返回封装好的对象
        return bean;
    }
}

package com.test05.handler;

import java.beans.PropertyDescriptor;
import java.lang.reflect.Method;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.List;

/*
    实现类2：用于将查询到的多条记录，封装为Student对象并添加到集合返回
 */
//1.定义一个类，实现ResultSetHandler接口
public class BeanListHandler<T> implements ResultSetHandler<T>{
    //2.定义Class对象类型变量
    private Class<T> beanClass;

    //3.通过有参构造为变量赋值
    public BeanListHandler(Class<T> beanClass) {
        this.beanClass = beanClass;
    }

    //4.重写handler方法。用于将多条记录封装到自定义对象中并添加到集合返回
    @Override
    public List<T> handler(ResultSet rs) {
        //5.声明集合对象类型
        List<T> list = new ArrayList<>();
        try {
            //6.判断结果集中是否有数据
            while(rs.next()) {
                //7.创建传递参数的对象，为自定义对象赋值
                T bean = beanClass.newInstance();
                //8.通过结果集对象获取结果集源信息的对象
                ResultSetMetaData metaData = rs.getMetaData();
                //9.通过结果集源信息对象获取列数
                int count = metaData.getColumnCount();
                //10.通过循环遍历列数
                for(int i = 1; i <= count; i++) {
                    //11.通过结果集源信息对象获取列名
                    String columnName = metaData.getColumnName(i);
                    //12.通过列名获取该列的数据
                    Object value = rs.getObject(columnName);
                    //13.创建属性描述器对象，将获取到的值通过该对象的set方法进行赋值
                    PropertyDescriptor pd = new PropertyDescriptor(columnName.toLowerCase(),beanClass);
                    //获取set方法
                    Method writeMethod = pd.getWriteMethod();
                    //执行set方法，给成员变量赋值
                    writeMethod.invoke(bean,value);
                }
                //将对象保存到集合中
                list.add(bean);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        //14.返回封装好的对象
        return list;
    }
}

package com.test05.handler;

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;

/*
    1.定义一个类，实现ResultSetHandler接口
    2.重写handler方法
    3.定义一个Long类型变量
    4.判断结果集对象中是否还有数据
    5.获取结果集源信息的对象
    6.获取第一列的列名
    7.根据列名获取该列的值
    8.返回结果
 */
//1.定义一个类，实现ResultSetHandler接口
public class ScalarHandler<T> implements ResultSetHandler<T> {

    //2.重写handler方法
    @Override
    public Long handler(ResultSet rs) {
        //3.定义一个Long类型变量
        Long value = null;
        try{
            //4.判断结果集对象中是否还有数据
            if(rs.next()) {
                //5.获取结果集源信息的对象
                ResultSetMetaData metaData = rs.getMetaData();
                //6.获取第一列的列名
                String columnName = metaData.getColumnName(1);
                //7.根据列名获取该列的值
               value = rs.getLong(columnName);
            }
        }catch (Exception e) {
            e.printStackTrace();
        }
        //8.返回结果
        return value;
    }
}

测试代码

package com.test04;

import com.test03.DataSourceUtils;
import org.junit.Test;

/**
 * @author zhangzengxiu
 * @date 2022/9/1
 */
public class JDBCTemplateTest01 {

    private JDBCTemplate jdbcTemplate = new JDBCTemplate(DataSourceUtils.getDataSource());

    /**
     * 测试添加
     */
    @Test
    public void insertTest() {
        String sql = "INSERT INTO student (name,age,birthday) VALUES(?,?,?)";
        Object[] objects = {"杨十", 30, "1997-07-07"};
        int res = jdbcTemplate.update(sql, objects);
        if (res != 0) {
            System.out.println("添加成功");
        } else {
            System.out.println("添加失败");
        }
    }

    /**
     * 测试更新
     */
    @Test
    public void updateTest() {
        String sql = "UPDATE student SET age=? WHERE name = ?";
        Object[] objects = {37, "杨十"};
        int res = jdbcTemplate.update(sql, objects);
        System.out.println(res);
    }

    /**
     * 测试删除
     */
    @Test
    public void deleteTest() {
        String sql = "DELETE FROM student WHERE name = ?";
        Object[] objects = {"杨十"};
        int res = jdbcTemplate.update(sql, objects);
        System.out.println(res);
    }

      @Test
    public void queryForScalar() {
        //查询聚合函数的测试
        String sql = "SELECT COUNT(*) FROM student";
        Long value = template.queryForScalar(sql,new ScalarHandler<Long>());
        System.out.println(value);
    }

    @Test
    public void queryForList() {
        //查询所有学生信息的测试
        String sql = "SELECT * FROM student";
        List<Student> list = template.queryForList(sql, new BeanListHandler<>(Student.class));
        for(Student stu : list) {
            System.out.println(stu);
        }
    }

}