INSERT, DELETE, UPDATE three SQL statements are three basic statements of database technology. It can be said to be everywhere in the usual web development. If it is simple to construct these SQL statements, on the one hand Give us a lot of workload, and the system flexibility is limited. So can you let the system to remove elements from the page form to construct the SQL statement from the page form? First let us see see generally INSERT, DELETE, UPDATE basic form of three kinds of statements: INSERT INTO table_name (col_1, col_2, col_3,) VALUES (value_1, value_2, value_3 ...) DELETE FROM table_name WHERE col_n = value_n UPDATE table_name SET col_1 = value_1, col_2 = Value_2, col_3 = value_3 where col_x = value_x
We know that borrowing the request.getParameterNames () method in J2EE, you can read the name of all elements in the form, and the element name borrows the Request.GetParameter (ElementName) method to get the value of the element. Assume that we will match the name of the page element and the field name of the underlying database table. Then, in these three statements are not unknown for us, unknown data remains Table_Name, COL_X, and Value_x. Now if we write a method, incoming the Request object, take Table_name, Col_x, Value_x as a parameter into the method, then we can easily construct the SQL statement. But doing so is still flexible, because every hand uses this method we have to operate Table_name, col_x and value_x; on the other hand, don't forget that the field of the string needs to add single quotes and replace strings in the SQL statement. Middle single quotes, integrity, floating point, system functions (such as now (), to_date () and other database functions such as single quotes, if there is no good solution, our method will be subject to Very big restriction. To reach further separation is the best way to make an article on the form element, we can define a set of elements naming rules, different processing for different rules named - set up our definition element naming specifications as follows: 1. Table_name, col_x, value_x This type of element is common elements. We stipulate that this type of element name begins with C_K, and we limit the element name of Table_name. COL_X = Value_x defines it together, and the element is named c_where. Of course, we don't forget that we still need an element to represent what INSERT, DELETE, UPDATE SQL statement. We named C_Genre to this element, which is limited in INSERT, DELETE, and UPDATE. 2. For elements of the corresponding database string type in the form, the processing of single quotes is required in the SQL configuration. This type of element we temporarily called the string type element. String elements We specify its named S_ Database Table Field Name (S = String). 3. For elements that do not need to do but quotation, such as Integer, FLOAT, database system functions, such as now (), to_date (), etc.). We temporarily and simple collective such elements for integer elements. For integer elements, we restrict its naming rules for i_ database table field name (i = integer).
Based on the above specifications we can easily write a JavaBean. code show as below:
/ ** * @version: 1.1 * @time: 2005.03.02 * /
Package com.river.page; import java.util. *; import javax.servlet.http.httpservletRequest;
Public class pageutil {private httpservletRequest request = null;
Public PageUTil () {}
Public void init (httpservletRequest _Request) {this.Request = _Request;}
Public void clear () {if (this.Request! = null) {this.Request = null;}}
Public String Get (String ElementName) {if (Request == Null || Request.getParameter (ElementName) == NULL) {Return ";} else {return request.getParameter (ElementName);}}}
public String get (HttpServletRequest _request, String elementName) {init (_request); return get (elementName);} public String getSQL (HttpServletRequest _request) {init (_request); return getSQL ();} public String getSQL () {String sqlstr = ""; String c_table = get ("c_table"); string c_genre = GET ("c_genre"); string c_where = GET ("c_where"); if (c_genre == null || c_genre.equals (")) {Return "THE Action IS NULL / EMPTY";}} (c_table == null || c_table.equals (")) {Return" unknow table / empty ";} if (c_genre.equalsignorecase (" insert ")) { Java.util.enumeration arg_names = request.getParameterNames ();
String colstr = "", valstr = ""; String arg_name, pre_name, end_name; while (arg_names.hasMoreElements ()) {arg_name = String.valueOf (arg_names.nextElement ()); if (arg_name.length () <2) {Continue;} pre_name = arg_name.substring (0, 2); End_name = arg_name.substring (2);
IF (pre_name.equalsignorecase ("i _")) {color = color "," end_name; if (get (arg_name) .Equals (")) {valstr = valstr ", null ";} else {Valstr = Valstr "," String.valueof (get (arg_name));}}} else} (pre_name.equalsignore ") {color = colstr ", " end_name; if (Get (arg_name) .Equals (") " ) {Valstr = valStr ", null";} else {valstr = valStr ", '" get (arg_name) .ReplaceAll ("'", "'") "'";}}}} (! Colstr. Equals (")) {color = colstr.substring (1); valstr = valstr.substring (1);} SQLSTR =" INTO " C_TABLE " (" Colstr ") VALUES (" Valstr ") "; return sqlstr;} else if (c_genre.equalsIgnoreCase ( "UPDATE")) {java.util.Enumeration arg_names = request.getParameterNames (); String colstr = ""; String arg_name, pre_name, end_name; while (arg_names.hasMoreElements () ) {Arg_name = String.Valueof (arg_names.nextelement) ))). Trim (); if (arg_name.length () <2) {Continue;}
PRE_NAME = arg_name.substring (0, 2); END_NAME = arg_name.substring (2); if (pre_name.equalsignorecase ("i _")) {IF (GET (Arg_name) .Equals (")) {colstr =" , " END_NAME " = null ";} else {color =", " END_NAME " = " Get (arg_name);}} else if (pre_name.equalsignorecase (" s _ ")) {IF (Get (ARG_NAME) . Equals (")) {color =", " end_name " = " get (arg_name);} else {color =", " end_name " = '" get (arg_name) .ReplaceAll ("' "," '' ") " '";}}} If (! Colstr.equals (")) {colstr = colstr.substring (1);} SQLSTR = "Update" C_Table "Set" colStr; IF (! c_where.equals (")) {SQLSTR =" Where " c_where;} Return SQLSTR;} else if (c_genre.equalsignorecase (" delete ")) {sqlstr =" delete from " c_table; if (IF) C_where! = null &&! c_where.equals (")) {SQLSTR =" Where " c_where;}} else {com.river.debug.debug.show (" Unknow Action Typ E: " c_genre); return null;} return SQLSTR;} public string toString () {return" Version 1.0, Date 2005.03.02, author river ";}}
This way we can guide the generation of the SQL statement according to the name of the page element. There is a lot of obvious benefits: 1. Reduce the code work, for many forms of the elements, do not need to write a lot of code, don't worry, don't worry, is there any error, the element name is wrong, single quotes are there? deal with. 2. Universal, stable, easy to maintain, JavaBean is inherent, do not have much explanation. 3. Separate the form content of the surface and the structure of the logical layer SQL statement. Imagine if the database table structure is adjusted, then we will modify the form, it does not use the original write logic. With the comment, if we write a class to automatically execute SQL, you can map to the same action for some basic increases, delete, and change operations, and is not very cool? Of course, the disadvantage of this is also there. That is there is a certain performance loss. Especially when you touch the form element. But I want to be worthwhile for those projects that are not very "harsh".
