How to optimize database access in Java backend function development?

How to optimize database access in Java backend function development?

Overview:
In Java back-end development, database access is a very important link. Optimizing database access can improve system performance and responsiveness. This article will introduce some common techniques for optimizing database access in the development of Java back-end functions and provide corresponding code examples.

1. Reduce the creation and destruction of database connections
   In Java, creating a database connection is a very time-consuming operation. Therefore, we can reduce the number of connection creation and destruction by using a database connection pool. The connection pool can create a certain number of connections when the application starts, obtain connections from the pool when needed, and return them to the pool after use for subsequent use.

The following is an example of using Apache Commons DBCP connection pool:

import org.apache.commons.dbcp2.BasicDataSource;

public class DatabaseUtil {
    private static final String URL = "jdbc:mysql://localhost:3306/mydb";
    private static final String USERNAME = "username";
    private static final String PASSWORD = "password";
    
    private static BasicDataSource dataSource;

    static {
        dataSource = new BasicDataSource();
        dataSource.setUrl(URL);
        dataSource.setUsername(USERNAME);
        dataSource.setPassword(PASSWORD);
        
        // 设置连接池的一些属性
        dataSource.setInitialSize(10);
        dataSource.setMaxTotal(100);
    }

    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }
}

In the code, we use a static code block to initialize the connection pool and set some properties of the connection pool . The getConnection() method is used to obtain a database connection. When you need to access the database, just call the getConnection() method to obtain a connection.

2. Using batch operations
   In some scenarios, we need to perform a large number of database operations. If each operation is sent to the database server, unnecessary network overhead will be caused. At this time, we can use the database's batch operation to merge multiple operations into one batch operation, and then send it to the database server for execution at one time.

The following is an example of batch operation using JDBC:

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

public class BatchUpdateExample {
    public void batchUpdate(List<User> userList) throws SQLException {
        Connection connection = DatabaseUtil.getConnection();
        String sql = "INSERT INTO user (name, age) VALUES (?, ?)";
        PreparedStatement statement = connection.prepareStatement(sql);

        for (User user : userList) {
            statement.setString(1, user.getName());
            statement.setInt(2, user.getAge());
            statement.addBatch();
        }

        statement.executeBatch();
        statement.close();
        connection.close();
    }
}

In the code, we first obtain the database connection, then create a PreparedStatement object and add the batch using the addBatch() method Operation statement. After adding all operations, call the executeBatch() method to perform batch operations, and finally close related resources.

3. Rational use of indexes
   The index of the database can greatly improve the speed of query. When designing database tables, we should create indexes reasonably based on business needs. Generally speaking, fields that are frequently used for querying and fields that are frequently used for sorting should be indexed.

The following is an example of creating an index:

CREATE INDEX idx_name ON user (name);

In the code, we use the CREATE INDEX statement to create an index on the name field of the user table.

4. Avoid using SELECT *
   In query statements, try to avoid using SELECT , especially when the table contains a large number of fields. SELECT will return data from all fields, which will cause a large amount of data exchange and unnecessary memory overhead. Only the required fields should be queried based on actual needs to reduce unnecessary data transmission and memory consumption.

The following is an example of using SELECT *:

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

public class SelectAllExample {
    public List<User> selectAll() throws SQLException {
        Connection connection = DatabaseUtil.getConnection();
        String sql = "SELECT * FROM user";
        PreparedStatement statement = connection.prepareStatement(sql);
        ResultSet resultSet = statement.executeQuery();

        List<User> userList = new ArrayList<>();
        while (resultSet.next()) {
            User user = new User();
            user.setId(resultSet.getInt("id"));
            user.setName(resultSet.getString("name"));
            user.setAge(resultSet.getInt("age"));
            userList.add(user);
        }

        resultSet.close();
        statement.close();
        connection.close();
        
        return userList;
    }
}

In the code, we use SELECT to query all fields of the user table and save the results to a List collection middle. If there are a large number of fields in the table, using SELECT will affect the performance of the query.

5. Use appropriate data types
   When designing database tables, you should choose appropriate data types based on actual needs. Selecting a data type that is too large wastes storage space, while selecting a data type that is too small may result in data loss or overflow. The right data type can meet business needs while minimizing storage space.

The following is an example of appropriate selection of data types:

CREATE TABLE user (
    id INT PRIMARY KEY,
    name VARCHAR(20),
    age TINYINT
);

In the code, we created fields of the user table using the INT, VARCHAR, and TINYINT data types respectively.

Summary:
By optimizing database access in the development of Java back-end functions, we can improve the performance and response speed of the system. When writing code, you need to pay attention to reducing the number of creation and destruction of database connections and use database connection pools; rationally use batch operations to reduce network overhead; rationally use indexes to increase query speed; avoid using SELECT * and only query required fields; Choose the appropriate data type to reduce storage space. I hope this article will help you optimize database access.

Reference:

• Apache Commons DBCP: http://commons.apache.org/proper/commons-dbcp/
• JDBC Batch Updates: https: //docs.oracle.com/javase/tutorial/jdbc/basics/batch.html
• How many indexes are too many? https://dev.mysql.com/doc/refman/8.0/en/ innodb-restrictions.html

The above is the detailed content of How to optimize database access in Java backend function development?. For more information, please follow other related articles on the PHP Chinese website!