Eventh talker JDBC programming
JDBC (Java Data Base Connectivity) is a Java language to support SQL functionality and a database-associated user interface. The JDBC includes a set of interfaces and classes written by Java language, which are independent of DBMS, or It is said that they can be associated with various data. After having JDBC, programmers can easily use SQL languages in the Java language, so that Java applications or Java Applets can access access to various relational databases distributed on the network. After using JDBC, programmers can focus on the upper layer of functionality without having to care about the connection and access process of the underlying and specific DBMS.
11.1 Relationship Database Introduction
The idea of relational database system is taken first by IBM's E.f.CODD in 1970.
The data model supported by the relational database is a relational model. The basic condition of the relationship model is that each attribute value in its relationship mode must be an inextricable amount of data. Simply put, the data structure in the relational database is typically a two-dimensional table. The value of each of the two-dimensional tables must be an indivisible data item, and the two lines should be different.
The main features of the relationship model are:
(1) Simple structure, strong sense, easy to master.
(2) Language integration, non-process operation, convenient for users.
(3) A certain mathematical theory is based on further research and development.
When the operation is time consumption, the execution efficiency is low.
Relational database management system generally provides users with data retrieval, data insertion, data deletion, and data modification of four basic operations.
11.2 JDBC Overview
JDBC consists of a class and interface written in a set of Java languages, using embedded SQL, mainly implementing three functions: establish a connection with the database, execute SQL declaration, and processing SQL execution results. JDBC supports basic SQL features that use it easy to establish a connection with different relational databases, do not need to write programs for different DBMs. Let's first introduce the relationship between JDBC and SQL and ODBC, and then introduce two models supported by JDBC, and finally describe the Abstract Interface and Database Driver Driver of JDBC.
11.2.1 JDBC and SQL
Although the general database system supports SQL semantic syntax in a wide range, they often do not agree on complex high-level functions such as storage function calls and external connections. In order to solve this contradiction, JDBC uses several measures:
(1) JDBC allows any interrogation statement from a system that belongs to DBMS, which will be transmitted to the DBMS actual execution of the background. This increases the flexibility of use, an inquiry of an application may not be in the form of SQL, but a specific lead form of SQL, such as file or picture query provided for a particular DBMS. The risk of doing this will have an error in some DBMs.
(2) The function of ANSI SQL Entry Level is generally considered to be complete and is widely supported. Therefore, in order to match the JDBC with SQL, the user is required to use at least ANSI SQL 92 Entry Level or more, so that the extensive portability application provides a joint naming guarantee.
(3) For more complex applications, JDBC provides interface class DatabaseMetadata, and the user can obtain the description of the corresponding DBMS through this class, and then take a specific query mode according to the resulting information.
11.2.2 JDBC and ODBC
Microsoft's ODBC (Open Database Connectivity is the most commonly used interface with the relationship database. JDBC is based on ODBC, which is actually deemed to be an ODBD Java language translation form. Of course, both are based on abstract definitions of X / Open SQL CLI (Call Level Interface). Compared with ODBC, JDBC is more convenient.
Since ODBC is already a generic interface, we can use ODBC directly in Java programs to establish JDBC interfaces, why the reason and benefits include the following: (1) ODBC uses the C language interface, and direct from Java Calling C Source Codes is easy to have problems in security, robustness, and portability, and operational efficacy is also affected.
(2) Translate ODBC's C language API is not ideal for Java. For example, Java has no pointer, an ODBC's object-oriented translation interface, which is more natural and convenient for Java's programmers.
(3) ODBC is difficult to learn and master, often use simple features with complex characteristics. And JDBC is relatively simple and clear, it is easy to understand.
(4) JDBC helps to achieve "pure Java" program. When using ODBC, each client requires the ODBC drive and manager. And when using JDBC, the driver is written in Java language, the JDBC code can be automatically loaded, transplanted on all Java platforms, and is safe.
Of course, ODBC can also be used in JDBC, but it is to be used by intermediary JDBC-ODBC Bridge.
11.2.3 JDBC support two models
In the connection operation with the database, JDBC supports two different models. These two Sichuan models are called two models and three-layer models, respectively, according to the relationship between the user and the database.
In the two-layer model, Java's application (Applet or Application) is directly connected to the database. The user's SQL declaration is submitted to the database, and the result is sent to the user, as shown below. This model has a client / server structure, and the user's machine is like a client, and the database is stored, the server is like a server. It can be a local area network or a wide area network.
┌────── ┐ │Java Application │ │ java applet │ Clinet Machine ├───────── ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─────── ─ ┐ │ DBMS │ Database Serve └───────────── ─ ─ ─ Figure 11.1 JDBC support two-layer model
In the three-layer model, the user does not contact the database directly. The user's command is first sent to a so-called "intermediate layer", and the intermediate layer is sent to DMBS again. The results of the execution are also transferred by the intermediate layer, as shown in Figure 11.2. The advantage of the three-layer model is that the control of the access rights and public data allows the type of access to access rights and public data can be kept by the intermediate layer. At the same time, users can use a more friendly high-rise API, converted from the intermediate layer to the appropriate low-level command to ensure better operation. So far, the intermediate layer is written in both C or C languages. With the gradual promotion of Java language, the intermediate layer written in Java will have characteristics of its robustness, multi-thread, safety.
┌─────── ┐ │ java applet │ │ html browse │ └ --────────────────────── ─ ─ │application Server │ │ (Java) │ ├─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────── ─ ─ ─ ─ ─ ───────────── ┘┘ Figure 11.2 JDBC support three-layer model
11.2.4 JDBC abstract interface
The most important part of JDBC is to define a series of abstract interfaces. Through these interfaces, JDBC implements three basic functions: establishing connection with data, executing SQL declarations and processing execution results.
These interfaces exist in the Java SQL package, their names and basic functions are:
* java.sql.drivermagnager
Manage the drive to support the creation of the drive and data connection.
* java.sql.connection
Represents a connection to a database, supports the creation of SQL declarations.
* java.sql.statement
Perform a static SQL declaration in the connection and achieve the results.
* java.sql.preparedStatement
Statement's subclasses represent the pre-compiled SQL declaration.
* java.sql.callablestatement
Subclass of Statement represents the stored procedure of SQL.
Java.sql.resultset
The data result generated after the SQL declaration is executed.
11.2.5 JDBC's database driver Driver
Java's application operates the database through a series of abstract classes defined in the SQL package, implementing these abstract classes, actually completing the operation, running by the database driver Driver. The relationship between them is shown in Figure 11.3
┌─────────┐│Java Application │└─────────┘ JDBC API -------- ┌─────────┐│JDBC Manager │ └─────────── - - - - - ─ ──────────────────────── ┌ --───────-┐DJBC │JDBC-NET ││JDBC-ODBC | │NATIVE-API│Native-Protocol |
Drivers│ Driver ││Bridge Driver││ Driver │ | Driver | └ - ──────────────────────────────────────────────────────────────────────────────────────────────────── ─ ↓ ↓ ↓ ↓ ↓ ┌ --───────────────────────────────────────────────────────────────────────────────────── << ───────────────────── ─ ─┘ Figure 11.3 JDBC Drivers
JDBC's Driver can be divided into the following four types:
(1) JDBC-ODBC Bridge and ODBC DRIVER
Such a drive provides a database connection via an ODBC drive. Using this drive requires every client to load an ODBC drive.
(2) Native-API Partly-Java Driver
This drive converts the JDBC instruction into the operational form of the connected DBMS. The database used by each client may be Oracle, which may be Sybase, or Access, you need to install the corresponding DBMS driver on the client.
(3) JDBC-NET All-Java Driver
Such a drive converts the JDBC instruction into the form of a network protocol independent of DBMS, and then converts the server into a specific DBMS protocol form. The agreement on DBMS is determined by various database vendors. This drive can be coupled to different databases, most flexible. Some vendors have begun to add this driver of JDBC to their existing database intermediary products. It should be noted that in order to support wide area network access, it is necessary to add measures related to security, such as firewalls, etc.
(4) Native-Protocol All-Java Driver
Such a drive converts the JDBC instruction into a network protocol, which is no longer converted, directly used by DBMS. It is equivalent to contacting the server directly to the server directly.
In these four drives, the latter two types of "pure Java" drives are more efficient and more versatile. However, the first, second type of drive is easier to obtain, and the use is also more common. 16.3 JDBC Programming
11.3.1 Basic Structure of Program
The general JDBC program completes three functions: establish a connection with the database; transmitting the SQL declaration and processing the return result. Below we will illustrate the implementation process of these three functions through a specific example.
Example 11.1 Cream.java gives a simple JDBC program. After this program executes a table named TestTable, the table includes two domains, and the domain names are ID and NAME, respectively.
1: Import java.net.URL; 2: import java.sql. *; 3: 4: Class Create {5: public static void main (string [] args) {6: string url = "jdbc: odbc: demo" 7: String query = "Create Table TestTable" "(ID INT, Name Char (10))"; 8: 9: Try {10: // Download JDBC-ODBC Bridge Drive 11: Class.Forname ("Sun. JDBC.odbc.jdbcodbcdriver "); // Refer to this sentence to Cyclone's annotation // Connect to the drive, here you can use different specific drivers, such as JDBCFORORORORACLE or JDBCFORSQLSERVER14: Connection Con = DriverManager.getConnection (URL," User " "Password"); 15: 16: // Create a statement object 17: statement stmt = con.createstatement (); 18: 19: // Execute SQL declaration 20: stmt.executeUpdate (query); 21: System.out .println ("CREATE SUCCESSFULLLY!"); 22:23: // Turns Stm24: Stmt.close (); 25:26: // Close connection 27: con.close (); 28:} catch (sqlexception ex) { // SQL exception information 29: system.out.println ("/ n *** sqlexception caughn (" / n "); 30: while (ex! = Null) {31: system.out.println (" SQLState: " ex.getsqlstate ()); 32: System.out.println (" message: " ex.getMessage ()); 33: System.out.println (" vendor: " ex.GeterrorCode ()); 34 : EX = ex.getnextexception (); 35: system.out.println ("");} 36: } catch (java.lang.exception ex) {37: ex.printStackTrace (); 38:} 39:} 40:}
11.3.2 STATEMENT and its subclasses
1) Statement interface
STATEMENT Stmt = con.createstatement (); // Execute SQL Declaration INT Count1 = Stmt.executeUpdate ("INSERT INTO TestTTable (ID, Name) VALUES (1, 'WU')"); int count2 = stmt.executeUpdate ("Insert) INTO TESTTTABLE (ID, NAME) VALUES (2, 'WANG') "); 2) preparedStatement interface
String data [] = {{"5", "xu"}, {"6", "yan"}};
PreparedStatement PSTMT = Con.PrepareStatement ("INSERT INTO TESTTABLE (ID, NAME) VALUES (?,?)"); // Parameter assignment, execute SQL declaration for (int i = 0; i 3) CallableStatement interface CallableStatement Cstmt = Con.PrepareCall ("{Call Search (?)}"); // Parameter assignment CSTMT.setint (1,934678); // Execute the storage process. Cstmt.execute (); 11.3.3 Results ResultSet and ResultSetMetadata String query = "select * from testtable"; Statement Stmt = con.createstatement (); // Send a query request, get the result set ResultSet RS = Stmt.executeQuery (Query); // The result set Each row of all columns System.out.println ("The detail of testtable is:" ); ResultSetMetadata RSMD = rs.getMetadata (); // Get results collection column in numcols = rsmd.getColumnCount (); // Display column header for (INT i = 1; i <= numbers; i ) {IF (i > 1) System.out.print (","); system.out.print (RSMD.GetColumnLabel (i));} system.out.println ("); // Display result set information while (rs.next ()) {// Display a row for (int i = 1; i <= numcols; i ) {if (i> 1) system.out.print (","); system.out.print (rs.getstring i)); 11.3.4 DatabaseMetadata DatabaseMetadata DMA = con?GetMetadata (); // Drive and URL information system.out.println ("/ nConnected to" Dma.getURL ()); system.out.println ("driver" Dma.GetDriverName ()) System.out.println ("Version" DMA.GetDriverVersion ()); // Database Information System.out.Println ("/ NDatabase Name:" DMA.GetDatabaseProductName () Dma.GetDatabaseProductVersion (); system. Out.println ("Database Supports SQL Keywords: / N / T" Dma.getsqlKeywords ()); // Database Function Information Function Dma.supportsi92ENTRYLEVELSQL () Dma.supportsnsi92fullsql () Dma.supportsStoredProcedProcedures () 11.3.5 JDBC Data Types and Type Conversions First, the data type of JDBC In addition to the abstract interfaces related to the database connection and the DRIVERMANAGER, DRIVERPROPERTYINFO, the DRIVERMANAGER, DRIVERTYITYINFO, etc., which is used to represent the SQL types that may be used in the database. Let's take a brief introduction to them one by one. 1, SQL.DATE The date class Date in the SQL package is a subclass of the DATE class in the Util package, and is actually a subset of the Util.DATE class. It only treats the day and month, and ignores hours and seconds to represent the Date information of SQL. The DATE class constructor is: Public Date (int year, int mouth, int day) The parameter format is the same as the construction method of the Util.date class, the annual parameter minus the integer value obtained by the desired set of 1900, the month parameter is 0 to 11, and the day parameter is 1 to 31. Method calls for the creation date class from January 23, 1998: Date D = New Date (98, 0, 23); The DATE class also provides two methods that conversion with String classes, namely: Public Static Date Valueof (String S) Convert the string class parameters to dateclass. The format of the String class parameter S is "year-month-day", plus "1997-04-12". Public String Tostring () Convert the date class object to the String class object, which is also used in the "Year-Month - Day" format. 2, SQL.Time This class is a subclass of the Util.Date class, and it is also a subset of it. In the Time class, only hours and seconds are processed, representing the TIME type of SQL. It comes with SQL.Date to represent a complete util.date class information. The construction method of the TIME class is: Public Time (int Hour, int minute, int special) Among them, the hour parameter value is 0 to 23, and the differential number of second parameters is 0 to 59. Like SQL.Date, the TIME class also defines two functions Valueof and String that transforms with String classes. Different are the format of the String class object is "hour: score: second", such as "12:26:06". 3, SQL.TimeStamp This class is also a subclass of the Util.Date class, which adds nanosecond, 1 nanosecond, in addition to the annual month, hours and the second and information, and 1 nanoseconds. The TimeStamp class is used to represent the SQL Timestamp type information. The construction method of the TimeStamp class is: Public TimeStamp (int year, int mouth, int 4, int.com) where the value of nanosecond parameters ranges from 0 to 999, 999, 999, and the remaining parameters are the same. The TimeStamp class specially defines the method of setting up and obtaining nanosecond information, respectively. Public Getnanos () Get timestamp nanosecond part Public void setnanos (INT N) Set the timestamp of the timestamp at a given value 4, SQL.TYPES The Types class is the direct subclass of the Object class. In this class, the numerical type of SQL can be used in this class is defined in the form of a static constant. All these types of constants are prefixed Public Final Static Int The form is indicated as a public static integer and is not changeable. The specific type name and meanings are shown in Table 11.1. Among them, Other is used to represent special data defined by the database, and it can be mapped to a Java's Object object with a getObject or setObject method. Table 11.1 SQL types defined in Types Type name meaning Bigint Long-intensity Binary Binary number Bit Bits Charr Character pattern Date Date type Decimal Decimal Double Double precision Float Floating point number Integer Integer Longvarbinary Variable length binary Longvarchar Variable lengthy character NULL Empty type Numeric Numerical type Other Other types REAL Real number Smallint Short integer Time Time type Timestamp Timestamp type Tinyint Micro integer Varbinary Variable binary VARCHAR Variable characters Second, SQL and Java Since the SQL data type is inconsistent with Java's data type, there is a need to read and write type conversion mechanism between the application using the Java type and the database using the SQL type. In fact, the "Get" method of the ResultSet class, Statement and its subclasses of STATEMENT, and its subclasses are part of this conversion mechanism. Read and write conversions needed include three cases: The first case is that after reading the value from the database, the data stored in the RESULTSET object is the data of the SQL type. When the "Get" series method is called, JDBC converts the SQL type to the specified Java type. In the general case, the Java type corresponding to the SQL type is shown in Table 11-2. Table 11.2 Java types corresponding to the SQL type SQL TYPE Java Type Charr Java.lang.string VARCHAR Java.lang.string Longvarchar Java.lang.string Numeric Java.lang.bignum Decimal Java.lang.bignum Bit Boolean Tinyint Byte Smallint Short Integer int Bigint Long REAL Float Float Double Double Double Binary Byte [] Varbinary Byte [] Longvarbinary Byte [] Date Java.sql.date Time Java.sql.time Timestamp Java.sql.timestamp Of course, the user can specify the SQL type to convert the SQL type to a particular type of required, without following Table 11.2. For example, in a Float type value in the result set, use the getDouble method to use the GETDOUBLE method according to the standard conversion application, but in fact, getFloat, GetInt, even the gefbyte method can be obtained, but it is only possible to affect the accuracy of the value. Table 11.3 lists a list of each SQL type to obtain the "get" method, where " " means that the method can be used, "*" means it is preferred. Table 11.3 Get the "GET" method used by SQL type / / SQL / type name / / / / / Get / method name / / / / Tinyint Smallint Integer Bigint REAL Float Double Decimal Numeric Bit Charr VARCHAR Longvarchar Binary Varbinary Longvarbinary Date Time Timestamp GetByte * Getshort * Getint * Getlong * Getfloat * GetDouble * * GetBignum * * GetBoolean * GetString * * GetBytes * * Getdate * GetTime * GetTimeStamp * Getasciistream * GetUnicODestream * GetBINARYSTREAM * GetObject Note: " " means allowing usage; "*" means recommended The second situation is to perform the "SET" series of these declaration classes when the SQL operation issued by the user passes preordatement and callablestatement. For example to PSTMT, calling method for preordatement class objects PSTMT.SETLONG (1,22222222) The drive will automatically convert 2222222 to the Bigint type data of SQL and send it to the database. Table 16-4 Given the standard for Java data to SQL data in this case. Table 11.4 SQL type corresponding to the Java type Java Type SQL TYPE Java.lang.string Varchar or longvarchar Java.lang.bignum Numeric Boolean Bit Byte Tinyint Short Smallint int Integer Long Bigint Float REAL Double Double Byte [] Varbinary or longvarbinary Java.sql.date Date Java.sql.time Time Java.sql.timestamp Timestamp The last situation is that the user may use the inout parameter when using CallableStatement. The conversion process is most complicated. First, use the "SET" family to assign these parameters, the drive uses the standard mapping shown in Table 11.4 to change the Java type data to the SQL type, and then send it to the database. Second, use the Callablestatement's RegisterOutParameter method to register for each parameter as the SQL type when the return value is returned. This registration type must be the name defined in SQL.Types, as shown in Table 11.1. Finally, use the "GET" method after the execution is completed, retrieve the result value of the parameters. The following program clip gives a complete conversion process of the inout parameter from assignment to the value, where the CSTMT is an instance object of CallaBleStatement, with two inout parameters: CSTMT.SetByte (1, 25); // Parameter assignment CSTMT.SETLONG (2,4678935); Cstmt.registerOutparameter (1, java.sql.types.tinyint); CSTMT.RegisterOutparameter (2, java.sql.type.bigint); // Return Type Type Registration ResultSet RS = cstmt.executeUpdate (); While (rs.next ()) {... // processing result} Byte x = cstmt.getbyte (1); // Remove the parameter output value; Long L = cstmt.getlong (2); We can write the type conversion process of these two inout parameters as follows: SetByte EXECUTEUPDATE GETBYTE ↓ ↓ ↓ 参 1 Byte --- → Tinyint ---- → Tinyint --- → Byte Setlong ExecuteUpdate getlong ↓ ↓ ↓ 参 参 参 --- → Bigint ---- → Bigint --- → long Figure 11.12 Parameter conversion flow chart
