Introduction to JDBC

Java Database Connectivity (JDBC) is an application interface in the Java language that specifies how client programs access the database, providing methods such as querying and updating data in the database.

Database Driver

What is a database driver? Similar to sound card driver, graphics card driver
Our programs are database driven and work with databases.

JDBC

SUN provides a specification, JDBC, for simplifying developer (unified database) operations.

The implementation of these specifications is done by specific manufacturers.

For developers, we only need to know the interface operations of JDBC.

java.sql

javax.sql

You also need to import the database driver package

First JDBC program

1. Create a test database

CREATE DATABASE jdbcStudy CHARACTER SET utf8 COLLATE utf8_general_ci;

USE jdbcStudy;

CREATE TABLE `users`(
id INT PRIMARY KEY,
NAME VARCHAR(40),
PASSWORD VARCHAR(40),
email VARCHAR(60),
birthday DATE
);

INSERT INTO `users`(id,NAME,PASSWORD,email,birthday)
VALUES(1,'zhansan','123456','zs@sina.com','1980-12-04'),
(2,'lisi','123456','lisi@sina.com','1981-12-04'),
(3,'wangwu','123456','wangwu@sina.com','1979-12-04')

2. Create a normal project

3. Import Database Driver
pom.xml

    <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>8.0.16</version>
    </dependency>

4. Write test code

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

public class JdbcDemo {
    public static void main(String[] args) throws Exception {
        //1. Load Driver
        Class.forName("com.mysql.cj.jdbc.Driver");//Fixed Writing jdbc driver version before 8.0 was com.mysql.jdbc.Driver

        //2. User information and url
        //useUnicode=true&characterEncoding=utf8&&useSSL=true
        //useUnicode=true supports Chinese encoding; characterEncoding=utf8 The Chinese character set is set to utf-8; useSSL=true uses secure links
        //Add serverTimezone=UTC after jdbc driver version 8.0
        String url ="jdbc:mysql://localhost:3306/jdbcstudy?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&&useSSL=true";
        String name = "root";
        String password = "123456";

        //3. Connection succeeded, returning the database object connection to represent the database
        Connection connection= DriverManager.getConnection(url,name,password);
        //4. Object to execute SQL Object to execute SQL
        Statement statement = connection.createStatement();

        //5. Objects executing SQL to execute SQL may have results and view returned results
        String sql="SELECT * FROM users";
        ResultSet resultSet = statement.executeQuery(sql);//Returned result set, which encapsulates the results of all our queries
        while(resultSet.next()){
            System.out.println("ID="+resultSet.getObject("id"));
            System.out.println("Full name="+resultSet.getObject("NAME"));
            System.out.println("Password="+resultSet.getObject("PASSWORD"));
            System.out.println("mailbox="+resultSet.getObject("email"));
            System.out.println("Birthday="+resultSet.getObject("birthday"));
            System.out.println("==================================");
        }
        //6. Release the connection (from back to front)
        resultSet.close();
        statement.close();
        connection.close();
    }
}

Detailed description of each object in JDBC

Step Summary:
1. Load Driver

2. Connect to the database DriverManager

3. Get the Statement of the object that executes SQL

4. Get the returned result set

5. Release the connection

DriverManager

//DriverManager.registerDriver(new com.mysql.jdbc.Driver());
Class.forName("com.mysql.jdbc.Driver");//Fixed Writing
Connection connection= DriverManager.getConnection(url,name,password);

//connection represents the database
//Database Settings Autocommit
//Transaction Commit
//rollback
connection.rollback();
connection.commit();
connection.setAutoCommit();

URL

String url ="jdbc:mysql://localhost:3306/jdbcstudy?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&&useSSL=true";

//mysql default port 3306
//Protocol: //Host Address: Port number/Database name? Parameter 1&Parameter 2&Parameter 3...

//Oracle   1521
//jdbc:oralce:thin:@localhost:1521:sid

Object for statement to execute SQL PrepareStatement to execute SQL

String sql="SELECT * FROM users";//Write Sql

statement.executeQuery();//Query operation, return ResultSet
statement.execute();//Execute any SQL
statement.executeUpdate();//Update, insert, delete, return an affected number of rows

ResultSet query result set, encapsulating all query results

Gets the specified data type

ResultSet resultSet = statement.executeQuery(sql);//Returned result set, which encapsulates the results of all our queries

resultSet.getObject();//Use without knowing column type

//Use the specified type if you know it
resultSet.getString();
resultSet.getInt();
resultSet.getFloat();
resultSet.getDate();
...       

Traversal, Pointer

        resultSet.next(); //Move to Next
        resultSet.afterLast();//Move to Last
        resultSet.beforeFirst();//Move to the front
        resultSet.previous();//Move to the previous one
        resultSet.absolute(row);//Move to specified line

Release memory

//6. Release the connection (from back to front) 
        resultSet.close();
        statement.close();
        connection.close();//connection connections consume the most resources

statement object

The statement object in JDBC is used to send SQL statements to the database. To complete the database add-delete check, you only need to send add-delete check statements to the database through this object.

The executeUpdate method of the Statement object, which is used to send sq| statements with additions, deletions, and changes to the database, returns an integer after executeUpdate is executed (that is, adding or deleting statements results in changes in several rows of data in the database).

Statement. The executeQuery method is used to generate a query statement to the database, and the executeQuery method returns a ResultSet object that represents the result of the query.

CRUD Operation-create
Use the executeUpdate(String sql) method to complete the data addition operation, sample operation:

 Statement statement = connection.createStatement();
        String sql = "insert into user(...) values(...)";
        int num = statement.executeUpdate(sql);
        if(num>0){
            System.out.println("Insert Successful");
        }

CRUD Operation-delete
Use the executeUpdate(String sql) method to complete the data deletion operation, example operation:

Statement statement = connection.createStatement();
        String sql = "delete from user where id =1";
        int num = statement.executeUpdate(sql);
        if(num>0){
            System.out.println("Delete succeeded");
        }

CURD Operation-update
Use the executeUpdate(String sql) method to complete the data modification operation, sample operation:

Statement statement = connection.createStatement();
        String sql = "update user set name ='' where name = ''";
        int num = statement.executeUpdate(sql);
        if(num>0){
            System.out.println("Successful modification");
        }

CURD Operation-read
Use the executeUpdate(String sql) method to complete the data query operation, sample operation:

Statement statement = connection.createStatement();
        String sql = "select * from  user where id =1";
        ResultSet rs= statement.executeQuery(sql);
        if(rs.next()){
            System.out.println("");
        }

Wrap as a tool class

Configure the file DB first. Properties wrote the information

driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&&useSSL=true
username=root
password=123456

Tool class

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

public class JdbcUtils {

    private static String driver = null;
    private static String url = null;
    private static String username = null;
    private static String password = null;
    static {
        try{
            FileInputStream in = new FileInputStream("src/main/resources/db.properties");
            Properties properties = new Properties();
            properties.load(in);
            driver=properties.getProperty("driver");
            url=properties.getProperty("url");
            username=properties.getProperty("username");
            password=properties.getProperty("password");

            //1. Drivers only load once
            Class.forName(driver);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //2. Get connected
    public static Connection getConnection() throws Exception{
        return DriverManager.getConnection(url, username, password);
    }
    //3. Release resources
    public static void release(Connection conn, Statement st, ResultSet rs) throws SQLException {

        if (rs != null) {
            rs.close();
        }
        if (st != null){
            st.close();
        }
        if(conn != null){
            conn.close();
        }

    }
}

Use tool classes (insert data as an example) (exectueUpdate can add, delete, and alter three operations)

import com.cheng.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

import static com.cheng.utils.JdbcUtils.getConnection;

public class TestInsert {
    public static void main(String[] args){
        Connection conn =null;
        Statement st = null;
        ResultSet rs =null;

        try {
            conn = getConnection();//Get Connections
            st = conn.createStatement();//Get SQL Execution Object
            String sql = "INSERT INTO users(id,`NAME`,`PASSWORD`,`email`,`birthday`)" +
                    "VALUES(5,'Hello','123456','233223@qq.com','2020-01-01')";

            int i = st.executeUpdate(sql);
            if(i>0){
                System.out.println("Insert Successful");
            }
            JdbcUtils.release(conn,st,rs);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

}

Using tool classes (querying data for example)

import com.cheng.utils.JdbcUtils;

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

import static com.cheng.utils.JdbcUtils.getConnection;

public class TestQuery {
    public static void main(String[] args) throws SQLException {
        Connection conn =null;
        Statement st = null;
        ResultSet rs =null;

        try {
            conn = getConnection();//Get Connections
            st = conn.createStatement();//Get SQL Execution Object
            String sql = "select * from users";
            rs=st.executeQuery(sql);//Return result set after query

            while (rs.next()){
                System.out.println(rs.getString("NAME"));
            }
            JdbcUtils.release(conn,st,rs);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

SQL Injection Problems

SQL is vulnerable and will be attacked resulting in data leaks SQL will be spliced or

Principle, such as password query is normal to match password, but if or 1=1 is added, password query will be considered to pass, because or is both right and left sides of a set-up, and because 1=1 is always set-up, the server is deceived to think that password query passed

Example

import com.cheng.utils.JdbcUtils;

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

import static com.cheng.utils.JdbcUtils.getConnection;

public class SqlInjection {
    public static void main(String[] args) {
        // Normal Query
        login("Hello","123456");

        System.out.println("=================");

        // SQL Injection
        login("' OR '1=1","' OR '1=1");
    }
    public static void login(String name,String password){
        Connection conn =null;
        Statement st = null;
        ResultSet rs =null;
        try {
            conn = getConnection();//Get Connections
            st = conn.createStatement();//Get SQL Execution Object

            // Normal Query: SELECT * FROM users WHERE `NAME`='Hello'AND `PASSWORD`='123456'
            // SQL injection: SELECT * FROM users WHERE `NAME`=''OR'1=1' AND `PASSWORD`=''OR'1=1'
            // SELECT * FROM users so you can get all the information for the entire table
            String sql = "SELECT * FROM users WHERE `NAME`='"+ name +"'  AND `PASSWORD`='"+ password +"'" ;

            rs=st.executeQuery(sql);//Return result set after query
            while (rs.next()){
                System.out.println(rs.getString("NAME"));
            }
                JdbcUtils.release(conn,st,rs);
            } catch (Exception e) {
                e.printStackTrace();
            }finally {
                try {
                    JdbcUtils.release(conn,st,rs);
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
    }
}

PreparedStatement object

PreparedStatement prevents SQL injection and is more efficient.

public class Test {
    public static void main(String[] args) {
        Connection connection= null;
        PreparedStatement pstm=null;
        try {
            connection = JdbcUtils.getConnection();
            //PreparedStatement Prevents SQL Injection Essentials: Treat parameters passed in as characters
            //Assume that escape characters, such as quotation marks, are escaped directly
            
            // Difference:
            // 1. Use question mark placeholders instead of parameters
            String sql = "insert into users(id, `NAME`, `PASSWORD`, `email`,`birthday`) values(?, ?, ?, ?, ?)";
            // 2. Pre-compile sql, write SQL first, then do not execute
            pstm = connection.prepareStatement(sql);
            // Manual assignment
            pstm.setInt(1,4);// 1 for the first question mark
            pstm.setString(2,"Zhang San");
            pstm.setString(3,"123123");
            pstm.setString(4,"123333@qq.com");
            pstm.setDate(5,new java.sql.Date(new Date().getTime()));// Note the Dite to be converted to sql
            //implement
            int i = pstm.executeUpdate();
            if (i>0){
                System.out.println("Insert Successful");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                JdbcUtils.release(connection,pstm,null);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

Output:

Hello
=================
zhansan
lisi
wangwu
Zhang San
Hello

Prevent SQL injection by passing characters with "" and escape characters from being escaped
Example improvements:

import com.cheng.utils.JdbcUtils;

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

public class Test {
    public static void main(String[] args) {
        // Normal Query
        login("Hello","123456");

        System.out.println("=================");
        // SQL Injection
        login("'' OR 1=1","'' OR 1=1");
    }
    public static void login(String username,String password) {
        Connection connection = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        try {
            connection = JdbcUtils.getConnection();
            // Difference:
            // 1. Use question mark placeholders instead of parameters
            String sql = "SELECT * FROM users WHERE `NAME`=? AND `PASSWORD`=?";
            // 2. Pre-compile sql, write SQL first, then do not execute
            pstm = connection.prepareStatement(sql);
            // Manual assignment
            pstm.setString(1, username);// 1 for the first question mark
            pstm.setString(2, password);

            rs = pstm.executeQuery();//Be careful!!!! Unlike st.executeQuery(sql); Then don't write SQL in parentheses, otherwise an error will be reported. Because PreparedStatement is precompiled, the PreparedStatement object already contains a SQL query
            //Return result set after query
            while (rs.next()) {
                System.out.println(rs.getString("NAME"));
            }
            JdbcUtils.release(connection, pstm, rs);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                JdbcUtils.release(connection, pstm, rs);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

Be careful!!!! rs = pstm.executeQuery(); Unlike rs = st.executeQuery(sql); Then don't write SQL in parentheses, otherwise an error will be reported. Because PreparedStatement is precompiled, the PreparedStatement object already contains a SQL query

Output: (SQL Injection Failure)

Hello
=================

Connect to the database using IDEA

Connect to database

add table

Modify Datasheet:
Double-click Datasheet, modify, submit

Write queries

JDBC Operational Transactions

Either all succeeded or all failed

ACID Principle

• Atomicity: either all or none
• Consistency: Total number of results unchanged
• Isolation: Multiple processes do not interfere with each other
• Persistence: Once committed irreversibly, persisted to the database

Isolation issues:

• Dirty Read: One transaction read another uncommitted transaction
• Non-repeatable reading: within the same transaction, data in tables is read repeatedly and tables are changed
• False reading: reading data inserted by others within a transaction, resulting in inconsistent results

code implementation

1. Open the transaction conn.setAutoCommit(false);
2. A set of businesses is executed and transactions committed
3. Definition rollbacks that can be displayed in the catch statement, but default failures roll back

Create a sample database:

public class JdbcTransaction{
    public static void main(String[] args) {

        Connection conn =null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = JdbcUtils.getConnection();
            //Turn off automatic commit of database and start transaction
            conn.setAutoCommit(false);
            //Open transaction automatically
            String sql = "update account set money = money-100 where id = 1";
            ps =conn.prepareStatement(sql);
            ps.executeUpdate();
            String sql2 = "update account set money = money-100 where id = 2";
            ps=conn.prepareStatement(sql2);
            ps.executeUpdate();

            //Business completed, transaction committed
            conn.commit();
            System.out.println("Operation Successful");
        } catch (Exception e) {
            try {
                //Default rollback if failed
                conn.rollback();//If failed, roll back
                System.out.println("fail");
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            e.printStackTrace();
        }finally {
            try {
                JdbcUtils.release(conn,ps,rs);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

Database Connection Pool

Traditional connection: database connection - execution complete - release

Frequent server duplicate connections - freeing up can be a waste of resources

Pooling technology: Prepare some pre-prepared resources to connect to pre-prepared resources

• Number of common connections: 10
• Minimum number of connections: 10 (generally equal to the number of common connections)
• Maximum number of connections: 100 (maximum hosting limit for business)
• If it is greater than the maximum number of connections, wait in line.
• Wait timeout: 100ms (link disconnected after 100ms)

Writing a connection pool: Implementing an interface DateSource

Common open source data source implementations (out-of-the-box)
These are the implementation classes for the common DateSource interfaces on the market

• DBCP
• C3P0
• Druid:Alibaba

With these database connection pools in place, we no longer need to write code to connect to the database in our project development

DBCP

JAR Packages Needed

(using maven import)
pom.xml

    <!-- https://mvnrepository.com/artifact/org.apache.commons/commons-dbcp2 -->
    <dependency>
      <groupId>org.apache.commons</groupId>
      <artifactId>commons-dbcp2</artifactId>
      <version>2.7.0</version>
    </dependency>

DBCP Profile

dbcp-config.properties

#connections setting up
driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&&useSSL=true
username=root
password=123456

#<!-- Initialize Connection-->
initialSize=10

#Maximum number of connections
maxActive=50

#<!-- Maximum idle connection-->
maxIdle=20

#<!-- Minimum idle connection-->
minIdle=5

#<!-- Timeout wait time in milliseconds 6000 milliseconds/1000 equals 60 seconds-->
maxWait=60000
#The JDBC driver must have connection property attributes attached to it in this format: [Property name = property;]
#Note that the attributes "user" and "password" are explicitly passed, so they do not need to be included here.
connectionProperties=useUnicode=true;characterEncoding=UTF8

#Specifies the auto-commit state of the connection created by the connection pool.
defaultAutoCommit=true

#driver default specifies the read-only state of the connection created by the connection pool.
#If this value is not set, the "setReadOnly" method will not be called. (Some drivers do not support read-only mode, such as Informix)
defaultReadOnly=

#driver default specifies the transaction level (TransactionIsolation) of the connection created by the connection pool.
#The available values are one of the following: (javadoc is available for details.) NONE,READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE
defaultTransactionIsolation=READ_UNCOMMITTED

Tool class

import org.apache.commons.dbcp2.BasicDataSourceFactory;
import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.*;
import java.util.Properties;

public class Dbcp {
    private static DataSource dataSource = null;
    static {
        try{
            FileInputStream in = new FileInputStream("src/main/resources/dbcpconfig.properties");
            Properties properties = new Properties();
            properties.load(in);

            // 1. Create Data Source Factory Mode - > Create
            dataSource = BasicDataSourceFactory.createDataSource(properties);


        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    // 2. Get connected
    public static Connection getConnection() throws Exception{
        return dataSource.getConnection();  // Get a connection from a data source
    }
    // 3. Release resources
    public static void release(Connection conn, Statement st, ResultSet rs) throws SQLException {

        if (rs != null) {
            rs.close();
        }
        if (st != null){
            st.close();
        }
        if(conn != null){
            conn.close();
        }

    }
}

Test Code

import com.cheng.utils.Dbcp;
import java.sql.*;

public class DbcpTest {
    public static void main(String[] args) throws SQLException {
        Connection conn =null;
        PreparedStatement ps = null;
        ResultSet rs =null;
        try {
            conn = Dbcp.getConnection();
            String sql = "select * from users";
            ps=conn.prepareStatement(sql);
            rs=ps.executeQuery();
            while (rs.next()){
                System.out.println(rs.getString("NAME"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            Dbcp.release(conn,ps,rs);
        }
    }
}

C3P0

JAR Packages Needed

(using maven import)
pom.xml

    <!-- https://mvnrepository.com/artifact/com.mchange/c3p0 -->
    <dependency>
      <groupId>com.mchange</groupId>
      <artifactId>c3p0</artifactId>
      <version>0.9.5.4</version>
    </dependency>

C3P0 Profile

c3p0-config.xml

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
    <!-- If in code"ComboPooledDataSource ds=new ComboPooledDataSource();"This means you are using c3p0 Defaults (default)-->
    <!-- c3p0 Default (default) configuration-->
    <default-config>
        <property name="driverClass">com.mysql.cj.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbcstudy?serverTimezone=UTC&amp;useUnicode=true&amp;characterEncoding=utf8&amp;&amp;useSSL=true</property>
        <property name="user">root</property>
        <property name="password">123456</property>

        <property name="acquireIncrement">5</property>
        <property name="initialPoolSize">10</property>
        <property name="minPoolSize">5</property>
        <property name="maxPoolSize">20</property>
    </default-config>
    <!-- If in code"ComboPooledDataSource ds=new ComboPooledDataSource("s1");"This means you are using s1 Configuration parameters)-->
    <named-config name="s1">
        
    </named-config>
    
</c3p0-config>

Additionally, code configuration parameters can be used

// You can configure parameters using code
	dataSource = new ComboPooledDataSource();
	dataSource.setDriverClass("");
	dataSource.setJdbcUrl("");
	dataSource.setUser("");
	dataSource.setPassword("");
	dataSource.setMaxPoolSize(100);

Tool class

import com.mchange.v2.c3p0.ComboPooledDataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;


public class C3p0 {
    private static ComboPooledDataSource dataSource = null;
    static {
        try{
            // create data source
            dataSource = new ComboPooledDataSource();

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    // 2. Get the connection Because getConnection() is the method of the interface, the following does not need to change as DBCP does
    public static Connection getConnection() throws Exception{
        return dataSource.getConnection();  // Get a connection from a data source
    }
    // 3. Release resources
    public static void release(Connection conn, Statement st, ResultSet rs) throws SQLException {

        if (rs != null) {
            rs.close();
        }
        if (st != null){
            st.close();
        }
        if(conn != null){
            conn.close();
        }
    }
}

Test Code

import com.cheng.utils.C3p0;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class C3p0Test {
    public static void main(String[] args) throws SQLException {
        Connection conn =null;
        PreparedStatement ps = null;
        ResultSet rs =null;
        try {
            conn = C3p0.getConnection();
            String sql = "select * from users";
            ps=conn.prepareStatement(sql);
            rs=ps.executeQuery();
            while (rs.next()){
                System.out.println(rs.getString("NAME"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            C3p0.release(conn,ps,rs);
        }
    }
}

conclusion

Whatever data source you use, the nature is unchanged, the DateSource interface does not change, and the method does not change, because these open source data sources are implementations of the DateSource interface.