Use JDBC to create database objects

James W. Cooper IBM t. J. Watson Research Center May 1998

From: Principles of object-oriented programming in Java 1.1

Summary

This article analyzes Java database connectivity (JDBC), a technology that connects to a database in Java using an object-oriented method. It is an object-oriented encapsulation and re-design of ODBC APIs. It is easy to learn and use and enables you to write vendor-independent code for querying and manipulating databases. JDBC provides some low-level methods to access the database, and also provides powerful objects to process the database at a high level.

What is a database?

DatabaseIt is a series of information tables stored in a certain file structure. This file structure allows you to access these tables, select columns in the table, sort the table, and select rows according to various criteria. Databases usually have multipleIndexIt is associated with many columns in these tables, so we can access these tables as quickly as possible.

During computing, databases are more commonly used than other types of structures. You will find that the database is at the core of the employee record and salary system, and the database can be found in the travel planning system and throughout the product production and sales process.

Taking employee records as an example, you can imagine a table containing employee name, address, salary, tax deduction and allowance. Let's take a look at how these contents may be organized together. Assume that a table contains the employee name, address, and phone number. Other information you want to save may include salary, salary range, last salary increase time, next salary increase time, and employee performance evaluation.

Should these contents be stored in a table? Almost certainly not. There may be no difference in the wage range of employees of different categories. In this way, you can only store the employee type in the employee record table, and store the salary range in another table, associate the table with the type number. Consider the following:

Key	Lastname	Salarytype		Salarytype	Min	Max
1	Adams	2		1	30000	45000
2	Johnson	1		2	45000	60000
3	Smyth	3		3	60000	75000
4	Tully	1
5	Wolff	2

SalarytypeThe data in the column references the second table. We can imagine a number of such tables, such as tables used to store the tax value of the residential city and each city, and the amount deducted from the health plan. Each table has a primary key column (such as the leftmost column in the two tables above) and several data columns. Creating a table in a database is both an art and a science. The structure of these tables is determined by theirParadigm. We usually say that a table belongs to the first, second, or third paradigm, referred to as 1nf, 2nf, or 3nf.

First paradigm: Each table element in a table should have only one value (never an array ). (1nf)

Second paradigm: satisfies 1nf, and each sub-key column fully depends on the primary key column. This indicates that the relationship between the primary key and the remaining table elements in the row is 1-to-1. (2nf)

Third paradigm: 2nf is satisfied, and all the sub-key columns are independent of each other. The values contained in any data column cannot be calculated from the data of other columns. (3nf)

Currently, almost all databases are created based on the third paradigm (3nf. This means that there are usually quite a few tables, and there are relatively few information columns in each table.

Obtain data from the database

Suppose we want to generate a table containing employees and their wage ranges, which will be used in an exercise we designed. This table does not exist directly in the database, but can be constructed by sending a query to the database. We want to get a table as follows:

Name	Min	Max
Adams	$45,000.00	$60,000.00
Johnson	$30,000.00	$45,000.00
Smyth	$60,000.00	$75,000.00
Tully	$30,000.00	$45,000.00
Wolff	$45,000.00	$60,000.00

Or, sort by wage increase order

Name	Min	Max
Tully	$30,000.00	$45,000.00
Johnson	$30,000.00	$45,000.00
Wolff	$45,000.00	$60,000.00
Adams	$45,000.00	$60,000.00
Smyth	$60,000.00	$75,000.00

We found that the Query Form for these tables is as follows:

SELECT DISTINCTROW Employees.Name, SalaryRanges.Min,SalaryRanges.Max FROM Employees INNER JOIN SalaryRanges ON Employees.SalaryKey = SalaryRanges.SalaryKeyORDER BY SalaryRanges.Min;

This language is called a structured query language, that is, SQL (generally read as "sequel"). It is a language that can be used by almost all databases. In recent years, SQL standards have been adopted, and most PC databases support most ANSI standards. SQL-92 standards are considered a fundamental standard and have been updated multiple times. However, none of the databases can perfectly support later SQL versions, and most databases provide a variety of SQL extensions to support their unique database performance.

Database Type

Since PC has become the main office tool, a large number of popular databases on PC have been developed, and these databases can be managed by themselves. They include primary databases such as Microsoft Works and more complex databases such as approach, dBase, Borland paradox, Microsoft Access, and Foxbase.

Another type of PC database includes databases that can be accessed by many PC clients through servers. These include ibm db/2, Microsoft SQL Server, Oracle, Sybase, sqlbase, and XDB. All these database products support a variety of relatively similar SQL dialects, so all databases initially seem to be interchangeable. Of course, theyNoThe reason for interchange is that each database has different performance characteristics, and each has different user interfaces and programming interfaces. You may think that since they all support SQL, the programming for them should be similar, but this is absolutely wrong, because each database uses its own method to receive SQL queries, and return results in its own way. This naturally leads to a new generation of standards: ODBC

ODBC

If we can write code that does not depend on the database of a specific manufacturer in some way and can get the same results from these databases without changing our calling program, that would be a good thing. If we can write some encapsulation for all these databases to make them have similar programming interfaces, this kind of database programming is easy to implement without vendor features.

Microsoft first tried this technique in 1992, and the company released a specification called object database connectivity. This is considered the answer to connecting to all databases in windows. Like the first version of all software, it also experienced some development troubles. In 1994, another version was launched, which is faster and more stable. It is also the first 32-bit version. In addition, ODBC has started to develop to other platforms outside of windows, so far it has become very common in the PC and workstation fields. Almost every major database vendor provides ODBC drivers.

However, ODBC is not a panacea we initially imagined. Many database vendors use ODBC as an "alternative interface" other than the standard interface, and the programming of ODBC is negligible. Like other windows programming, it includes handles, pointers, and options, making it hard to grasp. Finally, ODBC is not a neutral standard. It was developed by Microsoft and continuously improved by Microsoft, and Microsoft also launched a highly competitive software platform for all of us, making the future of ODBC unpredictable.

What is JDBC?

JDBC is a group of acronyms that once represent "Java database connectivity", but now it has become a trademark. It is an object-oriented encapsulation and re-design of ODBC APIs. It is easy to learn and use, and it enables you to write code that does not depend on the vendor, used to query and manipulate databases. Although it is object-oriented like all Java APIs, it is not a very high-level object set. In the rest of this chapter, we will propose a more advanced method.

In addition to Microsoft, most vendors use JDBC and provide JDBC drivers for their databases. This allows you to easily write code that is almost completely independent of databases. In addition, mongooft and intersolv have developed a product called JDBC-ODBC bridge that enables you to connect to a database without a direct JDBC driver. All databases supporting JDBC must support at least SQL-92 standards. This greatly realizes the portability across databases and platforms.

Install and use JDBC

JDBC classes are all classifiedJava. SQLPackage is automatically installed when Java JDK 1.1 or later is installed. However, if you want to use the JDBC-ODBC bridge, you must install two additional packages. First, if you are using Windows 95, you must upgrade your ODBC driver to a 32-bit driver. You can download it from the Microsoft website. This driver is hard to find on Microsoft's website; Search dataacc.exe and download and install it.

JDBC-ODBC drivers can be easily found and downloaded from Sun's Java website (http://java.sun.com. After you expand and install the driver, you must perform the following steps:

1. Set/Jdbc-ODBC/classes;Path to your path environment variable.
2. Set/Jdbc-ODBC/classes;Path to your classpath environment variable.
3. In Windows 95, place them in the autoexec. BAT file and reboot to make all settings take effect.
4. In Windows NT, add them to the Environment tab of the System Object in Control Panel, and log out and log on again to make them take effect.

JDBC driver type

There are actually four methods for connecting Java programs to the database:

1. JDBC-ODBC bridge and ODBC drivers -- in this way, this is a local solution because ODBC drivers and Bridge Code must appear on each machine of the user. Basically, this is a temporary solution.
3. Native code and Java drivers-It replaces ODBC and JDBC-ODBC bridges with another local solution (the Java-callable native code on the platform.
5. The pure Java driver of the JDBC network-an independent protocol sent to the server by the JDBC translated by the Java driver. Then, the server can connect to any number of databases. This method allows you to call the server from the client Applet and return the result to your applet. In this case, the middleware software provider can provide servers.
7. The local protocol Java driver-the Java driver directly converts the protocol to the database and calls it. This method can also be used through the network, and the results can be displayed in the Web browser applet. In this case, each database vendor will provide drivers.

If you want to write code to process a PC client database, such as dBase, FOXBASE, or access, you may use the first method and have all the code on your machine. Larger client-server database products (such as IBM DB2) already provide 3rd-level drivers.

Two-layer model and three-layer model

When the database and the application that queries it are on the same machine and there is no server code intervention, we call the generated programTwo-layer model. One layer is the application, and the other layer is the database. This is often the case in JDBC-ODBC bridge systems.

When an application or applet calls the server and the server calls the database again, we call itLayer-3 Model. This is often the case when you call a program called a "server.

Write JDBC code to access the database

Now, let's take a look at how to write a Java program to access the database. The database we want to use is a Microsoft Access database called groceries. MDB. The data in this database consists of the prices of some common items in three local grocery stores. The food table is as follows:

Foodkey	Foodname
1	Apples
2	Oranges
3	Hamburger
4	Butter
5	Milk
6	Cola
7	Green beans

The grocery table is as follows:

Storekey	Storename
1	Stop and Shop
2	Village Market
3	Waldbaum's

The grocery price table only consists of the key values and prices in the three tables:

Fskey	Storekey	Foodkey	Price
1	1	1	$0.27
2	2	1	$0.29
3	3	1	$0.33
4	1	2	$0.36
5	2	2	$0.29
6	3	2	$0.47
7	1	3	$1.98
8	2	3	$2.45
9	3	3	$2.29
10	1	4	$2.39
11	2	4	$2.99
12	3	4	$3.29
13	1	5	$1.98
14	2	5	$1.79
15	3	5	$1.89
16	1	6	$2.65
17	2	6	$3.79
18	3	6	$2.99
19	1	7	$2.29
20	2	7	$2.19
21	3	7	$1.99

Use ODBC to register your database

Before accessing the ODBC database in Windows 95 or NT, you must use the ODBC driver in the control panel to register it. In Windows 95, it is the ODBC icon in the "control panel" program. In Windows NT, you will find this program in the "Start" menu. (If not, install the ODBC driver wx1350.exe ).

Double-click the ODBC icon and click "add", as shown in 1. Select the database Driver (Microsoft Access is used here) and click OK ". In the "Data Source Name" and "Description", enter the data source name (groceries) and database description (grocery prices) (neither of which must be related to the file name ), click Select, find the database, and select the database. After you find the database, the screen will be 2. Click OK, and then click Close to close the panel.

Figure 1: ODBC Control Panel setting screen.

Figure 2: select the database and description in the ODBC control panel.

Connect to database

All Database-related objects and methods are included in the Java. SQL package. Therefore, you must add"Import java. SQL .*". JDBC to connect to the ODBC database, you must first load the JDBC-ODBC bridge driver

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

This statement loads the driver and creates an instance of this class. To connect to a specific database, you must create an instance of the Connect class and connect to the database using the URL syntax.

String url = "jdbc:odbc:Grocery prices";Connection con = DriverManager.getConnection(url);

Note that the database name you are using is the name of the "Data Source" you entered in the ODBC settings panel.

The URL syntax may vary greatly depending on the database type.

jdbc:subprotocol:subname

The first group of characters indicates the connection.ProtocolAnd alwaysJDBC. There may also beSub-ProtocolIn this section, the sub-protocol is specifiedODBC. It specifies the connectivity mechanism of a class of databases. If you want to connect to the database server on another machine, you may also need to specify the machine and a subdirectory:

jdbc:bark//doggie/elliott

Finally, you may need to specify the user name and password as part of the connection string:

jdbc:bark//doggie/elliot;UID=GoodDog;PWD=woof

Access Database

Once connected to the database, you can request information such as the table name and table column name and content, and you can run an SQL statement to query the database or add or modify its content. Objects that can be used to obtain information from the database include:

Databasemetadata	Information about the entire database: Table Name, table index, database product name and version, and operations supported by the database.
Resultset	Information about a table or query result. You must access data rows row by row, but you can access columns in any order.
Resultsetmetadata	Information about the names and types of columns in the resultset.

Although each object has a large number of methods that allow you to obtain extremely detailed information about database elements, there are several main methods in each object that allow you to obtain the most important information about data. However, if you want to see more information than here, we recommend that you learn the document for instructions on other methods.

Resultset

The resultset object is the most important single object in JDBC. Essentially, it is an abstraction of a table of General width and unknown length. Almost all methods and queries return data as resultset. The resultset contains any number of named columns. You can access these columns by name. It also contains one or more rows, which can be accessed from top to bottom in order. Before using resultset, you must query how many columns it contains. This information is stored in the resultsetmetadata object.

　

// Obtain the number of columns from the metadata. resultsetmetadata rsmd; rsmd = results. getmetadata (); numcols = rsmd. getcolumncount ();

When you obtain a resultset, it points to the position before the first row. You can useNext ()Method to get each other row. If no more rows exist, this method returnsFalse. Because retrieving data from the database may cause errors, you must always include the result set processing statement inTryBlock.

try{rsmd = results.getMetaData();numCols = rsmd.getColumnCount();boolean more = results.next();while (more) { for (i = 1; i <= numCols; i++) System.out.print(results.getString(i)+" "); System.out.println(); more = results.next(); }results.close();}catch(Exception e){System.out.println(e.getMessage());}

You can obtain data in the resultset in multiple forms, depending on the data type stored in each column. In addition, you can obtain the content of a column by column number or column name. Note that the column number starts from 1 rather than 0. The most common methods of the resultset object are as follows.

getInt(int);	Set the serial numberIntIs returned as an integer.
getInt(String);	The name isStringIs returned as an integer.
getFloat(int);	Set the serial numberIntAsFloatType.
getFloat(String);	The name isStringAsFloatType.
getDate(int);	Set the serial numberIntIs returned as a date.
getDate(String);	The name isStringIs returned as a date.
next();	Move the row pointer to the next row. If no remaining row exists, returnFalse.
close();	Disable the result set.
getMetaData();	ReturnResultsetmetadataObject.

Resultsetmetadata

You useGetmetadata ()Method slaveResultset.ResultsetmetadataObject. You can use this object to obtain the number and type of columns and the name of each column.

getColumnCount();	Returns the number of columns in the resultset.
getColumnName(int);	The number of returned columns isInt.
getColumnLabel(int);	Returns the label implied in this column.
isCurrency(int);	If this column contains a number with a currency unit, returnTrue.
isReadOnly(int);	If this column is read-only, returnTrue.
isAutoIncrement(int);	If this column is auto-incrementing, returnTrue. This type of column is usually a key and is always read-only.
getColumnType(int);	Returns the SQL data type of this column. These data types include Bigint Binary Bit Char Date Decimal Double Float Integer Longvarbinary Longvarchar Null Numeric Other Real Smallint Time Timestamp Tinyint Varbinary Varchar

Databasemetadata

DatabasemetadataObjects can provide you with information about the entire database. You can use it to obtain the name of the table in the database and the name of the column in the table. Because different databases support different SQL variants, there are also multiple ways to query which SQL methods are supported by the database.

getCatalogs()	Returns the list of information directories in the database. With the JDBC-ODBC bridge driver, you get a list of databases registered with ODBC. This is rarely used in JDBC-ODBC databases.
Gettables (catalog, schema, tablenames, columnnames)	The returned table name andTablenamesAnd the column name matchesColumnnamesDescription of all tables.
Getcolumns (catalog, schema, tablenames, columnnames)	The returned table name andTablenamesAnd the column name matchesColumnnamesDescription of all table columns.
getURL();	Obtain the name of the URL you are connected.
getDriverName();	Obtain the name of the database driver you are connected.

Obtain table information

You can useGettables ()Methods to obtain information about tables in the database. This method has the following four string parameters:

results =dma.getTables(catalog, schema, tablemask, types[]);

The parameter indicates:

catalog	The directory name in which you want to find the table name. For a JDBC-ODBC database and many other databases, you can set itNull. The directory items of these databases are actually the absolute path names in the file system.
schema	The Database "solution" to be included ". Many databases do not support the scheme. For other databases, it represents the username of the database owner. Set itNull.
tablemask	A mask used to describe the name of the table to be retrieved. If you want to retrieve all table names, set them as wildcards%.Please note that,