Java使用mongodb原生增删改查语句
Java使用mongodb原生增删改查语句
2018-03-16
自动化测试时,需校验数据库数据,为了快速自动化,在代码中用原生增删改查语句操作mongodb
结构
代码
0 pom.xml
<dependencies> <dependency> <groupId>org.mongodb</groupId> <artifactId>mongo-java-driver</artifactId> <version>3.2.2</version> </dependency> <dependency> <groupId>com.alibaba</groupId> <artifactId>fastjson</artifactId> <version>1.2.31</version> </dependency> </dependencies>
1 客户端操作Client.java
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
package com.mongodb.util; import com.mongodb.util.DBConnection.DriverName; public class Client { public static void main(String[] args) { DBConnection conn = new DBConnection(); conn.setDbConnectionName("mongoDb1"); conn.setSource("****"); conn.setDbName("****"); conn.setDriverName(DriverName.mongodb); conn.setServerHost("****"); conn.setServerPort("27017"); conn.setUserName("****"); conn.setPassword("****"); String querySqlWithFields = "db.getCollection('laAppInterface').find({$or:[{'appCode':'100005'},{'appCode':'10005'}]},{'appCode':1,'interfaceId':1})"; String querySqlWithoutFields = "db.getCollection('laAppInterface').find({$or:[{'appCode':'100005'},{'appCode':'10005'}]})"; Crud.Connect(conn); //注意:客户端工具Robomogon中,保存整数1,要用NumberInt(1),否则会变成1.0 String create = "db.getCollection('laAppInterface').save({'appCode':'10005','interfaceId':'TEST-API-0000068','invokeType':1,'callbackAddress':'',})"; System.out.println("Create: "+Crud.OperateDB(create,false)); System.out.println(Crud.OperateDB(querySqlWithFields, false)); System.out.println(Crud.OperateDB(querySqlWithFields, true)); System.out.println(Crud.OperateDB(querySqlWithoutFields, false)); System.out.println(Crud.OperateDB(querySqlWithoutFields, true)); String update="db.getCollection('laAppInterface').update({'interfaceId':'TEST-API-0000068'},{$set:{'appCode':'100005'}});"; System.out.println("Update: "+Crud.OperateDB(update,false)); System.out.println(Crud.OperateDB(querySqlWithFields, false)); String delete="db.getCollection('laAppInterface').remove({$or:[{'appCode':'100005'},{'appCode':'10005'}]});"; System.out.println("Delete: "+Crud.OperateDB(delete,false)); System.out.println(Crud.OperateDB(querySqlWithFields, false)); //比较运算 String queryGt="db.getCollection('laAppInterface').find({'invokeType':{$gt:1}},{'appCode':1,'invokeType':1})"; System.out.println("queryGt: "+Crud.OperateDB(queryGt, false)); //暂不支持模糊查询 {'appCode':/0005/} } }
执行结果:
Create: 0 [["10005","TEST-API-0000068"]] [{"appCode":"10005","interfaceId":"TEST-API-0000068"}] [[{"counter":7184126,"date":1521186717000,"machineIdentifier":7002056,"processIdentifier":13740,"time":1521186717000,"timeSecond":1521186717,"timestamp":1521186717},"10005","TEST-API-0000068",1,""]] [{"_id":{"counter":7184126,"date":1521186717000,"machineIdentifier":7002056,"processIdentifier":13740,"time":1521186717000,"timeSecond":1521186717,"timestamp":1521186717},"appCode":"10005","interfaceId":"TEST-API-0000068","invokeType":1,"callbackAddress":""}] Update: 1 [["100005","TEST-API-0000068"]] Delete: 1 [] queryGt: [["1002",4],["1002",4],["1002",4],["1002",4],["1002",4],["1004",4],["1002",4],["1002",4],["1002",4],["1002",4],["1001",4],["1001",4],["1001",4],["1001",4],["1001",4]]
2 mongodb增删改查操作Crud.java
只暴露两个方法
public static void Connect(DBConnection dbCon); public static String OperateDB(String sql, Boolean hasColumnName);
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
package com.mongodb.util; import java.util.ArrayList; import java.util.Iterator; import java.util.List; import com.alibaba.fastjson.JSONArray; import com.alibaba.fastjson.JSONObject; import com.mongodb.BasicDBObject; import com.mongodb.DB; import com.mongodb.DBCursor; import com.mongodb.DBObject; import com.mongodb.MongoClient; import com.mongodb.MongoCredential; import com.mongodb.ServerAddress; import com.mongodb.WriteResult; import com.mongodb.util.DBConnection.DriverName; public class Crud { static DB mongoConn = null; public static void Connect(DBConnection dbCon) { if (dbCon.getDriverName() == DriverName.mongodb) { ServerAddress serverAddress = new ServerAddress(dbCon.getServerHost(), Integer.valueOf(dbCon.getServerPort())); List<ServerAddress> addrs = new ArrayList<>(); addrs.add(serverAddress); MongoCredential credential = MongoCredential.createScramSha1Credential(dbCon.getUserName(), dbCon.getSource(), dbCon.getPassword().toCharArray()); List<MongoCredential> credentials = new ArrayList<>(); credentials.add(credential); // 通过连接认证获取MongoDB连接 MongoClient mongoClient = new MongoClient(addrs, credentials); @SuppressWarnings("deprecation") DB con = mongoClient.getDB(dbCon.getDbName()); mongoConn = con; } } public static String OperateDB(String sql, Boolean hasColumnName) { String result = ""; if (sql.toUpperCase().contains(").FIND(")) { result = Query(sql, hasColumnName); } else if(sql.toUpperCase().contains(").REMOVE(")) { result=String.valueOf(Delete(sql)); } else if(sql.toUpperCase().contains(").UPDATE(")) { result=String.valueOf(Update(sql)); } else { result= String.valueOf(Create(sql)); } return result; } private static String Query(String sql, Boolean hasColumnName) { String tableName = RegExpUtil.getTableName(sql); String query = RegExpUtil.getQuery(sql); List<String> columnNames = getColumnNames(query); BasicDBObject queryObject = (BasicDBObject) JSON.parse(query); DBCursor cursor = mongoConn.getCollection(tableName).find(queryObject); JSONArray lists = new JSONArray(); while (cursor.hasNext()) { JSONObject jsonObj = new JSONObject(); JSONArray subLists = new JSONArray(); DBObject o = cursor.next(); if (!columnNames.isEmpty()) { if(!hasColumnName) { for (String columnName : columnNames) subLists.add(o.get(columnName)); lists.add(subLists); } else { for (String columnName : columnNames) jsonObj.put(columnName, o.get(columnName)); lists.add(jsonObj); } } else { if(!hasColumnName) { Iterator<String> it = o.keySet().iterator(); while(it.hasNext()) subLists.add(o.get(it.next())); lists.add(subLists); } else { lists.add(o); } } } return FastJsonUtil.ObjectToString(lists); } private static int Create(String sql) { String tableName = RegExpUtil.getTableName(sql); String create = RegExpUtil.getCreate(sql); BasicDBObject createObject = (BasicDBObject) JSON.parse(create); WriteResult writeResult = mongoConn.getCollection(tableName).save(createObject); return writeResult.getN(); } private static int Update(String sql) { String tableName = RegExpUtil.getTableName(sql); String update = RegExpUtil.getUpdate(sql); BasicDBObject updateQueryObject = (BasicDBObject) JSON.parse(update); String upset = getUpset(update); BasicDBObject upsetObject = (BasicDBObject) JSON.parse(upset); WriteResult updateResult = mongoConn.getCollection(tableName).update(updateQueryObject, upsetObject); return updateResult.getN(); } private static int Delete(String sql) { String tableName = RegExpUtil.getTableName(sql); String delete = RegExpUtil.getRemove(sql); BasicDBObject deleteObject = (BasicDBObject) JSON.parse(delete); WriteResult writeResult = mongoConn.getCollection(tableName).remove(deleteObject); return writeResult.getN(); } private static List<String> getColumnNames(String query) { String tmp = RegExpUtil.getObject(query,1); return RegExpUtil.getColumnNames(tmp); } private static String getUpset(String query) { String tmp = RegExpUtil.getObject(query, 1); return tmp; } }
3 实体类DBConnection.java
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
package com.mongodb.util; public class DBConnection { public enum DriverName { oracle, db2, sqlserver, mysql, sybase, postgresql,mongodb } private String dbConnectionName; private DriverName driverName; private String serverHost; private String serverPort; private String source; private String dbName; private String userName; private String password; public String getDbConnectionName() { return dbConnectionName; } public void setDbConnectionName(String dbConnectionName) { this.dbConnectionName = dbConnectionName; } public DriverName getDriverName() { return driverName; } public void setDriverName(DriverName driverName) { this.driverName = driverName; } public String getServerHost() { return serverHost; } public void setServerHost(String serverHost) { this.serverHost = serverHost; } public String getServerPort() { return serverPort; } public void setServerPort(String serverPort) { this.serverPort = serverPort; } public String getDbName() { return dbName; } public void setDbName(String dbName) { this.dbName = dbName; } public String getUserName() { return userName; } public void setUserName(String userName) { this.userName = userName; } public String getPassword() { return password; } public void setPassword(String password) { this.password = password; } public String getSource() { return source; } public void setSource(String source) { this.source = source; } }
4 RegExpUtil.java
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
package com.mongodb.util; import java.util.ArrayList; import java.util.List; import java.util.Stack; import java.util.regex.Matcher; import java.util.regex.Pattern; public class RegExpUtil { static String tableRegExp = "getCollection\\('(\\w+?)'\\)"; static String queryRegExp = "\\.find\\((.*?)\\)"; static String columnNameRegExp = "'(\\w+?)':"; static String createRegExp = "\\.save\\((.*?)\\)"; static String removeRegExp = "\\.remove\\((.*?)\\)"; static String updateRegExp = "\\.update\\((.*?)\\)"; static public String getObject(String str, int index) { int len = str.length(); char[] chars = str.toCharArray(); Stack<Character> stack = new Stack<Character>(); int begin = 0, end = 0, i = 0, curIndex = 0; while (i < len && curIndex <= index) { // '{'入栈 while (i < len&&stack.empty()) { if (chars[i] == '{') { stack.push(chars[i]); begin = i; } ++i; } if (stack.empty()) { return ""; } // 第curIndex个object while (!stack.empty() && i < len) { if (chars[i] == '{') { stack.push(chars[i]); } if (chars[i] == '}') { if (!stack.empty() && stack.peek() == '{') { stack.pop(); } } ++i; } if (stack.empty()) { end = i - 1; if (curIndex == index) return str.substring(begin, end+1); } else { return ""; } ++curIndex; } return ""; } public static List<String> getColumnNames(String sql) { return GetWithRegExps(sql, columnNameRegExp); } public static String getTableName(String sql) { return GetWithRegExp(sql, tableRegExp); } public static String getQuery(String sql) { return GetWithRegExp(sql, queryRegExp); } public static String getCreate(String sql) { return GetWithRegExp(sql, createRegExp); } public static String getUpdate(String sql) { return GetWithRegExp(sql, updateRegExp); } public static String getRemove(String sql) { return GetWithRegExp(sql, removeRegExp); } private static String GetWithRegExp(String s, String regExp) { Pattern r = Pattern.compile(regExp); // 现在创建 matcher 对象 Matcher m = r.matcher(s); if (m.find()) { return m.group(1); } return ""; } private static List<String> GetWithRegExps(String s, String regExp) { List<String> ss = new ArrayList<String>(); Pattern r = Pattern.compile(regExp); // 现在创建 matcher 对象 Matcher m = r.matcher(s); while (m.find()) { ss.add(m.group(1)); } return ss; } }
5 FastJsonUtil.java
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
package com.mongodb.util; import com.alibaba.fastjson.JSON; import com.alibaba.fastjson.JSONPath; import com.alibaba.fastjson.parser.Feature; import com.alibaba.fastjson.serializer.SerializerFeature; public class FastJsonUtil { public static String ObjectToString(Object o) { String s = JSON.toJSONString(o); return TrimDoubleQuote(s); } private static String TrimDoubleQuote(String s) { if (s.startsWith("\"") && s.endsWith("\"")) { s = s.substring(1, s.length()); s = s.substring(0, s.length() - 1); } return s; } }
待完善
代码暂不支持:
- 正则表达式查询,如 db.getCollection('laAppInterface').find({'appCode':/^1.*?1$/}) 中正则表达式 /^1.*?1$/
- 不支持函数,如db.getCollection('laAppInterface').update({'interfaceId':'TEST-API-0000068'},{$set:{"invokeType":NumberInt(1)}}); 中函数NumberInt(1)