James W. Cooperibm T.J. Watson Research Center May 1998
Excerpted from: Object-oriented programming in Java 1.1
Summary
This paper analyzes Java Database Connectivity (JDBC), a technology that connects to the database in Java to object-oriented ways. It is an object-oriented package and redesign for ODBC API. It is easy to learn and use, and it enables you to write code that does not rely on suppliers for querying and manipulating databases. JDBC provides some equivalent low-layer methods to access databases, and also provide powerful objects in a fairly high layer to process databases.
What is a database?
The database is a series of information stores stored in some file structure, which allows you to access these tables, select the columns in the table, sort the table and select line according to various standards. Database usually have multiple indexs associated with many columns in these tables, so we can access these tables as quickly as possible.
When calculating, the database is more commonly used in other types of structures. You will find that the database is in the core status in the employee record and salary system, and the database can be found in the travel planning system and during the entire process of product production and sales.
Taking employee record as an example, you can imagine a table containing the employee's name, address, salary, deduction, and allowances. Let us consider these contents may be organized together. You can imagine a table that contains employee names, addresses, and phone numbers. Other information you want to save may include salary, wage, last salary time, next salary-saving time, employee performance assessment and other content.
Is these contents should be saved in a table? It is almost certain that it should not be. There may be no difference in the wage range of different categories of employees; this, you can store only employee types in the employee record table, and store the wage range in another table, and associate with this table by 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
The data in the SalaryType column is referenced to the second table. We can imagine a table such as a table for storing the tax value of living cities and each city, and the health plan is deducted. Each table has a primary key column (such as the leftmost column above the top of the top) and a number of data columns. The establishment of a table in the database is both an art and a science. The structure of these tables is pointed out by their paradigm. We usually say that the first, second or third paradigm is referred to as 1nf, 2nF or 3nf.
The first paradigm: Each table in the table should have only one value (never could be an array). (1nf)
Second paradigm: satisfy 1NF, and each subtalog is completely dependent on the primary key column. This means that the main key and the remaining table elements in the row are 1 pair 1. (2NF)
The third paradigm: satisfies 2NF, and all subtysis are independent of each other. The values contained in any of the data columns cannot be calculated from other columns. (3NF)
Now, almost all databases are created based on "Third Paradigm (3NF)". This means that there is usually a very much table, and the information columns in each table are relatively small.
Get data from the database
Suppose we want to generate a table containing employees and their wages, which will use this table in one exercise we designed. This form does not exist directly in the database, but can build it by sending a query to the database. We hope to get a table as follows:
Name
MIN
Max
ADAMS $ 45,000.00 $ 30,000.00 $ 45,000.00Smyth $ 60,000.00 $ 75,000.00 Tully $ 30,000 $ 45,000.00Wolff $ 45,000.00 $ 60,000.00 or in order of salary
Name
MIN
Max
Tully $ 30,000.00 $ 30,000.00 $ 45,000.00Wolff $ 45,000.00.00.00Adams $ 45,000.00.00.00.00Smyth $ 60,000.00 $ 75,000.00
We found that the query form of these tables is as follows
Select DistINCTROW Employees.name, SalaryRanges.min,
SalaryRanges.max from Employees Inner Join SalaryRanges on Employees.salaryKey = SalaryRanges.salaryKey
Order by SalaryRanges.min;
This language is called a structured query language, which is SQL (typically reading "Sequel"), and it is a language that almost currently all databases can be used. In these years, if the SQL standard is promulgated, most PC databases support most ANSI standards. SQL-92 standards are considered to be a basic standard and have been updated multiple times. However, there is no database to perfectly support the subsequent SQL version, and most databases provide a variety of SQL extensions to support their database alone.
Type of database
Since the PC has become the main office tool, a large number of popular databases on the PC have been developed, which are self-managed. They include primary databases such as Microsoft Works, as well as 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. These include IBM DB / 2, Microsoft SQL Server, Oracle, Sybase, SQLBase, and XDB. All of these database products support a variety of relatively similar SQL dialects, so all databases initially seem to be interchangeable. Of course, the reason why they cannot be interchanged 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 of them should be similar, but this is absolutely wrong because each database is used to receive SQL queries, and return the results in their own way. This naturally leads out a new generation of standards: ODBC
ODBC
If we can write code that does not depend on the database of specific vendors in some way, and you can get the same result from these databases without changing your call programs, that will be a good thing. If we can write some encapsulation only to all of these databases, they have similar programming interfaces, which can be easily implemented for database programming.
Microsoft first tried this skill in 1992, which issued a specification called object database connectivity. This is considered to be answering all the databases in a Windows environment. As in the first version of all software, it has also experienced some developing troubles, launched another version in 1994, which is run faster, and more stable. It is also the first 32-bit version. In addition, ODBC has developed to other platforms other than Windows, so far it is generally in the field of PC and workstations. Almost every major database vendor provides an ODBC driver. However, ODBC is not our original idea that I originally imagined. Many database vendors use ODBC as an "alternative interface" other than its standard interface, and the programming of ODBC is negligible. As with other Windows programming, it includes handles, pointers, and options that make it difficult to master. The last point, ODBC is not a neutral standard. It was developed by Microsoft, and the company continued to improve, and Microsoft also launched a very competitive software platform used by our owners, which makes ODBC's future difficult to predict.
What is JDBC?
JDBC is a group of first letters, which once represents "Java Database Connectivity", but now it has become a logo symbol. It is an object-oriented package and redesign for ODBC API. It is easy to learn and use, and it really enables you to write unrelated vendor's code for querying and manipulating databases. Although it is the same as all Java APIs, it is object-oriented, but it is not a high-level object set. In the remainder of this chapter, we will propose higher levels.
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 the database. In addition, JavaSoft and Intersolv have developed a product called JDBC-ODBC Bridge that allows you to connect to the database of JDBC drivers. All databases that support JDBC must support the SQL-92 standard. This greatly implements transplantability across databases and platforms.
Install and use JDBC
JDBC classes are returned to the Java.sql package, which is automatically installed when installing Java JDK 1.1 or higher. However, if you want to use a JDBC-ODBC bridge, you must also install two additional packages. First, if you use Windows 95, you must upgrade your ODBC driver to a 32-bit driver, you can download from Microsoft's website. This driver is difficult to find on Microsoft's website; please search DataAcc.exe and download and install.
The JDBC-ODBC driver can easily find and download from the Sun's Java website (http://java.sun.com). After you expand and install this driver, you must perform the following steps:
Add / JDBC-ODBC / CLASSES; the path is added to your PATH environment variable. Add / JDBC-ODBC / CLASSES; the path is added to your ClassPath environment variable. In a Windows 95 environment, put them in the AutoExec.bat file, reboot so that all settings take effect. In the Windows NT environment, add them to the Environment tab of the System object in the Control Panel, exit and log in to make it take effect.
Type of JDBC drivers
The method of connecting the database connection database actually has four:
JDBC-ODBC Bridge and ODBC Drivers - In this way, this is a local solution because the ODBC driver and bridge code must appear in each machine of the user. Fundamentally, this is a temporary solution. This machine code and Java driver - it uses another local solution (which is replaced by ODBC and JDBC-ODBC Bridge with another local solution (the Java-called native code). JDBC Network's Pure Java Driver - JDBC translated by Java driver forms an independent protocol for transmitting to the server. The server then connects 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 a server. This machine protocol JAVA driver-Java driver directly converts the protocol of the database and calls. This method can also be used through the network, and the results can be displayed in the Applet of the web browser. In this case, each database vendor will provide a driver. If you want to write code to handle the PC client database, such as DBASE, FoxBase, or Access, you may use the first method and have all the code on the user machine. A larger client-server database product (such as IBM DB2) has provided a third-level driver.
Two-layer model and three-layer model
When the database and query it on the same machine, and without the intervention of server code, we will be called two-layer model. One layer is an application, and the other layer is a database. This is usually this in the JDBC-ODBC bridge system.
When an application or applet calling the server, when the server calls the database, we call it three-layer model. This is usually the case when you call a program called "server".
Write a JDBC code access database
Now, we will start to see how to write Java programs to access the database. The database we have to use is a Microsoft Access database called Groceries.mdb. The data in this database consists of some of three local grocery stores. The food form is as follows:
FoodKey
Foodname
1Apples2ranges3hamburger4butter5milk6cola7green beans
The grocery store is as follows:
StoreKey
Storename
1stop and shop2village market3waldbaum's
The grocery store pricing table is only composed of key values and prices in the three forms:
Fskey
StoreKey
FoodKey
PRICE
$ 111 $ 0.27221 0.33412 0.29331 $ $ $ 0.47713 0.29632 0.36522 $ $ $ 2.45933 1.98823 2.291014 $ $ $ 3.291315 2.991234 2.391124 $ $ $ 1.891616 1.791535 1.981425 $ $ $ 2.991917 3.791836 2.651726 $ $ $ 2.292027 2.192137 $ 1.99
Register your database with ODBC
Before you access the ODBC database in a Windows 95 or NT environment, you must register it using the ODBC driver in the control panel. In the Windows 95 environment, it is an ODBC icon in the Control Panel program. In the Windows NT environment, you will find this program in the Start menu. (If you can't find it, you need to install the ODBC driver described above, ie WX1350.exe.
Double-click the ODBC icon and click Add, as shown in Figure 1. Then select the database driver (here you use Microsoft Access), then click OK. Type the data source name (Grocery Price) (these two items do not need to be related to the "Data Source Name" and "Description", and then click "Select" to find the database, and Select this database. After finding the database, the screen will be shown in Figure 2. Click OK, and then click Close to close the panel. Figure 1: ODBC Control Panel Settings the screen.
Figure 2: Select the database and instructions in the ODBC Control Panel.
Connect to the database
All objects and methods related to the database are in the Java.sql package, so "Import Java.SQL. *" Must be added in the program with JDBC. JDBC To connect 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 the class. Then, to connect to a specific database, you must create an instance of the Connect class and use the URL syntax to connect the database.
String Url = "JDBC: ODBC: Grocery Price";
Connection con = DriverManager.getConnection (URL);
Note that the database name you use is the "Data Source" name you entered in the ODBC Settings panel.
The URL syntax may vary greatly depending on the database type.
JDBC: Subprotocol: SUBNAME
The first set of characters represents the connection protocol and is always JDBC. There may also be a sub-protocol where the sub protocol is designated as ODBC. It specifies the connectivity mechanism for a class of databases. If you want to connect the database server on other machines, you may also specify the machine and a subdirectory:
JDBC: BARK / / DOGGIE / ELLIOTT
Finally, you may want to specify the username 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 a table name and a list of names and contents of the table column, and you can run the SQL statement to query the database or add or modify its content. Objects available to get information from the database:
DatabaseMetadata information about the entire database: table name, table index, name and version of the database product, and the operation of database support. ResultSet About a table of information or results of a query. You must access the data line by line, but you can access columns in any order. ResultSetMetadata information about the names and types of columns in ResultSet.
Although each object has a large number of ways to get the extremely detailed information of the database element, there are several major methods to get the most important information for data in each object. However, if you want to see more information than this, it is recommended that you learn a document to get the description of the rest.
Resultset
The ResultSet object is the most important single object in JDBC. In essence, it is an abstraction of a table for a general width and unknown length. Almost all methods and queries return the data as the Resultset. The ResultSet contains any number of naming columns, and you can access these columns by name. It also contains one or more rows, you can access it from top to top by sequence. You must query how many columns it contains before you use the ResultSet. This information is stored in the ResultSetMetadata object. // obtain the number of columns from metadata
ResultSetMetadata RSMD;
RSMD = Results.getMetadata ();
Numcols = rsmd.getcolumncount ();
When you get a resultset, it just points to the position before the first line. You can use the next () method to get another row, when there is no more line, the method returns false. Since getting data from the database may result in errors, you must always include result set processing statements in a TRY block.
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 get data in the ResultSet in a variety of forms depending on the data type stored in each column. Also, you can get the contents of the column by column or column name. Note that the column number starts from 1, not from 0. Some of the most common methods for the ResultSet object are as follows.
GetInt (int);
Returns the content of the column of the serial number INT as an integer. GetInt (String);
Return the content of the column name String as an integer. Getfloat (int);
Returns the content of the column of the serial number INT as a FLOAT type. Getfloat (String);
Return the contents of the column name String as a FLOAT model. Getdate (int);
Returns the content of the column of the serial number INT. Getdate (String);
Returns the content of the column name String as a date. NEXT ();
Move the row pointer to the next line. Returns false if there is no residual line. CLOSE ();
Close the result set. Getmetadata ();
Returns the ResultSetMetadata object.
ResultSetmetadata
You use the getMetadata () method to get the ResultSetMetadata object from the ResultSet. You can use this object to get the number and type of column and the name of each column.
GetColumncount ();
Returns the number of columns in the ResultSet. GetColumnName (int);
Returns the column number of the column number INT. GetColumnlabel (int);
Returns the contained label. ISCurrency (int);
Returns True if this column contains a number with currency units. IsreadOnly (int);
Returns True if this is read-only. ISAUTOINCREMENT (INT);
Returns true if this column is automatically incremented. This type of column is usually key and is always read-only. getColumnType (int); Returns this column SQL data type. These data types include
BIGINTBINARYBITCHARDATEDECIMALDOUBLEFLOATINTEGERLONGVARBINARYLONGVARCHARNULLNUMERICOTHERREALSMALLINTTIMETIMESTAMPTINYINTVARBINARYVARCHAR
DatabaseMetadata
DatabaseMetadata objects can provide you with information about the entire database. You mainly use it to get the name of the table in the database, and the names listed in the table. Since different databases support different SQL variants, there are also multiple methods to query the database to support which SQL methods.
Getcatalogs ()
Returns the list of information in the database. With a JDBC-ODBC Bridge driver, you can get a list of databases registered with ODBC. This is rarely used in the JDBC-ODBC database. GetTables (Catalog, Schema, TableNames, ColumnNames) returns to the table names that match the tablenames and column names and ColumnNames. GetColumns (Catalog, Schema, Tablenames, ColumnNames) Returns all table columns that are grouped with Tablenames and column names with ColumnNames. GetURL ();
Get the name of the URL you connect. GetDrivername ();
Get the name of the database driver you connect.
Get information about the table
You can use the DatabaseMetadata's GetTables () method to get information on the table in the database. This method has the following four String parameters:
Results =
Dma.gettables (Catalog, Schema, Tablemask, Types []);
The meaning of the parameters is:
Catalog
To find the directory name of the table name. For a JDBC-ODBC database, many other databases, it can be set to NULL. The directory items of these databases are actually the absolute path name in the file system. Schema
The database "scheme" to be included. Many databases do not support the scheme, and for other databases, it represents the username of the database owner. Generally set it to NULL. Tablemask
A mask used to describe the name of the table you want to retrieve. If you want to retrieve all table names, set it to wildcard. Note that wildcards in SQL are% symbols instead of a general PC user * symbol. Types []
This is a String array describing the type of the table you want to retrieve. A database usually includes many tables for internal processing, and it is not worthy of you as a user. If it is a null value, you get all these tables. If you set it with a single element array containing string "Tables", you will only get a table you useful for users.
The simple code used to get the table name in the database is equivalent to obtaining the DatabaseMetadata object and retrieves the table name from it:
Con = DriverManager.getConnection (URL);
/ / Get the metadata of the database
DMA = con.getMetadata ();
// Dump the name of the table in the database
String [] types = new string [1];
Types [0] = "Tables"; // Set the query type
/ / Please note that the wildcard is% symbol (instead of "*")
Results = Dma.gettables (NULL, NULL, "%", TYPES);
Then, we can print the table name, as we do above: boolean more = results.next ();
WHILE (more)
{
For (i = 1; i <= numcols; i )
System.out.print (Results.getstring (i) ");
SYSTEM.OUT.Println ();
More = results.next ();
}
As mentioned earlier, all code is included in the TRY block.
Execute SQL query
We have understood the basic objects of JDBC, and now you can perform SQL query. The query is performed as a STATEMENT object, and you easily get a Statement object from the Connection object:
String Query = "SELECT FOODNAME from food;"
ResultSet Results;
Try
{
Statement Stmt = con.createstatement ();
Results = stmt.executequery (query);
}
Catch (Exception E)
{System.out.println ("Query Exception");
Note that this simple query returns the entire FoodName column in the FOOD table. You use simple queries like this to get the contents of the entire column. Please note that the query itself is an RESULTSET, you can use the method we have just discussed above.
Print ResultSet
Because we always print data from ResultSets, we can design a simple way to dump the entire resultset, including table name metadata. The subroutine is as follows:
Private void DumpResults (String Head)
{
// This is the content of the print column header and each column
// General method
System.out.println (HEAD);
Try
{
// Get the number of columns from metadata
RSMD = Results.getMetadata ();
Numcols = rsmd.getcolumncount ();
// Print column name
For (i = 1; i <= numcols; i )
System.out.print (rsmd.getcolumnname (i) "");
SYSTEM.OUT.Println ();
// Print column content
Boolean more = results.next ();
WHILE (more)
{
For (i = 1; i <= numcols; i )
System.out.print (Results.getstring (i) ";
SYSTEM.OUT.Println ();
More = results.next ();
}
}
Catch (Exception E)
{System.out.println (E.getMessage ());
}
A simple JDBC program
We have learned all the basic functions of JDBC. Now we can write a simple program that opens the database, prints its table name and the content of a list, and then performs queries for the database. This procedure is as follows:
Import java.net.URL;
Import java.sql. *;
Import java.util. *;
Class JDBCODBC_TEST
{
ResultSet Results; ResultSetMetadata RSMD;
DatabaseMetadata DMA;
CONNECTION CON;
Int Numcols, I;
//
Public JDBCODBC_TEST ()
{
String Url = "JDBC: ODBC: Grocery Price";
String query = "Select DistINCTROW Foodname from food" "where (Foodname Like 'C%');";
Try
{
/ / Load JDBC-ODBC Bridge Driver
Class.Forname ("Sun.jdbc.odbc.jdbcodbcdriver");
//Connect to the database
Con = DriverManager.getConnection (URL);
/ / Get the metadata of the database
DMA = con.getMetadata ();
System.out.println ("Connected to:" DMA.GetURL ());
System.out.println ("driver" Dma.GetdriverName ());
// Dump the name of the table in the database
String [] types = new string [1];
Types [0] = "Tables";
/ / Please note that the wildcard is% symbol (instead of "*")
Results = Dma.gettables (NULL, NULL, "%", TYPES);
DUMPRESULTS ("- Tables--");
Results.close ();
}
Catch (Exception E)
{System.out.println (e);
/ / Get the name of the table
System.out.println ("- column names -");
Try {
Results =
Dma.getColumns (NULL, NULL, "Foodprice", NULL;
ResultSetmetadata RSMD = results.getMetadata ();
INT Numcols = rsmd.getColumnCount ();
While (results.next ())
String cname = result.getstring ("column_name");
System.out.print (CNAME ");
SYSTEM.OUT.Println ();
Results.close ();
}
Catch (Exception E)
{System.out.println (e);
// list the content of a column - this is a query
Try {
Statement Stmt = con.createstatement ();
Results =
Stmt.executeQuery ("SELECT FOODNAME FOOD";
}
Catch (Exception E)
{System.out.println ("Query Exception");
DumpResults ("- Contents of Foodname Column);
/ / Try the actual SQL statement
Try
{
Statement Stmt = con.createstatement ();
Results = stmt.executequery (query);
Catch (Exception E)
{System.out.println ("Query Exception");
DumpResults ("- Results of query--");
}
The result of this program is as follows:
C: / projects / Objectjava / Chapter19> Java JDBCODBC_TEST
Connected to: JDBC: ODBC: Grocery Price
Driver JDBC-ODBC Bridge (ODBCJT32.DLL)
--Tables ---
Table_qualifier table_owner table_name Table_Type Remarks
Grocery Null Food Table Null
Groceries Null FoodPrice Table NULL
Groceries Null Stores Table NULL
--Column names ---
Fskey Storekey FoodKey Price
--Contents of foodname column--
Foodname
Apples
Oranges
Hamburger
Butter
Milk
Cola
Green Beans
--Results of query -
Foodname
Cola
Build a higher level JDBC object
It will be apparent from the example from the example. If we can package our methods in several higher level objects, it will be very helpful, we can not only package the TRY block, but also access the ResultSet method more simply. .
In this section, we will build a new RESULTSET object that encapsulates the JDBC ResultSet object and returns a row of data in the form of a String array. We found that you always need to get the serial number and name of the column from the ResultSetMetadata object, so new objects that create a packaging metadata are very reasonable.
In addition, we often need to extract the elements of a row by name or integer index. If you don't have to always include these access statements, it will be greatly helpful. The last point, if we need the content of the whole line, it is more convenient to return the entire line in the form of a string array. In the ResultSet object shown below, we are committed to implementing these goals:
Class ResultSet
{
// This class is the more advanced abstraction of the JDBC Resultset object
ResultSet RS;
ResultSetMetadata RSMD;
Int numcols;
Public ResultSet (ResultSet Rset)
{
RS = RSET;
Try
{
/ / Get metadata and column numbers simultaneously
RSMD = rs.getMetAdata ();
Numcols = rsmd.getcolumncount ();
}
Catch (Exception E)
{System.out.println ("ResultSet Error"
E.getMessage ());
}
//
Public string [] getMetadata ()
{
/ / Returns to contain all column names or other metadata
// An array
String md [] = new string [numcolls];
Try
{
For (int i = 1; i <= numcols; i )
MD [i-1] = rsmd.getColumnname (i);
}
Catch (Exception E) {System.out.Println ("Meta Data Error"
E.getMessage ());
Return MD;
}
//
Public Boolean HasmoreElements ()
{
Try {
Return rs.next ();
}
Catch (Exception E) {Return False;}
}
//
Public String [] nextElement ()
{
// Copy the contents of the row to the array of strings
String [] row = new string [numcols];
Try
{
For (int i = 1; i <= numcols; i )
ROW [i-1] = rs.getstring (i);
}
Catch (Exception E)
{System.out.println ("Next Element Error"
E.getMessage ());
Return Row;
}
//
Public String getColumnValue (String ColumnName)
{
String res = "";
Try
{
Res = rs.getstring (columnname);
}
Catch (Exception E)
{System.Out.println ("Column Value Error:"
ColumnName E.getMessage ());
Return res;
}
//
Public String GetColumnValue (INT i)
{
String res = "";
Try
{
RES = rs.getstring (i);
}
Catch (Exception E)
{System.Out.println ("Column Value Error:"
ColumnName E.getMessage ());
Return res;
}
//
Public void finalize ()
{
Try {r.close ();
Catch (Exception E)
{System.out.println (E.getMessage ());
}
}
Create a ResultSet object by simply using the New operator, we can easily package any ResultSet objects in this class:
ResultSet Results = .. / / Resultsset is obtained according to the usual method
// Use it to create a more useful object
ResultSet RS = New ResultSet (RESULTS);
It is easy to use this object in any JDBC program.
Build a Database object
The other part of our 00 chain moves to the top of the top of the Database object, which will package the behavior of the following objects: Connection, Statement, and DatabaseMetadata objects, and our SQL query and resultet. Our Database object allows us to create a connection, get the table name, move in the database and more simply get the values of rows and columns. Note that the execute method returns a RESULTSET object, you can do it directly.
Class Database
{
// This is a class that encapsulates all the features of the JDBC database in a single object.
CONNECTION CON;
ResultSet Results;
ResultSetMetadata RSMD;
Databasemetadata DMA; String Catalog;
String Types [];
Public Database (String Driver)
{
Types = new string [1];
Types [0] = "Tables"; // Initialization Type
Try {class.forname (driver);} // load JDBC-ODBC bridge driver
Catch (Exception E)
{System.out.println (E.getMessage ());
}
//
Public Void Open (String Url, String Cat)
{
Catalog = cat;
Try {con = DriverManager.getConnection (URL);
DMA = con.getMetadata (); // Get metadata
}
Catch (Exception E)
{System.out.println (E.getMessage ());
}
//
Public string [] gettablenames ()
{
String [] tbnames = null;
Vector tname = new vector ();
// Add a table name to a vector,
// Because we don't know how many tables
Try {
Results =
New ResultSet (Dma.gettables (Catalog, Null,
"%", TYPES);
While (results.hasmorelements ())
TName.AddeElement (Results.getColumnValue ("Table_Name");
}
Catch (Exception E) {system.out.println (e);
/ / Copy the table name to a string array
TBNAMES = new string [tname.size ()];
For (int i = 0; i TBNAMES [I] = (String) TName.Elementat (i); Return TBNames; } // Public string [] getTableMetadata () { // Return to the table type information Results = NULL; Try { Results = New ResultSet (Dma.gettables (Catalog, Null, "%", TYPES); } Catch (Exception E) {System.out.println (E.getMessage ()); Return results.getMetAdata (); } // Public string [] getColumnMetadata (String Tablename) { // Return to a column data Results = NULL; Try { Results = New ResultSet (Dma.getColumn (Catalog, NULL, TableName, NULL); } Catch (Exception E) {System.out.println (E.getMessage ()); Return results.getMetAdata (); } // Public string [] getColumnNames (String Table) { / / Return a column name String [] tbnames = null; Vector tname = new vector (); Try { Results = new results (Dma.getColumns (Catalog, Null, Table, NULL); While (results.hasmorelements ()) TName.AddeElement (Results.getColumnValue); } Catch (Exception E) {system.out.println (e); TBNAMES = new string [tname.size ()]; For (int i = 0; i TBNAMES [I] = (String) TName.Elementat (i); Return TBNames; } // Public String getColumnValue (String Table, String columnname) { // Returns the value of the given column String res = null; Try { IF (Table.length ()> 0) Results = Execute ("SELECT" ColumnName "from" Table "Order by" columnname); IF (Results.haASMoreElements ()) Res = results.getColumnValue (ColumnName); } Catch (Exception E) {System.out.println ("Column Value Error" ColumnName E.getMessage ()); Return res; } // Public String getNextValue (String ColumnName) { // use stored ResultSet / / Return to the next value of the column String res = ""; Try { IF (Results.haASMoreElements ()) Res = results.getColumnValue (ColumnName); } Catch (Exception E) {System.out.println ("Next Value Error" ColumnName E.getMessage ()); Return res; } // Public ResultSet Execute (String SQL) { / / Execute a SQL query on this database Results = NULL; Try { Statement Stmt = con.createstatement (); Results = new resultSet (Stmt.executeQuery (SQL)); } Catch (Exception E) {System.out.println ("Execute Error" E.getMessage ()); Return Results; } } A visual database program In order to summarize the content of our learning, we write a simple GUI program that can display the table name, column name, and column content of the database. We will also include a text area where you can type a SQL query to be performed on the database. In the / chapter20 subdirectory on the Companion CD-ROM, you can find the ResultSet and Database classes used in this program (called dbframe.java). The display interface of the program is shown in Figure 3. Figure 3: DBFrame.java program used to display data in a database connected with JDBC. In this program, the table name of the default database (grocery ".mdb) is displayed in the column on the left. When you click on one of the table names, the column name is displayed in the middle of the column. Finally, when you click on a row in the middle column, the content of the line is displayed in the column on the right. The key to this program is to receive the list selection, then clear and populate the correct list box: Public Void ItemStateChanged (ItemEvent E) { Object obj = E.GETSource (); IF (Obj == Tables) // put in column name Showcolumns (); IF (Obj == Column) // Put the contents of the column SHOWDATA (); } // Private void loadingList (list list, string [] s) { / / Clear and populate the specified list box List.removeall (); For (int i = 0; i List.add (s [i]); } // Private void showcolumns () { // Display column name String cnames [] = DB.GetColumnNames (Tables.getSelectedItem ()); LoadList (Column, CNames); } // Private void showdata () { String colname = columns.getSelectedItem (); String colval = DB.GetColumnValue (Tables.getSelectedItem (), ColName); Data.setVisible (false); Data.RemoveAll (); Data.setVisible (TRUE); Colval = DB.GetNextValue (Column.getSelectedItem ()); While (colval.length ()> 0) { Data.Add (Colval); Colval = DB.GetNextValue (Column.getSelectedItem ()); } } Execute query Display the text area at the bottom of the screen allows you to type any SQL query you want. One query built in the demo program is as follows: String querytext = "SELECT DISTINCTROW FOODNAME, STORENAME, PRICE" "From (Food Inner Join Foodprice ON" "Food.foodkey = foodprice.foodkey)" "Inner Join Stores on" "FoodPrice.StoreKey = Stores.StoreKey" "WHERE ((Food.FoodName) = / 'ORANGES /'))" "Order by foodprice.price; This query simply lists the orange prices of each grocery store. When you click the Run Query button, it will perform this query and transfer the ResultSet object to a dialog for display: Public Void ActionPerformed (ActionEvent E) { Object obj = E.GETSource (); IF (Obj == quit) System.exit (0); IF (Obj == Search) ClickedSearch (); } // Private void clickedSearch () { ResultSet RS = db.execute (query.getText ()); String cnames [] = rs.getMetadata (); QueryDialog Q = New QueryDialog (this, RS); Q.Show (); } Query Results Dialog The Query dialog gave the ResultSet object and put each line in a string array, then put these String arrays in a vector so that you can quickly access these rows during the Paint () subroutine. Private void maketables () { // put each line into a string array and will // These strings are all in one vector. TABLES = New Vector (); String t [] = result.getMetadata (); Tables.addelement (t); While (results.hasmorelements ()) Tables.addelement (results.nextelement ()); } We draw data in a panel through Graphics's DrawString () method. Just like in the Printer object, we must track the location of X and Y yourself. Public void Paint (Graphics G) { String s. INT x = 0; / / Calculate the height of the font INT Y = g.getfontmetrics (). getHeight (); // Estimated column height INT DelTax = (int) 1.5F * (g.GetFontMetrics (). StringWidth ("wwwwwwwwwww"))); // Traversing table vector For (int i = 0; i { s = (String []) Tables.Elementat (i); / / Draw a row in the string array For (int J = 0; j { String st = s [j]; g.drawstring (ST, X, Y); X = deltax; // Move to the next column } X = 0; // Start a new line Y = g.getfontmetrics (). GetHeight (); // Extra space between column tags and column data IF (i == 0) y = g.getFontMetrics (). getHeight (); } } The QueryDialog of the built-in query is shown in Figure 4. Figure 4: QueryDialog displayed in the dbframe program, which shows the result of the default query. Sample file Groceries.zipdbframe.zip JDBC-ODBC Bridge summary In this article, we discussed the database and the verification of the database and performing a query on the database. We have seen that JDBC provides an object-oriented way to the platform and database to access these data, we also learned the main objects of JDBC: ResultSet, ResultSetMetadata and DatabaseMetadata. After writing a simple program with these objects, we have designed a higher level of RESULTSET and DATABASE objects, and we use them to build a simple visual interface to display database information. If you are familiar with the powerful features of the database, you will recognize that the SQL language allows you to perform more powerful tasks than we described here. For example, you can create a new table, add, change, or delete rows, columns, or individual tables of the table. With JDBC, all all this becomes universal and easy to handle. If you are using a specific platform's database driver, such as JDBC-ODBC Bridge, you will be restricted when writing applications, because Applets cannot be connected to this bridge running on another computer. Other client-server databases, such as the IB2 DB2, allows you to connect to JDBC in the applet.
