Introduction to JDBC

1. JDBC Introduction
Many developers and users are looking for convenient methods to access databases in Java programs. Java is robust, secure, easy to use, easy to understand, and can be automatically downloaded from the network. Therefore, Java has become a good language basis for developing database applications. It provides many advantages of C, C ++, smalltalk, basic, COBOL, and 4gls. Many companies have begun to work on the connection between Java and DBMS.
Many Java application developers want to write programs independent from specific DBMS. We also believe that a DBMS-independent interface will make it easier to connect to a variety of DBMS and make development faster. Therefore, we think it is very meaningful to define a universal SQL database access framework and provide a unified interface on a variety of database connection modules. This allows programmers to face a single database interface and make database-independent Java tools and products possible, so that database connection developers can provide a variety of connection solutions. We can see that we define a general low-level Java database connectivity (JDBC) API urgent task that supports basic SQL functions.
Fortunately, we don't have to design an SQL API from scratch. We can build our work on X/Open SQL CLI (call layer interface) (which is also the basis of Microsoft's ODBC ).
Our main task is to define a natural Java interface to connect to the basic abstraction layer and concept defined in X/Open CLI.
JDBC APIs are very important for database developers, connection developers, ISVs, and application developers. We believe that building our work on the ODBC abstraction layer makes JDBC easier for everyone to accept. In addition, ODBC is a good foundation for our design.
Because ODBC is a C language interface, it is not appropriate to directly Use ODBC in Java. Calling C code from Java is inconvenient in terms of security, robustness, convenience, portability, and so on. It makes Java unable to exert many advantages in these aspects.
We have implemented an ODBC-based API in the short term. In the long run, we can provide implementation in other ways.

Note:
We are very grateful to many early practitioners in the field of database, database connection, and database tools. They provided good comments and suggestions for early JDBC drafts. Their work has played an immeasurable role in this provision.

Ii. Goals and Philosophy
This section describes the goal and philosophy of guiding this API development.
2.1 SQL-level API
Our main goal is to define a "Call-level" SQL interface for Java. This means that our primary focus is on executing the original SQL statements and retrieving the results. We expect that high-level APIs will also be defined, and these may be built on grass-roots interfaces.
These high-level interfaces include directly and transparently injecting the data in the table into the Java class, representing more general queries using the syntax tree, and the SQL syntax embedded in Java.
We hope that a large number of application development tools will use our APIs. However, we also hope that programmers can use our APIs, especially when there is no other means in Java (or database access means.
2.2 follow SQL
The database system supports a variety of SQL syntaxes and semantics. They are inconsistent with each other in terms of advanced functions such as external connections and embedded processes, although we can expect the SQL of these parts to be standardized over time. At the same time, we adopt this attitude and position:
In fact, an application query need not even be SQL, or it may be a specialized derivative of SQL, e.g. For document or image queries, designed for specific dbmss.
In order to pass JDBC compliance tests and to be called "JDBC compliant" we require that a driver support at least ANSI SQL-2 entry level. this gives applications that want wide portability a guaranteed least common denominator. we believe ANSI SQL-2 entry level is reasonably powerful and is reasonably widely supported today.
* JDBC allows the query expression to be passed directly to the underlying data driver, so that a program can obtain as many SQL functions as possible, but may be rejected by the DBMS. In fact, the query of a program can be not even SQL, or a special evolution of SQL, for example, text or graphic query designed for a specialized database.
* To pass JDBC compatibility testing and be known as JDBC compatibility, we require a driver to support at least ANSI SQL-2 standards. This allows programs that require extensive portability to obtain a minimum denominator (this gives applications that want wide portability a guaranteed least common denominator .). We believe that ANSI SQL-2 is strong enough and is well supported.
2.3 JDBC must be available on the existing database interface
We must ensure that jdbc SQL APIs can be built on common SQL APIs, especially ODBC. These requirements have affected some parts of this specification, especially the processing of outgoing parameters and big data blocks.
2.4 ensure that this interface is consistent with other parts of the Java system. Currently, the positive response to Java is very enthusiastic. This is largely because the language standards and standard runtime libraries are considered to be consistent, simple, and powerful. We will do our best to provide this Java database interface, which will be built on the existing style of the Java kernel and will be further enhanced.
2.5 keep simple
We wowould prefer to keep this base API as simple as possible, at least initially. in general we wowould prefer to provide a single mechanic for each Ming a special task, and avoid provid-ing duplicate mechanisms. we will extend the API later if any important functionality is Miss-ing.
We will strive to make basic APIs as simple as possible, at least at the beginning. In general, we want to provide only one solution for each specific task, instead of multiple solutions. If some important functions are missing, we will expand this API later.
2.6 maintain a strong, static type as much as possible
We hope that this jdbc api will maintain a type check as much as possible, so that as much type information as possible can be expressed statically. So that as many errors as possible can be found during compilation.
Because SQL itself is a dynamic type, we may encounter a Type Mismatch problem when running the program. For example, if a programmer wants the SELECT statement to return an integer but returns the string "foo ". however, we still hope that programmers can clearly express their desired types during compilation so that we can perform as many static checks as possible. We also hope to support dynamic interfaces when necessary (see chapter 4)
2.7 simplify Common Tasks
We hope that common tasks can be simple, but not general tasks are feasible.
A common task refers to a programmer executing a simple SQL statement without parameters (for example, select, insert, update, delete), and then (for example, select) processing returned tuples with simple types. An SQL statement with input parameters (in parameter) is also common.
It is not so common but also very important when programmers use SQL statements with inout and out parameters. We also need to support SQL statements that read and write several megabytes of objects. More specifically, one statement returns multiple result sets.
We hope that the metadata (meatdata) is rarely used, but only skilled programmers and development tools need to deal with the problem. Metadata access functions and dynamic data access functions are at the end of this document. General programmers do not have to worry about these chapters.
2.8 different functions are implemented by different methods (functions) (the original "method" is: method, so the translation is similar to that of VB)
An interface design style is a process that uses a very small number of parameters. It provides many control signs for parameter transfer, so that they can be used to influence various behaviors within a large range. To express different functions. This tends to be different from using many methods, but each method agrees to understand it.
Generally, Java kernel classes use different methods ). The main advantage of this step is that programmers who start learning the basic interface do not have to be troubled by parameters related to complex functions. We try to adopt the same policy on the JDBC interface. Generally, different methods are used instead of different labels and multi-purpose methods.

3. Interface Overview
Interfaces are divided into two levels. One is the jdbc api for program developers. The other is the underlying JDBC driver API.
3.1 JDBC API
JDBC APIs are described as abstract Java interfaces of Yi people. Similar applications can connect to a database, execute SQL statements, and process results.

The most important interfaces are:
* Java. SQL. drivermanager processes the incoming data driven and supports new database connections.
* Java. SQL. Connection indicates the connection to a specific database.
* Java. SQL. statement indicates a specific container to execute SQL statements on a specific database.
* Java. SQL. resultset controls the access to row data of a specific statement.
Java. SQL. Statement has two subtypes:
1. java. SQL. preparedstatement is used to execute pre-compiled SQL statements.
2. java. SQL. callablestatement is used to call a database embedded process.
The following sections describe how to run JDBC. For the entire definition, see Chapter 13th. In addition, Chapter 4 describes how to obtain the metadata of a database.
3.2 JDBC driver API
Java. SQL. Driver is fully defined in Chapter 9th. Most JDBC drivers only need to complete the abstract classes defined by these JDBC APIs. In particular, all drivers must implement java. SQL. Connection, java. SQL. state-ment, java. SQL. Prepared-statement, and Java. SQL. resultset. If the target DBMS provides an embedded process with out parameters, the Java. SQL. callablestatement interface must also be provided. Each database driver must provide a class: Java. SQL. Driver, so that the system can be managed by Java. SQL. drivermanager.
An obvious driver is to provide JDBC implementation on ODBC, thus providing a JDBC-ODBC Bridge with ODBC interface, as shown in the figure above. since JDBC is placed after ODBC, the implementation is simple and efficient.
Another useful driver is to directly access database-independent network protocols. Publish a protocol that allows multiple servers to implement, for example, on ODBC or a specific DBMS (although there are already some products that use fixed protocols, but we do not intend to standardize them .), Yes.

Iv. JDBC usage
Before looking at specifics of the jdbc api, an understanding of typical use scenarios is help-ful. There are two common scenarios that must be treated differently for opurur poses: applets and applications.
Before reading the jdbc api, it is helpful to know the typical application scenarios. Generally, the following two cases must be taken: Applet and application.
4.1 Applet
Currently, Java uses the most downloaded applets from the network. They are part of web files. Database Access Applet and database access applet that can use JDBC.
For example, a user may download an applet that displays the stock price history. This applet obtains the historical stock price from the relational database over the Internet.
In the most general case, the use of Applet is through the border of unreliable. For example, you can obtain these applets from another company or the Internet. This is called an Internet scenario. However, the applet may also be downloaded through the LAN. In this case, the security of the client is still a problem.
Typical applet differs from traditional database applications in several aspects:
1) unreliable applets are strictly restricted to the operations they are allowed to perform. In particular, they are not allowed to access local files, and they are not allowed to establish network connections to any database.
2). As for the identification and connection to the online database, the applet in the Internet environment is facing new problems.
3) when the database may be thousands of miles away from you, the efficiency may be different. Compared with Local Area Networks, database applets on the Internet may encounter very different response times.
4.2 Application
Java can also be used to create common applications, so that they can be used on the client just like common applications. We believe that with the increasing number of development tools, people begin to recognize the necessity of improving program production efficiency and other advantages of Java, this usage of Java will become more and more popular. In this way, Java code can be trusted, and can be used to read and write files to open network connections, etc., just like other application code.
Maybe these Java applications are most used in a company or Intranet, so it may be an intranet application. For example, a company wants to use Java and Its GUI Component tools to build its cooperation software based on the cooperative data model. These applications will access data from the LAN or WAN. Java applications can do this.
Java application scenarios and Intranet scenarios are different from applet scenarios. For example, the most natural way to calibrate a database is to use a database name, just like "MERs" and "personnel. Then the user wants the system to locate specific machines, DBMS, JDBC driver, and Java applications.
4.3 Other occasions
There are other interesting occasions:
1) verified applets are those that have been recognized as trustworthy by the Java Virtual Machine. They are considered credible because they have configured a specific key or the user thinks that the applet from a specific source is credible. In terms of security, they are the same as the application, but other aspects (such as locating a database) are similar to the applet.
2). Different from using the Client/Server mode to describe the DBMS server directly from the Java GUI, the layer-3 access method may be used. In this case, the Java application calls the services on the middle layer. The services on the middle layer are online, and the middle layer then calls the database. These calls may be called through Remote Procedure Call or ORB (Object Request Broker ). In both cases, it is best to use an object change in the middle layer. We hope that the layer-3 structure will become more and more common, because for MIS managers, this gives them the opportunity to explicitly define legal operations on public databases. At the same time, the layer-3 structure can provide many efficiency benefits.
Currently, the intermediate layer is generally completed in languages such as C or C ++. The optimization compiler translates JAVA byte code into efficient machine code. The middle layer can also be implemented using Java. Java has many excellent features (robustness, security, and multithreading) to achieve the goal of the intermediate layer.

5. security considerations
Java, a language on the network, must be highly secure. Based on the above discussion, we must consider the security issues in the two main scenarios of JDBC:
* Java code is local in Java applications, so it is also "trusted"
* Unverified Java Applet Code cannot access local and other network data.
5.1 JDBC and unverified Applet
JDBC must first comply with general Java security rules. In addition:
* JDBC must think that the unverified applets is unreliable.
* JDBC cannot allow unreliable applets to access the local database.
* A jdbc driver that has been registered with JDBC drivermanager can only access its data sources.
* An applet can only access data from the server it downloads.
If the JDBC driver layer is completely confident that opening a connection to a database server will not cause authentication or permission issues (possibly caused by programs running on random hosts on the Internet ), then it allows the applet to open such a connection. The database server does not limit access through IP addresses, mainly for example. (Be careful. I may have translated this sentence !!! Let's take a look at the original article .)
These restrictions are cumbersome. However, they are consistent with the restrictions on general applets. We do not need to release these restrictions.
5.2 JDBC and Java applications
For a common Java application (for example, a Java code instead of an unreliable applet), JDBC obtains the driver from the local class path and allows the application to freely access files, remote servers.
However, like the applet, if for some reason a sun. SQL. Driver class that is not verified is obtained from a remote source, the driver can only work with code from the same place.
5.3 driver security responsibilities
JDBC driver may be used in various situations, so the driver compiler follows certain simple security rules to avoid illegal database connections by the applet.
If all the drivers are downloaded from the internet like an applet, these principles will be unnecessary, because common security rules have imposed restrictions on them. However, drivers must remember that once their drivers are successfully installed on a local disk, the drivers will become a trusted part of the Java environment, therefore, you must be sure that it will not be abused by the visiting applet. Therefore, we encourage all drivers to follow certain security principles.
All these principles are used when the connection is opened. This officially drives and virtual machines to check whether the current caller can really connect to the specified database. Once the connection is established, no more checks are required.
5.3.1 exercise caution when sharing TCP/IP connections. If a JDBC driver attempts to open a TCP connection, the connection will be automatically checked by the Java security management mechanism. This organization will check whether there is any Applet in the current call stack. If so, it will limit the machine set that it can access. Therefore, the JDBC driver can leave TCP establishment check to the Java Virtual Machine.
However, if a JDBC driver tries to share a TCP connection between multiple database connections, the driver must check whether each caller is allowed to contact the target database. For example, if we open a TCP connection to machine foobah for applet A, it does not mean that applet B is automatically allowed to share the connection. Applet B may not have any right to access the machine foobah. Therefore, before allowing a program to reuse a ready-made TCP connection, the JDBC driver must use a security mechanism to check whether the current caller can access the connection. The following code implements this function.

Securitymanager SECURITY = system. getsecuritymanager ();
If (Security! = NULL)
{
Security. checkconnect (hostname, portnumber );
}

If the connection is not allowed, the security. checkconnect method will generate a java. Lang. securityexception.
5.3.2 check all local File Access
If a JDBC request needs to access data on the local machine, it must be sure that the caller is allowed to open the file. For example:

Securitymanager SECURITY = system. getsecuritymanager ();
If (Security! = NULL)
{
Security. checkread (filename );
}

If access to a specific file is not allowed, the security. checkread method will generate a java. Lang. securityexception.
5.3.3 prepare for the worst
Some drivers may use local methods to bridge underlying database programs. In these cases, it is difficult to determine that the local files will be accessed by the underlying functions.
In these environments, the user must make the worst plan and reject all database access requests from the download applet, unless the driver may be completely confident that there is no problem with the access.
For example, a JDBC-ODBC bridge must check the name of the ODBC data source to ensure that the applet can only access its "Source ". If the host name of the data source cannot be determined in some names, the access can only be rejected.
To determine whether a caller is a trusted application or applet, The JDBC driver must be able to check whether the caller can write a random file:

Securitymanager SECURITY = system. getsecuritymanager ();
If (Security! = NULL)
{
Security. checkwrite ("foobaz ");
}

MySQL JDBC driver
Mm. MySQL is a MySQL JDBC driver of type 4 (pure Java) that complies with JDBC 2 specifications. The current version is 2.0 pre 5 (Beta)
You canHttp://mmmysql.sourceforge.net/Download the latest driver
This document briefly describes how to install and use mm. MySQL.

Install
1. Download mm. MySQL
2. decompress the package to a drive, such as C:
3. Modify the classpath and add the path of MM. MySQL. For example, your original classpath may be:
.; C:/jdk1.2.2/lib/tools. jar; C:/jdk1.2.2/lib/dt. jar;
After modification, it may be
.; C:/jdk1.2.2/lib/tools. jar; C:/jdk1.2.2/lib/dt. jar; C:/mm. MySQL. jdbc-2.0pre5;

Programming
1. Use drivermanager to register mm. MySQL
The Class Name of MM. MySQL is org. gjt. Mm. MySQL. Driver. It must be written during registration.
Class. forname ("org. gjt. Mm. MySQL. Driver"). newinstance ();

2. jdbc url parameter description

Parameter Name	Value	Default
User	Database username	None
Password	Database User Password	None
Autoreconnect	Whether to automatically connect when the database connection is lost. The value is true/false.	False
Maxreconnects	If autoreconnect is true, this parameter indicates the number of retries. The default value is 3.	3
Initialtimeout	If autoreconnect is true, this parameter is the number of seconds waiting before the reconnection.	2
Maxrows	Sets the number of rows returned during the query. 0 indicates all	0
Useunicode	Whether to use Unicode output, true/false	False
Characterencoding	If useunicode is used, this parameter is set to the encoding type. 8859_1 is recommended.	None

URL format: JDBC: mysql: // [hostname] [: Port]/dbname [? Param1 = value1] [& param2 = value2]...
Prompt
At the same time, useunicode and characterencoding can be used to solve Chinese problems in database output.
For example, JDBC: mysql: // localhost/test? User = root & useunicode = true; characterencoding = 8859_1

A simple example
This JSP Example uses a library, which has only one table (Address Book)

Create Database addressbook
Use addressbook
Create Table addressbook (ID int auto_increment primary key, name varchar (30), address varchar (255), phone varchar (20 ));

Insert record
Insert into addressbook (name, address, phone) values ('jx', 'zhejiang Yuyao ', '2017-0574 ');

JSP code:
The absolute and resultset. type_scrool_sensitive directories are used in the code to test whether mm. MySQL complies with JDBC 2.0 specifications.

<% @ Page import = "Java. SQL. *" %>
<%
Out. println ("Address Book! ");

Try {
Class. forname ("org. gjt. Mm. MySQL. Driver"). newinstance ();
} Catch (exception e ){
Out. println ("unable to load driver .");
}

Try {
Connection c = drivermanager. getconnection ("JDBC: mysql: // localhost/addressbook? User = root & Password = jjx & useunicode = true & characterencoding = 8859_1 ");
Statement S = C. createstatement (resultset. type_scroll_sensitive, resultset. concur_read_only );
Resultset rs=s.exe cutequery ("select * From addressbook ");
Out. println ("<Table border = 1> ");
Int I = 1;
For (I = 10; I <20; I ++)
{
If (Rs. Absolute (I ))
{
Out. println ("<tr> <TD> ");
Out. println (Rs. getstring (1 ));
Out. println ("</TD> ");
Out. println ("<TD> ");
Out. Print (Rs. getstring (2 ));
Out. println ("</TD> ");
Out. println ("<TD> ");
Out. Print (Rs. getstring (3 ));
Out. println ("</TD> </tr> ");
} Else {
Break;
}
}
Out. println ("</table> ");
Rs. Close ();
S. Close ();
C. Close ();
} Catch (sqlexception e ){
Out. println ("sqlexception:" + E. getmessage ());
Out. println ("sqlstate:" + E. getsqlstate ());
Out. println ("vendorerror:" + E. geterrorcode ());
}
%>