A common method for form data storage in JSP

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 and use JSP technology to present pages, servlet technology is used to process a large amount of services, and Bean is used to store data and process some services. In WEB applications, the processing of business data stored in the database is often very heavy. One of the main forms is the storage of form data to the database, the entire application processing process involves a large number of such data storage operations. Each form requires independent Writing of corresponding data storage programs, which takes a lot of time and effort by developers. It is worth studying how to reduce the development workload of form data storage.

Two common forms data storage and processing methods

1. Write the corresponding program code for each form

In JSP pages, JavaBean, or Servlet, use request. the getparameter () function extracts the data submitted by the form one by one, or writes the corresponding JavaBean, automatically retrieves the data to the JavaBean using the setProperty method, and then generates an SQL statement (insert, update, delete ), finally, execute the executeupdate () function to complete data table Storage.

2. automatically generate a JavaBean program code for each data table

The database system must support reading table structures and identifying key fields. Develop a java code automatically generated program by using object-oriented rapid development tools such as PowerBuilder and Delphi. Read the structure of the database table in this program: field name, data type, Data Length, automatically generate a JavaBean code. Define the variable with the same name as the field in the table in this Code, and create the setValue and getValue methods for all variables, create insert, update, and delete functions to generate and execute SQL statements of insert, update, and delete.

On the data processing page of Form submission, write the following code to store form data to the JavaBean:

<Jsp: useBean id = "table" class = "tabledomainbean"/>
<Jsp: setProperty name = "table" property = "*"/>
(Note: table‑bean is the automatically generated an corresponding to a table)

Then, the insert, update, and delete functions in tabledomainbean are called to store data tables and return execution results. For example:

<% Boolean success = table. insert (); %>

The first method is simple and intuitive, but the corresponding data processing program needs to be compiled for each form. For a slightly larger application, the number of forms may be large, the development workload is large, and the development efficiency is low. When adding or decreasing fields in a table structure change, you must modify the corresponding data processing program.

The second method is much easier than the first method. The data processing of each data table is implemented by the corresponding JavaBean, which is automatically generated by the JavaBean without writing. When the table structure changes, you only need to generate a new JavaBean, after java compilation, it is enough to overwrite the original java class. However, this method requires the development of a JavaBean Automatic Generation Program. When the table structure changes, the JavaBean needs to be re-generated and compiled.

This article introduces a simple and common 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 directly stores the data in a data table without any processing. In this case, we can write only one program to process these forms in a unified manner and store the data in the 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. The program file is named DbdataStore. jsp.

1. Call format

The form Action on the webpage is called as follows:

<Form Name = Frm1 Method = Post Action = "DBdataStore. jsp? Tablename = table1 & OperType =... ">

Table1 is the name of the database table to be stored. OperType operations are classified into three types: insert, update, and delete.

<Input type = text name =...>, <Textarea name =...> <Select name =...> The name value in the parameter must be the same as the field name in the data table, DBdataStore. in jsp, the data values of the corresponding field names submitted by the form are extracted one by one. If the input is not defined in the form and the obtained value is null, this field is not processed.

2. view definition using oracle as an Example

1) create a data type view for each column in 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 data table owner

2) create a key column view for 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 the table name
String OperType = request. getParameter ("OperType"); // extraction operation type
String sFieldValue = ""; // stores the field data value submitted by the form.
String fieldname = "", Datatype = "" // store the field name and 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 SQL statement keyword segment

Generate the insert statement keyword segment, for example, insert into table1 (id and values (100 ));

Only use the keyword segment to generate the update. The where part of the delete statement, for example, where id = 100;

When the operation type is update, the data of the keyword segment is not modified in the form of the webpage.

Rs11_stmt.exe cuteQuery (
"SELECT column_name FROM v_pkey_column WHERE table_name =" + tablename + "");
// Obtain the keyword field name while (rs1.next ()){
Fieldname = rs1.getString ("column_name ");
Rs21_stmt.exe cuteQuery ("SELECT data_type FROM v_dbstru WHERE
Table_name = "+ tablename +" AND column_name = "+ fieldname + "");
// Retrieve the data type of the key field
If (rs2.next ()){
Datatype = rs2.getString ("data_type ");
SFieldValue = request. getParameter (fieldname. toLowerCase ());
// Generate the insert statement keyword segment
If (OperType. equals ("insert ")){
InsSql1 + = fieldname + ",";
If (sFieldValue = null )){
// When no key field data value is submitted for a form, this article only processes the data in number type, and the data value is calculated by a sequential 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 the update AND delete statement where: 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) Non-Keyword segment SQL statement generation

Update statement, for example, 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) "comment rs1comment stmt.exe cuteQuery (strSql );
// Obtain the field names and data types of non-Keyword Fields
While (rs1.next ()){
Fieldname = rs1.getString ("column_name ");
Datatype = rs1.getString ("data_type ");
SFieldValue = request. getParameter (fieldname. toLowerCase ());
// If the value of this field is not submitted in the form, the processing of this field is ignored.
If (sFieldValue! = Null )){
// Generate the insert Statement = insSql1 + insSql2, that is, insert into tablename (... And values (...
If (OperType. equals ("insert ")){
InsSql1 + =