Jdbctm guide: getting started

This document is excerpted from jdbctm database access from javatm: a tutorial and annotated reference.
Javasoft is currently preparing this book. This is a tutorial and an important reference manual for JDBC, which will be published by Addison-Wesley in the spring of 1997 as part of the Java series.

1.1 What is jdbctm?
Jdbctm is a javatm API used to execute SQL statements (interestingly, JDBC itself is a trademark name rather than an abbreviation; however, JDBC is often considered to be "Java database connectivity )"). It consists of a group of JavaProgramming LanguageThe composition of the written classes and interfaces. JDBC provides a standard API for tools/database developers to write database applications using pure Java APIs.Program.

With JDBC, it is easy to send SQL statements to various relational databases. In other words, with the jdbc api, you do not have to write a program for accessing the Sybase Database, write another program for accessing the Oracle database, and write another program for accessing the Informix database. You only need to use the jdbc api to write a program. It can send SQL statements to the corresponding database. Moreover, applications written in the Java programming language do not have to worry about writing different applications for different platforms. The combination of Java and JDBC enables programmers to run programs on any platform only once.

Java is a powerful, secure, easy to use, easy to understand, and can be automatically downloaded from the network. It is an outstanding language for writing database applications. All you need is the method of dialog between Java applications and different databases. JDBC serves as a mechanism for this purpose.

JDBC extends Java functions. For example, you can use Java and JDBC APIs to publish a webpage containing an applet, and the information used by the applet may come from a remote database. Enterprises can also use JDBC to connect all employees to one or more internal databases over the Intranet (even if the computers used by these employees
Windows, Macintosh, UNIX, and other operating systems ). As more and more programmers begin to use the Java programming language, the requirements for convenient access to the database from Java are also increasing.

MIS administrators like the combination of Java and JDBC because it makes information dissemination easy and economical. Enterprises can continue to use their installed databases and easily access information, even if the information is stored on different database management systems. The development period of new programs is very short. Installation and version control will be greatly simplified. A programmer can write an application only once or update it only once, and then put it on the server. then anyone can get the latest version of the application. For commercial sales information services, Java and JDBC can provide external customers with a better way to obtain information updates.

1.1.1 what is the purpose of JDBC?
To put it simply, JDBC can do three things:

Establish a connection with the database,
Send SQL statements,
Processing result.

BelowCodeSection provides the basic examples of the above three steps:

Connection con = drivermanager. getconnection (
"JDBC: ODBC: wombat", "login", "password ");
Statement stmt = con. createstatement ();
Resultset rs = stmt.exe cutequery ("select a, B, c from Table1 ");
While (Rs. Next ()){
Int x = Rs. getint ("");
String S = Rs. getstring ("B ");
Float F = Rs. getfloat ("C ");
}

1.1.2 JDBC is a low-level API and is the foundation of advanced APIs
JDBC is a "low-level" interface, that is, it is used to directly call SQL commands. In this regard, it provides excellent functionality and is easier to use than other database connection APIs, but it is also designed as a basic interface on which advanced interfaces and tools can be created. An advanced interface is a user-friendly interface. It uses an API that is easier to understand and more convenient, and is converted to a lower-level interface such as JDBC. At the time of writing this article, two JDBC-based advanced APIs are being developed:

An embedded SQL statement for Java. At least one provider has planned to write it. DBMS implements SQL: A language specially designed for joint use with databases. JDBC requires that the SQL statement be passed to the Java method as a string. On the contrary, the embedded SQL Preprocessor allows programmers to mix SQL statements directly with Java. For example, you can use Java variables in SQL statements to accept or provide SQL values. Then, the embedded SQL Preprocessor converts the Java/SQL mixture to Java by calling JDBC.
Direct ing between relational database tables and Java classes. Javasoft and other providers both claim to implement this API. In this "Object/relationship" ing, 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. As a result, programmers can directly operate on Java objects and access data centers.
The required SQL call will be automatically generated under "Mask. In addition, more complex ing can be provided, such as combining rows in multiple tables into a Java class.
With increasing interest in JDBC, more and more developers are using JDBC-based tools to make programming easier. Programmers have been writing applications that make it easier for end users to access databases. For example, an application provides a menu for selecting database tasks.
After a task is selected, the application provides a prompt and a blank space to fill in the information required to execute the selected task. After the required information is entered, the application automatically calls the required SQL command. With the help of such a program, you can execute database tasks even if you do not understand the SQL syntax at all.

1.1.3 comparison between JDBC and ODBC and other APIs
At present, Microsoft's ODBC (Open Database Connection) API may be the most widely used programming interface for accessing relational databases. It can connect almost all databases on almost all platforms. Why does Java not Use ODBC?

The answer to this question is: Java Can Use ODBC, but it is better to use it in the form of a JDBC-ODBC bridge with the help of JDBC, which we will talk about later. Now the question has changed to: "Why does JDBC be required "? The answer is as follows:

ODBC is not suitable for use directly in Java because it uses the C language interface. Calling local C code from Java has many disadvantages in terms of security, implementation, robustness, and automatic program portability.
Literal Translation from odbc c api to Java API is not desirable. For example, Java does not have pointers, but ODBC is widely used for pointers (including the error-prone Pointer "Void *"). You can think of JDBC as ODBC which is converted into an object-oriented interface, while the object-oriented interface is more suitable for Java programmers.
Easy to receive.
ODBC is hard to learn. It combines simple and advanced functions, and its options are extremely complex even for simple queries. On the contrary, JDBC tries its best to ensure the simplicity of simple functions while allowing advanced functions when necessary.
To enable the "pure Java" mechanism, Java APIs such as JDBC are required. If you use ODBC, you must manually install the ODBC driver manager and driver on each client. If the JDBC driver is fully written in Java, the JDBC code is on all Java platforms (from network computers to mainframes)
Can be automatically installed, transplanted, and secure.
In short, jdbc api is a natural Java interface for basic SQL abstraction and concepts. It is built on ODBC instead of starting from scratch. Therefore, programmers familiar with ODBC will find that JDBC is easy to use. JDBC retains the basic design features of ODBC. In fact, both interfaces are based on X/Open SQL CLI.
(Call-level interface ). The biggest difference between them is that JDBC is optimized based on the Java style and advantages, making it easier to use.

Microsoft has recently introduced new APIs other than ODBC: rdo, ADO, and ole db. These designs are the same as JDBC in many aspects, that is, they are all object-oriented database interfaces and are based on classes that can be implemented on ODBC. However, in these interfaces, we have not seen any special feature that allows us to switch to it.
To replace ODBC, especially when the ODBC driver has established a well-developed market. They add a decoration to ODBC at most. This does not mean that JDBC does not need to be further developed from its original version. However, we think most of the new features should be classified into advanced APIs such as object/link ing and embedded SQL described in the previous section.

1.1.4 two-layer and three-layer models
JDBC APIs support both two-layer database access models and three-layer models.

In a two-tier model, Java applet or application directly communicates with the database. This requires a JDBC driver to communicate with the accessed database management system. The user's SQL statement is sent to the database, and the result is sent back to the user. The database can be located on another computer. You can connect to the database over the network. This is called the client/server configuration. The user's computer is the client, and the database computer is the server. The network can be an Intranet (which can connect employees of a company) or an Internet.

In a layer-3 model, commands are first sent to the "middle layer" of the service, and then sent to the database using SQL statements. The database processes SQL statements and sends the results back to the intermediate layer. The intermediate layer then sends the results back to the user. Managers of MIS have found that the layer-3 model is very attractive, because the middle layer can be used to control access to company data and the types of updates that can be made. Another advantage of the middle layer is that users can use easy-to-use advanced APIs, and the middle layer converts them into corresponding low-level calls. Finally, in many cases, the layer-3 structure provides some performance benefits.

So far, the middle layer is usually written in C or C ++ languages with fast execution speed. However, with the introduction of the optimal Compiler (which converts JAVA byte code into efficient machine-specific code), the use of Java to implement the middle layer will become more and more practical. This will be a huge improvement, which enables people to take full advantage of the many advantages of Java (such as strong, multi-threaded and secure features ). JDBC is very important for accessing the database from the middle layer of Java.

1.1.5 SQL consistency
Structured Query Language (SQL) is the standard language for accessing relational databases. The difficulty is that, although most DBMS (Database Management System) use standard SQL statements for their basic functions, however, they do not comply with the standard SQL syntax or semantics recently defined for more advanced features. For example, not all databases support storage programs or external connections, and those databases that support this function are inconsistent. People hope that the truly standard part of SQL can be expanded to include more and more functions. However, JDBC APIs must support existing SQL statements.

One way to solve this problem is to allow any query string to be passed to the DBMS driver involved. This means that the application can use any number of SQL functions, but it must take the risk that errors may occur on some DBMS. In fact, an application query is not necessarily an SQL statement, or it can be a specialized creature (for example, a document or image query) designed for a specific DBMS ).

The second method for JDBC to handle SQL consistency is to provide an ODBC-style escape clause. This will be discussed in Section 4.1.5 "SQL escape syntax in the statement object.

The escape Syntax provides a standard JDBC syntax for several common SQL differences. For example, there is an escape syntax for calls to date text and stored procedures.

For complex applications, JDBC uses the third method to handle SQL consistency issues. It uses the databasemetadata interface to provide descriptive information about DBMS, so that the application can adapt to the requirements and functions of each DBMS.

Since the jdbc api will be used as the basic API for developing advanced database access tools and APIs, it must pay attention to the consistency of all its superstructure. "JDBC-compliant TM" indicates the standard level of JDBC functions that users can depend on. To use this instruction, the driver must at least support the ANSI SQL-2 entry level (ANSI SQL-2 represents the standards adopted by the US National Institute of Standards in 1992. Entry level indicates a specific list of SQL functions ). Drivers developers can use the test kits included in the jdbc api to determine whether their drivers comply with these standards.

"JDBC-compliant TM" indicates that the provider's JDBC implementation has passed the consistency test provided by mongooft. These consistency tests will check whether all classes and methods defined in the jdbc api exist, and try to check whether the program has the SQL entry level function. Of course, these tests are not complete, and javasoft has no intention of benchmarking the implementation of each provider. However, such consistency definitions can indeed provide a certain degree of credibility for JDBC implementation. As more and more database providers, connection providers, Internet providers, and application programmers accept JDBC APIs, JDBC is rapidly becoming the standard for Java database access.

1.2 JDBC Product
At the time of writing this article, several JDBC-based products have been developed or are under development. Of course, the information in this section will soon become obsolete. Therefore, for the latest information, refer to the JDBC website, which can be viewed from the following URL:

Http://java.sun.com/products/jdbc

1.2.1 structured oft framework
Javasoft provides three JDBC product components, which are components of the Java Development Kit (JDK:

JDBC driver manager,

JDBC driver test toolkit, and

JDBC-ODBC bridge.

The JDBC driver manager is the pillar of the JDBC architecture. It is actually very small and very simple; its main function is to connect the Java application to the correct JDBC driver, and then exit.

The JDBC driver test toolkit provides a certain degree of reliability for making the JDBC driver program run. Only drivers that pass the JDBC driver test package are considered to comply with the JDBC standard TM.

The JDBC-ODBC bridge enables ODBC drivers to be used as JDBC drivers. Its implementation provides a way for the rapid development of JDBC. its long-term goal is to provide a method to access some uncommon DBMS (if JDBC is not implemented for these uncommon DBMS.

1.2.2 JDBC driver type
The JDBC drivers we currently know can be divided into the following four types:

JDBC-ODBC bridge and ODBC driver: The javasoft bridge product uses ODBC drivers to provide JDBC access. Note that ODBC binary code (including database client code in many cases) must be loaded to each client using the driver. Therefore, this type of driver is most suitable for Enterprise Network (the installation of clients on this network is not a major problem), or the three-tier application server code 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 that some binary code be loaded to each client.
JDBC network pure Java driver: This type of driver converts JDBC to a network protocol unrelated to DBMS, and then this protocol is converted into a DBMS Protocol by a server. This network server middleware can connect its pure Java client to a variety of different databases. The specific protocol used depends on the provider. Generally, this is the most flexible JDBC driver. It is possible that providers of all such solutions provide products suitable for intranet. In order for these products to support Internet access, they must handle additional requirements for Web security and access through the firewall. Several providers are adding JDBC drivers to their existing database middleware Products.
Local protocol pure Java driver: This type of driver converts JDBC calls directly to the network protocol used by DBMS. This will allow direct calls to the DBMS server from the client machine, which is a very practical solution for Intranet access. Since many of these protocols are dedicated, the database provider itself will be the main source, and several providers are already working on this.
finally, we expect 3rd and 4 drivers to become the preferred method for accessing the database from JDBC. 1st and 2 drivers will be used as a transitional solution before the direct pure Java driver is available. There may be some variants of the 1st and 2 Drivers (not listed in the table below) that require connectors, but these are generally more undesirable solutions. 3rd and 4 drivers provide all the advantages of Java, including automatic installation (for example, downloading the driver by using the JDBC driver applet ).

The following table shows the four types of drivers and their attributes:

Driver type pure Java? Network Protocol
1-JDBC-OCBC bridge non-direct
2-local API-based non-direct
3-jdbc networks require connectors
4-The local protocol is based on direct

1.2.3 JDBC driver acquisition
At the time of writing this article, there are dozens of drivers of different types, and the 1: ODBC driver can be used together with the javasoft bridge. There are more than 10 Drivers of Type 2 written based on the Local APIs of DBMS. Only a few drivers belong to category 3. At present, there are at least two drivers of category 4, but by the end of 1997, we expect the main DBMS to have category 4 drivers.

For the latest driver information, see the JDBC website at http: // java.sun.com/products/jdbc. The first providers that provide the 3rd drivers are SCO, Open Horizon, Visigenic, and weblogic. Intersolv, the leading provider of javasoft and database connectivity, has developed a JDBC-ODBC bridge and JDBC driver testing toolkit.

1.2.4 Other Products
Development tools for various JDBC applications are under development. Please check the external oft webpage for updated information.