1. What are the basic steps for JDBC to access the database?

(More interview question recommendations: java interview questions and answers)

Loading driver

Get the connection object Connection through the DriverManager object

Get the session through the connection object

Add, delete, modify and check data through the session, encapsulate the object

Close the resource

2. Talk about the difference between preparedStatement and Statement

Efficiency: Precompiled sessions are better than ordinary session objects. The database system will not compile the same sql statement again.

Security: It can effectively avoid sql injection attacks! SQL injection attack is to input some illegal special characters from the client, so that the server can still correctly construct the SQL statement when constructing it, thereby collecting program and server information and data.

For example:

“select * from t_user where userName = ‘” + userName + “ ’ and password =’” + password + “’”

If the user name and password are entered as '1' or '1'='1'; then the generated sql statement is:

“select * from t_user where userName = ‘1’ or ‘1’ =’1’  and password =’1’  or ‘1’=’1’

The where part of this statement does not play a role in data filtering.

3. Let’s talk about the concept of transactions and the steps of processing transactions in JDBC programming.

A transaction is a series of operations performed as a single logical unit of work.

A logical unit of work must have four properties, called atomicity, consistency, isolation, and durability (ACID) properties. Only in this way can it become a transaction

Transaction processing steps ：

conn.setAutoComit(false);Set the submission method to manual submission

conn.commit() commits the transaction

Exception occurs, rollback conn.rollback();

4. The principle of database connection pool. Why use connection pooling.

Database connection is a time-consuming operation, and the connection pool allows multiple operations to share a connection.

The basic idea of ??the database connection pool is to establish a "buffer pool" for database connections. Put a certain number of connections in the buffer pool in advance. When you need to establish a database connection, you only need to take one out of the "buffer pool" and put it back after use. We can prevent the system from endless connections to the database by setting the maximum number of connections in the connection pool. More importantly, we can monitor the number and usage of database connections through the connection pool management mechanism, providing a basis for system development, testing and performance adjustment.

The purpose of using the connection pool is to improve the management of database connection resources

(Related recommendations: java introductory tutorial)

5. Dirty reading of JDBC What is it? Which database isolation level prevents dirty reads?

When we use transactions, there may be a situation where a row of data has just been updated, and at the same time another query reads the newly updated value. This leads to dirty reading, because the updated data has not been persisted, and the business that updated this row of data may be rolled back, so the data is invalid. The database's TRANSACTIONREADCOMMITTED, TRANSACTIONREPEATABLEREAD, and TRANSACTION_SERIALIZABLE isolation levels can prevent dirty reads.

6. What is phantom reading? Which isolation level can prevent phantom reading?

Phantom reading means that a transaction executes a query multiple times but returns different values. Suppose a transaction is performing a data query based on a certain condition, and then another transaction inserts a row of data that satisfies the query condition. Afterwards, this transaction executes this query again, and the returned result set will contain the new data just inserted. This new row of data is called a phantom row, and this phenomenon is called a phantom read.

Only the TRANSACTION_SERIALIZABLE isolation level can prevent phantom reads.

7. What is the JDBC DriverManager used for?

JDBC’s DriverManager is a factory class through which we create a database connection. When the JDBC Driver class is loaded, it will register itself in the DriverManager class

Then we will pass the database configuration information to the DriverManager.getConnection() method, and DriverManager will use the driver registered in it. Obtain the database connection and return it to the calling program.

8. What is the difference between execute, executeQuery and executeUpdate?

The execute(String query) method of Statement is used to execute any SQL query. If the result of the query is a ResultSet, this method returns true. If the result is not a ResultSet, such as an insert or update query, it will return false. We can get the ResultSet through its getResultSet method, or get the number of updated records through the getUpdateCount() method.

The executeQuery(String query) interface of Statement is used to execute select query and return ResultSet. Even if no records are found in the query, the ResultSet returned will not be null. We usually use executeQuery to execute query statements. In this case, if an insert or update statement is passed in, it will throw a java.util.SQLException with the error message "executeQuery method can not be used for update".

Statement的executeUpdate(String query)方法用來執(zhí)行insert或者update/delete（DML）語句，或者 什么也不返回，對于DDL語句，返回值是int類型，如果是DML語句的話，它就是更新的條數(shù)，如果是DDL的話，就返回0。

只有當你不確定是什么語句的時候才應該使用execute()方法，否則應該使用executeQuery或者executeUpdate方法。

9、SQL查詢出來的結(jié)果分頁展示一般怎么做？

Oracle：

select * from
(select *,rownum as tempid from student )  t
where t.tempid between ” + pageSize*(pageNumber-1) + ” and ” + pageSize*pageNumber

MySQL：

select * from students limit ” + pageSize*(pageNumber-1) + “,” + pageSize;

sql server:

select top ” + pageSize + ” * from students where id not in +
(select top ” + pageSize * (pageNumber-1) +  id from students order by id) +  
“order by id;

（視頻教程推薦：java課程）

10、JDBC的ResultSet是什么？

在查詢數(shù)據(jù)庫后會返回一個ResultSet，它就像是查詢結(jié)果集的一張數(shù)據(jù)表。

ResultSet對象維護了一個游標，指向當前的數(shù)據(jù)行。開始的時候這個游標指向的是第一行。如果調(diào)用了ResultSet的next()方法游標會下移一行，如果沒有更多的數(shù)據(jù)了，next()方法會返回false?？梢栽趂or循環(huán)中用它來遍歷數(shù)據(jù)集。

默認的ResultSet是不能更新的，游標也只能往下移。也就是說你只能從第一行到最后一行遍歷一遍。不過也可以創(chuàng)建可以回滾或者可更新的ResultSet。

當生成ResultSet的Statement對象要關(guān)閉或者重新執(zhí)行或是獲取下一個ResultSet的時候，ResultSet對象也會自動關(guān)閉。
可以通過ResultSet的getter方法，傳入列名或者從1開始的序號來獲取列數(shù)據(jù)。

The above is the detailed content of Java high-frequency basic interview questions——(5). For more information, please follow other related articles on the PHP Chinese website!