Shulou(Shulou.com)06/02 Report--

This article introduces how to analyze the standard SQL database access interface JDBC API, the content is very detailed, interested friends can refer to, hope to be helpful to you.

JDBC API defines a series of Java classes that represent database connections, SQL statements, result sets, database metadata, and so on, enabling Java programmers to send SQL statements and process returned results. JDBC API is managed by a driver manager for multiple drivers connected to different databases. JDBC drivers can be written entirely in the Java language, or localized methods can be used to connect to existing database providers.

1. The overall structure Java application accesses JDBC manager through JDBC API interface, and JDBC manager accesses different JDBC drivers through JDBC driver API, thus realizing access to different databases.

1.JDBC API

JDBC API defines a series of abstract Java interfaces that enable application programmers to connect to specified databases, execute SQL statements, and process returned results. Its functional structure is shown in figure 2. @ @ I2; figure 2 the main functional structure of JDBC API @ @ JDBC API are: java.sgl.DriverManager: finish loading the driver and establish a new database connection. Java.sgl.Connection: represents a connection to a specified database. Java.sgl.Statement: manages the execution of SQL statements on a specified database connection. Java.sgl.ResultSet: accesses the original result of a specified statement. Java.sgl.Statement: the interface has two important subtypes: ja-va.sgl.PreparedStatement (for execution of precompiled SQL statements) and java.sgl.CallableStatement (for execution of a database stored procedure).

2. JDBC driver interface

Abstract classes defined in JDBC API must be implemented in the database driver, especially java.sgl.Connection, java.sgl.Prepared-Statement, java.sgl.CallableStatement, and java.sgl.Re-sultSet. In addition, each database driver must provide a class implementation java.sgl.Driver interface for generic java.sgl.DriverManager classes so that they can find the corresponding driver when accessing a specified database URL. Sun Microsystems provides a JDBC-to-ODBC connection bridge, as shown in the JDBC-ODBC bridge driver in figure 1. Because it is based on ODBC, it has the characteristics of small scale and high efficiency.

3. Typical usage patterns of users

(1) Applet mode uses Java language to compile Applet, which is published on the Internet as part of the WWW document, downloaded and run by the browser. Among them, some Applet may need to access the database, which can be achieved by using JDBC API. Because untrusted Applet cannot access local files and establish a network connection to any host, JDBC must comply with the standard Applet security model, so this application model has many security restrictions. For trusted Applet, the security model is similar to that of Java applications.

(2) Application mode in an application, Java code is trusted, so you can read and write local files and establish network connections. The application is shown in figure 4. @ @ I4; figure 4 Application pattern @ @ this application mode is very suitable for intranet (intranet) applications. Of course, this mode can also access the database through Internet. Second, JDBC API 1.JDBC interface definition JDBC API contains java.sgl interfaces and classes. Java.sgl.CallableStatement: the interface for executing stored SQL procedures. Java.sgl.Connection: a connection represents a session with a specified database. In this connection, you can execute SQL statements and process the returned results. Java.sql.DataTruncation: when JDBC encounters unexpected data truncation, it reports a warning (when reading data) or generates an exception (when writing data). Java.sql.Date: is a subset of the standard java.util.date and represents only the number of days, not hours, minutes, and seconds. Java.sql.Driver: define a driver interface that must be implemented in each database driver. Java.sql.DriverManager: provides access to the global SQL state. Java.sql.DriverPropertyInto: provides a means for advanced programmers and drivers to interact with connection feature information. Java.sql.NullData: when a SQL null value is read out by the getXXX or getObiect method, a NullData warning is generated. Java.sql.Numeric: is a class of arbitrary precision scalar values that can be used to represent SQL fixed-point Numerlc and Decimal types. Java.sql.PreparedStatement: an object that holds a precompiled SQL statement that can be executed multiple times efficiently. Java.sql.ResultSet: the result set provides access to the result table resulting from the execution of a SQL statement. The data in the table is taken out in row order. For portability, it is recommended that each row of data be read out in columns from left to right. Java.sql.SQLException: handle error messages while accessing the database. Java.sql.SQLWarning: handles warnings when accessing the database. Java.sql.Statement: used to execute a static SQL statement and receive the resulting result. Java.sql.Time: used to represent a subset of information of the standard java.util.date class, representing only hours, minutes, and seconds. Java.sql.Timestamp: extends the standard java.util.date class to represent the timestamp of SQL, adding a time domain in nanoseconds. Java.sql.Types: defines a constant that distinguishes SQL types. The class constant value is the same as the value in XOPEN. In addition, JDBC API defines JDBC metadata interfaces java.sql.DatabaseMetaData and java.sql.ResultSetMetaData. @ @ I5; the relationship between interfaces @ @ is shown in figure 5, where arrows represent functions and lines represent other methods.

two。 Database connection

(1) establishing a connection when the user accesses the database, a java.sql.Connection object needs to be generated by the java.sql.DriverManager.getConnection method in the JD-BC management layer. This method takes a database URL string as a parameter.

(2) Select the appropriate driver in the database URL, you can specify the name of the driver or not. If you do not specify a driver, search through the driver table indicated in the Java feature "sql.drivers" and use the successfully connected driver found by *.

(3) when database URL is connected, the database to be connected is specified by the database URL parameter, which can be called JDBC URL at this time. Its format is: jdbc::. If you are accessing the network database, it is recommended that you use standard URL as part of the subname. For example, the URL for fred access to data resources may be: jdbc:odbc:fred or jdbc:dbnet://wombat:356/fred sub-protocol odbc indicates access to ODBC data resources in the format of: jdbc:odbc: [; =] * connection parameters: indicated by the java.util.Properties object. It is recommended that most of the parameters are not given here, but are indicated in the agreement. Support for multiple connections: an application can use one or more drivers to establish connections to multiple databases. Driver registration: there are two methods, one is to find the "sql.drivers" feature when the JDBC java.sql.DriverManager class is initialized, and automatically register each driver; the other is to explicitly load a driver by the standard Class.forName method, with the driver name as the argument.

3. Parameter transfer and result reception

(1) after the query result executes a query statement, the returned result is a collection of rows accessible by the java.sql.ResultSet object. A series of "get" methods are provided in this object to access each of the current columns, and the Result-Set.next method enables you to move between the rows of the result set, specifying the corresponding column using a column index or column name. Data conversion of query results. The ResultSet.getXXX method converts the SQL type to the desired Java type. If an illegal type conversion is specified, a SQLException exception is generated. Null value judgment. First read out a column of data, and then use the Result-Set.wasNull method to determine whether the returned result is SQL "NULL". Readout of long data. JDBC supports reading arbitrarily long LONGVARBINARY or LONGVAR-CHAR type data by getByte and getString methods, as well as data streams returned by methods GetBinaryStream, GetAsciiStream, and GetUnicodeStream. The getResultSet, GetUpdateCount and Get-MoreResults methods are supported to return a result, the number of rows that have been modified, and multiple results, respectively.

(2) the java.sql.PreparedStatement interface for passing IN parameters provides a series of setXXX methods to pass parameters to SQL statements to realize dynamic SQL statements. The requirement of consistent data type must be met when passing parameters. Therefore, the type conversion method must be called in advance to complete the data conversion, and it also provides a method to pass SQL null and long data to the IN parameter.

(3) receive OUT parameters when calling a stored procedure, you can use the setXXX method to pass the IN parameter and the OUT parameter to receive the returned result. When using it, you must first call the CallableStatement.register-OutParameter method to register the type of each OUT parameter, and then execute the procedure call statement. * * use the getXXX method to fetch the result of the OUT parameter. The data type of the returned result is the Java type corresponding to the SQL type registered by the user. The processing step of null value is that the parameter value is read out first, and then the CallableStatement.was-Null method is used to determine whether it is null or not. The mechanism for reading out OUT parameters as a stream is not supported. The order in which the results are returned on receipt takes precedence over the OUT parameter.

(4) data truncation under certain conditions, data truncation may occur when reading or writing data. For example, when the * length of a domain is set by Connection.setMaxField-Size, the data exceeding the set length will be truncated. When reading data, if data truncation occurs, a SQLWarning warning for Data-Truncation is generated. When writing data, if data truncation occurs, a SQLException exception of DataTruncation is generated.

4. Conversion from SQL data type to Java data type due to the great differences between SQL data type and Java data type, there are still some inconsistencies between convertible types, so JDBC provides a detailed standard conversion table from SQL type to Java type and from Java type to SQL type.

Third, learn more about JDBC API 1. Async, threading, and transaction

(1) Asynchronous request for some database API (such as ODBC) provides a mechanism for asynchronous execution of SQL statements, which allows the operation of a database to run in the background while the foreground waits for other operations. Because Java provides a multithreading mechanism, there is no real need to implement asynchronous SQL statement execution. When asynchronous execution is required, database operations can be implemented by creating a new thread.

(2) the operation of multithread on all objects of java.sql is multithreaded safe, and the correctness of the operation is guaranteed when multiple threads access an object at the same time. Although different drivers may have different levels of concurrent execution, developers can assume full concurrent execution. Because if the driver needs some form of synchronization, it must provide the corresponding implementation mechanism. Another special application of multithreading is the ability to cancel a statement operation that takes too long to execute. To do this, start another thread to call the Statement.cancel () method

(3) each new JDBC connection for a transaction is initialized to "autocommit" mode, meaning that each statement is executed as a separate transaction. When you need to execute multiple statements as a complete transaction, you can call the Connection.setAutoCommit (false) method to cancel the autocommit. After executing a transaction, call Connection.Commit to explicitly complete the commit, or call Connection.rollback to roll back the entire transaction operation. When a transaction is submitted or rolled back, close all PreparedStatements, CallableStatements, and ResultSets on the connection, only the simple Statements is open.

two。 Pointer

JDBC supports simple pointers, which refer to concepts in the SQL database. The application can use the ResultSet.GetCursorName () method to get the pointer associated with the current Resultset, which can be used to modify and delete the current row. The pointer is valid until the end of the ResultSet or its parent statement.

3. The extended JDBC for SQL all supports SQL-2 basic (Entry) level specification, and some supports SQL-2 transition (Transitional) level specification. There are two points to extend the basic level of SQL-2: one is to support DROP TABLE commands; the other is that the selected transition-level semantics must be supported by Escape syntax, so that a driver can easily scan and translate into a specific DBMS syntax.

(1) SQL Escape syntax in stored procedures, scalar functions, date, time, output connections, etc., JDBC supports the same DBMS-independent Escape escape syntax as ODBC. The format is: {keyword... Parameters. }

(2) the syntax format of activating a stored procedure in stored procedure JDBC is: {call procedure name [parameter 1, parameter 2, …] } or a procedure with a returned result parameter: {? = call procedure name [parameter 1, parameter 2,...] } (3) time and period words JDBC supports the ISO standard format of these text quantities, using Escape escaped statements to represent time and date, such as {d 'yyyy-mm-dd'} or {t 'nn:mm:ss'}, respectively.

(4) the scalar function JDBC supports numeric, string, time, date, system, and conversion functions of scalar values, such as {fn concat ("Hot", "Java")}.

(5) output connection syntax format is: {oj outer-join} where outer-join is in the form of table LEFT OUTER JOIN {table (outer-join} ON search-condition)

4. Dynamic database access

Although we want JDBC programmers to understand the schema of the database when programming, so that they can access the database using strongly typed JDBC interfaces. However, an application program sometimes needs to get the database schema dynamically and use this information to complete the corresponding dynamic database access.

(1) metadata information JDBC can access metadata, such as row result description, statement parameters, database characteristics and so on. The Java.Sql.ResultSetMetaData type provides a number of ways to get the type and properties of a specified column of Java.sql.ResultSet objects. The Java.Sql.DatabaseMetaData interface provides a series of methods to obtain all kinds of metadata associated with a database, including database process, table, schema, etc., as well as column, column access, table access and other information in the table.

(2) dynamically typed data access JDBC supports more general data access using ResultSet.getObject, PreparedStatement.setObject, and CallableStatement.getObject methods. The conversion tables from SQL types to Java object types and from Java object types to SQL types during dynamic access are not enumerated here. But the type conversion in the above three methods is based on these two tables.

Java language provides API to access the database, which is very helpful to broaden its scope of application. Since only one basic API has been implemented at present, we hope that developers can build more advanced classes and application tools on this basis. At the same time, I also hope that many developers will provide a variety of database drivers to meet the needs of programmers to access different database resources.

On how to analyze the standard SQL database access interface JDBC API to share here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.