search
HomeDatabaseMysql Tutorial使用XML封装数据库操作语句的实现_MySQL

在项目开发的过程当中,项目组开发成员的编程风格差异和数据库操作语句SQL的灵活性给项目组带来了越来越多的操作和维护难度。
比如:从user表中取出所有数据,有的人会写成“select * from user”,有的人会写成“select all from user”,虽然在操作中不会有任何的错误,但在其他人读程序的过程时就会产生不好的感觉。
如果这种程序差异在项目中的数量级很多,那么在开发的过程当中程序就会出现各种各样的风格,在维护的过程中就会拼命的挠头并诅咒那些当初写程序的人。

为了整篇文章的举例,现在我们在数据库中建立如下表
TBL_USER
USERID BIGINT
USERNAME VARCHAR(20)
PASSWORD VARCHAR(20)
CREATETIME DATE

TBL_USER_INFO
USERID BIGINT
EMAIL VARCHAR(64)
MOBILE VARCHAR(13)

一、分析

A、分析select语句

于是使用XML来封装数据库操作语句成为项目规范化操作的第一步骤。在这个步骤中,我们将举几个例子来逐步实现封装的目的。
比如 “ SELECT USERNAME, PASSWORD FROM TBL_USER ” 这个语句,分析成XML文件时可以有各种各样的写法,我们现在使用如下的表达方式:
分析(1)
1
2
3
4
5

6

在第一行的句子中使用 dbtrans 为节点名称,
属性name为这个交易的名称,这里为“selectUser”,
属性table为索取表的名称,这里为“TBL_USER”,
属性method为操作数据库的方法,这里为“select”,
子节点意思为从数据库读取数据。
子节点为读取的数据库字段,其中:
属性name为字段的名字,
属性type 为字段的类型,这里设置类型在后面的程序中可以体现出来。

对于“SELECT USERNAME, PASSWORD FROM TBL_USER WHERE USERID=123”语句,我们根据上述的分析,则可将XML描绘为:
分析(2)
1.
2.
3
4

5
6
7
8

9

如果使用的是like操作,我们可以将第3句描述成为

对于“SELECT USERNAME, PASSWORD FROM TBL_USER ORDER BY USERNAME DESC”这个语句,XML如下分析:
分析(3)
1.
2
3
4
5

6
7

这样的语句分析基本上可以完成了绝大部分的普通数据库的select语句的操作,但是毕竟还是会有一些我们无法预料的数据库操作语句会出现,比如
“SELECT USERNAME, PASSWORD FROM TBL_USER WHERE CREATETIME >‘2003-7-16’ AND CREATETIME之中,这时我们可以将XML描绘成为:

”>

但即使使用了以上的变通方法,还是会有很多的特殊语句是无法完成的,比如 “ SELECT COUNT(*) FROM TBL_USER ”, 这时的操作会出现使用XML语句无法描绘的时候,这个时候我们就可以引入了special这个属性,例如:
分析(4)
1
2

这个属性的意思是将所有的特殊交易都特殊表现出来。

B、分析INSERT语句

INSERT 语句在关系型数据库中操作可以说是最麻烦的一条语句了,因为如果你需要在TBL_USER和TBL_USER_INFO表中建立一条对应的数据时,你需要知道插入数据库的主键的值,在JDBC3.0中可以使用Statement.RETURN_GENERATED_KEYS来获取,但是如果为了兼容性考虑,我们在操作过程之中决定采用另一种办法来实现。
我们在数据库中建立一个表,名为:TSYS_PRIMARYKEY,其中包括三个字段,如下:
TSYS_PRIMARYKEY
KEYID BIGINT
TABLENAME VARCHAR(64)
PRIMARYKEY VARCHAR(30)
其中TABLENAME保存表名,PRIMARYKEY保存主键的名称,KEYID保存主键的值,这样的做法目的是在insert语句操作前,先取到现在主键的值,并将该值加1,成为现有的主键,然后进行insert操作,操作完成之后我们还需要update一下TSYS_PRIMARYKEY这个表,确保数据的同步。
现在我们开始分析 INSERT语句了,
INSERT INTO TBL_USER ( USERID, USERNAME, PASSWORD ) VALUES ( 100, ‘test’, ‘test’ )
INSERT INTO TBL_USER_INFO ( USERID, EMAIL, MOBILE ) VALUES ( 100, ‘test@test.com’, ‘1234567890’ )

描绘为XML文件时我们可以描绘如下
分析5)
1.
2
3
4
5
6

7

以及
1
2
3
4
5
6

7

C、分析DELETE语句

Delete语句最常用的可以分为两种,一种是按照键值删除,一种是全部删除,为此我们将此操作划分为两种类型,delete和clear
对于delete类型,举例为:
DELETE FROM TBL_USER_INFO WHERE USERID=123
描述为:
分析(6)
1
2
3
4

5

对于clear类型,举例为:
DELETE FROM TBL_USER_INFO
描述为:
分析(7)
1
2

D、分析UPDATE语句

从update通常的操作我们可以知道使用XML描述时将会出现两种tag,包括,比如:
UPDATE TBL_USER_INFO SET EMAIL=’aaa@aaa.com’ WHERE USERID=123
描述称XML为:
分析8)
1.
2
3
4

5
6
7

8

二、程序设计

好的,在分析了XML文件之后需要我们进入到程序的设计上来了。从以上实现的分析我们可以清楚的看到要实现以上操作,我们必须要做到以下几步:
1 读取XML文件
2 定位相应的交易节点
3 拼SQL 语句
4 数据库操作
5 取数据
6 返回数据

其中针对第一步的读取文件,我们可以封装所有的XML parse语句以及前期的操作封装进入一个类之中,这里我们命名为 XMLLoadFile。

交易处理的过程包括2,5,6三个步骤,可以封装成XMLTransaction类中。当然返回数据这个操作可以单独抽出来作为一个相应的返回类,如果这样是为了在返回的数据报文做处理,比如可以返回XML,Vector或者Hashtable或Map等。这里暂定返回数据为Vector类型,所以将第6步封装进来。

拼装SQL语句,独立建立一个类(XMLCombine),当然也可以分为多个,比如SelectCombine,insertCombine等,这里我们进行统一封装。

数据库操作单独封装成一个类,XMLExecuteSQL。

以上所有的类统一了一个出口类,这里为XMLUtils。这个类提供的几个方法为外部数据操作的主要方法,比如select, insert, delete, update等,还有提供外部程序存取数据的几个方法,比如:setTransNode(设置交易节点),setTransValue(设置交易数据值), setTransKeyValue(设置交易键值数据值)

三、外部程序调用

对于select语句,分析(1)所需编写的程序如下
XMLUtils util = new XMLUtils();
util.setTransNode(“selectUser”);
Vector vRtn = util.select( con );

分析(2)为
XMLUtils util = new XMLUtils();
util.setTransNode(“selectUserByKey”);
util.setTransKeyValue(“userid”, 123 );
Vector vRtn = util.select( con );

对于insert语句,分析(5)程序如下
XMLUtils util = new XMLUtils();
util.setTransNode(“insertUser”);
util.setTransValue(“username”, “test” );
util.setTransValue(“password”, “test” );
Vector vRtn = util.insert( con ); //假设操作成功
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 );

对于 delete语句 分析( 6)程序如下
XMLUtils util = new XMLUtils();
util.setTransNode(“deleteUser”);
util.setTransKeyValue(“userid”, 100);
util.delete( con );

对于update语句,分析( 8)程序如下
XMLUtils util = new XMLUtils();
util.setTransNode(“updateUserInfo”);
util.setTransKeyValue(“userid”, 123);
util.setTransValue(“email”, “aaa@aaa.com”);
util.update( con );

大家在看这些SQL的操作时,是不是觉得很工整,也很舒服呢?这样做的好处很多,程序员可以不必太多的去拼写SQL 语句,封装的操作可以使所有程序员的程序都可以写的很工整,并有统一的风格。


Keli thisishnh@163.com


word文档放置于此 http://keli.nease.net/XMLDB.doc

最新jar包放置于 http://keli.nease.net/iartbean/iartbean.jar

Sample 放置于 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", "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 {
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 {
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

最新jar包放置于 http://keli.nease.net/iartbean/iartbean.jar

Sample 放置于 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", "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 {
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 {
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;
}
}

Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
How do you handle database upgrades in MySQL?How do you handle database upgrades in MySQL?Apr 30, 2025 am 12:28 AM

The steps for upgrading MySQL database include: 1. Backup the database, 2. Stop the current MySQL service, 3. Install the new version of MySQL, 4. Start the new version of MySQL service, 5. Recover the database. Compatibility issues are required during the upgrade process, and advanced tools such as PerconaToolkit can be used for testing and optimization.

What are the different backup strategies you can use for MySQL?What are the different backup strategies you can use for MySQL?Apr 30, 2025 am 12:28 AM

MySQL backup policies include logical backup, physical backup, incremental backup, replication-based backup, and cloud backup. 1. Logical backup uses mysqldump to export database structure and data, which is suitable for small databases and version migrations. 2. Physical backups are fast and comprehensive by copying data files, but require database consistency. 3. Incremental backup uses binary logging to record changes, which is suitable for large databases. 4. Replication-based backup reduces the impact on the production system by backing up from the server. 5. Cloud backups such as AmazonRDS provide automation solutions, but costs and control need to be considered. When selecting a policy, database size, downtime tolerance, recovery time, and recovery point goals should be considered.

What is MySQL clustering?What is MySQL clustering?Apr 30, 2025 am 12:28 AM

MySQLclusteringenhancesdatabaserobustnessandscalabilitybydistributingdataacrossmultiplenodes.ItusestheNDBenginefordatareplicationandfaulttolerance,ensuringhighavailability.Setupinvolvesconfiguringmanagement,data,andSQLnodes,withcarefulmonitoringandpe

How do you optimize database schema design for performance in MySQL?How do you optimize database schema design for performance in MySQL?Apr 30, 2025 am 12:27 AM

Optimizing database schema design in MySQL can improve performance through the following steps: 1. Index optimization: Create indexes on common query columns, balancing the overhead of query and inserting updates. 2. Table structure optimization: Reduce data redundancy through normalization or anti-normalization and improve access efficiency. 3. Data type selection: Use appropriate data types, such as INT instead of VARCHAR, to reduce storage space. 4. Partitioning and sub-table: For large data volumes, use partitioning and sub-table to disperse data to improve query and maintenance efficiency.

How can you optimize MySQL performance?How can you optimize MySQL performance?Apr 30, 2025 am 12:26 AM

TooptimizeMySQLperformance,followthesesteps:1)Implementproperindexingtospeedupqueries,2)UseEXPLAINtoanalyzeandoptimizequeryperformance,3)Adjustserverconfigurationsettingslikeinnodb_buffer_pool_sizeandmax_connections,4)Usepartitioningforlargetablestoi

How to use MySQL functions for data processing and calculationHow to use MySQL functions for data processing and calculationApr 29, 2025 pm 04:21 PM

MySQL functions can be used for data processing and calculation. 1. Basic usage includes string processing, date calculation and mathematical operations. 2. Advanced usage involves combining multiple functions to implement complex operations. 3. Performance optimization requires avoiding the use of functions in the WHERE clause and using GROUPBY and temporary tables.

An efficient way to batch insert data in MySQLAn efficient way to batch insert data in MySQLApr 29, 2025 pm 04:18 PM

Efficient methods for batch inserting data in MySQL include: 1. Using INSERTINTO...VALUES syntax, 2. Using LOADDATAINFILE command, 3. Using transaction processing, 4. Adjust batch size, 5. Disable indexing, 6. Using INSERTIGNORE or INSERT...ONDUPLICATEKEYUPDATE, these methods can significantly improve database operation efficiency.

Steps to add and delete fields to MySQL tablesSteps to add and delete fields to MySQL tablesApr 29, 2025 pm 04:15 PM

In MySQL, add fields using ALTERTABLEtable_nameADDCOLUMNnew_columnVARCHAR(255)AFTERexisting_column, delete fields using ALTERTABLEtable_nameDROPCOLUMNcolumn_to_drop. When adding fields, you need to specify a location to optimize query performance and data structure; before deleting fields, you need to confirm that the operation is irreversible; modifying table structure using online DDL, backup data, test environment, and low-load time periods is performance optimization and best practice.

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

ZendStudio 13.5.1 Mac

ZendStudio 13.5.1 Mac

Powerful PHP integrated development environment

MantisBT

MantisBT

Mantis is an easy-to-deploy web-based defect tracking tool designed to aid in product defect tracking. It requires PHP, MySQL and a web server. Check out our demo and hosting services.

SecLists

SecLists

SecLists is the ultimate security tester's companion. It is a collection of various types of lists that are frequently used during security assessments, all in one place. SecLists helps make security testing more efficient and productive by conveniently providing all the lists a security tester might need. List types include usernames, passwords, URLs, fuzzing payloads, sensitive data patterns, web shells, and more. The tester can simply pull this repository onto a new test machine and he will have access to every type of list he needs.

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

DVWA

DVWA

Damn Vulnerable Web App (DVWA) is a PHP/MySQL web application that is very vulnerable. Its main goals are to be an aid for security professionals to test their skills and tools in a legal environment, to help web developers better understand the process of securing web applications, and to help teachers/students teach/learn in a classroom environment Web application security. The goal of DVWA is to practice some of the most common web vulnerabilities through a simple and straightforward interface, with varying degrees of difficulty. Please note that this software