JDBC

xiaoxiao2021-03-06  104

1.1 What is JDBCTM? JDBCTM is a Javatm API for executing the SQL statement. JDBC itself is a trademark name instead of an abbreviated word; however, JDBC is often considered to be a "Java Database Connectivity"). It consists of a set of classes and interfaces written in Java programming languages. JDBC provides a standard API for tools / database developers, enabling them to write database applications with pure Java APIs. 4 With JDBC, send SQL statements to various relational databases is a very easy thing. In other words, there is a JDBC API, you don't have to write a program for accessing the Sybase database, and write a program for accessing the Oracle database, and write another program for accessing the Informix database, and so on. You only need to write a program with JDBC API, which can send SQL statements to the corresponding database. Moreover, using the application written in Java programming language does not need to worry about writing different applications for different platforms. Combine Java and JDBC will allow programmers to write only a program to run on any platform. Java has rugged, safe, easy to use, easy to understand and automatically downloaded from the network, is an outstanding language to write database applications. The required method is only a method of dialogue between the Java application and various databases. JDBC is a mechanism for such uses. JDBC extends the functionality of Java. For example, using Java and JDBC APIs can publish a web page containing the applet, and the information used by the applet may come from a remote database. Enterprises can also use JDBC to connect all staff into one or more internal databases over Intranet (even if the computers used by these staff have various operating systems such as Windows, Macintosh, and UNIX). As more and more programmers began using Java programming languages, the requirements from Java in conjunction with the database are also increasing. MIS administrators like Java and JDBC combination because it makes information spread easy and economical. Companies can continue to use them to install the database and can easily access information, even if this information is stored on different database management systems. The new program is very short. Installation and version control will be greatly simplified. Programmers can only write only one version or only update once, then put it on the server, and anyone can get the latest version of the application. For business sales information services, Java and JDBC provide better ways to obtain information updates for external customers. 1.1.1 What is the use of JDBC? Simply put, JDBC can do three things: establish a connection with the database, send the SQL statement, and process the results. The following code segment gives the basic example of the above three steps: Connection Con = DriverManager.getConnection ("JDBC: ODBC: Wombat", "Login", "Password"); statement stmt = con.createstatement (); ResultSet RS = STMT .ExecuteQuery ("SELECT A, B, C from table1"); while (RS1.1.2 JDBC is a low-level API, the basic JDBC of the advanced API is a "low" interface, that is, it is used to call the SQL command directly In this regard, its function is excellent and easy to use than other databases, but it is also designed as a foundation interface, which can establish advanced interfaces and tools.

The advanced interface is an "friendly" interface for the user. It is a more easy to understand and more convenient API, which is converted to a low-level interface such as JDBC behind the scenes. When writing this article, two JDBC-based advanced APIs are being developed: an embedded SQL for Java. At least one provider has planned to write it. DBMS implementation SQL: a language specially designed to use in combination with the database. JDBC requires a SQL statement to pass to the Java method as a string. Instead, the embedded SQL preprocessor allows programmers to use the SQL statement directly with Java. For example, Java variables can be used in SQL statements for accepting or providing SQL values. Then, the embedded SQL preprocessor converts the mixture of this Java / SQL into Java via JDBC calls. Direct mapping of relational database tables to Java classes. Both JavaSoft and other providers claim to implement the API. In such an "object / relationship" map, each row in the table corresponds to an instance of the class, and the value of each column corresponds to an attribute of the instance. Thus, the programmer can operate directly to the Java object; the SQL call required to access data will be automatically generated in "cover". More complex mappings can also be provided, such as combining rows in multiple tables into a Java class. As people's interest in JDBC is increasing, more and more developers have been using JDBC-based tools to make the program's writing easier. Programmers have been writing to make the end user's access to the database more simple applications. For example, an application provides a menu that selects a database task. After the task is selected, the application will give prompts and blank to fill in the information required to perform the selected task. After the required information is entered, the application will automatically call the desired SQL command. With the assistance of such a program, even if the user does not understand the syntax of SQL, the database task can also be performed. 1.1.3 Comparison of JDBC and ODBC and Other APIs Currently, Microsoft's ODBC (Open Database Connection) API may be the most widely used and used to access programming interfaces for relational databases. It can connect almost all databases on almost all platforms. Why does Java do not use ODBC? The answer to this question is: Java can use ODBC, but it is best to use the JDBC-ODBC bridge with the help of JDBC. This will then say later. The problem now has become: "Why do you need JDBC? The answer is as follows: ODBC is not suitable for use directly in Java because it uses a C language interface. There are many disadvantages from Java to call local C code in security, implementation, robustness, and automatic portability of procedures. Field translation from ODBC C API to Java API is not available. For example, Java does not have a pointer, while ODBC is widely used (including pointers "void *"). You can imagine JDBC to be converted to ODBC-oriented interface, and object-oriented interfaces are easier to receive for Java programmers. ODBC is difficult to learn. It is mixed with simple and advanced features, and even for simple queries, its options are also extremely complicated. Instead, JDBC should ensure the simple functionality of simple functions, while the advanced features are allowed when necessary. Enable the "Pure Java" mechanism requires Java API like JDBC. If you use ODBC, you must manually install the ODBC driver manager and driver on each client. If you write a JDBC driver using Java, the JDBC code can be automatically installed, transplanted, and assured security on all Java platforms (from network computers to large machines).

In summary, JDBC API is a natural Java interface for basic SQL abstractions and concepts. It is built on ODBC instead of from scratch. Therefore, the programmer familiar with ODBC will find that JDBC is easy to use. JDBC retains the basic design characteristics of ODBC; in fact, both interfaces are based on the X / Open SQL CLI (call-level interface). The biggest difference between them is that JDBC is optimized based on Java style and advantages, making it easier to use. Recently, Microsoft introduced new APIs outside ODBC: RDO, ADO, and OLE DB. These designs are the same as JDBC in many ways, ie they are object-oriented database interfaces and are based on classes that can be implemented on ODBC. However, in these interfaces, we have not seen that there is a special feature to choose them to replace ODBC, especially in the case of the ODBC driver has established a relatively complete market. Their most is also a decoration in ODBC. This is not to say that JDBC does not need to be developed from its original version; however, we feel that most new features should be induced into the object / relationship maps and embedded SQLs such as the previous section. Advanced API. 1.1.4 Two-layer model and three-layer model JDBC API support both two-layer models of database access, and also supports three-layer models. In two layers models, Java Applets or applications will talk directly to the database. This will require a JDBC driver to communicate with the accessed specific database management system. The user's SQL statement is sent to the database, and the result will be sent back to the user. The database can be located on another computer, and the user is connected to the above. This is called client / server configuration, where the user's computer is a client, providing a computer for a server for a server. The network can be intranet (it can connect the company staff) or the Internet. In the three-layer model, the command first is sent to the "intermediate layer" of the service, and then it is sent to the database by it. The database is handled on the SQL statement and send the result back to the intermediate layer, and the intermediate layer is sent back to the user. The MIS executives found that the three-layer model was very attractive because the intermediate layer can be used to control the type of access to the company's data and the available update. Another benefit of the intermediate layer is that users can utilize the easy-to-use advanced APIs, while the intermediate layer will convert it to the corresponding low-level calls. Finally, in many cases the three-layer structure can provide some performance benefits. So far, the intermediate layer is usually written in a C or C language, and these languages ​​are executed faster. However, with the introduction of the optimization compiler (which converts Java byte code to efficient machine-specific machine-specific, the intermediate layer will become increasingly practical. This will be a big progress that allows people to make full use of the advantages of Java (such as rugged, multi-threaded and security). JDBC is very important for accessing the database from the intermediate layer of Java. 1.1.5 Single Structured Query Language (SQL) is a standard language for accessing the relational database. Difficulties are: Although most DBMS (database management systems) use standard SQLs for their basic functions, they do not meet the recent standard SQL syntax or semantics defined for more advanced features. For example, not all databases support storage programs or external connections, and databases that support this feature are inconsistent. That of the true standard in SQL is able to expand to include more and more functions. But at the same time, the JDBC API must support existing SQL. One way to solve this problem is to allow any query string to be transferred to the DBMS driver involved.

This means that the application can use any more SQL features, but it must take such a risk: it is possible to make an error on some DBMS. In fact, the application query does not even have to be SQL, or it can be a dedicated organ for SQL designed for a specific DBMS (eg, document or image check). The second method of the JDBC handling the SQL consistency problem is to provide an ODBC style essential clause. This will be discussed in Section 4.1.5 "SQL Swordic Syntax". Syntax provides a standard JDBC syntax for several common SQL disagreements. For example, there is a syntax for the call to date text and stored procedures. For complex applications, JDBC uses a third method to process SQL consistency issues. It uses the DatabaseMetadata interface to provide descriptive information about DBMS, allowing the application to adapt to each DBMS requirements and functions. Since the JDBC API will be used as the foundation API of the Advanced Database Access Tool and API, it must also pay attention to all of its upper buildings. "JDBC Standard TM" represents the standard level of the JDBC function that the user can rely on. To use this description, the driver must at least support ANSI SQL-2 Entrylevel (ANSI SQL-2 represents the specific list of the standards used by the US National Standards Bureau in 1992.Entry Level represents a specific list of SQL functions). The driver developer can use the Test Kit with JDBC API to determine if their drivers meet these standards. "JDBC Standard TM" means that the provider's JDBC implementation has passed the consistency test provided by JavaSoft. These consistency tests will check all the classes and methods defined in JDBCAPIs, and check whether the program has SQL Entry Level feature as much as possible. Of course, these tests are not complete, and JavaSoft currently does not intend to standardize the implementation of each provider. But this consistency definition can indeed provide a certain credibility of JDBC implementation. With more and more database providers, connection providers, Internet providers, and application programmers accepted by JDBC API, JDBC is also rapidly becoming the standard of Java database access. 1.2 JDBC products When writing this article, there are several JDBC-based products that have been developed or are being developed. Of course, the information in this section will soon become outdated information. Therefore, please refer to the JDBC website for the latest information, you can start browsing from the following URL: http://java.sun.com/products/jdbc1.2.1 JavaSoft Framework Javasoft provides three JDBC product components, they are Java Development Toolkit (JDK) part: JDBC Driver Manager, JDBC Driver Test Kit, and JDBC-ODBC Bridge. The JDBC driver manager is the pillar of the JDBC architecture. It is actually very small, it is very simple; its main role is to connect the Java application to the correct JDBC driver, and then exit. The JDBC Driver Test Kit provides a certain credibility to make the JDBC driver run your program. Only the driver that passes the package through the JDBC driver is considered to be a JDBC standard TM. The JDBC-ODBC bridge allows the ODBC driver to be used as a JDBC driver. Its implementation provides a way for JDBC's rapid development, and its long-term goal provides a way to access certain uncommon DBMSs (if these uncommon DBMS unpreceptible JDBCs). 1.2.2 Type of JDBC Driver Our currently known JDBC driver can be divided into the following four categories: JDBC-ODBC bridge plus ODBC driver: JavaSoft bridge products provide JDBC access using the ODBC driver.

Note that the ODBC binary code (in many cases also includes database client code) is loaded on each client using the driver. Therefore, this type of driver is best suited for enterprise network (this network client installation is not the main issue), or the three-layer structure of the three-layer structure written in Java. Local API - Some drivers written in Java: This type of driver converts JDBC calls on the client API to Oracle, Sybase, Informix, DB2, or other DBMS calls. Note that like a bridge driver, this type of driver requires some binary code to each customer BLOG:

http://blog.9cbs.net/chensheng913/

2 JDBC Connection Overview Connection object represents the connection to the database. The connection process includes the resulting SQL statement and the result returned on the connection. An application can have one or more connections with a single database, or can be connected to many databases. 2.1.1 Opening the connection to the database to establish a connection to the database is to call the DriverManager.getConnection method. This method accepts a string containing a URL. The DriverManager class (ie, the so-called JDBC Management) will try to find a driver that can be connected to the database represented by the URL. The DRIVERMANAGER has a list of registered DRIVER classes. When the method getConnection is called, it will check each driver in the list until a driver that can be connected to the database specified in the URL. Driver's method Connect uses this URL to establish a practical connection. The user can bypass the JDBC management directly call the Driver method. This will be useful in the following special circumstances: When the two drivers can be connected to the database, the user needs to expressly select the specific drive. But in general, let DRIVERMANAGER class handle this kind of job will be simpler. 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", the password is "12java": String Url = "JDBC: ODBC: Wombat"; connection con = DriverManager.getConnection (URL, "OBOY", "12java"); 2.1.2 General usage URLs are often confused because the URL is often confused, and we will first simply explain the general URL, and then discuss the JDBC URL. URL (Uniform Resource Locator) provides information required to locate resources on the Internet. You can imagine it as an address. The first part of the URL specifies the protocol used to access the information, which is always following the colon. Commonly used protocols have "FTP" (representing "File Transfer Protocol") and "HTTP" (represents "Hypertext Transfer Protocol"). If the protocol is "file", it means that the resource is on a local file system, not on the Internet (the following example is used to represent the part we described; it is not a component component). FTP: //javasoft.com/docs/jdk-1_apidocs.ziphtp://java.sun.com/products/jdk/currentreleasefile:/Home/HaroldW/DOCS/Books/Tutorial/summary.htmlurl's remainder (colon) The following) gives information about the location of the data resource. If the protocol is file, the remaining part of the URL is the path to the file. For FTP and HTTP protocols, the remaining parts of the URL identify the host and optionally give a more detailed address path. For example, the following is the URL of the JavaSoft home page. This URL only identifies the host: http://java.sun.com You can go to many other web pages from this homepage, one of which is the JDBC home page. The URL of the JDBC home page is more specific. It looks like: http://java.sun.com/products/jdbc2.1.3 JDBC URL JDBC URL provides a method of identifying the database, allowing the corresponding driver to identify the The database is connected to it. In fact, the driver programmer will determine what JDBC URL identifies a particular driver.

Users don't have to care about how to form a JDBC URL; they only need to use the URL provided with the drivers used. JDBC's role is to provide some conventions, and the driver programmer should follow these conventions when constructing their JDBC URL. Since the JDBC URL should be used with a variety of drivers, these agreements should be very flexible. First, they should allow different drivers to use different scenarios to name the database. For example, the ODBC sub protocol allows (but not required) URL contains attribute values. Second, the JDBC URL should allow the driver programmer to be encapsulated in it. This allows you to open a database connection with the applet to a given database dialog, without requiring users to do any system management. Third, the JDBC URL should allow some degree of indirectness. That is, the JDBC URL can point to the logical host or database name, and this logical host or database name will dynamically convert to the actual name by the network naming system. This allows the system administrator to declare a particular host as part of the JDBC name. Network naming services (such as DNS, NIS, and DCE) have a variety of, but no restrictions on which naming service used. The standard syntax of the JDBC URL is as follows. It consists of three parts, each part is separated by colon: JDBC: : JDBC URL can be decomposed as follows: JDBC - protocol. The protocol in the JDBC URL is always JDBC. ─ Driver name or database connection mechanism (this mechanism can be supported by one or more drivers). A typical example of the sub-protocol name is "ODBC", which is specifically reserved for the URL of the data resource name for specifying the ODBC style. For example, in order to access a database via the JDBC-ODBC bridge, the URL: JDBC: ODBC: Fred this example is used as shown below, the sub-protocol is "ODBC", the child name "fred" is the local ODBC data resource. If you want to use a network naming service (such a database name in the JDBC URL does not have to be an actual name), the naming service can be used as a sub-protocol. For example, the URL: JDBC: DCENAMING: JDBC: DCENAMING: ACCOUNTS-PAYABLE This example can be used, which specifies the local DCE naming service to resolve the database name "Accounts-Payable" to a more specific name that can be used to connect the real database. . Method for identifying the database. The child name can vary depending on the sub-protocol. It also has a sub-name of a child (including any internal syntax selected by the driver programmer). The purpose of using sub-name is to provide sufficient information for positioning the database. In the precedent, since the ODBC will provide information on the remaining part, it is sufficient to use "FRED". However, the database on the remote server requires more information. For example, if the database is accessed through the Internet, a part of the network address is included as a sub-name in the JDBC URL, and must follow the standard URL naming convention as shown below: // host name: port / child The protocol assumes that "DBNET" is a protocol used to connect a host to the Internet, then JDBC URL is similar: JDBC: DBNET: // Wombat: 356 / Fred 2.1.4 "ODBC" sub-protocol protocol ODBC is a special Happening. It is preserved for the URL for specifying the data resource name of the ODBC style, and has the following characteristics: Allow any multiple attribute values ​​to be specified after the child name (data resource name).

The full syntax of the ODBC sub protocol is: JDBC: ODBC: [; = ] * So, the following is a legal JDBC: ODBC Name: JDBC: ODBC: QEOR7JDBC: ODBC: Wombatjdbc: ODBC: Wombat; cachesize = 20; extensioncase = limited = limited; uid = kgh; pwd = fooey2.1.5 Registered sub-protocol driver programmer can preserve a name to use as JDBC URL . When the DRIVERMANAGER class adds this name to the registered driver list, the name of the name should be recognized and connected to the database identified by it. For example, ODBC is reserved for a JDBC-ODBC bridge. Example 2, assume that there is a Miracle company, which may register "miracle" as a sub-protocol to connect to the JDBC driver on its Miracle DBMS, so that others cannot use this name. JavaSoft is currently responsible for registering the JDBC sub protocol name as an informal agent. To register a sub-protocol name, send an email to the following address: jdbc@wombat.eng.sun.com 2.1.6 Send SQL statement connections Once established, you can use to transfer SQL statements to the database it involved. JDBC does not add any restrictions on the type of SQL statement that can be sent. This provides a lot of flexibility that allows specific database statements or even non-SQL statements. However, it requires the user to ensure that the database involved can handle the sent SQL statement, otherwise you will be self-sufficient. For example, if an application tries to send a storage program to the DBMS that does not support the storage program, it will fail and will throw an exception. JDBC requires a driver to provide at least ANSI SQL-2 Entry Level functionality to be in accordance with JDBC standard TM. This means that users can at least trust this standard level. JDBC provides three classes to send SQL statements to the database. Three methods in the Connection interface can be used to create instances of these classes. These classes are listed below and their creation methods: Statement - created by method CreateStatement. The Statement object is used to send a simple SQL statement. PreparedStatement - created by method prepareStatement. The PreparedStatement object is used to send a SQL statement with one or more input parameters (IN parameters). PreparedStatement has a group of methods for setting the value of the IN parameter. These IN parameters will be sent to the database when performing statements. The example of PreparedStatement extends Statement, so they all include a STATEMENT method. The PreparedStatement object is likely to be higher than the STATEMENT object, as it has been precompiled and stored there for future use. CallableStatement - created by Method PrepareCall. The CallableStatement object is used to execute SQL storage programs - a set of SQL statements that can call (as in the call of the icon "by name. The CallableStatement object inherits the method of processing the IN parameter from PreparedStatement, and also adds a method for processing OUT parameters and inout parameters.

The methods provided below can quickly decide which connection method to use to create different types of SQL statements: CreateStateMent method for: Simple SQL statement (without parameters) pre previoustement method for: SQL statement with one or more in parameters A simple SQL statement prepareCall method that is often performed is used for: Calling the stored procedure 2.1.7 Transaction transaction consists of one or more of such statements: These statements have been executed, completed and submitted or restored. When the method commit or rollback is called, the current transaction ends, and another transaction begins. By default, the new connection will be in automatic submission mode. That is, after the expression is performed, the COMMIT method will be called automatically. In this case, since each statement is submitted separately, a transaction consists only by one statement. If automatic submission mode is disabled, the transaction will wait until the CommT or Rollback method is explicitly invoked, so it will include all the statements that have been executed since the last call commit or rollback method. For the second case, all statements in the transaction will be submitted or restored as a group. Methods Commit makes any changes made by the SQL statement to the database, which will also release all locks held by the transaction. The method ROLLBACK will discard those changes. Sometimes the user does not want this to take effect before another change. This can be achieved by disabling automatic submission and combining two updates in a transaction. If the two updates are successful, call the commit method so that the two update results become permanent; if one or two updates fails, call the ROLLBACK method to restore the value to be updated. value. Most JDBC drivers support transactions. In fact, drivers that meet JDBC must support transactions. DatabaseMetadata gives the information given by DBMS. 2.1.8 Transaction isolation Level If DBMS supports transaction processing, it must have some way to manage two transactions simultaneously conflicts with a database. Users can specify a transaction isolation level to indicate how DBMS should spend more energy to solve potential conflicts. For example, when the transaction changes a certain value and the second transaction is to read this value before the change is submitted or restored, the first transaction is restored, the change value read by the second transaction Will be invalid, then this conflict can be allowed? The JDBC user can use the following code to indicate that the DBMS allows the value ("DIRTY read") to be read before the value is submitted, where CON is current connection: con.settransactioniSolation; transaction isolation level is higher, to avoid conflict The more energy is also. The Connection interface defines five levels, where the lowest level specifies that the transaction is not supported, and the highest level specifies that any other transaction does not change the data that the transaction is being read when the transaction is operated in a database. Typically, the higher the isolation level, the slower the speed of the application execution (due to the increase in resources for lock, and the concurrent operation between the user is reduced). When deciding which isolation level, developers must trade between performance requirements and data consistency requirements. Of course, the actual level can be supported depends on the function of the DBMS involved. When creating a Connection object, its transaction isolation level depends on the driver, but is usually the default value of the database involved. Users can change the transaction isolation level by calling the SetisolationLevel method. The new level will take effect within the remainder of the connection process.

转载请注明原文地址:https://www.9cbs.com/read-124742.html

New Post(0)