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.
Like what:
Fetching all the data from the user table, some people will write "select * from User", some will write "Select all from user", although there is no error in the operation, but when 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_useruserid Bigintusername VARCHAR (a) PASSWORD VARCHAR (a) createtime Datetbl_user_infouserid bigintemail VARCHAR ( MOBILE VARCHAR (13)
|
One: Analysis
A) Analysis of SELECT statements
The use of XML to encapsulate database operation statements becomes the first step in the project normalization operation. In this step, we will take a few examples to gradually achieve the purpose of encapsulation.
For example, the statement "SELECT USERNAME, PASSWORD from Tbl_user" can be written in a variety of ways when parsing XML files, and we now use the following expressions:
Analysis 1:
1 <dbtrans name= "Selectuser" table= "Tbl_user" method= "select" >2 <get>3 <property "name=" username " 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", the 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 nodes Reads data from a database. Child nodes Database fields for reading, where:
The property name is the name of the field, the property type is the type of the field, and the setting type is reflected in the following program.
For
SELECT USERNAME, PASSWORD from Tbl_user WHERE userid=123
|
Statement, which, based on an analysis of an appeal, can be depicted as:
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 you are using the like operation, we can describe the 3rd sentence as:
<property name= "username" type= "string" match= "like"/>
|
For
SELECT USERNAME, PASSWORD from Tbl_user order by USERNAME DESC
|
This statement, XML is analyzed as follows
Analysis 3
1. <dbtrans name= "Selectuser" table= "Tbl_user" method= "select" >2 <get>3 <property "name=" username "String"/>4 <property name= password type= "string"/>5 </get>6 <order name= "Respcode" match= "desc"/ >6 </dbtrans>
|
Such statement analysis can basically complete most of the ordinary database of the SELECT statement operation, but after all, there will be 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 ', while createtime in <key>, we can portray 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 can't be done, such as
SELECT COUNT (*) from Tbl_user
|
At this point the operation will appear when the XML statement can not be depicted, this time we can introduce the special this attribute, such as:
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
Insert statements are 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 set up a table in the database named: Tsys_primarykey, which includes three fields, as follows:
Tsys_primarykeykeyid biginttablename VARCHAR ($) PRIMARYKEY VARCHAR (30)
|
Where TableName saves the table name, PrimaryKey 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 1 to the existing primary key and then the insert operation. After the operation is complete we also need to update the Tsys_primarykey this table to ensure that the data is synchronized.
Now we're going to start parsing the INSERT statement
Insert into Tbl_user (USERID, USERNAME, PASSWORD) VALUES (+, ' test ', ' test ') insert into Tbl_user_info (USERID, EMAIL , MOBILE) VALUES (MB, ' test@test.com ', ' 1234567890 ')
|
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 <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" mobile "name=" string "type= 6 </set>7 </dbtrans>
|
C) parsing the DELETE statement
The most common types of DELETE statements can be divided into two categories, one being deleted by the key value and the other being all deleted, for which we divided the operation into two categories, delete and clear.
For the delete type, for example:
DELETE from Tbl_user_infowhere 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) Analysis of UPDATE statements
From the usual operation of update we can know that there will be two kinds of tags when using XML representations, including And Like what:
UPDATE tbl_user_infoset 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: Fetching 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, analyze 1
The procedures to be prepared are 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); Suppose the operation succeeds 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 Analysis 6) The procedure is as follows:
Xmlutils util = new Xmlutils (); Util.settransnode ("DeleteUser"); Util.settranskeyvalue ("userid"); Util.delete (Con );
|
For the UPDATE statement, the Analysis 8 program 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.
Import Com.iart.database.xmlcontrol.*;import com.microsoft.*; Import Java.sql.*;import java.util.*;p ublic 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 (ExceptionEx) {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. Sql server. SQLServerDriver "); String Strcon = "Jdbc:microsoft:sqlserver://localhost:1433; USER=NLC; PASSWORD=NLC;DATABASENAME=NLC "; Connection conn = drivermanager.getconnection (Strcon); Return conn; }}
|
