# MxTestSql **Repository Path**: mxspace/mx-test-sql ## Basic Information - **Project Name**: MxTestSql - **Description**: 自定义的数据库操作测试类,可建议替换正常的sql类进行调试 - **Primary Language**: Unknown - **License**: Not specified - **Default Branch**: master - **Homepage**: None - **GVP Project**: No ## Statistics - **Stars**: 0 - **Forks**: 0 - **Created**: 2023-04-07 - **Last Updated**: 2023-07-19 ## Categories & Tags **Categories**: Uncategorized **Tags**: SQL, 测试 ## README 大家好哇,我又来了,还是我梦辛工作室的灵,最近在调试 老项目的时候,老出现数据异常,查了好半天都查不到,本来想把数据库同步到本地后来调试,又觉得麻烦,就想着要不自己写一个测试类,然后自定义一些sql语句的匹配,返回定义好的数据这样来调试代码,所以我就花了一些时间来写了这个MxTestSql类,有帮助的小伙伴帮忙点个赞,先看下实现效果: ```java 执行:update user set name = '1' from user 执行结果:1 执行查询:select name,phone from user 查询结果: 小明,110 执行查询:select name,phone from user2 查询结果: 06d9706b92904dadb5022916edbdf667 , 183199 0ec005bb835c457a94ac232bc65bc2ca , 182911 Process finished with exit code 0 ``` ```java package com.mx; import com.mx.test.sql.Connection; import com.mx.test.sql.PreparedStatement; import com.mx.test.sql.ResultSet; public class Main { public static void main(String[] args) { Connection connection = new Connection(); //配置查询 connection.addQueryResult("select name,phone from user","小明,110"); //支持分析直接从面板 拷贝出来的数据 connection.parseQueryResult("select name,phone from user2", "| 06d9706b92904dadb5022916edbdf667 | 183199 \n" + "| 0ec005bb835c457a94ac232bc65bc2ca | 182911 \n" ); //配置更新结果 connection.putUpdateResult("update user set name = '1' from user",1); PreparedStatement ps = connection.prepareStatement("update user set name = ? from user"); ps.setString(1,"1"); int result = ps.executeUpdate(); ps = connection.prepareStatement("select name,phone from user"); ResultSet resultSet = ps.executeQuery(); while (resultSet.next()){ } ps = connection.prepareStatement("select name,phone from user2"); resultSet = ps.executeQuery(); while (resultSet.next()){ } } } ``` 是不是这样就很丝滑,在本地调试也简单了许多,不用导入各种数据,直接模拟线上情况,在本地跑就行了,替换也简单,导入的sql类更换下就可以了,下面是全部代码: Connection: ```java package com.mx.test.sql; import java.util.ArrayList; import java.util.HashMap; import java.util.List; public class Connection { private HashMap> queryDataResult = new HashMap<>(); private HashMap updateDataResult = new HashMap<>(); public void addQueryResult(String sql,String result){ if(queryDataResult.get(sql) == null){ List resultAdd = new ArrayList<>(); resultAdd.add(result); queryDataResult.put(sql,resultAdd); } else { queryDataResult.get(sql).add(result); } } public void parseQueryResult(String sql,String result){ String[] resultSplit = result.split("\n"); List resultAdd = new ArrayList<>(); for (String split:resultSplit){ String addResult = ""; if(split.startsWith("|")){ addResult = split.replaceFirst("\\|",""); } resultAdd.add(addResult.replaceAll("\\|",",")); } putQueryResult(sql,resultAdd); } public void putQueryResult(String sql,List resultAdd){ queryDataResult.put(sql,resultAdd); } public void putUpdateResult(String sql,int resultAdd){ updateDataResult.put(sql,resultAdd); } public PreparedStatement prepareStatement(String sql){ return new PreparedStatement(sql,this); } public Statement createStatement(){ return new Statement(this); } public ResultSet doSqlQuery(String sql) { System.out.println("执行查询:" + sql); List result = queryDataResult.get(sql); System.out.println("查询结果:"); if(result == null){ System.out.println("数据为空"); return new ResultSet(null); } result.forEach(item -> { System.out.println(item); }); return new ResultSet(result); } public int doSqlUpdate(String sql) { System.out.println("执行:" + sql); Integer result = updateDataResult.get(sql); System.out.println("执行结果:" + result); return result; } } ``` PreparedStatement: ```java package com.mx.test.sql; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; public class PreparedStatement { private Connection sqlConn; List result = null; Map datas = null; String curSql = ""; public PreparedStatement(String sql, Connection sqlConn){ curSql = sql; datas = new HashMap<>(); result = new ArrayList<>(); this.sqlConn = sqlConn; } public void setInt(int index,int value){ datas.put(index,value + ""); } public void setString(int index,String value){ datas.put(index, "'" + value + "'"); } public void setLong(int index,long value){ datas.put(index,value + ""); } public void setFloat(int index,float value){ datas.put(index,value + ""); } public void setDouble(int index,double value){ datas.put(index,value + ""); } public void addBatch(){ result.add(getCurSetSql()); } public void executeBatch(){ System.out.println("开始批量执行:"); result.forEach(item -> { this.sqlConn.doSqlUpdate(item); }); System.out.println("结束批量执行:"); } public int executeUpdate(){ return this.sqlConn.doSqlUpdate(getCurSetSql()); } public ResultSet executeQuery(){ return doSqlQuery(getCurSetSql()); } private String getCurSetSql(){ String[] concatStr = curSql.split("\\?"); int len = concatStr.length; StringBuffer resultBuffer = new StringBuffer(); for (int i = 0;i < len ;i++){ resultBuffer.append(concatStr[i]); resultBuffer.append(datas.getOrDefault(i + 1,"null")); } String result = resultBuffer.toString(); if(result.endsWith("null")){ return result.substring(0,result.length() - 4); } return resultBuffer.toString(); } public ResultSet doSqlQuery(String sql){ return this.sqlConn.doSqlQuery(sql); } } ``` ResultSet: ```java package com.mx.test.sql; import java.util.ArrayList; import java.util.List; public class ResultSet { List datas = null; int current = -1; public ResultSet(){ this.datas = new ArrayList<>(); current = -1; } public ResultSet(List datas){ this.datas = datas; current = -1; } public boolean doCheckNext(){ if(datas == null || datas.size() == 0){ return false; } return current < datas.size() - 1; } public boolean next(){ if (doCheckNext()){ current++; return true; } return false; } public String getString(int index){ String value = getValue(index).trim(); return value; } public int getInt(int index){ String value = getValue(index).trim(); if("null".equals(value)){ return 0; } return value != null?Integer.parseInt(value):0; } public long getLong(int index){ String value = getValue(index).trim(); if("null".equals(value)){ return 0; } return value != null?Long.parseLong(value):0; } public float getFloat(int index){ String value = getValue(index).trim(); if("null".equals(value)){ return 0; } return value != null?Float.parseFloat(value):0; } public double getDouble(int index){ String value = getValue(index).trim(); if("null".equals(value)){ return 0; } return value != null?Double.parseDouble(value):0; } public String getValue(int index){ index--; String currentStr = datas.get(current); if(currentStr == null){ return ""; } String[] currentStrDatas = currentStr.split(","); return index < currentStrDatas.length?currentStrDatas[index]:""; } } ``` Statement: ```java package com.mx.test.sql; public class Statement { private Connection sqlConn; public Statement(Connection sqlConn){ this.sqlConn = sqlConn; } public int result = 0; public void setResult(int result) { this.result = result; } public ResultSet executeQuery(String sql){ return doSqlQuery(sql); } public int executeUpdate(String sql){ return doSqlUpdate(sql); } public int doSqlUpdate(String sql){ return sqlConn.doSqlUpdate(sql); } public ResultSet doSqlQuery(String sql){ return this.sqlConn.doSqlQuery(sql); } } ```