Java Database Programming (JDBC) Technology

Java Database Programming (JDBC) Technology

Section 1 JDBC Overview

Why JDBC, jdbc basic program structure, JDBC disadvantages, JDBC working principle, JDBC structure, database application model, and database access through JDBC

Section 2 create a connection to the data source

JDBC drivers, General connection methods, access connection applications, SQL Server 2000 connection applications, MySQL connection applications, Oracle connection applications, DB2 connection applications, different types of databases driver Configuration

Section 3 perform database operations using JDBC

Query some structure information of the database, query data in the database, use macro statements, use stored procedures, and process large binary Fields

Section 4 Transaction Processing

Why do we need to process transactions, no transactions, and instances for transaction processing?

Section 5 cursor operations

Resultset object, instance analysis of cursor operation, database update, insert operation, delete operation, batch operation

 

Section 1 JDBC Overview

JDBC (Java database connectivity, Java Database Connection Technology) is the standard for Java to access database resources. The JDBC standard defines a set of Java APIs, allowing us to write SQL statements and hand them over to the database.

With JDBC, Java programmers can use Java to write complete database applications. In addition, data stored in different database management systems can be operated, regardless of the data storage format in the database management system. At the same time, the Java language is irrelevant to the platform, so you do not have to write different database applications on different system platforms.

I. Why does JDBC

JDBC is one of the oldest Enterprise Java specifications. The earliest drafting date dates back to 1996. JDBC has the same functionality as the Open Database Connectivity (ODBC) standard developed by Microsoft, it provides a set of common APIs to access the database through a specific database driver.

Without JDBC or ODBC, developers must use a different set of APIs to access different databases. JDBC or ODBC only requires a group of APIs, in addition, the database driver provided by the database vendor can be used. Therefore, with JDBC, We can port the same enterprise-level Java application to another database application.

Objective of JDBC Design

1) ODBC: Microsoft's ODBC is written in C and only applicable to Windows platforms. It cannot operate databases across platforms.

2) SQL language: Although SQL includes data definition, data operations, data management, and other functions, it is not a complete programming language and does not support flow control, it must be used in combination with other programming languages.

3) JDBC design: the Java language is robust, secure, easy to use, and automatically downloaded to the network. Therefore, if you use the Java language to connect to the database, it will be able to overcome the limitations of ODBC to a system platform. By combining the SQL language with the Java language, it can connect different database systems, that is, JDBC can easily transmit SQL statements to any relational database.

4) objective of JDBC design: it is a specification. The main purpose of the design is to allow database developers to provide Java programmers with Standard Database handler classes and interfaces, this makes it possible to develop a Java application independent of DBMS (the database changes, the driver changes, but the application remains unchanged ).

Main functions of JDBC:

1) create a connection to the database;

2) Send SQL statements to any relational database;

3) process the data and query the results.

Ii. Basic JDBC program structure

Try {
// (1) load the driver connecting to the database
Class. forname ("Sun. JDBC. ODBC. jdbcodbcdriver ");
// (2) create a connection to the database
Connection con = drivermanager. getconnection ("JDBC: ODBC: databasedsn", "login", "password ");
// (3) Send SQL statements to the database
Statement stmt = con. createstatement ();
Resultset rs1_stmt.exe cutequery ("select * From dbtablename ");
// (4) process the data and query the results.
While (Rs. Next ()){
String name = Rs. getstring ("name ");
Int age = Rs. getint ("Age ");
Float wage = Rs. getfloat ("wage ");
}
// (5) Close
Rs. Close ();
Stmt. Close ();
Con. Close ();
} Catch (sqlexception e ){
System. Out. println ("sqlstate:" + E. getsqlstate ());
System. Out. println ("message:" + E. getmessage ());
System. Out. println ("vendor:" + E. geterrorcode ());
}

Iii. disadvantages of JDBC

1) JDBC does not meet the object-oriented requirements. JDBC requires you to explicitly process data fields and map them to the tables of relational databases. Developers are forced to deal with two data models, languages, and data access methods that are very different: Java, and relational data models in SQL.

2) during development, the ing from the relational data model to the Java object model is so complex that most developers never define an object model for the data, instead, you can write procedural Java code to manipulate data tables in the underlying relational database.

3) the final result is that developers cannot get any benefits from object-oriented development.

Iv. Working Principles of JDBC

 
Working principle structure of JDBC

Java applications

JDBC API

JDBC driver Manager

JDBC driver

Database Management System

JDBC is designed based on the X/Open SQL CLI (call-level interface) model. It defines a set of API objects and methods for interaction with the database.

To operate a database in a Java program, follow these steps ):

1) load the database connection Driver Class. forname ("Sun. JDBC. ODBC. jdbcodbcdriver ");

2) create a connection to the data source

String url = "JDBC: ODBC: databasedsn ";

Connection con = drivermanager. getconnection (URL, "login", "password ");

3) query database: Create a statement object and execute an SQL statement to return a resultset object.

Statement stmt = con. createstatement ();

Resultset rs1_stmt.exe cutequery ("select * From dbtablename ");

4) obtain the values of each field of a record in the current record set.

String name = Rs. getstring ("name ");

Int age = Rs. getint ("Age ");

Float wage = Rs. getfloat ("wage ");

5) Close the query statement and connection to the database (note that the order of closure is Rs, stmt, and con)

Rs. Close ();

Stmt. Close ();

Con. Close ();

V. JDBC Structure

JDBC consists of JDBC APIs for Java programmers and JDBC drive APIs for database vendors.

1) jdbc api for Java programmers:

By calling this API, Java programmers can connect to a database, execute SQL statements, return result sets, and other programming databases. It is mainly composed of a series of interface definitions.

Java. SQL. drivemanager: this interface is mainly used to process the loaded driver and provide support for creating new database connections.

Java. SQL. Connection: this interface mainly defines the function of connecting to a specified database.

Java. SQL. Statement: this interface mainly defines the container for executing declarations as SQL statements in a given connection to perform database operations. It mainly contains the following two seed types.

Java. SQL. preparedstatement: this interface mainly defines the SQL statements used to execute pre-compiled statements with or without the in parameter.

Java. SQL. callablestatement: this interface mainly defines the carving of stored procedures used to execute databases.

Java. SQL. resultset: this interface mainly defines the result set returned by the database operation.

2) JDBC drive API for database vendors:

(In addition to providing API interfaces, some database vendors also provide DBMS-side buffering)

The database vendor must provide the corresponding driver and implement the basic interfaces required by the jdbc api (each database system vendor must provide specific implementations for interfaces such as drivemanager, connection, statement, and resultset ), in this way, Java programmers can operate on different databases through JDBC.

Vi. Database Application Model

1) two-layer structure (C/S ):

In this model, the client program is directly connected to the database server and sends SQL statements (however, the JDBC driver of the accessed database needs to be installed on the client ), the DBMS server returns the corresponding results to the customer, and the customer program is responsible for formatting the data.

Client ODBC/jdbc server (DBMS)

Major Disadvantages: due to the restrictions of the database vendor, the user needs to rewrite the customer program when changing the database. Due to the limitations of the database version, once the database vendor upgrades the database, the customer program that uses the database needs to be re-compiled and released. The operations and processing on the database are implemented in the customer program, making the customer program more complex in programming and design.

2) Three (or more) layer structure (B/S ):

In this model, an intermediate server (which can be programmed using C ++ or Java) is added between the client program and the database server to isolate the client program from the database server. The client program (which can be simply a general browser) communicates with the intermediate server, and then the intermediate server processes client program requests and manages the connection with the database server.

7. database access through JDBC

1) Reference necessary packages

Import java. SQL. *; // It contains classes and interfaces used to operate databases.

2) load the driver class connecting to the database

To connect to a specific database, JDBC must load the corresponding driver class. This usually applies to class. the forname () method explicitly loads a driver class, which is used by the driver to register with drivermanager and connect to the database.

Class. forname ("Sun. JDBC. ODBC. jdbcodbcdriver ");

Note:

1. The statement directly loads the JDBC-ODBC bridge driver class provided by Sun.

2. ODBC-JDBC bridge mode, you can use ODBC data source name to specify the database file

String url = "JDBC: ODBC: studlist ";

You can also directly use the physical location of the specified database file (which can be an absolute or relative path)

String url = "JDBC: ODBC: Driver = {Microsoft Access Driver (*. mdb)}; DBQ = D: // stud. mdb ";

Section 2 create a connection to the data source

1. JDBC driver

Generally, drivers are provided by database vendors. Currently, there are four types of JDBC drivers:

The first JDBC-ODBC bridge and ODBC driver

This was Sun's first JDBC driver. At that time, the main goal was to quickly promote JDBC so that the industry could accept this standard. In fact, this type of driver maps the jdbc api to the odbc api. JDBC-ODBC bridging uses Microsoft's Open Database interconnect interface (odbc api) to communicate with the database server, the client computer should first install and configure ODBC driver and JDBC-ODBC bridge driver.

The biggest problem with this bridging method is that Java is not bound to Windows, and Java cross-platform features are lost, which is unacceptable for Java applications. In addition, sun's ODBC-JDBC bridge is not multi-threaded, that is, it is not suitable for enterprise applications that require concurrent execution. As for the current usage, this type of driver is rarely used.

Second, local API

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.

This driver converts the Special Protocol of the database vendor into Java code and binary code, so that the Java Database client can communicate with the database server. For example, Oracle uses the sqlnet protocol and DB2 uses the IBM database protocol. The special protocol of the database vendor should also be installed on the client.

Third, JDBC network pure Java driver

In the early stages of Java, when applet is very popular, it is necessary to use applet to directly access the database. However, the applet security mode prohibits it from accessing database resources on multiple Web servers. To solve this problem, this driver is like an applet accessing the database proxy. Currently, this type of driver is rarely used.

This type of driver converts JDBC to a network protocol unrelated to DBMS, which is then converted into a DBMS Protocol by a server. This network server middleware can connect its pure Java client to a variety

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.

This method is pure Java driver. Database customers use standard network protocols (such as HTTP and shttp) to communicate with database access servers, the database access server then translates the standard network protocol into a database vendor's proprietary Special Database Access Protocol (which may also use ODBC driver) to communicate with the database. This is an ideal solution for Internet and Intranet users. The Java driver is automatically downloaded and installed on your computer in a transparent manner with applets from the web server.

Fourth local protocol pure Java driver

This type of driver directly converts JDBC calls 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.

This method is also pure Java driver. Database vendors provide special JDBC protocols to allow Java database customers to communicate with database servers. However, we will switch the proxy protocol to the database server's special JDBC driver. This is efficient for intranet applications, but the protocols of the database vendor may not be supported by the firewall. The lack of firewall support may pose potential security risks in Internet applications.

The choice of the driver is often difficult to decide. Generally, the second type of driver has many years of advantages, and the fourth type of driver avoids the hidden danger of local code, it may be better and easier to transplant.

Currently, Sybase, Informix, and SQL server can both use the fourth driver, while oracle can use the second driver. Oracle also provides the fourth type of driver for free. Visit the following URL:

Http://technet.oracle.com/software/tech/java/sqlj_jdbc/sofrware_index.html

The installation of the driver should carefully read the information provided by the database vendor. Next we will take the most common databases as an example to discuss the configuration of the database driver.

II. General Connection Methods

String url = "JDBC: ODBC: databasedsn ";

Connection con = drivermanager. getconnection (URL, "login", "password ");

Note:

Use the getconnection () method in the drivermanager class to establish a connection with the data source specified by the URL and return an object of the connection class. Subsequent operations on the data source are based on the connection class object; however, for small databases such as access, no user name or password is required.

String url = "JDBC: ODBC: databasedsn ";

Connection con = drivermanager. getconnection (URL );

System. Out. println (con. getcatalog (); // obtain the complete path and file name of the database.

JDBC uses the URL syntax to determine the global database (the database URL is similar to a common URL). The representation format of the data source specified by the URL is

JDBC: <subprotocal>: [database locator]

JDBC --- indicates the use of JDBC

Subprotocal --- define the driver type

Database locator --- Provide the location and port number of the network database, including the host name, port, and database system name)

JDBC: ODBC: // host.domain.com: Port/databasefile

The main Protocol JDBC, the driver type is ODBC, it specifies how the JDBC manager accesses the database, this example is specified to use the JDBC-ODBC bridge mode; others are the location of the database.

Database systems of different vendors are mainly different in JDBC drivers and Database URL formats.

Some specific problems are discussed below.

3. Access Connection Application

The Access database is a database that comes with Microsoft Office. It performs well in terms of security, convenience, and user interface, but it is only suitable for a small number of users.

Since it itself must be used in Windows, it is useful here to illustrate the application of the ODBC-JDBC bridge.

Data Source Name: faqaccess

Test code:

Import java. SQL .*;

Public class accessdemo {
Public static void main (string [] ARGs) throws exception {
// Faqaccess refers to the database name declared on ODBC
String url = "JDBC: ODBC: faqaccess ";
String query, subject, answer;
Connection conn; // create a connection class
Statement statement; // create an SQL statement execution class
Resultset = NULL; // creates a result set class.
// Instruct the program to use JDBC to create a database connection with the ODBC bridge
Class. forname ("Sun. JDBC. ODBC. jdbcodbcdriver ");
// Use the getconnection () method of the drivermanager class to establish a connection. The first character parameter defines the user name and the second character parameter defines the password.
Conn = drivermanager. getconnection (URL ,"","");
Statement = conn. createstatement (); // create an SQL statement execution class
// Number of records for data retrieval
Query = "select count (*) as rowcount from FAQs ";
// Execute the SQL command using executequery ()
Resultset = statement.exe cutequery (query );
Resultset. Next (); // next record that is moved to the database
// If the record is empty, add 10 records
If (resultset. getint ("rowcount") = 0 ){
For (INT I = 1; I <11; ++ I ){
String SQL = "insert into FAQs values (" + I + ", 'Question" + I + "', 'answer" + I + "')";
// Use executeupdate () to execute SQL commands other than Query
Statement.exe cuteupdate (SQL );
}
}
// Obtain all database records
Query = "select * From FAQs ";
Resultset = statement.exe cutequery (query );
// Use the next () method to travel each record of the database
While (resultset. Next ()){
// Use the getstring () method to retrieve the field content
Subject = resultset. getstring ("subject ");
Answer = resultset. getstring ("Answer ");
System. Out. Print ("question content =" + subject );
System. Out. println (", customer case content =" + answer );
}
Resultset. Close (); // close the result set.
Statement. Close (); // closes the SQL statement execution class
Conn. Close (); // closes the database connection class
}
}

Iv. SQL Server 2000 connection Application

Microsoft SQL Server is widely used in small and medium-sized database projects. It has fast connection speed, supports many users, provides high security, and provides a visual interface, therefore, it is widely used in many MIS Systems in China.

Microsoft has three drivers:

Msbase. Jar

Msutil. Jar

MSSQLServer. Jar

We can create a folder: jdbcsqlserver2000 under the C root directory. Copy the three files.

Construct the file setenv. CMD in the working directory,

Set Path = % PATH %; C:/j2sdk1.4.0/bin;

Set classpath =.; C:/j2sdk1.4.0/lib

Set classpath = % classpath %.; C:/jdbcsqlserver2000/msbase. jar; C:/jdbcsqlserver2000/msutil. jar; C:/jdbcsqlserver2000/MSSQLServer. Jar

To set the classpath path.

Start the file and set the path before running.

Note: In a UNIX operating system, the path is set:

Classpath = .; /home/user1/mssqlserver2000jdbc/lib/msbase. jar;/home/user1/mssqlserver2000jdbc/lib/msutil. jar;/home/user1/mssqlserver2000jdbc/lib/MSSQLServer. jar

You can make a small example of the call situation of the experiment database:

File Name: sqldemo. Java

Import java .*;
Import java. Lang .*;
Import java. SQL .*;

Public class sqldemo {
Public static void main (string [] ARGs) throws exception {
// Declare the connection, SQL statement execution object, and result set variable
Java. SQL. Connection conn = NULL;
Java. SQL. Statement stmt = NULL;
Java. SQL. resultset rs = NULL;
// Load the database driver
// Driver classname = com. Microsoft. JDBC. sqlserver. sqlserverdriver
Class. forname ("com. Microsoft. JDBC. sqlserver. sqlserverdriver ");
// Create a connection
// URL = JDBC: Microsoft: sqlserver: // COMMONOR-02A84C: 1433
// Properties
// Password =
// Databasename = pubs
// User = sa
// The computer name must be changed based on the actual situation
Conn = drivermanager. getconnection ("JDBC: Microsoft: sqlserver: // COMMONOR-02A84C: 1433; databasename = pubs; user = sa; Password = ");
// Create an SQL statement object
Stmt = conn. createstatement ();
// Execute the SQL statement
Stmt.exe cute ("select * from employee ");
// Obtain the result set
Rs = stmt. getresultset ();
// Print the result
While (Rs. Next ()){
System. out. println (RS. getstring ("fname") + "-" + Rs. getstring ("lname") + "-" + Rs. getstring ("job_id "));
}
Rs. Close (); // close the result set.
Stmt. Close (); // closes the SQL statement execution class
Conn. Close (); // closes the database connection class
}
}

Compile, run, and should work properly.