MySQL common query operation class (JSP + servlet + JavaBean + MySQL)

After one and a half months of training at tengke, I finally learned JSP + servlet + JavaBean + MySQL and the first Java Web solution. The teacher asked us to start a ordering system for six people. This system is nothing special, just like the electronic Mall. Because I want to add, delete, modify, and query a large number of databases, and there are a lot of code, I think this layer of database operations cannot be packaged (sqloperdao), let me operate on the database, use a few lines of code. Then I only modify the SQL statement and the passed parameters.

 

Addition, deletion, modification, and other operations do not return the result set, but search is to return the result set

1) add, delete, and modify operations first

Public int buildconn (string SQL) throws exception {
Connection conn = NULL;
Statement stmt = NULL;
Int result = 0;
Boolean flag;
Try {
Conn = sqlconn. Conn ();
Stmt = conn. createstatement ();
Result1_stmt.exe cuteupdate (SQL );
System. Out. println ("Congratulations on your successful execution" + Result + "records! ");
Flag = true;
} Catch (exception e ){
System. Out. println ("Sorry! Execution failed! "+ E. getmessage ());
Flag = false;
} Finally {

Stmt. Close ();
Conn. Close ();
}
Return result;
}

2) the query operation is a little more troublesome. We usually package the query result set into a JavaBean combination. For example, I have a login JavaBean with only name and password, name, password, two columns of user_list in the corresponding table. now we need to read all the data in user_list, read one, and put it in a JavaBean, And put multiple data in list <JavaBean>. First, we can talk about how to read all the data in the database using the getstring method. Of course, if the attribute of the table in the database is not of the varchar type, this method cannot be used.

This generic query only applies to tables of the string type.

/**
* General query: input the SQL statement to be queried.
* Returns a list <string []>
*/
Public list <string []> query (string SQL) throws exception {
This. querylist = new arraylist <string []> ();
Statement stmt = NULL;
Resultset rs = NULL;
Connection conn = NULL;
Conn = This. sqlconn. Conn ();
Stmt = conn. createstatement ();
Rs = stmt.exe cutequery (SQL );
Resultsetmetadata rsmd = Rs. getmetadata ();
Int columncount = rsmd. getcolumncount ();
// 2. Obtain the table body information. Determine whether the table body exists. If not, exit.
While (true ){
If (false = Rs. Next ()){
Break;
}
String datalist [] = new string [columncount];
For (INT body = 1; body <= columncount; body ++ ){
String columnvalue = Rs. getstring (body );
Datalist [body-1] = columnvalue;
}
This. querylist. Add (datalist );
}
Rs. Close ();
Stmt. Close ();
Conn. Close ();
Return querylist;
}

This method reads the table content and stores it in a list <string []>. However, the disadvantage is that the read data has not been packaged into a JavaBean, it is cumbersome to package it by yourself. So on the basis of this method, I wrote another method to package list <string []> into a JavaBean. Note that the general operation class of this database must be generic and the class sqloperdao <t>. Otherwise, the object type cannot be converted to the desired JavaBean type.

/**
* Input a JavaBean and SQL query statement.
* Put the returned result set in a collection list <JavaBean>
* This is a value assignment operation. Only the string type is copied. If the Set Method in the JavaBean file is not of the string type
* It can be rewritten
* Public void setattribute (string Str ){
* This. Attribute = integer. parseint (STR );
*}
* T you can input a JavaBean
*/
Public list <Object> query (Object T, string SQL) throws exception {
List <Object> resultlist = new arraylist <Object> ();
Class Cl = T. getclass ();
Class mycl = Class. forname (Cl. getname ());
Field [] fields = mycl. getdeclaredfields ();
List <string []> querylist = This. Query (SQL );
For (string [] Results: querylist ){
Object instance = mycl. newinstance ();
Int I = 0;
For (field: fields ){

// Use the Java reflection mechanism to obtain the method name of the Set Method in the JavaBean. Because the set method is composed of the set + attribute name (string), use the string to piece together.
String setmethod = "set" + String. valueof (field. getname (). charat (0 )). touppercase () + "" + field. getname (). substring (1 );
Method set = mycl. getmethod (setmethod, String. Class );
Set. Invoke (instance, Results [I]);
I ++;
}
Resultlist. Add (instance );
}
Return resultlist;
}

 

Now the general data query is complete. Let's take a look at how to use it?

JavaBean: Class userlist {private string name;

Private string password; // The get and set methods are automatically generated by myeclipse}

Dao layer: Read all data in user_list of the database.

Public static void main (string [] args0) throws exception {

Sqloperdao sod = new sqloperdao ();
Userlist T = new userlist ();
String SQL = "select * From user_list ";
List <userlist> List = sod. Query (T, SQL );
For (eatery E: List ){
System. Out. println (E. geteateryname ());
}

// In this way, you can write a database query operation. In this way, the DaO layer we write is 70% less than the code of others.

 

}