#What are the basic steps for JDBC to access the database? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? (Recommended learning: java interview questions )

1, load the driver

2, obtain the connection object Connection

3 through the DriverManager object, Obtain the session through the connection object

4, add, delete, modify and check data through the session, encapsulate the object

5, close the resource

Let’s talk about the difference between preparedStatement and Statement

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

2. Security: SQL can be effectively avoided. Injection attack! 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' ; The produced 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.

Let’s talk about the concept of transactions and the steps to process transactions in JDBC programming.

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

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

Transaction processing steps:

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

4, conn.commit() commits the transaction

5, an exception occurs, rollback conn.rollback();

The principle of database connection pool, why use connection pool?

1. Database connection is a time-consuming operation. The connection pool can enable multiple operations to share a connection.

2. The basic idea of ??the database connection pool is to establish a "buffer" for the database connection. "Pool". 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 set Set the maximum number of connections in the connection pool to prevent the system from endless connections to the database. 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.

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

What is dirty reading in JDBC? Which database isolation level can prevent dirty reading?

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 update The 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.

What is phantom reading, and 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.

After this transaction executes this query again, 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.

What is JDBC’s 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.

What is the difference between execute, executeQuery and executeUpdate?

1. Statement's execute(String query) method 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.

2. Statement’s executeQuery (String query) interface 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". ,

3. Statement's executeUpdate(String query) method is used to execute insert or update/delete (DML) statements, or return nothing. For DDL statements, the return value is int type. If it is a DML statement, If it is, it is the number of updates. If it is DDL, it returns 0.

You should use the execute() method only when you are not sure what statement it is, otherwise you should use the executeQuery or executeUpdate method.

How to display the results of SQL query in pages?

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;

What is the ResultSet of JDBC?

After querying the database, a ResultSet will be returned, which is like a data table of the query result set.

The ResultSet object maintains a cursor pointing to the current data row. At the beginning, the cursor points to the first row. If the next() method of ResultSet is called, the cursor will move down one row. If there is no more data, the next() method will return false. You can use it in a for loop to iterate over a data set.

The default ResultSet cannot be updated, and the cursor can only move down. In other words, you can only traverse from the first line to the last line. However, you can also create a ResultSet that can be rolled back or updated.

When the Statement object that generated the ResultSet is to be closed or re-executed or the next ResultSet is obtained, the ResultSet object will also be automatically closed.

You can obtain column data through the getter method of ResultSet by passing in the column name or a serial number starting from 1.

The above is the detailed content of javaweb interview questions (2). For more information, please follow other related articles on the PHP Chinese website!