Objective: To understand how to share the database connection code, use preparesatement, configure and use the database connection pool, and analyze the database access process, encapsulate the database access process into dbbean; l modify the user. in Java, you can use dbbean to query users by page number to perform database operations. l modify user. add User information in Java, use dbbean to perform database operations, configure database connection pools, and modify the database connection code in dbbean, using the connection pool to complete connection 1. dbbean writing in the previous few lectures, there are multiple methods to connect to the database to operate on the database, and there are a lot of copying and pasting in the code writing process, this means that many codes can be shared in each method. So there is a process that can be shared in the database connection code? The basic process of database operations is as follows: l load the driver, l create a connection, l write SQL statements, l create statement objects, l execute SQL statements, and l process the results; l close related objects. In the above process, many operations are the same, different aspects include: different functions need to write different SQL statements to process the results. Encapsulate this process to get the following code (there are many encapsulation methods): Package JavaBean; import Java. SQL. *; import Java. util. *; public class dbbean {private connection con; private preparedstatement stmt; private resultset RS; // gets the connection object public connection getconnection () throws exception {If (con = NULL) {// specifies the driver class required to connect to the database. forname ("oracle. JDBC. driver. oracledriver "); // establish a connection with the database. Con = drivermanager. getconnection ("JDBC: oracle: thin :@ 192.168.0.170: 1521: fhdn "," Scott "," Tiger ");} return con;} // create the statement object public preparedstatement getstatement (string SQL) throws exception {con = getconnection (); stmt = con. preparestatement (SQL); Return stmt;} // execute the public resultset executequery (string SQL, arraylist paras) throws exception {stmt = getstatement (SQL) query with result set ); if (paras! = NULL) {object o [] = paras. toarray (); For (INT I = 0; I <O. length; I ++) {If (O [I] instanceof string) {stmt. setstring (I + 1, (string) O [I]) ;}} return stmt.exe cutequery () ;}// execute the public int executeupdate (string SQL, arraylist paras) throws exception {stmt = getstatement (SQL); If (paras! = NULL) {object o [] = paras. toarray (); For (INT I = 0; I <O. length; I ++) {If (O [I] instanceof string) {stmt. setstring (I + 1, (string) O [I]) ;}} return stmt.exe cuteupdate () ;}// close the public void close () {try {Rs. close () ;}catch (exception e) {}try {stmt. close () ;}catch (exception e) {}try {con. close () ;}catch (exception e) {}} Note: Because pre-compiled statement objects are used, parameters must be provided during SQL statement execution, because the number of parameters is not fixed, arraylist objects are used for encapsulation. Only parameters of the string type are processed in the Code. If other types of parameters are used, you need to write support code. 2. Modify the getuserbypage method. The basic process of using the methods in dbbean is as follows: l create the dbbean object; l compile the SQL statement; l encapsulate the parameters required by the SQL statement; l execute the SQL statement; l close the object. Public arraylist getuserbypage (INT pageno) {int number = 10; // number of records displayed on each page int begin = (pageno * Number)-9; int end = pageno * number; int Index = 1; dbbean DB = new dbbean (); // arraylist users = new arraylist (); string SQL = "select * From usertable"; resultset RS; try {rs = db.exe cutequery (SQL, null); While (RS. next () {// The record before in is not displayed if (index <begin) {index ++; continue ;}// If (index> end) break; index ++; string username = Rs. getstring (1); string userpass = Rs. getstring (2); // Java. util. date Birthday = Rs. getdate (3); // int age = Rs. getint (4); User user = new user (); User. setusername (username); User. setuserpass (userpass); users. add (User) ;}} catch (exception e) {e. printstacktrace ();} finally {dB. close ();} return users;} 3. Modify the adduser method. The adduser method is used as an example to introduce Number of SQL statements. The basic access process is the same as described in 2. The code for the modified adduser method is as follows: public Boolean adduser () {dbbean DB = new dbbean (); string SQL = "insert into usertable values (?,?) "; Arraylist <string> paras = new arraylist <string> (); paras. add (username); paras. add (userpass); int n = 0; try {n = db.exe cuteupdate (SQL, paras);} catch (exception e) {system. out. println (E. tostring ();} dB. close (); Boolean B; // indicates whether the addition is successful. If (n> 0) B = true; else B = false; return B ;} 4. advantages of using the connection pool in the previous example, each user needs to create a database connection for each access and release the connection after use. If users can share database connections with each other for multiple accesses, the access efficiency can be greatly improved. You can use the database connection to share the connection. When the server starts, multiple connections are created. If you need to access the database, you can directly obtain a connection from the connection pool, after use, directly put back the connection pool. Another advantage of using the connection pool is that if there are too many clients, the connection pool can protect the database server by controlling the number of connections without slowing the database response and thus affecting the server performance. 5. configuration of the Connection Pool in Tomcat includes three parts: l place the JDBC driver of the database under the Lib of the tomcat installation directory. Some versions of Tomcat should be placed under the Common/lib installation directory. L configure CONF/server in the installation directory. open Server in XML. add the following code before
Reference material: Basic tutorial on Java Web Programming
