Java JDK8 Learning Notes--16th Chapter Integration Database

16th Chapter Integration Database 16.1 JDBC Introduction 16.1.1 JDBC Intro

1. JDBC is the standard specification for Java online databases. It defines a set of standard classes and interfaces, and the interfaces in the standard API have database vendor operations called the JDBC driver.

2, the JDBC standard is divided into two parts: the JDBC Application developer interface and the JDBC driver developer interface. The application requires an online database, and its associated APIs are primarily in the java.sql and javax.sql two packages.

3. The application uses the common syntax of the JDBC online database:

Connection conn = drivermanager.getconnection (...);
Statement st = Conn.createstatement ();
ResultSet rs = st.executequery ("SELECT * from T_user");

4, JDBC wants to achieve a "write a Java program, operation of all databases" purpose.

5, the drive according to the operation mode can be divided into four types:
(1) Jdbc-odbc Bridge Driver: It is the most mature on Microsoft systems.
Disadvantage: ODBC is set up on the platform first, the elasticity is insufficient, and itself has cross-platform limitation.
(2) Native API Driver: To invoke the native link library provided by the database.
Cons: There are cross-platform limitations
Advantages: Fastest Speed
(3) Jdbc-net Driver: Convert a JDBC method call to a specific network protocol call
Pros: Cross-platform
Cons: Slow, get architectural elasticity is the purpose of using this type of driver
(4) Native Protocol Driver: Provided directly by the database manufacturer
Pros: Cross-platform-is the most common type of driver .

16.1.2 Connection Database

1. To connect to the database, the driver jar document must be set in the classpath.

2, the basic database operation related JDBC interface or class is located in the java.sql package. Several actions are required to get the database online:

(1) Register driver object
(2) Get connection operation object
(3) Close Connection action Object

3. Register Driver Object
There are 4 ways to load A. class document using the JDBC requirement:
(1) using Class.forName ()
(2) Self-established driver interface Operation class instance
(3) Specifying the Jdbc.drivers property when starting the JVM
(4) Set/services/java.sql.driver document in Jar

4, Get connection operation object
The action object of the connection interface is the database online representative object. It defines the protocol, sub-protocol, and data source recognition when a database connection is made:

Protocol: Sub-Protocol: Data source recognition
Protocol: JDBC
Sub-protocol: Bridged driver
Data source recognition: The address of the database, port number, name, user, password and other information.
Note : To use Chinese access must be given parameters Useunicode and characterencoding. Indicates whether Unicode is used and specifies how the character is encoded.
In an XML configuration file, you cannot write the & symbol directly, as &amp.

16.1.3 using statement, ResultSet

1, statement is the representative object of SQL description, you can use Executeupdate (), ExecuteQuery () and other methods to execute SQL.

2, statement the Executeupdate () method for select and other query database SQL, returns an int result, represents the number of data changes

3. The statement executequery () method returns the Java.sql.ResultSet object, which represents the query result.

4. The Execute () method of statement is used to execute SQL and returns true to indicate that SQL execution will return resultset as the result of the query.

16.1.4 using PreparedStatement, CallableStatement

1. You can use Java.sql.PreparedStatement if some of the operations are just some of the parameters in the SQL statement, and the rest of the SQL clauses are the same. Call Clearparametere () to clear the parameters of the setting, and then use the PreparedStatement instance again.

2. Benefits of using PreparedStatement:
(1) SQL description can be precompiled into the database execution instructions, the execution speed can be much faster.
(2) Prevent SQL injection

3. If you compose a stored procedure for a database and want to invoke it using JDBC, you can use Java.sql.CallableStatement. You must call Preparecall () to establish the CallableStatement exception, and you can register the output parameters with Registeroutparameter ().

16.2 JDBC Advanced 16.2.1 use DataSource to get online

1, let Messagedao rely on the Java.sql.DataSource interface, can be defined by the getconnection () method to obtain connection.

2, in the future to modify the database server host location, in order to reuse the connection object and want to join the online pool mechanism, etc., this Messagedao do not have to modify.

3, online related information can be used in the. Properties settings.

16.2.2 scrolling, updating data with resultset

1. When establishing an statement or PreparedStatement instance, you can specify the result set type and the parallel mode.

createstatement (int resultsettype,int resultsetconcurrency) preparestatement (String sql,int resultSetType,int resultSetConcurrency)

2, the result set type ResultsetType can specify 3 kinds:

Resultset.type _ FORWARD _ Only (default)
Resultset.type _ SCROLL _ Insensitive
Resultset.type _ SCROLL _ Sensitive

3, update the setting resultsetconcurrency can specify 2 kinds:

Resultset.concurREADonly (default)
Resultset.concur_updatable

4, ResultSet for data modification conditions limit

A single table must be selected
Primary key must be selected
All values that are NOT NULL must be selected

16.2.3 Batch Update

1. The limitation of batch update is that SQL cannot make select, otherwise it throws an exception.

2. To support batch updates, the Rewritebatchedstatements = True parameter must be appended to the JDBC URL to have a practical effect.

16.2.4 Blob and CLOB

1, blob for storing a large number of binary data, such as image files, video files, etc. clob is used to store large amounts of text data.

2. The Blob field can correspond to byte[] or input \ output stream.

16.2.5 Trading Profile

1. Four basic principles of trading:

Atomicity: If one step fails, you must withdraw the action you have performed and return to the state before the transaction.
Consistency: The set of data for a trading role must be consistent before and after trading
Quarantine behavior: Between trading and trading, must not interfere with each other
Continuity: Even if the system is hung, the result of the transaction cannot be lost.

2, in the transaction management, only want to recall a SQL execution point, you can set the storage point.

3, the database can set the specified isolation behavior, can set the constant is:

TRANSACTION _ Uncommitted: Update lost
At the lowest isolation level, the chance of reading bad data is too high, and this isolation level is generally not used.
TRANSACTION _ COMMITTED: Update lost + dirty Read
The data read by the transaction must be the confirmed data of other transactions
TRANSACTION _ repeatable _ Read: Update lost + dirty Read + Unable to read repeatedly
Read transactions do not block other read transactions until they are confirmed, but they prevent other updates from being traded.
TRANSACTION _ SERIALIZABLE: Update lost + dirty Read + cannot repeat read + Phantom read transaction if there is a doubt that the data is inconsistent, the transaction must be carried out sequentially.

4, through JDBC to know whether the database supports an isolation behavior setting, can be obtained through connection GetMetaData () DatabaseMetaData object, The DatabaseMetaData supporttransaction-isolationlevel () is known to support an isolated behavior.

16.2.6 Metadata Introduction

Metadata is "the data of reading data" can be obtained through the connection GetMetaData () method DatabaseMetaData object, can obtain the overall information of the database, and resultset represents the query to the data, The ResultSetMetaData object can be obtained by ResultSet's GetMetaData () method.

16.2.7 Rowset Introduction

1, Javax.sql.RowSet interface, represents the column collection of data. The collection of columns can be added and checked. The query instruction is set by SetCommand (), and execute () executes the query instruction to populate the data.

2. Rowset defines five standard column set sub-interfaces:

(1) Jdbcrowset
is an online rowset, keeping it online with the database, which can be considered as the rowset behavior of acquisition and operation.
(2) Cacherowset
is an offline rowset that, after querying and populating the data, disconnects the data source from the online
(3) Filteredrowset
You can filter the collection of columns to implement functionality similar to where in SQL.
(4) Joinrowset
Lets you combine two rowset objects, you can specify which rows to combine by Setmatchcolumn (), and then use Addrowset () to join rowset.
(5) Webrowset
CachedRowSet is a sub-interface that not only provides offline operation, but also allows XM to read and write.

Java JDK8 Study Notes chapter 16th

Java JDK8 Learning Notes-16th chapter Consolidated database