Java JDBC database Transaction acid__ Database

1. Describe the steps of the JDBC operations database.

A: The following code illustrates the steps of the JDBC operations database with an example of an Oracle database that connects to this computer. Load driver.

    Class.forName ("Oracle.jdbc.driver.OracleDriver");

Create a connection.

    Connection con = drivermanager.getconnection ("Jdbc:oracle:thin: @localhost: 1521:orcl", "Scott", "Tiger");

Creates a statement.

    PreparedStatement PS = con.preparestatement ("SELECT * from emp where Sal between?") and? ");
    Ps.setint (1, 1000);
    Ps.setint (2, 3000);

EXECUTE statement

    ResultSet rs = Ps.executequery ();

Process the results.

    while (Rs.next ()) {
        System.out.println (rs.getint ("empno") + "-" + rs.getstring ("ename"));
    

Closes the resource.

    Finally {
        if (con!= null) {
            try {
                con.close ();
            } catch (SQLException e) {
                e.printstacktrace ();
            }
        }
    }

Tip: The order to turn off external resources should be the opposite of the order in which they were opened, that is, close the resultset first, then close statement, and then close the connection. The above code only closes the connection (connection), although the statements created on the connection and the cursors that are opened are also closed when the connection is closed, but are not guaranteed to always be the case, and should therefore be closed separately in the order in which they were mentioned. In addition, the first step load driver can be omitted in JDBC 4.0 (automatically load the driver from the classpath), but we recommend retention.

What is the difference between 2.Statement and PreparedStatement? Which performance is better.

A: The ①preparedstatement interface represents a precompiled statement compared to statement, and its main advantage is that it can reduce SQL compilation errors and increase SQL security (reduce the likelihood of SQL injection attacks) The SQL statements in ②preparedstatement can be parameterized, avoiding the hassle and insecurity of concatenation of SQL statements with strings; ③ The PreparedStatement has a significant performance advantage when handling SQL in batches or frequently executing the same query. Since the database can cache compiled, optimized SQL statements, the next execution of the same structure statement will be quick (without recompiling and building the execution plan again).

Supplemental: In order to provide calls to stored procedures, the CallableStatement interface is also provided in the JDBC API. A stored procedure (Stored Procedure) is a collection of SQL statements in a database that are compiled and stored in a database to perform a specific function, and the user executes it by specifying the name of the stored procedure and giving the parameter, if the stored procedure has parameters. While calling stored procedures can have a lot of benefits for network overhead, security, and performance, there is a lot of trouble if the underlying database migrates, because there are a lot of differences in the way the stored procedures for each database are written.

3. How to improve the performance of reading data when using JDBC to manipulate the database. How to improve the performance of updated data.

A: To enhance the performance of reading data, you can specify the number of records per crawl (typical space-time policy) by the Setfetchsize () method of the result set (ResultSet) object. ; To promote performance of the updated data you can use the PreparedStatement statement to build the batch and put several SQL statements into a batch execution.

4. What is the role of the connection pool in database programming?

A: Because of the great overhead of creating and releasing connections (especially when the database server is not local, each connection requires a TCP three handshake, the release of the connection requires TCP four handshake, the overhead is not negligible), in order to enhance the system access to the database performance, Several connections can be created in advance in the connection pool, get directly from the connection pool when needed, and return the connection pool at the end without shutting down the connection, thus avoiding the overhead of frequent creation and release of connections, a typical space-for-time strategy (a waste of space storage connectivity, but saves time to create and release connections). Pooling technology is common in Java development and is the same as creating a thread pool when using threads. Java-based open source database connection pool mainly include: C3p0, Proxool, DBCP, BONECP, Druid and so on.

Add: Time and space are irreconcilable contradictions in computer systems, and understanding this is critical to designing algorithms that meet performance requirements. A key to the performance optimization of large Web sites is the use of caching, which is very similar to the connection pooling above, and also a strategy for using space for time. Hotspot data can be placed in the cache and can be obtained directly from the cache when the user queries the data, which in any case is faster than the query in the database. Of course, the cache replacement strategy will also have a significant impact on system performance, the discussion of this issue has gone beyond the scope to be elaborated here.

5. What is DAO mode.

A: DAO (data Access object), by definition, is an object that provides an abstract interface for a database or other persistence mechanism, providing a variety of data access operations without exposing the details of the underlying persistence scheme implementation. In actual development, all access operations to the data source should be abstracted and encapsulated in a common API. In the programming language, it is an interface that defines all the transaction methods that will be used in this application. In this application, the interface is used when it is necessary to interact with the data source, and a separate class is written to implement the interface, which logically corresponds to a particular data store. The DAO pattern actually contains two patterns, one is the data accessor, the other is the data object, which solves the problem of how to access the data, and the latter is about how to encapsulate the data with the object.

6. What does the acid of a transaction mean?

For:
-Atomicity (Atomic): The operations in the transaction, either all or none, the failure of any one operation will lead to the failure of the entire transaction;
-Consistency (Consistent): The system state is consistent after the transaction ends;
-Isolation (Isolated): Concurrently executing transactions cannot see each other's middle state;
-Persistence (Durable): Changes that are made after a transaction is completed are persisted, even if a catastrophic failure occurs. Logs and synchronous backups allow you to reconstruct data after a failure occurs.

Add: Regarding the affairs, the probability which is asked in the interview is very high, may ask the question is also many. The first thing you need to know is that transactions are required only when there is concurrent data access. When multiple transactions access the same data, there may be 5 types of problems, including 3 types of data-reading problems (dirty read, non-repeatable read and Phantom Reading), and 2 types of data update problems (class 1th loss updates and class 2nd loss updates).

Dirty Read (Dirty Read): A transaction reads data that has not yet been committed by the B transaction and operates on this basis, while the B transaction performs a rollback, then the data that a reads is dirty data.

Time	Transfer Transaction A	Withdrawal Service B
T1		Start a transaction
T2	Start a transaction
T3		Inquiry account balance is 1000 yuan
T4		Remove 500 yuan balance modified to 500 yuan
T5	Inquiry account balance is 500 yuan (dirty Read)
T6		Undo transaction balance restored to 1000 yuan
T7	Import 100 yuan to change the balance to 600 yuan
T8	Commit a transaction

Non-repeatable read (unrepeatable Read): Transaction A re-read the previously read data and found that the data has been modified by another committed transaction B.

Time	Transfer Transaction A	Withdrawal Service B
T1		Start a transaction
T2	Start a transaction
T3		Inquiry account balance is 1000 yuan
T4	Inquiry account balance is 1000 yuan
T5		Remove 100 yuan to modify the balance of 900 yuan
T6		Commit a transaction
T7	Inquiry account balance is 900 yuan (not repeat read)

Phantom Read (Phantom Read): Transaction a executes a query, returning a series of rows that match the query criteria, and finds that the rows submitted by transaction B are inserted.

Time	Statistics Amount Transaction A	Transfer Transaction B
T1		Start a transaction
T2	Start a transaction
T3	Total Deposit is 10000 yuan
T4		Add a deposit account to deposit 100 yuan
T5		Commit a transaction
T6	Again the total deposit is 10100 yuan (phantom reading)

Class 1th loss update: When transaction A is revoked, the updated data for the committed transaction B is overwritten.

Time	Withdrawal transaction A	Transfer Transaction B
T1	Start a transaction
T2		Start a transaction
T3	Inquiry account balance is 1000 yuan
T4		Inquiry account balance is 1000 yuan
T5		Import 100 Yuan to modify the balance of 1100 yuan
T6		Commit a transaction
T7	Remove 100 yuan to change the balance to 900 yuan
T8	Undo Transaction
T9	Balance restored to 1000 yuan (lost update)

Class 2nd Loss Update: Transaction a overwrites the data already committed by transaction B, resulting in loss of operations by transaction B.

time	Transfer Transaction A	withdrawal transaction b
T1	/td>	start transaction
T2	start transaction
T3		Query account balance is 1000 $
T4	Query account balance is 1000 CNY
T5		Remove 100 yuan to change the balance to 900 $
T6		COMMIT TRANSACTION
T7	Import $100 to change the balance to 1100 $
T8	Commit Transaction
T9	query account balance is 1100 yuan (lost update)

Problems caused by concurrent access to data, in some scenarios may be allowed, but some scenarios may be fatal, the database usually through the lock mechanism to solve the data concurrency access problem, according to the different locking object can be divided into table-level lock and row-level lock, the concurrent transaction locking relationship can be divided into shared locks and exclusive locks, Specific content you can access the information to understand.
Direct use of locks is very troublesome, this database provides an automatic locking mechanism for the user, so long as the user specifies the transaction isolation level of the session, the database will then add the appropriate locks to the resources that the transaction accesses by parsing the SQL statements, in addition, the database maintains these locks to improve the performance of the system by various means, These are transparent to the user (that is, you don't have to understand, I actually don't know). The Ansi/iso SQL 92 standard defines a 4-level transaction isolation level, as shown in the following table:

Isolation level	Dirty Read	Do not read repeatedly	Phantom reading	First Class missing update	Second Class loss update
READ uncommited	Allow	Allow	Allow	Not allowed	Allow
READ committed	Not allowed	Allow	Allow	Not allowed	Allow
Repeatable READ	Not allowed	Not allowed	Allow	Not allowed	Not allowed
SERIALIZABLE	Not allowed	Not allowed	Not allowed	Not allowed	Not allowed

It should be explained that the transaction isolation level and the concurrency of data access are antagonistic, and the higher the transaction isolation level, the worse the concurrency. Therefore, according to the specific application to determine the appropriate level of transaction isolation, this place does not have the principle of omnipotence.

How to handle transactions in 7.JDBC.
A: Connection provides a method of transaction processing by invoking Setautocommit (false) to set up a manual commit transaction, committing a transaction with commit () when the transaction completes, or by rollback () If an exception occurs during the transaction process () Perform a transaction rollback. In addition, the concept of savepoint (SavePoint) is introduced from JDBC 3.0, allowing the store point to be set through code and rolling the transaction back to the specified save point.

8.JDBC can handle BLOBs and CLOB.
A a blob is a binary large object (Binary Large object), and Clob is a big character object (Character Large objec), so the Blob is designed to store large binary data. The CLOB is designed to store large text data. JDBC PreparedStatement and ResultSet both provide a way to support BLOB and CLOB operations. The following code shows how to use JDBC to manipulate the LOB:
Take the MySQL database as an example, create a user table with three fields, including number (ID), name (name), and photo (photo), and the following table statement:

CREATE TABLE Tb_user
(
ID int primary key auto_increment,
name varchar unique NOT NULL,
photo Longblo b
);

The following Java code inserts a record into the database:

Import Java.io.FileInputStream;
Import java.io.IOException;
Import Java.io.InputStream;
Import java.sql.Connection;
Import Java.sql.DriverManager;
Import java.sql.PreparedStatement;

Import java.sql.SQLException;
        Class Jdbclobtest {public static void main (string[] args) {Connection con = null;
            try {//1. Load driver (Java6 above version can be omitted) class.forname ("Com.mysql.jdbc.Driver"); 2.
            Establish connection con = drivermanager.getconnection ("Jdbc:mysql://localhost:3306/test", "root", "123456"); 3.
            Create a statement object PreparedStatement PS = con.preparestatement ("INSERT into tb_user values (default,?,?)");              Ps.setstring (1, "John");
                Replace the first placeholder in the SQL statement with a string try (inputstream in = new FileInputStream ("Test.jpg")) {//Java 7 TWR      Ps.setbinarystream (2, in); Replace the second placeholder in the SQL statement with binary stream/4. Emit the SQL statement to get the number of affected rows System.out.println (ps.executeupdate () = 1? "Insert succeeded":"Insert Failed");
            catch (IOException e) {System.out.println ("read photo failed!"); catch (ClassNotFoundException |
        SQLException e) {//Java 7 Multiple exception capture E.printstacktrace (); finally {//release of external resources code should be put in finally to ensure that it can be executed try {if (Con!= null &&!con.isclosed ()    ) {con.close (); 5.     Free database connection con = null;
            Indicates that the garbage collector can reclaim the object} catch (SQLException e) {e.printstacktrace (); }
        }
    }
}