Automatic Construction of SQL statements in J2EE

The insert, delete, and update statements are the three basic statements of database technology. in general web development, processing is everywhere. if we simply construct these SQL statements by hand, on the one hand, it brings a lot of work to our development, and on the other hand, the system flexibility is greatly limited. so can the system automatically retrieve elements from the page form to construct SQL statements based on certain rules? First, let's look at the basic forms of general insert, delete, and update 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 the request. getparameternames () method in J2EE can be used to read the names of all elements in the Form. With the element name, the value of this element can be obtained using the request. getparameter (elementname) method. Suppose that the name of the page element is consistent with the field name of the underlying database table during development. In these three statements, col_n and value_n are not unknown, and table_name, col_x, and value_x are left for the unknown data. Now, if we write a method, pass in the request object, and then use table_name, col_x, value_x as the parameter input method, we can easily construct an SQL statement automatically. However, this is still not flexible, because on the one hand, we have to manually set table_name, col_x and value_x every time we use this method; on the other hand, do not forget to add single quotes and replace single quotes to the string fields in SQL statements, while database functions such as integer, floating point, and system functions (such as now () and to_date) you do not need to process the single quotes. If we do not solve these problems well, our method will be very limited. The best way to achieve further separation is to post on the form Element naming. we can define a set of element naming rules by ourselves, perform different processing on the elements named by different rules. Let's define the element naming specifications as follows:
1. elements such as table_name, col_x, and value_x are common elements. We stipulate that the names of these elements should start with c_k (C = common). we restrict the names of table_name elements to c_table, col_x = value_x, and define the element names as c_where. of course, don't forget that we still need an element to indicate what kind of SQL statements (insert, delete, update) are constructed. We name this element c_genre. Its value is limited to insert, delete, and update.
2. single quotation marks are required in SQL construction for elements corresponding to the database string type in the form. These elements are now called string elements. The name of a string element is S _ + database table field name (S = string ).
3. for elements that do not need to be processed with quotation marks (such as integer, float, database system functions-such as now (), to_date (), and so on ). For now, we can simply refer to these elements as integer elements. For integer elements, the naming rules are limited to I _ + database table Field Names (I = integer ).

Based on the above specifications, we can easily write a JavaBean. The Code is as follows:
/**
* @ 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 ";
}
If (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 _")){
Colstr = colstr + "," + end_name;
If (get (arg_name). Equals ("")){
Valstr = valstr + ", null ";
} Else {
Valstr = valstr + "," + String. valueof (get (arg_name ));
}
} Else if (pre_name.w.signorecase ("s _")){
Colstr = colstr + "," + end_name;
If (get (arg_name). Equals ("")){
Valstr = valstr + ", null ";
} Else {
Valstr = valstr + ", '" + Get (arg_name). replaceall ("'", "'' ") + "'";
}
}
}
If (! Colstr. Equals ("")){
Colstr = colstr. substring (1 );
Valstr = valstr. substring (1 );
}
Sqlstr = "insert into" + c_table + "(" + colstr + ") values (" + valstr + ")";
Return sqlstr;
} Else if (c_genre.w.signorecase ("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 {
Colstr + = "," + end_name + "=" + Get (arg_name );
}
} Else if (pre_name.w.signorecase ("s _")){
If (get (arg_name). Equals ("")){
Colstr + = "," + end_name + "=" + Get (arg_name );
} Else {
Colstr + = "," + 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.w.signorecase ("delete ")){
Sqlstr = "delete from" + c_table;
If (c_where! = NULL &&! C_where.equals ("")){
Sqlstr + = "where" + c_where;
}
} Else {
Com. River. Debug. Debug. Show ("unknow action type:" + c_genre );
Return NULL;
}
Return sqlstr;
}
Public String tostring (){
Return "version 1.0, date 2005.03.02, author River ";
}
}

In this way, we can guide the generation of SQL statements based on the names of page elements. This has many obvious advantages:
1. Reduce coding. For many forms of elements, we don't need to write a lot of code, so we don't have to worry about which element has fallen, whether the element name is wrong, and whether the single quotation marks are processed.
2. Common, stable, and easy to maintain. The inherent advantages of JavaBean do not need to be explained too much.
3. Separate the form content from the logic-layer SQL statement structure. Imagine that if the structure of the database table is adjusted, we only need to modify the form, so we don't have to deal with the logic processing that was previously written. With another note, if we want to write another class to automatically execute SQL statements, some basic addition, deletion, and modification operations can be mapped to the same action for processing, isn't it great?

Of course, there are also some shortcomings. There is a certain amount of performance loss. Especially when there are many form elements. However, I think it is worthwhile for those projects that are not very "harsh.