1. Load the driver.
There are multiple methods for registering the driver, Class.forName (); is an explicit load. When a driver class is classloader loaded, DriverManager registers the instance of the driver class during the dissolution process. This call happens automatically, This means that the Drivermanager.registerdriver () method is automatically called,
Class.forName ("Oracle.jdbc.driver.OracleDriver");
Of course we can also directly call Drivermanager.registerdriver () to register the driver, but. Ms's Browse applet cannot succeed in invoking this method, which means that the JVM built into the browser by MS is invalid for the implementation of the method.
Drivermanager.registerdriver (New Oracle.jdbc.driver.OracleDriver ());
In addition, we can also use the System Properties jdbc.drivers to load multiple 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
After successfully registering the driver, we can use DriverManager static method getconnection to get a reference to the database link:
Connection conn = drivermanager.getconnection (URL);
If the link is successful, the connection object conn is returned, and if NULL or throws an exception, the connection to the database is not established.
There are three overloaded methods for the getconnection () method,
One is the simplest to give only the data source namely: getconnection (URL),
url = "JDBC:ORACLE:THIN:@127.0.0.1:1521:ORCL";
conn = drivermanager.getconnection (URL);
The other is 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 other is to give the data source, user name 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);
Oracle's URL value 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. If we want to give more information in the link to the information can be pressed into a properties, of course, can be directly pressed into the user name password, but also can be pressed into the specified character set, encoding or default operation and other information.
3. Bind the statement to execute through the link handle.
After getting a link, there is a channel for dealing with the database. We can do what we want. Let's start by introducing some general operations: if we are going to manipulate the tables in the database, we need to bind a statement for the first reason:
Statement stmt = Conn.createstatement ();
The statement is then used to perform the operation. There can be two results returned if the query operation is executed, returned as the result set resultset, and if the update operation is performed, the number of records for the operation is returned INT.
Note that the SQL operation strictly distinguishes only two, one is the read operation (query operation), the other is the 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 ...");
It is possible to perform an update operation with ExecuteQuery, but do not assign it to a handle, although a slightly inexperienced programmer 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.
A very necessary step for the completion of one operation is to close the database link, and before you know more about JDBC, take this step as the most important step in the JDBC operation.
Example:
try{
Class.forName ("Org.gjt.mm.mysql.Driver");
}catch (Exception e) {
SYSTEM.OUT.PRINTLN ("Driver not successfully loaded:" +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 is operating, from now on, you must write the database shutdown code to the finally block, earnestly!
About Statement objects:
As mentioned earlier, the statement object is used to bind the action to be performed, and there are three methods of execution on it:
The ExecuteQuery () that is used to perform the query operation,
ResultSet rs = stmt.executequery ("Select * from table");
Executeupdate () to perform the update operation
int x = stmt.executeupdate ("Delete from table where ...");
and execute () that is used to perform 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, and if True indicates that SQL is a SELECT statement, you can get the result set through Getresultset (), and if it is false, SQL is either a DML statement or a DDL statement.
JDBC does not detect the SQL statement to execute at compile time, just looks at it as a string and only knows when the driver executes the SQL statement.
Attention:
A statement object can have only one result set at the same time. This is tolerant, that is, even if you do not call the close () method of ResultSet, opening the second result set implies closing the previous result set. So if you want to operate on multiple result sets at the same time, It is necessary to create multiple statement objects, and if you do not need to manipulate them at the same time, you can manipulate 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 if 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 as many statement objects as you want without having to retrieve the links again.
Options for getting and setting statement: Just look at its GetXXX method and Setxxx method to understand, here as a basic knowledge only mention the following:
SetQueryTimeout, set a time-out limit for SQL execution.
setMaxRows, sets the number of rows that the result set can hold.
Setescapeprocessing, if the argument 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 executions of the same statement, statement each time the SQL statement is sent to the database, which is significantly less efficient, and if the database supports precompilation, PreparedStatement can send the statement you want to execute one at a time, and then each execution without having to send the same statement, of course, efficiency increases, of course, if the database does not support precompilation, PreparedStatement will work like statement, It is inefficient and does not require user intervention. In addition, the PreparedStatement also supports receiving parameters. After pre-compilation, it can be executed with the transfer of different parameters, which greatly improves the performance.
PreparedStatement PS = conn.preparestatement ("Select * from table where field =?");
Ps.setstring (1, parameter);
ResultSet rs = Ps.executequery ();
CallableStatement: is a subclass of PreparedStatement that is just used to execute stored procedures.
CallableStatement sc = Conn.preparecall ("{Call query ()}");
ResultSet rs = Cs.executequery ();
If the SQL statement performs a query operation, it returns a ResultSet object, and the resultset must be processed if the query results are to be displayed to the user at last. ResultSet returns a record that matches a condition in a table. The processing of the resultset is done on a row-by-line basis, and for each row of columns, it can be in any order
(Note that this is only a requirement for 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 any order, you can get higher performance if you press left-to-right order.
Here from the bottom to explain the ResultSet object, the ResultSet object actually maintains a two-dimensional pointer, the first dimension is pointing to the current row, initially it points to the first row of the result set, so if you want to access the first row, you must first next (), then each row will first next () To access, and then the second dimension of the pointer to the column, as long as you go to rs.getxxx (column), only through the connection and then go to the database to get the real data out, otherwise there is no machine can really put the data to be taken in memory. So, be sure to remember that If the connection is closed, it is impossible to get the data from the resultset again.
Someone asked if you could take a resultset and write it to the session and then close the connection so that you don't have to link it every time. The idea is very 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 pulls all the values out of the RESULTSET rowset once.
Access the columns in the row, which can be accessed 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, and if Next () returns false, there are no more rows available. But sometimes we may not even have a line, and if there is a record and do not know how many lines, At this point, if you want to have records and no records for different processing, you should use the following process to judge:
if (Rs.next ()) {
Because the next (), the records should be used 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:
ResultSet's GetXXX method will try to convert the SQL data type in the result set to the Java data type, the fact that most types can be converted,
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 get a inputstream,xxx type including Ascii,binay,unicode by Rs.getxxxstream ().
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 text files 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 an error code that can be queried for errors.
Source data
There are two provenance data in JDBC, one is database source data and the other is ResultSet source data.
The source data is the data structure that describes 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 title of the column corresponding to the INT
getColumnName (int) returns the name of the column that corresponds to the int in the database.
Getcolumntype (int) Returns the data type in the database for the column that corresponds to the Int.
Getcolumntypename (int) returns the name of the data type of the column that corresponds to the int in the data source.
isreadonly (int) returns whether the column corresponding to the int is read-only.
isnullable (int) returns whether the column corresponding to the int can be empty
Database source data
DatabaseMetaData
GetURL (), get the URL of the connection database
Getdatabaseproductname () Get the name of the database product
Getdriverversion () Gets the version number of the JDBC driver in string form
Gettables () Get all the tables for that user in the database
GetUserName () Gets the database user name.
ResultSet gettables (String catalog, String schemapattern,string tablenamepattern,string[] types)
You can get all of the "tables" in the library, where tables include tables, views, system tables, temporary spaces, aliases, synonyms
For each parameter:
String catalog, table directory, 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
For example:
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 detailed 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 you get the information in RSMD, you can also 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 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-re-divided, requiring atomic operations to succeed at the same time. A transaction is the boundary of a bound atomic operation.
To use a transaction in JDBC, you first use the connection call Setautocommite (False) method to set Autocommit (commit) to false. Opening a transaction turns off autocommit. No business is going to put Setautocommite (true)
When a transaction is processed, execution succeeds and confirms after the SQL statement is sent, and the commit message is sent using the connection call commit () method in the try block, and the execution fails after the SQL statement is sent.
The connection call rollback () method is used in the Catch statement block to send the rollback information, or you can do a rollback operation (subjective reason) when needed.
Problems and transaction isolation levels generated by JDBC transaction concurrency
1, Dirty reads (dirty read), read the data not submitted.
2, non-repeatable read (Unprpeatable read), two reads to different data, is to maintain at the same point in time to read the same data two times, can not make the query data changes.
3, Phantom Read (Phantom), two times 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.
Transaction_read_uncommitted can be read as submission data.
Transaction_read_committed can avoid dirty reads and not be able to read uncommitted data, most commonly used isolation level the default isolation level for most databases
Transaction_repeatable_read can avoid dirty reads, repeat reads,
Transaction_serializable can 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 (bidirectional scrolling), this result set can not only scroll in two directions, relative positioning, absolute positioning, and can modify the data information.
Scrolling properties
Next (), this method moves the cursor down one record.
Previous (), which allows a record to be moved on the cursor prior to the previous record.
Absolute (int row), you can use this method to jump to the specified record location. Positional success Returns True, False is not successfully returned, the cursor does not move if the return value is false.
Afterlast (), after the cursor jumps to the last record, where 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 (), with the Puma pointing to the final record.
Relative (int rows), relative positioning method, the parameter value can be positive negative, the parameter is positive, the cursor moves down the specified value from the current position, the parameter is negative, the cursor moves up the specified value from the current position.
Type_forward_only, unidirectional, which indicates the type of ResultSet object that the pointer can only move forward. Non-scrollable.
Type_scroll_insensitive, bidirectional, that indicates the type of ResultSet object that can be scrolled but is not usually affected by other changes.
Type_scroll_sensitive, bidirectional, which indicates the type of ResultSet object that can be scrolled and is typically affected by other changes. This attribute is not supported for some databases.
When you want to use a scrollable result set, you specify the parameters when statement is created before you can use the
Statement st=null; (int,int) (scrollable properties, updatable features)
St=con.createstatement (reusltset.type_scroll_insensitive,resuleset.concur_updatable)
ResultSet the result set, first use Movetoinsertrow () to move the cursor to a buffer 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 ().
The ability to use updatable result sets depends on the support of the database driver used, and on the single table with the primary key field (which may be the federated primary key) and not the ability to have a table connection.
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
The ability to 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 (), executes all 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 for execution, and returns an array of update counts if all of the commands are executed successfully.
When using bulk update in PreparedStatement, use the Addbatch () method to join the cache after setting the parameters first.
Note: Only update or INSERT statements can be used in a bulk update
Row types in SQL3.0
Array, arrays
STURCT, structure 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 such syntax to perform actual write operations 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 CLOB operation 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, establishing 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 ();
Jndi Server parameters for WebLogic
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), which contains the information required to connect to a database, can be connected through a data source or database, and sometimes due to changes in the information of some connected databases,
Therefore, data sources that contain database connection information are often used.
Obtaining a bound resource through Jndi
public static Object lookup (String context) throws Namingexception
{
Properties Pro = new properties ();
Jndi Server parameters for WebLogic
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, keep the connection pool with a specified number of connections, and do not close the connection after the program has been used, and then put back to the connection pool waiting for other programs to be used when needed,
This can save a lot of the cost of destroying and creating connection resources.
JTA Distributed Transactions
Distributed transaction is for many different databases at the same time operation, 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 to wait for the database response, all responding to the success of the operation before committing, or rollback at the same time.
RowSet
Rowset, which is a JavaBean (event mechanism) that enhances the functionality of the ResultSet by rowset to obtain a data source, set the isolation level, or send a lookup statement,
Also implemented offline operation traversal, rowset also supports precompiled statement.
The methods in rowset are roughly the same as ResultSet, consult the Java API Reference documentation when you need to use them.
Object-oriented Database design
IDs are typically used to represent the uniqueness of a record, typically using a business-independent numeric type
The object ID object's id,sequence is available only to Oracle, which uses the high-low algorithm to get the object ID (OID), which is used to generate the low-level, by operation.
Class should object to table, attribute corresponding field, object corresponding record.
Class inheritance Relationship table,
1, each class to build a table, for the parent-child class each class corresponding to create the table, this method class relationship is clear, but if the class is more than appropriate.
2, only the concrete class to build the table, that is, the parent class is evenly distributed to the child class table, that is, the parent class does not build tables, this table relationship can not use polymorphic
3, all classes corresponding to a table, this method is to add a field in the label to distinguish the parent-child class, but only for the case of less class properties, and the data will be redundant.
Class Association relation table
1, a one to one association, the class relationship corresponds to a table when there are two ways to refer to the primary key, that is, the other party refers to the primary key as a foreign key has as its own primary key. The second is the foreign key reference, the other side refers to the other party's primary key as its own foreign key, and own the primary key.
2, a one-to-many association, that is, a multi-reference one side of the primary key as a foreign key, multi-terminal own primary key.
3, many-to-many relationships, many-to-many relationships are achieved through the intermediate table, the intermediate table refers to the primary key of the two tables as a federated primary key, you can achieve many-to-many associations.
Layering of JDCB applications
Stratification is the isolation of functions, which reduces the coupling between layers and layers.
