Introduction

Ref:

Connection

import JDBC Packages

// for standard JDBC programs
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;  

// for BigDecimal and BigInteger support
import java.math.*;

register JDBC Driver

  1. Class.forName("com.mysql.jdbc.Driver")
    • 使用反射，后期绑定，不需要驱动程序在编译时可用
    • 大多数JDBC驱动程序类通过调用在自己的静态初始化器中注册自己registerDriver()
  2. DriverManager.registerDriver()
    • Driver重複加載
    • 耦合度高

Ref:

database URL Formulation

  • getConnection(String url)
  • getConnection(String url, Properties prop)
  • getConnection(String url, String user, String password)

URL:

RDBMS JDBC driver name URL format
MySQL com.mysql.jdbc.Driver jdbc:mysql://hostname/databaseName
Oracle oracle.jdbc.driver.OracleDriver jdbc:oracle:thin:@hostname:port:databaseName
SQL Server com.microsoft.sqlserver.jdbc.SQLServerDriver jdbc:sqlserver://localhost:1433;databaseName=

create Connection Object

Example:

conn = DriverManager.getConnection(DB_URL,USER,PASS);

Statement stmt = conn.createStatement();
PreparedStatement pstmt = conn.preparedStatement(SQL);
CallableStatement cstmt = conn.prepareCall (SQL);
  • PreparedStatement > createStatement:
    • 简化的SQL字符串非标准的Java对象的设置（对象类型明确性，除了使用setObject()，更可以明确getter、 setter类型）
    • SQL语句的预编译和数据库侧缓存可提高整体执行速度
    • 通过内置对引号和其他特殊字符的转义，自动防止SQL Injection，这要求您使用任何PreparedStatement setXxx()方法来设置值，因此，不要通过字符串连接来内联SQL字符串中的值。
//Better:
preparedStatement = connection.prepareStatement("INSERT INTO Person (name, email, birthdate, photo) VALUES (?, ?, ?, ?)");
preparedStatement.setString(1, person.getName());
preparedStatement.setString(2, person.getEmail());
preparedStatement.setTimestamp(3, new Timestamp(person.getBirthdate().getTime()));
preparedStatement.setBinaryStream(4, person.getPhoto());
preparedStatement.executeUpdate();

//Easily causes SQL Injection
preparedStatement = connection.prepareStatement("INSERT INTO Person (name, email) VALUES ('" + person.getName() + "', '" + person.getEmail() + "'");
preparedStatement.executeUpdate();
  • CallableStatement: access the database stored procedures

Ref: java - Difference between Statement and PreparedStatement - Stack Overflow

Sample Code

//STEP 1. Import required packages

import java.sql.*;

public class FirstExample {
    // JDBC driver name and database URL
    static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    static final String DB_URL = "jdbc:mysql://localhost/EMP";

    //  Database credentials
    static final String USER = "username";
    static final String PASS = "password";

    public static void main(String[] args) {
        Connection conn = null;
        Statement stmt = null;
        try {
            //STEP 2: Register JDBC driver
            Class.forName("com.mysql.jdbc.Driver");

            //STEP 3: Open a connection
            conn = DriverManager.getConnection(DB_URL, USER, PASS);

            //STEP 4: Execute a query
            stmt = conn.createStatement();
            String sql;
            sql = "SELECT id, first, last, age FROM Employees";
            ResultSet rs = stmt.executeQuery(sql);

            //STEP 5: Extract data from result set
            while (rs.next()) {
                //Retrieve by column name
                int id = rs.getInt("id");
                int age = rs.getInt("age");
                String first = rs.getString("first");
                String last = rs.getString("last");
            }
            //STEP 6: Clean-up environment
            rs.close();
            stmt.close();
            conn.close();
        } catch (Exception e) {
            //Handle errors for Class.forName
            e.printStackTrace();
        } finally {
            //finally block used to close resources
            try {
                if (stmt != null)
                    stmt.close();
            } catch (SQLException se) {
                se.printStackTrace();
            }
            try {
                if (conn != null)
                    conn.close();
            } catch (SQLException se) {
                se.printStackTrace();
            }
        }
    }
}

DriverManager vs DataSource

  • DriverManager: 当在Java类中创建/关闭连接时，会影响应用程序的性能。
  • DataSource: 由于未在类内创建/关闭连接，而是由应用程序服务器管理这些连接，并且可以在运行时进行获取，因此可以提高应用程序的性能。
DriverManager DataSource
To Provide Connection Details Need No Need
Provide Connection Pool N Y

Example Code:

下面的代码显示了两种获取连接的方法。
在mySqlDataSource注释此行的情况下，无需了解URL 。

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

public class MySqlDataSourceTest {
    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        /************** using MysqlDataSource starts **************/
        MysqlDataSource d = new MysqlDataSource();
        d.setUser("root");
        d.setPassword("root");
        // d.setUrl("jdbc:mysql://localhost:3306/manavrachna");
        d.setDatabaseName("manavrachna");
        Connection c = (Connection) d.getConnection();
        /************** using MysqlDataSource ends**************/


        /************** using DriverManager start **************/
        Class.forName("com.mysql.jdbc.Driver");
        Connection c = (Connection) DriverManager.getConnection("jdbc:mysql://localhost:3306/manavrachna", "root", "root");
        /************** using DriverManager ends **************/

        Statement st = (Statement) c.createStatement();
        ResultSet rs = st.executeQuery("select id from employee");
        while (rs.next()) {
            System.out.println(rs.getInt(1));
        }
    }
}

Ref:

Connection Pool

数据库连接生命周期：

  1. 使用数据库驱动程序打开与数据库的连接
  2. 打开TCP套接字以读取/写入数据
  3. 通过套接字读取/写入数据
  4. 断开连接
  5. 关闭插座

使用连线池原因：

  1. 数据库连接是相当昂贵的操作
  2. 能够重用许多现有连接，节省执行大量昂贵的数据库行程的成本

Frameworks:

  • Apache Commons DBCP 2
  • HikariCP (Spring Boot 2.x By Default)
  • C3P0
package com.journaldev.example;
 
/**
 * Java JDBC Connection pool using HikariCP example program
 * 
 * @author pankaj
 */
 
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
 
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
 
public class HikariCPDemo {
 
    private static HikariDataSource dataSource = null;
 
    static {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl("jdbc:mysql://localhost:3306/empdb");
        config.setUsername("root");
        config.setPassword("root");
        config.addDataSourceProperty("minimumIdle", "5");
        config.addDataSourceProperty("maximumPoolSize", "25");
 
        dataSource = new HikariDataSource(config);
    }
 
    public static void main(String[] args) throws SQLException {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            connection = dataSource.getConnection();
            statement = connection.createStatement();
            resultSet = statement.executeQuery("select * from tblemployee");
            while (resultSet.next()) {
                System.out.println("empId:" + resultSet.getInt("empId"));
                System.out.println("empName:" + resultSet.getString("empName"));
                System.out.println("dob:" + resultSet.getDate("dob"));
                System.out.println("designation:" + resultSet.getString("designation"));
            }
        } finally {
            resultSet.close();
            statement.close();
            connection.close();
        }
    }
}

Ref: