Java high-frequency basic interview questions——(5)

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)方法用来执行insert或者update/delete（DML）语句，或者 什么也不返回，对于DDL语句，返回值是int类型，如果是DML语句的话，它就是更新的条数，如果是DDL的话，就返回0。 

只有当你不确定是什么语句的时候才应该使用execute()方法，否则应该使用executeQuery或者executeUpdate方法。

9、SQL查询出来的结果分页展示一般怎么做？

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是什么？

在查询数据库后会返回一个ResultSet，它就像是查询结果集的一张数据表。

ResultSet对象维护了一个游标，指向当前的数据行。开始的时候这个游标指向的是第一行。如果调用了ResultSet的next()方法游标会下移一行，如果没有更多的数据了，next()方法会返回false。可以在for循环中用它来遍历数据集。

默认的ResultSet是不能更新的，游标也只能往下移。也就是说你只能从第一行到最后一行遍历一遍。不过也可以创建可以回滚或者可更新的ResultSet。

当生成ResultSet的Statement对象要关闭或者重新执行或是获取下一个ResultSet的时候，ResultSet对象也会自动关闭。
可以通过ResultSet的getter方法，传入列名或者从1开始的序号来获取列数据。

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!