本文主要是介绍QTP校验数据库中数据后台项目，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
数据校验功能后台主要包括两个类：QTPCommonServlet.java和QTPCommonDao.java
其中QTPCommonServlet.java为：
package com.runqianapp.qtp;import java.io.PrintWriter;import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;import com.runqianapp.qtp.QTPCommonDao;
import com.geezn.exception.GeeznException;
import com.runqianapp.common.log.GEZLoggerManager;
import com.runqianapp.common.servlet.AbstractGezServlet;
/*** QTP数据检查Servlet* @author RAQ**/
public class QTPCommonServlet extends AbstractGezServlet {/**获取表中所有数据*/private static final String GET_ALL_DATA = "getAllData";/**检查删除操作结果*/private static final String CHECK_DELETE_RESULT = "checkDeleteResult";/*** 重写service()方法*/public void service(HttpServletRequest request, HttpServletResponse response){response.setContentType("text/html");PrintWriter out = null;try {request.setCharacterEncoding("UTF-8");response.setCharacterEncoding("UTF-8"); out = response.getWriter();} catch (Exception e) {GeeznException ge;if(e instanceof GeeznException){ge = (GeeznException) e;}else{ge = new GeeznException(QTPCommonDao.MODULE_QTP, GEZLoggerManager.LOGLEVEL_ERROR,"设置字符格式、获取输出对象时，发生错误", e, "{}");}out.print(ge.getErrorMessageJson());} String action = request.getParameter("action");if(GET_ALL_DATA.equals(action)){String sql = request.getParameter("sql");String sqlDateNumber = request.getParameter("sqlDateNumber");String allDatas = "";try {allDatas = QTPCommonDao.getAllDatas(sql,sqlDateNumber);} catch (GeeznException e) {GeeznException ge;if(e instanceof GeeznException){ge = (GeeznException) e;}else{ge = new GeeznException(QTPCommonDao.MODULE_QTP, GEZLoggerManager.LOGLEVEL_ERROR,"得到表中数据时，发生错误", e, "{}");}out.print(ge.getErrorMessageJson());}out.print(allDatas);}else if(CHECK_DELETE_RESULT.equals(action)){String sql = request.getParameter("sql");boolean deleteResult = false;try {deleteResult = QTPCommonDao.checkDeleteResult(sql);} catch (GeeznException e) {GeeznException ge;if(e instanceof GeeznException){ge = (GeeznException) e;}else{ge = new GeeznException(QTPCommonDao.MODULE_QTP, GEZLoggerManager.LOGLEVEL_ERROR,"检查删除动作结果时，发生错误", e, "{}");}out.print(ge.getErrorMessageJson());}out.print(deleteResult);} }
}QTPCommonDao.java为：
package com.runqianapp.qtp;import java.sql.*;
import com.geezn.exception.GeeznException;
import com.runqian.mis2.util.DBAccess;
import com.runqianapp.common.log.GEZLoggerManager;
/*** QTP通用数据库操作类* @author RAQ**/
public class QTPCommonDao {/*** 用于存储表字段名称，各字段名称之间用“;”分隔*/public static StringBuffer columns = new StringBuffer();/*** 用于存储当前操作表名*/public static String tableName = "";/*** 用于日志输出，模块标识*/public static final String MODULE_QTP = "qtpModule"; /**** 获取表字段名称及所有数据信息* @param sql 用户输入的查询sql语句* @param sqlDateNumber 查询数据条数(保留功能，目前暂不处理)* @return 表字段名称及所有链接信息 如："short_link;...;link_name||/link1,...，百度@_@{};/link2,...,谷歌@_@{}"* 表字段名称与表数据之间用"||"分隔，各表字段名称之间与各表数据之间用";"分隔，每条表数据内部用"#"和"@_@"分隔* 其中"#"用来分隔表字段数据，"@_@"用来分隔表字段数据与json字符串* @throws GeeznException */public static synchronized String getAllDatas(String sql, String sqlDateNumber)throws GeeznException {getTableName(sql); //解析出表名PreparedStatement pstmt = null;DBAccess dba = null;ResultSet rs = null;StringBuffer allDatas = new StringBuffer(); //方法返回的字符串try {dba = new DBAccess();rs = dba.executeQuery(sql);ResultSetMetaData rsmd = rs.getMetaData();int columnCount = rsmd.getColumnCount();/** 每次请求处理之前，先对columns进行一次清空 */ columns = columns.delete(0, columns.length()); /** 在返回的字符串里添加表字段信息 */for (int i = 1; i <= columnCount; i++) {allDatas.append(rsmd.getColumnName(i)).append(";");columns.append(rsmd.getColumnName(i)).append(";");}allDatas.deleteCharAt(allDatas.length() - 1);columns.deleteCharAt(columns.length() - 1);String[] column = columns.toString().split(";");allDatas.append("||");/** 在返回的字符串里添加表数据信息 */while (rs.next()) {for (int i = 0; i < column.length; i++) {allDatas.append(rs.getString(column[i])).append("#");}allDatas.deleteCharAt(allDatas.length() - 1);allDatas.append("@_@"); //以"@_@"符号分隔前台的数据字段信息和整条数据信息 /**生成每条数据的json字符串*/allDatas.append("{");for (int i = 0; i < column.length; i++) {allDatas.append("\"").append(column[i]).append("\"").append(":").append("\"").append(rs.getString(column[i])).append("\"").append(",");}allDatas.deleteCharAt(allDatas.length() - 1);allDatas.append("}"); allDatas.append(";"); }allDatas.deleteCharAt(allDatas.length() - 1); } catch(Exception e){throw new GeeznException(QTPCommonDao.MODULE_QTP, GEZLoggerManager.LOGLEVEL_ERROR,"根据传入的sql获取数据时，发生错误", e,"{}");} finally {close(dba, pstmt, rs);}return allDatas.toString();}/*** 从用户输入的sql语句中解析出表名并存入变量tableName中* @param sql 用户输入的sql语句*/public static void getTableName(String sql){if (sql.contains("where") || sql.contains("WHERE")) {int start = 0;int end = 0;if (sql.contains("from")) {start = sql.indexOf("from");} else if (sql.contains("FROM")) {start = sql.indexOf("FROM");}if (sql.contains("where")) {end = sql.indexOf("where");} else if (sql.contains("WHERE")) {end = sql.indexOf("WHERE");}if (start == 0 || end == 0) {System.out.println("解析表名失败");} else {tableName = sql.substring(start + 5, end);}} else {int start = 0;if (sql.contains("from")) {start = sql.indexOf("from");} else if (sql.contains("FROM")) {start = sql.indexOf("FROM");}if (start == 0) {System.out.println("解析表名失败");} else {tableName = sql.substring(start + 5, sql.length());}}}/*** 判断某条数据是否已被成功删除方法* @param sql 查询删除数据的sql语句* @return 返回某条超链接是否已被成功删除 true：已被删除 false：没有被删除* @throws GeeznException*/ public static synchronized Boolean checkDeleteResult(String sql)throws GeeznException{boolean flag = true; //方法返回的字符串DBAccess dba = null;ResultSet rs = null;try{dba = new DBAccess();rs = dba.executeQuery(sql);while(rs.next()){flag = false;}}catch(Exception e){throw new GeeznException(QTPCommonDao.MODULE_QTP, GEZLoggerManager.LOGLEVEL_ERROR,"判断某条数据是否已被成功删除时，发生错误", e,"{}");}finally{close(dba, null, rs);}return flag;}/**关闭所有连接* @throws GeeznException */private static void close(DBAccess dba,Statement ps,ResultSet rs) throws GeeznException{try{if(rs!=null){rs.close();}if(ps!=null){ps.close();}if(dba!=null){dba.close();}}catch(Exception e){throw new GeeznException(QTPCommonDao.MODULE_QTP, GEZLoggerManager.LOGLEVEL_ERROR,"关闭所有连接时，发生错误", e,"{}"); } }
}
这篇关于QTP校验数据库中数据后台项目的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
