JDBC One (Web Foundation Learning Note Seven)

First, JDBC

Java database Connectivity, which enables Java programs to access various databases

Consists of a set of classes and interfaces (JDBC API) written in the Java language, which java.sql and javax.sql

Ii. JDBC Principle and Driver Classification 2.1, JDBC principle

The original SUN company (now Oracle), when developing the JDBC API, placed these interfaces and classes in two packages, java.sql packages and javax.sql packages. While the main interfaces and classes are in the java.sql package, the Javax package is called an expansion pack, and the Javax.sql package has some interfaces and classes for data sources, connection pooling, and other extensions. The most commonly used interfaces and classes in JDBC are the DriverManager class, the Connection interface, the Statement interface, the ResultSet interface, the DriverManager class is the management driver, and the database connection object can be obtained. The JDBC driver is the class that implements the JDBC API interface, provided by the database vendor, so when we use JDBC to connect to the database, we need to import the database driver package, connect the different database, need to import different packages. And while we're writing Java applications, we don't really care much about the classes in these packages. We only need to program with the JDBC interface, which is the interface in the java.sql package and the 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

1. There are four JDBC drivers, and there are two common JDBC drivers. In personal development and testing, you can use the Jdbc-odbc Bridge connection method,
2. In production-oriented development, it is recommended to use a pure Java drive method.

2.3, through the Jdbc-odbc bridge

With 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, the Jdbc-odbc Bridge can access almost all databases. However, because JDBC-ODBC first calls ODBC and then calls the local database interface by ODBC to access the database. Therefore, the execution efficiency is low, for those big data volume Access application is not suitable. Furthermore, this approach requires that the client must have an ODBC driver installed, so it is inappropriate for Internet and intranet-based applications. Because, you can't ask all customers to find an ODBC driver.

The steps for bridging with JDBC-ODBC are as follows:

1. Configure Data Source: Control Panel → Administrative Tools →odbc data source → System DSN
2, programming, connect with the database through the bridge to establish a connection

2.4. Pure Java Driver

The pure Java driver translates the JDBC call directly into a request that conforms to the relevant database system specification. Applications written with this driver can communicate directly with the database server. This type of drive is fully implemented by Java, thus achieving platform independence.

Since the pure Java driver does not need to pass the JDBC call to the ODBC or the local database interface or to the middle-tier server, the JDBC call is converted directly to the network protocol used by the DBMS, so its execution efficiency is very high. This driver can be downloaded dynamically. However, it has a disadvantage, that is, for different databases, you need to download a different driver.

To use a pure Java drive for direct-attached steps:
1. Download the driver package provided by the database vendor
2, the driver package into the project
3, programming, through a pure Java drive to establish a connection with the database

Iii. JDBC Programming Steps

There is a fixed set of steps to connect and manipulate the database using JDBC. By following these steps, you can ensure that you connect to the database correctly and manipulate it.

• The first step is to register the driver, and the Class.forName () method loads the driver class into the virtual machine's memory.
• The second step is to obtain a connection to the database, which is obtained using the getconnection () method of the DriverManager class, and the URL and user name and password of the connection database are given in the parameters. This step will get a database connection object, which is of type connection.
• The third step is to use the database connection object to obtain a statement object that can be used to execute the SQL statement.
• The fourth step is to execute the SQL statement with the statement object. In fact, it is not accurate to say that SQL is executed with the Statement object, because SQL statements can only be executed by the database, Statement is responsible for sending SQL and receiving results. But regardless of who is executing, this step will execute the SQL statement.
• The fifth step is to deal with the results of the execution, if the operation is updated, it may be to determine whether the update is successful, if it is the operation of the query, it may be the output query results and so on.
• Finally, be sure to remember to release the connection to the database. Because the connection to the database is a heavyweight object. If multi-user concurrent access applications, such as the B/s application, each user is only connected but not released, will continue to consume the server resources until the server down.

Iv. Introduction of the JDBC API

The JDBC API mainly defines some interfaces for connecting and manipulating databases, while the specific classes are few. This is because, while developing the Java API, it is not possible to have JDBC connect to a specific database, but only to define a set of specifications. The implementation of the connection-specific database should be done by the manufacturer of the database. Through the unified JDBC specification, we connect the various databases, the code is the same way, which is more conducive to code reuse and porting.

As you can see from the JDBC programming step above, using JDBC to manipulate the database relies on several major JDBC APIs, and then the use of these JDBC APIs is described.

4.1. The DriverManager class is used to manage database-driven

Most of the java.sql packages are interfaces, which is one of the few classes. It is a very common class, the main function is to get the database connection, it defines three connections to the database method, the difference in the number of parameters. The three-parameter getconnection () method is the most common. The three parameters are the URL, user name, and password of the database, respectively.

return type	Method signature	Description
Static Connection	getconnection (String URL)	Attempt to build to a given database URL of the connection
Static Connection	getconnection (String URL, Properties info)	Attempt to build to a given database URL of the connection
Static Connection	getconnection (String URL, String user, String password)	Attempt to build to a given database URL of the connection

4.2. The Java.sql.Connection interface represents the connection object of the application to the database

Implemented by the database vendor, the method of obtaining the connection object is through the getconnection () method of the DriverManager class. Through the connection object, we can get 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 object is used to execute the SQL statement, and is to execute a static SQL statement

The object of the Statement interface is used to execute the SQL statement and is to execute a static SQL statement. The so-called static SQL statement, refers to the SQL statement is determined by a fixed SQL string, the runtime can not modify the parameters

return type	Method signature	Description
Int	Executeupdate (String sql)	Executes the given SQL statement, which may be an INSERT, UPDATE, or delete statement, or an SQL statement (such as a SQL DDL statement) that does not return any content
ResultSet	ExecuteQuery (String sql)	Executes the given SQL statement, which returns a single ResultSet object
void	Close ()	Release the database and JDBC resources for this Statement object immediately, instead of waiting for the This action occurs when the object is automatically closed

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

When we call statement's ExecuteQuery () method, we get a ResultSet object. The ResultSet object contains a result set that is queried against a query statement, but in fact the content is still in the database and is not actually removed into the memory of the virtual machine. ResultSet actually holds a cursor to its current row of data, we need to use the ResultSet method to move the cursor down one line and then fetch the data for each row, so the database connection cannot be closed during the operation of the ResultSet object.

return type	Method signature	Description
Boolean	Next ()	Moves the cursor (cursor) one row forward from the current position
Type	GetType (String ColumnLabel)	Gets the value of the specified column based on the column name of the database table
Type	GetType (int columnindex)	The value of the specified column is obtained from the ordinal of the column, and the ordinal of the first column is 1.
void	Close ()	Release the database and JDBC resources for this ResultSet object immediately, instead of waiting for the This action occurs when an object is automatically closed

Five, java.sql5.1, java.sql description

The API for programming languages to access and process data stored in a data source (typically a relational database). This API includes a framework that allows you to dynamically install different drivers to access different data sources. Although the JDBCTM API is primarily used to pass SQL statements to a database, it can also be used to read and write data from any data source in a tabular manner. javax.sql.RowSetReader/writer utilities that can be used by groups of interfaces can be customized to use and update data from spreadsheets, plain text files, or any other tabular data source

5.2. Java.sql Content

java.sqlThe package contains APIs for the following:

• DriverManagerto establish a connection to a database through a utility program
  • DriverManagerClass: Establishing a connection to a driver
  • SQLPermissionClass: Contemporary Code provides permissions when running in Security Manager (such as an applet), attempting to DriverManager set a record stream
  • DriverInterfaces: Provides APIs for registering and connecting drivers based on JDBC technology ("JDBC Driver"), typically used only by DriverManager classes
  • DriverPropertyInfoClass: Provides the properties of the JDBC driver, not intended for use by the general user
• Sending SQL statements to the database
  • Statement: Used to send basic SQL statements
  • PreparedStatement: Used to send prepared statements or basic SQL statements (derived from Statement )
  • CallableStatement: Used to invoke a database stored procedure (derived from PreparedStatement )
  • ConnectionInterfaces: Provides methods for creating statements and managing connections and their properties
  • Savepoint: Providing a savepoint in a transaction
• Get and update the results of a query
  • ResultSetInterface
• standard mapping relationships for classes and interfaces in SQL type to Java programming language
  • array interface: SQL array mapping relationship
  • blob interface: Mapping relationship for SQL BLOB
  • Clob interface: SQL Clob mappings
  • date class: Mapping relationship for SQL DATE
  • NClob interface: SQL NClob mappings
  • Ref Interface: mapping relationship for SQL REF
  • RowId interface: SQL RowId mapping relationship
  • Struct interface: mapping relationship for SQL STRUCT
  • SQLXML interface: SQL XML mapping relationship
  • time class: Mapping relationship for SQL time
  • Timestamp class: SQL Timestamp mappings
  • TYP ES class: Provides constants for SQL types
• Customizing the mapping of SQL user-defined types (UDTs) to classes in the Java programming language
  • SQLDataInterface: Specifies the mapping of a UDT to an instance of this class
  • SQLInputInterface: Provides a way to read UDT properties from a stream
  • SQLOutputInterface: Provides methods to write the UDT properties back into the Reflow
• Meta data
  • DatabaseMetaDataInterface: Provides information about the database
  • ResultSetMetaDataInterfaces: Provides information about the ResultSet columns of an object
  • ParameterMetaDataInterface: Provides information about the PreparedStatement parameters of the command
• Abnormal
  • SQLException: Thrown by most methods when there is a problem accessing the data, and by some other means for other reasons
  • SQLWarning: Thrown to indicate a warning
  • DataTruncation: to indicate that the data may have been truncated
  • BatchUpdateException: Thrown to indicate that all commands in the bulk update are not executed successfully

Six,JDBC 4.0

The java.sql and features introduced in the JDBC 4.0 API javax.sql

• Automatic java.sql.Driver discovery: No longer need Class.forName to load java.sql.Driver classes by
• Added support for State character set (national Character set)
• Added support for sql:2003 XML data types
• SQLException enhancement: Support added for the reason chain; add new SQLException for common SQLState class value codes
• Enhanced BLOB/CLOB functionality: Provides the ability to create and release Blob/clob instances and some other methods added to improve accessibility
• Added support for accessing SQL ROWID
• Added support that allows JDBC applications to access instances of JDBC resources that have been packaged by vendors, typically in an application server or connection pool environment.
• PooledConnection PreparedStatement the availability to be notified when the associated closed or driver is determined to be invalid

JDBC One (Web Foundation Learning Note Seven)