1. Load the driver.
The brochure driver has multiple methods, Class.forName (); is an explicit load. When a driver class is classloader loaded, DriverManager will register an instance of the driver class during the dissolution process. This call was taken on its own initiative, This means that the Drivermanager.registerdriver () method is called by itself,
Class.forName ("Oracle.jdbc.driver.OracleDriver");
Of course we can also call Drivermanager.registerdriver () directly to register the driver, but. Ms's Browse applet does not succeed in invoking this method, which means that the JVM built into MS in the browser is not valid for the implementation of the method.
Drivermanager.registerdriver (New Oracle.jdbc.driver.OracleDriver ());
In addition, we are able to load multiple drivers using System attribute Jdbc.drivers:
System.setproperty ("Jdbc.drivers", "Driver1:driver2: ...:d rivern"), and multiple drivers are separated by ":" so that JDBC searches sequentially when linking. Until you find the first driver to successfully link to the specified URL.
System.setproperty ("Jdbc.drivers", "Oracle.jdbc.driver.OracleDriver");
2. A handle to a database connection via DriverManager
Once the driver is successfully registered, we are able to use DriverManager's static method getconnection to get a reference to the database link:
Connection conn = drivermanager.getconnection (URL);
If the link is successful, it returns the connection object conn, assuming null or throws an exception, indicating that no connection is made to the database.
There are three overloaded methods for the getconnection () method,
One is the simplest to just give the data source namely: getconnection (URL),
url = "JDBC:ORACLE:THIN:@127.0.0.1:1521:ORCL";
conn = drivermanager.getconnection (URL);
There is also the same time to give some data source information namely getconnection (Url,properties),
Properties Info = new properties ();
Info.setproperty ("User", "WZG");
Info.setproperty ("Password", "Wzg");
conn = drivermanager.getconnection (URL, info);
The second is to give the data source, username and password:getconnection (URL,USER,PASSWOD),
String url = "JDBC:ORACLE:THIN:@127.0.0.1:1521:ORCL";
String user = "Wzg";
String password = "WZG";
conn = drivermanager.getconnection (URL, user, password);
The URL value for Oracle is the IP address and port number of the protocol and database to which the database is connected and the name of the library (Datebasename) to connect to.
The format of the Oracle URL
Jdbc:oracle:thin: (protocol) @XXX. XXX.X.XXX:XXXX (IP address and port number): XXXXXXX (the name of the library used)
Example: JDBC:ORACLE:THIN:@192.168.0.39:1521:TARENADB
For data source information. Let's say we want to give a lot of other information to the link to get this information into a properties, which can be pressed directly into the Usernamepassword, and can be pressed into the specified character set, encoding or default operation and other information.
3. Bind the statement to run through the link handle.
After getting a link, there is a channel for dealing with the database. We'll be able to do what we want to do. Let's start by introducing some general operations: suppose we want to manipulate the tables in the database, first of all, to bind a statement:
Statement stmt = Conn.createstatement ();
Then use this statement to run the operation. The ability to return two results, assuming that the query operation is run, returned as a result set resultset, assuming that the update operation is run, returns the number of records for the operation Int.
Note that the SQL operation strictly distinguishes only two, one is the read operation (query operation), there is a write operation (update operation), so, create,insert,update,drop,delete and so on the data has the rewriting behavior is the update operation.
ResultSet rs = stmt.executequery ("Select * from table where xxxxx");
int x = stmt.executeupdate ("Delete from table where ...");
Suppose you are able to run an update operation with ExecuteQuery, but do not assign it to a handle, and of course a slightly more experienced program ape will not do so.
As for the processing of the result set, we put it in the next section, because it is an actionable option and only the query operation returns the result set.
For the completion of a single operation, a very necessary step is to close the database link, before you know a lot of other JDBC knowledge, you first take this step as the most important step in the JDBC operation,
Examples:
try{
Class.forName ("Org.gjt.mm.mysql.Driver");
}catch (Exception e) {
SYSTEM.OUT.PRINTLN ("Driver not loaded successfully:" +e.tostring ());
Return
}
Connection conn = null;
try{
conn = Drivermanager.getconnection ("Jdbc:mysql://host:3306/mysql", "User", "passwd");
Statement stmt = Conn.createstatement ();
ResultSet rs = stmt.executequery ("Select * from table");
RS Processing
[Rs.close ();]
[Stmt.close ();]
}
catch (Exception e) {
SYSTEM.OUT.PRINTLN ("Database operation exception occurred:" +e.tostring ());
}
finally{
try{
Conn.close ();
}catch (Exception) {
}
}
No matter what you have learned about how the database process operates, from now on, please be sure to write the database shut down code in the finally block, earnestly!
About Statement objects:
As I said earlier, the statement object is used to bind the operation to be run, and there are three ways to run it:
The ExecuteQuery () that is used to run the query operation,
ResultSet rs = stmt.executequery ("Select * from table");
Executeupdate () To run the update operation
int x = stmt.executeupdate ("Delete from table where ...");
and execute () that is used to run the dynamic unknown operation.
Boolean FLG = St.execute (sql);
F (FLG) {
ResultSet rs= St.getresultset ();
}else{
int count = St.getupdatecount ();
}
Execute (String sql), the return value of this method is a Boolean type. Suppose that returning true means that SQL is a SELECT statement. The result set can be obtained through getresultset (), assuming that False,sql is a DML statement or a DDL statement.
The SQL statement that JDBC is not correctly compiling at compile time is just looking at a string and only knows if the driver is running the SQL statement.
Attention:
A statement object can only have a result set at the same time. This is tolerant, meaning that if you do not call ResultSet's close () method, simply opening the second result set implies closing the previous result set. So suppose you want to operate on multiple result sets at the same time , it is necessary to create multiple statement objects, assuming that you do not need to operate at the same time, you can order multiple result sets on a statement object.
Connection conn = null;
Statement stmt = null;
conn = ...;
stmt = Conm.createstatement (XXXXXX);
ResultSet rs = stmt.executequery (SQL1);
while (Rs.next ()) {
str = rs.getstring (xxxxx);
ResultSet rs1 = Stmt.executequery ("Select * from table where field =str");
}
When Stmt.executequery ("SELECT * from table where field =str") is assigned to RS1, the implied action is to have the RS closed, So assuming that you want to manipulate multiple result sets at the same time, make sure that it is bound to a different statement object. Fortunately, a connection object can create arbitrary multiple statement objects without having to get the link again.
Options for getting and setting statement: Just look at its GetXXX method and Setxxx method is clear, here as a basic knowledge only to mention the following:
SetQueryTimeout, set the timeout limit for a SQL run.
setMaxRows, sets the number of rows that the result set can hold.
Setescapeprocessing, assuming that the parameter is true, the driver escapes the SQL statement before it is sent to the database, otherwise lets the database handle it itself, and of course the default values can be queried by the Get method.
Two subcategories of statement:
PreparedStatement: For multiple runs of the same statement, statement each time to send SQL statements to the database, which is significantly less efficient, assuming that the database supports precompilation, PreparedStatement is able to send the statement to run one at a time, and then each run without having to send the same statement, of course, efficiency, of course, assuming the database does not support precompilation, PreparedStatement will work like statement, It is simply inefficient and does not require the user's hand to intervene. Additionally, the PreparedStatement also supports receiving parameters. It is possible to run with only a few different parameters after precompilation, which greatly improves performance.
PreparedStatement PS = conn.preparestatement ("Select * from table where field =?")
");
ps.setstring (1, number of references);
ResultSet rs = Ps.executequery ();
CallableStatement: is a subclass of PreparedStatement that is used only to run stored procedures.
callablestatement sc = conn.preparecall ("{Call query ()}");
ResultSet rs = Cs.executequery (); The
SQL statement assumes that you are running a query operation that returns a ResultSet object that must be processed for the final explicit display of the query results to the user. ResultSet returns a record that matches a condition in a table. The processing of ResultSet is done on a line-by-row basis, and for each row of columns, it is possible to
in random order (note that this is only a requirement of the JDBC specification, and some JDBC implementations still require the user to process the column in order, but this is rare). In fact, While you can handle columns in a random order, it is assumed that you can get higher performance in order from left to right.
to explain the ResultSet object from the bottom, the ResultSet object actually maintains a two-dimensional pointer, the first dimension points to the current row, and initially it points to the first row of the result set, so if you want to access the first line, then next (), Each line will be next () ability to visit, and then the second dimension of the pointer to the column, only when you go to rs.getxxx (column), only through the connection and then go to the database to get the real data out, otherwise no machine can really put the data to be taken in memory. So, Be sure to remember that assuming the connection is closed, it is impossible to get the data from the resultset again.
It is not possible to get a resultset to write it to the session and then close the connection so you don't have to link it every time. The idea is good, but it's wrong! Of course, there are cacherow and Webcacherow in the JDBC Advanced application in the Javax.sql package that can cache the result set, but it's no different from our own data structure that takes all the values of the ResultSet row set out once.
Visit the columns in the row, and be able to access them by field name or index. The following is a simple procedure for retrieving results:
ResultSet rs = stmt.executequery ("Select a1,a2,a3 from table");
while (Rs.next ()) {
int i = Rs.getint (1);
String a = rs.getstring ("A2");
..............
}
For the result set to be displayed, using while to do next () is the most common, assuming that next () returns false, there are no more rows available. But sometimes we may not even have a single line, and assuming there is a record and do not know how many rows, Assuming that there are different treatments for the records and no records, the following process should be inferred:
if (Rs.next ()) {
Since the next () has been first, the records should be Do{}while ();
do{
int i = Rs.getint (1);
String a = rs.getstring ("A2");
}while (Rs.next ());
}
esle{
System.out.println ("Failed to get a record of qualifying!");
}
Type conversions:
The GetXXX method of resultset will try to convert the SQL data type in the result set to the Java data type, the fact that most types are capable of conversion,
But there are still a lot of tricks that can't be converted, such as you can't convert a SQL float to a Java date, and you can't convert the VARCHAR "we" to the Java Int.
Larger value:
For values greater than getmaxfieldsize return value in statement, the normal GetBytes () or GetString () is unreadable, but Java provides a way to read the input stream.
For large objects, we can use Rs.getxxxstream () to get a inputstream,xxx type containing ascii,binay,unicode.
Depending on the type of field you store, use a different stream type, in general, binary files with Getbinaystream (), Text files with Getasciistyream (),
For Unicode characters in a text file with Getunicodestream (), the corresponding database field type should be: Blob,clob and Nlob.
SqlException is to check that the exception must be handled either throws, or Try{}catch () {}
GetErrorCode () can get the error code. Ability to query for errors.
Source data
There are two source data in JDBC, one is the database source data and the other is the resultset data.
The source data is the data structure describing the container that stores user data.
ResultSet rs=ps.executequery (SQL);
ResultSetMetaData Rsmd=rs.getmetadata ();
Rsmd.getcolumncount () returns the number of columns.
Getcolumnlabel (int) Returns the display caption for the corresponding column of the INT
getColumnName (int) returns the name of the corresponding column of the int in the database.
Getcolumntype (int) Returns the data type in the database for the corresponding column of the Int.
Getcolumntypename (int) returns the name of the data type of the corresponding column of the int in the data source.
isreadonly (int) returns whether the corresponding column of the int is read-only.
isnullable (int) returns whether the corresponding column of the int is empty enough
Database source data
DatabaseMetaData
GetURL (), get the URL of the connection database
Getdatabaseproductname () Get the name of the database product
Getdriverversion () Get a string version of the JDBC driver
Gettables () Get all the tables for that user in the database
GetUserName () Gets the database username.
ResultSet gettables (String catalog, String schemapattern,string tablenamepattern,string[] types)
Be able to get all the "tables" in the library, where the table contains tables, views, system tables, temporary spaces, aliases, synonyms
For each of the parameters:
String catalog, table folder, possibly NULL, "null" matches all
String Schemapattern, outline of table, ibid.
String Tablenamepattern, table name, IBID.
String[] types, the type of table, "null" matches all, the available types are: Table,view,sysem table,global temporary,local temporary,alias,synonym
Like what:
DatabaseMetaData Dbmd = Conn.getmetadata ();
ResultSet rs = dbmd.gettables (null,null,null,null);
ResultSetMetaData RSMD = Rs.getmetadata ();
Int J = Rsmd.getcolumncount ();
for (int i=1;i<=j;i++) {
Out.print (Rsmd.getcolumnlabel (i) + "/t");
}
Out.println ();
while (Rs.next ()) {
for (int i=1;i<=j;i++) {
Out.print (rs.getstring (i) + "/t");
}
Out.println ();
}
For more specific information about the columns in the table, you can use DBMD (not RSMD). GetColumns (String catalog,string schemapattern,string tablenamepattern,string Columnnamepattern)
Not only can get the information in the RSMD, but also can get the column size, scale, precision, default value, listed in the table
Location and other relevant information.
There are also two ways to get information about stored procedures and indexes, just as you would call and get table information:
ResultSet getprocedures (String catalog,string schemapattern,string procedurepattern);
ResultSet getindexinfo (String catalog,string schemapattern,string table,boolean unique,boolean approximate);
Transactions (Transaction)
A transaction is an atomic operation that requires atomic operations to be non-sub-divided, requiring atomic operations to succeed at the same time and fail at the same time. A transaction is the boundary of a bound atomic operation.
Use transactions in JDBC. The Setautocommite (false) method is called first using the connection. Set your own unsolicited commit (commit) to false. Opening a transaction closes its own unsolicited submission. No business is going to put Setautocommite (true)
When a transaction is processed, when it is run successfully and confirmed after the SQL statement is sent, a connection call commit () method is used in the try block to send the commit information, and when the SQL statement fails to run, the
The connection call rollback () method is used in the Catch statement block to send the rollback information. It is also possible to do rollback operations (subjective reasons) when needed.
Problems and transaction isolation levels generated by JDBC transaction concurrency
1. Dirty Read (dirty Read), reading the data that was not committed.
2, not read repeatedly (Unprpeatable read), two times read to different data. is to keep the data read two times at the same point in time. Similarly, you cannot make changes to the query data.
3, Phantom Read (Phantom), two times at the same point in time data, the number of data changes, to maintain at the same point in time to read the same data two times.
Transaction ISOLATION LEVEL
Transaction_none does not use transactions.
The transaction_read_uncommitted is able to read for submission data.
Transaction_read_committed can avoid dirty reading and can not read uncommitted data. Most frequently used isolation levels The default isolation level for most databases
Transaction_repeatable_read can avoid dirty reading, repeated reading,
Transaction_serializable to avoid dirty reads, repeated reads, and Phantom reads (transactional serialization) reduces database efficiency
The five transaction isolation levels above are static constants defined in the connection class, and the transaction isolation level can be set using the Settransactionisolation (Intlevel) method.
JDBC2.0 new Features
Scrollable result set (reversible scrolling). This result set can not only be bidirectional scrolling, relative positioning, absolute positioning, but also can change the data information.
Scrolling properties
Next (), this method moves the cursor down one record.
Previous (), this method enables a record to be moved on a cursor, preceded by a record.
Absolute (int row). The ability to use this method to jump to the specified record location.
The positional success returns True, False is not successfully returned, and the return value is false. The cursor does not move.
Afterlast (). After the cursor jumps to the last record, it has a position when the result set comes back.
Beforefirst (), the cursor jumps to the first record, which is where the result set comes back.
(jumps to cursor initial bit)
First (), and the cursor points to a record.
Last (). There's a puma pointing to the last record.
Relative (int rows), relative positioning method. The parameter can be positive, the number is positive, the cursor moves down the specified value from the current position, the number is negative, and the cursor moves up the specified value from the current position.
Type_forward_only. One-way, which indicates the type of the ResultSet object that the pointer can only move forward.
Non-scrollable.
Type_scroll_insensitive. Bidirectional, which indicates the type of ResultSet object that can be scrolled but is usually unaffected by other changes.
Type_scroll_sensitive, bidirectional. This constant indicates the type of ResultSet object that can be scrolled and is usually affected by other changes.
This attribute is not supported for some databases.
To use a scrollable result set, specify the number of parameters to use when statement is created
Statement st=null; (int,int) (scrollable properties.) Updatable features)
St=con.createstatement (reusltset.type_scroll_insensitive,resuleset.concur_updatable)
ResultSet result set. Use Movetoinsertrow () first to move the cursor to a buffer that is similar to the result set structure
You can then use the updatexxx (Intcolumn,columntype value) method to update the specified column data, insert the record using the InsertRow () method, and finally point the cursor back to its original position.
Movetocurrentrow ().
Whether the updatable result set can be used. To see if the database driver used is supported. There is also a single table with a primary key field (which may be a federated primary key), and no table connection is allowed.
All non-empty fields are taken and there are no default values.
Result set with SELECT * from T also not, cannot be used *, cannot be sorted
Whether you can use the new features of Jdbc2.0resultset depends on whether the database driver supports it.
Batch Update
Statement.addbatch (String sql), method adds an SQL statement to the batch cache
ExecuteBatch (). Run all the SQL statements in the batch cache.
PreparedStatement. Prepare a set of parameters first
Addbatch () Adds a set of parameters to the batch command for this PreparedStatement object.
ExecuteBatch () submits a batch of commands to the database to run, assuming all commands run successfully. Returns an array of update count components.
When using batch updates in PreparedStatement, use the Addbatch () method to increase the cache after you have set the parameters first.
Note: Only update or INSERT statements can be used in a batch update
Row types in SQL3.0
Array. Array
STURCT, structurally similar to a descriptive alias for the column type
Blob, large binary data file
CREATE TABLE T_blob (
IDNumber (primary key),
FileName varchar (20),
BLOBData blob);
Ps=con.preparestatement ("Insert Intot_blob" + "VALUES (?,?, Empty_blob ())");.
Clob, large text file object.
When using this large object, insert a blank placeholder object when using JDBC to insert the record, and then use the
Select BLOBData from t_blob where id = ' + id + ' FOR update this syntax to perform the actual write operation on the obtained large object
Blod gets the stream to write through the Getbinaryoutputstream () method.
The Getbinarystream () method obtains the stream to get the data stored in the BLOB.
The operation of the CLOB is also the same as the BLOB. The Getasciistream () method is used to read the stored text object, and the Getasciioutputstream () method obtains the stream used to write to the file object.
JDBC2.0 extension
Jndi and Datasourse
JNDI, (named Path service) is also used to store data, but what he stores is a fragmented piece of information.
The Jndi method is under the Javax.naming package
Bind (String name, object obj) binds the name to the object resource. Establishes the association of the specified string and object resource
Lookup (string name) to obtain the previously bound resource from the specified string
Here's how to bind resources and Jndi names
public static void Bind (String context, Object obj) throwsnamingexception
{
Properties Pro = new properties ();
WebLogic's jndiserver number of references
Pro.put (Initialcontext.initial_context_factory, "weblogic.jndi.WLInitialContextFactory");
Pro.put (Initialcontext.provider_url, "t3://localhost:7001");
Context CTX = new InitialContext (PRO);
Ctx.bind (context, obj);//Establish an association of the specified string and object resource
}
Datasourse (data source). Includes the information needed to connect to a database, be able to connect through a data source or database, and sometimes because the information of some connected databases changes,
Therefore, data sources that include database connection information are often used.
Obtaining a bound resource through Jndi
public static Object lookup (String context) throws Namingexception
{
Properties Pro = new properties ();
WebLogic's jndiserver number of references
Pro.put (Initialcontext.initial_context_factory, "weblogic.jndi.WLInitialContextFactory");
Pro.put (Initialcontext.provider_url, "t3://localhost:7001");
Context CTX = new InitialContext (PRO);
return Ctx.lookup (context);//Gets the previously bound resource through the specified string.
}
Connection pool, which maintains a specified number of connections in the connection pool. And after the program has been used, do not close the connection, and then put back to the connection pool waiting for other programs to be used when needed to access,
This allows for substantial savings in destroying and creating connection resource consumption.
JTA Distributed Transactions
Distributed transactions are for multiple different databases at the same time, to ensure that atomic operations are not divided, and no longer write their own commits, and rollback, all to the intermediary server to deal with.
(Two phase commit). That is, the intermediate server sends the SQL statement waiting for the database response, all responding to the successful submission of the operation, or rollback at the same time.
RowSet
Rowset, which is a JavaBean (event mechanism). It enhances the functionality of the ResultSet by enabling rowset to obtain data sources, set isolation levels, and send lookup statements.
An offline operation traversal is also implemented. Rowset also supports pre-compiled statement.
The method in rowset is roughly the same as ResultSet, please consult the Java API Reference documentation when you need to use it.
Object-oriented Database design
IDs are typically used to represent the uniqueness of a record, typically using a business-independent numeric type
The ID of the object ID. Sequence is available only with Oracle, and the object ID (OID) uses the high-low algorithm to become high. In generating low, the object ID is obtained by operation.
The class should object to the table, attribute the corresponding field, and the object records accordingly.
The corresponding table for the class inheritance relationship,
1. Each class builds a table. A table is created for each class of the parent-child class, so the class relationship is clear, but assuming more analogies is not appropriate.
2, only the detailed class to build the table, that is, the attributes in the parent class are evenly allocated to the child class table, that is, the parent class does not build tables, such a table relationship can not use polymorphic
3. All classes correspond to a table, and the method is to add a field to the label to differentiate the parent-child class, but only for cases where the class attribute is less. And the data will be redundant.
Class Association relationship corresponding table
1, one-to-one correlation. There are two approaches to a class relationship as a table, one is to refer to the primary key, that is, the primary key of the other party as the primary key of the foreign key. The second is a foreign key reference, one side refers to the other side of the primary key as its own foreign key, and own the primary key.
2, one-to-many associations. That is, a multi-reference one side of the primary key as a foreign key, multi-terminal itself has a primary key.
3, many-to-many relationships. Many-to-many relationships are implemented through an intermediate table, which refers to the primary key of the two tables as the Federated primary Key, enabling a many-to-many association.
Layering of JDCB applications
Layering is the isolation of functionality. Reduce the coupling between layers and layers.
JDBC Specific explanations (2)
