Content: 2 - Connect This overview is from "JDBCTM Database Access from JavaTM: A Tutorial
and Annotated Reference 》 is extracted from the book. JavaSoft is currently preparing this book. This book is a tutorial and an important reference manual for JDBC, which will be published in the spring of 1997 by Addison-Wesley Publishing Company as an integral part of the Java series.
2.1 Overview
The Connection object represents a connection to the database. The connection process includes the executed SQL statement and the result returned on the connection. An application can have one or more connections to a single database, or can have connections to many databases.
2.1.1 The standard way to open a connection and establish a connection with the database is to call
method. This method accepts a string containing a URL. The DriverManager class (the so-called JDBC management layer) will try to find a driver that can connect to the database represented by that URL. The DriverManager class contains a list of registered Driver classes. When the method getConnection is called, it checks each driver in the manifest until it finds a driver that can connect to the database specified in the URL. Driver's method connect Use this URL to establish the actual connection.
Users can bypass the JDBC management to directly call the Driver method. This will be useful in the following special cases: When two drives can be connected to the database at the same time, and the user needs to explicitly select the specific drive therein. But in general, it will be easier to let the DriverManager class handle opening connections.
The following code shows how to open a connection to a database located at the URL "jdbc:odbc:wombat". The user identifier used is "oboy" and the password is "12Java":
String url = "jdbc:odbc:wombat";
Connection con = (url, "oboy", "12Java");
2.1.2 URLs in general usage Since URLs often cause confusion, we will give a brief explanation of the general URL first, and then discuss JDBC URLs.
The URL (Uniform Resource Locator) provides the information you need to locate a resource on the Internet. Think of it as an address.
The first part of the URL specifies the protocol used to access the information, followed by a colon. Commonly used protocols are "ftp" (representing "File Transfer Protocol") and "http" (representing "Hypertext Transfer Protocol").
If the protocol is "file", it means that the resource is on a local file system rather than on the Internet (the following example is used to represent the part we describe; it is not a component of the URL).
ftp:///docs/JDK-1_apidocs.zip
/products/jdk/CurrentRelease
file:/home/haroldw/docs/books/tutorial/
The rest of the URL (after the colon) gives information about where the data resource is located. If the protocol is a file, the rest of the URL is the path to the file. For the ftp and http protocols, the rest of the URL identifies the host and optionally gives a more detailed address path. For example, the following is the URL of the JavaSoft homepage. This URL identifies only the host:
Start browsing from the homepage and you can enter many other web pages, one of which is
JDBC Home Page. The URL of the JDBC homepage is more specific, it looks similar: /products/jdbc
2.1.3 JDBC URL JDBC URL provides a method to identify a database, allowing the corresponding driver to recognize and establish a connection to the database. In fact, the driver programmer will decide what JDBC URL to use to identify a specific driver. Users don't have to care about how to form a JDBC URL; they just need to use the URL provided with the driver they are using. The role of JDBC is to provide certain conventions that driver programmers should follow when constructing their JDBC URLs.
Since JDBC URLs are to be used with a variety of different drivers, these conventions should be very flexible. First, they should allow different drivers to name the database using different schemes. For example, the odbc subprotocol allows (but does not require) URLs to contain attribute values. Second, the JDBC URL should allow driver programmers to compile all the required information into it. This allows the applet to talk to a given database to open the database connection without requiring the user to do any system management work.
Third, JDBC URLs should allow for some degree of indirectness. That is, the JDBC URL can point to a logical host or database name that will be dynamically converted to the actual name by the network naming system. This allows the system administrator to not have to declare a specific host as part of the JDBC name. There are many types of network naming services (such as DNS, NIS, and DCE), but there is no limit on which naming service to use. The standard syntax for JDBC URLs is shown below. It consists of three parts, separated by colons:
jdbc:<Subprotocol>:<Subname>The three parts of the JDBC URL can be decomposed as follows: jdbc ─Protocol. The protocol in the JDBC URL is always jdbc.
<Subprotocol> ─The name of the driver name or database connection mechanism (this mechanism may be supported by one or more drivers). A typical example of a subprotocol name is "odbc", which is reserved specifically for the URL used to specify an ODBC-style data resource name. For example, in order to access a database through the JDBC-ODBC bridge, you can use the URL shown below:
jdbc:odbc:fred In this example, the sub-protocol is "odbc", and the sub-name "fred" is the local ODBC data resource.
If you want to name a service with a network (so that the database name in the JDBC URL does not have to be the actual name), the naming service can be used as a sub-protocol. For example, a URL like this is available:
jdbc:dcenaming:accounts-payable In this example, the URL specifies that the local DCE naming service should
Database name "accounts-payable" resolves to a more specific name that can be used to connect to the real database. <Subname> ─A method of identifying a database. Subnames can be based on different subprotocols
change. It can also have subnames of subnames (containing any internal syntax selected by the driver programmer). The purpose of using subnames is to provide sufficient information for the location database. forward
In the example, since ODBC will provide the rest of the information, using "fred" is enough. However, a database located on a remote server requires more information. For example, if the database is accessed over the Internet, the network address should be included as part of the subname in the JDBC URL and must follow the standard URL naming convention shown below:
//Hostname: Port/Subprotocol Assuming that "dbnet" is a protocol used to connect a host to the Internet, the JDBC URL is similar:
jdbc:dbnet://wombat:356/fred 2.1.4 "odbc" subprotocol
Sub-protocol odbc is a special case. It is reserved for the URL used to specify the ODBC-style data resource name and has the following characteristics: Allows to specify any multiple attribute values after the child name (data resource name). The complete syntax of the odbc subprotocol is: jdbc:odbc:< Data resource name >[;< Attribute name >=< Attribute value >]* Therefore, the following are all legal jdbc:odbc names: jdbc:odbc:qeor7jdbc:odbc:wombat
jdbc:odbc:wombat;CacheSize=20;ExtensionCase=LOWER
jdbc:odbc:qeora;UID=kgh;PWD=fooey2.1.5 Register subprotocol driver programmers can retain a name to use it as the subprotocol name of the JDBC URL.
When the DriverManager class adds this name to the registered driver list, the driver that retains the name for it should recognize the name and establish a connection to the database it identifies. For example, odbc is reserved for the JDBC-ODBC bridge. Example 2: Assume there is a Miracle company, it might register "miracle" as a subprotocol connected to the JDBC driver on its Miracle DBMS, thus making it impossible for anyone else to use this name. JavaSoft is currently responsible for registering JDBC subprotocol names as an informal agent. To register a sub-protocol name, send an email to the following address:
[email protected] Send SQL statement Once a connection is established, it can be used to transfer SQL statements to the database it involves. JDBC does not place any restrictions on the types of SQL statements that can be sent. This provides great flexibility, that is, allows the use of specific database statements or even non-SQL statements. However, it wants
Ask the user to be responsible for ensuring that the database involved can process the sent SQL statements, otherwise he will suffer the consequences. For example, if an application attempts to send a storage program call to a DBMS that does not support the storage program, it fails and an exception will be thrown. JDBC requires that the driver should at least provide ANSI SQL-2 Entry Level functionality to be considered to be in compliance with JDBC standard TM. This means that users can trust at least this standard level of functionality. JDBC provides three classes for sending SQL statements to a database. Three methods in the Connection interface can be used to create instances of these classes. The following are the classes and their creation methods:
Statement ─ Created by the method createStatement. The Statement object is used to send simple SQL statements.
PreparedStatement ─ Created by the method prepareStatement.
The PreparedStatement object is used to send one or more input parameters (IN parameters)
SQL statements. PreparedStatement has a set of methods that set the value of the IN parameter.
These IN parameters are sent to the database when the statement is executed. PreparedStatement
Examples extend Statement, so they all include Statement methods.
A PreparedStatement object may be more efficient than a Statement object because it has been precompiled and stored there for future use.
CallableStatement ─ Created by method prepareCall. CallableStatement object
Used to execute SQL storage programs - a set of calls that can be called by name (just like a function call)
SQL statement. The CallableStatement object is inherited from PreparedStatement for use
Methods for processing IN parameters, and methods for processing OUT parameters and INOUT parameters are also added.
The methods provided below can quickly determine which Connection method to apply to create different types of SQL statements: The createStatement method is used for:
Simple SQL statement (without parameters) The prepareStatement method is used for: SQL statements with one or more IN parameters Simple SQL statements that are often executed
The prepareCall method is used to: Call stored procedure 2.1.7 Transactions are composed of one or more such statements: These statements have been executed, completed, and committed or restored. When the method commit or rollback is called, the current transaction ends and another transaction begins immediately.
By default, new connections will be in automatic submission mode. That is to say, after the statement is executed, the commit method will be automatically called for that statement. In this case, since each statement is submitted separately, a transaction consists of only one statement. If autocommit mode is disabled, the transaction will wait until the commit or llback method is explicitly called
, so it will include all executed statements since the last call to commit or rollback methods. For the second case, all statements in the transaction will be submitted or restored as groups. Method commit makes any changes made by the SQL statement to the database permanent, and it will also free all locks held by the transaction. And the method rollback will discard those changes.
Sometimes the user does not want another change to take effect before it takes effect. This can be achieved by disabling automatic commits and combining two updates in one transaction. If both updates are successful
, the commit method is called, making the two update results permanent; if one or both of the updates fail, the rollback method is called to restore the value to the value before the update is made.
Most JDBC drivers support transactions. In fact, JDBC-compliant drivers must support transactions. DatabaseMetaData describes the level of transaction support provided by DBMS. 2.1.8 Transaction Isolation Level If DBMS supports transaction processing, it must have some way to manage conflicts that may occur when two transactions operate on a database at the same time. Users can specify transaction isolation levels to indicate how much effort the DBMS should spend on resolving potential conflicts. For example, what should I do when a transaction changes a value and the second transaction reads the value before the change is committed or restored? Assuming that the change value read by the second transaction will be invalid after the first transaction is restored, is this conflict allowed? The JDBC user can use the following code to instruct DBMS to allow the value to be read before it is submitted ("dirty read"), where con is the current connection:
(TRANSACTION_READ_UNCOMMITTED);
The higher the transaction isolation level, the more effort it takes to avoid conflicts. The Connection interface defines five levels, where the lowest level specifies that the transaction is not supported at all, and the highest level specifies that when a transaction is operating on a database, no other transaction can make any changes to the data that that transaction is reading. Generally, the higher the isolation level, the slower the application execution (due to the increased resource consumption for locking, and the reduced concurrent operations between users). When deciding what isolation level to adopt, developers must trade off between performance requirements and data consistency requirements. Of course, the level that can actually be supported depends on the functionality of the DBMS involved.
When a Connection object is created, its transaction isolation level depends on the driver, but is usually the default value for the database involved. The user can change the transaction isolation level by calling the setIsolationLevel method. The new level will take effect for the remainder of the connection process. To change the transaction isolation level of only one transaction, it must be set before the transaction starts and reset after the transaction ends. We do not recommend changing the transaction isolation level midway through the transaction, because this will immediately trigger the call to the commit method, so that in the
Any changes made before this become permanent.
and Annotated Reference 》 is extracted from the book. JavaSoft is currently preparing this book. This book is a tutorial and an important reference manual for JDBC, which will be published in the spring of 1997 by Addison-Wesley Publishing Company as an integral part of the Java series.
2.1 Overview
The Connection object represents a connection to the database. The connection process includes the executed SQL statement and the result returned on the connection. An application can have one or more connections to a single database, or can have connections to many databases.
2.1.1 The standard way to open a connection and establish a connection with the database is to call
method. This method accepts a string containing a URL. The DriverManager class (the so-called JDBC management layer) will try to find a driver that can connect to the database represented by that URL. The DriverManager class contains a list of registered Driver classes. When the method getConnection is called, it checks each driver in the manifest until it finds a driver that can connect to the database specified in the URL. Driver's method connect Use this URL to establish the actual connection.
Users can bypass the JDBC management to directly call the Driver method. This will be useful in the following special cases: When two drives can be connected to the database at the same time, and the user needs to explicitly select the specific drive therein. But in general, it will be easier to let the DriverManager class handle opening connections.
The following code shows how to open a connection to a database located at the URL "jdbc:odbc:wombat". The user identifier used is "oboy" and the password is "12Java":
String url = "jdbc:odbc:wombat";
Connection con = (url, "oboy", "12Java");
2.1.2 URLs in general usage Since URLs often cause confusion, we will give a brief explanation of the general URL first, and then discuss JDBC URLs.
The URL (Uniform Resource Locator) provides the information you need to locate a resource on the Internet. Think of it as an address.
The first part of the URL specifies the protocol used to access the information, followed by a colon. Commonly used protocols are "ftp" (representing "File Transfer Protocol") and "http" (representing "Hypertext Transfer Protocol").
If the protocol is "file", it means that the resource is on a local file system rather than on the Internet (the following example is used to represent the part we describe; it is not a component of the URL).
ftp:///docs/JDK-1_apidocs.zip
/products/jdk/CurrentRelease
file:/home/haroldw/docs/books/tutorial/
The rest of the URL (after the colon) gives information about where the data resource is located. If the protocol is a file, the rest of the URL is the path to the file. For the ftp and http protocols, the rest of the URL identifies the host and optionally gives a more detailed address path. For example, the following is the URL of the JavaSoft homepage. This URL identifies only the host:
Start browsing from the homepage and you can enter many other web pages, one of which is
JDBC Home Page. The URL of the JDBC homepage is more specific, it looks similar: /products/jdbc
2.1.3 JDBC URL JDBC URL provides a method to identify a database, allowing the corresponding driver to recognize and establish a connection to the database. In fact, the driver programmer will decide what JDBC URL to use to identify a specific driver. Users don't have to care about how to form a JDBC URL; they just need to use the URL provided with the driver they are using. The role of JDBC is to provide certain conventions that driver programmers should follow when constructing their JDBC URLs.
Since JDBC URLs are to be used with a variety of different drivers, these conventions should be very flexible. First, they should allow different drivers to name the database using different schemes. For example, the odbc subprotocol allows (but does not require) URLs to contain attribute values. Second, the JDBC URL should allow driver programmers to compile all the required information into it. This allows the applet to talk to a given database to open the database connection without requiring the user to do any system management work.
Third, JDBC URLs should allow for some degree of indirectness. That is, the JDBC URL can point to a logical host or database name that will be dynamically converted to the actual name by the network naming system. This allows the system administrator to not have to declare a specific host as part of the JDBC name. There are many types of network naming services (such as DNS, NIS, and DCE), but there is no limit on which naming service to use. The standard syntax for JDBC URLs is shown below. It consists of three parts, separated by colons:
jdbc:<Subprotocol>:<Subname>The three parts of the JDBC URL can be decomposed as follows: jdbc ─Protocol. The protocol in the JDBC URL is always jdbc.
<Subprotocol> ─The name of the driver name or database connection mechanism (this mechanism may be supported by one or more drivers). A typical example of a subprotocol name is "odbc", which is reserved specifically for the URL used to specify an ODBC-style data resource name. For example, in order to access a database through the JDBC-ODBC bridge, you can use the URL shown below:
jdbc:odbc:fred In this example, the sub-protocol is "odbc", and the sub-name "fred" is the local ODBC data resource.
If you want to name a service with a network (so that the database name in the JDBC URL does not have to be the actual name), the naming service can be used as a sub-protocol. For example, a URL like this is available:
jdbc:dcenaming:accounts-payable In this example, the URL specifies that the local DCE naming service should
Database name "accounts-payable" resolves to a more specific name that can be used to connect to the real database. <Subname> ─A method of identifying a database. Subnames can be based on different subprotocols
change. It can also have subnames of subnames (containing any internal syntax selected by the driver programmer). The purpose of using subnames is to provide sufficient information for the location database. forward
In the example, since ODBC will provide the rest of the information, using "fred" is enough. However, a database located on a remote server requires more information. For example, if the database is accessed over the Internet, the network address should be included as part of the subname in the JDBC URL and must follow the standard URL naming convention shown below:
//Hostname: Port/Subprotocol Assuming that "dbnet" is a protocol used to connect a host to the Internet, the JDBC URL is similar:
jdbc:dbnet://wombat:356/fred 2.1.4 "odbc" subprotocol
Sub-protocol odbc is a special case. It is reserved for the URL used to specify the ODBC-style data resource name and has the following characteristics: Allows to specify any multiple attribute values after the child name (data resource name). The complete syntax of the odbc subprotocol is: jdbc:odbc:< Data resource name >[;< Attribute name >=< Attribute value >]* Therefore, the following are all legal jdbc:odbc names: jdbc:odbc:qeor7jdbc:odbc:wombat
jdbc:odbc:wombat;CacheSize=20;ExtensionCase=LOWER
jdbc:odbc:qeora;UID=kgh;PWD=fooey2.1.5 Register subprotocol driver programmers can retain a name to use it as the subprotocol name of the JDBC URL.
When the DriverManager class adds this name to the registered driver list, the driver that retains the name for it should recognize the name and establish a connection to the database it identifies. For example, odbc is reserved for the JDBC-ODBC bridge. Example 2: Assume there is a Miracle company, it might register "miracle" as a subprotocol connected to the JDBC driver on its Miracle DBMS, thus making it impossible for anyone else to use this name. JavaSoft is currently responsible for registering JDBC subprotocol names as an informal agent. To register a sub-protocol name, send an email to the following address:
[email protected] Send SQL statement Once a connection is established, it can be used to transfer SQL statements to the database it involves. JDBC does not place any restrictions on the types of SQL statements that can be sent. This provides great flexibility, that is, allows the use of specific database statements or even non-SQL statements. However, it wants
Ask the user to be responsible for ensuring that the database involved can process the sent SQL statements, otherwise he will suffer the consequences. For example, if an application attempts to send a storage program call to a DBMS that does not support the storage program, it fails and an exception will be thrown. JDBC requires that the driver should at least provide ANSI SQL-2 Entry Level functionality to be considered to be in compliance with JDBC standard TM. This means that users can trust at least this standard level of functionality. JDBC provides three classes for sending SQL statements to a database. Three methods in the Connection interface can be used to create instances of these classes. The following are the classes and their creation methods:
Statement ─ Created by the method createStatement. The Statement object is used to send simple SQL statements.
PreparedStatement ─ Created by the method prepareStatement.
The PreparedStatement object is used to send one or more input parameters (IN parameters)
SQL statements. PreparedStatement has a set of methods that set the value of the IN parameter.
These IN parameters are sent to the database when the statement is executed. PreparedStatement
Examples extend Statement, so they all include Statement methods.
A PreparedStatement object may be more efficient than a Statement object because it has been precompiled and stored there for future use.
CallableStatement ─ Created by method prepareCall. CallableStatement object
Used to execute SQL storage programs - a set of calls that can be called by name (just like a function call)
SQL statement. The CallableStatement object is inherited from PreparedStatement for use
Methods for processing IN parameters, and methods for processing OUT parameters and INOUT parameters are also added.
The methods provided below can quickly determine which Connection method to apply to create different types of SQL statements: The createStatement method is used for:
Simple SQL statement (without parameters) The prepareStatement method is used for: SQL statements with one or more IN parameters Simple SQL statements that are often executed
The prepareCall method is used to: Call stored procedure 2.1.7 Transactions are composed of one or more such statements: These statements have been executed, completed, and committed or restored. When the method commit or rollback is called, the current transaction ends and another transaction begins immediately.
By default, new connections will be in automatic submission mode. That is to say, after the statement is executed, the commit method will be automatically called for that statement. In this case, since each statement is submitted separately, a transaction consists of only one statement. If autocommit mode is disabled, the transaction will wait until the commit or llback method is explicitly called
, so it will include all executed statements since the last call to commit or rollback methods. For the second case, all statements in the transaction will be submitted or restored as groups. Method commit makes any changes made by the SQL statement to the database permanent, and it will also free all locks held by the transaction. And the method rollback will discard those changes.
Sometimes the user does not want another change to take effect before it takes effect. This can be achieved by disabling automatic commits and combining two updates in one transaction. If both updates are successful
, the commit method is called, making the two update results permanent; if one or both of the updates fail, the rollback method is called to restore the value to the value before the update is made.
Most JDBC drivers support transactions. In fact, JDBC-compliant drivers must support transactions. DatabaseMetaData describes the level of transaction support provided by DBMS. 2.1.8 Transaction Isolation Level If DBMS supports transaction processing, it must have some way to manage conflicts that may occur when two transactions operate on a database at the same time. Users can specify transaction isolation levels to indicate how much effort the DBMS should spend on resolving potential conflicts. For example, what should I do when a transaction changes a value and the second transaction reads the value before the change is committed or restored? Assuming that the change value read by the second transaction will be invalid after the first transaction is restored, is this conflict allowed? The JDBC user can use the following code to instruct DBMS to allow the value to be read before it is submitted ("dirty read"), where con is the current connection:
(TRANSACTION_READ_UNCOMMITTED);
The higher the transaction isolation level, the more effort it takes to avoid conflicts. The Connection interface defines five levels, where the lowest level specifies that the transaction is not supported at all, and the highest level specifies that when a transaction is operating on a database, no other transaction can make any changes to the data that that transaction is reading. Generally, the higher the isolation level, the slower the application execution (due to the increased resource consumption for locking, and the reduced concurrent operations between users). When deciding what isolation level to adopt, developers must trade off between performance requirements and data consistency requirements. Of course, the level that can actually be supported depends on the functionality of the DBMS involved.
When a Connection object is created, its transaction isolation level depends on the driver, but is usually the default value for the database involved. The user can change the transaction isolation level by calling the setIsolationLevel method. The new level will take effect for the remainder of the connection process. To change the transaction isolation level of only one transaction, it must be set before the transaction starts and reset after the transaction ends. We do not recommend changing the transaction isolation level midway through the transaction, because this will immediately trigger the call to the commit method, so that in the
Any changes made before this become permanent.