Java - JDBC Best Practices

分享一个大牛的人工智能教程。零基础!通俗易懂!风趣幽默!希望你也加入到人工智能的队伍中来!请点击http://www.captainbed.net

In this guide, I would like to explain JDBC Best Practices. We can follow these best practices in the day to day project work.

As I know, we basically prefer ORM framework to develop DAO layer like Hibernate ORM Framework but If you still using JDBC in your projects or if you are learning about JDBC then this post will guide you the best practices of JDBC.

1. Check ResultSet

Always check the return values of navigation methods (next, previous, first, last) of a ResultSet. If the value return is ‘false’, it should be handled properly.

Example:

Statement stat = conn.createStatement();
ResultSet rst = stat.executeQuery("SELECT name FROM person");
rst.next(); // what if it returns false? bad form
String firstName = rst.getString(1);

Statement stat = conn.createStatement();
ResultSet rst = stat.executeQuery("SELECT name FROM person");
if (rst.next()) { // result is properly examined and used
    String firstName = rst.getString(1);
} else  {
    // handle missing data
}

2. Use Prepared Statement

PreparedStatement gives more benefits over using Statement interface like

  • Prepared Statement is used for executing a precompiled SQL statement. java.sql.PreparedStatement is suitable for executing DML commands: SELECT, INSERT, UPDATE and DELETE
  • Prepared Statement is faster as compared to Statement because it is used for executing pre-compiled SQL statements. Hence, the same SQL query can be executed repeatedly in Prepared Statement which improves the performance.
  • PreparedStatement allows you to write a dynamic and parametric query.
  • PreparedStatement prevents SQL Injection attacks in Java.

Here is an example of how to use PreparedStatement in Java:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class PreparedStmtExample {

 // JDBC Driver Name & Database URL
 static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";  
 static final String JDBC_DB_URL = "jdbc:mysql://localhost:3306/tutorialDb";
 // JDBC Database Credentials
 static final String JDBC_USER = "root";
 static final String JDBC_PASS = "admin@123";
 public static void main(String[] args) {
     try {
          Class.forName(JDBC_DRIVER);  
          Connection connObj = DriverManager.getConnection(JDBC_DB_URL, JDBC_USER, JDBC_PASS);
          PreparedStatement prepStatement = connObj
         .prepareStatement("SELECT DISTINCT loan_type FROM bank_loans WHERE bank_name=?");
          prepStatement.setString(1, "Citibank");
          ResultSet resObj = prepStatement.executeQuery();
          while(resObj.next()) {
              System.out.println("Loan Type?= " + resObj.getString("loan_type"));
          }
      } catch (Exception sqlException) {
          sqlException.printStackTrace();
      }
  }
}

3. Use ConnectionPool

ConnectionPool as JDBC best practice has already gained recognition and it even becomes standard nowadays. There are a few choices when using the JDBC connection pool:

  • Developers can depend on application server if it supports this feature, generally, all the application servers support connection pools. Application server creates the connection pool on behalf of developers when it starts. Developers need to give properties like min, max and incremental sizes to the application server 
  • Developers can use JDBC 2.0 interfaces, for e.g. ConnectionPoolDataSource and PooledConnection if the driver implements these interfaces 
  • Developers can even create their own connection pool if they are not using any application server or JDBC 2.0 compatible driver. By using any of these options, one can increase the JDBC performance significantly

4. Disable Auto-Commit Mode

It’s recommended to run SQL query with auto-commit mode disable. 

The reason behind this JDBC best practice is that with auto-commit mode disabled we can group SQL Statement in one transaction, while in the case of auto-commit mode enabled every SQL statement runs on its own transaction and committed as soon as it finishes. So, always execute SQL queries with auto-commit mode disabled.

For example: In JDBC API, the Connection interface provides the setAutoCommit() , commit() and rollback() methods to perform transaction management.

The below diagram shows all the JDBC Connection interface transaction management methods:

JDBC Transaction Management
JDBC Transaction Management

5. Use JDBC Batch Statements

This is another JDBC best practice which is very popular among developers. JDBC API provides addBatch() method to add SQL queries into a batch and executeBatch() to send batch queries for execution.

The reason behind this JDBC best practices is that JDBC batch update potentially reduces the number of database roundtrip which again results in significant performance gain. So always use JDBC batch update for insertion and update queries.

In simple words, Batch statement sends multiple requests from Java to the database in just one call. Without Batch statements, multiple requests will be sent in multiple (one by one) calls to the database.

Here is a complete JDBC PreparedStatement Batch Insert Example:

import java.sql.BatchUpdateException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Arrays;

/**
 * Insert Batch operation using PreparedStatement Interface
 */
public class BatchInsertExample {

    public static void main(String[] args) {
        parameterizedBatchUpdate();
    }

    public static void printSQLException(SQLException ex) {
        for (Throwable e: ex) {
            if (e instanceof SQLException) {
                e.printStackTrace(System.err);
                System.err.println("SQLState: " + ((SQLException) e).getSQLState());
                System.err.println("Error Code: " + ((SQLException) e).getErrorCode());
                System.err.println("Message: " + e.getMessage());
                Throwable t = ex.getCause();
                while (t != null) {
                    System.out.println("Cause: " + t);
                    t = t.getCause();
                }
            }
        }
    }

    private static void parameterizedBatchUpdate() {
        String INSERT_USERS_SQL = "INSERT INTO users" + "  (id, name, email, country, password) VALUES " +
            " (?, ?, ?, ?, ?);";

        try (Connection connection = DriverManager
            .getConnection("jdbc:mysql://localhost:3306/mysql_database?useSSL=false", "root", "root");
            // Step 2:Create a statement using connection object
            PreparedStatement preparedStatement = connection.prepareStatement(INSERT_USERS_SQL)) {
            connection.setAutoCommit(false);

            preparedStatement.setInt(1, 20);
            preparedStatement.setString(2, "a");
            preparedStatement.setString(3, "a@gmail.com");
            preparedStatement.setString(4, "India");
            preparedStatement.setString(5, "secret");
            preparedStatement.addBatch();

            preparedStatement.setInt(1, 21);
            preparedStatement.setString(2, "b");
            preparedStatement.setString(3, "b@gmail.com");
            preparedStatement.setString(4, "India");
            preparedStatement.setString(5, "secret");
            preparedStatement.addBatch();

            preparedStatement.setInt(1, 22);
            preparedStatement.setString(2, "c");
            preparedStatement.setString(3, "c@gmail.com");
            preparedStatement.setString(4, "India");
            preparedStatement.setString(5, "secret");
            preparedStatement.addBatch();

            preparedStatement.setInt(1, 23);
            preparedStatement.setString(2, "d");
            preparedStatement.setString(3, "d@gmail.com");
            preparedStatement.setString(4, "India");
            preparedStatement.setString(5, "secret");
            preparedStatement.addBatch();

            int[] updateCounts = preparedStatement.executeBatch();
            System.out.println(Arrays.toString(updateCounts));
            connection.commit();
            connection.setAutoCommit(true);
        } catch (BatchUpdateException batchUpdateException) {
            printBatchUpdateException(batchUpdateException);
        } catch (SQLException e) {
            printSQLException(e);
        }
    }

    public static void printBatchUpdateException(BatchUpdateException b) {
        System.err.println("----BatchUpdateException----");
        System.err.println("SQLState:  " + b.getSQLState());
        System.err.println("Message:  " + b.getMessage());
        System.err.println("Vendor:  " + b.getErrorCode());
        System.err.print("Update counts:  ");
        int[] updateCounts = b.getUpdateCounts();

        for (int i = 0; i < updateCounts.length; i++) {
            System.err.print(updateCounts[i] + "   ");
        }
    }
}

Output:

[1, 1, 1, 1]

6. Access ResultSet Using Column Name Instead of Column Index

JDBC API allows accessing data returned by a SELECT query using ResultSet, which can further be accessed using either column name or column index.
This JDBC best practice suggests using column name over column index in order to avoid InvalidColumnIndexException which comes if the index of the column is incorrect, most common of them is 0, since ResultSet column Index starts from 1, zero is invalid.
Example:

ResultSet resultSetObj = prepStmtObj.executeQuery();
while(resultSetObj.next()) {
    // Avoid
    String result resultSetObj.getString(2); // passed index
    // Better
    String result resultSetObj.getString("columnName"); // passed columnName
}

7. Use Placeholders for Parameterization

In JDBC best practices, I would like to suggest using PreparedStatement in Java because of better performance. But performance can only be improved if the developer use bind variables denoted by ? or placeholders which allow the database to run the same query with a different parameter.
Below example demonstrates the use of placeholders with PreparedStatement:

prepStmt = con.prepareStatement("select * from EMPLOYEE where ID=?");
prepStmt.setInt(1, 8);

8. Always Close Statement, PreparedStatement, and Connection

Its common Java coding practice to close any resource in finally block or Java7 try-with-resources as soon as you are done with that.
JDBC Connection and other JDBC classes are a costly resource and should be closed in finally block or Java7 try-with-resources to ensure the release of connection even in case of any SQLException.

Let's demonstrate closing resources using Java7 try-with-resources with an example:

// Step 1: Establishing a Connection
try (Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/mysql_database?useSSL=false", "root", "root");

// Step 2:Create a statement using connection object
PreparedStatement preparedStatement = connection.prepareStatement(UPDATE_USERS_SQL)) {
    preparedStatement.setString(1, "Ram");
    preparedStatement.setInt(2, 1);

    // Step 3: Execute the query or update query
    preparedStatement.executeUpdate();
    } catch (SQLException e) {

    // print SQL exception information
    printSQLException(e);
    }

// Step 4: try-with-resource statement will auto close the connection.

9. Choose Suitable JDBC Driver for Your Application

There are 4 types of JDBC driver in Java and it can directly affect the performance of the DAO layer. Always use latest JDBC Driver if available and prefer type 4 native JDBC Driver.

10. Use Correct getxxx() Method

This is the last JDBC best practice in this article which suggests using correct getter while getting data from ResultSet to avoid data conversion even though JDBC allows to get any data type using getString() or getObject().

  • 1
    点赞
  • 0
    收藏
  • 打赏
    打赏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:书香水墨 设计师:CSDN官方博客 返回首页
评论

打赏作者

做人,最重要的就是开心嘛

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值