Using XML to encapsulate the implementation of database operation statements _ MySQL

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, if you extract all the data from the user table, some users write the data as "select * fromuser" and some users write the data as "selectallfromuser ", although there will be no errors during the project development process, the differences in programming styles and the flexibility of SQL statements for database operations of Project team developers make the project team more and more difficult to operate and maintain.
For example, if you extract all the data from the user table, some users write "select * from user" and some users write "select all from user ", although there will be no errors in the operation, other people may feel bad when reading the program.
If such programs differ by an order of magnitude in the project, there will be various styles in the development process, in the maintenance process, we will try our best to scratch the head and curse those who wrote the program.

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
2
3
4
5
6
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 ",
Subnode Read data from the database.
Subnode 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 "select username, password from TBL_USER where userid = 123" statement, we can describe the XML as follows based on the above analysis:
Analysis (2)
1.
2.
3
4
5
6
7
8
9
If the like operation is used, we can describe 3rd sentences

For the statement "select username, password from TBL_USER order by username desc", XML is analyzed as follows:
Analysis (3)
1.
2
3
4
5
6
7
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 '", In this case, we can portray XML:

">

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
2
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.
2
3
4
5
6
7
And
1
2
3
4
5
6
7

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
2
3
4
5

For the clear type, for example:
Delete from TBL_USER_INFO
Description:
Analysis (7)
1
2

D. analyze the UPDATE statement

From the general update operations, we can know that two types of tags will appear when the XML description is used, including And For example:
UPDATE TBL_USER_INFO set email = 'AAA @ aaa.com 'where userid = 123
The description is called XML:
Analysis 8)
1.
2
3
4
5
6
7
8

II. program design

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. fetch data
6. return data

For the first step of file reading, we can encapsulate all the XML parse statements and the previous operations into 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)

3. 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, the analysis (5) 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 );

The delete statement analysis (6) program is as follows:
XMLUtils util = new XMLUtils ();
Util. setTransNode ("deleteUser ");
Util. setTransKeyValue ("userid", 100 );
Util. delete (con );

For the update statement, the analysis program (8) 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.

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;
}

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;
}
}