JDBC main functions and responsibilities

Java Database Connectivity (JDBC) is an industry standard for database-independent connections between the Java programming language and various databases, SQL databases, and other tabular data sources such as spreadsheets or flat files. The JDBC API is a CALL-level API for SQL-based database access.

JDBC is an interface provided in the Java language to access relational databases.

JDBC operations on data sources are as follows:

Establishing a data source Connection

  1. DataSource (official recommended) : After calling the getConnection() method of the DataSource provided by JDBC 2.0, the DataSource instance returns a Connection object that connects to the DataSource.

    UnpooledDataSource dataSource = new UnpooledDataSource(driverClassLoader, "com.mysql.jdbc.Driver", "JDBC: mysql: / / 127.0.0.1 / test", "root", "123"); Connection connection = dataSource.getConnection();Copy the code

    JDBC API only provides the DataSource interface, there is no concrete implementation of DataSource. The concrete implementation of DataSource is provided by JDBC drivers, such as JDBCDataSource, MysqlDataSource, etc. The main database connection pools (DBCP, C3P0, Druid, etc.) also implement the DataSource interface

  2. DriverManager: JDBC 1.0 uses the DriverManager class to generate a Connection object that is connected to the data source. The overloaded getConnection() method is used to retrieve the Connection object. When an application connects to a data source through a URL for the first time, DriverManager automatically loads all JDBC drivers in the CLASSPATH.

    Connection conn = DriverManager.getConnection("jdbc:hsqldb:mem:association_nested", "SA", "");
Copy the code

    The DriverManager class tries to load the driver class referenced in the “jdbc.drivers” system property.

Execute SQL statement

After a data source Connection is established, you can use the methods provided by the Connection interface to create a Statement, PreparedStatement, or CallableStatement object to query and modify the data source.

 Connection realConn = conn.getRealConnection();
 Statement statement = realConn.createStatement());
 statement.executeQuery("select * from t");
Copy the code

The Statement interface is actually the executor of the SQL statements provided in the JDBC API, The Statement interface defines the executeQuery() query operation, executeUpdate() update operation, executeBatch() batch operation, execute() query/update operation, getResultSet() query result set, and getUpdateCo Unt () the number of rows affected by the update operation, and so on.

A. If the number of updates returned by the database is greater than integer.max_value, call the executeLargeXXX() method

BNT executeXXX(String SQL, int autoGeneratedKeys), the second parameter can be int, int[], String[], etc. Through autoGeneratedKeys columnIndexes/columnNames parameter tells the driver which column is automatically generated can be used to retrieve the key. If the SQL statement is not an INSERT statement, the columnNames parameter is ignored.

Process SQL execution results

An SQL Statement is executed through the Statement interface. The result after the SQL execution is obtained based on the returned ResultSet, and the final query result is obtained by traversing. ResultSet provides related methods such as getString(int columnIndex) and getBoolean(int columnIndex). According to various enumerations such as JDBCType, the data type of each column query result is converted to the corresponding Java type.

Close the connection

After all Sql statements are executed and the result set is obtained, the Statement and Connection are closed by using the close() method.

JDBC Main Contents

Wrapper

The Wrapper interface provides jDBC-using applications with access to primitive types to use some of the non-standard features of JDBC drivers. Basic interfaces such as Connection, DataSource, Statement, ResultSet, and DatabaseMetaData inherit the Wrapper, which contains the unwrap() and isWrapperFor() methods:

  • The unwrap() method is used to return an unwrapped instance of the primitive type of the JDBC driver from which non-standard methods provided in the JDBC driver can be invoked.
  • The isWrapperFor() method is used to determine whether the current instance is a wrapper type of a type in the JDBC driver.

DataSource

The DataSource advantages:

  1. You can register a DataSource object through JNDI and reference it with a logical name in your program, and JNDI will automatically find the DataSource object bound to that name. This allows you to use the DataSource object to establish connections to a specific database.
  2. The DataSource interface supports connection pooling and distributed transactions. Connection pooling can significantly improve the efficiency of programs by reusing connections, eliminating the need to create a new physical connection each time a data source is manipulated.

PooledConnection

When an application calls the method datasource. getConnection, a Connection object is returned. But when you use a database Connection pool (such as Druid), the Connection object is actually a handle to the PooledConnection object, which is a physical Connection.

PooledConnection provides a handle to connection pool management. The PooledConnection object represents a physical connection to the data source. When the application completes the connection, the connection can be reclaimed rather than closed, reducing the number of connections that need to be established. The PooledConnection interface is typically not used directly by developers; it is used by the mid-tier infrastructure that manages connection pools, that is, through a mid-tier infrastructure that manages connection pools.

  1. Establish a connection

The connection pool manager (typically an application server) maintains a pool of objects called PooledConnection. If a PooledConnection object is available in the pool, the Connection pool manager returns a Connection object that is the handle to the physical Connection. If no PooledConnection object is available, the connection pool manager calls ConnectionPoolDataSource’s (PooledConnection object Factory) getPoolConnection method to create a new physical connection. Typically for the JDBC driver that implements ConnectionPoolDataSource, create a new PooledConnection object and return a handle to it. Such as MysqlConnectionPoolDataSource implementation is as follows:

public synchronized PooledConnection getPooledConnection() throws SQLException { try { Connection connection = this.getConnection(); MysqlPooledConnection mysqlPooledConnection = MysqlPooledConnection.getInstance((JdbcConnection)connection); return mysqlPooledConnection; } catch (CJException var4) { throw SQLExceptionsMapping.translateException(var4); }}Copy the code
  1. Close the connection

When an application closes a Connection, the close method of Connection is called. When the connection pool, connection pool manager will receive a notification (using the ConnectionPool addConnectionEventListener method, to register for the ConnectionEventListener object). The connection pool manager deactivates the handle to the PooledConnection object and returns the PooledConnection object to the connection pool so that it can be used again. Therefore, when an application closes its connection, the underlying physical connection is reclaimed rather than closed. The physical connection is not closed until PooledConnection manager calls the close method of PooledConnection. The close method is usually called to shut down the server in an orderly fashion.

Calling the Commit () method of the Connection object closes the ResultSet object created in the current transaction.

  1. Distributed connection
  • XAConnection inherits PooledConnection, an object that supports distributed transactions. XAConnection objects can join distributed transactions through XAResource objects. The transaction manager, usually part of a mid-tier server, manages XAConnection through the XAResource object. Applications do not use this interface directly; It is used by transaction managers working in mid-tier servers.
  • XAConnection interface inherits PooledConnection interface and has all PooledConnection features. We can call the getConnection() method of the XAConnection instance to get the java.sql.Connection object

RowSet and ResultSet

  1. The RowSet interface inherits the ResultSet interface from the java.sql package, providing a set of JavaBeans properties that allow a RowSet instance to be configured to connect to and read some data from a JDBC data source (The RowSet is used to establish a mapping between the data source and the application in the content). A set of setter methods (setInt, setBytes, setString, and so on) provide a way to pass input arguments to command properties of a rowset. This command is the SQL query that the row set uses to get data from a relational database. The RowSet interface supports JavaBeans events, allowing other components in the application to be notified when an event occurs on a RowSet, such as a change in its value.

  • A RowSet object can establish a connection to a data source and maintain that connection throughout its life cycle, in which case it is referred to as a RowSet for a connection

  • A RowSet object can also establish a connection to a data source, get data from it, and then close it, which is called a disconnected RowSet. A non-connected Rowset can change its data when disconnected and then rewrite it back to the underlying data source, although it must re-establish a connection to do so.

Compared to java.sql.ResultSet, the offline operation of RowSet can effectively use computer memory to reduce the burden of the database. Because data operations are performed in memory and then batch committed to the data source, flexibility and performance are greatly improved. By default, a RowSet is a scrollable, updatable, serializable result set, and it works as a JavaBean component that can be easily transported across the network for data synchronization at both ends. In layman’s terms, a RowSet is a mapping of database table data in application memory, and all of our operations can interact directly with the RowSet object. Data synchronization between the RowSet and the database is of no concern to the developer.

  1. Properties such as the type, parallelism, and maintainability of a ResultSet can be set when the Statement object is created by calling the createStatement(), prepareStatement(), or prepareCall() methods of the Connection object, for example:
Connection connection = DriverManager.getConnection("abc");
Statement statement = connection.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE, ResultSet.CLOSE_CURSORS_AT_COMMIT);
Copy the code

The ResultSet type

TYPE_FORWARD_ONLY (default) : The cursor can only be moved forward, from the first row to the last

TYPE_SCROLL_INSENSITIVE: Cursors can be scrolled forward/backward (relative to the current location) or to relative locations. Changes to the ResultSet object do not affect records in the corresponding database

TYPE_SCROLL_SENSITIVE: The cursor can be moved forward/backward (relative to the current position) or to an absolute position. The modification of the ResultSet object directly affects the records in the database

The ResultSet parallelism

CONCUR_READ_ONLY (default) : After setting this property for a ResultSet object, only data can be read from the ResultSet object, but data cannot be updated in the ResultSet object.

CONCUR_UPDATABLE: This property indicates that data can be read from and updated in a ResultSet object.

ResultSet maintainability

HOLD_CURSORS_OVER_COMMIT: The ResultSet created by the current transaction is not closed when the commit() method of the Connection object is called.

CLOSE_CURSORS_AT_COMMIT: The ResultSet created by the current transaction will be closed after the transaction is committed to improve system performance.

After a ResultSet object is closed, Blob, Clob, NClob, or SQLXML objects created by a ResultSet object are not closed unless they are cleared by calling their free() method

The transaction

A setTransactionIsolation() method is provided in the Connection interface that allows JDBC clients to set the transaction isolation level of Connection objects. The autoCommit property of the Connection object determines when to end a transaction. When automatic commit is enabled, transactions are automatically committed after each SQL statement has been executed. Automatic transaction commit is enabled by default when the Connection object is created. The setAutoCommit() method on the Connection interface disables automatic transaction commit. In this case, either the commit() method of the Connection interface is called to explicitly commit the transaction, or the rollback() method is called to rollback the transaction. Disabling automatic transaction commit is applicable when multiple SQL statements need to be committed as a single transaction or the transaction is managed by the application server.

The “cliche” transaction isolation level:

  1. Dirty read, phantom read, unrepeatable read
  • Dirty read, caused by reading uncommitted data. For example, if transaction A modifies A piece of data but does not commit the changes, the changes made by transaction A to the data are visible to other transactions, and the uncommitted changes can be read by transaction B. If transaction A rolls back, the incorrect data is read in transaction B.
  • Unrepeatable read, (1) A transaction reads A row of data. (2) The data in the row is modified in B transaction. (3) Reading the data of the row again in A transaction will get different results.
  • (1) A transaction reads several rows using the WHERE condition. (2) B transaction inserts several data that meet the conditions. (3) The data inserted in transaction B will be read when the data is read again under the same conditions in transaction A.
  1. Several transaction isolation levels are as follows:
  • TRANSACTION_NONE: indicates that the driver does not support transactions and is incompatible with the JDBC specification.
  • TRANSACTION_READ_UNCOMMITTED: Allows transactions to read uncommitted data. Dirty reads, unrepeatable reads, and magic reads may occur.
  • TRANSACTION_READ_COMMITTED: Any data changes made in a transaction are not visible to other transactions until committed. Can prevent dirty read, can not solve non-repeatable read and phantom read.
  • TRANSACTION_REPEATABLE_READ: Handles dirty and unrepeatable reads, but not phantom reads.
  • TRANSACTION_SERIALIZABLE: Transactions can be executed in serial mode, which can effectively solve dirty read, unrepeatable read, and magic read problems, but the concurrency efficiency is low

The resources

JDBC 4.2 specification documentation:Download.oracle.com/otndocs/jcp…

JDBC related features in Java 8:Docs.oracle.com/javase/8/do…

JTA specification documents:Download.oracle.com/otndocs/jcp…