JMeter interface test for data validation via SQL queries

When using JMeter to do query class interface test, we can verify the data by the results of the database query and the result of the actual interface.

The return value of the interface:
{
"Pageresult": {
"Summarydata": {
"Summonery": 10000,
"Sumfee": 900,
"Sumincome": 16000
},
"PageSize": 10,
"CurrentPage": 1,
"Data": [
{
"Id": 10,
"Fee": 7.65,
"Monery": 8.65,
"UserName": "Test",
"Time": "2017-12-12 09:00:00",
"Partneridentity": "Agent of Jiangxi Province",
"Partnertype": 1,
"State": 1,
"Income": 7.65
}
],
"TotalItems": 10
}
}
As above is the normal return data of our interface.

We use Oracle as a database, so we need to load the Oracle installation directory (C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\JDBC\LIB) The Ojdbc6.jar file is copied to the Lib\ext directory of the JMeter software.

Import Odbc6.jar into classpath in the test plan
Then add a JDBC connection configuration

Then write a Java project:

Import Com.alibaba.fastjson.*;import java.util.*;p ublic class jsondiff{/** * Sort JSON * @param jsonstr *  @return */@SuppressWarnings ({"Rawtypes", "Unchecked"}) public static list<treemap<string,object>>  Generatesortedtreemap (String jsonstr) {//finally accepts sorted data list<treemap<string,object>> dataList = new        Arraylist<treemap<string,object>> ();        Convert the data into an ordered Map list<map> List = new arraylist<map> ();        try {list = Json.parsearray (Jsonstr, Map.class);            }catch (Exception e) {System.out.println ("+++++++++ convert JSON array failed, attempt to convert to JSON object:" +e.getmessage ());                try {Map obj = Json.parseobject (jsonstr, Map.class);            List.add (obj);            }catch (Exception E1) {System.out.println ("+++++++++json object conversion failed:" +e.getmessage ()); }} for (Map map:list) {//Get JSON key set<string> keys =(set<string>) Map.keyset ();            list<string> Sortkeys = new arraylist<string> (keys);            Sorts the keys collections.sort (Sortkeys);            Use an ordered list to store treemap<string,object> tree = new treemap<string,object> ();            for (String Key:sortkeys) {tree.put (key, Map.get (key));        } datalist.add (tree);    } return dataList; }/** * Compare two JSON * @param json1 * @param json2 * @return */public static Boolean Comparejson (St        Ring json1,string json2) {list<treemap<string,object>> data1list = Generatesortedtreemap (Json1);        list<treemap<string,object>> data2list = Generatesortedtreemap (Json2);            for (treemap<string,object> map:data1list) {if (!data2list.contains (map)) {return false;    }} return true; }/* public static void Main (string[] args) {//json wordstring//string json1 = "[{\" size\ ": 6,\" data\ ": \" Hello linux\ ", \" data1\ ": {\" aa\ ": 1,\" id\ ": {\" userid\ ": 3333,\" name\ "        : \ "Hello world\"}}}},{\ "data\": \ "world\", \ "Size\": 7}] "; String Json2 = "[{\" data\ ": \" world\ ", \" Size\ ": 7}, {\" data1\ ": {\" aa\ ": 1,\" id\ ": {\" userid\ ": 3333,\" name\ ": \" Hello        World\ "}},\" data\ ": \" Hello linux\ ", \" Size\ ": 6}]"; String Json1 = "[{\" name\ ": \" Work\ ", \" passwd\ ": \" 123456\ ", \" classname\ ": [{\" name\ ": \" match\ ", \" score\ ": 78},{\"        Name\ ": [{\" name\ ": \" match\ ", \" score\ ": +}]}],\" Teacher\ ": [{\" name\ ": \" bob\ ", \" classname\ ": \" 99\ "}]}]"; String Json2 = "[{\" name\ ": \" Work\ ", \" passwd\ ": \" 123456\ ", \" classname\ ": [{\" score\ ": 78,\" name\ ": \" match\ "},{\"        Name\ ": [{\" name\ ": \" match\ ", \" score\ ": +}]}],\" Teacher\ ": [{\" name\ ": \" bob\ ", \" classname\ ": \" 99\ "}]}]";        String json1= "{\" size\ ": 6,\" data\ ": \" Hello linux\ "}";        String json2= "{\" data\ ": \" Hello linux\ ", \" Size\ ": 6}"; String json1 = "{\" name\ ": \" TEstuser\ ", \" passwd\ ": \" 123456\ ", \" classname\ ": [{\" name\ ": \" match\ ", \" score\ ": 78},{\" name\ ": [{\" name\ ": \" match\        ", \" score\ ":],\}]}" Teacher\ ": [{\" name\ ": \" bob\ ", \" classname\ ": \" 99\ "}]}"; String json2 = "{\" passwd\ ": \" 123456\ ", \" name\ ": \" Testuser\ ", \" classname\ ": [{\" name\ ": \" match\ ", \" score\ ": 78},{        \ "Name\": [{\ "name\": \ "match\", \ "score\":}]}],\ "Teacher\": [{\ "name\": \ "bob\", \ "classname\": \ "99\"}]} ";        Boolean result = Comparejson (Json1, Json2);    SYSTEM.OUT.PRINTLN (result); }*/}

Finally, a jar package, also added to the classpath just below

Create a new JDBC Postprocessor under the HTTP connection

And then create a new BeanShell postprocessor

 import Com.alibaba.fastjson.*;import Java.util.*;import jsondiff.*; String response_data =prev.getresponsedataasstring (); Get Web Response content Jsonobject obj=json.parseobject (response_data); String app_str = Obj.get ("Data"). ToString (). toLowerCase (); Log.info ("Response:" +APP_STR); List results=vars.getobject ("Sqlresult"); Gets the SQL query string sql_str=jsonobject.tojsonstring (results). toLowerCase (); The list is converted to JSON data log.info ("SQL query:" +SQL_STR); Boolean Result=jsondiff.comparejson (APPS_STR,SQL_STR) if (result) { Log.info ("Data consistent"); }else{//With the expected JSON string and the result of the query to the JSON string to compare//If not the case, the assertion of the database failed, it may be the problem of the program String result =prev.getresponsedataasstring (); The value that gets the actual result, which is used to store the Prev.setresponsecode ("506") in the return data; We have customized a code code that tells the result that the code is this prev.setsuccessful (false); Set the result to error prev.setresponsemessage ("Interface assertion passed, database checksum failed"); Set a prompt message, presumably to explain the cause of the//write results returned results, as well as the database check failed, our expected value of the database is, the actual value is, plus this is to conveniently locate the problem Prev.setresponsedata (result+ ", \ N Database checksum failed, \ n Expected value: "+str+", \ n actual value: "+apps_str"; } 

If the checksum does not pass, an incorrect value is returned automatically, otherwise the normal value is returned

JMeter interface test for data validation via SQL queries