DM4 JDBC Programming Guide
JDBC (Java Database Connectivity) is an interface specification for Java applications and databases to allow each database developer to provide a standard database application programming interface (API) for Java programmers. JDBC defines a universal SQL database API cross-database across platforms.
The DM4 JDBC driver is a JDBC driver for the DM4 database. It is a universal low-level application programming interface that supports basic SQL functions, supports general SQL database access.
With the JDBC driver, users can implement connection and access to the DM4 database in the application, and the main features of the JDBC driver include:
Establish a connection to the DM4 database;
Transfer sends SQL statements to the database;
Process and return the statement execution result.
1.1 data type
Java defines the standard SQL data types supported by JDBC in java.sql.types, which are mainly:
Bit - a single binary position;
Tinyint - 1 byte integer;
Smallint - 2 byte integers;
Integer - 4 byte integers;
Bigint - 8 byte integers;
Float - 4 bytes floating point;
REAL - 4 bytes floating point;
Double - 8 bytes floating point;
Numeric - Accurate integersion with accuracy and scale;
Decimal - Accurate floating point with accuracy and scale;
CHAR - a string of fixed length;
VARCHAR - a variable length string;
Longvarchar - a variable length string;
Date - date type;
Time - Time type;
TimeStamp - Timestamp type;
Binary - binary data of fixed length;
Varbinary - binary data of variable length;
Longvarbinary - binary data of variable length;
BLOB - binary large object;
Clob - Character large object;
The DM4 SQL data type is slightly different, for example, DM4 does not support certain types, but it has expanded some types of multimedia data types, which are not one or one or one. In addition, Java also includes a set of data types similar to but slightly different. Therefore, the three must be a mapping in the driver. The corresponding relationship between the standard SQL data type, the DM4 SQL data type, and the Java data type, as shown in Table 1.1.1.
Table 1.1.1: Data Type Correspondence Table
Standard SQL Data Type DM4 SQL Data Type Java Data Type
Char char string
Varchar varchar string
Longvarchar text string
Clob Text Java.sql.clob
Boolean Bit Boolean
Bit bit boolean
Tinyint Tinyint Byte
Smallint Smallint Short
Integer Integer INT
Bigint Bigint Long
REAL FLOAT FLOAT
Float float float
Double Double Double
Numeric Decimal Java.math.bigDecimal
Decimal Decimal Java.math.bigDecimal
Binary binary Byte []
Varbinary Varbinary Byte []
Longvarbinary blob Byte []
Blob blob java.sql.blob
Date date java.sql.date
Time Time Java.sql.TimetimeStamp DateTime Java.sql.TimeStamp
1.2 Objects and interfaces provided
The DM4 JDBC driver has two JDBC drivers, and the DM4 JDBC 2.0 driver meets the JDBC2.1 standard, which implements all of the interfaces that must be implemented in JDBC2.1 core API:
Java.sql.driver
Java.sql.connection
Java.sql.statement
Java.sql.preparedStatement
Java.sql.callablestatement
Java.sql.resultset
Java.sql.resultsetMetadata
Java.sql.DatabaseMetadata
At the same time, it also implements the partial interface of the JDBC2.1 extension (optional) API:
Javax.sql.datasource
Javax.sql.connectionEvent
Javax.sql.connectionEventListener
Javax.sql.connectionPoolDataSource
Javax.sql.pooledConnection
JDBC 3.0 specification is no longer distinguishing between the core API and the extension (optional) API, which includes both APIs into and add some new features. The DM4 JDBC 3.0 driver complies with the JDBC3.0 standard, which is compatible with DM4 JDBC 2.0, implements the following interfaces required by the JDBC3.0 specification:
Java.sql.driver
Java.sql.connection
Java.sql.statement
Java.sql.preparedStatement
Java.sql.callablestatement
Java.sql.resultset
Java.sql.resultsetMetadata
Java.sql.DatabaseMetadata
Java.sql.blob
Java.sql.clob
Java.sql.prametermetadata
Java.sql.savepoint
Javax.sql.datasource
Javax.sql.connectionEvent
Javax.sql.connectionEventListener
Javax.sql.connectionPoolDataSource
Javax.sql.pooledConnection
Currently DM4 provides two drivers for JDBC 2.0 and JDBC 3.0.
1.2.1 DriverManager
1 Overview
The DriverManager class is the management of JDBC, which acts between users and drivers. It tracks the available drivers and establishes a connection between the database and the corresponding driver. In addition, the DRIVERMANAGER class also handles display such as driver login time limits and login and tracking messages.
For simple applications, general programmers only need to use GetConnection to establish connections to the database.
2. Load DM4 JDBC driver
The DM4.jdbc.driver.dm4driver class contains a static part that creates an instance of this class. When the driver is loaded, the driver automatically calls DriverManager.RegisterDriver methods to register yourself to DriverManager. The driver is explicitly loaded by calling the method class.Forname (String Str). The following code loads the DM4 JDBC driver:
Class.Forname ("DM4.jdbc.driver.dm4driver");
3. Establish a connection
Load the DM4 JDBC driver and register in the DRIVERMANAGER class, you can use to connect to the database. The DriverManager object provides three ways to establish a database connection. Each method returns a Connection object instance, the difference is different from the parameters. Connection DriverManager.getConnection (String Url, Java.util.properties Info);
Connection DriverManager.getConnection (String URL);
Connection DriverManager.getConnection (String Url, String User, String Password);
A database connection is usually performed by specifying a database URL, a username, a password, which is used to connect to a database. The grammatical structure of the DM4 JDBC URL see Section 1.2.2.
1.2.2 Driver
1 Overview
Driver object represents the driver object. The DM4 JDBC driver DRIVER class's implementation class is DM4.jdbc.driver.dm4driver. The version number of the JDBC driver is provided in the DRiver object, whether it is a function of driver information such as JDBC CompliantTM and URL format, connecting to a database.
2. Main method introduction
(1) Public Boolean Acceptsurl (String Url) Throws Sqlexception
This method returns a Boolean value that explains the sub protocol in the URL string represented by the parameter URL and whether the driver matches the convention for the sub-protocol, that is, whether it can be established to the database through this URL.
JDBC identifies a URL address when a database is used, and the syntax structure of the JDBC URL is as follows:
JDBC: : //
Among them, the sub-protocol is used to identify different drivers, and the sub-names typically contain host names, port addresses, database names, attribute settings, and more.
The DM4 JDBC driver uses DM4 as a sub-protocol, and the child name is:
For example, suppose the host name of the DM4 database server is bpx, its IP address is 223.254.254.100, using port 12345, the logged in database is TEST, the corresponding URL is:
JDBC: DM4: // BPX: 12345 / TEST
Or JDBC: DM4: // 223.254.254.100:12345/test
Port 12345 is the default port, the default port may omit, that is, simplified to:
JDBC: DM4: // BPX / TEST
Or JDBC: DM4: //223.254.254.100/test
Each user has a default database. If Test is the default database, you can omit, that is, simplified to:
JDBC: DM4: // BPX
Or JDBC: DM4: //223.254.254.100
If it is a database server that is connected to this unit, use localhost instead of the host name.
The port number used by the DM4 server, please consult the DM4 system administrator to the DM4 system administrator.
(2) Public Connection Connect (String Url, Java.util.properties INFO)
This method is used to establish a physical connection with a given database. The first parameter is used to specify the JDBC URL of the connected database. The second parameter is a Properties object that contains the properties required to connect a particular database, usually including usernames and passwords. 1.2.3 Connection
1 Overview
The Connection object represents the connection to the database. The connection process includes the resulting SQL statement and the result returned on the connection. An application can have one or more connections with the database. Since the establishment of the connection is high, the connection object should be reused as much as possible in the application.
2. Open the connection
The standard method for establishing a connection with the database is to call the DriverManager.getConnection method. This method accepts a string containing the URL. The following code establishes a connection to the database:
Class.Forname ("DM4.jdbc.driver.dm4Driver"); / / Loading Driver
String Url = "JDBC: DM4: //223.254.254.19"; // Host IP = 223.254.254.19
String userid = "sysdba";
String passwd = "sysdba";
Connection Con = DriverManager.getConnection (URL, UserId, Passwd);
3. Send SQL statement
Once the connection is established, you can use to transfer the SQL statement to the database server. JDBC does not add any restrictions on the type of SQL statement that can be sent. This provides a lot of flexibility, that is, allowing a specific database statement or even a SQL statement. However, it requires the user to be responsible for ensuring that the database server can handle the sent SQL statement.
JDBC provides three classes to send SQL statements to the database. Three methods in the Connection interface can be used to create instances of these classes. These classes are listed below and their creation methods:
Statement: Created by method CreateStatement, used to send a simple SQL statement.
PREPAREDSTATEMENT: The SQL statement used by the method PreparedStatement is used to send a SQL statement with one or more input parameters (IN parameters). PreparedStatement has a group of methods for setting the value of the IN parameter. These IN parameters will be sent to the database server when performing statements. PreparedStatement inherits from STATEMENT, thus includes all methods of Statement. The PreparedStatement object may be higher than the STATEMENT object, as it has been precompiled and stored there for future use.
CallableStatement: Created by method prepareCall used to perform SQL stored procedures - a set of SQL statements that can be called by name. The CallableStatement object inherits the method of processing the IN parameter from PreparedStatement, and also adds a method for processing OUT parameters and inout parameters.
It is worth noting that CreateStatement, preparedStatement, prepareCall method in the DM4 JDBC driver does not support the statement object of creating a Type_Scroll_Sensitive type.
4. Transaction
By default, the new connection will be in automatic submission mode. That is, after a statement executes the character sentence, the system will automatically submit or roll back. Automated submission mode can be disabled or enabled by setAutocommit methods. If automatic submission mode is disabled, the transaction will end after the transaction will end after the COMMIT or ROLLBACK method is explicitly called, so it will submit or roll back all the statements that have been implemented in the last Commit or RollBack method. Methods Commit makes any changes made by the SQL statement to the database, which will also release all locks held by the transaction. The method ROLLBACK will drop those changes. In automatic submission mode, the COMMIT or ROLLBACK method should not be explicitly called, otherwise the exception will be thrown.
3.0 The support for logical transaction points is implemented in the driver. The logical transaction point is a logical location that divides the transaction into multiple phases. This allows the transaction to phase control, when rolled back to a logical transaction point, other logical transaction points set after the logical transaction point will be invalid, not before the logical transaction point Transaction operation.
It is worth noting that when executing DDL, DCL statement, DM4 is likely to implicitly submit or rollback operations (whether in auto-commit status or non-automatic submission state). Its effect is the same as explicit calls of the CommT or Rollback method. For the explicit or implicit end of the transaction, please refer to the "DM4_SQL Language Manual" related sections.
Sometimes the user does not want this to take effect before another change. This can be achieved by disabling automatic submission and combining two updates in a transaction. If the two updates are successful, the commit method can call, so that the two update results become permanent; if one or two updates fails, the rollback method should be called to restore the value to be updated. Value.
Table 1.2.1: Method Description Table
Method name function description
getDefaultrowPrefetch gains the number of meta groups obtained from the server from the server by default.
GetSocketTimeout gets the longest time required to establish a database connection.
GetConnectTimeout gets the longest time that the connection is in an idle state.
GETESCAPESPROCESSING gets the server defaults to perform escape processing.
GetContinueBatchONError gets the statement after the statement occurs during the batch processing.
GetusesqlState gains the default SQLState type.
SetDefaultrowPrefetch Sets the number of metabits acquired from the server.
SetSocketTimeout Sets the maximum time required to establish a database connection.
SetConnectTimeout Sets the longest time to connect to the idle state.
SetScapeSProcessing Set whether the server is essential.
SetContinueBatchONError Settings If there is an abnormality in the batch processing, do you continue to perform the statements behind this statement.
SetusesqlState Set the SQLState type used.
SetCliencoding (String Encoding) Sets the encoding used by the client
GetCliencoding () gets the encoding used by the client
GetCliencodingId () gets the code code used by the client.
GetServerutf8Encoding () gets the server to use the UTF-8 as information storage coding
When calling the automatic submission mode of the setAutoCommit switching connection, if the automatic submission state is changed from the automatic submission state, the system will implicate a submission operation. If the user needs to actively submit or rollback if the non-automatic submission state is automatically submitted. Therefore, call setAutocommit is best performed immediately after the connection is established.
The transaction isolation level can be set via the SetTransactioniSolation method.
1. Results Specialty
JDBC 3.0 allows the user to call StholdAbility to specify whether to turn off the result set after submission or rollback operation, facilitate the user segmentation operation result set. If the user wants to operate multiple result sets at the same time, use different statement objects to generate a result set, because DM4 does not support a statement to generate a plurality of result sets. 6. Turn off the connection
By calling the Close will close the connection to the database server, release the resources of the database server. Since the database server establishes and maintains a connection overhead, if the application does not perform any database operation, it is recommended to turn off the connection to improve the performance of the database server. It is worth noting that when the connection is in a non-self-commit mode, it is necessary to explicitly submit or rollback transactions before the connection is closed, otherwise, the data that is not submitted may be lost.
7. Custom Method List
In order to further control the properties of the connection object, some new custom methods are added during the implementation of the Connection. These custom methods can be accessed across the DMDBConnection type to the DMDBConnection type.
In addition, DM4 does not support the standard methods required in the JDBC specification, setTypeMap, SETTYPEMAP.
1.2.4 Statement
1 Overview
The STATEMENT object is used to send SQL statements to the database server. DM4 JDBC provides three types of statement objects: Statement, PreparedState, CallableStatement. Where PREPAREDSTATEMENT is a subclass of Statement, CallableStatement is a subclass of PreparedStatement. Each statement object is used to run a specific type of SQL statement.
STATEMENT objects are used to run simple type SQL statements, and no specified parameters are required in statements.
The PreparedStatement object is used to run a precompiled SQL statement containing (or not) in type parameters.
The CallableStatement object is used to call the database stored procedure.
2. Create a Statement object
After establishing a connection, the Statement object is created with the CreateStateMent method of the Connection object. The following code creates a Statement object:
Connection Con = DriverManager.getConnection (URL, "Sysdba", "Sysdba");
Statement Stmt = con.createstatement ();
3. Using the Statement object to execute a statement
The STATEMENT interface provides three ways to perform SQL statements: ExecuteQuery, ExecuteUpdate, and EXECUTE.
Method EXECUTEQUERY is used to generate a single result set statement, such as a SELECT statement.
Methods EXECUTEUPDATE are used to perform INSERT, UPDATE, or DELETE statements, and SQL DDL statements such as CREATE TABLE and DROP TABLE. The effect of INSERT, UPDATE or DELETE statement is to modify a column or multiple columns in a table in a table. The return value of ExecuteUpdate is an integer that represents the number of rows affected. For the DDL statement such as CREATE TABLE or DROP TABLE, the return value of ExecuteUpdate is always zero.
Methods Execute are used to perform statements that return multiple result sets, multiple update metabits, or two combinations.
The three methods of performing statements will close the current open result set (if present) of the called Statement object. This means that the processing of the current Resultset object is required before re-executing the Statement object.
4. Close Statement object
The STATEMENT object can be automatically turned off by the Java garbage collection program. But as a good programming style, it should be explicitly shut down without the statement object. This will immediately release the database server resources to help avoid potential memory issues. 1. Performance optimization adjustment
(1) Batch update
The DM4 JDBC driver provides a batch update feature that can be executed in a single statement collection at a time by batch update. JDBC provides three ways to support batch updates: add a statement to batch statements through the addbatch method; perform batch updates through ExecuteBatch; clear the batch statement set through Clearbatch.
During the batch update process, if the exception occurs, the DM4 will immediately exit the execution of the batch, and return the number of update metabits that have been executed (in Auto Submit mode).
Recommended batch updates are performed in non-automatic submission mode of transactions. At the same time, after the batch update is successful, it is necessary to actively submit to ensure the permanent of the transaction.
(2) Performance optimization parameter settings
The DM4 JDBC driver provides setFetchDirection, setFetchSize to imply the default gaining direction of the user operating statement result set and the number of default meta-acquisitions. The settings of these values can provide a reference for driver optimization.
6. Statement object
JDBC When you create a statement object, you can specify the default attribute of the statement object: the result set type and result set concurrent type. DM4 JDBC driver supports Type_forward_only and type_scroll_insensitive two result set types, does not support type_scroll_sensitive result set type; support Concur_Read_only, concur_updatable two result set concurrent types.
The default attribute of the statement object is used to specify the default type of the result set generated by the execution statement, the specific meaning and usage of the 1.2.7 "Result Enhancement Characteristics section". It is worth noting that when the executed query statement is involved in the DM4 JDBC driver involves multiple base tables, the result set cannot be updated, and the type of result set may be automatically converted to the Concur_Read_only type.
1.2.5 preparedStatement
1 Overview
PreparedStatement inherits Statement and differ from it in both:
The PreparedStatement object contains the compiled SQL statement, and the statement is "ready".
The SQL statement included in the PreparedStatement object can have one or more IN parameters. The value of the IN parameter is not specified when the SQL statement is created. Instead, the statement retains a question mark ("?") As a placeholder for each IN parameter. The value corresponding to each question mark must be provided by the appropriate setxxx method before the statement is executed.
Since the PreparedStatement object has been precompiled, its execution speed is faster than the Statement object. Therefore, the SQL statement that requires multiple repeated execution frequently creates a preparedStatement object to increase efficiency.
As a STATEMENT subclass, PreparedStatement inherits all features of Statement. In addition, it also adds a set of methods to set the value sent to the database to replace the IN parameter placeholder. At the same time, three methods Execute, ExecuteQuery and ExecuteUpdate can perform statements objects for setting the parameters.
2. Create a PreparedStateMent object
The following code segment (where CON is Connection object) Creates a preparedStatement object:
PreparedStatement PSTMT = Con.Preparestatement
"UPDATE VERM registration SET manufacturer name =? WHERE manufacturer number =?"); Object PSTMT contains statement "UPDATE VERM registration SET manufacturer name =? WHERE manufacturer number =?", This statement with two IN parameter placeholders, it has Send it to the database and prepare it by the server.
3. Pass in parameters
You must set the value of each parameter before executing the PreparedStateMent object. This can be done by calling the setxxx method, where xxx is a type corresponding to this parameter. For example, if the parameter has a Java type String, the method used is setString. For different types of parameters, there is generally a recommended setting method and a plurality of possible setup methods. For each different type of corresponding relationship with its setup method, please refer to 1.2.10 Data Type Access this section. The first parameter of the setXXX method is the serial number of the parameters to be set (from 1), and the second parameter is the value set to this parameter. For example, the following code sets the first parameter to "China Television Plant", the second parameter is set to "B0A01":
PSTMT.SetString (1, "China TV Factory");
PSTMT.SetString (2, "b0a01");
This statement can be executed whenever a parameter value of a given statement is set. Before setting a set of new parameter values, call the ClearParameters method to clear the original setting parameter value.
4. Using SETOBJECT
The SetObject method can explicitly convert the input parameters to a specific JDBC type. This method can accept three parameters, where the third parameter is used to specify the target JDBC type. Before sending Java Object to the database, the driver converts it to the specified JDBC type.
If you do not specify a JDBC type, the driver maps Java Object to its default JDBC type and sends it to the database. This is similar to the conventional setxxx method. In both cases, the driver maps the Java type of the value to the appropriate JDBC type before sending the value to the database. The difference between the two is that the SetXXX method uses a standard mapping from the Java type to the JDBC type, and the SETOBJECT method uses mappings from the Java Object type to the JDBC type.
Method SETOBJECT allows all Java objects, which makes the application more common and accepts the input of parameters at runtime. Thus, if the user does not determine the input type when editing the application, you can give an acceptable Java object by using SETOBJECT, and then automatically convert it to the JDBC type required by the JDBC driver. However, if the user has clearly the input type, use the corresponding setxxx method to be recommended to improve efficiency.
1. Send JDBC NULL as the IN parameter
The SetNull method allows the programmer to send the JDBC NULL value as the IN parameter to the database. In this case, the target JDBC type of the parameter can be specified as any value, and the target accuracy of the parameters is no longer played.
6. Send a large IN parameter
SetBytes and setString methods can send unlimited data. However, memory is enough to accommodate relevant data. Sometimes programmers prefer to deliver large data with smaller blocks, which can be done by setting the IN parameter to Java input stream. When the statement is executed, the JDBC driver will repeat the input stream to read its contents and use them as actual parameter data transmission.
JDBC provides four ways to set IN parameters to input streams: SetBinaryStream is used for byte streams, setasciistream is used for ASCII character streams, setUnicODestream is used for Unicode characteristics, from JDK1.2, input word streaming new method for setcharacterStream And SetAasciistream and SetUnicODestream have little use. 7. Get parameter metadata
DM4 JDBC 3.0 implements a getParameterMetadata () method, and through this method, various attribute information, such as type, precision, scale, etc., such as type, accuracy, scale, etc. With this information, the user can set the value of the IN parameter more accurately. `
This method is involved in the following code:
PreparedStatement PSTMT = Conn.PrepareStatement ("SELECT * FROM BOOKLIST" "Where ISBN =?");
...
/ / Get parameter metadata objects
ParameterMetadata PMD = PStmt.getParameterMetadata ();
// Number of getting parameters
INT paramcount = pstmt.getParametercount ();
// Get the type of the first parameter
INT colType = pmd.getParameterType (1);
...
8. Custom Method List
In order to achieve the time interval type provided by Dream Database and the Time Type of Na, Some custom extension methods are added to implement the PreparedStateMent interface. These methods can be accessed across the DMDBPReparedStaTMent type of PreparedStatement objects. For these methods, please refer to the 1.2.16 expansion class.
Table 1.2.2: Custom Method List
Method name function description
SetInterValym Sets the DMDBINTERVALYM type value of the parameter.
SetIntervaldt Set the DMDBINTERVALDT type value of the parameter.
Settime Sets the DMDBTIME type value of the parameter.
In addition, setARRAY, SETURL, and SetRef methods are not supported in DM4.
1.2.6 Callablestatement
1 Overview
CallableStatement is used to run the SQL stored procedure. The stored procedure is the SQL statement already existing in the database, which is called through the name.
CallableStatement is a subclass of PreparedStatement. The method defined in CallableStatement is used to handle the output section of the OUT parameter or inout parameter: the JDBC type of the OUT parameter (general SQL type), retrieve the result from these parameters, or check if the returned value is JDBC NULL.
2. Create a CallableStatement object
The CallableStatement object is created with Connection.PrepareCall. The following code creates a callablestatement object with a call to the stored procedure P1, and CON is a connection object:
CallableStatement Cstmt = Con. PrepareCall ("Call P1 (?,?)");
Where "?" Placeholders are in, but OUT is also inout parameters, depending on the stored procedure P1.
3. IN and OUT parameters
Pass the in parameter to the CallableStatement object is done through the setxxx method. This method inherits from preparedStatement. The type of incoming parameters determines the setxxx method used (for example, with setstring to pass the String value, etc.). If the stored procedure returns an OUT parameter, you must register the JDBC type of each OUT parameter before performing the CallableStatement object, and some parameters also provide the scale. The registration of the JDBC type is done with the RegisterOutParameter method. After the statement is executed, the CallableStatement's getxxx method will retrieve the parameter. Where XXX is a Java type corresponding to the JDBC type registered with each parameter. In other words, REGISTEROUTPARAMETER uses a JDBC type (so it matches the JDBC type returned by the database), and Getxxx is converted to the Java type.
The definition of the storage procedure P1 is as follows:
Create Or Replace Procedure P1 (A1 in Char (5), A2 Out Char (20)) AS
Declare Cur1 Cursor for
SELECT manufacturer name
From manufacturer registration
WHERE Vendor Number = A1;
Begin
Open CUR1;
FETCH CUR1 INTO A2;
END;
The following code first registers the OUT parameter, execute the stored procedure called by the CSTMT, and retrieve the value returned by the OUT parameter. Method getString removes strings from the OUT parameter:
CallableStatement Cstmt = Con. PrepareCall ("Call P1 (?,?)");
CSTMT.SetString (1, "b0a05");
Cstmt.registerOutparameter (2, java.sql.types.varchar);
Cstmt.executeUpdate ();
String x = cstmt.getstring (2);
4. Inout parameters
If the parameter is a parameter type (INOUT parameter) that does both both the input and the output is accepted, the corresponding setxxx method is called in addition to the registeroutparameter method. SetXXX method Set the parameter value to the input parameters, and the RegisterOutParameter method registers its JDBC type as the output parameter. The SetXXX method provides a Java value, the driver first converts this value to the JDBC value, then send it to the database server.
The JDBC type of the IN value and the JDBC type provided to the RegisterOutparameter method should be the same. If you want to retrieve the output value, you must use the corresponding getxxx method.
The definition of a stored procedure P2 is as follows:
Create or Replace Procedure P2 (A1 Inout Char (20)) AS
Declare Cur1 Cursor for
SELECT manufacturer name
From manufacturer registration
WHERE Vendor Number = A1;
Begin
Open CUR1;
FETCH CUR1 INTO A1;
END;
In the following code, the method setString sets the parameters to "B0A05". Then, RegisterOutparameter registers this parameter as JDBC VARCHAR. After performing the stored procedure, a new JDBC VARCHAR value will be returned. Method GetString will return this new value as a String type of Java.
CallableStatement cstmt = con.preparecall ("Call P2 (?)"); Cstmt.setstring (1, "b0a05");
Cstmt.registerOutparameter (1, java.sql.types.varchar);
Cstmt.executeUpdate ();
String x = cstmt.getstring (1);
1. Use the parameter name to operate
In normal cases, the parameter index is generally used for assignment. JDBC3.0 specification requirements can be assigned to parameters using parameter names, and the DM4 JDBC 3.0 driver implemented this. If some of the parameters of a stored procedure have default, it is very useful to assign a value using the parameter name, and the user can assign only those parameters without the default. The parameter name can be obtained by calling DatabaseMetadata.getProcedureColumns ().
In the following code, the stored procedure P2 is the stored procedure P2 above. Use the parameter name to operate:
CallableStatement cstmt = con.preparecall ("{Call P2 (?)}");
CSTMT.SetString ("A1", "B0A05");
CSTMT.RegisterOutparameter ("a1", java.sql.types.varchar);
Cstmt.executeUpdate ();
String x = cstmt.getstring ("a1");
Moreover, when reading the value of the parameter and the parameter registration, setxxx, getxxx, registeroutparameter can also use the parameter name. By calling the DatabaseMetadata.supportsNameDparameters () method, you can determine if the JDBC driver supports the use of parameter names.
Note: In the process of performing the same statement, the cross-use parameter index and parameter name are not allowed, otherwise the exception will be thrown.
6. Custom Method List
In order to realize the time interval type provided by Dream Database and the time type with nanosecond, some custom extension methods are added to implement the CallableStatement interface. These methods can be accessed across the DMDBCALLABLESTAMENT type to the CallableStateMent object type. For these methods, please refer to the 1.2.16 expansion class.
Table 1.2.3: Custom Method List
Method name function description
GetIntervalyM (int) gets the DMDBINTERVALYM type value of the specified column.
GetInterValyM (String) Gets the DMDBintervalym type value of the specified column.
GetIntervaldt (int) Get the DMDBintervaldt type value of the specified column.
GetInterValdt (String) Gets the DMDBINTERVALDT type value of the specified column.
getTime (int) Get the DMDBTIME type value of the specified column.
GetTime (string) Get the DMDBTIME type value of the specified column.
Settime (String, DMDBTIME) Sets the DMDBTIME type value according to the parameter name.
SetInterValdt (String, DMDBINTERVALDT) Sets the DMDBINTERVALDT type value based on the parameter name.
SetIntervalyM (String, DMDBINTERVALYM) Sets the DMDBINTERVALYM type value according to the parameter name.
In addition, the method is not supported in DM4 GetArray, Seturl, GetURL, GetRef. Since CallableStatement is a subclass of PreparedStatement, it will inherit all methods of PreparedStatement. 1.2.7 resultset
1 Overview
Retset provides a method of obtaining data from the database to the result of the database after executing the SQL statement. After executing the SQL statement, the database return result is processed into a result set object by JDBC. You can use the NEXT method of the ResultSet object to browse in behavioral units, with the Getxxx method to remove a list of a column of the current row.
Three different types of statements with Statement, PreparedStatement, both different types of statements can be queried to return to the RESULTSET type object.
2. Row and cursor
ResultSet maintains the logical cursor of its current data line. Each time the next method is called, the cursor moves down. Initially, it was located before the first line, so the first call next time, the cursor will be placed on the first line, making it a current line. As the NEXT that causes the NEXT to cause the cursor to move down, obtain the Resultset line according to the order from the top.
The cursor remains valid until the ResultSet object or the corresponding statement object is turned off.
3. Column
Method GetXXX provides a way to get a column value in the current line. In each line, you can get column values in any order.
Column names or column numbers can be used to identify columns to get data from it. For example, if the second column name of the ResultSet object RS is "Title", the following two methods can get the values stored in the column:
String s = rs.getstring ("Title");
String s = rs.getstring (2);
Note that the column is numbered from left to right and starts from 1.
In the DM4 JDBC driver, if the column is in the form of a "table name. Column name". In the absence of confusion (the result set has two tables with the same column name), the table name can be omitted, and the column name can be omitted directly.
About the information in the resultSet can be obtained by calling the method ResultSet.getMetadata. The returned ResultSetMetadata object will give the name, type, and other properties of its ResultSet object.
4. Get the column value using a flow mode
In order to obtain a column of large data, the DM4 JDBC driver provides four ways to get flow:
GetBINARYSTREAM returns only the stream that only provides database origin without any conversion.
GetasciistReam returns a stream that provides a single-byte ASCII character.
GetUnicODestream returns a stream that provides a double-byte Unicode character.
GetCharacterStream returns a java.io.reader stream that provides a double-byte unicode character.
In these four functions, the JDBC specification is not recommended to use the GetCharacTream method, which features GetUnicodestream instead.
1. NULL results value
To determine if a given result value is a JDBC NULL, you must first read the column, then use the ResultSet object's WASNULL method to check whether the read is returned to JDBC NULL.
When you read the JDBC NULL using the getxxx method of the ResultSet object, one of the following values will be returned:
Java NULL: For the Getxxx method that returns a Java object (such as getString, getBigDecimal, getBytes, GetDate, GetTime, GetTimeStamp, Getasciistream, getOndestream, getBinaryStream, getObject, etc.).
Zero: For getByte, getshort, getint, getlong, getfloat, and getdouble. FALSE value: For getBoolean.
6. Result set enhancement characteristics
The resulting set enhancement characteristic of the JDBC 2.0 standard is provided in the DM4 JDBC driver: can be scrolled, update the result set. And the holdability of JDBC3.0 standards.
(1) Rollingability of the result set
The result set created by executing statements not only supports forward (from the first line to the last line) to browse content, but also supports the ability to browse the content backward (from the last row to the first line). The result set supporting this capability is called the rolling result set. The rolling result set also supports relative positioning and absolute positioning. The absolute positioning refers to the ability to move directly to a row by specifying the absolute position in the result set, and relatively positioning refers to the ability to move to a row by specifying the location of the current row. DM4 supports rolling result sets.
The DM4 JDBC driver supports only two result set types of only the forward rolling set set (ResultSet.Type_forward_only) and scrolling insensory result set (ResultSet.Type_Scroll_InSitive), does not support scrolling sensitive result sets (ResultSet.Type_Scroll_Sensitive). When the result set is a rolling insensitive result set, it provides a static view of the basic data, that is, the result of the result of each line of the results, the column value is usually fixed.
(2) Uproidity of the result set
Two result sets are provided in the DM4 JDBC driver: read-only result set (ResultSet.concur_read_only) and can update result set (ResultSet.concur_updata). The result set of read-only concurrent types is not allowed to update its content. The updateable result set supports the update operation of the result set.
(3) Holder
JDBC 3.0 provides two result sets to hold type: Submit Closure result set (ResultSet.close_cursors_at_commit) and rogue set submission (ResultSet.Hold_Cursors_over_Commit). The result set of submitting closing results set is closed after transaction, and the result set of cross-results set submission type can remain open after transaction.
By the DatabaseMetadata.supportsholdAbility () method, you can determine if the driver supports the holdability of the result set. Currently DM4 supports these two types.
(4) Performance optimization
MethodSetFetchDirection and SetFetchSize are provided in the result set object of the DM4 JDBC driver to set the direction of the default retrieval result set and the number of records obtained from the database from the database. The meaning of their meaning is the same as the same name function in the usage and statement object.
7. Update large object data
From the DM JDBC 2.0 driver, support the updatable result set, but only the LOB object can only be read, and this is also specified in the JDBC 2.0 standard. The JDBC 3.0 specification specifies that users can update the LOB object, and this is implemented in the DM4 JDBC 3.0 driver:
Statement Stmt = Conn.createStatement (ResultSet.Type_forward_only,
ResultSet.concur_updatable;
ResultSet RS = Stmt.executeQuery ("SELECT Comment from Booklist"
"where isbn = 140185852");
rs.next ();
Clob CommentClob = New Clob (...);
Rs.UpdateClob ("Author", CommentClob); // CommentClob Is a Clob Object
Rs.Updaterow ();
8. Custom Method List In order to implement the time interval type provided by Dream Database and the Time Type of Naoseconds, some custom extension methods are added in the process of implementing the ResultSet interface. You can access these methods across the RESULTSET object to DMDBRESULTSET types. For these methods, please refer to the 1.2.16 expansion class.
Table 1.2.4: Custom Method List
Method name function description
GetIntervalyM (int) gets the DMDBINTERVALYM type value of the specified column.
GetInterValyM (String) Gets the DMDBintervalym type value of the specified column.
GetIntervaldt (int) Get the DMDBintervaldt type value of the specified column.
GetInterValdt (String) Gets the DMDBINTERVALDT type value of the specified column.
getTime (int) Get the DMDBTIME type value of the specified column.
GetTime (string) Get the DMDBTIME type value of the specified column.
UpdateIntervalyM (INT, DMDBINTERVALYM) Sets the specified column to the DMDBINTERVALYM type value.
UpdateInterValyM (String, DMDBINTERVALYM) Sets the specified column to the DMDBINTERVALYM type value.
UpdateIntervaldt (int, dmdbintervaldt) Sets the specified column to the DMDBINTERVALDT type value.
UpdateInterValdt (String, DMDBINTERVALDT) Sets the specified column to the DMDBINTERVALDT type value.
UpdateTime (int, dmdbtime) Sets the specified column to the DMDBTIME type value.
UpdateTime (String, DMDBTIME) Sets the specified column to the DMDBTIME type value.
In addition, GetArray, GetURL, GetRef method is not supported in DM4.
1.2.8 ResultSetMetadata
1 Overview
ResultSetMetadata provides many ways to read the RESULTSET object returns meta information. Including: Column name, column data type, column belongings, and whether columns allow for NULL values, etc., through these methods, some of the information columns can be determined.
2. Create a result of the result of the semi-data object
The resulting metadata is the feature of the result set, so it is necessary to first perform the query obtaining result set, to create a result of the resulting element data object.
3. Create a ResultSetMetadata object as shown in the following example:
If there is a table testtable (no int, name varchar (10)), you can know the type of each column of this table with the following code:
ResultSet RS = Stmt.executeQuery ("SELECT * from testtable");
ResultSetmetaData RSMD = rs.getMetadata ();
For (int i = 1; i <= rsmd.getcolumncount (); i )
{
String TypeName = rsmd.getColumnTypename (i);
System.out.println ("" TypeName); " Typename);
}
1.2.9 DatabaseMetadata
1 Overview
DatabaseMetadata provides a number of methods for obtaining metadata information for the database. Includes: Describe the information of the database feature (if supported by multiple result sets), directory information, mode information, table information, table permission information, table column information, stored procedure information, etc. DatabaseMetadata has some ways to return results in the form of a ResultSet object, and you can get the required data with the getxxx () method of the ResultSet object. 2. Create a database metadata object
Database metadata objects are created by the connection object. The following code creates a DatabaseMetadata object (where CON is the connection object):
DatabaseMetadata dbmd = con.getMetadata ();
Use the database metadata object to get some information about the database and JDBC drivers:
String DatabaseName = dbmd.getdatabaseProductName (); // Database product name
INT Majorversion = dbmd.getjdbcmajorversion (); // JDBC driver main version number
String [] Types = {"Table"};
ResultSet tablesinfor = dbmd.gettables (NULL, NULL, "* TE%", TYPES);
1.2.10 Data Type Access
1. Overview
The access to the data is mainly (1) Setting through the setxxx method of the PreparedStatement object, the setXXX method of the CallableStatement object, and the Updatexxxx () method of the ResultSet object; (2) Retrieve the getxxx () method of the ResultSet object by the CallableStatement object. At the same time, in order to support DM4-specific data types, the DM4 JDBC driver has added some special extension methods.
2. SetXXX methods or updatexxx methods that can be used for each type
Please see Sun's JDBC 3.0 Specification Document on page 186 of Table-6. Generally, just change the getxxx inside to setXXX or Updatexxx.
3. Getxxx method available in each type
Please see Sun's JDBC 3.0 Specification Document on page 186 of Table-6.
4. Support for DM4 special types
In order to access DM4-specific data types in the DM4 JDBC driver, we add many custom extension methods in DMDBPRepareDStatement, DMDBCalLableStatement, and DMDBResultSet classes. If users want to use these methods, they must enforce objects to DMDBXXX types:
PreparedStatement PSTMT = Connection.PrepareStatement ("INSERT INTO TESTINTERVAL VALUES);
(DMDBPReparedStatement) PSTMT.SetIntervalym (1, "Interval '0015-08' Year to Month");
INT updateCount = pstmt.executeUpdate ();
The use of other methods is similar to this.
DMDBPReparedStatement class added:
Table 1.2.5: Method Meter
Method name function description
SetIntervalyM (int, string) set parameters as the year - month time interval type value
SetInterValdt (int, string) Setting parameters as day - time interval type value
SetTime (int, string) Setting parameter is time type (with - nanosecond)
These types can also be operated in SETOBJECT (INT, OBJECT) and SETSTRING (INT, STRING). The DMDBCALLABLESTATEMENT class has been added:
Table 1.2.6: Method menu
Method name function description
GetIntervalym (int): String gets the value of the parameter
GetIntervalym (String): String gets the value of the parameter
GetIntervaldt (int): String gets the value of the parameter
GetInterValdt (string): String gets the value of the parameter
GetTime (int): String gets the value of the parameter
GetTime (string): String gets the value of the parameter
SetInterValdt (string, string) Setting parameters as the year - month time interval type value
SetIntervalyM (String, String) Setting parameters as day - time interval type value
Settime (string, string) Setting parameters Time type (with nanosecose)
SetObject (String, Object), SetString (String, String), GetObject (int), getObject (string), getString (int), getString (string) can also operate these types.
The DMDBRESULTSET class added:
Table 1.2.7: Method Meter
Method name function description
GetIntervalym (int): String gets the value of the column
GetIntervalym (String): String Gets the value of the column
GetIntervaldt (int): String Gets the value of the column
GetInterValdt (string): String gets the value of the column
GetTime (int): String Gets the value of the column
GetTime (string): String gets the value of the column
UpdateInterValdt (int, string) settings are listed as the year - month time interval type value
UpdateIntervalym (int, string) set list as day-time interval type value
UpdateTime (int, string) setting is a time type (with nanosecond)
UpdateIntervaldt (String, String) Settings listed as the year - month time interval value
UpdateIntervalyM (String, String) Settings As the day - time interval type value
UpdateTime (String, String) Settings As time type (with nanosecose)
Further getObject (int), getObject (String), getString (int), getString (String), updateObject (int, Object), updateObject (String, Object), updateString (int, String), updateString (String, String) may be Operate these types.
1.2.11 ParameterMetadata
1. Overview
The parameter metadata is a newly introduced interface of the JDBC 3.0 standard. It is mainly description of the parameters in the PreparedStatement, CallaBleStatement object, such as the number of parameters, the type of parameters, and the accuracy of the parameters, similar to the ResultSetMetadata interface. By introducing this interface, the parameters can be more detailed and accurate.
2. Create a parameter metadata object
The ParameterMetadata object for this precompiled object can be obtained by calling the getParameterMetadata () method of the PreparedStatement or the CallableStatement object:
ParameterMetadata PMD = PStmt.getParameterMetadata ();
You can then use this object to get some information about the parameter description:
// Number of parameters
INT ParaCount = PMD.GetParameterCount (); for (int i = 1; i <= paracount; i ) {
// Get parameter type
System.out.println ("Type of Parameter" PTMT.GetParameterType (i));
// Get parameter type name
System.out.println ("Type Name of Parameter") IS "
PTMT.GetParameterTypename (i));
// Get parameter accuracy
System.out.println ("The Precision of Parameter" PTMT.GetPrecision (i));
// Get the parameter is empty
System.out.Println ("Parameter (" i ") is NULLABLE?" PTMT.Inullable (i));
}
1.2.12 large object
1. Overview
The JDBC standard adds two interfaces of java.sql.blob and java.sql.clob in JDBC 3.0 standard in order to enhance the operation of large data objects. These two interfaces define a number of ways to operate large objects, and through these methods, the contents of the large object can be operated.
2. Generate LOB objects
Calling getBlob () and getClob () methods in the ResultSet and CallableStatement objects can get the blob object and Clob object:
BLOB BLOB = rs.getblob (1);
Clob Clob = rs.getClob (2);
3. Set the LOB object
Lob objects can be used as a parameter as a parameter as a normal data type, when operating preparedStatement, CallableStatement, and ResultSet objects:
PreparedStatement PSTMT = Conn.PrepareStatement ("INSERT INTO BIO (Image, Text)
"VALUES (?,?)");
// AuthorImage is a blob object
PSTMT.SetBlob (1, AuthorImage);
// authorbio is a clob object
PSTMT.SETCLOB (2, Authorbio);
In an updatable result set, you can also update the current row using UpdateBlob (int, blob), and UpdateClob (int, clob).
4. Change the content of the LOB object
The LOB interface provides a way to make the user can modify the content of the LOB object:
BYTE [] VAL = {0, 1, 2, 3, 4};
BLOB DATA = rs.getblob ("DATA");
Int numWritten = data.setbytes (1, val); // Insert data at the specified location
PREPAREDSTATEMENT PS = Conn.PrepareStatement ("Update DataTab SET DATA =?");
Ps.setblob ("Data", DATA);
ps.executeUpdate ();
1.2.13 SavePoint
1. Overview
In order to increase the control of the transaction, the JDBC specification 3.0 adds the SavePoint interface. It represents a logical transaction point. In an automatic submission mode, multiple SavePoint can be set in a transaction. This will roll back to the specified SavePoint when rollback, and the operation starting from the transaction to the SavePoint remains. This improves the particle size of the transaction, better manipulation data. SavePoint is divided into two kinds of naming and unnamed. Unrecognized use of the ID as an identifier. At the same time, you can determine if the JDBC driver supports SavePoint via DatabaseMetadata.supportssavePoint ().
2. Operation SavePoint
SavePoint's operation is located in the Conncetion interface:
Statement Stmt = conn.createstatement ();
INT rows = stmt.executeUpdate ("INSERT INTO TAB1 (COL) VALUES ('first')"); // Insert the first data
SavePoint svpt1 = conn.setsavepoint ("savepoint_1"); // Set a SavePoint
Rows = stmt.executeUpdate ("" "" "" ") ('Second')"); // Insert the second data
......
Conn.rollback (svpt1); // Roll back to the specified SavePoint
......
Conn.commit (); // Submit a transaction, truly insert only the first data in the database
In this way, only the record of the first insertion is added in the database. Note: Before using SavePoint, be sure to set the current connection to non-automatic submission mode.
After the transaction is submitted, all SavePoints in the previous transaction are automatically canceled. If there are two SavePoint, when the transaction rolls back to the SavePoint in front, the later SavePoint is automatically canceled.
1.2.14 AutoGenerated Key
In many databases, the database will automatically generate a unique identifier for new records when a new record is inserted. STATEMENT in the JDBC3.0 specification adds a method: getGeneratedKeys, using this method to retrieve the keywords generated by the system automatically inserted new lines. You can pass a statement.return_generated_keys to identify the execute, executeUpdate () method of the Statement object or some of the preparedStatement object, so that newly generated keywords can be retrieved after the method is performed.
Statement Stmt = conn.createstatement ();
Int rows = stmt.executeUpdate ("INSERT INTO Orders (ISBN, CUSTOMERID) VALUES (1953, 'Billg')",
Statement.return_Generated_Keys);
ResultSet RS = stmt.get generatedKeys ();
Boolean b = rs.next ();
IF (b)
{
/ / Retrieve the value of the newly generated keyword
......
}
In order to retrieve convenience, the user can specify a column name or index for the automatically generated keyword:
String [] KeyColumn = {"ORDER_ID"};
...
Statement Stmt = conn.createstatement ();
Int rows = stmt.executeUpdate ("INSERT INTO Orders (ISBN, CUSTOMERID) VALUES (9664, 'Billg')", KeyColumn); ResultSet RS = Stmt.getGeneratedKeys ();
While (rs.next ())
{
/ / Retrieve the value of the newly generated keyword
Byte [] BS = rs.getbytes (ORDER_ID);
}
......
The JDBC driver supports automatically generated keywords, which can be judged by DatabaseMetadata.supportsgetGeneratedKeys. The DM4 database system is currently available to automatically generate keywords.
1.2.15 data source
The data source is introduced by the JDBC 2.0 specification as the extension package, which is the core API in the JDBC 3.0 specification. Data sources not only fully replace the way to establish a connection with DriverManager, but also have the following advantages:
(1) Enhance the transplantability of the code;
(2) Convenient code maintenance;
(3) Use the connection pool to improve the performance of the system.
The DM4 JDBC driver provides support for the data source, implements the javax.sql.datasource interface and java.sql.connectionpoolDataSource interface. The user establishes the connection through the Javax.sql.DataSource interface.
When using a data source to establish a connection, first register a data source to JNDI. When establishing a connection, first get the data source to use by JNDI: DataSource DS = (DataSource) CTX.lookup (DataSourceName); then use this data source to create a connection object: Connection Con = ds.getConnection (); The connection is the same as the connection established by DRIVERMANAGER. For specific procedures for establishing a connection through a data source, please refer to 1.3.2 Creating a JDBC Data Source.
Implementing the Javax.sql.connectionPoolDataSource interface is to improve the performance of the system. By setting a connection buffer, this connection buffer is shared and reused by a large number of parallel users, avoiding a new physical connection while each time it needs to be used. The expensive operation of the connection is turned off when it is released. The connection pool is a measure of the JDBC to provide system performance, which is implemented inside the JDBC that can provide transparent high-speed buffer access to users. Users use the data source to establish a connection to make changes to this any code.
1.2.16 expansion class
The DM4 JDBC driver is to support the DM4-specific data type, which uses a custom extension package to solve it. DM4.SQL This package contains classes corresponding to the DM4 unique data type. The time interval type is DM4 has a data type, and there is no corresponding type in the JDBC standard. DM4.SQL.DMDBINTERVALYM and DM4.SQL.DMDBINTERVALDT correspond to the annual - monthly interval type and day-time septum type. The java.sql.types.time type in the JDBC standard is not allowed to have nanoseconds, and the DM4.SQL.DMDBTIME type is set in order to indicate the time type of Naose in DM4. The main methods of these types are:
1. DM4.SQL.dmdbintervaldt
Method name function description
DMDBINTERVALDT (Byte []) utilizes byte arrays as a constructor of the parameter.
DMDBINTERVALDT (String) utilizes strings as constructor of parameters.
setBytes (Bytes []) Sets the byte array.
SetBytes (int, int, int, int, "converts a given int parameter into a byte array.
GetString () returns the value of DMDBINTERVALDT in the form of a string. Tostring () transforms the value of DMDBINTERVALDT into a string.
getBytes () gets the byte array.
2. Dm4.sql.dmdbintervalym
Method name function description
DMDBINTERVAL (Byte []) utilizes byte arrays as constructor's constructor.
DMDBINTERVAL (STRING) utilizes a string as a constructor of the parameter.
setBytes (Bytes []) Sets the byte array.
GetString () returns the value of DMDBINTERVALYM in the form of a string.
Tostring () transforms the value of DMDBINTERVALDT into a string.
getBytes () gets the byte array.
SetBytes (int, int) converts a given INT parameter into a byte array.
3. DM4.SQL.DMDBTIME
Method name function description
DMDBTIME (bytes []) utilizes byte arrays as a constructor of the parameter.
DMDBTIME (String) utilizes a string as a constructor of the parameter.
setBytes (Bytes []) Sets the byte array.
GetString () returns the value of DMDBTIME in the form of a string.
Tostring () transforms the value of DMDBTIME into a string.
getBytes () gets the byte array.
GetTime () returns the value of DMDBTIME in java.sql.types.time. Just getting the nanosecond.
4. interview method
In order to access these unique data types in the DM4 JDBC driver, we add a number of methods in DMDBPRepAredStatement, DMDBCALLABLESTATEMENT, DMDBCALLABLESTATEMENT, and DMDBResultSet classes. If you want to use these methods, you must convert objects to DMDBXXX types:
PreparedStatement PSTMT = Connection.PrepareStatement ("INSERT INTO Testinterval" "VALUES (?)");
(DMDBPReparedStatement) PSTMT.SetIntervalym (1, "Interval '0015-08' Year to Month");
INT updateCount = pstmt.executeUpdate ();
The use of other methods is the same. Moreover, the time interval type can be accessed using the setString and getString methods, and the time type with nanosecond can also be operated as a normal time type (without nanosecond).
1.3 establish a JDBC connection
There are usually two ways to obtain JDBC connection objects, one is established by the GetConnection (String Url, String User, String Password) method for the Drive Manager DRIVERMANAGER, and the other is to create through the data source. Both DM4 JDBC 2.0 and JDBC 3.0 support these two ways to establish a connection.
1.3.1 Establish a connection via DriverManager
This way of establishing a connection is the most commonly used, also known as programmed connections. Using this way to establish a connection usually requires the following steps:
Register the Database Driver (Driver). You can explicitly register the driver by calling the Java.sql.DriverManager class Register driver, or you can implicitly registering the driver by loading the database driver class.
// Display registration
Drivermanager.RegisterDriver (new dm4.jdbc.driver.dm4driver ());
// Implicit registration
Class.Forname ("DM4.jdbc.driver.dm4driver");
The type of Java.sql.Driver is loaded during implicit registration. There is a static execution code segment in which the class is registered to the drive manager DriverManager during class load. The code segment of this static execution is actually the above explicit registered code. establish connection. After registering the driver, you can call the GetConnection method of the drive manager to establish a connection. Establish a database connection to specify the URL indicating that the different databases, the username User and password used by the login database. Please refer to 1.2.1 DriverManager this section through the DRIVERMANAGER to establish a connection.
With this way to establish a database connection, the parameter information required to connect the database is hardcoded into the program, so that each replacement of different databases or login user information must rewrite, compile, not flexible enough. Moreover, when the user needs multiple connections, multiple connections must be established, resulting in resource waste and low performance. In order to solve these problems, Sun has defined a data source interface in the JDBC 2.0 extension package, providing a new way to establish a connection.
1.3.2 Creating a JDBC Data Source
The data source is a concept introduced in JDBC 2.0. This concept is defined in the JDBC 2.0 extension package to describe this concept. If the user wants to create a database connection, you can get a corresponding database connection from the data source by querying the data source in the JNDITM service. This way users only need to provide a logic name, not the specific details of the database login.
JNDITM's full name is Java Naming and Directory Interface, which is understood to be a Java name and a directory service interface. JNDI provides an application with a mechanism for querying and using remote services. These remote services can be any business service. For JDBC applications, JNDI provides database connection services. JNDI enables the application to obtain the services provided by the object and objects using the logical name, so that the programmer can avoid using the code associated with the organization that provides an object.
A DataSource object represents an actual data source. All information for establishing database connections is stored in the data source. The system administrator corresponds to the DataSource object with a logical name, which can be arbitrary. The name of the DataSource object in the example below is NativeDB. According to traditional habits, the name of the DataSource object is included in JDBC /, so the full name of this data source object is: JDBC / NativeDB.
After the logical name of the data source is determined, you need to register the data source to the JNDI service. The following code shows the process of registering a data source to the JNDI service.
// Initialization Name - Directory Service Environment
CONTEXT CTX = NULL;
Try
{
Hashtable env = new hashtable (5);
env.put (Context.Initial_Context_Factory,
"com.sun.jndi.fscontext.reffscontextfactory);
Env.put (Context.Provider_URL, "File: JNDI");
CTX = New InitialContext (ENV);
}
Catch (Namingexception Ne)
{
Ne.PrintStackTrace ();
}
Bind (CTX, "JDBC / NativeDb");
The program first became a context instance. The javax.naming.context interface defines a name service environment (Naming Context) and the operation supported by the environment. The name service environment is actually composed of mutual mapping between names and objects. Context factory, initialization name service environment, is com.sun.jndi.fscontext.reffsContextFactory (This class can be found in fscontext.jar, because the user needs from WWW because the fscontext.jar is not a standard API. .javasoft.com's JNDI Zone download fscontext.jar), the role of the environment is the instance of the name service environment. The javax.naming.spi.initialContextFactory interface defines how the environmental plant should initialize the name service environment (this interface is implemented in ProviderUtil.jar, because the user needs to be in www.javasoft.com) because the providentil.jar is not a standard API. The JNDI area downloads providerutil.jar). The URL of the environment needs to be defined when the name service environment is initialized. The program is used "File: JNDI", which is to save the environment in the JNDI directory of the local hard drive. Many J2ETM application servers have implemented their own JNDI services, and users can choose these service packages. After initializing the name service environment, you can register the data source instance into the name service environment. When registering the javax.naming.context.bind () method, the parameter is the registration name and registration object. After the registration is successful, a .binding file will generate a .binding file in the JNDI directory, the file records the current name - the name and object owned by the service environment. The specific implementation is shown in the following example.
Void Bind (Context CTX, String LN) THROWS NAMINGEXCEPTION, SQLEXCEPTION
{
// Create a DMDBDataSource instance
DMDBDataSource DMDS = New DMDBDataSource ();
// Register the DMDBDataSource instance into JNDI
CTX.bind (ln, dmds);
}
When you need to query an object in the Name Service Environment, you need to call the javax.naming.context.lookup () method, and explicitly convert the query object to the data source object. The database operation is then performed by the data source object.
DataSource DS = (Datasource) Lookup (CTX, "JDBC / NativeDB");
Connection conn = ds.getConnection ();
The Connection object obtained in the DataSource object and the object obtained with the DriverManager.getConnection method are equivalent. Since the DataSource method has many advantages, the method is a recommended method for obtaining a connection.
1.3.3 Data source and connection pool
Using the data source can enhance the portability of the code, it is convenient for code maintenance. And you can also utilize the functionality of the connection pool to improve the performance of the system. The working principle of the connection buffer pool is: When an application turns off a connection, this connection is not truly released but is looped. Because establishing a connection is a large operation, the loop utilization connection can reduce the establishment of a new connection, which can significantly improve performance.
The JDBC specification defines two interfaces, a client interface, and a server-side interface for the connection pool. The client interface is javax.sql.datasource so that the customer has previously used the data source to get the connection code does not require any modifications. Whether the connection obtained by the data source is buffered, depending on whether the specific implementation of the JDBC driver implements the server-side interface javax.sql.connectionpoolDataSource, which is connected to the connection pool. DM4 JDBC2.0 and JDBC 3.0 These two drivers have implemented connection buffer pools. A new level of cache is used during the process of achieving the connection buffer pool. In general, the connection cache is a way to maintain a small number of physical database connections in a pool, which is shared and reused by a large number of parallel users, thereby avoiding a new physics at each need. Database connections, and expensive operations for turning off the connection when it is released. The implementation of the connection pool is transparent to the user, and the user does not need to modify any code for it. 1.4 Programming Basic Steps
The application of the DM4 JDBC driver mainly includes four ways:
(1) Database access is performed through the Java Applet applet;
(2) Database access by Java Application application;
(3) Database access by JSP / servlet;
(4) Database access via JavaBeans.
In the first working mode, the client automatically downloads the Java Applet program containing the application logic from the web server with a web browser, together with the JDBC driver together with the JDBC driver), and the microbiographic APPLET directly accesses the source host (Web). The database server on the server).
This way is interactive, but it is necessary to download the appliance and driver from the network while working, the download amount is large, and there is a secure limit. Typically, select the second mode to implement the connection and access control of the database through the application. .
When the database access control is performed in the application, first load the JDBC driver via the JDBC Driver Manager, then call the JDBC standard interface in the program, perform the database connection, run the SQL statement, and obtain the database return result.
JSP / servlet is a thin client technology, and is also one of the mainstream technologies currently building a dynamic web page. When performing database access control in JSP / Servlet, it is mainly connected to the database data source and the database is connected, and then the JDBC standard interface is called in the program, run the SQL statement, and obtain the database returns.
JavaBeans is a component technology and one of the mainstream technology of the J2EE platform. When database access is performed in JavaBeans, it is mainly connected to the database with the database, then call the JDBC standard interface, run the SQL statement, and obtain the database return result.
Although the application environment of the JDBC driver is different, its operation is basically the same. This may be in the biggest difference with the drive manager or data source. The general steps for programming using the JDBC driver are:
Get a java.sql.connection object. The connection with the database is established with DRIVERMANAGER or the data source.
Create a java.sql.Statement object. Here also includes java.sql.preparedStatement and java.sql.callablestatement objects. Create it using the method of creating a statement object of the connection object. In the process of creation, set the properties of the result set as needed.
Data operation. The data operation is mainly divided into two aspects, one is an update operation, such as updating the database, deleting a line, creating a new table; the other is the query operation. After performing the query, you will get a java.sql.resultset object. This object can be operated to obtain the information of the specified column, read the value of a column of the specified line. Release the resource. After the operation is complete, the user needs to release system resources, mainly to turn off the result set, turn off the statement object, and release the connection. Of course, these actions can also be automatically executed by the JDBC driver, but due to the characteristics of Java language, this process will be slow (need to wait until Java is garbage collection), which is prone to unexpected problems.
The following is a specific programming instance to show the basic steps using the JDBC driver for database operations.
/ * This routine implements basic operations such as inserting data, modifying data, data queries. * /
Import java.sql. *;
Public class basicapp {
/ / Define DM4 JDBC Drive Strings
String jdbcstring = "dm4.jdbc.driver.dm4driver";
/ / Define DM4 URL connection string
String Urlstring = "JDBC: DM4: // localhost: 12345";
/ / Define connection user name
String Username = "sysdba";
/ / Define the connection user password
String password = "sysdba";
/ / Define connection objects
Connection conn = NULL;
/ * Load JDBC driver
* @Throws Sqlexception exception * /
Public void loadingjdbdriver () throws sqlexception {
Try {
System.out.println ("Loading JDBC Driver ...");
// Load JDBC driver
Class.Forname (jdbcstring);
}
Catch (classnotfoundexception e) {
Throw new SQLEXCEPTION ("Load JDBC Driver Error:" E.GetMessage ());
}
Catch (Exception EX) {
Throw new Sqlexception ("Load JDBC Driver Error:" ex.getMessage ());
}
}
/ * Connect DM4 database
* @Throws Sqlexception exception * /
Public void connect () throws sqlexception {
Try {
System.out.Println ("Connecting to DM4 Server ...");
/ / Connect DM4 database
Conn = drivermanager.getConnection (Urlstring, Username, Password);
}
Catch (SQLException E) {
Throw new SQLEXCEPTION ("Connect to DM4 Server Error:"
E.getMessage ());
}
}
/ * Close connection
* @Throws Sqlexception exception * /
Public void disconnect () throws sqlexception {
Try {
// Close connection
CONN.CLOSE ();
}
Catch (SQLException E) {
Throw New Sqlexception ("Close Connection Error:" E.GetMessage ());}
}
/ * Insert data to the manufacturer registration table
* @Throws Sqlexception exception * /
Public void inserttable () throws sqlexception {
// Insert the data statement
String SQL = "INSERT INTO Veter Registration VALUES ('B0A01',"
"'Duck Washing Machine Factory', 2000, 'Shanghai');";
// Create a statement object
Statement Stmt = conn.createstatement ();
System.out.Println ("Inserting Table ...");
// Execute a statement
Stmt.executeUpdate (SQL);
// Close statement
Stmt.close ();
}
/ * Modify the manufacturer registration form data
* @Throws Sqlexception exception * /
Public void updateTable () throws sqlexception {
/ / Update the data statement
String SQL = "Update Vendor Registration SET Vendor Address =?"
"WHERE Vendor Number = 'B0A01';";
// Create a statement object
PreparedState pstmt = conn.preparestatement (SQL);
/ / Assignment for parameters
PSTMT.SetString (1, "Beijing");
// Execute a statement
PSTMT.ExecuteUpdate ();
// Close statement
PSTMT.Close ();
}
/ * Query the manufacturer registration form
* @Throws Sqlexception exception * /
Public void queryTable () throws sqlexception {
// check sentence
String SQL = "SELECT * FROM manufacturer registration";
// Create a statement object
Statement Stmt = conn.createstatement ();
System.out.println ("Querying Table ...");
// Execute Query
ResultSet RS = Stmt.executeQuery (SQL);
// Display result set
DisplayResultSet (RS);
// Close the result set
Rs.close ();
// Close statement
Stmt.close ();
}
/ * Display result set
* @Param RS result set object
* @Throws Sqlexception exception * /
Private Void DisplayResultSet (ResultSet RS) throws sqlexception {
// acquirted result collection element data
ResultSetmetaData RSMD = rs.getMetadata ();
/ / The number of columns included in the result set
INT Numcols = rsmd.getColumnCount ();
// Display the column header
For (INT i = 1; i <= numcols; i ) {
IF (i> 1)
System.out.print (",");
System.out.print (RSMD.GetColumnLabel (i));
}
System.out.println ("");
// Display all data in the result set
While (rs.next ()) {for (int i = 1; i <= numcols; i ) {
IF (i> 1)
System.out.print (",");
System.out.print (Rs.getstring (i));
}
System.out.println ("");
}
}
/ * Class master method
* @Param args parameter * /
Public static void main (string args []) {
Try {
// Define the class object
Basicapp BasicApp = New BasicApp ();
// Load the driver
Basicapp.10jdbdriver ();
/ / Connect DM4 database
Basicapp.connect ();
// Insert data
Basicapp.inserttable ();
// Query the manufacturer registration form before updating
Basicapp.QueryTable ();
// Modify the manufacturer registration form
Basicapp.UpdateTable ();
// After updating the manufacturer registration form
Basicapp.QueryTable ();
// Close connection
Basicapp.disconnect ();
}
Catch (SQLException E) {
System.out.println (E.getMessage ());
}
}
}
1.5 multi-character set / multilingual support
In order to achieve storage and access to multiple languages, DM4 uses two coding methods to store data. One is to store according to the system default encoding provided by the operating system, one is to store with Unicode encoding, specifically, using UTF-8 to encode. UTF-8 encoding format is gradually a way to exchange international text information dominant because it can support all languages in the world, and it is also compatible with ASCII. UTF-8 uses a growing coding. The character from 0 to 0x7f (127) is encoded into a single byte, and the character is encoded to 2 to 6 bytes.
The Java language itself uses Unicode encoding, which provides a lot of convenience to solving internationalization. When the Java virtual machine is started, it sets the File.Encoding property of the virtual machine according to the operating system's encoding mode. Thereafter, any by -1 fertilizer in the application is converted to the character stream, and the JVM will convert according to the default encoding method of this system.
The server's encoding method has been determined in the process of initializing the database. However, there may be multiple clients that are in different locales to operate the same database at the same time. In this case, the server's encoding method is typically set to UTF-8, and each client is set by the custom method provided by the DM4 JDBC driver to set the encoding method used. Information conversion used by the DM4 JDBC automatically implements the encoding used to the server from the client. If the user does not specify the client's encoding, the Java virtual machine will be processed in accordance with the client's system default encoding.
DM4 JDBC drivers provide some custom methods in the class DMDbConnection implementation of the Java.sql.connection interface for users to set and read the encoding information used by the client and the server.
After the user establishes a connection with the database, you can distinguish the obtained connection object into the DMDBConnection type, then call the custom method to operate. The general operation process is as shown in the following example.
Connection con = DRIVERMANAGER.GETCONNECTION (URL, User, Password);
DMDBCONNECTION DMDBCON = (DMDBConnection) Con;
DMDBCon.SetCliencoding ("GBK");
String encoding = dmdbcon.getcliencoding ();
Boolean Server_UTF8 = DMDBCON.GETSERVERUTF8Encoding (); statement stmt = con.createstatement ();
ResultSet RS = Stmt.executeQuery ("SELECT * FROM manufacturer registration;");
...
During the execution, the DM4 JDBC driver first determines whether the server uses the UTF-8 encoding method to store data. If so, then the customer's input is parsed according to the encoding used by the client, and then converts it to the UTF-8 encoding method to send to the server. After obtaining the data from the server, first determine whether the server uses the UTF-8 encoding method to store data. If so, then these data is parsed by UTF-8 encoding, and then converted to the encoding method used in the client. If the server does not store data using the UTF-8 encoding format, the client directly uses the default encoding method of the own system to parse data without coding conversion.
