Shulou(Shulou.com)06/02 Report--

What is the automatic construction method of SQL sentences in Java EE, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain in detail for you, people with this need can come to learn, I hope you can get something.

INSERT, DELETE and UPDATE SQL statements are the three basic statements of database technology. It can be said to be ubiquitous in normal Web development. If we simply construct these SQL statements by hand, on the one hand, it brings a lot of work to our development, on the other hand, the flexibility of the system is greatly limited. So can the system automatically extract 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_nUPDATE table_name SET col_1=value_1,col_2=value_2,col_3=value_3 WHERE col_x=value_x

We know that you can read the name of all the elements in the form by borrowing the request.getParameterNames () method in Java EE, and you can get the value of that element by borrowing the request.getParameter (elementName) method with the element name. Suppose that in development we make the name of the page element the same as the field name of the underlying database table. Then in these three statements, col_n and value_n are not unknown to us, and the unknown data is only table_name,col_x and value_x. Now if we write a method, pass in the request object, and then pass in table_name,col_x,value_x as an argument to the method, we can easily automatically construct the SQL statement. But this is still inflexible, because on the one hand, every time we use this method, we have to set table_name,col_x and value_x manually. On the other hand, do not forget that the sql statement for the string fields need to add single quotes and replace the string in the middle of the single quotation marks, and integers, floating-point types, system functions (such as now (), to_date () and other database functions) do not need to do single quotation marks processing, if there is no good solution, our method will be very limited. The way to further separate * * is to make an article on the naming of form elements. We can define a set of element naming rules and do different processing for elements named by different rules-let's define the element naming specifications as follows:

1. Elements such as table_name,col_x,value_x are common elements. We specify that the name of this type of element begins with c=common, we restrict the name of the element of table_name to be defined together, and the name of the element as c_where. Of course, let's not forget that we also need an element to represent what kind of SQL statement (INSERT, DELETE, UPDATE) is constructed. We name this element c_genre, and its value is limited to INSERT, DELETE, and UPDATE.

two。 For the elements in the form that correspond to the database string type, you need to do single quotation marks in the SQL construction. Let's call these elements string elements for the time being. We specify the string element as slots + database table field name (s=String).

3. For elements that do not require quotation marks (such as integer, float, database system functions-- such as now (), to_date (), and so on). For the time being, we simply call these elements integer elements. For integer elements, we limit the naming convention to iSuppli + database table field name (i=Integer).

Based on the above specifications, we can easily write a Javabean with the following code:

/ * *

* @ version: 1.1

* @ Time: 2008.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 (0jue 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.equalsIgnoreCase ("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" + cantilever + "(" + 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 (0jue 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.equalsIgnoreCase ("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" + cantilever + "SET" + colstr

If (! c_where.equals ("")) {

Sqlstr + = "WHERE" + c_where

}

Return sqlstr

} else if (c_genre.equalsIgnoreCase ("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 naming of page elements. This has many obvious benefits:

1. Reduce coding work, for many forms of elements, we don't have to write a lot of code, we don't have to worry about which element is left behind, whether there is something wrong with the element name, and whether the single quotation marks are handled.

two。 Universal, stable, easy to maintain, the inherent advantages of Javabean, do not need to explain too much.

3. Separate the form content of the surface layer from the construction of the logical layer SQL statement. Imagine that if our database table structure is adjusted, then we just need to modify the form, regardless of the logic originally written. Incidentally, imagine that if we write another class to automatically execute SQL, then some basic add, delete, and change operations can be mapped to the same action to deal with, and it is not very cool?

Of course, there are shortcomings in doing so. That is, there is a certain degree of performance loss. Especially when it comes to a lot of form elements. But I think it's worth the loss for projects that are not very "demanding".

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.