JDBC 1 (web basic learning note 7) and jdbc learning note

JDBC 1 (web basic learning note 7) and jdbc learning note
I. JDBC

Java DataBase Connectivity enables Java programs to access various databases

It is composed of a group of classes and interfaces (JDBC APIs) written in the Java language. In java. SQL and javax. SQL

Ii. JDBC principles and driver classification 2.1. JDBC principles

When developing JDBC APIs, SUN put these interfaces and classes in two packages: java. SQL package and javax. SQL package. The main interfaces and classes are in the java. SQL package. The javax package is called an extension package. The javax. SQL package contains some extended interfaces and classes about the data source, connection pool, and others. The most common interfaces and classes in JDBC are DriverManager, Connection, Statement, and ResultSet. DriverManager is used to manage drivers and obtain database Connection objects. The JDBC driver is a class that implements the jdbc api and is provided by the database vendor. Therefore, when we use JDBC to connect to the database, we need to import the driver package of the database, to connect to different databases, You need to import different packages. When writing Java applications, we basically don't have to worry too much about the classes in these packages. We only need to program with the JDBC interface, that is, the interfaces in the java. SQL package and javax. SQL package. The objects of these interfaces are obtained through other objects, and we do not need to create them through new. The specific implementation of these objects is implemented in the driver, and is the object of the driver class.

2.2 driver classification 2.3, bridge through JDBC-ODBC

Through the JDBC-ODBC bridge, developers can use JDBC to access an ODBC data source. The JDBC-ODBC bridge Driver provides a way for Java applications to map JDBC calls to ODBC calls. As long as the local machine is equipped with the relevant ODBC driver, then the use of JDBC-ODBC bridge can access almost all the databases. However, because the JDBC-ODBC first calls ODBC and then ODBC to call the local database interface to access the database. Therefore, the execution efficiency is relatively low, which is not suitable for applications with large data access. In addition, this method requires the client to install the ODBC driver, so it is not suitable for internet and intranet-based applications. Because, you cannot require all customers to find the ODBC driver.

The steps to bridge a connection with a JDBC-ODBC are as follows:

1. Configure the Data source: Control Panel → Administrative Tools → ODBC Data Source → system DSN
2. Programming: establish a connection with the database through a Bridge Connection

 

2.4. pure Java driver

The pure Java driver directly converts JDBC calls to requests that comply with the relevant database system specifications. Applications written using this driver can communicate directly with the database server. This type of driver is fully implemented by Java, thus realizing platform independence.

The pure Java driver does not need to pass the JDBC call to ODBC, local database interface, or intermediate layer server, but directly converts the JDBC call to the network protocol used by the DBMS, therefore, the execution efficiency is very high. Such drivers can be dynamically downloaded. However, it has a disadvantage that different drivers need to be downloaded for different databases.

Steps for direct connection using a pure Java DRIVER:
1. Download the driver package provided by the database vendor
2. Introduce the driver package into the project
3. Programming: establish a connection with the database through a pure Java driver

Iii. JDBC programming steps

There is a fixed set of steps to connect to and operate the database using JDBC. Follow these steps to ensure that you are correctly connected to the database and operate on it.

• The first step is to register the driver. The Class. forName () method loads the driver Class into the memory of the VM.
• The second step is to obtain the database connection, which is obtained using the getConnection () method of the DriverManager class. At the same time, the URL, user name, and password of the database to be connected must be provided in the parameter. In this step, a database Connection object is obtained, which belongs to the Connection type.
• Step 3: Use the database connection object to obtain a Statement object, which can be used to execute SQL statements.
• Step 4: Use the Statement object to execute the SQL Statement. In fact, it is not accurate to use a Statement object to execute SQL statements because SQL statements can only be executed by the database. Statement is only responsible for sending SQL statements and receiving results. However, no matter who is executing the SQL statement, this step will execute the SQL statement.
• The fifth step is to process the execution results. For an update operation, it may be to determine whether the update is successful. For a query operation, it may be to output the query results.
• Finally, remember to release the database connection. Because the database connection is a heavyweight Object. For applications that are concurrently accessed by multiple users, such as B/S applications, each user is connected but not released, and the server resources are continuously consumed until the server goes down.

4. Introduction to JDBC APIs

JDBC APIs mainly define database connection and operation interfaces, but few specific classes. This is because, when developing Java APIs, it is impossible for JDBC to connect to a specific database, but only one set of specifications can be determined. The implementation of connecting to a specific database should be done by the database vendor. Through the unified JDBC specification, the encoding method is the same when we connect to various databases, which is more conducive to code reuse and transplantation.

The preceding JDBC programming steps show that the use of JDBC to operate databases depends on several major JDBC APIs. Next we will introduce the use of these JDBC APIs.

4.1. The DriverManager class is used to manage database drivers.

Most java. SQL packages use interfaces, which are one of the few classes. It is a very common class. The main function is to obtain the database connection. It defines three methods to connect to the database. The difference lies in the number of parameters. The getConnection () method of the three parameters is the most commonly used. The three parameters are the URL, user name, and password of the database.

Return type	Method Signature	Description
Static Connection	GetConnection (String url)	Try to create a given Database URL Connection
Static Connection	GetConnection (String url, Properties info)	Try to create a given Database URL Connection
Static Connection	GetConnection (String url, String user, String password)	Try to create a given Database URL Connection

4.2 java. SQL. Connection interface indicates the Connection object between the application and the database

Implemented by the database vendor. The method for obtaining the Connection object is through the getConnection () method of the DriverManager class. Through the Connection object, we can obtain the Statement, PreparedStatement, CallableStatement, and other objects of the Operation database. These objects are used to execute SQL and stored procedures.

4.3. The Statement interface is used to execute SQL statements and is used to execute static SQL statements.

The Statement interface is used to execute SQL statements and execute static SQL statements. Static SQL statements are determined by a fixed SQL string and cannot be modified during runtime.

 

Return type	Method Signature	Description
Int	ExecuteUpdate (String SQL)	Execute a given SQL statement, which may be INSERT, UPDATE, or DELETE. Or SQL statements (such as SQL DDL statements) that do not return any content)
ResultSet	ExecuteQuery (String SQL)	Executes the given SQL statement and returns a single ResultSet object.
Void	Close ()	Release the database and JDBC resources of this Statement object immediately, instead of waiting This operation occurs when the object is automatically disabled.

4.4 The ResultSet interface is used to represent the query result set.

When we call the executeQuery () method of Statement, we will get a ResultSet object. The ResultSet object contains a result set queried Based on the query statement. However, in fact, the content is still in the database and is not actually retrieved into the memory of the VM. The ResultSet actually saves a cursor pointing to its current data row. We need to use the ResultSet method to move the cursor down one row and then get the data of each row, therefore, the database connection cannot be closed during operations on the ResultSet object.

 

Return type	Method Signature	Description
Boolean	Next ()	Move CURSOR (CURSOR) one row forward from current position
Type	GetType (String columnLabel)	Obtains the value of a specified Column Based on the column name of the database table.
Type	GetType (int columnIndex)	Obtain the value of the specified Column Based on the column number. The first column number is 1.
Void	Close ()	Release the database and JDBC resources of the ResultSet object immediately, instead of waiting for This operation occurs when the object is automatically disabled.

V, java.sql5.1 java. SQL description

APIs used by programming languages to access and process data stored in a data source (usually a relational database. This API includes a framework that allows dynamic installation of different drivers to access different data sources. Although the jdbctm api is mainly used to pass SQL statements to databases, it can also be used to read and write data from any data source as a table. Interfacejavax.sql.RowSetReader/writer utilities that can be used by a group can be customized to use and update data from workbooks, plain text files, or any other table-based data sources.

5.2 java. SQL content

java.sqlThe package contains APIs for the following purposes:

• PassDriverManagerThe utility establishes a connection to the database.
  • DriverManagerClass: establish a connection with the driver
  • SQLPermissionClass: When the code runs in Security Manager (such as applet ),DriverManagerSet a record stream
  • DriverInterface: provides APIs for registering and connecting drivers based on the JDBC technology ("JDBC driver ").DriverManagerClass usage
  • DriverPropertyInfoClass: Provides the attributes of the JDBC driver, not for general users.
• Send SQL statements to the database
  • Statement: Used to send basic SQL statements
  • PreparedStatement: Used to send prepared statements or basic SQL statements (derived fromStatement)
  • CallableStatement: Used to call database stored procedures (derived fromPreparedStatement)
  • ConnectionInterface: provides methods to create statements and manage connections and their attributes.
  • Savepoint: Provides storage points in transactions.
• Obtain and update query results
  • ResultSetInterface
• Standard ing between classes and interfaces of SQL to Java programming languages
  • ArrayInterface: SQLARRAYIng relationship
  • BlobInterface: SQLBLOBIng relationship
  • ClobInterface: SQLCLOBIng relationship
  • DateClass: SQLDATEIng relationship
  • NClobInterface: SQLNCLOBIng relationship
  • RefInterface: SQLREFIng relationship
  • RowIdInterface: SQLROWIDIng relationship
  • StructInterface: SQLSTRUCTIng relationship
  • SQLXMLInterface: SQLXMLIng relationship
  • TimeClass: SQLTIMEIng relationship
  • TimestampClass: SQLTIMESTAMPIng relationship
  • TypesClass: Provides constants for the SQL type.
• Custom ing SQL user-defined type (UDT) to classes in Java programming language
  • SQLDataInterface: Specifies the ing between a UDT instance and an instance of this type.
  • SQLInputInterface: provides a method to read the UDT attribute from the stream.
  • SQLOutputInterface: provides a method for writing the UDT attribute back.
• Metadata
  • DatabaseMetaDataInterface: Provide information about the database
  • ResultSetMetaDataInterface: provides relatedResultSetObject column information
  • ParameterMetaDataInterface: provides relatedPreparedStatementCommand Parameter Information
• Exception
  • SQLException: Throws are thrown by most methods when data access fails, and by other methods for other reasons.
  • SQLWarning: Throw to indicate a warning
  • DataTruncation: Throws to indicate that the data may have been truncated.
  • BatchUpdateException: It is thrown to indicate that not all commands in batch update are successfully executed.

Vi. JDBC 4.0

Thejava.sqlAndjavax.sqlFeatures

• Automatic java. SQL. Driver discovery: you no longer need to passClass.forNameTo loadjava.sql.DriverClass
• Added National Character Set support
• Added support for SQL: 2003 XML Data Types
• SQLException enhancement: added support for the cause chain; added a new SQLException for the common SQLState class value code
• Enhanced Blob/Clob functions: provides methods to create and release Blob/Clob instances and to improve accessibility.
• Added support for accessing SQL ROWID
• The added support allows JDBC applications to access JDBC resource instances that have been packaged by the supplier, usually in an application server or connection pool environment.
• WhenPooledConnectionAssociatedPreparedStatementThe availability to be notified is disabled or the driver is determined to be invalid