I haven't used the native database connection method for a long time, and I almost forget it. I'll take notes for this article.

Later, we use some advanced frameworks to operate the database, such as dbutil + c3p0, hibernate, JDBC template, etc. the advanced framework is also built on the basis of the bottom layer.
So it's also important to operate the underlying database.

In general, jdbc native mode is divided into the following seven steps:

1. Import mysql package
2. Loading drive [reflection]
3. Get database Connection object
4. Create sql object
5. Execute sql command and return result set
6. Processing result set
7. Close the result set, close the session, and close the connection.

Note: my demo is built with maven, so the first step to import the JAR package has no comments in the code.

Don't talk too much, just go to the code:

package com.zone.db;

import java.sql.*;

/**
 * @ClassName JdbcUtils1
 * @Author ZONE
 * @Date 2019/7/8 11:37
 * @Version 1.0
 **/
public class JdbcUtils1 {
    //Static code blocks are used here (so-called static code blocks: execute as the class loads, and only once)
    static {//2. Loading drive
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
    public static Connection getConnection(){//3. Establish connection
        Connection connection=null;
        try {
            connection= DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/mydata?characterEncoding=utf-8","root","root");
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }
    public static void close(Connection connection, PreparedStatement preparedStatement, ResultSet resultSet){//close resource
        if(resultSet!=null){
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(preparedStatement!=null){
            try {
                preparedStatement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(connection!=null){
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

Here is the test, and the notes are also clear:

import com.zone.db.JdbcUtils1;
import com.zone.db.JdbcUtils2;
import org.junit.Test;

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

/**
 * @ClassName JdbcUtils1Test
 * @Author ZONE
 * @Date 2019/7/8 12:01
 * @Version 1.0
 **/
public class JdbcUtils1Test {
    @Test
    public void test(){
        String sql="select * from jingdongbook WHERE bookId<=?";
        //The condition here is just to demonstrate the following sentence: ps.setString(1,"1356605");
        //If there is no condition, you do not need to set the parameter. The first null is the position and the second null is the condition parameter.
        Connection connection= JdbcUtils1.getConnection();//At the time of execution, the driver loading is actually completed.
        //That is to say, there are two steps mentioned above: 2. Drive and load; 3. Get the connection object.
        PreparedStatement ps=null;
        ResultSet resultSet=null;

        try {
            ps=connection.prepareStatement(sql);//4. Create sql object
            ps.setString(1,"1356605");
            resultSet=ps.executeQuery();//5. Execute sql command and return result set
            while (resultSet.next()){//6. Processing result set
                System.out.println("bookName:"+resultSet.getString("bookName")+";BookPrice"+resultSet.getString("bookPrice"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        JdbcUtils1.close(connection,ps,resultSet);//Close connection
    }
    @Test
    public void test2(){
        InputStream inputStream=JdbcUtils1Test.class.getClassLoader().getResourceAsStream("db.properties");
        System.out.println(inputStream);
    }
}

The following optimization extracts parameters to db.properties:

driver = com.mysql.jdbc.Driver
url = jdbc:mysql://127.0.0.1:3306/mydata?characterEncoding=utf-8
username = root
password = root

package com.zone.db;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

/**
 * @ClassName JdbcUtils2
 * @Author ZONE
 * @Date 2019/7/813:44
 * @Version 1.0
 **/
public class JdbcUtils2 {
    private static Properties properties;
    static {
        properties=new Properties();
        //Using ClassLoader to load the properties configuration file to generate the corresponding input stream
        InputStream inputStream=JdbcUtils2.class.getClassLoader().getResourceAsStream("db.properties");
        //Using the properties object to load the input stream
        try {
            properties.load(inputStream);
            Class.forName(properties.getProperty("driver"));//Get the corresponding value
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    public static Connection getConnection(){
        Connection connection=null;
        try {
            connection= DriverManager.getConnection(properties.getProperty("url"),
                    properties.getProperty("username"),
                    properties.getProperty("password"));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }
    public static void close(Connection connection, PreparedStatement preparedStatement, ResultSet resultSet){//close resource
        if(resultSet!=null){
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(preparedStatement!=null){
            try {
                preparedStatement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(connection!=null){
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

Finally, test screenshot:

Original: https://blog.csdn.net/qq_37094660/article/details/95059896