JDBC Overview (2)

JDBC Overview (2)

2 JDBC connection Overview

The connection object represents the connection to the database. The connection process includes the executed SQL statement and the result returned by the connection. One applicationProgramYou can have one or more connections to a single database or many databases.

2.1.1 open a connection

The standard method for establishing a connection with a database is to call the drivermanager. getconnection method. This method accepts strings containing a URL. The drivermanager class (the so-called JDBC management layer) will try to find the driver that can be connected to the database represented by that URL. The drivermanager class contains a list of registered driver classes. When the getconnection method is called, it checks every driver in the list until it finds the driver that can be connected to the database specified in the URL. The driver method connect uses this URL to establish the actual connection.

You can bypass the JDBC management layer to directly call the driver method. This can be useful in the following special circumstances: when two drives can be connected to the database at the same time, the user needs to explicitly select the specific drive. But in general, it is easier to let the drivermanager class handle the process of opening a connection.

BelowCodeShows how to open a connection to a database in the URL "JDBC: ODBC: wombat. The User ID is "oboy", and the password is "12 Java": String url = "JDBC: ODBC: wombat ";
Connection con = drivermanager. getconnection (URL, "oboy", "12 Java ");

2.1.2 General URL

Because URLs are often obfuscated, we will first briefly describe general URLs and then discuss JDBC URLs.

URL (Unified Resource Locator) provides the information needed to locate resources on the Internet. You can think of it as an address. The first part of the URL specifies the protocol used to access information, followed by a colon. Common protocols include FTP and HTTP "). If the protocol is "file", the resource is on a local file system rather than on the Internet (The following example is used to indicate the part we described; it is not a part of the URL ).

Ftp://javasoft.com/docs/JDK-1_apidocs.zip
Http://java.sun.com/products/jdk/CurrentRelease
File:/home/haroldw/docs/books/tutorial/summary.html

The rest of the URL (after the colon) provides information about the location of the data resource. If the protocol is file, the rest of the URL is the file path. For the FTP and HTTP protocols, the rest of the URL identifies the host and can provide a more detailed address path. For example, the following is the URL of the external oft homepage. This URL only identifies the Host:

The http://java.sun.com starts browsing from the home page and you can access many other pages, one of which is the JDBC home page. The URL of the JDBC home page is more specific and looks like: http://java.sun.com/products/jdbc

2.1.3 JDBC URL

Jdbc url provides a way to identify a database, so that the corresponding driver can identify the database and establish a connection with it. In fact, the driver programmer will decide what jdbc url to use to identify a specific driver. Users do not have to worry about how to form JDBC URLs; they only need to use the URLs provided with the driver used. The role of JDBC is to provide certain conventions that drivers programmers should follow when constructing their JDBC URLs.

Since JDBC URLs must be used together with various drivers, these conventions should be flexible. First, they should allow different drivers to use different schemes to name databases. For example, the ODBC sub-protocol allows (but is not required) URLs to contain attribute values. Second, the jdbc url should allow the driver programmer to include all required information. In this way, you can enable the database connection of the applet that you want to talk to a given database without requiring you to perform any system management work. Third, JDBC URLs should allow some degree of indirect. That is to say, the jdbc url can point to the logical host or database name, and the logical host or database name will be dynamically converted from the network naming system to the actual name. This prevents the system administrator from declaring a specific host as a part of the JDBC name. There are multiple network naming services (such as DNS, NIS, and DCE), but there is no restriction on which Naming Service to use. The standard syntax of jdbc url is as follows. It consists of three parts separated by colons:

JDBC: <sub-Protocol >:< sub-Name>

The jdbc url can be divided into the following three parts: JDBC-protocol.
The protocol in the jdbc url is always JDBC.
<Sub-Protocol>-name of the driver name or database connection mechanism (this mechanism can be supported by one or more drivers. A typical example of a sub-protocol name is "ODBC", which is specially reserved for URLs used to specify ODBC-style data resource names. For example, to access a database through a JDBC-ODBC bridge, you can use the following URL:

JDBC: ODBC: Fred

In this example, the sub-protocol is "ODBC" and the sub-name "Fred" is a local ODBC data resource.

If you want to use the network Naming Service (the database name in the jdbc url does not need to be the actual name), the naming service can be used as a sub-protocol. For example, the following URL is available: JDBC: dcenaming: Accounts-payable. In this example, this URL specifies that the local DCE Naming Service should resolve the database name "accounts-payable" to a more specific name that can be used to connect to the real database. <Sub-Name>-a method used to identify a database. Sub-names can vary according to different sub-protocols. It can also have sub-names (containing any internal syntax selected by the driver programmer ). The sub-name is used to provide sufficient information for locating the database. Because ODBC provides the rest of the information, it is sufficient to use "Fred. However, databases on remote servers need more information. For example, if the database is accessed through the Internet, the network address should be included as a part of the sub-name in the jdbc url, and the following standard URL naming conventions must be observed: // host name: Port/sub-Protocol. If "dbnet" is a protocol used to connect a host to the Internet, the jdbc url is similar:
JDBC: dbnet: // wombps: 356/Fred

2.1.4 "ODBC" sub-Protocol

The sub-Protocol ODBC is a special case. It is reserved for specifying the URL of an ODBC-style data resource name and has the following features: You can specify any number of attribute values after a sub-Name (data resource name. The complete syntax of the ODBC sub-protocol is: JDBC: ODBC: <data resource Name> [; <attribute name >=< attribute value>] *

Therefore, the following are valid JDBC: ODBC names:

JDBC: ODBC: qeor7jdbc: ODBC: wombat
JDBC: ODBC: wombat; cachesize = 20; extensioncase = lower
JDBC: ODBC: qeora; uid = kgh; Pwd = fooey

2.1.5 register a sub-agreement

A driver programmer can retain a name as a sub-protocol name for a jdbc url.
When the drivermanager class adds the name to the registered driver list, the driver that retains the name should be able to identify the name and establish a connection with the database it identifies. For example, ODBC is reserved for the JDBC-ODBC bridge.

Example 2: assume there is a miracle company that may register "miracle" as a sub-Protocol connecting to the JDBC driver on its miracle DBMS, so that no one else can use this name. Javasoft is currently responsible for registering the JDBC sub-protocol name as an informal proxy. To register a sub-protocol name, send an email to the following address:

Jdbc@wombat.eng.sun.com

2.1.6 sending SQL statements

Once a connection is established, it can be used to send SQL statements to the database it involves. JDBC imposes no restrictions on the types of SQL statements that can be sent. This provides great flexibility, that is, to allow the use of specific database statements or even non-SQL statements. However, it requires the user to be responsible for ensuring that the database involved can process the SQL statements sent; otherwise, the result will be self-defeating. For example, if an application attempts to send a storage program call to a DBMS that does not support the storage program, it will fail and throw an exception. JDBC requires that the driver be able to provide at least the ANSI SQL-2 entry level function to be considered compliant with the JDBC standard TM. This means that users can trust at least this standard-level function. JDBC provides three types for sending SQL statements to the database. The three methods in the connection interface can be used to create instances of these classes. These classes and their creation methods are listed below:

Statement-created by the createstatement method. The statement object is used to send simple SQL statements.
Preparedstatement-created by the preparestatement method.
The preparedstatement object is used to send an SQL statement with one or more input parameters (in parameter. Preparedstatement has a set of methods used to set the value of the in parameter.

When a statement is executed, these in parameters are sent to the database. Preparedstatement instances extend the statement, so they all include the statement method.

The preparedstatement object may be more efficient than the statement object because it has been precompiled and stored there for future use.

Callablestatement-created by the preparecall method. The callablestatement object is used to execute the SQL storage Program-a group of SQL statements that can be called by name, just like a function call. The callablestatement object inherits the methods used to process the in parameter from the preparedstatement object, and also adds methods used to process the out and inout parameters.

The methods listed below can quickly decide which connection method to apply to create different types of SQL statements:

The createstatement method is used for a simple SQL statement (without parameters). The preparestatement method is used for a simple SQL statement that is frequently executed with one or more in parameters. The preparecall method is used: call Stored Procedure

2.1.7 transactions

A transaction is composed of one or more such statements: these statements have been executed, completed, committed, or restored. When the method commit or rollback is called, the current transaction ends and another transaction starts immediately.

By default, new connections are automatically submitted. That is, after the statement is executed, the commit method is automatically called for that statement. In this case, because each statement is submitted separately, a transaction is composed of only one statement. If the automatic commit mode is disabled, the transaction ends only when the commit or rollback method is explicitly called. Therefore, it includes all statements executed since the last call of the commit or rollback method. In the second case, all statements in the transaction are committed or restored as groups.

The commit method makes any changes made by the SQL statement to the database permanent, and it also releases all the locks held by the transaction. The rollback method will discard those changes.

Sometimes users do not want the change to take effect before another change takes effect. This can be achieved by disabling automatic commit and combining two updates in one transaction. If both updates are successful, call the commit method to make the two updates permanent. If either or both of the updates fail, call the rollback method, to restore the value to the value before the update.

Most JDBC drivers support transactions. In fact, JDBC-compliant drivers must support transactions. The information provided by databasemetadata describes the level of transaction support provided by DBMS.

2.1.8 transaction isolation level

If the DBMS supports transaction processing, it must have some way to manage the conflicts that may occur when two transactions operate on a database at the same time. You can specify the transaction isolation level to specify how much effort the DBMS should spend to resolve potential conflicts. For example, when a transaction changes a value and the second transaction reads the value before the change is committed or restored, assuming that the first transaction is restored, the change value read by the second transaction is invalid. Can this conflict be allowed? JDBC users can use the following code to instruct DBMS to allow reading a value ("Dirty read") before the value is submitted, where con is the current connection:

Con. settransactionisolation (transaction_read_uncommitted );

The higher the transaction isolation level, the more effort it takes to avoid conflicts. The connection interface defines five levels. The minimum level specifies that transactions are not supported at all, while the highest level specifies that when a transaction operates on a database, no other transaction may make any changes to the data being read by that transaction. Generally, the higher the isolation level, the slower the application execution speed (because the resource consumption for locking is increased, and the concurrent operations between users are reduced ). When deciding on the isolation level, developers must weigh between performance requirements and data consistency requirements. Of course, the level actually supported depends on the DBMS functions involved.

When a connection object is created, the transaction isolation level depends on the driver, but usually the default value of the involved database. You can change the transaction isolation level by calling the setisolationlevel method. The new level will take effect for the remaining time of the connection process. To change the transaction isolation level of a transaction, you must set it before the transaction starts and reset it after the transaction ends. We do not advocate changing the transaction isolation level in the middle of the transaction, because this will immediately trigger the call to the commit method, making any changes made before this permanent.