JDBC 连接数据库

阿里云国内75折回扣微信号：monov8

阿里云国际，腾讯云国际，低至75折。AWS 93折免费开户实名账号代冲值优惠多多微信号：monov8 飞机：@monov6

JDBCJava DataBase Connectivity简单来讲JDBC是利用Java语言或程序连接并且访问数据库的一门技术是Java语言中用来规范客户端程序如何访问数据库的应用程序接口提供了查询和更新数据库操作方法通常是面向关系型数据库的。

步骤

注册驱动
获取连接
获取传输器
通过传输器发送SQL到服务器质性并且返回执行结果
数据处理
释放资源

1. 快速开始

数据准备

create database testjdbc_db charset utf8;
use testjdbc_db;
create table account(
    id int primary key auto_increment,
    name varchar(50),
    money double
);
insert into account values(null, 'tom', 1000);
insert into account values(null, 'andy', 1000);
insert into account values(null, 'tony', 1000);

mysql 驱动包下载

创建 lib 目录将 jar 包拷贝到里面然后将整个 lib 目录 Add as Library

示例

package com.hubery.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.SQLException;

public class ConnectionBasic {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1.注册驱动
        Class.forName("com.mysql.cj.jdbc.Driver");

        //2.获取连接
        Connection conn = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/testjdbc_db?characterEncoding=UTF-8&serverTimezone=GMT%2B8&useServerPrepStms=true&cachePrepStms=true",
                "xxx",
                "xxx"
        );

        //3.获取传输器
        Statement st = conn.createStatement();

        //4.通过传输器发送SQL到服务器执行并且返回执行结果
        String sql = "select * from account";
        ResultSet rs = st.executeQuery(sql);

        //5.数据处理
        while (rs.next()) {
            int id = rs.getInt("id");
            String name = rs.getString("name");
            double money = rs.getDouble("money");
            System.out.println(id + ":" + name + ":" + money);
        }

        //6.释放资源
        if (rs != null) {
            try {
                rs.close();
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                rs = null;
            }
        }
        if (st != null) {
            try {
                st.close();
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                st = null;
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                conn = null;
            }
        }
    }
}

1、注册驱动

MySQL版本在8.0之前的驱动的全限定类名是 com.mysql.jdbc.Driver
MySQL版本在8.0之后的驱动的全限定类名是 com.mydql.cj.jdbc.Driver

2、连接

Connection conn = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/testjdbc_db?characterEncoding=UTF-8&serverTimezone=GMT%2B8&useServerPrepStms=true&cachePrepStms=true&useSSL=false",
                "xxx",
                "xx"
        )
// 协议名称
jdbc:mysql

// mysql 主机和端口
localhost:3306

// 数据库名称
testjdbc_db

// 设置编码和时区mysql8 后需要自己设置时区
characterEncoding=UTF-8&serverTimezone=GMT%2B

// 保证 mysql 驱动把连接器 PreparedStaement 类型传输来的 SQL 语句发送给数据库进行预编译为函数交给服务器存储 key
useServerPrepStms=true

// 保证在执行相同 SQL 语句时数据库不会二次编译
cachePrepStms=true

// mysql 用户名及密码
root
root

3、发送 SQL 语句即处理结果集

st.executeQuery(sql)执行查询类型的 sql 语句返回 ResultSet 结果集
st.executeUpdate(sql)执行更新添加、删除、修改类型的 sql 语句返回 int 值表示影响的记录数

ResultSet 结果集使用 next() 方法可以判断是否还有下一个获取值可用以下方法

// 不同类型用不同方法获取值

while (rs.next()) {
    getInt(int columnIndex)
    getInt(String columnLable)
    getString(int columnIndex)
    getString(String columnLable)
    getDouble(int columnIndex)
    getDouble(String columnLable)
    getObject(int columnIndex)
    getObject(String columnLable)
}

4、释放资源

遵循的规则越晚获取的资源越先关闭

2. 数据库注册工具类

可以将上述连接数据库、关闭资源这一系列步骤封装成工具类直接调用即可简化操作

1、JDBCUtils.java

package com.hubery.jdbc.utils;

import java.sql.*;

/**
 * 注册驱动 + 获取连接
 */
public class JDBCUtils {
    public static Connection getConnection() {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.获取连接
            Connection conn = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/testjdbc_db?characterEncoding=UTF-8&serverTimezone=GMT%2B8&useServerPrepStms=true&cachePrepStms=true",
                    "xxx",
                    "xxx"
            );
            return conn;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    public static Connection getConnection(String dbName, String username, String password) {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.获取连接
            Connection conn = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/" + dbName + "?characterEncoding=UTF-8&useSSL=false&serverTimezone=GMT%2B8&useServerPrepStms=true&cachePrepStms=true",
                    username,
                    password
            );
            return conn;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 关闭连接
     *
     * @param conn
     * @param statement
     * @param resultSet
     */
    public static void close(Connection conn, Statement statement, ResultSet resultSet) {
        if (conn != null) {
            try {
                conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                conn = null;
            }
        }

        if (statement != null) {
            try {
                statement.close();
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                statement = null;
            }
        }

        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                resultSet = null;
            }
        }
    }

}

2、测试类 testJdbcUtil.java

import com.hubery.jdbc.utils.JDBCUtils;
import org.junit.Test;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

public class testJdbcUtil {

    /**
     * 查询所有数据
     */
    @Test
    public void testSelect() {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            connection = JDBCUtils.getConnection(); // 注册驱动、获取连接
            statement = connection.createStatement();

            // 通过传输器将 SQL 语句发送过去
            String sql = "select * from account";

            resultSet = statement.executeQuery(sql);

            // 处理结果
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                double money = resultSet.getDouble("money");
                System.out.println("id: " + id + ", name: " + name + ", money: " + money);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 关闭连接释放资源
            JDBCUtils.close(connection, statement, resultSet);
        }
    }

    /**
     * 修改、删除、插入数据
     */
    @Test
    public void testUpdate() {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            connection = JDBCUtils.getConnection(); // 注册驱动、获取连接
            statement = connection.createStatement();

            // 通过传输器将 SQL 语句发送过去
            String sql = "update account set money = 4800 where name = 'tony'";
            // 删除数据
//            String sql = "delete from account where name = 'tom'";
            // 插入数据
//            String sql = "insert into account values(null, 'Anny', 40000) ";

            int rows = statement.executeUpdate(sql);

            // 处理结果
            System.out.println("更新完毕影响行数" + rows);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 关闭连接释放资源
            JDBCUtils.close(connection, statement, resultSet);
        }
    }
}

3. SQL 注入

3.1 Statement 和 PreparedStatement 区别

PreparedStatement 为 Statement 子接口比之更安全效率更高

Statement每次都需要将语句发送给数据库编译正确则执行否则抛出异常每次都是一次新的操作参数以拼接形式线程不安全
PreparedStatement线程安全先将语句发送给数据库预编译错误则抛出异常正确则生成骨架函数用户只需传递参数即可

3.2 SQL 注入

通过拼接的方式来传递一些 SQL 语句需要的参数时有时候可能会因为一些特殊的符号导致 SQL 语句语义发生变化导致一些有问题的语句也能成功执行比如下面几个示例中不需要用户名或密码也能登录成功

stat = conn.createStatement();
String sql = "select * from user where username='"+ user + "'and password = '"+ password +"'";
resultSet = stat.executeQuery(sql);

// 正常 SQL 语句
select * from user where username = 'rose' and password = '123';

// 用户名输入 rose '#'密码为空
select * from user where username = 'rose '#'' and password = '';

// 用户名输入 rose 'or' 1=1密码为空
select * from user where username = 'rose 'or' 1=1' and password = '';

// 用户名为空密码为 or' 1=1
select * from user where username = '' and password = ''or' 1=1';

解决办法

使用 PreparedStatement 对象替换 Statement对传递的参数进行校验

String user = "rose";
String password = "123456";

//1.注册驱动2.获取连接
conn = JdbcUtil.getConnection();

//3.获取传输器通过传输器发送SQL语句到服务器执行并返回结果
String sql = "select * from user where username = ? and password = ?";

ps = conn.prepareStatement(sql);

//4.设置SQL语句中的参数其中 1/2 表示 sql 语句中 ? 的位置
ps.setString(1, user);
ps.setString(2, password);

//5.执行SQL语句
rs = ps.executeQuery();

4. 连接池

在上面我们每次操作数据库都需要创建连接、初始化连接、关闭连接这些操作都比较耗时效率低下要解决这种问题可以使用连接池连接池可以一次性创建多个连接当 close 后就把连接放回池子中而不是直接关闭这样就避免了频繁地创建、初始化、关闭连接。

获取连接方式

将配置写死在静态代码中不推荐不够灵活
通过配置文件获取连接c3p0.properties
通过配置文件获取连接c3p0-config.xml

1、src/c3p0-config.xml

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
    <default-config>
        <property name="driverClass">
            com.mysql.cj.jdbc.Driver
        </property>
        <property name="jdbcUrl">
            jdbc:mysql:///testjdbc_db?characterEncoding=UTF-8&amp;serverTimezone=GMT%2B8&amp;useServerPrepStms=true&amp;cachePrepStms=true
        </property>
        <property name="user">
            xxx
        </property>
        <property name="password">
            xxx
        </property>
    </default-config>
</c3p0-config>

2、src/c3p0.properties

#key=value
c3p0.driverClass=com.mysql.cj.jdbc.Driver
c3p0.jdbcUrl=jdbc:mysql:///testjdbc_db?characterEncoding=UTF-8&serverTimezone=GMT%2B8&useServerPrepStms=true&cachePrepStms=true
c3p0.user=xxx
c3p0.password=xxx

3、工具类JdbcPoolUtil.java

package com.hubery.jdbc.utils;

import com.mchange.v2.c3p0.ComboPooledDataSource;

import java.sql.*;

public class JdbcPoolUtil {
    // 创建连接池对象
    static ComboPooledDataSource pool = new ComboPooledDataSource();

//    // 设置连接池信息方式一
//    static {
//        try {
//            pool.setDriverClass("com.mysql.cj.jdbc.Driver");
//            pool.setJdbcUrl("jdbc:mysql://localhost:3306/testjdbc_db?characterEncoding=UTF-8&serverTimezone=GMT%2B8&useServerPrepStms=true&cachePrepStms=true");
//            pool.setUser("root");
//            pool.setPassword("root");
//        } catch (Exception e) {
//            e.printStackTrace();
//        }
//    }

    // 获取连接配置文件方式
    public static Connection getConnection() throws SQLException {

        return pool.getConnection();
    }

    public static void close(Connection conn, Statement statement, ResultSet resultSet) {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                resultSet = null;
            }
        }

        if (statement != null) {
            try {
                statement.close();
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                statement = null;
            }
        }

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


    }
}

4、使用 TestJdbcPoolDemo.java

import com.hubery.jdbc.utils.JdbcPoolUtil;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.junit.Test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class TestJdbcPoolDemo {

    // 查询所有数据
    @Test
    public void testFindAll() throws SQLException {
        // 初始化连接对象
        Connection connection = JdbcPoolUtil.getConnection();

        // 编写 SQL获取连接器
        String sql = "select * from account";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        // 查询
        ResultSet resultSet = preparedStatement.executeQuery();

        // 处理结果
        while (resultSet.next()) {
            int id = resultSet.getInt("id");
            String name = resultSet.getString("name");
            double money = resultSet.getDouble("money");
            System.out.println(id + ":" + name + ":" + money);
        }

        // 关闭连接
        JdbcPoolUtil.close(connection, preparedStatement, resultSet);

    }
}

以上使用的是 C3P0 连接池速度较慢但稳定性不错更多选择可参考JDBC连接池

静态代码封装读取 properties 配置文件

1、JdbcPoolUtil.java 工具类

package com.hubery.jdbc.utils;

import com.mchange.v2.c3p0.ComboPooledDataSource;

import java.beans.PropertyVetoException;
import java.io.FileReader;
import java.io.IOException;
import java.net.URL;
import java.sql.*;
import java.util.Properties;

public class JdbcPoolUtil {
    // 创建连接池对象
    static ComboPooledDataSource pool = new ComboPooledDataSource();

    // 文件读取采用静态代码读取只需要读取一次
    static {
        try {
            // 创建 Properties 集合
            Properties properties = new Properties();

            // 获取 src 下文件的文件方式ClassLoader 类加载器
            ClassLoader classLoader = JdbcPoolUtil.class.getClassLoader();
            URL res = classLoader.getResource("jdbc.properties");

            String path = res.getPath();

            // 加载文件
            properties.load(new FileReader(path));

            // 获取值
            String url = properties.getProperty("url");
            String user = properties.getProperty("user");
            String password = properties.getProperty("password");
            String driver = properties.getProperty("driver");

            System.out.println("url " + url + ", user" + user + ", password: " + password + ", driver: " + driver);
            // 给数据源设置相关参数
            pool.setDriverClass(driver);
            pool.setJdbcUrl(url);
            pool.setUser(user);
            pool.setPassword(password);

            // 初始化连接数
            pool.setInitialPoolSize(10);
            pool.setMaxPoolSize(50);    // 最大连接数
            System.out.println("连接成功");
        } catch (IOException | PropertyVetoException e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection() throws SQLException {


        return pool.getConnection();
    }

    public static void close(Connection conn, Statement statement, ResultSet resultSet) {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                resultSet = null;
            }
        }

        if (statement != null) {
            try {
                statement.close();
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                statement = null;
            }
        }

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


    }
}

2、jdbc.properties

driver=com.mysql.cj.jdbc.Driver
user=root
password=root
url=jdbc:mysql:///testjdbc_db?characterEncoding=UTF-8&amp;serverTimezone=GMT%2B8&amp;useServerPrepStms=true&amp;cachePrepStms=true