Create database objects using JDBC 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 structured query language, namely
SQL
(General reading
"sequel"
), And it is a language that almost all current databases can be used. This has been promulgated in these years.
SQL
Standard, and most
PC
Database supports most
ANSI
standard.
SQL-92
Standards are considered to be a basic standard and have been updated multiple times. However, no database can perfectly support later
SQL
Version, and most databases provide a variety of
SQL
Extend to support their database alone.
Type of database
due to
PC
It has become the main office tool, so it has been developed
PC
A large amount of popular databases, these databases can be managed. They include primary databases, such as
Microsoft Works
Also include more complex databases, such as
Approach
,
DBASE
,
Borland Paradox
,
Microsoft Access
with
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, once represented
"Java Database Connectivity"
But now it itself has become a logo symbol. It is right
ODBC API
An object-oriented package and redesign, 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 with all
Java API
Same, it is object-oriented, but it is not a high-level object set, in this chapter, we will propose a higher level 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 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
The class is returned to
Java.sql
In the package, installation
Java JDK 1.1
Or later, it will be installed automatically. However, if you want to use
JDBC-ODBC
The bridge must also be installed two additional packages. First, if you use
WINDOWS 95
You must put your
ODBC
The driver is upgraded to
32
Bit driver, you can
Microsoft
Website download. This driver is
Microsoft
It is difficult to find on the 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
Java
The method of the program connection database actually has four: JDBC-ODBC Bridges 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 a code to process
PC
Client database, such as
DBASE
,
Foxbase
or
ACCESS
You may use the first method and have all the code on the user machine. Larger client
-
Server database products (such as
IBM
of
DB2
)
3
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. in
JDBC-ODBC
This is usually this in the 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 watching how to write
Java
The program is to access the database. The database we have to use is a
Groceries.mdb
of
Microsoft Access
database. 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
in
WINDOWS 95
or
NT
Environmental visit
ODBC
Before the database, you must use the control panel.
ODBC
The driver registered it. in
WINDOWS 95
In the environment, it is
"
control panel
"
Program
ODBC
icon. in
Windows NT
In the environment, you will
"
Start
"
This program is found in the menu. (If you can't find it, you need to install the above.
ODBC
Driver, 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
Java.sql
In the package, there must be joined in a program that uses JDBC.
"Import java.sql. *"
.
JDBC
Have to connect
ODBC
Database, you must first load
JDBC-ODBC
Bridge driver
Class.Forname ("Sun.jdbc.odbc.jdbcodbcdriver");
This statement loads the driver and creates an instance of the class. Then, you have to connect a specific database, you must create
Connect
An example of a class, and use
URL
Syntax connection database.
String Url = "JDBC: ODBC: Grocery Price";
Connection con = DriverManager.getConnection (URL);
Note that the database you are using is yours
ODBC
Set up in the panel
"
data source
"
name.
The URL syntax may vary greatly depending on the database type.
JDBC: Subprotocol: SUBNAME
The first set of characters represent the connection protocol and always
JDBC
. There may also have a sub-protocol, here, 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 the name and content of the table column, and you can run
SQL
The statement querys the database or adds or modifies 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
Resultset
Object is
JDBC
The most important individual objects in. In essence, it is an abstraction of a table for a general width and unknown length. Almost all methods and queries use data as
Resultset
return.
Resultset
Contains any number of naming columns, you can access these columns by name. It also contains one or more rows, you can access it from top to top by sequence. Use RESULTSET
Before, you must query how many columns are included. This information is stored
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 ());
Get a variety of forms
Resultset
Data in this depending on the data type stored in each column. Also, you can get the contents of the column by column or column name. Please note that the column number
1
Start, not from
0
Start.
Resultset
Some of the most common methods of objects 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
GetMetadata ()
Approach
Resultset
Acquired
ResultSetmetadata
Object. 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 the SQL data type of this column. These data types include
BIGINTBINARYBITCHARDATEDECIMALDOUBLEFLOATINTEGERLONGVARBINARYLONGVARCHARNULLNUMERICOTHERREALSMALLINTTIMETIMESTAMPTINYINTVARBINARYVARCHARDatabaseMetaData
DatabaseMetadata
Objects can provide you with information on the entire database. You mainly use it to get the name of the table in the database, and the names listed in the table. Different databases support different databases
SQL
Variation, there is also a variety of ways to query the database to support which
SQL
method.
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 it
DatabaseMetadata
of
GetTables ()
Methods to get information on the table in the database. This method is as follows
4
A
String
parameter:
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 of the code is included
Try
Block.
Execute SQL query
We have already understood
JDBC
Basic objects, can now be implemented
SQL
Query it. The query is as
Statement
You can easily
Connection
Object acquisition
Statement
Object:
String Query = "SELECT FOODNAME" "
ResultSet Results;
Try
{
Statement Stmt = con.createstatement ();
Results = stmt.executequery (query);
}
Catch (Exception E)
{System.out.println ("Query Exception");
Please note that this simple query returns
Food
Table
Foodname
Column. You use simple queries like this to get the contents of the entire column. Please note that the query itself is one
Resultset
You can use the methods you just discussed above.
Print ResultSet
Because we always have to
Resultsets
Print data, we can design a simple way, will be the whole
Resultset
Dump, 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 already learned
JDBC
All basic functions, now we can write a simple program that opens the database, prints its table name, and the content of a list, then execute the query on 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 encapsulate some of the methods we use in several higher level objects, it will be very helpful, we can not only pack
Try
Blocks, and can be accessed more simply
Resultset
method.
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 ());
}
}
By using simple use
New
Operator creates one on site
Resultset
Object, we can easily
Resultset
Object package 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);
And it is easy to
JDBC
This object is used in the program.
Build a Database object
We along
00
Another part of the chain moved to the top of the effort is to create a
Database
Object, it will package the behavior of the following objects:
Connection
,
Statement
with
DatabaseMetadata
Object, and we have just built
SQL
Query and
Resultset
. our
Database
Object allows us to create a connection, get a table name, move in the database, and more simply obtain the values of rows and columns. caution,
EXECUTE
Method returns one
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 ResultSet (Dma.getColumn (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, it can display the table name, column name, and column content of the database. We will also include a text area where you can type it in it. SQL Inquire. in Companion CD-ROM Up / chapter20 In the subdirectory, you can find this program (called dbframe.java )Used Resultset with Database class. The display interface of the program is shown 3 Indicated. 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 anything SQL Inquire. 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 Query dialog Resultset Object, put each line into one String In arrays, then these String Array put into one Vector In this way Paint () These rows are quickly accessed during the subroutine run. 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 pass Graphics of Drawstring () Method Draw data in one Panel in. Just like PRINTER Like the object, we must track yourself x with y s position. 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 (); } } Built-in query QueryDialog Figure 4 Indicated. 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, JDBC We have also learned a kind of data-oriented method that is unrelated to platform and database. We have also learned JDBC The main object: Resultset , ResultSetMetadata and DatabaseMetadata. After writing a simple program with these objects, we have designed a higher level Resultset with Database Objects, we have built 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. To learn more about Java and database connectivity on this website, please visit: http://www.cnet.com/content/builder/programming/databases/programming/databases/?st.bl.fd.prog1.feat
