Abstract
This article takes the Oracle database as an example to introduce a simple and universal form data storage and processing method when developing WEB applications using JSP technology to reduce the development workload and provide the main program code.
Introduction
J2EE (Java 2 Enterprise Edition) technology has been widely used in Web application development. The JavaBean and Servlet technologies provide developers with a clearer development environment. JSP technology is used to display pages, and Servlet technology is used to complete a large number of business processes. , using Beans to store data and some business processing. In WEB applications, the processing work of storing business data in the database is often very arduous. One of the main forms is to store form data in the database. The entire application processing process involves a large number of such data storage operations, and each form needs to be processed. It takes developers a lot of time and energy to write the corresponding data storage program separately. What methods to use to reduce the development workload of form data storage is a question worth studying.
Two common form data storage and processing methods:
1. Write corresponding program code for each form
in the JSP page or JavaBean or Servlet. Use the request.getparameter() function to extract the data submitted by the form one by one, or write the corresponding JavaBean. Use the setProperty method to automatically fetch the data into the JavaBean, then generate SQL statements (insert, update, delete), and finally execute the executeupdate() function to complete the data table storage.
2. Automatically generate a JavaBean program code for each data table.
The database system must support users to read the table structure and identify key fields. Use object-oriented rapid development tools, such as PowerBuilder, Delphi, etc., to develop a Java code automatic generation program. In this program, the structure of the database table is read: field name, data type, data length, and a JavaBean code is automatically generated. In this code, define variables with the same names corresponding to the fields in the table, establish setValue and getValue methods for all variables, and establish insert, update, and delete functions to handle the generation and execution of SQL statements for insert, update, and delete respectively.
In the data processing page submitted by the form, write the following code to store the form data in JavaBean:
<jsp: useBean id="table" class="table1_bean" /＞
＜jsp：setProperty name="table" property="*" /＞
(Note: table1_bean is the automatically generated JavaBean corresponding to a certain table mentioned above)
Then call the insert, update, and delete functions in table1_bean to complete the data table storage and return the execution results. like:
<%boolean success =table.insert(); %>
The first method is simple and intuitive, but you need to write a corresponding data processing program for each form. For slightly larger applications, the number of forms may be large, resulting in a heavy development workload and low development efficiency. When the table structure changes, such as adding or subtracting fields, the corresponding data processing program needs to be modified.
The second method is much simpler than the first. The data processing of each data table is implemented by the corresponding JavaBean. The JavaBean is automatically generated and does not need to be written. When the table structure changes, you only need to regenerate a new JavaBean and overwrite it after compiling with Java. The original java class can be used. However, this method requires the development of a JavaBean automatic generation program, and JavaBeans need to be regenerated and compiled when the table structure changes.
Introducing a simple and universal method to store form data
in WEB application development. Many forms are submitted to the backend server after simple data verification on the front-end browser. The server does not perform any processing on the data and directly stores the data into a database. table. In this case, we can just write a program to process these forms uniformly and store the data in a corresponding data table. This method also requires the database system to support table structure reading and key field identification. We use JSP technology to write this program, and the program file is named DbdataStore.jsp.
1. Calling format
The Action calling method of the form in the web page is as follows:
<Form Name=Frm1 Method=Post Action="DBdataStore.jsp? tablename=table1&OperType=…">
table1 is the table name of the database table where the data will be stored, and the OperType operation There are three types: insert, update, and delete.
The name values in <input type=text name=…>, <textarea name=…><select name=…> in the form should be the same as the field names of the data table. The corresponding fields submitted by the form are extracted one by one in DBdataStore.jsp If there is no input defined in the form and the resulting value is a null value, the field will not be processed.
2. View definition taking Oracle as an example
1) Create a data type view for each column of the table
CREATE OR REPLACE VIEW v_dbstru AS SELECT table_name, column_name, data_type, data_length, data_precision, data_scale, column_id
FROM all_tab_columns WHERE owner='user1'; //user1 is the owner of the data table.
2) Create the key column view of the table
CREATE OR REPLACE VIEW v_pkey_column AS
SELECT b.table_name, b.column_name, b.position
FROM all_constraints a, all_cons_columns b
WHERE a.owner=b.owner AND a.constraint_name=b.constraint_name AND a.owner='user1' AND a.constraint_type='P';
3. Main program code
1) Program initialization
String tablename=request.getParameter(" tablename"); //Extract table name
String OperType=request.getParameter("OperType"); //Extract operation type
String sFieldValue="";//Storage the field data value submitted by the form
String fieldname="", Datatype="" //Storage field name, field data type
int iFieldvalue=0;
String updateSql="", whereSql=" where ", insSql1="", insSql2="", opSql="", strSql="";
ResultSet rs1=null, rs2=null;
insSql1="insert into "+tablename+" (";
insSql2="values(";
2) Generate the key field part of the sql statement
and generate the key field part of the insert statement, such as: insert into table1(id and values(100));
only use the key fields to generate the update and delete statement where parts , such as: where id=100;
when the operation type is update, the data of the key fields will not be modified in the web form.
rs1=Stmt.executeQuery("SELECT column_name FROM v_pkey_column WHERE table_name='"+tablename+"'"); //Get the key field name
while(rs1.next()){
fieldname=rs1.getString("column_name");
rs2=Stmt.executeQuery("SELECT data_type FROM v_dbstru WHERE table_name='"+tablename+"' AND column_name='"+fieldname+"'"); //Get the key field data type if(rs2.next()){
Datatype=rs2.getString("data_type");
sFieldValue=request.getParameter(fieldname.toLowerCase());
//Generate the key field part of the insert statement if(OperType.equals("insert")){
insSql1+=fieldname+",";
if((sFieldValue==null) ){
//When the key field data value is not submitted in the form, this article will only process it as a numeric type, and the data value will be calculated according to the serial number.
rs2= Stmt. executeQuery("SELECT max("+fieldname+")+1 FROM "+tablename); rs2. next(); iFieldvalue=rs2.getInt(1); insSql2+=Integer.toString(iFieldvalue)+"," ; }else if(Datatype.equals("DATE")){
insSql2+= "To_Date('" + sFieldValue + "','YYYY-MM-DD'),";
}else if(Datatype.equals("VARCHAR2") || Datatype.equals("CHAR")){
insSql2+="'" + sFieldValue+"',";}
else /*NUMBER, FLOAT */ insSql2+=sFieldValue+","}
//Generate update and delete statement where part: where fieldname=... AND
if(OperType.equals("update") || OperType.equals("delete")){
if(Datatype.equals("DATE")){
whereSql+=fieldname+"=To_Date('" + sFieldValue + "','YYYY-MM-DD') AND ";
}else if(Datatype.equals("VARCHAR2") || Datatype.equals("CHAR")){
whereSql+=fieldname+"='" + sFieldValue+"' AND ";}
else /*NUMBER, FLOAT */ whereSql+=fieldname+"="+ sFieldValue+" AND ";}
}
}
whereSql=whereSql.substring(0, whereSql.length()-4);
3) The sql statement of the non-key field part generates
an update statement, such as: update table1 set column1=value1,... where id=100
insert statement, such as: insert into table1(id, column1,…)values(100, value1,…)
updateSql="update "+tablename+" set ";
strSql="SELECT column_name, data_type, data_length, data_precision, data_scale FROM v_dbstru a "+"where table_name='"+tablename+"' AND a.column_name not in (SELECT b.column_name FROM v_pkey_column b where b.table_name=a.table_name )";
rs1=Stmt.executeQuery(strSql); //Get the field name and data type of non-key fields
while(rs1.next()){
fieldname=rs1.getString("column_name"); Datatype=rs1.getString("data_type"); sFieldValue=request.getParameter(fieldname.toLowerCase()); //If the form does not submit the value of the field, ignore the field Processing if((sFieldValue!=null)){
//Generate insert statement =insSql1+insSql2, that is, insert into tablename(… and values(…)
if(OperType.equals("insert")){ insSql1+=fieldname+",";
if(Datatype.equals("DATE")){
insSql2+= "To_Date('" + sFieldValue + "','YYYY-MM-DD'),";
} else if(Datatype.equals("VARCHAR2") || Datatype.equals("CHAR")){
insSql2+="'" + sFieldValue+"',";}else /*NUMBER,FLOAT*/ insSql2+= sFieldValue+",";}
//Generate update statement=updateSql+whereSql, that is, update tablename set... where fieldname=... if(OperType.equals("update")){
if(Datatype.equals("DATE")){
updateSql+=fieldname+"=To_Date('" + sFieldValue + "','YYYY-MM-DD'),";
}else if(Datatype.equals("VARCHAR2") || Datatype.equals("CHAR")){
updateSql+=fieldname+"='" + sFieldValue,1}+"',";}else /*NUMBER,FLOAT*/ updateSql+=fieldname+"="+sFieldValue+",";}))
rs1.close();
4) Generate a complete sql statement and execute
if(OperType.equals("insert"))
opSql=insSql1.substring(0, insSql1.length()-1)+")"+insSql2.substring(0, insSql2.length()-1)+")";
if(OperType.equals("update"))
opSql=updateSql.substring(0, updateSql.length()-1)+" "+whereSql; if(OperType.equals("delete"))
opSql="delete FROM "+tablename+" "+whereSql;
//The complete sql statement opSql has been generated
try{sqlnrows=Stmt.executeUpdate(opSql);}
catch(SQLException e){out.println("SQLException:"+opSql);}
4. Features
This method uses this program uniformly for all such directly stored forms. It is universal and does not need to be used for every form or every form. The corresponding program is developed independently for the data table. The development workload is very small and the call is very simple. At the same time, when the table structure changes, there is no need to modify the DBdataStore.jsp program. This program can also be rewritten as Servelet, and the calling format is <Form Name=Frm1 Method=Post Action="DBdataStoreServelet?tablename=table1&OperType=…">.
Conclusion
In a web application, if the form data requires further data verification or processing in the server background after submission, the second method needs to be used. But in many cases, users enter or modify data in the form, use JavaScript to simply verify or process the data on the front-end browser, and then submit it. No processing is done on the back-end server, and the data submitted by the form is directly stored in the database. in a table. At this time, using the third method is very simple and can greatly reduce the developer's workload.