10 JDBC Best Practices

JDBC is a unified access interface provided by Java for a variety of relational databases. The following are ten best practices I have summarized in my long-term use of JDBC.

JDBC Best Practice 1: Use prearedstatement

Any Java that has used JDBCProgramAlmost everyone knows this. preparedstatment can be pre-compiled to avoid SQL injection when splicing SQL statements.

JDBC best practice 2. Use connectionpool)

Using the connection pool as the best practice has almost become a recognized standard. Some frameworks have provided built-in connection pool support, such as the database connection pool in spring. If your application is deployed on the javaee application server, such as JBoss, was, these servers also have built-in connection pool support, such as DBCP. The reason for using the connection is simply because it takes a long time to create a JDBC connection. If a connection is re-opened and closed for each query, the performance will be very low, if a batch of connections are created and cached in advance, they are retrieved when used. If they are not used, they will not be closed, which will save a lot of time to create and close connections.

JDBC best practices 3. Disable Automatic submission

This best practice is very useful when we use JDBC for batch commit. After automatic commit is disabled, you can put a group of database operations in a transaction, in the automatic commit mode, each SQL statement is executed to execute its own transactions and committed at the end of execution.

JDBC best practices 4. Use batch update

JDBC APIs provide the addbatch () method to add SQL queries to batch, and then execute batch queries through executebatch. JDBC batch update can reduce the number of round-trips for database data transmission, thus improving performance.

JDBC best practice 5: Use a column name to retrieve data in the resultset to avoid invalidcolumindexerror

The query results in JDBC are encapsulated in resultset. You can obtain the queried data by column name or column number. If the input column number is incorrect, an invalidcolumindexexception is thrown, for example, if 0 is input, an error occurs because the column number in resultset starts from 1. In addition, if you change the column sequence in the data table, you do not have to change the JDBCCodeTo maintain the robustness of the program. Some Java programmers may say that accessing columns by serial numbers is faster than accessing column names. This is true, but for the robustness and readability of the program, we recommend that you use the column name for access.

JDBC Best Practice 6: bind variables instead of concatenating strings

In the first best practice, we have already said that using preparedstatment can prevent injection? Or other placeholders will also improve the performance, because the database can use different parameters to execute the same query, this best practice brings higher performance while also preventing SQL injection.

JDBC Best Practice 7: Remember to disable statement, preparedstatement, and connection

The usual practice is to close them in the Finally block. The advantage of this is that the resources can be released no matter whether the statement is executed correctly or not, whether an exception is thrown or not. In Java 7, you can use the automatic Resource Management block to automatically close resources.

JDBC best practice 8: select the appropriate JDBC driver

There are four JDBC drivers:

• The first JDBC driver is called the JDBC-ODBC bridge Driver (Bridge driver)
• The second JDBC driver is called Native-API/partly Java Driver (native driver)
• The third JDBC driver is alljava/net-Protocol Driver (middleware driver)
• The fourth JDBC driver is all Java/Native-Protocol Driver (pure Java driver)

JDBC best practice 9: Try to use standard SQL statements to avoid Database SQL support differences to some extent

The SQL syntax supported by database products of different database vendors may vary. To facilitate migration, we recommend that you use standard ansi SQL to write SQL statements.

JDBC Best Practice 10: use the correct getxxx () method

When reading data from the resultset, although JDBC allows you to use the getstring () and GetObject () methods to obtain any data type, we recommend that you use the correct getter method, this avoids data type conversion.