JDBC learning notes (2)
Thinkersky [email: yanghuangming@rongji.com]
Hello everyone, after another day, today's Fuzhou has experienced a baptism of a storm, and it is a lazy. It is a lazy. Commissioning for a day, the head is a little dizzy, relax, listen to some music, write some east east.
Through yesterday's learning, the importance of JDBC in JSP application and the concept of JDBC have a preliminary understanding. To do this, see how to use JDBC API to access the database.
I am stupid, learning the programming is from others, so in order to have a sensibility to access the JSP through the JDBC access database management system, let's take a simple routine:
Package com.rongji.deemo;
Import java.sql.connection;
Import java.sql.driverManager;
Import java.sql.statement;
Public class dataconn {
Public dataconn () {
}
Public static void main (String [] args) {
Try
{
// Load the driver
// The following code is to load the JDBD-ODBC driver
Class.Forname ("com.mysql.jdbc.driver);
//establish connection
// Second step is to connect to DBMS with an appropriate driver, look at the following code:
String url = "JDBC: mysql: // localhost: 3306 / ums_db? Useunicode = true & characterencoding = GB2312
// Create a connection with URL
Connection con = DriverManager.getConnection (URL);
// Then create a JDBC declaration
Statement Stmt = con.createstatement ();
String query = "CREATE TABLE GOODS"
"(Goods_name Varchar (60),"
"Price float,"
Sales Integer, "
"Total Integer";
// Execute a declaration
Stmt.executeUpdate (query);
// Close statement
Stmt.close ();
// Close connection
C. close ();
System.out.println ("Successful Operation! Get off work :)");
}
Catch (Exception E)
{
// Output exception information
System.err.Println ("SQLEXCEPTION Meet When Creating Goods Table:" E.GETMESSAGE ();
E.PrintStackTrace ();
}
}
}
From this simple routine we can see: JSP calls JDBC API Access Database Management System is implemented by the following five steps:
(1) Load a specific JDBC driver
In order to connect with a specific data source, the JDBC must load the corresponding driver. These drivers are all statements: Class.Forname ("driver name"); There is a tip of this, we can fill in a series of driver names in "Driver Name", such as "Sun.jdbc.odbc.jdbCodbcDriver: Oracle.jdbc.Driver.OracleDriver"); The middle is separated by colon. Jsp will search the driver in the list order and load the first driver connected to a given URL. When searching the driver list, JSP will skip the driver containing untrustful code, Unless he is from the database management system to be open, (2) Ren a connection to the database management system with the registered driver
The second step we have to do is to establish a connection to the database management system with a registered driver, which is implemented by the DRIVERMANAGER Class. Particularly, it is important to note that the value of the String type URL parameter, the URL represents a data source of a specific database management system to be connected. The use of different database drivers, the URL is different. The routine loaded "com.mysql.jdbc.driver" driver, URL value "JDBC: mysql: // localhost: 3306 / ums_db? Useunicode = true & characterencoding = GB
2312
"
If the "Oracle.jdbc.driver.Oracledriver" driver is loaded, the URL should be "JDBC: Oracle: Thin: @Host Name: Port Number: Service Name". The value of the URL of other drivers, you should see the appropriate text.
The getConnection () method in the routine has only one parameter String URL, which represents the ODBC data source. If you connect large databases, you need three parameters: String Url, strng user, string password. User and Password represent the username and password of the database management system. The general large databases such as Oracle, MS SQL Server, DB2 are necessary. Small databases such as Access, FoxPro, etc. are not needed.
If the connection is successful, a CONNECTION class object CON is returned. The operation of the database will be based on the CON object. The getConnection () method is a static method for the DriverManager class. You don't have to generate an object of the DRIVERMANAGER class when you use, you can call it directly using the class DRIVERMANAGER.
(3) Creating a statement statement, execute SQL statement
Instantiate a CONNECTION class object Con, successfully established a connection to the database management system. The third step we have to do is to generate an object STMT of a Statement class using the CON object. This object is responsible for passing the SQL statement to the database management system, if the SQL statement produces a result set, the STMT object also returns the result set to a RESULTSET class.
There are three main methods for the Statement class:
ExecuteUpdate (String SQL)
ExecuteQuery (String SQL)
Execute (String SQL)
ExecuteUpdate (STRING SQL) method is used to perform a DDL type SQL statement, which creates, modifies, deletes an object of the database management system, usually does not return a result set.
The ExecuteQuery (STRING SQL) method is used to execute a SELECT statement for a query database. If there is data that meets the query condition, the method returns a ResultSet class object that contains the corresponding data, otherwise the next () method of the object will return false. The Execute (String SQL) method is used to perform a SQL statement that may return a stored procedure or a dynamically generated number of result sets. If the stored procedure or SQL statement produces a result set, the method returns false. If multiple result sets are generated, the method returns TRUE. We can comprehensively use the Statement class getResultSet (), getUpdateCount (), getMoreResults () method to retrieve different result sets.
When the server compiles the JSP program, the server does not check the SQL statement to be executed, just use it as a String object. Java virtual machine is performed only when the client is issued, and the SERVLE is explained to the database management system when the SQL statement is passed to the database management system. For erroneous SQL statements, SQLEXCEPION is generated during execution. In fact, all JSP statements operated with JDBC are related to the database management system and the corresponding driver, which is more controlled by the JSP. These statements can only be checked in the actual explanation to check whether it can be successfully implemented, so be sure to declare and capture exceptions:
Try {
.
} catch (SQLEXCEPTION E)
{
Sytem.err.println ("SQLException:" E.getMessage ());
}
Otherwise, the JSP program cannot be compiled into a servlet.
(4) Close Statement object
A Statement object can call ExecuteQuery (String SQL), Execute (STRING SQL) method multiple times, and interact with the database management system multiple times after opening. But a Statement object can only open a result set at the same time, and the opening of the second result set is hidden to close the first result set. If you want to operate multiple result sets, you must create multiple Statement objects, perform SQL statements on each Statement object to get the appropriate result set.
(5) Turn off the Connection object
After processing the procedure of the database, be sure to turn the Connection object to release the system resources occupied by JDBC. Use the DriverManager Static Class Initialization New Connection object to generate a system error in the premise that the Connection object is not closed. And a Connection object that has been established can initialize multiple Statement objects at the same time. The CONNECTION object corresponding to different database management systems can initialize the number of Statement objects. It is 50 in Oracle.
I don't know if the above description is detailed, grace, in fact, I can do it again, it is very simple .ok, Beijing time at 18:20, I have already got off work. 88
