One. Remember to build the connection foundation before getting the database
1. driver 2. connection 3. statement 4. resultSet

//1
Class.forName(ConfigUtil.getValue("driver"));
//2
Connection connection =  DriverManager.getConnection("jdbc:mysql://localhost:3306/resource_integrate","root","root");//The three parameters are the address of the database (resource · integrate is the database name), the database user name, and the database password
//3
Statement statement = connection.createStatement();
//4
ResultSet resultSet = statement.executeQuery("SELECT * FROM software ");

Then you can get the data through the resultSet

softwares = new ArrayList<>();
while (resultSet.next()) {
    Software software = new Software();
    software.setName(resultSet.getString("name"));
    software.setUrl(resultSet.getString("url"));
    software.setPassword(resultSet.getString("password"));
    software.setTime(resultSet.getString("time"));
    softwares.add(software);
}

The parameters of the above four steps can be written to a properties file, and then a util class can be written to obtain these parameters

Filename: jdbc.properties

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/resource_integrate
username=root
password=root

File name: ConfigUtil.java

/*
* Get the data in the properties file
* */
public class ConfigUtil {
    //Get value through key
    public static String getValue(String key){
        Properties properties = new Properties();
        InputStream inputStream = ConfigUtil.class.getResourceAsStream("/jdbc.properties");
        String value = "";
        try {
            properties.load(inputStream);
            value = properties.getProperty(key);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return value;
    }
}

Two. Improvement: fill in the addition, deletion, modification and query to another file, and use the next time to retrieve directly.
BaseDao.java

public class BaseDao {
     Connection connection = null;
     PreparedStatement statement = null;
     ResultSet resultSet = null;

    public BaseDao(String sql) {
        try {
            Class.forName(ConfigUtil.getValue("driver"));
            connection =  DriverManager.getConnection(ConfigUtil.getValue("url"),ConfigUtil.getValue("username"),ConfigUtil.getValue("password"));
            statement = connection.prepareStatement(sql);
        } catch (ClassNotFoundException e) {

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

    //Get statement


    public PreparedStatement getStatement() {
         return statement;
    }

    public void close(){
        try {
            if(resultSet!=null)
                resultSet.close();
            if(statement!=null)
                statement.close();
            if(connection!=null)
                connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

SoftwareDao.java

              resultSet.close();
                if(statement!=null)
                    statement.close();
                if(connection!=null)
                    connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return softwares;
    }

    //Add a software
    public int insertSoftware(Software software){
        String sql = "INSERT INTO software(name,url,password,time) VALUES(?,?,?,?)";
        BaseDao baseDao = new BaseDao(sql);
        PreparedStatement statement = baseDao.getStatement();

        try {
            statement.setString(1,software.getName());
            statement.setString(2,software.getUrl());
            statement.setString(3,software.getPassword());
            if (software.getTime()!=null&&!software.getTime().equals(""))
                statement.setString(4,software.getTime());

            int result = statement.executeUpdate();
            return result;

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

        return 0;

    }

    //Modify software information
    public int update(Software software){
        String sql = "update software set name=?,url=?,password=?,time=? where id=?";
        BaseDao baseDao = new BaseDao(sql);
        PreparedStatement statement =  baseDao.getStatement();

        try {
            statement.setString(1,software.getName());
            statement.setString(2,software.getUrl());
            statement.setString(3,software.getPassword());
            statement.setString(4,software.getTime());
            statement.setString(5,software.getId()+"");
            int result = statement.executeUpdate();
            return result;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return  0;
    }

    //Delete software
    public int delete(int id){
        String sql = "delete from software where id="+id;
        BaseDao baseDao = new BaseDao(sql);
        PreparedStatement statement =  baseDao.getStatement();
        try {
            int result = statement.executeUpdate();
            return result;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return  0;
    }

}

Three. Improvements: using connection pools
Explanation: Although the above method can obtain the database, it needs to be connected and closed every time to obtain the database. If the amount of access becomes larger and the number of users increases, the pressure on the database will be too large, so the Connection pool method is used to improve. Getting the Connection object of each user connecting to the database from the Connection pool can effectively reduce the number of connections.
Detailed introduction: https://www.cnblogs.com/wang-meng/p/5463020.html
DBCPUtil .java

package javaweb.jdbc.util;

import org.apache.commons.dbcp2.BasicDataSourceFactory;
import org.junit.Test;

import javax.sql.DataSource;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Properties;

public class DBCPUtil {
    DataSource dataSource;
    public DBCPUtil() {
        Properties properties = new Properties();
        InputStream inputStream =  DBCPUtil.class.getResourceAsStream("/jdbc.properties");
        try {
            properties.load(inputStream);
            dataSource = BasicDataSourceFactory.createDataSource(properties);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    /*Get connection*/
    public Connection getConnection(){
        try {
            return dataSource.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
    /*Close connection*/
    public  void close(Connection connection){
        if (connection!=null){
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    @Test
    public void Test(){
        DBCPUtil dbcpUtil = new DBCPUtil();
        Connection connection = dbcpUtil.getConnection();
        String sql = "insert into software(name,url,password) values('qq','asjfka','123') ";
        try {
            PreparedStatement statement =  connection.prepareStatement(sql);
            statement.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

4: Improve dao layer and use java reflection mechanism

package javaweb.jdbc.dao;

import com.class1.model.Software;
import javaweb.jdbc.util.ConfigUtil;

import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;

/*Upgrade jdbc*/
public class BaseDao2 {
    Connection connection = null;
    PreparedStatement statement = null;
    ResultSet resultSet = null;

    public  Connection getConnection() {
        try {
            Class.forName(ConfigUtil.getValue("driver"));
            return connection = DriverManager.getConnection(ConfigUtil.getValue("url"),
                    ConfigUtil.getValue("username"), ConfigUtil.getValue("password"));
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            return null;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    /*Create a general method of adding, modifying and deleting*/
    public void update(String sql, Object[] params) {
        connection = getConnection();
        PreparedStatement statement = null;
        try {
            statement = connection.prepareStatement(sql);
            if (params != null && params.length > 0) {
                for (int i = 0; i < params.length; i++) {
                    statement.setObject(i + 1, params[i]);
                }
            }
            statement.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            close();
        }
    }
    /*Search for a single message*/
    public Object searchOne(String sql, Object[] params,Class<?> clz){
        connection = getConnection();
        Object bean = null;
        try {
            statement = connection.prepareStatement(sql);
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                bean= doResultSet(resultSet, clz);
            }
            return bean;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            close();
        }
        return null;
    }
    /*Process resultset, return single object*/
    public Object doResultSet(ResultSet resultSet,Class<?> clz){
        try {
            Object bean = clz.newInstance();
            ResultSetMetaData metaData =  resultSet.getMetaData();
            int count = metaData.getColumnCount();//There are several fields
            for(int i=0;i<count;i++) {
                String colName = metaData.getColumnName(i+1);//Field name
                Object value = resultSet.getObject(i+1);//field value
                Field field = clz.getDeclaredField(colName);
                field.setAccessible(true);
                field.set(bean,value);//Change the relevant field name in the bean object to the new data
            }
            return bean;
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (NoSuchFieldException e) {
            e.printStackTrace();
        }
        return  null;
    }


    /*Search multiple messages*/
    public List<?> searchList(String sql, Object[] params,Class<?> clz){
        connection = getConnection();
        List<Object> objects = null;
        try {
            statement = connection.prepareStatement(sql);
            if (params!=null&&params.length>0) {
                for (int i=0;i<params.length;i++) {
                    statement.setObject(i + 1, params[i]);
                }
            }
            resultSet = statement.executeQuery();
            objects = doResultSetList(resultSet, clz);
            return objects;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            close();
        }
        return null;
    }
    /*Process resultset, return multiple objects*/
    public List<Object> doResultSetList(ResultSet resultSet,Class<?> clz){
        List<Object> objects = new ArrayList<Object>();
        try {
            while (resultSet.next()) {
                Object bean = clz.newInstance();
                ResultSetMetaData metaData = resultSet.getMetaData();
                int count = metaData.getColumnCount();//There are several fields
                for (int i = 0; i < count; i++) {
                    String colName = metaData.getColumnName(i + 1);//Field name
                    Object value = resultSet.getObject(i + 1);//field value
                    Field field = clz.getDeclaredField(colName);
                    field.setAccessible(true);
                    field.set(bean, value);//Change the relevant field name in the bean object to the new data
                }
                objects.add(bean);
            }
            return objects;
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (NoSuchFieldException e) {
            e.printStackTrace();
        }
        return  null;
    }
    /*Close object*/
    public void close() {
        try {
            if (resultSet != null)
                resultSet.close();
            if (statement != null)
                statement.close();
            if (connection != null)
                connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Five. Using c3p0 thread pool optimization
Fast start c3p0: https://www.cnblogs.com/ygj0930/p/6405861.html