How to encapsulate database operation statements using XML

In the process of project development, the differences in programming style among project team members and the flexibility of database operation statement SQL bring more and more operation and maintenance difficulties to the project team.
For example:
Retrieve all the data from the user table. Some users write 'select * From user' and some write 'select all from user'. Although no error occurs in the operation, however, when others read the program, they may feel bad.
If such programs differ by an order of magnitude in the project, there will be various styles in the development process, during the maintenance process, I will try my best to scratch my head and curse those who wrote the program (well, at least I will swear at it ).

For the example of the entire article, we create the following table in the database:
Tbl_user
Userid bigint
Username varchar (20)
Password varchar (20)
Createtime date

Tbl_user_info
Userid bigint
Email varchar (64)
Mobile varchar (13)

I. Analysis

A) analyze the SELECT statement

Therefore, using XML to encapsulate database operation statements becomes the first step of Project standardization operations. In this step, we will give a few examples to gradually implement the purpose of encapsulation.
For example, the statement "select username, password from tbl_user" can be analyzed into an XML file in various ways. The following expressions are used:
Analysis 1)
1 <dbtrans name = "selectuser" table = "tbl_user" method = "select">
2 <get>
3 <property name = "username" type = "string"/>
4 <property name = "password" type = "string"/>
5 </get>
6 </dbtrans>
Use dbtrans as the node name in the first line of the sentence,
The property name is the name of the transaction. Here it is "selectuser ",
The attribute table is the name of the requested table. Here it is "tbl_user ",
The attribute method is the method used to operate the database. Here it is "select ",
The sub-node <get> </get> indicates reading data from the database.
The sub-node <property/> is the read database field, where:
Attribute name is the name of the field,
The property type is the type of the field. The setting type can be reflected in subsequent programs.

For the statement "select username, password from tbl_user where userid = 123", based on the appeal analysis, we can describe the XML:
Analysis 2)
1. <dbtrans name = "selectuserbykey" table = "tbl_user" method = "select">
2. <key>
3 <property name = "userid" type = "long"/>
4 </key>
5 <get>
6 <property name = "username" type = "string"/>
7 <property name = "password" type = "string"/>
8 </get>
9 </dbtrans>
If the like operation is used, we can describe 3rd sentences
<Property name = "username" type = "string" match = "like"/>

For the statement "select username, password from tbl_user order by username DESC", XML is analyzed as follows:
Analysis 3)
1. <dbtrans name = "selectuser" table = "tbl_user" method = "select">
2 <get>
3 <property name = "username" type = "string"/>
4 <property name = "password" type = "string"/>
5 </get>
6 <order name = "respcode" match = "DESC"/>
6 </dbtrans>

Such statement analysis can basically complete the SELECT statement operations of the vast majority of common databases, but after all there will still be some unexpected database operation statements, such
When 'select username, password from tbl_user where createtime> '2017-7-16' and createtime <'2017-9-16 ', createtime is included in <key>, in this case, we can portray XML
<Key>
<Property name = "starttime" column = "createtime" type = "date" match = ">
<Property name = "endtime" column = "createtime" type = "date" match = "<">
</Key>

However, even if the preceding work und is used, many special statements cannot be completed, such as "select count (*) from tbl_user", when an XML statement cannot be used to describe the operation, we can introduce the special attribute. For example:
Analysis 4)
1. <dbtrans name = "selectuser" table = "tbl_user" method = "select" special = "select count (*) from tbl_user">
2 </dbtrans>

This attribute represents all special transactions.

B) analyze the insert statement

The insert statement is the most troublesome statement in a relational database, because if you need to create a corresponding data in the tbl_user and tbl_user_info tables, you need to know the value of the primary key of the inserted database. statement can be used in jdbc3.0. return_generated_keys. However, to ensure compatibility, we decided to adopt another method during the operation.
Create a table named tsys_primarykey in the database, which contains three fields:
Tsys_primarykey
Keyid bigint
Tablename varchar (64)
Primarykey varchar (30)
Here, tablename stores the table name, primarykey stores the name of the primary key, and keyid stores the value of the primary key. In this way, the primary key value is obtained before the insert statement operation, add the value to 1 to become the existing primary key and then perform the insert operation. After the operation, we need to update the table tsys_primarykey to ensure data synchronization.
Now we are analyzing the insert statement,
Insert into tbl_user (userid, username, password) values (100, 'test', 'test ')
Insert into tbl_user_info (userid, email, mobile) values (100, 'test @ test.com ', '123 ')

We can describe the XML file as follows:
Analysis 5)
1. <dbtrans name = "insertuser" table = "tbl_user" method = "insert">
2 <primarykey name = "userid">
3 <set>
4 <property name = "username" type = "string"/>
5 <property name = "password" type = "string"/>
6 </set>
7 </dbtrans>
And
1. <dbtrans name = "insertuserinfo" table = "tbl_user_info" method = "insert">
2 <set>
3 <property name = "userid" type = "long"/>
4 <property name = "email" type = "string"/>
5 <property name = "mobile" type = "string"/>
6 </set>
7 </dbtrans>

C) analyze the delete statement

Delete statements are commonly used in two types: delete by key value and delete by all. Therefore, we divide the operation into two types: delete and clear.
For the delete type, for example:
Delete from tbl_user_info where userid = 123
Description:
Analysis 6)
1. <dbtrans name = "deleteuserinfo" table = "tbl_user_info" method = "delete">
2 <key>
3 <property name = "userid" type = "long"/>
4 </key>
5 </dbtrans>

For the clear type, for example:
Delete from tbl_user_info
Description:
Analysis 7)
1. <dbtrans name = "clearuserinfo" table = "tbl_user_info" method = "clear">
2 </dbtrans>

D) analyze the update statement

From the general update operations, we can know that there will be two types of tags when using XML expressions, including <key> and <set>, for example:
Update tbl_user_info set email = 'aaa @ aaa.com 'Where userid = 123
The description is called XML:
Analysis 8)
1. <dbtrans name = "updateuserinfo" table = "tbl_user_info" method = "update">
2 <key>
3 <property name = "userid" type = "long"/>
4 </key>
5 <set>
6 <property name = "email" type = "string"/>
7 </set>
8 </dbtrans>

Ii. Programming

Okay. After analyzing the XML file, we need to design the program. From the analysis of the above implementation, we can clearly see that to achieve the above operations, we must do the following steps:
1: Read XML files
2: locate the corresponding transaction Node
3: Spell SQL statements
4. Database Operations
5. Data Retrieval
6: return data

For the first step of file reading, we can encapsulate all the XML Parse statements and the earlier operation encapsulation.
Enter a class. Here we name it xmlloadfile.

The transaction processing process consists of 2, 5, and 6 steps, which can be encapsulated into the xmltransaction class. Of course, the operation to return data can be extracted separately as a corresponding return class. If this is done to process the returned data packets, for example, XML, vector, hashtable, or map can be returned. The returned data is of the vector type, so step 1 is encapsulated.

Assemble SQL statements and create a class (xmlcombine) independently. Of course, there can also be multiple classes, such as selectcombine and insertcombine. Here we perform unified encapsulation.

Database Operations are encapsulated into a separate class, xmlexecutesql.

All the preceding classes have a uniform export class, which is xmlutils. This class provides several methods for external data operations, such as select, insert, delete, and update, as well as several methods for external programs to access data, such: settransnode (SET transaction node), settransvalue (SET transaction data value), settranskeyvalue (SET transaction key value data value)

Iii. External program calls

For select statements, analysis 1) the program to be compiled is as follows:
Xmlutils util = new xmlutils ();
Util. settransnode ("selectuser ");
Vector vrtn = util. Select (CON );

Analysis 2) is
Xmlutils util = new xmlutils ();
Util. settransnode ("selectuserbykey ");
Util. settranskeyvalue ("userid", 123 );
Vector vrtn = util. Select (CON );

For the insert statement, analyze 5) The program is as follows:
Xmlutils util = new xmlutils ();
Util. settransnode ("insertuser ");
Util. settransvalue ("username", "test ");
Util. settransvalue ("password", "test ");
Vector vrtn = util. insert (CON); // assume that the operation is successful.
Long userid = (long) (hashtable) vrtn. elementat (0). Get ("userid"). longvalue ();

Util. settransnode ("insertuserinfo ");
Util. settransvalue ("userid", userid );
Util. settransvalue ("email", "test@test.com ");
Util. settransvalue ("mobile", "1234567890 ");
Vector vrtn = util. insert (CON );

For Delete Statement Analysis 6) The program is as follows:
Xmlutils util = new xmlutils ();
Util. settransnode ("deleteuser ");
Util. settranskeyvalue ("userid", 100 );
Util. Delete (CON );

For the update statement, analyze 8) The program is as follows:
Xmlutils util = new xmlutils ();
Util. settransnode ("updateuserinfo ");
Util. settranskeyvalue ("userid", 123 );
Util. settransvalue ("email", "aaa@aaa.com ");
Util. Update (CON );

Do you think these SQL operations are neat and comfortable? This method has many benefits. programmers do not have to spell SQL statements too much. encapsulated operations allow all programmers to write neatly and have a unified style.
Good luck, enjoy.

Keli thisishnh@163.com

Word documents placed in this http://keli.nease.net/XMLDB.doc

Latest jar package placed in http://keli.nease.net/iartbean/iartbean.jar

Sample placed in http://keli.nease.net/iartbean/Sample.java
Import com. IART. database. xmlcontrol .*;
Import com. Microsoft .*;
Import java. SQL .*;
Import java. util .*;
Public class sample {
 
Public static void main (string [] ARGs ){

Xmlutils util = new xmlutils ();
Connection con = NULL;
Try {
System. out. println ("[startupservlet] Begin ================================ ============== ");
Util. startupservlet ();
System. out. println ("[startupservlet] End ================================ ============== ");
Con = getconnection ();
System. Out. println ("[getconnection]" + con. tostring ());
//
/* Util. settransnode ("clear ");
Util. Clear (CON );

Util. settransnode ("setpassbyname ");
Util. settransvalue ("logonname", "keli2 ");
Util. settransvalue ("username", "keli3 ");
Util. settransvalue ("password", "hnh 3 ");
Util. insert (CON );
*/
 
Util. settransnode ("backemail", "selectrespcode ");
// Util. settranskeyvalue ("starttime", "00:00:00. 0 ");
// Util. settranskeyvalue ("endtime", "23:59:59. 0 ");
// Util. settranskeyvalue ("docstatusid", "2 ");
 
// Util. settransvalue ("createtime", "current_timestamp ");

 

/* Util. settransnode ("insertdocument ");
Util. settransvalue ("doctypeid", "2 ");
Util. settransvalue ("docstatusid", "1 ");
 
Vector vrtn = util. insert (CON );
*/
Hashtable vrtn = util. Select (con, 0,-1 );
System. Out. println (vrtn. tostring ());
System. Out. println (vector) vrtn. Get ("data"). Size ());
 
Util. settransnode ("backemail", "selectrespcode ");
Vrtn = util. Select (con, 2, 20 );
System. Out. println (vrtn. tostring ());
System. Out. println (vector) vrtn. Get ("data"). Size ());
 
Vrtn = util. Select (con, 3, 20 );
System. Out. println (vrtn. tostring ());
System. Out. println (vector) vrtn. Get ("data"). Size ());
/* Util. settransnode ("selectmaxdoc ");
Vector vresult = util. Select (CON );
Long docid = (long) (hashtable) vresult. elementat (0). Get ("docid ");
 
Util. settransnode ("insertemail ");
Util. settransvalue ("mid", docid. tostring ());
Util. settransvalue ("subject", "test ");
Util. settransvalue ("targetaddr", "test@test.com ");
Util. settransvalue ("sourceaddr", "test@test.com ");
Util. settransvalue ("content", "test@test.com ");
 
Util. insert (CON );
 
 
Util. settransnode ("selectemail ");
Vector vresult1 = util. Select (CON );

For (INT I = 0; I <vresult1.size (); I ++)
{
Hashtable vcolumn = (hashtable) vresult1.elementat (I );
If (vcolumn! = NULL)
System. Out. println ("1" + vcolumn. tostring ());
}
 
*/
 
/* Util. settransnode ("deletebyname ");
Util. settranskeyvalue ("logonname", "Keli ");
Util. Delete (CON );
 

Util. settransnode ("getpassbyname ");
Util. settranskeyvalue ("logonname", "% Keli % ");
Vector vresult2 = util. Select (CON );

For (INT I = 0; I <vresult2.size (); I ++)
{
Hashtable vcolumn = (hashtable) vresult2.elementat (I );
If (vcolumn! = NULL)
System. Out. println (vcolumn. tostring ());
}
*/
} Catch (exception ex)
{
Ex. printstacktrace ();
} Finally {
Try {
Con. Close ();
} Catch (exception ex1)
{
System. Out. println (ex1.getmessage ());
}
}

}
 
Public static connection getconnection () throws exception {
Class. forname ("com. Microsoft. JDBC. sqlserver. sqlserverdriver ");
String strcon = "JDBC: Microsoft: sqlserver: // localhost: 1433; user = NLC; Password = NLC; databasename = NLC ";
Connection conn = drivermanager. getconnection (strcon );
Return conn;
}

}