Java application design and development environment-JDBC programming (transfer)

The Java language has the characteristics of robust, safety, portability, easy-speaking and automatic download, making it a basic language suitable for use in database applications. The JDBC established on this basis provides a universal interface that can be connected to multiple different databases for applications that are independent of Database Management System DBMS. 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.

1.2.1 JDBC and SQL

We have already introduced in the previous section, and the SQL language is the standard operation language of relational data. JDBC is based on SQL declaration, so that a unified user interface is provided at the level of different database function modules. JDBC is a lower layer API interface, that is, it executes SQL declarations and retrieves execution results, which is the basis for higher level APIs. The high-level API has a more friendly user interface, and the low-level interface that is translated to JDBC is completed. 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 backend DBMS actual execution. 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. 1.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.

1.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 in Figure 1.1. 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 1.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 1.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 1.2 JDBC support three-layer model

1.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. The main interface and functional implementation relationship is shown in Figure 1.3.

┌─────────────┐ │DriverManager│ └─────────────┘ ↙ ↘ ┌──────────┐┌── ──── ─ ┐ │Connection││Connection│ └ - ───────────────────────────────────────────────── ─ ─ ──────────────── │Statement││prepared-│ │CALLABLABLABLABLE-│ │ │Statement│ sStatement│ └─────── ─ ─ ────────────────────────────────────────────────── ─ ─ ─────── ─ │ResultSet││ResultSet││ResultSet│ └────────────────────────────────────────────────────────────────────────────────────────────── ──────── ─ ─ ─ ─ The main interfaces in the SQL package and interrelationships These interfaces exist in the Java's SQL package, their names and basic functions are: * Java.sql.drivermagnager management drive, support drive and data Connection creation. * Java.sql.connection represents the connection to a database, supports the creation of the SQL declaration. * Java.sql.Statement Performs a static SQL declaration in the connection and obtains the results. * Java.sql.preparedStatement Statement subclass represents the pre-compiled SQL declaration. * Java.sql.callaBleState Statement subclass represents the stored procedure of the SQL. Java.sql.ResultSet represents the data result generated after the SQL declaration. In the next section, we will introduce these interfaces.

1.2.5 JDBC 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 1.4 - ──── ┐ │Java Application│ └──────────JDBC API ------------- ---------------- ┌───── ┐ │JDBC Manager│ └───── ------------- -------------------- - ──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────── ─ ┐ DJBC │JDBC-Net││JDBC-ODBC ││native-Api││native-Protocol │ Drivers│Driver ││Bridge Driver │ │Driver │ d d │ └────────────────────────────────────────────────────────────────────────── ─ ─ ───────────────────────────────────────────────── FIG ────────┐ │ DBMS │ └────────────────────────────────┘ 1.4 JDBC Drivers

JDBC's Driver can be divided into the following four types: (1) JDBC-ODBC Bridge and ODBC Driver This drive provides database connections through the ODBC drive. Using this drive requires every client to load an ODBC drive. (2) Native-API Partly-Java Driver This drive transforms 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 This drive transforms JDBC instructions into the form of network protocols independent of DBMS, and then converts the server to 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 This driver converts the JDBC instruction into a network protocol, which is no longer converted, and is used directly 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. The routines in this book are all done with the JDBC-ODBC Bridge driver.

1.3 JDBC programming

In this section, we will specifically introduce JDBC programming. Using JDBC Drivers in this section, all JDBC-ODBC Bridge is used, and the database used is an Access database.

1.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 1.1 Cream.java gives a simple JDBC program. After execution, create a table named TestTable, including two domains, and the domain names are ID and NAME, respectively.

Example 1.1 Create.java and program descriptions.

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 // Establish connection 14: 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 SuccessFully!"); 22: 23: // Close STM 24: 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:} 41: [Cyclone Note: I compiled this example is VJ 6.0, the operating system is WIN98 (2), compiled, but report "no suitable driver" after executing the program, I judge the error reason is that Win98 is not coordinated with Sun.jdbc.odbc.jdbcodbdriver. Thereafter, the author changed the 11th sentence to: class.forname ("com.ms.jdbc.odbc.jdbcodbcdriver"); recompile running. ]

Example 1.1 The basic function is to create a table called TestTable, which has two domain IDs and name. The program structure is very simple: the first step, download the drive used and connect to the database (10 ~ 14) line; step 2, create a statement instance, execute this SQL declaration (20 line); third step ,process result. Only "Create SuccessFully!" (21 lines) is simply output in this program; the fourth step is to close the connection and statement objects (24 ~ 27 rows). The TRY-CATCH statement is provided in the program to capture the SQL exception, and the corresponding information is given when an exception is exceeded. The first time the cteate.java screen displays "CREATE SUCCESSFULLY!" If the Create.java program is running again, the exception handling information will be displayed on the screen. The results of the operation are as follows. The result of the first time of Create.java: C: / MyDemo / MyDB> Java Create Create SuccessFully! Run the result of Create.java for the second time: c: / mydemo / mydb> java create *** SQLException caught *** SqlState: S0001 Message: [Microsoft] [ODBC Microsoft Access 7.0 Driver] Table 'TestTable' Already Exists. Vendor: -1303 The following we have further discussed the issues involved in the program. First, establish a data source before the execution procedure we must first establish a data source. The database used in this section is an Access database. The basic steps to establish a data source are as follows: (1) Turn on the control panel, select the ODBC item (shown in Figure 1.15) () (2) Select the Add ... button (shown in Figure 1.6) () (3) Select Microsoft Access Driver Item (Figure 1.7) () (4) Filling the name of the data source in the Data Source Name, add some description information in the discrtiption, press the Select ... button to connect the data source to a database file, Press (CREATE ... button) You can create a new database file and connect it to the data source. The button OK button ends the operation (as shown in Figure 1.8). Second, the load driver (driver) First, the method is used in Example Class .forname (String) To load the drive (Driver). A DRIVER interface is provided in the JDBC. Each real drive must implement this interface. You must load the drive before writing the JDBC program, which means that a driver class must be created. Example. Java provides two ways to load the driver. The first way to load the drive is used in use., As used, as used in Example 1.1. This is also recommended by everyone, because the method can be used At any time, the driver is explicitly loaded without the need to install this method, and will create an instance of the specified drive class and automatically call the instance as a parameter, and the method DRIVERMANAGER.REGISTERDRIVER is automatically added to the driver table for DriverManager management. Change In order to register this drive, it is allowed to create a connection. Another way to load the drive is to join the driver to java.SystemProperty "jdbc.drivers". Figure 1.8 Create a new data file and put it And data sources (omitted) Example You can add the following statements in ~ / .hotjava / property file to specify the drive you want.

Jabc.drivers = foo.bah.driver: wombat.sql.driver; when initialization, DriverManager will look for system resources JDBC.DRIVERS, and load the drive already specified in, and then automatically register these drives, allowing to create a connection. Third, after the connection is established, after the drive is loaded, the connection to the specific database can be established with the method in DriverManager. The method is as follows: Public Static Synchronized Connection getConnection (String URL, String User, String Password "The DriverManager selects the appropriate drive from the registered JDBC driver collection and returns an instance of a Connection class associated with this drive. In this section, we introduce the basic structure of the URL, introduce the ordinary URL before introducing the JDBC URL. (1) The current (Uniform Resource Locator) is a related information for determining the resources on the Internet, and the role is equivalent to an address. Under normal circumstances, the URL is determined by three parts: 1 Access the protocol used by the resource. Generally there are FTP (File Transfer Protocol), HTTP (Hypertext Transfer Protocol), and File (local file system). Their back is generally colon: this is essential. 2 host information. It gives the information of the host where the resource to be accessed is. Host information on all Internet starts in "//", and host information of the local file system begins with "/". They end with "/". 3 Access path. Below is a typical internet URL: http://www.nju.edu.cn/incoming Its meaning is to press the HTTP protocol to access www.nju.edu.cn host name INCOMING. (2) JDBC's URL JDBC URL provides a method of labeled databases, making DriveRManager to find a suitable drive and establish a connection. The basic form of JDBC's URL is: JDBC: : 1jdbc is fixed as the first part in the URL of JDBC. 2 : Subprotocol is a connection mechanism for the drive or database, and different drives can support the same sub-protocol. They are provided by the writer of the drive. 3 : Used to determine a specific database. If the resource accessed is on the Internet, it should include network addresses in Subname, of course, the form of network addresses must comply with standard URL naming regulations. Subprotocol in the program (Subprotocol) is ODBC, which is a special protocol. The basic format of the URL using the ODBC sub protocol is: JDBC: ODBC: = ] In this URL format, specify the name of the data source of the ODBC style as subname, and allow Additional attribute parameters such as PASSWORD are added thereafter. In the program 1.1 (line 14), string url = "jdbc: odbc: demo"; connection con = drivermanager.getConnection (URL, "User", "password"); established a connection associated with data source Demo . DriverManager provides the most basic service for managing a set of JDBC drivers, in addition to the connection between the database and the corresponding driver, the supplied drive and related information printing.

In addition to the methods that have been explained above (outside, DriverManager also provides other methods: 1 Public Static Synchronized void RegisterDriver (Driver Driver) THROW SQLEXCEPTIN; calling this method to register the newly loaded drive so that it can be managed by DriverManager . 2 Public Static Void SetLogStream (PrintStream Out) Sets the trace information output stream used by DRIVERMANAGER and other drives. Example in Example 1.1 Class.Forname ("JDBC.ODBC.jdbCodbcdriver); then add statement drivermanager.setlogstream (System.out); after recompiling execution, the reader will find a lot of connection and other operations on the screen information. DriverManager also provides many other method management drivers, and details can be found in the JDBC API. 4. Pass the SQL declaration to establish an instance of a CONNECTION class after the program is established in the program. By this example can communicate with a particular database. (1) The CONNECTION class provides the following three ways to create three category declarations. The specific functions of these three category declarations are different, but basic functions are transmitted and executed, and the specific content will be introduced in the next section. Three methods: public abstract statement createStatement () throws SQLException; public abstract PreparedStatement prepareStatement (String sql) throws SQLException; public abstract CallableStatement prepareCall (String sql) throws SQLExecption; ⑵ connected database may provide information about itself, such as its The supported SQL syntax, the method of using the class DatabaseMetAdate can get relevant information. The Connection class provides a way to get an instance of the DatabaseMetadata class corresponding to the connected database. The method is: Public Abstract DatabasemetaData getMetadata () throws sqlexception; (3) CONNECTION class provides other related methods. For example, it provides method close () to close the connection to a particular database, providing method getWarnings () to get SQL warning information, etc.. In Example 1.1, we created a class Statement instance using the method in the Connection class, and transmits and executes a SQL declaration for creating a table name Testable, and finally closed the connection. In addition, Example 1.1 also processed SQL abnormalities. 5. The result is handled the SQL declaration, and the user can generate the corresponding results, and the user can use the methods provided in the JDBC to acquire the results and perform corresponding processing. Further introductions will be combined in conjunction with examples. 1.3.2 Statement class and its subclasses

JDBC provides three classes to deliver SQL declarations to the database. They are: (1) :Statement: Used to perform a simple SQL declaration without parameters. (2 )PreparedStatement: Used to perform a pre-compiled SQL declaration and allow the IN parameter to be carried in the SQL declaration. (3) CallableStatement: Used to perform the call of the database store. It allows three types of parameters for IN, OUT or INOUT. These three classes are shown in the following figure: Statement │ │ Add IN parameters └── ─ → PreparedStatement │ │ Add OUT parameter └─── ─ → Callablestatement Figure 1.9 Inheritance between Statement, the class statement (1) Create method Statement object STATEMENT is used to perform a static SQL declaration (ie, the SQL declaration without parameters) and acquire the processing result. The method of creating an instance of a Statement class is simple, just call the method CreateStatement () in the Cabin Connection. The general form is as follows: Connection Con = DriverManager.getConnection (URL, "User", "password") statement stmt = con.createstatement (); (2) After the implementation method creates an instance of the Statement class, you can modify the method to perform SQL Declare that three implementations are provided in JDBC, which is Execute (), ExecuteQuery (), ExecuteUpdate (). 1ExecuteUpdate method PUBLIC Abstract int executeUpdate (STRING SQL) Throw Sqlexception. This method is typically used to perform SQL INSERT, UPDATE, or DELETE declaration, or execute SQL DDL declarations that have not returned values ​​(ie SQL data definition language), such as CREATE or DROP declarations Wait. When executing the SQL declaration such as INSERT, the return value of this method is the total number of records that have been recorded after this SQL declaration. If the return value is 0, it means that the execution has not affected the database. If the declaration of the execution is the SQL DDL (Data definition language) declaration, the return value is also 0. 2ExecuteQuery method Public Abstract Resultset ExecuteQuery (String SQL) Throw Sqlexecption; this method is generally used to perform SQL SELECT declarations. Its return value is an instance of a ResultSet class generated after the SQL declaration. Use the method in the RESULTSET class to view the appropriate results. 3Execute method public abstract Boolean Execute (String SQL) throw sqlexception; this method is special, usually only what results will be generated after the user does not know if the SQL declaration is executed or may be used in multiple types of results. For example, when a stored procedure is executed, it may contain both delete declarations, including the SELECT declaration, thereby executing only a resultset, which affects related records, that is, two types of results, this Methods must be performed with method eXcute () to obtain complete results. Execute () execution results allow for generating multiple resultsets, or multiple records being affected or both. Due to the particularity of the results, there is also a specific method for queries that the results generated after the call ().

Execute () This method itself is a Boolean value, when the next result is RESULTSET, it returns true, otherwise returns false. The resulting getResultSet, getUpdateCount, getMoreresult, etc. are provided in the Statement class to query the results of executing execute (). * Public Abstract ResultSet GetResultSet () THROWS SQLEXCETION If the current result is a resultset, return a resultSet instance, otherwise return null, for each result, that is, each result can only be acquired once. Public Abstract Int getUpdateCount () THROWS SQLEXCEPTION If the current result is modified to some records, return to a total of the total number of rows, otherwise returns -1. Similarly, each result can only call this method. * Public Abstract Boolean getMoreResults () throw SQLEXCEPTION This method sets the current result to the next result, so that the line can be queried with the method getResultSet () and getUpdateCount (). When the next result is RESULTSET, returns true, otherwise returns false. A program INSERT.JAVA is given below. Which using ExecuteUpdate () executes the SQL declaration, when you introduce the Resultset, we will discuss the SQL declaration with ExecuteQuery (). As for the SQL declaration with Execute (), the corresponding result is obtained, because it is generally only used to process the stored procedure, so we will introduce when CallaBestatement is described. Example 1.2 INSERT.JAVA and program description

1: import java.net.ur1; 2: import java.sql. *; 3: 4: Class Insert {5: public static void main (string args []) {6: string url = "JDBC: ODBC: Demo" 7: try {// Download JDBC-ODBC Bridge Drive 8: Class.Forname ("Sun.jdbc.odbc.jdbCodbcDriver"); // Refer to Note 9: // Establish connection with the drive 10 in Example 1.1 : Connection Con = DRIVERMANAGER.GETCONNECTION (URL, "User", "password"); // Create a statement object 11: statement stmt = con.createstatement (); 12: // Execute SQL declaration 13: int count1 = STMT. ExecuteUpdate ("INSERT INTO TESTTTABLE (ID, NAME) VALUES (1, 'WU')"); 14: INT Count2 = Stmt.executeUpdate ("INSERT INTO TESTTTABLE (ID, NAME) VALUES (2, 'WANG')") 15: // Print execution result 16: System.out.println ("INSERT SUCCESSFULLLY!"); 17: System.out.println ("Updated Rows IS" "."); // Close connection 18: Stmt.close (); 19: con.close (); 20:} catch (sqlexception ex) {// Print SQL exception information 21: system.out.println ("/ n *** sqlexception caught * ** / N "); 22: while (ex! = null) {23: system.out.println (" SQLState: " ex. maxqlstate )); 24: system.out.println ("message:" ex.getMessage ()); 25: system.out.println ("vendor:" ex.GeterrorCode ()); 26: EX = ex.getnextexception (); 27: system.out.println ("");} 28:} catch (java.lang.exception ex) {29: ex.printstacktrace (); 30:} 31:} 32:}

The function of program INSERT.JAVA is completed to add two records to the established table testtable. Similar to Example 1.1, the database is first established in Insert.java, then create a statement object to perform INSERT SQL declaration, and finally enter the processing result. Using Method ExcuteUpdate (String) in the program, it is generally used to perform INSERT, DELETE, UPDATE, and SQL DDL declarations, and its return result is the total number of records that affect the record. After running insert.java, the results are as follows. The reader can call the Select.java program in the next section to view the contents of the testTable to understand the role of the INSERT declaration. Program INSERT.JAVA operation results. ! C: / MyDemo / mydb> java Insert Insert successfully Updated rows is 2 C: / MyDemo / mydb> java Select The detail of testTable is; id, name 1, wu 2, wang II class PreparedStatement PreparedStatement class is Statement class Subclasses, it directly inherits and rewrites Statement methods. The PrepardStatement class has two features: Features 1: The SQL declaration included in the object of a preparedStatement is precompiled, so when you need to perform the same SQL declaration multiple times, this SQL declaration can greatly improve the execution efficiency. Features II: One or more IN parameters are allowed in the SQL declaration included in PreparedStatement objects. When you create an instance of class preparedStatement, the IN parameter is replaced by "?". Before the SQL declaration with parameters, "?" Must be assigned, in order to add a large number of setxxx methods to "?" Assignment, PREPAREDStatement classes, and assign the IN parameter. ⑴ create an instance created with the method of Statement PreparedStatement object class is similar, to create a class object PreparedStatement also just after the connection is established, the calling method public Connection class throws SQLException abstract PreparedStatement prepareStatement (String sql); Example create an object of a PreparedStatement There is a SQL declaration containing a parameter. PreparedStatement PSTMT = Con.PrepareStatement ("INSERT INTO TESTTTABLE (" INSERT INTO TESTTTABLE (ID, NAME) VALUES (?,?)); (2) The assignment of the (2) parameter provides a large number of setxxx methods to assign the IN parameters in the assignmentStatement. The appropriate setxxx method should be selected according to the SQL type of the IN parameter. For the above example, if the first parameter is required to be 3, the second parameter is set to XU, that is, the inserted record ID = 3, Name = "XU", the following statement can be implemented: pstmt.setint (1, 3); PSTMT.SetString (2, "xu"); except for common methods such as Setint, SetLong, SetString, SetBoolean, Setshort, SetByte, PreparedStatement also provides several special setxxx methods.

1Setnull (int parameterindex, int SQLTYPE) This method assigns the parameter value to NULL. SQLTYPE is the SQL type model defined in java.sql.types. Example PSTMT.SetNull (1, java.sql.types.integer); assigns the value of the first IN parameter into NULL. ②setUnicodeStream (int Index, InputStream x, int length); setBinaryStream (int Index, inputStream x, int length); setAsciiStream (int Index, inputStream x, int length); when the parameter value is large, the parameter value can be placed In an input stream x, by calling the above three methods, it employs a particular parameter, the parameter length represents the length of the string in the input stream. A program that uses class preparedStatement is given below. Example 1.3 INSERT2.JAVA and program description

1: Import java.net.URL; 2: import java.sql. *; 3: 4: Class INSERT2 {5: public static void main (string args []) {6: string url = "JDBC: ODBC: Demo" 7: String data [] [] = {{"5", "xu"}, {"6", "yan"}}; 8: try {// Download JDBC-ODBC Bridge Drive 9: Class.Forname "sun.jdbc.odbc.jdbcodbcdriver"); // For this sentence, see 11 sentences of Example 1.1 Note 10: // Establish connection with the drive 11: Connection Con = DriverManager.getConnection (URL, "User", "Password" ); 12: // Create a PAREPARESTATEMENT object 13: preparedState pstmt = con?preparestatement (14: "Insert INTO TESTTABLE (ID, NAME) VALUES (?,?)"); 15: // Parameter assignment, execute SQL declaration 16 : For (int i = 0; i

Program INSERT2.JAVA uses preparedStatement to perform a precompiled SQL declaration: INSERT INTO TESTTTTTTTABLE (ID, NAME) VALUES (?,?) This SQL declaration has two IN parameters, used? instead. Program (17 ~ 18) PSTMT.SetString (2, DATA [i] [l]); these two statements use the setxxx method assignments to the IN parameter. Like program insert.java, INSERT2.java's functionality also adds two new records to TestTable, and the SQL declaration of INSERT has been executed. Since PreparedStatement allows the pre-compiled SQL declaration, INSERT declaration in program insert2.java is precompiled before execution, and it reflects its efficient superiority when executed. In addition, it is precisely because prepaaredStatement allows the SQL declaration that is allowed to perform the IN parameter, so the same INSERT declaration may be executed multiple times. The operation results of the program insert2.java are as follows, and the select.java program in the next section can view the contents in the table. Operation results of the program insert2.java. C: / myDemo / mydb> java insert2 insert successful! C: / mydemo / mydb> java select the detail of testtable is; id, name 1, wu 2, wang 5, xu 6, yan program insert2.java reflects class prepaaredStatement Two major features: (1) It has two IN parameters in the INSERT statement it execute; (2) ISERT This SQL declaration is precompiled, so multiple execution efficiency is higher. Third, class CallableStatement CallableStatement provides a standard way for the stored procedure to call the database in the JDBC program. The syntax for activating a stored procedure in JDBC is: {call proc_name (?, ...?)} Or? = Call proc_name (?,, ...?)} Additional stored procedures in CallableStatement Allows the IN parameters, OUT parameters, or inout parameters, so as the subclass of preparedstatement. In addition to inheriting methods in preparedStatement, CallableStatement has also increased how to process OUT parameters. (1) Creating a CallableStatement object Creating a CallableStatement is mainly used to perform a stored procedure, so the reader must first find whether your database supports the stored procedure. Readers can use the relevant methods in the DatabaseMetadata class to get relevant information (see DatabaseMetadata section). Connection method calls the class: public abstract CallableStatement prepareCall (String sql) throws SQLException; CallableStatement object can create an embodiment CallableStatement cstmt = con.prepareCall; ⑵ parameter set ( "{call test (,)??}") Inheriting the setXX method in the preparedStatement class inherits the setxxx method to assign the IN parameter, the OUT parameter, the callableStatement provides the value of the type registration and retrieves its value.

1 Type Registration Method Before performing a stored procedure, you must first register the OUT parameters, when you use the getxxx method to get the value of the OUT parameter, XXX this Java type must match the registered SQL type. CallableStatement provides two ways to register: * RegisterOutParamenter (int ParameterIndex, int SQLTYPE); * RegisterOndParameter (int parameterIndex., Int SQLTYPE, INT Scale); the first method is suitable for various types, except Numeric and Decimal two Type. The two types generally use the second method for registration, the parameter scale in the second method is an integer greater than equal to zero, which is a precision value that represents the number of bits allowed in the right of the registered type. The example below is the type of OUT parameters in the stored procedure called Test: cstmt.registeroutparameter (2, java.sql.types.varchar); this statement is the second in the stored procedure of TEST The parameter is output type registration, the registered SQL type is java.sql.types.varchar. 2 Getxxx method of query results Since CallableStatement allows the stored procedure with an OUT parameter, it provides a complete GetXXx method to obtain the value of the OUT parameter, and the obtained value is slightly different. * Boolean Wasnull () Returns True when the obtained parameter value is null. * GetBoolean (int) Returns True when the value is null. * GetByte (int), get11, getShort, GetShort (int) returns 0 when the parameter value is SQLNULL. * GetString (int), getNumeric (int, int), getTime (int), gettime (int) When the parameter value is SQLNULL, the return value is NULL. In the CallableStatement class, there is an inout parameter in addition to the in parameter and OUT parameters. The inout parameter has all the other functions of the other two parameters, and the parameter value can be set by the setxxx method, and the parameter is registered, allowing this parameter to use the Getxxx method. After performing the SQL declaration that is taking this parameter, you can get a changed value with the getxxx method. Of course, when the type is registered, the type consistency problem is considered (discussing the type consistency will be discussed in the following chapter). CallableStatement is typically used to perform a stored procedure, so the execution result may not only be single - might be multiple ResultSets, or multiple modified records or both. So the CallableStatement General call method execute () executes the SQL declaration. The example 1.4 Callable.java is given below, which uses CallaBleStatement, and execute () method is executed. The two storage procedures used in the program use two-to-use parameters, which are defined in Access.

They SQL statement as follows: ⑴Search PARAMETERS IDofStudent Long; SELECT DISTINCTROW Students * FROM Students WHERE ((Students.StudentID = [IDofStudent])); ⑵Delete PARAMETERS IDofStudent Long; DELETE * FROM Students WHERE ((Students (?). (?) .Studentid = idofstudent))); Creating a storage process in the Access database is easy. First, open the database (Database) that is connected to using the data source (the relationship has been introduced in 1.3.1.1); then, after Figure 1.10, after selecting the query item, select the button New, at this time Select, select the New query item (Figure 1.11) to prepare a storage process. At this point, select the SQL button on the toolbar to implement the storage process manually in SQL language. Example 1.4 Callable.java.

1: Import java.net.URL; 2: import java.sql. *; 3: 4: Class Callable {5: public static void main (string args []) {6: string url = "JDBC: ODBC: TEST" ; // Note that this is no longer DEMO data source 7: try {8: // Download JDBC-ODBC Bridge Drive 9: Class.Forname ("Sun.jdbc.odbc.jdbCodbcDriver"); // For this sentence, see example 1.1 Note 10: 11: // Establish connection 12: connection con = drivermanager.getConnection (URL, "My-User", "My-Password"); 15: // Creating a CallableStatement to perform storage procedure 16 : Callablestatement cstmt = con.prepareCall ("{Call search (?)}"); 17: // Parameter assignment 18: cstmt.setint (1,934678); 19: // Execute the storage process.

20: cstmt.execute (); // Processing Execution 21: Display (CSTMT, "Search"); 23: CallableStatement Stmt = Con.PrepareCall ("{Call Delete (?)}); 24: Stmt.setint 1,934655); 25: stmt.execute (); 26: Display (stmt, "delete"); // Close connection stmt.close (); cstmt.close (); con.close ();} catch (sqlexception EX) {// Print SQL exception information system.out.println ("/ n *** sqlexception caught *** / n"); while (ex! = null) {system.out.println ("SQLState:" EX.GETSQLSTATE ()); system.out.println ("message:" ex.getMessage ()); system.out.println ("vendor:" ex.GeterRorcode ()); EX = ex. maxnextexception () System.out.println (");}} catch (java.lang.exception ex) {ex.printStackTrace ();}} // Processing Execute Storage Procedure 47: Private Static Void Display (Callablestatement CSTMT, String Name) THROWS SQLEXCEPTION {system.out.println ("Excute Procedure"; While (TRUE) {// Processing Situation: Recording 51: int RowCount = cstmt.getUpdateCount (); if (Rowcount> 0) {system.out.println ("Updated Rows IS" RowCount "); // Judgment whether there is a result of the result processing if (! Cstmt.getmoreresults ()) Break; Continue;} // processing : Executive results for ResultSet57: ResultSet RS = cstmt.getResultSet (); if (rs! = Null) {ResultSetMe Tadata RSMD = rs.getMetadata (); // Print Result Set Title INT NumCols = rsmd.getColumnCount (); for (INT i = 1; I <= Numcols; i ) System.out.Println ("/ T / T " rsmd.getColumnlabel (i) " "); system.out.println (); // Print result set content Boolean more = rs.next (); if (! more) system.out.println (" / T / T0 / T / T / T0 / T / T0 "); While (more) {for (int i = 1; i <= number) system.out.print (" / t / t " rs. GetString (i) ""); more = rs.next (); system.out.println ();} // Judgment whether there is a waiting result 72: if (! cstmt.getmoreresults ()) BREAK; Continue } // processing is:

The execution result affects 0 records if (RowCount == 0) System.out.Println ("NO ROW IS Updated!"); If (! Cstmt.getmoreresults ()) Break;} 79:}} Run results: C: / myDemo / mydb> java Callable execute procedure Search ID StudentID FirstName 6 934678 li 15 934678 zhao execute procedure Delete Updated rows is 1 Example 1.4 in two CallableStatement object is created to perform the two Search during storage with parameters (?) and DELETE (?) (15 ~ 26 line). Method Display () (47 ~ 79 line definition) is used to process the results generated after executing the storage process, where the relevant method used is described when STATEMENT is described, such as: 51: int RowCount = cstmt.getupdateCount (); // Number of records affected after the execution process 57: resultSet RS = cstmt.getResultSet (); // Get the number of records obtained after the execution process 72: if (! Cstmt.getmoreresults ()) Break; // Judgment whether there is Waiting for processing The result is the method of the result letter, we will introduce in the next section.

1.3.3 Results RESULTSET

I. Basic Concepts and Method Results ColtSet is an abstract interface class used to represent a collection of results generated after the SQL declaration. Its instance object is typically generated after the SQL statement is executed by the STATEMENT class through method execute or executeQuery, which includes the execution result of these statements. The usual form of ResultSet is similar to the table in the database, which contains a specified line in all rows that meet the query requirements. For example, we have established a table Student: Table Student: ID Name Score -------- ------ 1 XU 90 2 Li 70 3 WU 85 4 WANG 80 5 ZHANG 88 Execute the following query statement on table Student and obtain results via ResultSet: ResultSet RS = Stmt.executeQuery ("SELECT ID, NAME FROM STUDENT WHERE score> = 85); The resulting result set RS includes the following content: ID Name // column header -------------- rs: 1 xu // column value 2 WU 3 ZHANG Since a result set may contain a number of rows that meet the requirements, in order to read convenience, use the read pointer to mark the current line. The initial position of the pointer points to the first line, the resultSet class provides the following Next method to move the pointer, each called a next method, the pointer is moved down: public abstract boolean next () When the pointer is the last line of the result set, then call the next method, return the value is false, indicating that the result set has been processed Once, thus, the program section of the usual processing set has the following structure: while (rs.next ()) {... // Handling Each result line} It should be noted that the first paragraph of the result set is also The first time the next method can be taken. After obtaining the current line, the ResultSet class provides a means of obtaining the column value of the specified column from the current row from the current row by a series of "GET" methods. This series of methods is Get column value Type (Column Name) Get Column Value Type (Column Number) These column names and column number are used to specify columns to get the value. If the RS object you created earlier can take the following statement: rs.getint (" ID "); rs.getstring (" name "); or equivalent to execute RS.Getint (1); rs.getstring (2); in one line, the values ​​of each column can be read in any order. But in order to keep The maximum portability is usually taken from left to right, and each column is only read once. When When a result set may have two or two or more columns, it is best to use the column number to specify the column required. The column number is also numbered from left to right, starting with serial number 1. Corresponding to the type specified by the GET method, JDBC Driver always tries to convert the data type that the database actually defined into the appropriate Java definition. For example, a GetString method is executed for a VARCHAR type data of a database, and a String class object of Java will be returned. ResultSet also provides an extremely useful method GetObject. It can return any data type to Java's Object class object, which is effective for obtaining a specific abstract class and writing general programs. The specific type corresponds to the conversion here, please read more about 1.3.5.

In addition, the relatively useful method in the ResultSet is: ■ Public Abstract Int FindColumn (String ColumnName) The functionality of this method is to identify the corresponding column number based on the result set column name given. ■ Public Abstract Boolean WASNULL () The functionality is to check if the latest read column value is the NULL type value of SQL. Note To first read a column with the "GET" method, then call this method to check if it is empty. ■ Public Abstract Void Close () The functionality of this method is to turn off the result set. In general, the result set does not need to be explicitly closed. The corresponding result set will be automatically turned off when the declaration class object that produces a result set is turned off or executed again. When reading the next result of multiple result sets, the previous result set will also be automatically turned off. But in some specific cases, it is necessary to force closing and release resources. Second, RusultSetMetadata should be very clear in most cases, we should be very clear for the form and structure of the results set. But sometimes we don't understand the result set before gaining results. At this time, you need to use the RESULTSET's auxiliary RESULTSETMETADA to obtain a descriptive information of the number, column value type, and other characteristics of the result set. This class is also useful when writing a versatility database operation program. The object of ResultSetMetadata is obtained through the RESULTSET method GetMetadata. For example, it is assumed that the RS is the result set, which can be used to obtain the information description object corresponding to the result set: ResultSetMetadata (); the ResultSetMetadata class provides a lot of useful methods to get information about the result set. These methods mainly include: * Public Abstract Int getColumnCount () The functionality of this method is the total number of columns of the result set. * Public Abstract Int getColumndisPlaySize (int column) The functionality of this method is the maximum length of the column represented by the specified number (character represented). * Public Abstract String getColumnLable (int column) The functionality of this method is to get the recommended sequence quoted suggested title. * Public Abstract String getColumnName (int column) The functionality of this method is to get the name of the specified serial number. * Public Abstract Int getColumnType (int column) The functionality of this method is to obtain the corresponding integer value of the specified serial number corresponding to the class defined by the package SQL class Types. * Public Abstract String getColumnPename (int Column) The functionality of this method is to get the specified serial number list in the specific SQL class name. * Public Abstract Boolean LsreadOnly (int column) The functionality of this method is to check if the specified serial number is only readable. If you only readable columns, return value true; otherwise return value False. * Public Abstract Boolean IsdefinitelyWritable (int column) The functionality of this method is to check if the specified serial number can be written. * Public Abstract Int getPrecision (int column) The functionality of this method is to obtain the accuracy of the specified number column by the number of fewer numbers allowed by the return column value.

Third, instance Select.java Here we give an example select.java, the functionality of the program is a table testTable created in Example 1.1 for query, execute SQL action: select * from testtable then display the query result. Example 1.5 Select.java program file.

Import java.net.URL; import java.sql. *; public class select {public static void main (string args [] (string args [jdbc: odbc: demo "; string query =" select * from testtable "; Try {// Mount Drive JDBC-ODBC Bridge Driver Class.Forname ("Sun.jdbc.odbc.jdbCodbcDriver"); // Established Connections Connection Con = DriverManager.getConnection (URL, "User", "Passwd"); / / Create a statement object statement stmt = con.createStatement (); // Send a query request, get the result set ResultSet RS = Stmt.executeQuery (query); // Show result set Each row system.out.println ("THE DETAIL OF TestTable IS: "); ResultSetMetadata RSMD = rs.getMetadata (); // Get Results Column in Numcols = rsmd.getColumnCount (); // Display Column Title for (INT I = 1; I <= NumCols; i ) {IF (i> 1) System.out.print (","); system.out.print (rsmd.getColumnLabel (i));} system.out.println ("); // Show 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));} system.out.print (""); } // Close the result set rs.close (); // Turn the declaration stmt.close (); // Close connection con.close ();} catch (sqlexception ex) {// processing exception system.out.print (" / n * * * sqlexception caught * * * / n "); while (ex! = null) {system.out.println (" sqlstate: " ex. maxqlstate ()); system.out.println (" Message: " ex.getMessage ()); system.out.println (" vendor: " ex.GeterrorCode ()); ex= ex.getnextexception ()); system.out.println (");}} catch Java.lang.Exception ex) {ex.printStackTrace ();}} In this program,

We created a SELECT class, and its only way is the main method. In the main method, two String constants are defined: URL and Query. Where the URL is used to store the data source address, Query is used to store SQL operations to be executed. Such a programming style is easy to check the error and modification, and it can be directly referenced in the following statement. Next, we load into the drive in accordance with a convention, establish a connection and create a SQL declaration object: 10: Class.Forname ("Sun.jdbc.odbc.jdbcodbcDriver"); 12: Connection Con = DriverManager.getConnection (URL, "User", " Password "); 15: statement stmt = con.createstatement (); and the execution of the SQL query operation and the result of the result of the results are implemented by the following statement: 17: RESLTSET RS = Stmt.executeQuer (query); this time RS is stored in RS There is a query result set. The next block 19 to 40 lines are used one by one to display the contents of the result set RS one by one. First we create RS corresponding result set information object RSMD: 21: ResultssetMetAdata RSMD = rs.getMetadata (); then, get the column number of results RS by RSMD: 23: Int numcols = rsmd.getColumnCount (); in next When the RS column header, we also use the RSMD to getColumnlabel () to get the display title of each column: 28: System.out.print (rsmd.getcolumnLable (i)); Show column headings, we use The While loop is engaged with the rs.next () method, and the value of each line of TestTable is displayed by line. Here, each column value of the current line is obtained with the resultSt's getString method. Although TestTable ID to numeric type is int, JDBC will translate it into Java's String class to make it easy to output: 37: system.out.print (Rs.getstring (i)); Finally, it will set the result set RS , Declare STMT and connection CON turn off (line 42 ~ 4 dry). When capturing SQLException is abnormal, an error message is displayed as the previous example. If there is other type of abnormality, the stack track (lines 47 to 57) is displayed. The result of the execution of SELECT.JAVA actually shows the content of the entire TestTable table, and outputs it below. Example 1.6 SELECT.JAVA operation results. C: / MyDemo / MyDB> Java Select The detail of testtable is: studentid, firstname 2, li 3, wang c: / mydemo / mydb> 1.3.4 DatabaseMetadata

First, the function brief is similar to the ResultSetMetadata class, and DatabaseMetadata is a database information class with an auxiliary nature. It provides users with ways to obtain information about the drive and database. The instance object of the DatabaseMetadata class is created by the GetMetadata method for the Connection class. For example, the following statement creates a DataBaseMetadata class object called DMA: Connection Con = DriverManager.getConnection (URL, "User", "password"); DatabaseMetadata DMA = Con.getMetadata (); DatabaseMetadata provides a large number of information about information These methods can be divided into two major categories: one class return value is a Boolean type, most to check if the database or drive supports a feature; the other class uses some of the feature values ​​of the database or drive itself, the return value may be Integer, may be a string type, and may even be an object of the ResultSet class. Here are the presentation. Second, the Common Method (1) In the method of the return value is a Boolean type, it is more important and commonly used: * Public Abstract Boolean AllProceduresArECALLABLE () This method is to check if the process returned by the getProcedures method can be called by the current user If it is, return TRUE. * Public Abstract Boolean UseLocalFiles () The functionality of this method is to check the data inventory reservoir table, return TRUE when the database is stored using the locally file list. * Pubile Abstract Boolean IsReadOnly () The functionality of this method is to check the database, and returns True when the database is read-only mode. * Public Abstract Boolean SupportsGroupby () The functionality of this method is to check the database and return TRUE when the database supports the GroupBy clause. * Public Abstract Boolean SupportsMultipleResultSets () This method is to check the database and drive, return true when they support multiple result tables, otherwise returning false. * Public Abstract Boolean SupportsnSi92ENTRYLEAN Supports QL () The functionality of this method is to check the database when the database supports the ANSI92 Entry Level syntax. Returns TRUE. The database used by JDBC consistency should support this. * Public Abstract Boolean Supportsnsi92Fullsql () The functionality of this method is to check the database, returning true when it supports all ANSI92 SQL syntax, otherwise returns false. * Public Abstract Boolean SupportsouterJolns () The functionality of this method is to check the database, returns true when it supports external connections, otherwise returns false. * Public Abstract Boolean SupportsSstoredProcedures () The functionality of this method is to check the database. When it supports the stored procedure, you can return to True when using the escape syntax of the process, otherwise returns false. Third, the common method (2) In the method of checking the database and drive characteristics, we only list some more important and commonly used, and the rest of the details, please refer to the Java API. * Public Abstract StringgetURL () The functionality of this method is to return the URL address for connecting the database.

* Public Abstract String getUserName () The functionality of this method is to get the current username. * Public Abstract String getDatabaseProductName () The functionality of this method is to get the database product name used. * Public Abstract String getDatabaseProductverslon () The functionality of this method is to get the database version number used. * Public Abstract String getDriverName () The functionality of this method is to get the drive name used to connect. * Public Abstract String getProductverslon () The functionality of this method is to get the drive version number for connection. * Public Abstract String getsqlkeywords () The functionality of this method is to get the keywords in the database in the database to list them in a comma-separated form. * Public Abstract String getProcedureterm () The functionality of this method is to get the database vendor's specific name (TERM) for the stored procedure. * Public Abstract String getMaxColumnNameLength () The functionality of this method is to get the maximum allowable length of the column name defined in the database. * Public Abstract Int getMaxStatements () The functionality of this method is to get the maximum number of SQL declarations that allow simultaneous open SQL declarations. * Public Abstract Int getMaxRowsize () The functionality of this method is to get the maximum length allowed. * Public Abstract ResultSet getProcedures (String Catalog, String Schemapattern, String ProcedureNamePattera) The functionality of this method is to get all the stored procedures that specify the specified type, specify mode, in line with the specified process name template. * Public Abstract ResultSet GetTypeInfo () The functionality of this method is to get a description of all data types that may be obtained in the database. Fourth, the Program Example Let's give an instance dbmeta.java using DatabaseMetadata. In this program, we don't create any SQL declaration objects, nor do any SQL operations, just establish a connection with the database, and then get some information about the database and drives through the DatabaseMetadata class.

Example 1.7 dbmeta.java

Import java.net.URL; import java.sql. *; public class dbmeta {public static void main (string args []) string url = "jdbc: odbc: demo"; try {// loaded drive JDBC-ODBC Bridge driverClass.forName ( "sun.jdbc.odbc.JdbcOdbcDriver"); // establish connection connection con = DriverManager.getConnection (url, "user", "passwd"); // Get DatabaseMetaData object 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 System.out.Print (" / NDatabase Supports Ansi92 Entry Level SQL: "); if (Dma.supportsansi92entrylevelsql ()) System.out. Println ("YES"); ElseSystem.Out.println ("NO"); System.out.print ("Database Supports Ansi92 Full SQL SQL:"); if (Dma.supportsi92fullsql ()) System.out.println (" YES "); ElseSystem.out.printl n ("no");

System.out.print ("Database Supports Stored Procedure:"); if (Dma.supportsStoredProcedProceducedures ()) System.out.Println ("YES"); ElseSystem.Println ("NO");

System.out.print ("Database Supports Outer Join:"); if (Dma.supportsouterJoints ()) System.out.Println ("YES"); ElseSystem.Println ("NO");

// Close connection con.close ();} catch (sqlexception ex) {// processing exception system.out.print ("/ n * * * sqlexception caught * * * / n"); while (ex! = Null) {System.out.println ("SqlState:" ex.GetsqlState (); system.out.println ("message:" ex.getMessage ()); system.out.println ("vendor:" EX. GetErrorCode ()); ex= ex.getnextexception ()); system.out.println ("");}} catch (java.lang.exception ex) {EX.PrintStackTrace ();}} First, we still need to install Enter the drive and establish a connection to the database (lines 8 ~ 13). Next, create a DatabaseMetadata object: 16: DatabaseMetadata DMA = con?GetMetadata (); then, we can use this object to obtain the join URL information, respectively: 19: Dma.getURL () Driven and version number used: 20: 20: Dma.getdriverName () 21: DMA.GetDriverVersion () Database Name: DMA.GetDatabaseProductName () Database Used Specific keywords: 25: Dma.getsqlKeywords () can then use DMA check database: Do you support ANSI 92 Entry Level SQL syntax, supports ANSI92 all SQL syntax, supports stored procedures and support external connections: 29: dma.supportsANSI92EntryLevelSQL () 34: dma.supportsANSI92FUllSQL () 39: dins.SupportsStoredProcedures () 44: dma.supportsOuterJoins () And output "Yes" or "NO" accordingly. Below is the result of executing the DBMETA program when connecting the Access database through JDBC-ODBC Driver. Example 1.8 DBMETA execution results.

C: / MyDemo / MyDB> Java DBMeta Connected to JDBC: ODBC: TEST / / Data Source Driver JDBC-ODBC Bridge (ODBCJT32.DLL) // Database Drive Version.1001 (3.40.2728) Eight Drive Reassembly Number Database Name: Accessz .0 // database name and version number dataBase supports SQL keywords: // SQL keywords ALPHANUMERIC, AUTOINCREMENT, BINARY, BYTE, CHAR, COLUMN, CONSTRAINT, COUNTER, CURRENCY, DATETIME, DISALLOW, DISTINCTROW, FLOAT4, FLOATS, GENERAL, IEEEDOUBLE , IEEESINGLE, INT, INTEGERI, INTEGERZ, iNTEGER4, LEVEL, LOGICAL, LOGICALI, LONG, LONGBINARY, LONGCHAR, LONGTEXT, MEMO, MONEY, NOTE, NUMBER, OLEOBJECT, OPTION, OWNERACCESS, PARAMETERS, PERCENT, PIVOT, REAL, SHORT, SINGLE , SMALLINT, STRING, TABLEID, TEXT, TOP, TRANSFORM, VALUES, VARBINARY, YESNO DataBase supports ANSI92 Entry Level SQL: YES DataBase SupportS ANSI92 Full SQL: NO Database supports Stored Procedure: YES Database supports Outer Join: YES C: / myDemo / MYDB> 1.3.5 JDBC Data Type and Type Conversion

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) Converts string class parameters to date class objects. 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 1.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 1.1 SQL type defined in Types

Type name

meaning

BiGint long-intensive binary binary bits Bit number CHAR characters Date Date Decimal Decimal Double Double Double Accuracy Float Floating - Point Integer Integer Longvarbinary Variable Long Binary LONGVARCHAR ETHER NULL NULL NATION NUMERIC Numeric Other Other Type Real Real SMALLINT Short Time Time Type TimeStamp Timestamp Type Tinyint Micro Interest Varbinary Variable Binary Count VARCHAR Type Character Type

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 general cases, the Java type corresponding to the SQL type is shown in Table 1-2. Table 1.2 Java types corresponding to the SQL type

SQL TYPE

Java Type

CHARjava.lang.StringVARCHARjava.lang.StringLONGVARCHARjava.lang.StringNUMERICjava.lang.BignumDECIMALjava.lang.BignumBITbooleanTINYINTbyteSMALLINTshortINTEGERintBIGINTlongREALfloatFLOATdoubleDOUBLEdoubleBINARYbyte [] VARBINARYbyte [] LONGVARBINARYbyte [] DATEjava.sql.DateTIMEjava.sql.TimeTIMESTAMPjava.sql.Timestamp

Of course, the user can specify the SQL type to convert the SQL type to a particular type of required, without following the table 1.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 1.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 1.3 Get the "get" method used by the SQL type

/ / SQL / Type Name / / / / / get / method name / / / / TINYINTSMALLINTINTEGERBIGINTREALFLOATDOUBLEDECIMALNUMERICBITCHARVARCHARLONGVARCHARBINARYVARBINARYLONGVARBINARYDATETIMETIMESTAMPgetByte * getShort * getInt * GetString ** GetBytes ** GetDate * GetTime GetTimeStamp GetASciistReam 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 1-4 Given the standard for Java data to SQL data in this case.

Table 1.4 The SQL type of the conversion corresponding to the Java type

Java Type

SQL TYPE

java.lang.StringVARCHAR or LONGVARCHARjava.lang.BignumNUMERICbooleanBITbyteTINYINTshortSMALLINTintINTEGERlongBIGINTfloatREALdoubleDOUBLEbyte [] VARBINARY or LONGVARBINARYjava.sql.DateDATEjava.sql.TimeTIMEjava.sql.TimestampTIMESTAMP last case in the use CallableStatement, the user may use INOUT parameters. The conversion process is most complicated. First, use the "SET" series to assign these parameters, the drive uses the standard mapping shown in Table 1.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 1.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 1.12 Parameter conversion flow chart

Finally, it is necessary to note that the type of "GET" method and "set" method of the inout parameter should be consistent, such as the setByte and GetByte methods of parameter 1 in the examples.

chapter summary

This chapter introduces the Java database interface JDBC, which describes the establishment of a database connection, executes the SQL declaration, and obtains the full process of obtaining the results, and lists the correspondence and conversion of the Java data type and database data type. Through this chapter, the reader can master how to use JDBC and Java.sql, including various SQL operations, is especially useful to users of the network database.