JAVA learning notes (JDBC)

Posted by herrin on Mon, 31 Jan 2022 23:07:44 +0100

JDBC

Today's goal

Master the CRUD of JDBC
Understand the role of various objects in JDBC
Master the use of Druid

1. JDBC overview

In the development, we use java language, so it is necessary to operate the data in the database through Java language. This is the JDBC to learn next.

1.1 JDBC concept

JDBC is a set of API s that use Java language to operate relational databases

Full name: (Java database connectivity) Java database connectivity

The same set of Java code we developed cannot operate different relational databases, because the underlying implementation details of each relational database are different. If so, the problem will be great. In the company, MySQL database can be used in the development stage, and when the company finally selects oracle database, we need to modify the code in large quantities, which is obviously not what we want to see. What we need to do is that the same set of Java code operates different relational databases. At this time, sun company specifies a set of standard interface (JDBC), which defines all the rules for operating relational databases. As we all know, the interface cannot be used directly. We need to use the implementation class of the interface, and this set of implementation class (called driver) is given by their respective database manufacturers.
First experience the full version of the code:

1.2 JDBC essence

A set of rules defined by the official (sun company) to operate all relational databases, namely the interface
Each database manufacturer implements this interface and provides database driver jar package
We can use this set of interfaces (JDBC) to program, and the real executed code is to drive the implementation classes in the jar package

1.3 JDBC benefits

Each database manufacturer uses the same interface, and Java code does not need to be developed separately for different databases
The underlying database can be replaced at any time, and the Java code accessing the database is basically unchanged

In the future, the code for operating the database only needs to be oriented towards JDBC (Interface). The driver package of the database needs to be imported to operate any relational database. If you need to operate MySQL database, you need to import the driver package of MySQL database in the project. The figure below shows the MySQL driver package

2. JDBC quick start

Let's first look at the process of operating the database through Java

Step 1: write Java code

Step 2: Java code sends SQL to MySQL server

Step 3: the MySQL server receives the SQL statement and executes it

Step 4: return the result of SQL statement execution to Java code

2.1 coding steps

Create a project and import the driver jar package
Register driver

Class.forName("com.mysql.jdbc.Driver");

Get connection
```
Connection conn = DriverManager.getConnection(url, username, password);
```
If the Java code needs to send SQL to the MySQL server, you need to establish a connection first
Define SQL statement
```
String sql =  "update..." ;
```
Get execute SQL object

Executing SQL statements requires an SQL execution object, which is the Statement object
```
Statement stmt = conn.createStatement();
```
Execute SQL
```
stmt.executeUpdate(sql);  
```
Processing returned results
Release resources

2.2 specific operation

Create a new empty project
Define the name of the project and specify the location
Set the project, including JDK version and compiled version
Create a module and specify the name and location of the module

Import driver package

Put the mysql driver package under the lib directory (arbitrarily named) under the module, and add the jar package as a library file
When adding as a library file, you have the following three options
- Global Library: globally valid
- Project Library: the project is valid
- Module Library: the module is valid
Create class under src
Write the code as follows

/**
 * JDBC quick get start
 */
public class JDBCDemo {

    public static void main(String[] args) throws Exception {
        //1. Register driver
        //Class.forName("com.mysql.jdbc.Driver");
        //2. Get connection
        String url = "jdbc:mysql://127.0.0.1:3306/db1";
        String username = "root";
        String password = "1234";
        Connection conn = DriverManager.getConnection(url, username, password);
        //3. Define sql
        String sql = "update account set money = 2000 where id = 1";
        //4. Get the object Statement executing sql
        Statement stmt = conn.createStatement();
        //5. Execute sql
        int count = stmt.executeUpdate(sql);//Number of rows affected
        //6. Treatment results
        System.out.println(count);
        //7. Release resources
        stmt.close();
        conn.close();
    }
}

3. Detailed explanation of JDBC API

3.1 DriverManager

DriverManager (driver management class) functions:

Register driver

The registerDriver method is used to register drivers, but the entry case we did before is not written like this. Instead, it is implemented as follows
```
Class.forName("com.mysql.jdbc.Driver");
```
We query the Driver class provided by MySQL to see how it is implemented. The source code is as follows:

    static {
        try {
            DriverManager.registerDriver(new Driver());
        } catch (SQLException var1) {
            throw new RuntimeException("Can't register driver!");
        }
    }

In the static code block in this class, the registerDriver() method of the DriverManager object has been executed to register the driver. Then we only need to load the driver class and the static code block will be executed. And class forName("com.mysql.jdbc.Driver"); You can load the driver class.

Tips:

For the driver package after MySQL 5, the steps of registering the driver can be omitted
Automatically load meta-inf / services / java.xml in jar package sql. Driver class in driver file

Get database connection

Parameter Description:
- url: connection path
  
  Syntax: jdbc:mysql://ip Address (domain name): port number / database name? Parameter key value pair 1 & parameter key value pair 2
  
  Example: jdbc:mysql://127.0.0.1:3306/db1
  
  Details:
  - If the local mysql server is connected and the default port of mysql service is 3306, the url can be abbreviated as: jdbc:mysql: / / / database name? Parameter key value pair
  - Configure the useSSL=false parameter, disable the secure connection mode, and solve the warning prompt
- User: user name
- poassword: password
  
  solve:

3.2 Connection

Function of Connection object:

Gets the object that executes SQL
Management services

3.2.1 get execution object

Normal execution SQL object
```
Statement createStatement()
```
The entry case is the execution object obtained through this method.
Execution SQL object of precompiled SQL: preventing SQL injection
```
PreparedStatement  prepareStatement(sql)
```
The PreparedStatement SQL statement execution object obtained in this way is what we will focus on later. It can prevent SQL injection.
The object that executes the stored procedure
```
CallableStatement prepareCall(sql)
```
The CallableStatement execution object obtained in this way is used to execute stored procedures, which are not commonly used in MySQL, so we will not explain this.

3.2.2 transaction management

First review the operation of MySQL transaction management:

START TRANSACTION: BEGIN; Or START TRANSACTION;
COMMIT transaction: COMMIT;
ROLLBACK transaction: ROLLBACK;

MySQL automatically commits transactions by default

Next, learn the method of JDBC transaction management.

Three corresponding methods are defined in Connection ports:

Open transaction

[the external chain picture transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the picture and upload it directly (img-1eZHTF03-1643288380462)(assets/image-20210725173444628.png)]

Participating in autoCommit indicates whether to automatically commit transactions, true indicates to automatically commit transactions, and false indicates to manually commit transactions. To start a transaction, you need to set this parameter to false.
Commit transaction

[the external chain picture transfer fails, and the source station may have an anti-theft chain mechanism. It is recommended to save the picture and upload it directly (img-smJwdHp5-1643288380464)(assets/image-20210725173618636.png)]
Rollback transaction

It is suggested to save the picture directly from the external link (eapn8738g-86878s, eapn8738g-image may be saved from the external link) (it is suggested to save the picture directly from the external link)

The specific code is as follows:

/**
 * JDBC API Detailed explanation: Connection
 */
public class JDBCDemo3_Connection {

    public static void main(String[] args) throws Exception {
        //1. Register driver
        //Class.forName("com.mysql.jdbc.Driver");
        //2. Get connection: if the connection is local mysql and the port is the default 3306, writing can be simplified
        String url = "jdbc:mysql:///db1?useSSL=false";
        String username = "root";
        String password = "1234";
        Connection conn = DriverManager.getConnection(url, username, password);
        //3. Define sql
        String sql1 = "update account set money = 3000 where id = 1";
        String sql2 = "update account set money = 3000 where id = 2";
        //4. Get the object Statement executing sql
        Statement stmt = conn.createStatement();

        try {
            // ============Open transaction==========
            conn.setAutoCommit(false);
            //5. Execute sql
            int count1 = stmt.executeUpdate(sql1);//Number of rows affected
            //6. Treatment results
            System.out.println(count1);
            int i = 3/0;
            //5. Execute sql
            int count2 = stmt.executeUpdate(sql2);//Number of rows affected
            //6. Treatment results
            System.out.println(count2);

            // ============Commit transaction==========
            //When the program runs here, it indicates that there is no problem, and the transaction needs to be committed
            conn.commit();
        } catch (Exception e) {
            // ============Rollback transaction==========
            //When an exception occurs, the program will execute to this place. At this time, the transaction needs to be rolled back
            conn.rollback();
            e.printStackTrace();
        }

        //7. Release resources
        stmt.close();
        conn.close();
    }
}

3.3 Statement

3.3.1 general

The Statement object is used to execute SQL statements. The methods used for different types of SQL statements are also different.

Execute DDL and DML statements
Execute DQL statement

This method involves the ResultSet object, which we haven't learned yet. We'll focus on it later.

3.3.2 code implementation

Execute DML statement

/**
  * Execute DML statement
  * @throws Exception
  */
@Test
public void testDML() throws  Exception {
    //1. Register driver
    //Class.forName("com.mysql.jdbc.Driver");
    //2. Get connection: if the connection is local mysql and the port is the default 3306, writing can be simplified
    String url = "jdbc:mysql:///db1?useSSL=false";
    String username = "root";
    String password = "1234";
    Connection conn = DriverManager.getConnection(url, username, password);
    //3. Define sql
    String sql = "update account set money = 3000 where id = 1";
    //4. Get the object Statement executing sql
    Statement stmt = conn.createStatement();
    //5. Execute sql
    int count = stmt.executeUpdate(sql);//The number of rows affected after the execution of DML statement
    //6. Treatment results
    //System.out.println(count);
    if(count > 0){
        System.out.println("Modified successfully~");
    }else{
        System.out.println("Modification failed~");
    }
    //7. Release resources
    stmt.close();
    conn.close();
}

Execute DDL statement

/**
  * Execute DDL statement
  * @throws Exception
  */
@Test
public void testDDL() throws  Exception {
    //1. Register driver
    //Class.forName("com.mysql.jdbc.Driver");
    //2. Get connection: if the connection is local mysql and the port is the default 3306, writing can be simplified
    String url = "jdbc:mysql:///db1?useSSL=false";
    String username = "root";
    String password = "1234";
    Connection conn = DriverManager.getConnection(url, username, password);
    //3. Define sql
    String sql = "drop database db2";
    //4. Get the object Statement executing sql
    Statement stmt = conn.createStatement();
    //5. Execute sql
    int count = stmt.executeUpdate(sql);//After executing DDL statement, it may be 0
    //6. Treatment results
    System.out.println(count);

    //7. Release resources
    stmt.close();
    conn.close();
}

be careful:

Later development rarely uses java code to operate DDL statements

3.4 ResultSet

3.4.1 general

ResultSet (result set object) functions:

Encapsulates the results of SQL query statements.

After executing the DQL statement, the object will be returned. The corresponding method of executing the DQL statement is as follows:

ResultSet  executeQuery(sql): implement DQL Statement, return ResultSet object

Then we need to get the data we want from the ResultSet object. The ResultSet object provides methods to operate query result data, as follows:

boolean next()

Moves the cursor forward one line from the current position
Judge whether the current line is a valid line

Method return value Description:

true: valid data. There is data in the current line
false: invalid row. There is no data in the current row

XXX getxxx (parameter): get data

xxx: data type; For example: int getInt (parameter); String getString (parameter)
parameter
- Parameter of type int: the number of the column, starting from 1
- Parameter of String type: the name of the column

The following figure shows the results after executing the SQL statement

At the beginning, the cursor is assigned in front of the first line, as shown in the figure, and the red arrow points to the header line. When we call the next() method, the cursor moves down to the first row of data, and the method returns true. At this time, we can get the value of the id field of the current row through getInt("id"), or get the value of the name field of the current row through getString("name"). If you want to get the data of the next row, continue to call the next() method, and so on.

3.4.2 code implementation

/**
  * Execute DQL
  * @throws Exception
  */
@Test
public void testResultSet() throws  Exception {
    //1. Register driver
    //Class.forName("com.mysql.jdbc.Driver");
    //2. Get connection: if the connection is local mysql and the port is the default 3306, writing can be simplified
    String url = "jdbc:mysql:///db1?useSSL=false";
    String username = "root";
    String password = "1234";
    Connection conn = DriverManager.getConnection(url, username, password);
    //3. Define sql
    String sql = "select * from account";
    //4. Get the statement object
    Statement stmt = conn.createStatement();
    //5. Execute sql
    ResultSet rs = stmt.executeQuery(sql);
    //6. Process the result and traverse all data in rs
    /* // 6.1 Move the cursor down one line and judge whether there is data in the current line
        while (rs.next()){
            //6.2 Get data getXxx()
            int id = rs.getInt(1);
            String name = rs.getString(2);
            double money = rs.getDouble(3);

            System.out.println(id);
            System.out.println(name);
            System.out.println(money);

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

        }*/
    // 6.1 move the cursor down one line and judge whether there is data in the current line
    while (rs.next()){
        //6.2 get data (getxxx)
        int id = rs.getInt("id");
        String name = rs.getString("name");
        double money = rs.getDouble("money");

        System.out.println(id);
        System.out.println(name);
        System.out.println(money);

        System.out.println("--------------");
    }

    //7. Release resources
    rs.close();
    stmt.close();
    conn.close();
}

3.5 cases

Requirement: query the account table data, encapsulate it in the account object, and store it in the ArrayList collection

code implementation

/**
  * Query the account table data, encapsulate it in the account object, and store it in the ArrayList collection
  * 1. Define entity class Account
  * 2. Query the data and encapsulate it into the Account object
  * 3. Store the Account object in the ArrayList collection
  */
@Test
public void testResultSet2() throws  Exception {
    //1. Register driver
    //Class.forName("com.mysql.jdbc.Driver");
    //2. Get connection: if the connection is local mysql and the port is the default 3306, writing can be simplified
    String url = "jdbc:mysql:///db1?useSSL=false";
    String username = "root";
    String password = "1234";
    Connection conn = DriverManager.getConnection(url, username, password);

    //3. Define sql
    String sql = "select * from account";

    //4. Get the statement object
    Statement stmt = conn.createStatement();

    //5. Execute sql
    ResultSet rs = stmt.executeQuery(sql);

    // Create collection
    List<Account> list = new ArrayList<>();
   
    // 6.1 move the cursor down one line and judge whether there is data in the current line
    while (rs.next()){
        Account account = new Account();

        //6.2 get data (getxxx)
        int id = rs.getInt("id");
        String name = rs.getString("name");
        double money = rs.getDouble("money");

        //assignment
        account.setId(id);
        account.setName(name);
        account.setMoney(money);

        // Save to collection
        list.add(account);
    }

    System.out.println(list);

    //7. Release resources
    rs.close();
    stmt.close();
    conn.close();
}

3.6 PreparedStatement

PreparedStatement functions:

Precompiling and executing SQL statements: preventing SQL injection problems

We certainly don't understand the problem of SQL injection in the above functions. Let's explain SQL injection first

3.6.1 SQL injection

SQL injection is a method of modifying pre-defined SQL statements through operation input to attack the server by executing code.

Modify application The user name and password in the properties file are as follows:

spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/test?useSSL=false&useUnicode=true&characterEncoding=UTF-8
spring.datasource.username=root
spring.datasource.password=1234

Create a database named test in MySQL

create database test;

SQL. Running from the command prompt Jar this jar package.

At this point, we can see the user table in the database

Next, enter localhost: 8080 / login in the address bar of the browser HTML can see the following page

We can enter the user name and password in the figure above to log in. If the user name and password are entered correctly, the login is successful and jump to the home page. If the user name and password are entered incorrectly, an error prompt will be given, as shown in the following figure

But I can log in to the home page by entering some special characters.

The user name is written at will, and the password is written as' or '1' ='1 '

This is the SQL injection vulnerability, which is also very dangerous. Of course, there will be no such problem in the systems on the market now, so we should not try to try other systems in this way.

So how to solve it? Here, you can replace the SQL execution object Statement with the PreparedStatement object.

3.6.2 code simulation SQL injection

@Test
public void testLogin() throws  Exception {
    //2. Get connection: if the connection is local mysql and the port is the default 3306, writing can be simplified
    String url = "jdbc:mysql:///db1?useSSL=false";
    String username = "root";
    String password = "1234";
    Connection conn = DriverManager.getConnection(url, username, password);

    // Receive user input user name and password
    String name = "sjdljfld";
    String pwd = "' or '1' = '1";
    String sql = "select * from tb_user where username = '"+name+"' and password = '"+pwd+"'";
    // Get stmt object
    Statement stmt = conn.createStatement();
    // Execute sql
    ResultSet rs = stmt.executeQuery(sql);
    // Judge whether the login is successful
    if(rs.next()){
        System.out.println("Login successful~");
    }else{
        System.out.println("Login failed~");
    }

    //7. Release resources
    rs.close();
    stmt.close();
    conn.close();
}

The above code splices the user name and password into the sql statement. The spliced sql statement is as follows

select * from tb_user where username = 'sjdljfld' and password = ''or '1' = '1'

It can be seen from the above statement that the condition username = 'sjdljfld' and password = '' is satisfied or not, and the '1' = '1' after or is always satisfied. If the final condition is established, you can log in normally.

Next, let's learn about the PreparedStatement object

3.6.3 PreparedStatement overview

PreparedStatement functions:

Precompiling and executing SQL statements: preventing SQL injection problems

Gets the PreparedStatement object

// Parameter values in SQL statements, using? Placeholder substitution
String sql = "select * from user where username = ? and password = ?";
// Get through the Connection object and pass in the corresponding sql statement
PreparedStatement pstmt = conn.prepareStatement(sql);

Set parameter value

In the above sql statement, the parameter use? Do you have to set these before occupying? Value of.

PreparedStatement object: setXXX (parameter 1, parameter 2): to? assignment
- Xxx: data type; Such as setInt (parameter 1, parameter 2)
- Parameters:
  - Parameter 1:? Position number of the, starting from 1
  - Parameter 2:? Value of
Execute SQL statement

executeUpdate(); Execute DDL and DML statements

executeQuery(); Execute DQL statement

be careful:
- When calling these two methods, you do not need to pass the SQL statement, because the SQL statement has been precompiled when obtaining the execution object of the SQL statement.

3.6.4 using PreparedStatement to improve

 @Test
public void testPreparedStatement() throws  Exception {
    //2. Get connection: if the connection is local mysql and the port is the default 3306, writing can be simplified
    String url = "jdbc:mysql:///db1?useSSL=false";
    String username = "root";
    String password = "1234";
    Connection conn = DriverManager.getConnection(url, username, password);

    // Receive user input user name and password
    String name = "zhangsan";
    String pwd = "' or '1' = '1";

    // Define sql
    String sql = "select * from tb_user where username = ? and password = ?";
    // Get pstmt object
    PreparedStatement pstmt = conn.prepareStatement(sql);
    // set up? Value of
    pstmt.setString(1,name);
    pstmt.setString(2,pwd);
    // Execute sql
    ResultSet rs = pstmt.executeQuery();
    // Judge whether the login is successful
    if(rs.next()){
        System.out.println("Login successful~");
    }else{
        System.out.println("Login failed~");
    }
    //7. Release resources
    rs.close();
    pstmt.close();
    conn.close();
}

By executing the above statement, you can find that there will be no SQL injection vulnerability. So how is PreparedStatement solved? It is used to escape special characters. The escape SQL is as follows:

select * from tb_user where username = 'sjdljfld' and password = '\'or \'1\' = \'1'

3.6.5 PreparedStatement principle

PreparedStatement benefits:

Precompiled SQL for better performance
Prevent SQL injection: Escape sensitive characters

The process of Java code operating database is shown in the figure below:

Send sql statements to MySQL server
MySQL server will perform the following operations on sql statements
- Check SQL statements
  
  Check whether the syntax of the SQL statement is correct.
- Compile SQL statements. Compile SQL statements into executable functions.
  
  Checking and compiling SQL takes longer than executing SQL. If we just reset the parameters, the check SQL statement and compile SQL statement will not need to be executed repeatedly. This improves performance.
- Execute SQL statement

Next, let's look at the principle by querying the log.

Enable precompiling

When writing the url in the code, you need to add the following parameters. We didn't turn on the precompile function before, but we just solved the SQL injection vulnerability.
```
useServerPrepStmts=true
```
Configure MySQL execution log (effective after restarting MySQL service)

Add the following configuration in the mysql configuration file (my.ini)
```
log-output=FILE
general-log=1
general_log_file="D:\mysql.log"
slow-query-log=1
slow_query_log_file="D:\mysql_slow.log"
long_query_time=2
```

The java test code is as follows:

 /**
   * PreparedStatement principle
   * @throws Exception
   */
@Test
public void testPreparedStatement2() throws  Exception {

    //2. Get connection: if the connection is local mysql and the port is the default 3306, writing can be simplified
    // The useServerPrepStmts=true parameter enables precompiling
    String url = "jdbc:mysql:///db1?useSSL=false&useServerPrepStmts=true";
    String username = "root";
    String password = "1234";
    Connection conn = DriverManager.getConnection(url, username, password);

    // Receive user input user name and password
    String name = "zhangsan";
    String pwd = "' or '1' = '1";

    // Define sql
    String sql = "select * from tb_user where username = ? and password = ?";

    // Get pstmt object
    PreparedStatement pstmt = conn.prepareStatement(sql);

    Thread.sleep(10000);
    // set up? Value of
    pstmt.setString(1,name);
    pstmt.setString(2,pwd);
    ResultSet rs = null;
    // Execute sql
    rs = pstmt.executeQuery();

    // set up? Value of
    pstmt.setString(1,"aaa");
    pstmt.setString(2,"bbb");
    // Execute sql
    rs = pstmt.executeQuery();

    // Judge whether the login is successful
    if(rs.next()){
        System.out.println("Login successful~");
    }else{
        System.out.println("Login failed~");
    }

    //7. Release resources
    rs.close();
    pstmt.close();
    conn.close();
}

Execute the SQL statement and view D: \ mysql The log is as follows:

Prepare in the third line in the above figure is to precompile the SQL statement. The fourth and fifth lines execute the SQL statement twice, and the SQL is not precompiled before the second execution.

Summary:

When obtaining the PreparedStatement object, send the sql statement to the mysql server for inspection and compilation (these steps are time-consuming)
These steps are no longer needed when executing, and the speed is faster
If the sql template is the same, you only need to check and compile it once

4. Database connection pool

4.1 introduction to database connection pool

Database connection pool is a container that is responsible for allocating and managing database connections
It allows applications to reuse an existing database connection instead of re establishing one;
Release the database connection whose idle time exceeds the maximum idle time to avoid database connection omission caused by not releasing the database connection
benefit
- resource reuse
- Improve system response speed
- Avoid missing database connections

Previously, we created a Connection object without using the Connection in our code, and it will be destroyed after use. This repeated creation and destruction process is particularly time-consuming and time-consuming.

After the database uses the database Connection pool, the Connection object can be reused, as shown in the following figure

Connection pool is to store some connection objects created at the beginning. When users need to connect to the database, they do not need to create their own connection, but only need to obtain a connection from the connection pool for use, and return the connection object to the connection pool after use; In this way, resources can be reused, and the time spent on frequent connection creation and destruction can be saved, so as to improve the speed of system response.

4.2 implementation of database connection pool

DataSource: standard interface

The standard interface of database connection pool provided by sun, which is implemented by a third-party organization. This interface provides the function of obtaining connection:
```
Connection getConnection()
```
In the future, you do not need to obtain the Connection object through the DriverManager object, but through the Connection pool (DataSource).
Common database connection pools
- DBCP
- C3P0
- Druid
We now use Druid more, and its performance will be better than the other two.
Druid (Druid)
- Druid connection pool is an open source database connection pool project of Alibaba
- With powerful functions and excellent performance, it is one of the best database connection pools in Java language

4.3 Driud usage

Import the jar package druid-1.1.12 jar
Define profile
Load profile
Get database connection pool object
Get connection

Now through code implementation, you need to first put the druid jar package under lib under the project and add it as a library file

The project structure is as follows:

Write the configuration file as follows:

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql:///db1?useSSL=false&useServerPrepStmts=true
username=root
password=1234
# Number of initialization connections
initialSize=5
# maximum connection
maxActive=10
# Maximum waiting time
maxWait=3000

The code for using druid is as follows:

/**
 * Druid Database connection pool demo
 */
public class DruidDemo {

    public static void main(String[] args) throws Exception {
        //1. Import jar package
        //2. Define profile
        //3. Load configuration file
        Properties prop = new Properties();
        prop.load(new FileInputStream("jdbc-demo/src/druid.properties"));
        //4. Get connection pool object
        DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);

        //5. Get database Connection
        Connection connection = dataSource.getConnection();
        System.out.println(connection); //After obtaining the connection, you can continue to do other operations

        //System.out.println(System.getProperty("user.dir"));
    }
}

4.3.1. druid configuration details

attribute	explain	recommended value
url	The jdbc connection address of the database. It is generally connected to oracle/mysql. Examples are as follows:
	mysql : jdbc:mysql://ip:port/dbname?option1&option2&...
	oracle : jdbc:oracle:thin:@ip:port:oracle_sid

username	User name to log in to the database
password	User password to log in to the database
initialSize	How many connections are initialized in the connection pool when the program is started	10-50 is enough
maxActive	How many active sessions are supported in the connection pool
maxWait	When the program requests a connection from the connection pool, after exceeding the value of maxWait, it is considered that the request fails, that is, the connection pool	100
	There is no connection available, in milliseconds. Setting - 1 indicates infinite wait
minEvictableIdleTimeMillis	After the idle time of a connection in the pool reaches N milliseconds, the connection pool will check the idle connection next time	See the description section
	Recycle the connection, which should be less than the firewall timeout setting
	net.netfilter.nf_conntrack_tcp_timeout_established settings
timeBetweenEvictionRunsMillis	The frequency of checking idle connections, in milliseconds. Non positive integers mean no checking
keepAlive	If the program has no close connection and the idle time exceeds minEvictableIdleTimeMillis, it will execute	true
	Line the SQL specified by validationQuery to ensure that the program connection pool will not be kill ed, and its range does not exceed
	The number of connections specified by minIdle is exceeded.
minIdle	When reclaiming idle connections, at least minIdle connections will be guaranteed	Same as initialSize
removeAbandoned	It is required that the program must close after N seconds after get ting the connection from the pool, otherwise the druid will forcibly recycle the connection	false, when the program is not found
	Connection, regardless of whether the connection is active or idle, to prevent the process from seizing the connection without closing.	Set to true during normal close connection
removeAbandonedTimeout	Set the time limit for druid to forcibly recycle the connection. When the program get s the connection from the pool, it exceeds this limit	It should be longer than the longest running time of the business
	Value, druid forces the connection to be reclaimed in seconds.
logAbandoned	Whether to record stack trace in the log after druid forcibly reclaims the connection	true
testWhileIdle	When a program requests a connection, does the pool first check whether the connection is valid when allocating the connection. (high efficiency)	true
validationQuery	Check whether the connection in the pool is still available. drui will connect to the database and execute the SQL. If
	If it returns normally, it indicates that the connection is available; otherwise, it indicates that the connection is unavailable
testOnBorrow	When the program applies for a connection, it checks the effectiveness of the connection (inefficient, affecting performance)	false
testOnReturn	When the program returns the connection, check the connection validity (low efficiency, affecting performance)	false
poolPreparedStatements	Cache SQL initiated by the following two methods:	true
	public PreparedStatement prepareStatement(String sql)
	public PreparedStatement prepareStatement(String sql,
	int resultSetType, int resultSetConcurrency)
maxPoolPrepareStatementPerConnectionSize	How many SQL are cached per connection	20
filters	Plug ins are configured here. Common plug-ins are:	stat,wall,slf4j
	Monitoring statistics: filter:stat
	Log monitoring: filter:log4j or slf4j
	Defensive SQL injection: filter:wall
connectProperties	Connection properties. For example, set some configuration of connection pool statistics.
	druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
	For example, set some database connection properties:

5. JDBC practice

5.1 requirements

Complete the addition, deletion, modification and query of commodity brand data

Query: query all data
Add: add brand
Modify: modify by id
Delete: delete by id

5.2 case realization

5.2.1 environmental preparation

Database table tb_brand

-- delete tb_brand surface
drop table if exists tb_brand;
-- establish tb_brand surface
create table tb_brand (
    -- id Primary key
    id int primary key auto_increment,
    -- Brand name
    brand_name varchar(20),
    -- Enterprise name
    company_name varchar(20),
    -- sort field
    ordered int,
    -- Description information
    description varchar(100),
    -- Status: 0: Disabled 1: enabled
    status int
);
-- Add data
insert into tb_brand (brand_name, company_name, ordered, description, status)
values ('Three squirrels', 'Three squirrels Co., Ltd', 5, 'Delicious but not hot', 0),
       ('Huawei', 'Huawei Technology Co., Ltd', 100, 'Huawei is committed to bringing the digital world into everyone, every family and every organization to build an interconnected intelligent world', 1),
       ('millet', 'Xiaomi Technology Co., Ltd', 50, 'are you ok', 1);

Entity class Brand under pojo package

/**
 * brand
 * alt + Left mouse button edit: entire column
 * In the entity class, it is recommended to use its corresponding wrapper type for the basic data type
 */
public class Brand {
    // id primary key
    private Integer id;
    // Brand name
    private String brandName;
    // Enterprise name
    private String companyName;
    // sort field
    private Integer ordered;
    // Description information
    private String description;
    // Status: 0: Disabled 1: enabled
    private Integer status;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getBrandName() {
        return brandName;
    }

    public void setBrandName(String brandName) {
        this.brandName = brandName;
    }

    public String getCompanyName() {
        return companyName;
    }

    public void setCompanyName(String companyName) {
        this.companyName = companyName;
    }

    public Integer getOrdered() {
        return ordered;
    }

    public void setOrdered(Integer ordered) {
        this.ordered = ordered;
    }

    public String getDescription() {
        return description;
    }

    public void setDescription(String description) {
        this.description = description;
    }

    public Integer getStatus() {
        return status;
    }

    public void setStatus(Integer status) {
        this.status = status;
    }

    @Override
    public String toString() {
        return "Brand{" +
                "id=" + id +
                ", brandName='" + brandName + '\'' +
                ", companyName='" + companyName + '\'' +
                ", ordered=" + ordered +
                ", description='" + description + '\'' +
                ", status=" + status +
                '}';
    }
}

5.2.2 query all

 /**
   * Query all
   * 1. SQL: select * from tb_brand;
   * 2. Parameter: not required
   * 3. Results: List < brand >
   */

@Test
public void testSelectAll() throws Exception {
    //1. Get Connection
    //3. Load configuration file
    Properties prop = new Properties();
    prop.load(new FileInputStream("jdbc-demo/src/druid.properties"));
    //4. Get connection pool object
    DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);

    //5. Get database Connection
    Connection conn = dataSource.getConnection();
    //2. Define SQL
    String sql = "select * from tb_brand;";
    //3. Get pstmt object
    PreparedStatement pstmt = conn.prepareStatement(sql);
    //4. Set parameters
    //5. Execute SQL
    ResultSet rs = pstmt.executeQuery();
    //6. The processing result List < Brand > encapsulates the Brand object and loads the List set
    Brand brand = null;
    List<Brand> brands = new ArrayList<>();
    while (rs.next()){
        //get data
        int id = rs.getInt("id");
        String brandName = rs.getString("brand_name");
        String companyName = rs.getString("company_name");
        int ordered = rs.getInt("ordered");
        String description = rs.getString("description");
        int status = rs.getInt("status");
        //Encapsulate Brand objects
        brand = new Brand();
        brand.setId(id);
        brand.setBrandName(brandName);
        brand.setCompanyName(companyName);
        brand.setOrdered(ordered);
        brand.setDescription(description);
        brand.setStatus(status);

        //Mount collection
        brands.add(brand);
    }
    System.out.println(brands);
    //7. Release resources
    rs.close();
    pstmt.close();
    conn.close();
}

5.2.3 adding data

/**
  * add to
  * 1. SQL: insert into tb_brand(brand_name, company_name, ordered, description, status) values(?,?,?,?,?);
  * 2. Parameter: all parameter information except id is required
  * 3. Result: boolean
  */
@Test
public void testAdd() throws Exception {
    // Receive parameters submitted by the page
    String brandName = "Fragrance floating";
    String companyName = "Fragrance floating";
    int ordered = 1;
    String description = "Circle the earth";
    int status = 1;

    //1. Get Connection
    //3. Load configuration file
    Properties prop = new Properties();
    prop.load(new FileInputStream("jdbc-demo/src/druid.properties"));
    //4. Get connection pool object
    DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
    //5. Get database Connection
    Connection conn = dataSource.getConnection();
    //2. Define SQL
    String sql = "insert into tb_brand(brand_name, company_name, ordered, description, status) values(?,?,?,?,?);";
    //3. Get pstmt object
    PreparedStatement pstmt = conn.prepareStatement(sql);
    //4. Set parameters
    pstmt.setString(1,brandName);
    pstmt.setString(2,companyName);
    pstmt.setInt(3,ordered);
    pstmt.setString(4,description);
    pstmt.setInt(5,status);

    //5. Execute SQL
    int count = pstmt.executeUpdate(); // Number of rows affected
    //6. Treatment results
    System.out.println(count > 0);

    //7. Release resources
    pstmt.close();
    conn.close();
}

5.2.4 modifying data

/**
  * modify
  * 1. SQL: 

     update tb_brand
         set brand_name  = ?,
         company_name= ?,
         ordered     = ?,
         description = ?,
         status      = ?
     where id = ?

   * 2. Parameters: required, all data
   * 3. Result: boolean
   */

@Test
public void testUpdate() throws Exception {
    // Receive parameters submitted by the page
    String brandName = "Fragrance floating";
    String companyName = "Fragrance floating";
    int ordered = 1000;
    String description = "Three circles around the earth";
    int status = 1;
    int id = 4;

    //1. Get Connection
    //3. Load configuration file
    Properties prop = new Properties();
    prop.load(new FileInputStream("jdbc-demo/src/druid.properties"));
    //4. Get connection pool object
    DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
    //5. Get database Connection
    Connection conn = dataSource.getConnection();
    //2. Define SQL
    String sql = " update tb_brand\n" +
        "         set brand_name  = ?,\n" +
        "         company_name= ?,\n" +
        "         ordered     = ?,\n" +
        "         description = ?,\n" +
        "         status      = ?\n" +
        "     where id = ?";

    //3. Get pstmt object
    PreparedStatement pstmt = conn.prepareStatement(sql);

    //4. Set parameters
    pstmt.setString(1,brandName);
    pstmt.setString(2,companyName);
    pstmt.setInt(3,ordered);
    pstmt.setString(4,description);
    pstmt.setInt(5,status);
    pstmt.setInt(6,id);

    //5. Execute SQL
    int count = pstmt.executeUpdate(); // Number of rows affected
    //6. Treatment results
    System.out.println(count > 0);

    //7. Release resources
    pstmt.close();
    conn.close();
}

5.2.5 deleting data

/**
  * delete
  * 1. SQL: 
            delete from tb_brand where id = ?
  * 2. Parameter: required, id
  * 3. Result: boolean
  */
@Test
public void testDeleteById() throws Exception {
    // Receive parameters submitted by the page
    int id = 4;
    //1. Get Connection
    //3. Load configuration file
    Properties prop = new Properties();
    prop.load(new FileInputStream("jdbc-demo/src/druid.properties"));
    //4. Get connection pool object
    DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
    //5. Get database Connection
    Connection conn = dataSource.getConnection();
    //2. Define SQL
    String sql = " delete from tb_brand where id = ?";
    //3. Get pstmt object
    PreparedStatement pstmt = conn.prepareStatement(sql);
    //4. Set parameters
    pstmt.setInt(1,id);
    //5. Execute SQL
    int count = pstmt.executeUpdate(); // Number of rows affected
    //6. Treatment results
    System.out.println(count > 0);

    //7. Release resources
    pstmt.close();
    conn.close();
}

Topics: Java Database Oracle JDBC

Programmer Think