Use JDBC to create a database access program

What is a database?

A database stores a series of information tables in a certain file structure. This file structure allows you to access these tables, select columns in the tables, sort the tables, and select rows according to various criteria. Databases usually have multiple indexes associated with many columns in these tables, so we can access these tables as quickly as possible.

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

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

Key lastname salarytype 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 references the second table. We can imagine a number of such tables, such as tables used to store the tax value of the residential city and each city, and the amount deducted from the health plan. Each table has a primary key column (such as the leftmost column in the two tables above) and several data columns. Creating a table in a database is both an art and a science. The structure of these tables is pointed out by their paradigm. Generally, a table belongs to 1nf, 2nf, or 3nf.

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

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

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

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

Obtain data from the database

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

Name min max
Tully $30,000.00 $45,000.00
Johnson $30,000.00 $45,000.00
Wolff $45,000.00 $60,000.00
Adams $45,000.00 $60,000.00
Smyth $60,000.00 $75,000.00

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

Select distinctrow employees. Name, salaryranges. Min,
Salaryranges. Max from employees inner join salaryranges on employees. salarykey = salaryranges. salarykey
Order by salaryranges. min;

This language is called a structured query language (SQL). It is a language that can be used by almost all databases. SQL-92 standards are considered a fundamental standard and have been updated multiple times.

Database Type

Databases on PC, such as dBase, Borland paradox, Microsoft Access, and Foxbase.

Database servers: ibm db/2, Microsoft SQL Server, Oracle, Sybase, sqlbase, and XDB.

All these database products support a variety of relatively similar SQL dialects, so all databases initially seem to be interchangeable. Each database has different performance characteristics, and each has different user interfaces and programming interfaces.

ODBC

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

What is JDBC?

JDBC is an object-oriented encapsulation and re-design of ODBC APIs. It is easy to learn and use and enables you to write code that does not depend on the vendor, used to query and manipulate databases. Although it is object-oriented like all Java APIs, it is not a very high-level object set.

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

Install and use JDBC

JDBC classes are included in the Java. SQL package and will be automatically installed when Java JDK 1.4 is installed. However, if you want to use the JDBC-ODBC bridge. JDBC-ODBC drivers can be easily found and downloaded from Sun's Java website (http://java.sun.com. After you expand and install the driver, you must perform the following steps:

Add the/jdbc-ODBC/classes path to your path environment variable.

Add the/jdbc-ODBC/classes; path to your classpath environment variable.

JDBC driver type

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

1. JDBC-ODBC bridge and ODBC drivers -- in this way, this is a local solution because ODBC drivers and Bridge Code must appear on each machine of the user. Basically, this is a temporary solution.

2. Local Code and Java driver-It replaces ODBC and JDBC-ODBC Bridge with another local solution (Java-callable local code on the platform.

3. The pure Java driver of the JDBC network-the JDBC translated by the Java driver forms an independent protocol sent to the server. Then, the server can connect to any number of databases. This method allows you to call the server from the client Applet and return the result to your applet. In this case, the middleware software provider can provide servers.

4. The local protocol Java driver-the Java driver directly converts the protocol to the database and calls it. This method can also be used through the network, and the results can be displayed in the Web browser applet. In this case, each database vendor will provide drivers.

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

Two-layer model and three-layer model

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

When an application or applet calls the server and the server calls the database again, we call it a three-tier model. This is often the case when you call a program called a "server.

Write JDBC code to access the database

Use ODBC to register your database

Connect to database

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

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

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

String url = "JDBC: ODBC: northwind ";

Connection con = drivermanager. getconnection (URL );

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

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

JDBC: subprotocol: subname

The first group of characters represents the connection protocol and is always JDBC. There may also be a sub-Protocol, where the sub-protocol is specified as ODBC. It specifies the connectivity mechanism of a class of databases. If you want to connect to the database server on another machine, you may also need to specify the machine and a subdirectory:

JDBC: Bark // doggie/Elliott

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

JDBC: Bark // doggie/Elliot; uid = gooddog; Pwd = Woof

MSSQL Server access method: (driver needs: msutil. jar, msbase. jar, MSSQLServer. Jar)

Dbdriver = com. Microsoft. JDBC. sqlserver. sqlserverdriver
Url = JDBC: Microsoft: sqlserver: // localhost: 1433; databasename = demo
Username = sa
Password =
Maxcon = 10
Mincon = 1
Poolname = skydev

Use the developed database class as follows:

Dbobject DBO = new dbobject (New sqlserverconnectionfactory ("localhost ",
1433, "Demo", "sa ",""));
Connection con = DBO. getconnection ();
// Class code (excluding connection factory implementation)
Package skydev. modules. Data;

Public final class sqlserverconnectionfactory
Extends connectionfactory {
Private final string dbdriver =
"Com. Microsoft. JDBC. sqlserver. sqlserverdriver ";
Private string host;
Private int port;
Private string databasename;

Public sqlserverconnectionfactory (){
Super. setdrivername (dbdriver );
}

/**
*
* @ Param host the Host Name of the database, for example, "localhost"
* @ Param port the port number running on the SQL server. If the default value is 1433, enter a negative number.
* @ Param databasename Database Name
* @ Param Username: Username
* @ Param Password
*/

Public sqlserverconnectionfactory (string host,
Int port,
String databasename,
String username,
String password ){
This. sethost (host );
This. setport (port );
This. setdatabasename (databasename );
This. setusername (username );
This. setpassword (password );

Init ();
}

Private void Init (){
Super. setdrivername (dbdriver );
Super. seturl ("JDBC: Microsoft: sqlserver: //" + host. Trim () + ":" +
New INTEGER (port). tostring () + "; databasename =" +
Databasename. Trim ());
// Super. seturl ("JDBC: Microsoft: sqlserver: // localhost: 1433; databasename = Demo ");
}
......

//-----------------------------------------

How to access MYSQL:

Dbdriver = com. MySQL. JDBC. Driver
Url = JDBC: mysql: // localhost/demo
Username =
Password =
Maxcon = 5
Mincon = 1
Poolname = zhengmao

Access Database

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

Databasemetadata information about the entire database: Table Name, table index, database product name and version, database-supported operations.

Resultset information about a table or query result. You must access data rows row by row, but you can access columns in any order.

Resultsetmetadata: information about the names and types of columns in the resultset.

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

Resultset

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

// Obtain the number of columns from the metadata
Resultsetmetadata rsmd;
Rsmd = results. getmetadata ();
Numcols = rsmd. getcolumncount ();

When you obtain a resultset, it points to the position before the first row. You can use the next () method to obtain each other row. If there are no more rows, this method returns false. Retrieving data from the database may cause errors. You must always include the result set processing statement in a try block.

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

Getint (INT); the content of the column with the serial number of Int Is returned as an integer.

Getint (string); returns the content of the column named string as an integer.

Getfloat (INT); the content of the column with the serial number of Int Is returned as a float number.

Getfloat (string); the content of the column named string is returned as a float number.

Getdate (INT); returns the content of the column whose serial number is int as the date.

Getdate (string); returns the content of a column named string as a date.

Next (); move the row pointer to the next row. If no remaining row exists, false is returned.

Close (); close the result set.

Getmetadata (); returns the resultsetmetadata object.

Resultsetmetadata

You can use the getmetadata () method to obtain the resultsetmetadata object from the resultset. You can use this object to obtain the number and type of columns and the name of each column.

Getcolumncount (); returns the number of columns in the resultset.

Getcolumnname (INT); returns the column name whose serial number is int.

Getcolumnlabel (INT); returns the label implied in this column.

Iscurrency (INT); returns true if this column contains a number with a currency unit.

Isreadonly (INT); if this column is read-only, true is returned.

Isautoincrement (INT); returns true if this column is auto-incrementing. This type of column is usually a key and is always read-only.

Getcolumntype (INT); returns the SQL data type of this column. These data types include

Bigint
Binary
Bit
Char

Date
Decimal
Double
Float
Integer
Longvarbinary
Longvarchar
Null
Numeric
Other
Real
Smallint
Time
Timestamp
Tinyint
Varbinary
Varchar
 
Databasemetadata

The databasemetadata object provides you with information about the entire database. You can use it to obtain the name of the table in the database and the name of the column in the table. Because different databases support different SQL variants, there are also multiple ways to query which SQL methods are supported by the database.
 
Getcatalogs () returns the list of information directories in the database. With the JDBC-ODBC bridge driver, you get a list of databases registered with ODBC. This is rarely used in JDBC-ODBC databases.

Gettables (catalog, schema, tablenames, columnnames) returns the description of all tables whose names match tablenames and whose names match columnnames.
Getcolumns (catalog, schema,
Tablenames, columnnames) returns the description of all table columns whose names match tablenames and whose names match columnnames.
Geturl (); get the name of the URL you are connected.

Getdrivername (); get the name of the database driver you are connected.

Obtain table information

You can use the gettables () method of databasemetadata to obtain information about tables in the database. This method has the following four string parameters:
Results = DMA. gettables (catalog, schema, tablemask, types []);

The parameter indicates:

Catalog: Find the Directory Name of the table name. For JDBC-ODBC databases and many other databases, you can set it to null. The directory items of these databases are actually the absolute path names in the file system.

The Database "solution" to be included in the schema ". Many databases do not support the scheme. For other databases, it represents the username of the database owner. Generally, it is set to null.

Tablemask is a mask used to describe the name of the table to be retrieved. If you want to retrieve all table names, set them to wildcard %. Note that the wildcard character in SQL is "%" rather than "*" of a general PC user.

Types [] This is a string array that describes the type of the table to be retrieved. A database usually contains many tables for internal processing, which has little value to you as a user. If it is null, you will get all these tables. If you set it to a single-element array containing the string "tables", you will only obtain tables that are useful to users.

A simple JDBC Program

We have learned all the basic functions of JDBC. Now we can write a simple program to open the database and print its table name and the content of a certain table column, then, query the database. This program is as follows:

Package skydevkit;
Import java. SQL .*;
Public class jdbcodbc_test {
Resultset results;
Resultsetmetadata rsmd;
Databasemetadata DMA;
Connection con;

Public jdbcodbc_test () throws sqlexception {
String url = "JDBC: ODBC: northwind ";
Try {
// Load JDBC-ODBC bridge driver
Class. forname ("Sun. JDBC. ODBC. jdbcodbcdriver ");
Con = drivermanager. getconnection (URL); // connect to the database
DMA = con. getmetadata (); // get the metadata of the database
System. Out. println ("connected to:" + DMA. geturl ());
System. Out. println ("driver" + DMA. getdrivername ());
} Catch (exception e ){
System. Out. println (E );
}
Try {
Statement stmt = con. createstatement ();

Results = stmt.exe cutequery ("select * from customer ;");
Resultsetmetadata resultmetadata = results. getmetadata ();
Int Cols = resultmetadata. getcolumncount ();
String resultrow = "";
For (INT I = 1; I <Cols; I ++ ){
Resultrow + = resultmetadata. getcolumnname (I) + ";";
}
System. Out. println (resultrow );
While (results. Next ()){
Resultrow = "";
For (INT I = 1; I <Cols; I ++ ){
Try {
Resultrow + = results. getstring (I) + ";";
} Catch (nullpointerexception e ){
System. Out. println (E. getmessage ());
}
}
System. Out. println (resultrow );
}
} Catch (exception e ){
System. Out. println ("query exception ");
} Finally {
Results. Close ();
}
}
}

Example of calling the SQL Server Stored Procedure: (use the database connection class we developed)

Create procedure [DBO]. [sp_getstudentbyname] (@ name char (10 ))
As
Select * from students where [name] = @ name
Go

Dbobject DBO = new dbobject (New sqlserverconnectionfactory ("localhost ",
1433, "Demo", "sa ",""));
Connection con = DBO. getconnection ();
Callablestatement pstmt = NULL;
System. Out. println ("testdb1 ()............");
/* Try {
Pstmt = con. preparecall ("{call sp_getstudentbyid (?)} ");
Pstmt. setint (1, 1 );
}*/
Try {
Pstmt = con. preparecall ("{call sp_getstudentbyname (?)} "); // Pay attention to how parameters are transmitted
Pstmt. setstring (1, "Tom ");
}
......
Use output parameters:

Create procedure [DBO]. [sp_insertstudent] (@ name char (10), @ age int, @ ID int output)
Insert into students ([name], [age]) values (@ name, @ age)
Select @ ID = @ identity
Go

Try {
Pstmt = con. preparecall ("{call sp_insertstudent (?,?,?)} ");
Pstmt. setstring (1, "zengqingsong ");
Pstmt. setint (2, 22 );

Pstmt. registeroutparameter (3, types. integer );
Pstmt.exe cuteupdate ();

Int id = pstmt. getint (3 );
System. Out. println (ID );
}
Example of using return parameters:

Create procedure [DBO]. [sp_insertstudent] (@ name char (10), @ age int, @ ID int output)
Insert into students ([name], [age]) values (@ name, @ age)
Select @ ID = @ identity-test output parameters
Return 30-test returns 30
Go

Try {
Pstmt = con. preparecall ("{? = Call sp_insertstudent (?,?,?)} ");
Pstmt. setstring (2, "zengqingsong ");
Pstmt. setint (3, 22 );

Pstmt. registeroutparameter (4, types. integer );
Pstmt. registeroutparameter (1, types. integer );
Int ret = pstmt.exe cuteupdate (); // number of rows affected by execution

Int ret2 = pstmt. getint (1); // return parameter (output parameter)
Int id = pstmt. getint (4); // output parameter
System. Out. println (RET );
System. Out. println (ret2 );
System. Out. println (ID );
}