Abstract: JDBC, namely Java Database Connectivity, Java Database connection. Is a Java API for executing SQL statements, which is a database connection specification in Java.

This article is excerpted from breakDraw’s 10 Questions to Avoid COMMON JDBC Pitfalls in Java.

Connection.setAutoCommit(boolean)

For transaction commits.

SetAutoCommit (true), all SQL executions executed are committed and run directly as a single transaction

SetAutoCommit (false), the commit will not run until conn.mit () is called

Q: setAutoCommit is true or false by default

A: The default is true.

Q: What are the disadvantages of setAutoCommit (True)?

A: If multiple SQL statements are executed at once, errors in the intermediate SQL will result in dirty data.

Q: After setAutoCommit(false), what happens if an error occurs and the catch is not rollBack in Connection?

A: The operating table will be locked, resulting in A database deadlock

fetchSize

FetchSize specifies the number of records fetched from the database each time a JDBC Statement reads data

  • The larger the fetchSize is, the larger the memory usage of the client is, the fewer times the database is read, and the faster the fetchSize is.

Q: What is the difference between fetchSize in Oracle and Mysql?

A: Oracle sends fetchSize to the client each time over the network, whereas MYSQL sends it all at once. Therefore, MYSQL fetchSize is an analog cursor.

PreparedStatement

Q: What are the advantages of Statement?

A:

PreparedStatement is a PreparedStatement that is faster and more efficient than Statement. Therefore, PreparedStatement is best used even if SQL has no parameters

2. Better readability and maintainability of code (compared to SQL concatenation)

3. PreparedStatement can prevent SQL injection attacks, but Statement cannot

Q: Is prepareStatement an implementation of the Statement interface?

A: prepareStatement is not an implementation, but an inherited interface

CallableStatement

  • CallableStatement inherits from PreparedStatement

  • The CallableStatement interface adds methods to call the stored procedure kernel functions and handle output parameters (INOUT).

  • That is, stored procedures use callableStatements

Connection Pool

Connection pool advantages:

1. Reduce the number of connection creation times

2. Faster overall system response speed

3. Unified connection management to reduce connection failures.

ResultSet

Purpose: Cache data result set

Statement st = conn. createStatement (int resultSetType, int resultSetConcurrency)
ResultSet rs = st.executeQuery(sqlStr)
Copy the code

Scroll, which means call.next() or.previous() or move to the corresponding row

ResultSetType sets the type of the ResultSet object to scrollable or unscrollable. The values are as follows:

  • Resultset.type_forward_only Can only scroll forward

  • Resultset.type_scroll_insensitive supports scrolling backwards and forwards and is insensitive to changes

  • Resultset.type_scroll_sensitive Supports forward and backward scrolling and is sensitive to changes

ResultSetConcurency is a ResultSet object that can be modified. The values are as follows:

  • ResultSet.CONCUR_READ_ONLY Parameter that is set to read-only.

  • ResultSet.CONCUR_UPDATABLE Set this parameter to a modifiable type.

Q: What is the closing sequence of Connection, Statement, and ResultSet?

A: ResultSet, Statement, and Connection.

Because this is cumbersome, it is best to use JDBC connection pooling, or try-with-resource

Database knowledge

Q: What is the ACID property of a transaction?

A:

  • A is atomicity, the behavior within A transaction executes at once or falls back

  • C) consistency a+ B = C, then a must change with b

  • I is for isolation isolated transaction isolation, which is the intermediate execution of a transaction that is not visible to another transaction.

  • D is For Durability After submitting I successfully, modifications do not change and will also be recorded.

Q: What are dirty reads, non-repeatable reads, and phantom reads?

A:

  • Dirty read: Data is updated, but not committed, and then another transaction reads the updated data. As a result, the transaction rolls back, causing the read data to be dirty

  • Unrepeatable read: a transaction reads data twice. As a result, the first read is inconsistent with the second read.

  • Phantom read: a transaction reads data twice, and the number of records for the second time is inconsistent.

Click to follow, the first time to learn about Huawei cloud fresh technology ~