Implementation of database operation statements using XML encapsulation

xml| Package | data | database | statement

In the process of project development, the programming style difference of project team members and the flexibility of database operation statement SQL bring more and more difficulty to operation and maintenance to the project team.
For example: from the user table to remove all the data, some people will be written "SELECT * from user", some people will be written "Select all from user", although there will be no errors in the operation, but in other people read the process of the program will have a bad feeling.
If this program difference in the number of items in the order of many, then in the process of development, the program will appear in a variety of styles, in the maintenance process will be desperately scratching their heads and curse those who wrote the program.

For an example of the entire article, now 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)

First, analysis

A, analyzing SELECT statements

The

then uses XML to encapsulate the database action statement as the first step in the project normalization operation. In this step, we will take a few examples to gradually achieve the purpose of encapsulation.
such as the "SELECT USERNAME, PASSWORD from Tbl_user" statement, parsing into an XML file can be written in a variety of ways, we now use the following expression:
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,
property name is the name of the transaction, here is "Selectuser",
Property table is the name of the request table, here is "Tbl_user",
Property method is the way to manipulate the database, here is "select",
child node <get></get> meaning reads data from the database. The
child node <property/> is a read database field where the
property name is the name of the field, and the
property type is the type of the field, where the setting type is reflected in the following program.

For the Select USERNAME, PASSWORD from Tbl_user WHERE userid=123 statement, we can describe the XML as:
Parsing (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 you are using a like operation, we can describe the 3rd sentence as a <property Name= "username" type= "string" match= "like"/>

For the "SELECT USERNAME, PASSWORD from Tbl_user order by USERNAME DESC" statement, XML is parsed 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" Desc "/>
7 </dbtrans>
Such statement analysis can basically complete the majority of the normal database of the SELECT statement, but there are still some of our unpredictable database operation statements will appear, such as
" SELECT USERNAME, PASSWORD from Tbl_user WHERE createtime > ' 2003-7-16 ' and createtime< ' 2003-9-16 ' At the same time createtime in <key>, we can describe XML as:
<key>
<property name= "StartTime" column= "Createtime" "Type=" date "match=" > "
<property name=" Endtime "column=" Createtime "type=" date "match=" < ">
</key>

But even with the above workarounds, there are a lot of special statements that cannot be done, such as "SELECT COUNT (*) from Tbl_user", where the operation will occur when the XML statement cannot be depicted, and this time 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 means that all special transactions are specifically represented.

B, parsing INSERT statements

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 that was inserted into the database. You can use Statement.return_generated_keys to get it in JDBC3.0, but if it is for compatibility, we decide to use another approach to implement it.
We create a table in the database named: Tsys_primarykey, which includes three fields, as follows:
Tsys_primarykey
keyID BIGINT
TableName VARCHAR ()
PRIMARYKEY VARCHAR
where TableName saves the table name, PRIMARYKEY saves the name of the primary key, keyID the value of the primary key, so that the value of the primary key is taken before the INSERT statement operation, and the value is added by 1. To become an existing primary key, and then insert, and then we need to update the Tsys_primarykey this table to ensure that the data is synchronized.
Now we're starting to parse the INSERT statement,
INSERT INTO Tbl_user (USERID, USERNAME, PASSWORD) VALUES (+, ' test ', ' test ')
Insert I NTO Tbl_user_info (USERID, EMAIL, MOBILE) VALUES (MB, ' test@test.com ' , ' 123456789 0 ')

When depicted as an XML file, we can describe the following
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, parsing the DELETE statement

The most commonly used DELETE statements can be divided into two categories, one is to delete the key value, one is all deleted, so we divided this operation into two types, delete and clear
For the delete type, for example:
DELETE from Tbl_user_info WHERE userid=123
Described as:
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 clear types, examples are:
DELETE from Tbl_user_info
Described as:
Analysis (7)
1 <dbtrans name= "Clearuserinfo" table= "Tbl_user_info" method= "clear" >
2 </dbtrans>

D, parsing UPDATE statements

From the usual operation of update we can know that there will be two kinds of tags when using XML description, 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>

Second, program design

OK, after analyzing the XML file, we need to get into the design of the program. From the above analysis we can clearly see that to achieve the above operations, we have to do the following steps:
1 reading XML files
2 Locate the corresponding trading node
3 Spelling SQL statements
4 Database Operations
5 Fetch data
6 Return Data

For the first step of reading the file, we can encapsulate all the XML parse statements as well as the previous operation encapsulation into a class, where we named Xmlloadfile.

The process of transaction processing includes 2,5,6 three steps, which can be encapsulated into xmltransaction classes. Of course the return data this operation can be extracted separately as a corresponding return class, if this is to do processing in the returned data message, such as can return Xml,vector or hashtable or map. Here tentatively returns the data to the vector type, so the 6th step is encapsulated in.

Assemble SQL statements, create a separate class (Xmlcombine), of course, can also be divided into several, such as Selectcombine,insertcombine, where we carry out a unified package.

Database operations are individually encapsulated into a class, xmlexecutesql.

All of the above classes unify an export class, here for Xmlutils. This class provides several methods for manipulating external data, such as SELECT, INSERT, delete, update, and several methods for accessing data from external programs, such as: Settransnode (set up trading nodes), Settransvalue ( Set transaction data value), Settranskeyvalue (SET Transaction key value data value)

Third, external program calls

For the SELECT statement, the program that is required to parse (1) 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 INSERT statements, the Analysis (5) procedure is as follows
Xmlutils util = new Xmlutils ();
Util.settransnode ("Insertuser");
Util.settransvalue ("username", "test");
Util.settransvalue ("Password", "Test");
Vector Vrtn = Util.insert (con); Assuming 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 the DELETE statement parsing (6) The procedure is as follows
Xmlutils util = new Xmlutils ();
Util.settransnode ("DeleteUser");
Util.settranskeyvalue ("userid", 100);
Util.delete (con);

For the UPDATE statement, the Analysis (8) procedure is as follows
Xmlutils util = new Xmlutils ();
Util.settransnode ("Updateuserinfo");
Util.settranskeyvalue ("userid", 123);
Util.settransvalue ("email", "aaa@aaa.com");
Util.update (con);

When you look at the operation of these SQL, do not feel very neat, but also very comfortable? This is a lot of benefits, programmers can not be too much to spell SQL statements, encapsulation of the operation can make all programmers can write the program is very neat, and have a unified style.

Keli thisishnh@163.com

The Word document is placed in this http://keli.nease.net/XMLDB.doc

Latest Jar pack placed in Http://keli.nease.net/iartbean/iartbean.jar

Sample placed inHttp://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", "Hnh3");
Util.insert (con);
*/

Util.settransnode ("Backemail", "Selectrespcode");
Util.settranskeyvalue ("StartTime", "2003-08-22 00:00:00.0");
Util.settranskeyvalue ("Endtime", "2003-08-22 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 pack placed in Http://keli.nease.net/iartbean/iartbean.jar

Sample placed inHttp://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", "Hnh3");
Util.insert (con);
*/

Util.settransnode ("Backemail", "Selectrespcode");
Util.settranskeyvalue ("StartTime", "2003-08-22 00:00:00.0");
Util.settranskeyvalue ("Endtime", "2003-08-22 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