一篇文章带你入门JDBC和DBCP

loserwang
281 阅读8分钟

本文正在参加「技术专题19期 漫谈数据库技术」活动, 我是新人loserwang，希望大家多多支持和交流。

JDBC

JDBC (Java Darabase Connection)是一组Java版的应用程序API，提供了Java应用程序与数据库服务器的连接和通讯能力。 JDBC api 分为两个程序包：

  • Java.sql  核心API ---- J2SE  (Java2标准版）的一部分。使用 java.sql.DriverManager 类、 java.sql.Driver 和 java.sql.Connection 接口连接到数据库。
  • Javax.sql 可扩展API ---- J2EE(Java2企业版)的一部分。包含了基于JIND的资源，以及管理连接池、分布式事务等，使用DateSource接口连接到数据库。

搭建开发环境

  • 安装Mysql数据库并且创建表格

    CREATE DATABASE jdbctest;
use jdbctest;
CREATE TABLE user(
  uid INT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(20),
  password VARCHAR(20),
  name VARCHAR(20)
);
INSERT INTO user VALUES(null, "aaa", "111", "张三");
INSERT INTO user VALUES(null, "bbb", "222", "李四");
INSERT INTO user VALUES(null, "ccc", "333", "王五");

  • 创建项目并导入mysql-connector-java.jar

JDBC编程

  1. 编写程序，在程序中加载数据库驱动
  2. 建立连接
  3. 创建用于向数据库发送SQL的Statement对象
  4. 从代表结果集的ResultSet中取出数据
  5. 断开与数据库的连接，并释放相关资源

加载驱动程序

Class.forName(driverClass)
//加载MySql驱动
Class.forName("com.mysql.jdbc.Driver")
//加载Oracle驱动
Class.forName("oracle.jdbc.driver.OracleDriver")

或者：

DriverManager.registerDriver(new Driver); //会导致注册驱动两次

获取数据库连接

Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/databaseNameuseUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8", "root", "password");

创建Statement\PreparedStatement对象

Statement stmt = conn.createStatement();
PreparedStatement pstmt = conn.prepareStatement(sql);

获取ResultSet

 ResultSet resultSet = stmt.executeQuery("Select * From User;");

释放资源

释放资源的过程非常重要，如果不释放，会造成宕机。所以必须放到 try..catch...finally中

resultSet.close();
stmt.close();
connection.close();

eg:

public class JDBCUtil {
    //serverTimezone解决时区
    // 北京时间和标准时间不同useUnicode和characterEncoding解决中文编码
    public static final String URL = "jdbc:mysql://localhost:3306/jdbctest?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8";
    public static final String USER = "root";
    public static final String PASSWORD = "w@nghong$hun01";
    public static void main(String[] args) {
        Connection conn= null;
        Statement stmt = null;
        ResultSet rs   = null;
        try {
            // 1.加载驱动程序
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.获得数据库连接
            conn = DriverManager.getConnection(URL, USER, PASSWORD);
            //3.操作数据库
            stmt = conn.createStatement();
            //4.实现增删改查
            rs  = stmt.executeQuery("SELECT * FROM user;");


            //如果有数据,rs.next()返回true
            while( rs.next() ){
                System.out.println("username= " +rs.getString("username") + ", password= "
                        + rs.getString("password") + ", name= "+ rs.getString("name") );
            }

            stmt.close();
            conn.close();
        }catch (ClassNotFoundException ex1){
            ex1.printStackTrace();
        }catch(SQLException ex2){
            ex2.printStackTrace();
        }finally{
            if( rs != null ){
                try{
                rs.close();
                }catch( SQLException sqlEx){
                    //
                }
                rs = null;
                if( stmt != null ){
                    try{
                        stmt.close();
                    }catch( SQLException sqlEx){
                        //
                    }
                    conn = null;
                    if( conn != null ){
                        try{
                            conn.close();
                        }catch( SQLException sqlEx){
                            //
                        }
                        conn = null; //垃圾回收机制会更早地垃圾释放

        }
    }
}

常用类

DriverManager: 驱动管理类

  1. 注册驱动
  2. 获得连接 : Connection get Connection(String url, String username, String password);

Connection：连接对象

  1. 创建执行SQL语句的对象

    • Statement createStatement() //执行SQL语句
    • PreparedStatement papreStatement(String sql); //预编译SQL语句
    • CallableStatement prepareCall(String sql); //执行SQL存储过程

  2. 进行事务的管理

    • setAutoCommit(boolean aotuCommit) //设置事务是否自动提交
    • commit() //事务提交
    • rollback() //事务回滚

Statement: 执行SQL

  1. 执行SQL语句

    • boolean excute(String sql) //执行aqk
    • ResultSet executeQuery(String sql) //执行SQL中的select语句
    • int executeUpda(String sql) //执行SQL中的insert/update/select语句，返回影响的行数

  2. 执行批处理操作

    • addBatch() //添加到批处理
    • executeBatch() //执行批处理
    • clearBatch() //清空批处理

ResultSet：结果集

  1. 结果集获取查询到的结构

    • next(): //获取下一行，一开始光标指到第一行之前
    • 针对不同类型的数据可以使用getXXX(columnLable)获取数据，通用getObject(columnLable();

JDBC工具类的抽取

由于JDBC操作代码有很大部分是重复的将这些重复的内容抽取出来，有利于使代码更简洁。

public class JDBCUtil {
    public static final String DRIVER_CLASS = "com.mysql.cj.jdbc.Driver";
    public static final String URL = "jdbc:mysql://localhost:3306/jdbctest?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC";
    public static final String USER = "root";
    public static final String PASSWORD = "w@nghong$hun01";

    /**
     * 获得连接到方法
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public static Connection getConnection() throws ClassNotFoundException, SQLException {
        Class.forName(DRIVER_CLASS);
        Connection connection =  DriverManager.getConnection(URL, USER, PASSWORD);
        return connection;
    }

    /**
     * 资源的释放，分两种，updata只释放两个，而query还有再释放ResultSet
     */
    public static void release(Connection connection, Statement stmt){
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException sqlEx) {
                //
            }
            stmt = null;
        }

        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException sqlEx) {
                //
            }
            connection = null; //垃圾回收机制会更早地垃圾释放

        }
    }


    public static void release(Connection connection, Statement stmt, ResultSet resultSet){
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException sqlEx) {
                //
            }
            resultSet = null;
        }

        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException sqlEx) {
                //
            }
            stmt = null;
        }

        if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException sqlEx) {
                    //
                }
                connection = null; //垃圾回收机制会更早地垃圾释放

            }
        }
}

JDBC的SQL注入漏洞

举例说明

public class TryLogin {

    private static boolean dologin(String username , String password){
        Connection connection = null;
        Statement stmt = null;
        ResultSet resultSet = null;
        boolean flag = false;
        try{
            connection = JDBCUtil.getConnection();
            stmt = connection.createStatement();
            //编写sql
            String sql = "select * from user where username = '" + username + "' and password= '"+ password + "';";
            resultSet = stmt.executeQuery(sql);
            if( resultSet.next() ){
                flag = true;
            }else{
                flag = false;
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtil.release(connection, pstmt, resultSet);
        }
        return flag;
    }
    public static void login(String username , String password){
        boolean flag = dologin(username, password);
        if(flag )
            System.out.println("login success");
        else
            System.out.println("login fail");
    }

    public static void main(String[] args) {
        login("aaa", "111");
        login("aaa", "123");
        login("aaa ' or ' 1 = 1", "123");
    }
}

结果

login success
login fail
login success
login success

为什么第三次和第四次明明不对，却现在登录成功呢？ 因为第三次的sql相当于 select * from user where username = 'aaa' or 1= 1 and 'password = 111 ,而1=1为true。第四次相等于select * from user where username = 'aaa' -- and 'password = 111，后面的password被注释掉了。

PreparedStatement解决SQL漏洞

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

  • PreparedStatement可以避免SQL注入问题。

  • Statement会使数据库频繁编译SQL，可能造成数据库缓冲区溢出。PreparedStatement可对SQL进行预编译，从而提高数据库的执行效率。

  • PreparedStatement对于sql中的参数，允许使用占位符的形式进行替换，简化sql语句的编写。

    public class TryLogin {

  private static boolean dologin(String username , String password){
      Connection connection = null;
      PreparedStatement pstmt = null;
      ResultSet resultSet = null;
      boolean flag = false;
      try{
          connection = JDBCUtil.getConnection();
          //编写sql
          String sql = "select * from user where username = ? and password= ? ;";
          //预处理sql
          pstmt = connection.prepareStatement(sql);
          //设置参数,1代表第一个？位置
          pstmt.setString(1, username);
          pstmt.setString(2, password);
          //执行SQL
          resultSet = pstmt.executeQuery();
          if( resultSet.next() ){
              flag = true;
          }else{
              flag = false;
          }
      }catch (Exception e){
          e.printStackTrace();
      }finally {
          JDBCUtil.release(connection, pstmt, resultSet);
      }
      return flag;
  }
  public static void login(String username , String password){
      boolean flag = dologin(username, password);
      if(flag )
          System.out.println("login success");
      else
          System.out.println("login fail");
  }

  public static void main(String[] args) {
      login("aaa", "111");
      login("aaa", "123");
      login("aaa ' or ' 1 = 1", "123");
      login("aaa ' -- '", "123");
  }
}

数据库连接池

应用程序直接获取连接的缺点： 用户每次请求都需要向数据库获得连接，而数据库创建连接通常需要消耗相对较大的资源，创建时间也较长。假设网站一天10万访问量，数据库服务器就需要创建10万次连接，极大的浪费数据库的资源，并且极易造成数据库服务器内存溢出。

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

DataSource

JDBC2.0 提供了javax.sql.DataSource接口，它负责建立与数据库的连接，当在应用程序中访问数据库时不必编写连接数据库的代码，直接引用DataSource获取数据库的连接对象即可。用于获取操作数据Connection对象。

一个DataSource对象代表了一个真正的数据源。根据DataSource的实现方法，数据源既可以是从关系数据库，也电子表格，还可以是一个表格形式的文件。当一个DataSource对象注册到名字服务中，应用程序就可以通过名字服务获得DataSource对象，并用它来产生一个与DataSource代表的数据源之间的连接。

A factory for connections to the physical data source that this DataSource object represents. An alternative to the DriverManager facility, a DataSource object is the preferred means of getting a connection. An object that implements the DataSource interface will typically be registered with a naming service based on the Java Naming and Directory Interface (JNDI) API. A driver vendor will provide a class that is a basic implementation of the DataSource interface as part of its Java Database Connectivity (JDBC) 2.0 or 3.0 driver product.

The DataSource interface is implemented by a driver vendor. There are three types of implementations:

  • Basic implementation — produces a standard Connection object
  • Connection pooling implementation — produces a Connection object that will automatically participate in connection pooling. This implementation works with a middle-tier connection pooling manager.
  • Distributed transaction implementation — produces a Connection object that may be used for distributed transactions and almost always participates in connection pooling. This implementation works with a middle-tier transaction manager and almost always with a connection pooling manager.

Using a DataSource object is the preferred alternative to using the DriverManager for establishing a connection to a data source. They are similar to the extent that the DriverManager class and DataSource interface both have methods for creating a connection, methods for getting and setting a timeout limit for making a connection, and methods for getting and setting a stream for logging.

建立对数据源的连接时，相比于使用DriverManager，更推荐使用DataSource对象。它们在有些范围相似：都有创建连接的方法，都可以为一个连接创建超时限制，都有获取和设置日志数据流的方法。

however. Unlike the DriverManager, a DataSource object has properties that identify and describe the data source it represents. Also, a DataSource object works with a Java Naming and Directory Interface (JNDI) naming service and can be created, deployed, and managed separately from the applications that use it.

不同于DriverManager, DataSource对象拥有标识和描述它们代表的数据源的属性。此外，DataSource对象可与Java命名和目录接口（JNDI）命名服务一起使用，并且可以与使用它的应用程序分开创建，部署和管理。（比如使用工厂模式和单例模式，每次生成唯一标识一个数据库的DataSource，就可以在项目中到处使用，而不是每次都要建立新的连接。）

A DataSource object has properties that can be modified when necessary. For example, if the data source is moved to a different server, the property for the server can be changed. The benefit is that because the data source's properties can be changed, any code accessing that data source does not need to be changed.

DataSource 对象具有可以在必要时修改的属性。 例如，如果将数据源移动到不同的服务器，则可以更改服务器的属性。 好处是因为可以更改数据源的属性，所以不需要更改访问该数据源的任何代码。

The second major advantage is that the DataSource facility allows developers to implement a DataSource class to take advantage of features like connection pooling and distributed transactions. Connection pooling can increase performance dramatically by reusing connections rather than creating a new physical connection each time a connection is requested. The ability to use distributed transactions enables an application to do the heavy duty database work of large enterprises.

第二个主要优势是 DataSource 工具允许开发人员实现 DataSource 类以利用连接池和分布式事务等功能。 连接池可以通过重用连接而不是在每次请求连接时创建新的物理连接来显着提高性能。 使用分布式事务的能力使应用程序能够完成大型企业的繁重数据库工作。

DBCP连接池

  1. 导入相关jar包: commons-dbcp2.jar 、 commons-pool2.jar 、 commons-logging.jar 。并且添加依赖。

  2. 在项目根目录 /src 增加配置文件 dbcp.properties .


## 驱动的Java类名

driverClassName=com.mysql.cj.jdbc.Driver #传递给JDBC驱动的用于建立连接的URL url=jdbc:mysql://localhost:3306/jdbctest?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8 #用户名和密码 username=root password=1234 #最大活动连接：连接池在同一时间能够分配的最大活动连接的数量 maxActive=30 #最大空闲连接:连接池中容许保持空闲状态的最大连接数量，超过的空闲连接的数量 maxIdle=10 #最大等待时间 maxWait=1000 #初始话连接 initialSize=1

3.使用DBCP

public class TryDBCP {
    private static final String configFile = "dbcp.properties";
    public static void main(String[] args) {
        DataSource dataSource = null;
        Connection connection = null;
        PreparedStatement pstmt =  null;

        Properties properties = new Properties();
        try{
            //TryTryDBCP为项目名，运行时动态获取
            InputStream in = TryDBCP.class.getResourceAsStream("/dbcp.properties");
            properties.load(in);
            dataSource = BasicDataSourceFactory.createDataSource(properties);
             connection = dataSource.getConnection();
             String sqlString = "INSERT INTO user (username, password, name) VALUES (?,?,?)";
             pstmt = connection.prepareStatement(sqlString);
             pstmt.setString(1, "loser");
             pstmt.setString(2, "password");
             pstmt.setString(3, "55");
             int count = pstmt.executeUpdate();
             if(count == 1){
                 System.out.println("add success");
             }else
                 System.out.println("add wrong");
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            try { if (pstmt != null) pstmt.close(); } catch(Exception e) { }
           try { if (connection != null) connection.close(); } catch(Exception e) { }
        }

    }
}

运行test:

User{uid=1, username='loser', password='ppppp'}
User{uid=2, username='loser2', password='qqqqq'}
avatar
文章
阅读
粉丝