Jsp+oracle form data Storage processing method

Taking Oracle database as an example, this paper introduces a simple and common form data storage processing method when using JSP technology to develop Web application, in order to reduce the development workload and provide the main program code.

Java 2 Enterprise Edition technology has been widely used in Web application Development, in which the JavaBean,Servlet Technology for developers to provide a clearer development environment, Using the JSP Technology Performance page, using Servlet technology to do a lot of business processing, using beans to store data and some business processing. In Web applications, business data storage to the database processing work is often very heavy, one of the main forms is the form data storage to the database , the entire application process involves a large number of such data storage operations, It takes a lot of time and effort for a developer to write a separate data store program for each form. What methods are used to mitigate the development workload of form data storage is a problem worth studying.

Two common forms of data storage processing methods

1, for each form to write the appropriate program code

In a JSP page or JavaBean or Servlet , use request. The GetParameter () function extracts the data submitted by the form individually, or writes the corresponding JavaBean, uses the SetProperty method to automatically fetch the data into the JavaBean, and then generates the SQL statement (Insert,update,delete). Finally, execute the executeupdate () function to complete the data table store.

2. Automatically generate a JavaBean program code for each datasheet

The database system must support the ability of users to read the table structure and identify key fields. Using object-oriented rapid development tools, such as PowerBuilder, Delphi, and so on, the development of a Java code automatic generation program. Read the structure of the database table in the Program: field name, data type, data length, and automatically generate a JavaBean code. In this code, define the variable with the same name as the field in the table, establish the SetValue and GetValue methods for all the variables, and establish the INSERT, update, and delete functions to handle the SQL statement generation and execution of INSERT, UPDATE, delete, respectively.

In the Data processing page submitted by the form, write the following code to store the form data in JavaBean:

(Note: Table1_bean for the above automatically generated javabean corresponding to a table)

The INSERT, UPDATE, delete function in Table1_bean is then called to complete the data table store and returns the execution result. Such as:

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

The first method is straightforward, but you need to write the appropriate data handlers for each form. For a slightly larger application, the number of forms may be large, the development effort is high, and the development effort is inefficient. Table structure changes if you increase or decrease the field, you need to modify the corresponding data handler.

The second method is much simpler, the data processing of each datasheet is realized by corresponding JavaBean, JavaBean automatically generated, do not need to write, the table structure changes only need to regenerate new JavaBean, after Java compilation, the original Java class can be overwritten. But this method needs to develop JavaBean automatic generation program, the table structure changes when the JavaBean need to regenerate and compile.

A simple and general method is introduced to realize form data storage

In the development of Web application, many forms are submitted to the backend server after the simple data check of the foreground browser, and the data is stored in a data table directly by the server without any processing. In this case, we can simply write a program that unifies these forms and stores the data in a corresponding datasheet. The method also requires the database system to support table structure reading and key field recognition. We use JSP technology to write the program, the program file named Dbdatastore.jsp.

1. Calling format

The action call method for the form in the Web page is as follows:

Table1 for the table name of the database table to be stored by the data, the Opertype operation type is divided into three kinds: insert,update,delete.

The ,

2. View definition with Oracle as example

1 Set the table column data type view

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 datasheet.

2 Create a 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, the main program code

1) Program initialization

String tablename=request.getparameter ("tablename");//Extract table name

String opertype=request.getparameter ("Opertype");//extract operation type

String sfieldvalue= "";//the field data value that holds the form submission

String fieldname= "", datatype= ""//Holding 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 SQL Statement key field section

Generate the INSERT statement key field section, such as INSERT INTO Table1 (ID and values (100));

Generates the Update,delete statement where part using only critical fields, such as where id=100;

When the action type is update, the data for the key field is not modified in the page form form.

Rs1=stmt.executequery ("Select column_name from V_pkey_column WHERE table_name= '" +tablename+ "");
Take the key field 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+");//Fetch key field data type
if (Rs2.next ()) {
Datatype=rs2.getstring ("Data_type");
Sfieldvalue=request.getparameter (Fieldname.tolowercase ());
Generate INSERT statement key field section
if (Opertype.equals ("Insert")) {
Inssql1+=fieldname+ ",";
if ((Sfieldvalue==null)) {
When a form does not submit a key field data value, this article is only processed numerically, and the data value is computed by the next stream 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,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 Non-critical field partial SQL statement generation

Update statements, 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)//Take non-critical Field field name and data type
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, the processing of the field is ignored
if ((Sfieldvalue!=null)) {
Generate INSERT statement =INSSQL1+INSSQL2 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 the 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 has been generated opsql
Try{sqlnrows=stmt.executeupdate (opsql);}
catch (SQLException e) {out.println ("SQLException:
"+opsql);}

4, features

This procedure is used uniformly by this method for all forms that are stored directly, with generality, without having to develop the program independently of each form or table of data, with very little development effort and simple invocation. At the same time, when the table structure changes, do not modify the DBDATASTORE.JSP program. This procedure can also be rewritten as Servelet, calling the format

Conclusion

In a Web application, the second method is required if the form data is submitted and requires further data validation or processing by the server background. However, in many cases, users enter or modify data in the form, use JavaScript in the foreground browser to simply check or process the data, and then submit, without any processing on the backend server , to store the form submission data directly into a table in the database. This is a simple way to use the third method, which can greatly reduce the developer's workload.