JDBCTM Guide: Getting Started _jsp programming

This profile is quoted in the book Jdbctm Database Access from Javatm:a Tutorial and annotated Reference.
JavaSoft is currently preparing the book. This is a tutorial, as well as an important reference manual for JDBC, which will be published as part of the Java family in the spring of 1997 by Addison-wesley Publishing Company.


1.1 What is JDBCTM?
JDBCTM is a JAVATM API for executing SQL statements (interestingly, JDBC itself is a trademark name rather than an abbreviation; however, JDBC is often considered to represent "Java database connection (Java DB Connectivity)"). It consists of a set of classes and interfaces written in the Java programming language. JDBC provides tools/database developers with a standard API that enables them to write database applications with a pure Java API.

With JDBC, it's easy to send SQL statements to various relational databases. In other words, with the JDBC API, there is no need to write a program specifically to access the Sybase database, write a program for accessing an Oracle database, write another program for accessing the Informix database, and so on. Suffice it to write a program with the JDBC API to send SQL statements to the appropriate database. Also, applications written in the Java programming language do not need to worry about writing different applications for different platforms. Combining Java with JDBC will allow programmers to write the program once and let it run on any platform.

Java is robust, secure, easy-to-use, easy to understand, and automatically downloadable from the Web, and is an excellent language for writing database applications. All you need is a way for the Java application to talk to a variety of different databases. And JDBC is the mechanism for this purpose.

JDBC extends the functionality of Java. For example, a Web page containing applets can be published with the Java and JDBC APIs, and the information used by the applet may be from a remote database. Enterprises can also use JDBC to connect all employees to one or more internal databases through an Intranet (even if the computers used by these employees
There are various operating systems, such as Windows, Macintosh, and UNIX. As more and more programmers begin to use the Java programming language, the need for easy access to the database from Java is increasing.

MIS administrators like the combination of Java and JDBC because it makes it easy and economical to spread information. Organizations can continue to use their installed databases and access information easily, even if they are stored on different database management systems. The new program has a very short development period. Installation and versioning will be greatly simplified. Programmers can write only one application or update it once, and then put it on the server, and then anyone can get the latest version of the application. For business sales information Services, Java and JDBC can provide external customers with a better way to get information updated.


What is the purpose of 1.1.1 JDBC?
Simply put, JDBC can do three things:



Establish a connection with the database,
Send SQL statement,
Process the results.


The following code snippet gives a 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 (Rs.next ()) {
int x = Rs.getint ("a");
String s = rs.getstring ("B");
float f = rs.getfloat ("C");
}


1.1.2 JDBC is a low-level API that is the foundation of advanced APIs
JDBC is a "low-level" interface, which means it is used to invoke SQL commands directly. It is very functional in this regard and is easier to use than other database connection APIs, but it is also designed as an infrastructure interface on which to build advanced interfaces and tools. The advanced interface is the "user-friendly" interface, which uses an API that is easier to understand and easier to convert behind the scenes into low-level interfaces such as JDBC. At the time of writing this article, two advanced JDBC based APIs are being developed:


An embedded SQL used in Java. At least one provider is planning to write it. DBMS implementation SQL: A language specifically designed to be used in conjunction with a database. JDBC requires that the SQL statement be passed to the Java method as a String. Instead, 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. The embedded SQL preprocessor then converts this java/sql mixture to Java through a JDBC call.
A direct mapping of relational database tables to Java classes. JavaSoft and other providers are claiming to implement the API. In this "object/relationship" mapping, each row in the table corresponds to an instance of the class, and the value of each column corresponds to one of the properties of the instance. As a result, programmers can manipulate Java objects directly, and access data
The required SQL calls are automatically generated under cover. You can also provide more complex mappings, such as combining rows from multiple tables into a Java class.
As people become increasingly interested in JDBC, more and more developers have been using JDBC based tools to make programs easier to write. Programmers have also been writing applications that attempt to make access to the database easier for end users. For example, an application can provide a menu that selects database Tasks.
When a task is selected, the application gives hints and blanks to fill in the information needed to perform the selected task. When the required information is entered, the application will automatically invoke the required SQL command. With the help of such a program, a database task can be performed even if the user does not know the syntax of SQL at all.


Comparison of 1.1.3 JDBC with ODBC and other APIs
Currently, the Microsoft ODBC (Open Database Connectivity) API is probably the most widely used programming interface for accessing relational databases. It can connect almost all the databases on almost any platform. Why does Java not use ODBC?

The answer to this question is: Java can use ODBC, but it is best to use it in the form of Jdbc-odbc Bridge with the help of JDBC, which we'll talk about later. Now the question has become: "Why do we need JDBC?" The answer is as follows:


ODBC is not suitable for direct use in Java because it uses the C language interface. Calling native C code from Java has many drawbacks in terms of security, implementation, robustness, and automatic portability of programs.
The literal translation from the ODBC C API to the Java API is undesirable. For example, Java has no pointers, and ODBC uses a wide range of pointers (including error-prone pointer "void *"). You can think of JDBC as an ODBC that is converted to an object-oriented interface, whereas an object-oriented interface is more
Easy to receive.
ODBC is difficult to learn. It mixes simple and advanced features, and the options are extremely complex even for simple queries. In contrast, JDBC tries to ensure simplicity of simple functionality while allowing for advanced functionality when necessary.
Enabling the "pure Java" mechanism requires a Java API such as JDBC. If you use ODBC, you must manually install the ODBC Driver Manager and drivers on each client computer. If you write the JDBC driver completely in Java, the JDBC code is on all Java platforms (from the network computer to the mainframe)
Can be automatically installed, ported, and secured.
In summary, the JDBC API is a natural Java interface for basic SQL abstractions and concepts. It is built on ODBC rather than from scratch. As a result, programmers who are familiar with ODBC will find JDBC easy to use. JDBC retains the basic design features 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 based on Java style and advantages and is optimized to make it easier to use.

Recently, Microsoft has introduced new Api:rdo, ADO, and OLE DB outside of ODBC. These designs are in many ways the same as JDBC, that is, they are object-oriented database interfaces and are based on classes that can be implemented on ODBC. But in these interfaces, we don't see any special features that make us choose to switch to it.
To replace ODBC, especially if the ODBC driver has been established in a more sophisticated market. At best, they add a kind of decoration to ODBC. This is not to say that JDBC does not need to evolve from its original version; However, we feel that most of the new features should be grouped into advanced APIs such as the object/relational mappings and embedded SQL described in the previous section.


1.1.42-layer model and three-layer model
The JDBC API supports both the two-tier model of database access as well as the three-tier model.

In a two-tier model, Java applets or applications will be directly talking to the database. This will require a JDBC driver to communicate with the specific database management system that you are accessing. The user's SQL statement is sent to the database, and the results are sent back to the user. The database can be located on another computer and the user is connected to it over the network. This is called a client/server configuration where the user's computer is a client and the computer that provides the database is the server. A network can be an Intranet (it can connect company employees), or it can be the Internet.





In the three-tier model, the command is sent first to the "middle tier" of the service, which then sends the SQL statement to the database. The database processes the SQL statements and sends the results back to the middle tier, which then sends the results back to the user. MIS executives found that the three-tier model was attractive because the middle tier was available to control access to corporate data and the types of updates that could be made. Another advantage of the middle tier is that the user can take advantage of an easy-to-use advanced API, and the middle tier will convert it to the corresponding low-level call. Finally, in many cases, the three-tier architecture provides some performance benefits.




So far, the middle tier is usually written in languages such as C or C + +, and these languages perform faster. However, with the introduction of the optimizer compiler (which translates Java byte code into efficient machine-specific code), it becomes more practical to implement the middle tier in Java. This will be a big step forward, enabling people to take full advantage of Java's many advantages (such as robustness, multithreading, and security). JDBC is important for accessing databases from the Java middle tier.


1.1.5 SQL Consistency
Structured Query Language (SQL) is the standard language for accessing relational databases. The difficulty is that while most DBMS (database management Systems) use standard forms of SQL for their basic functionality, they do not conform to the standard SQL syntax or semantics recently defined for more advanced functionality. For example, not all databases support stored programs or external connections, and the databases that support this feature are inconsistent with each other. People want the real standard part of SQL to scale to include more and more features. However, the JDBC API must also support existing SQL.

One way the JDBC API solves this problem is to allow any query string to be uploaded to the DBMS driver involved. This means that an application can use as many SQL functions as possible, but it must take the risk that there may be an error on some of the DBMS. In fact, an application query is not even necessarily SQL, or it can be a dedicated creature of SQL designed for a particular DBMS (for example, a document or an image query).

The second way JDBC handles SQL consistency issues is to provide ODBC-style conversion clauses. This is discussed in the 4.1.5 section, "SQL escape syntax in the statement object."

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

For complex applications, JDBC uses a third method to handle SQL consistency issues. It leverages the DatabaseMetaData interface to provide descriptive information about the DBMS, enabling applications to adapt to the requirements and functionality of each DBMS.

Because the JDBC API will be used as the base API for developing advanced database access tools and APIs, it must also be aware of the consistency of all its superstructure. "Compliant JDBC Standard TM" represents the standard level of JDBC functionality that users can rely on. To use this description, the driver must at least support ANSI SQL-2 Entry level (ANSI SQL-2 represents the standard adopted by the U.S. National Standards Agency for 1992. The Entry level represents a specific list of SQL features. Driver developers can use the test kits that are available with the JDBC API to determine whether their drivers meet these standards.

"Compliant JDBC Standard TM" indicates that the provider's JDBC implementation has passed the conformance test provided by JavaSoft. These conformance tests examine whether all classes and methods defined in the JDBC API exist and, if possible, check that the program has SQL Entry level functionality. Of course, these tests are not complete, and JavaSoft currently has no intention of scaling the implementations of each provider. But this consistency definition does provide some credibility to the JDBC implementation. As more and more database providers, connection providers, Internet providers, and application programmers accept the JDBC API, JDBC is rapidly becoming a standard for Java database access.


1.2 JDBC Products
When writing this article, several JDBC based products have been developed or are in the process of being developed. Of course, the information in this section will soon become obsolete information. Therefore, for the most recent information, please refer to the JDBC Web site by starting at the following URL to find:

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


1.2.1 JavaSoft Framework
JavaSoft provides three JDBC product components that are part of the Java Development Kit (JDK):


JDBC Driver Manager,


JDBC Driver Test Kit, and


Jdbc-odbc Bridge.


The JDBC Driver Manager is the backbone of the JDBC architecture. It's actually small and simple; Its primary role is to connect the Java application to the correct JDBC driver and then exit.

The JDBC Driver Test Kit provides a certain level of credibility for the JDBC driver to run your program. Only drivers that test packages through the JDBC driver are considered to be compliant 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, and its long-term goal provides a way to access some of the less common DBMS (if JDBC is not implemented for these uncommon DBMS).





Type of 1.2.2 JDBC driver
The JDBC drivers we currently know can be categorized into the following four categories:


JDBC-ODBC Bridge plus ODBC driver: JavaSoft Bridge product provides JDBC access using ODBC drivers. Note that ODBC binaries (in many cases also including database client code) must be loaded on each client computer that uses the driver. Therefore, this type of driver is best suited to the Enterprise network (the installation of the client on this network is not a major problem) or the application server code written in Java with a three-tier structure. Local API-Partially written drivers in Java: This type of driver converts JDBC calls on the client API to calls from Oracle, Sybase, Informix, DB2, or other DBMS. Note that, like the bridge driver, this type of driver requires that some binaries be loaded onto each client.
JDBC Network Pure Java driver: This driver converts JDBC to a DBMS-independent network protocol, which is then converted by a server into a DBMS protocol. This kind of network server middleware can connect its pure Java client to many different databases. The specific protocol used depends on the provider. Typically, this is the most flexible JDBC driver. It is possible that all providers of this solution offer products that are appropriate for Intranet use. In order for these products to also support Internet access, they must handle additional requirements for the security provided by the WEB, access through firewalls, and so on. Several providers are adding JDBC drivers to their existing database middleware offerings.
Native protocol Pure Java driver: This type of driver converts a JDBC call directly to the network protocol used by the DBMS. This will allow direct invocation of the DBMS server from the client machine, which is a useful solution for Intranet access. Since many of these protocols are private, the database provider will be the main source themselves, and several providers are already working on it.
Finally, we anticipate that the class 3rd and 4 drivers will be the preferred method of accessing the database from JDBC. The 1th, 2-class driver will be used as a transition scheme before the direct pure Java driver is listed. There may be variants for class 1th and 2 drivers (not listed in the table below), which require connectors, but these are generally more undesirable solutions. The 3rd, 4-Class drivers provide all the benefits of Java, including automatic installation (for example, by using the JDBC applet to download the driver).

The following table shows the 4 types of drivers and their properties:


Driver kind of pure JAVA? Network protocol
1-JDBC-OCBC Bridge is not directly
2-non-direct based on local API
3-JDBC network is to require connectors
4-based on the local protocol is directly



1.2.3 JDBC Driver Access
At the time this article was written, there were dozens of driver drivers for the 1:ODBC driver that could be used in conjunction with the JavaSoft Bridge. About 10 drivers of type 2 are written based on the DBMS's local API. There are only a few drivers that belong to type 3. There are at least 2 drivers of type 4, but by the end of 1997 we expect that the main DBMS will have a type 4 driver.

To get the latest information about the driver, check the JDBC Web site at: http://java.sun.com/products/jdbc. The first providers to provide a 3rd driver are SCO, Open Horizon, Visigenic, and WebLogic. JavaSoft and the leading provider of database Connectivity Intersolv collaborated on the development of the Jdbc-odbc Bridge and the JDBC Driver Test Kit.


1.2.4 Other Products
Development tools for various JDBC applications are under development. Please check the JavaSoft Web page for updated information.