Jdbc basics (II)

As mentioned above, the registration driver Program There are multiple methods, class. forname (); is an explicit load. When a drive
After the animation class is loaded by classloader, drivermanager registers the driver class instance during the dissolution process.
This call occurs automatically, that is, the drivermanager. registerdriver () method is automatically called. Of course
We can also directly call drivermanager. registerdriver () to register the driver. However, in my experience.
In Ms browsing, the applet fails to call this method. That is to say, the JVM embedded in ms in the browser
The implementation is invalid.
In addition, we can use the system property JDBC. drivers to load multiple drivers:
System. setproperty (\ "JDBC. Drivers \", \ "driver1: driver2:...: Drivern \"); among multiple drivers
Are separated by \ ": \". In this way, JDBC searches sequentially until the first driver that can successfully link the specified URL is found.
Program.
In the basic section, we will not introduce the advanced features of datasource.

After successfully registering the driver, we can use the static method getconnection of drivermanager.
Reference to the database link:
Connection conn = drivermanager. getconnection (URL );
If the connection is successful, the connection object Conn is returned. If it is null or an exception is thrown, it indicates that no
Establish a connection with the database.
There are three overload methods for the getconnection () method. The simplest method is to only provide the data source:
Getconnection (URL), the other is to provide some data source information at the same time, that is, getconnection (URL, properties ),
The other is to provide the data source, user name and password: getconnection (URL, user, passwod), for the data source information.
If we want to provide more information during the link, we can press the information into a properties. Of course, we can directly Press
Enter the username and password. You can also press in to the specified character set, encoding method, default operation, and other information.

After we get a connection, we have a channel to deal with the database. We can do what we want.
Done.
Let's first introduce some general operations:
If we want to operate tables in the database, we need to bind a statement first:
Statement stmt = conn. createstatement ();
This statement is then used to execute the operation. For the basic operation purpose, two results can be returned.
Operation. The result set is resultset. If the update operation is performed, the number of records in the operation is int.
Note that there are only two distinct SQL operations: Read operations (query operations) and write operations (more
New operations). Therefore, create, insert, update, drop, delete, and other operations that rewrite data are update operations.

Resultset rs = stmt.exe cutequery (\ "select * from table where XXXXX \");
Int x = stmt.exe cuteupdate (\ "delete from table where ......\");
If you want to use executequery to execute an update operation, but do not assign it to a handle,
Of course some experienced programmers won't do this.
As for processing the result set, we will discuss it in the next section, because it is operable and only supports query operations.
The result set is returned. to complete an operation, you need to disable the database connection.
Before learning about JDBC, take this step as the most important step in JDBC operations.
Keep reminding you to close the Database Link !!!!!!!!!!!

Follow the steps described above. The example is as follows: (Note: In order to follow the steps described above, this example
Sub-accounts are not the best)
Try {
Class. forname (\ "org. gjt. Mm. MySQL. Driver \");
} Catch (exception e ){
System. Out. println (\ "failed to load driver: \" + E. tostring ());
Return;
} // For experience like me, you can directly identify the cause of the exception from a few simple words in E. tostring,
// If you are a newbie, you should select to capture its subclass. How do you know which exceptions to capture? A simple
// Without try {}, directly class. forname (\ "org. gjt. Mm. MySQL. Driver \");, compile
// The interpreter will tell you which exceptions you want to capture. Of course, this is a clever method, and it is best to be yourself.
// Go to the JDK documentation and it will tell you which exceptions are to be captured in each method.
Connection conn = NULL;
Try {
Conn = drivermanager. getconnection (
\ "JDBC: mysql: // host: 3306/MySQL \",
\ "User \",
\ "Passwd \");
Statement stmt = conn. createstatement ();
Resultset rs = stmt.exe cutequery (\ "select * from Table \");
// RS processing
[RS. Close ();]
[Stmt. Close ();]
}
Catch (exception e ){
System. Out. println (\ "database operation exception: \" + E. tostring ());
}
Finally {
Try {conn. Close ();} catch (exception ){}
} // No matter how the database process you learned is operated, if you believe me, from now on,
// Please shut down the database Code Write to finally block, cut!

About statement object:
As mentioned above, the statement object is used to bind the operation to be executed. There are three methods to execute it:
That is, the executequery () used to execute the query operation, the executeupdate () used to execute the update operation, and the executeupdate used to execute
Execute () for a dynamic unknown operation ().
JDBC does not check the SQL statement to be executed during compilation, but only looks at it as a string.
The program knows whether the SQL statement is correct or not.
A statement object can only have one result set in the activity at the same time. This is tolerant, that is, even if there is no
Call the close () method of resultset. As long as the second result set is opened, the previous result set is closed.
If you want to operate on multiple result sets at the same time, you need to create multiple statement objects. If you do not need to operate at the same time, you can
To operate multiple result sets in sequence on a statement object.

Here I have to note that many people will use a statement to perform nested queries and then ask
Why can't I loop? The reason has been clarified above. Let's analyze the nested query in detail:
Connection conn = NULL;
Statement stmt = NULL;
Conn = .......;
Stmt = conm. createstatement (xxxxxx );
Resultset rs = stmt.exe cutequery (sql1 );
While (Rs. Next ()){
STR = Rs. getstring (XXXXX );
Resultset RS1 = stmt.exe cutequery (\ "select * from table where field = STR \");
}
When stmt.exe cutequery (\ "select * from table where field = STR \") is assigned to RS1, the implicit operation
RS has been disabled. Can you continue the loop?
Therefore, if you want to operate multiple result sets at the same time, you must bind them to different statement objects. Fortunately, a connection
Object can create any number of statement objects without re-obtaining the link.

About the options for getting and setting statement: You just need to check its getxxx method and setxxx method. Here
As a basic knowledge, you can only mention the following:
Setquerytimeout: Set the timeout limit for an SQL statement.
Setmaxrows: set the number of rows that can be accommodated in the result set.
Setescapeprocessing. If the parameter is true, the driver will escape and replace the SQL statement before sending it to the database.
Otherwise, let the database handle it by itself. Of course, these default values can be queried through the get method.

Two sub-classes of statement:
Preparedstatement: for multiple executions of the same statement, statement sends the SQL statement to the data each time.
Database, the efficiency is obviously not high. If the database supports pre-compilation, preparedstatement can first issue the statement to be executed
To it, and then execute each time without having to send the same statement, the efficiency is certainly improved. Of course, if the database does not support pre-compilation,
Preparedstatement works like statement, but it is inefficient and does not require manual intervention.
In addition, preparedstatement also supports receiving parameters. After pre-compilation, you only need to transmit different parameters for execution.
Improved performance.

Preparedstatement PS = conn. preparestatement (\ "select * from table where field =? \");
PS. setstring (1, parameter );
Resultset rs = ps.exe cutequery ();

Callablestatement: a subclass of preparedstatement. It is only used to execute stored procedures.
Callablestatement SC = conn. preparecall (\ "{call query ()}\");
Resultset rs = cs.exe cutequery ();

For more advanced knowledge, we will introduce it in JDBC advanced applications.

As the last part of the basic knowledge, let's talk about the processing of the result set. Of course, it is about the processing of the general result set.
As for the multiple result sets returned by stored procedures, we will introduce them in advanced applications.
If the SQL statement executes a query operation, a resultset object is returned.
Clearly displayed to the user, the resultset must be processed. The resultset returns a record that meets the conditions in the table.
The processing of the resultset needs to be performed row by row, and the processing of columns in each row can be performed in any order (note that this is only the JDBC rule
Some JDBC implementations still require the user to process columns in order.) In fact, although
However, you can process columns in any order, but you can get high performance if you follow the order from left to right.

here, we will explain the resultset object from the underlying layer. You won't be able to get this knowledge in any book about jdbc
because it is a matter of database vendors. the resultset object actually maintains a two-dimensional pointer. The first dimension points to the current
row. At first, it points to the first row of the result set, therefore, if you want to access the first line, You need to first next (), and then each row will be
you need to first next () to access, and then the second-dimensional Pointer Points to the column, as long as you go to Rs. when getxxx (column) is used,
connection is used to retrieve the real data from the database, otherwise, no machine can store all the data to be retrieved.
.
therefore, remember that if the connection is closed, it is impossible to retrieve data from the resultset.
many people asked me if I could get a resultset to write it to the session and close the connection. Therefore,
do not connect it every time. I can only tell you that you think very well, but, yes Incorrect! Of course, in javax. the JDBC high-level applications in the SQL package include cacherow and webcacherow. You can cache the result set, but it is no different from setting up a data structure to extract all the values in the
resultset row set at a time.
columns in the access row can be accessed by field name or index. the following is a simple search result program:

Resultset rs = stmt.exe cutequery (\ "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, it is the most common to use while for next (). If next () returns false
It indicates that no rows are available, but sometimes we may not even have a row. If there is a record and we do not know how many rows are there
If you want to process different records, use the following process:

If (Rs. Next ()){
// Because next () is already in place, do {} while (); should be used to process the processed records.
Do {
Int I = Rs. getint (1 );
String A = Rs. getstring (\ "A2 \");
} While (Rs. Next ());
}
Esle {
System. Out. println (\ "no matching record is obtained! \");
}

Type conversion:
The getxxx method of resultset will try to convert the SQL data type in the result set to the Java data type. Fact
Most types can be converted, but there are still a lot of obfuscation that cannot be converted. For example, you cannot convert a SQL float
Java date, you cannot convert varchar \ "We \" to Java Int.

Large value:
For a value greater than the value returned by getmaxfieldsize in statement, use the common getbytes () or getstring ()
It cannot be read. Fortunately, Java provides a method for reading input waves. For large objects, we can use Rs. getxxxstream ()
To obtain an inputstream. The types of XXX include ASCII, binay, and Unicode.
The same stream type. Generally, getbinaystream () is used for binary files and getasciistyream () is used for text files.
Getunicodestream () is used for text files with Unicode characters. The corresponding database field types should be blob, clob, and
Nlob.

Obtain the result set information:
In most cases, programmers understand the database structure and the columns in the result set.
I don't know what columns are in the result set and what type it is. In this case, you can use getmetadata () to obtain the result set:

Resulsetmetadata rsmd = Rs. getmetadata ();
Rsmd. getcolumncount () returns the number of columns.
Getcolumnlabel (INT) returns the title of the column corresponding to the int.
Getcolumnname (INT) returns the name of the column corresponding to the int in the database.
Getcolumntype (INT) returns the Data Type of the column corresponding to the int in the database.
Getcolumntypename (INT) returns the name of the Data Type of the column corresponding 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.
JDBC basic application instance (2) [dynamic database access]

One of the above friends asked, what if I know the table of the currently linked database when I have already joined the database?
As a matter of fact, if you have already linked the database, you will be able to know the situation in this database, not just the situation in the Table above:

sometimes (I have only seen it once so far), we do not know its structure or the content of a new database.
, how can we obtain the database?
the real example is as follows: My friend's company received a list about the name of the database used by the other party.
\ "titanium, to tell the truth, I have never known such a database before because of my ignorance, let alone how to
access it. Now, my friends have to look at what \ "Things \" is, of course, beyond all expectations. so I had to contact you.
after receiving the call, I first asked him what platform he was running on. If the connection was made, he said that he could create an ODBC data source in windows.
Haha, that is to say, you can use Java to establish a connection. OK
You can only create a connection. Then you can get all the metadata of this database:
databasemetadata dbmd = Conn. getmetadata (); then you can get the following message from this object
information:
geturl (); // return the URL linked to this database, of course it is known, or how do you connect to
GetUserName (); // return the user connected to the database, as above
isreadonly (); whether the database is read-only
getdatabaseproducename (); // database product name
getdatabaseproduceversion (); // version
getdrivername (); // driver
getdriverversion (); // driver version

the preceding content has no significance.

Resultset gettables (string catalog,
String schemapattern,
String tablenamepattern,
String [] types)
All the \ "tables \" in the database can be obtained. The tables here include tables, views, system tables, temporary spaces, aliases, and synonyms.
For each parameter:
String catalog, table Directory, which may be null, \ "null \" matches all
String schemapattern, table outline, same as above
String tablenamepattern, table name, same as above
String [] types, table type, \ "null \" matches all, available types:
Table, view, sysem table, global temporary, local temporary, alias, synonym

For example:
Databasemetadata dbmd = conn. getmetadata ();
Resultset rs = dbmd. gettables (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 ();
}
You can use dbmd (not rsmd). getcolumns (
String catalog,
String schemapattern,
String tablenamepattern,
String columnnamepattern
)
You can not only obtain information in rsmd, but also obtain the column size, decimal places, precision, default value, and column in the table.
Location and other information.
There are two other methods to obtain the stored procedure and index information by calling and obtaining table information:
Resultset getprocedures (
String catalog,
String schemapattern,
String procedurepattern
);
Resultset getindexinfo (
String catalog,
String schemapattern,
String table,
Boolean unique, Boolean approximate
);