This article analyzes
Java Database Connectivity (JDBC)
One
Java
The technique of connecting the database in an object-oriented approach. It is right
ODBC API
A object-oriented package and redesign, 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
Some quite low-layer methods are provided to access the database while also providing a powerful object to the fairly high layer to process the database.
What is a database?
database
It 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.00 Smyth $ 60,000 $ 75,000.00 Tully $ 30,000.00 $ 45,000.00 Wolff $ 45,000.00 $ 60,000.00
Or, sorted in the order of salary
Name
MIN
Max
Tully $ 30,000.
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 program connection database is actually 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 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
1 apples 2 Oranges 3 Hamburger 4 Butter 5 Milk 6 Cola 7 Green Beans
The grocery store is as follows:
StoreKey
Storename
1 Stop and Shop 2 Village Market 3 Waldbaum's
The grocery store pricing table is only composed of key values and prices in the three forms:
Fskey
StoreKey
FoodKey
PRICE
1 1 $ 0.27 2 1 $ 0.29 3 3 1 $ 0.33 4 1 2 $ 0.36 5 2 $ 0.29 6 3 2 $ 0.47 7 1 3 $ 1.98 8 2 $ 2.45 9 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
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
"
begin
"
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 returns.
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 it
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
begin.
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); returns the contents 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); returns the content of the column name String as a FLOAT type. GetDate (int); Returns the content of the column of the number INT as the date. Getdate (string); returns the content of the column named String. Next (); move the row pointer to the next line. Returns false if there is no residual line. Close (); Close 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 name INT. GetColumnLabel (int); returns this column containing labels. IScurrency (int); Returns true if this column contains a number with currency units. IsReadonly (int); Returns true if this is read-only. ISAUTOINCREMENT; if this column is automatically incremented, TRUE is returned. This type of column is usually key and is always read-only. GetColumnType (int); Returns this column SQL data type. These data types include
Bigint Binary Bit Char Date Decal Double Float Integer Longvarbinary Longvarchar NUMERIC OTHER REAL SMALLINT TIME TIMESTAMP TINYINT VARBINARY VARCHAR DATABASEMETADATABASEMETADATA object 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. Since different databases support different SQL variants, there are also multiple methods to query the database to support which SQL methods. getCatalogs () Returns the information directory list 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 URL name 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 the 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's database "scheme" is 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 is a mask 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 from the database is equivalent to obtaining the DatabaseMetadata object and retrieves the table name: 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 described earnes, all code is included in the TRY block. Executing 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, you can 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");} Please 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. Printing 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, 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 the 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
COLAXX
