【2021软件创新实验室暑假集训】JDBC（原理、使用以及实现简单的数据库连接池）-阿里云开发者社区

一、JDBC的前世今生

JDBC全称Java DataBase Connectivity（Java数据库连接），是Java语言中用来规范客户端程序如何来访问数据库的应用程序接口，提供了诸如查询和更新数据库中数据的方法。

早期SUN公司的天才们想编写一套可以连接天下所有数据库的API，但是当他们刚刚开始时就发现这是不可完成的任务，因为各个厂商的数据库服务器差异太大了。后来SUN开始与数据库厂商们讨论，最终得出的结论是，由SUN提供一套访问数据库的规范（就是一组接口），并提供连接数据库的协议标准，然后各个数据库厂商会遵循SUN的规范提供一套访问自己公司的数据库服务器的API出现。SUN提供的规范命名为JDBC，而各个厂商提供的，遵循了JDBC规范的，可以访问自己数据库的API被称之为驱动！

很多时候我们往往错误的认识了JDBC，以为它是用于数据库连接的框架。其实不然，它只是sun公司为了规范操作，屏蔽底层数据库之间的差异而定义的一套标准，它位于java.sql包下面。

各个数据库厂商自己编写相关的驱动包来实现这套标准。因为规范了接口，所以Java工程师们可以不用关心数据库层面的差异，利用统一的jdbc操作数据库即可。

二、JDBC的使用及原理

1.jdbc简单使用步骤

以下操作以mysql为例

①加载驱动程序

//加载MySql驱动
Class.forName("com.mysql.cj.jdbc.Driver")

或者也可以这么写

driver = new com.mysql.cj.jdbc.Driver();
DriverManager.registerDriver(driver);

其实上面的操作本质是一样，

看一看源码，Class.forName("com.mysql.cj.jdbc.Driver")这句话其实就是把就是把这个类加载到内存中，加载的过程中会执行static块里的代码，而该代码实际上和后面一种是一样的。

②获得数据库连接

根据数据库路径、账号、密码获取数据库连接

DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/imooc", "root", "root");

③创建Statement\PreparedStatement对象

Statement statement=conn.createStatement();
PreparedStatement ps=conn.prepareStatement(sql);

④调用executeQuery/executeUpdate方法

//调用Statement的executeQuery方法
ResultSet rs = stmt.executeQuery(sql);

//调用Statement的executeUpdate方法
Integer i=statement.executeUpdate(sql);

//调用PreparedStatement的executeQuery方法
ResultSet rs=ps.executeQuery();

//调用PreparedStatement的executeUpdate方法
Integer i=ps.executeUpdate(sql);

注：这里省略了业务处理流程

⑤关闭ResultSet、Statement/PreparedStatement、Connection

这里一定要注意关闭顺序，应该以ResultSet、Statement/PreparedStatement、Connection的顺序（先开后关）

同时捕获异常时最后每个都单独捕获，这样不至于在关闭时因为前面关闭出错而导致后面资源没关闭。

try {
            ....
} catch (SQLException throwables) {
    throwables.printStackTrace();
}finally {
    try {
        rs.close();
    } catch (SQLException throwables) {
        throwables.printStackTrace();
    }
    try {
        statement.close();
    } catch (SQLException throwables) {
        throwables.printStackTrace();
    }
    try {
        connection.close();
    } catch (SQLException throwables) {
        throwables.printStackTrace();
    }
}

2.简单的例子

//数据库路径
private static String url="jdbc:mysql://localhost:3306/store?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8&useSSL=false&allowPublicKeyRetrieval=true";
//数据库账号
private static String user="root";
//数据库密码
private static String password="jinhaolin";
void testQueryStatement(){
Driver driver= null;
Statement statement=null;
Connection connection=null;
ResultSet rs=null;
try {
    //创建驱动类对象
    driver = new com.mysql.cj.jdbc.Driver();
    //注册驱动类
    DriverManager.registerDriver(driver);
    //获取连接
    connection=DriverManager.getConnection(url,user,password);
    //获取Statement
    statement=connection.createStatement();
    //sql语句
    String sql="select * from user";
    //执行查询，获得结果集
    rs=statement.executeQuery(sql);
    //调用next方法，将指针指向下一条记录，一开始调用next方法后，指针指向第一条记录。next返回值为Boolean类型，
    // 表示是否还有下一条记录
    while (rs.next()){
        System.out.println("-----------------------------");
        //获取当条记录的userName字段并打印
        System.out.println("username"+rs.getString("userName"));
        //获取当条记录的password字段并打印
        System.out.println("password"+rs.getString("password"));
        System.out.println("-----------------------------");
    }
} catch (SQLException throwables) {
    throwables.printStackTrace();
}finally {
    try {
        rs.close();
    } catch (SQLException throwables) {
        throwables.printStackTrace();
    }
    try {
        statement.close();
    } catch (SQLException throwables) {
        throwables.printStackTrace();
    }
    try {
        connection.close();
    } catch (SQLException throwables) {
        throwables.printStackTrace();
    }
}

3.Statement/PreparedStatement使用

①Statement

对于Statement，一般都是写好sql语句或者自己拼接好对应的sql语句去执行（自己拼接的话要注意格式，比如字符串得加上‘’，日期格式得以’yyyy-MM-dd HH:mm:ss’形式才行等等）。

一般来讲查询语句调用ResultSet executeQuery(String sql)，增删改查都是调用int executeUpdate(String sql)

查询操作

ResultSet executeQuery(String sql)方法会返回一个结果集，结果集的读取调用，模板如下：

//rs中有多条记录，其内置一个指针，每调用一次next方法就会跳转到下一条记录，初始指针指向空。
while (rs.next()){
    //用getXXX(字段名称)的方式获取当前指针指向的记录字段
          int id=rs.getInt("id");
          //....

以下是代码实例

private static String url="jdbc:mysql://localhost:3306/store?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8&useSSL=false&allowPublicKeyRetrieval=true";
private static String user="root";
private static String password="jinhaolin";
void testQueryStatement(){
    Driver driver= null;
    Statement statement=null;
    Connection connection=null;
    ResultSet rs=null;
    try {
        //创建驱动类对象
        driver = new com.mysql.cj.jdbc.Driver();
        //注册驱动类
        DriverManager.registerDriver(driver);
        //获取连接
        connection=DriverManager.getConnection(url,user,password);
        //获取Statement
        statement=connection.createStatement();
        //sql语句
        String sql="select * from user";
        //执行查询，获得结果集
        rs=statement.executeQuery(sql);
        //调用next方法，将指针指向下一条记录，一开始调用next方法后，指针指向第一条记录。next返回值为Boolean类型，
        // 表示是否还有下一条记录
        while (rs.next()){
            System.out.println("-----------------------------");
            //获取当条记录的userName字段并打印
            System.out.println("username"+rs.getString("userName"));
            //获取当条记录的password字段并打印
            System.out.println("password"+rs.getString("password"));
            System.out.println("-----------------------------");
        }
    } catch (SQLException throwables) {
        throwables.printStackTrace();
    }finally {
        try {
            rs.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        try {
            statement.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        try {
            connection.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
}

增删改操作

关于增删改一般调用executeUpdate(String sql)`即可，其返回值为当次操作受影响的记录行数。

void testUpdateStatement(){
     Driver driver= null;
     Statement statement=null;
     Connection connection=null;
     try {
         driver = new com.mysql.cj.jdbc.Driver();
         DriverManager.registerDriver(driver);
         connection=DriverManager.getConnection(url,user,password);
         statement=connection.createStatement();
         String sql="update user set userName='李四' where id=2";
         Integer i=statement.executeUpdate(sql);
         System.out.println("当前受影响的记录数："+i);
     } catch (SQLException throwables) {
         throwables.printStackTrace();
     }finally {
         try {
             statement.close();
         } catch (SQLException throwables) {
             throwables.printStackTrace();
         }
         try {
             connection.close();
         } catch (SQLException throwables) {
             throwables.printStackTrace();
         }
     }
 }

②PreparedStatement

PreparedStatement和Statement操作类似，不过PreparedStatement在创建时就需要传入对应的sql语句，这是为了“预编译”，同时sql语句支持占位符的方式（占位符序号从1开始）。

PreparedStatement操作有两个好处：

1.添加参数时不用操心类型转化

我们自己拼接字符串时总要为参数类型而操心，比如字符串要加’’，日期要改成合适格式。而PreparedStatement会帮我们做了这些事情。

2.防止sql注入（这个后面会讲）

查询操作

void testQueryPreparedStatement(){
    Driver driver= null;
    Connection connection=null;
    PreparedStatement ps=null;
    ResultSet rs=null;
    try {
        driver = new com.mysql.cj.jdbc.Driver();
        DriverManager.registerDriver(driver);
        connection=DriverManager.getConnection(url,user,password);
        String sql="select * from user where id=? or id=?";
        ps=connection.prepareStatement(sql);
        //传参
        ps.setInt(1,1);
        ps.setInt(2,2);
        rs=ps.executeQuery();
        while (rs.next()){
            System.out.println("-----------------------------");
            System.out.println("username"+rs.getString("userName"));
            System.out.println("password"+rs.getString("password"));
            System.out.println("-----------------------------");
        }
    } catch (SQLException throwables) {
        throwables.printStackTrace();
    }finally {
        try {
            rs.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        try {
            ps.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        try {
            connection.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
}

增删改操作

void testUpdatePreparedStatement(){
     Driver driver= null;
     Connection connection=null;
     PreparedStatement ps=null;
     try {
         driver = new com.mysql.cj.jdbc.Driver();
         DriverManager.registerDriver(driver);
         connection=DriverManager.getConnection(url,user,password);
         String sql="update user set userName='李四' where id=?";
         ps=connection.prepareStatement(sql);
         ps.setInt(1,1);
         Integer i=ps.executeUpdate(sql);
         System.out.println("当前受影响的记录数："+i);
     } catch (SQLException throwables) {
         throwables.printStackTrace();
     }finally {
         try {
             ps.close();
         } catch (SQLException throwables) {
             throwables.printStackTrace();
         }
         try {
             connection.close();
         } catch (SQLException throwables) {
             throwables.printStackTrace();
         }
     }
 }

4.事务的使用

有时候我们希望有些操作要么一起执行，要么一起失败。比如转账业务，需要在转账方账户扣除相应的资金，在转入方增加相应的资金，不能说一方成功了，一方失败了，这是不被允许。

所以我们需要有一种机制能保证某几个操作能一起成功或者一起失败。这就叫事务机制。

数据库事务(transaction)是访问并可能操作各种数据项的一个数据库操作序列，这些操作要么全部执行,要么全部不执行，是一个不可分割的工作单位。事务由事务开始与事务结束之间执行的全部数据库操作组成。

事务使用示例：

void testTransaction(){
        Driver driver= null;
        Connection connection=null;
        PreparedStatement ps1=null,ps2=null;
        try {
            driver = new com.mysql.cj.jdbc.Driver();
            DriverManager.registerDriver(driver);
            connection=DriverManager.getConnection(url,user,password);
            //将自动提交设置为false
           connection.setAutoCommit(false);
            String sql="update user set userName='王五' where id=?";
            ps1=connection.prepareStatement(sql);
            ps1.setInt(1,1);
            ps1.executeUpdate();
            ps2=connection.prepareStatement(sql);
            ps2.setInt(1,2);
            ps2.executeUpdate();
            //所有操作完成后提交事务
            connection.commit();
        } catch (SQLException throwables) {
            //打印堆栈信息
            throwables.printStackTrace();
            try {
                //回滚事务
                connection.rollback();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }finally {
            try {
                ps1.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            try {
                ps2.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

三、PreparedStatements原理

1.Sql注入

什么是sql注入？

SQL注入即是指web应用程序对用户输入数据的合法性没有判断或过滤不严，攻击者可以在web应用程序中事先定义好的查询语句的结尾上添加额外的SQL语句，在管理员不知情的情况下实现非法操作，以此来实现欺骗数据库服务器执行非授权的任意查询，从而进一步得到相应的数据信息。

举个例子，比如我后端有这么一句sql语句

delete form user where id=XXX

其中XXX是前端传过来的数据，正常情况下前端传过来需要删除的id，后端就会删除相应的用户记录。

但是，总有那么些人盯着你的系统想搞些破坏，他们在前端输入字符串"1 or 1=1",查这么一看你是不是觉得很奇怪，但当你将它拼入你的sql语句中时，你会发现语句变成了delete form user where id=1 or 1=1，这时你恍然大悟，这不就是删除所有用户了吗？一拍脑门，可发现为时已晚，数据库user表被删的一干二净（虽然一般可以用备份恢复）。

2.为什么PreparedStatement能防止sql注入？

为什么PreparedStatement能防止sql注入呢？

查看源码，我们可以在ClientPreparedQueryBindings这个类中发现缘由

@Override
    public void setString(int parameterIndex, String x) {
        if (x == null) {
            setNull(parameterIndex);
        } else {
            int stringLength = x.length();
            if (this.session.getServerSession().isNoBackslashEscapesSet()) {
                // Scan for any nasty chars
                boolean needsHexEscape = isEscapeNeededForString(x, stringLength);
                if (!needsHexEscape) {
                    StringBuilder quotedString = new StringBuilder(x.length() + 2);
                    quotedString.append('\'');
                    quotedString.append(x);
                    quotedString.append('\'');
                    byte[] parameterAsBytes = this.isLoadDataQuery ? StringUtils.getBytes(quotedString.toString())
                            : StringUtils.getBytes(quotedString.toString(), this.charEncoding);
                    setValue(parameterIndex, parameterAsBytes, MysqlType.VARCHAR);
                } else {
                    byte[] parameterAsBytes = this.isLoadDataQuery ? StringUtils.getBytes(x) : StringUtils.getBytes(x, this.charEncoding);
                    setBytes(parameterIndex, parameterAsBytes);
                }
                return;
            }
            String parameterAsString = x;
            boolean needsQuoted = true;
            if (this.isLoadDataQuery || isEscapeNeededForString(x, stringLength)) {
                needsQuoted = false; // saves an allocation later
                StringBuilder buf = new StringBuilder((int) (x.length() * 1.1));
                buf.append('\'');
                //
                // Note: buf.append(char) is _faster_ than appending in blocks, because the block append requires a System.arraycopy().... go figure...
                //
                for (int i = 0; i < stringLength; ++i) {
                    char c = x.charAt(i);
                    switch (c) {
                        case 0: /* Must be escaped for 'mysql' */
                            buf.append('\\');
                            buf.append('0');
                            break;
                        case '\n': /* Must be escaped for logs */
                            buf.append('\\');
                            buf.append('n');
                            break;
                        case '\r':
                            buf.append('\\');
                            buf.append('r');
                            break;
                        case '\\':
                            buf.append('\\');
                            buf.append('\\');
                            break;
                        case '\'':
                            buf.append('\'');
                            buf.append('\'');
                            break;
                        case '"': /* Better safe than sorry */
                            if (this.session.getServerSession().useAnsiQuotedIdentifiers()) {
                                buf.append('\\');
                            }
                            buf.append('"');
                            break;
                        case '\032': /* This gives problems on Win32 */
                            buf.append('\\');
                            buf.append('Z');
                            break;
                        case '\u00a5':
                        case '\u20a9':
                            // escape characters interpreted as backslash by mysql
                            if (this.charsetEncoder != null) {
                                CharBuffer cbuf = CharBuffer.allocate(1);
                                ByteBuffer bbuf = ByteBuffer.allocate(1);
                                cbuf.put(c);
                                cbuf.position(0);
                                this.charsetEncoder.encode(cbuf, bbuf, true);
                                if (bbuf.get(0) == '\\') {
                                    buf.append('\\');
                                }
                            }
                            buf.append(c);
                            break;
                        default:
                            buf.append(c);
                    }
                }
                buf.append('\'');
                parameterAsString = buf.toString();
            }
            byte[] parameterAsBytes = this.isLoadDataQuery ? StringUtils.getBytes(parameterAsString)
                    : (needsQuoted ? StringUtils.getBytesWrapped(parameterAsString, '\'', '\'', this.charEncoding)
                            : StringUtils.getBytes(parameterAsString, this.charEncoding));
            setValue(parameterIndex, parameterAsBytes, MysqlType.VARCHAR);
        }
    }

其实mysql驱动包里的操作便是将特定字符进行转义，防止sql注入的情况

三、实现一个简单的数据库连接池

虽然我们强调数据库操作需要在最后关闭数据库连接，但是在大多数情况下，频繁的开关数据库连接并不是一个明智的选择。

我们要明白——建立和关闭数据库连接是一个非常耗时的操作，如果我们仅仅为了增删改查一点数据就建立/关闭一次连接，这是一种非常大的浪费。

为了避免这种情况，我们就得对连接进行复用，而复用的方法之一便是编写一个数据库连接池，当然有很多开源的数据库连接池，比如阿里的druid。不过我们这里自己编写一个数据库连接池。

具体代码如下：

import java.sql.*;
import java.util.LinkedList;
import java.util.Queue;
public class JDBCUtil {
    private static Queue<Connection> pool=new LinkedList<>();
    private static Driver driver;
    //在加载这个类时会自动执行static代码块的代码，这块代码只会在加载这个类的时候执行（即只会执行一次）
    static {
        try {
            driver=new com.mysql.cj.jdbc.Driver();
            //注册驱动类
            DriverManager.registerDriver(driver);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
    private static String url="jdbc:mysql://localhost:3306/store?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8&useSSL=false&allowPublicKeyRetrieval=true";
    private static String user="root";
    private static String password="jinhaolin";
    /**
     * 获取一个数据库连接池
     * @return 数据库连接
     */
    public static synchronized Connection getConnection(){
        if (pool.size()==0){
            try {
                pool.add(DriverManager.getConnection(url,user,password));
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return pool.poll();
    }
    /**
     * 将用完的连接放回连接池
     * @param connection 要释放的连接
     */
    public static synchronized void release(Connection connection){
        pool.add(connection);
    }
}

以上只是一个粗浅的实现，是为了帮助大家理解数据库连接池的功能，上述实现只有最基础的功能。

为了避免线程安全问题，我在获取和释放连接的方法前加了synchronized 修饰。

总结

JDBC是sun公司为了方便Java操作各种数据库而制定的接口协议。各个数据库厂商如果要支持jdbc则需要实现jdbc制定的接口，提供相应的驱动包。

JDBC的使用很简单，但是繁琐，而且很多情况下我们需要重复性的劳动以实现数据库的操作。

在今后的学习中，你应该会遇到一些好用的框架（比如mybatis）来减少这些繁琐的操作，甚至有些自动化的工具可以一键生成相关代码（因为太有规律了）。

但是不要忘记这些Java持久层框架是基于jdbc的，所以了解其原理，掌握其使用，这对于一名Java后端开发工程师来说是一门必修课。

【2021软件创新实验室暑假集训】JDBC（原理、使用以及实现简单的数据库连接池）

一、JDBC的前世今生

二、JDBC的使用及原理

1.jdbc简单使用步骤

2.简单的例子

3.Statement/PreparedStatement使用

4.事务的使用

三、PreparedStatements原理

1.Sql注入

2.为什么PreparedStatement能防止sql注入？

三、实现一个简单的数据库连接池

总结

热门文章

最新文章

相关课程

相关电子书

相关实验场景

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

【2021软件创新实验室暑假集训】JDBC（原理、使用以及实现简单的数据库连接池）

一、JDBC的前世今生

二、JDBC的使用及原理

1.jdbc简单使用步骤

2.简单的例子

3.Statement/PreparedStatement使用

4.事务的使用

三、PreparedStatements原理

1.Sql注入

2.为什么PreparedStatement能防止sql注入？

三、实现一个简单的数据库连接池

总结

热门文章

最新文章

相关课程

相关电子书

相关实验场景