JDBC & database connection pool

1. JDBC overview

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.

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

1,4 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

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

The code is 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();
    }
}

2. Detailed explanation of JDBC API

2.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:

    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

2.2 Connection

Function of Connection object:

  • Gets the object that executes SQL
  • Management services

2.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.

2.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

    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

  • Rollback transaction

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();
    }
}

2.3 Statement

2.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.

2.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

2.4 ResultSet

2.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.

2.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();
}

2.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();
    }
    

2.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

2.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.

2.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'

2.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.

2.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'

2.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 SQL statement and check 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

3. Database connection pool

3.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.

3.2 implementation of database connection pool

  • Standard interface: DataSource

    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

3.3 Driud usage

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

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. JDBC practice

4.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

4.2 case realization

4.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 the whole 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 +
                    '}';
        }
    }
    

4.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. Parameter setting
    //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();
}

4.2.3 adding data

/**
  * add to
  * 1. SQL: insert into tb_brand(brand_name, company_name, ordered, description, status) values(?,?,?,?,?);
  * 2. Parameters: 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();
}

4.2.4 modify 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();
}

4.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();
}

Tags: SQL JDBC

Posted by simpli on Fri, 15 Apr 2022 19:44:58 +0930