背景说明
对于Mysql，有灵活的sql语句直接执行，且sql语句对于程序来说即一个字符串，很方便扩展或变换，即使是有变量，也可以通过字符串操作直接实现替换，最重要的是sql结构比较简单易懂，而且天生适合关联查询。而MongoDB操作起来就不那么方便，没有sql，必须同时熟悉javascript语言语法和mongo命令、参数，如果不熟悉的话，则要在代码里写死查询框架，不方便修改和扩展。这里通过java实现了一个类似sql的语言框架，采取json格式，以方便通过配置的方式实现mongo查询。
代码实现
查询主框架
private JSON doGet(MongoTemplate mongoTemplate, JSONObject nosql, Map<String, String> parameterMap) throws Exception {
if (isSimpleType(nosql.getString("type"))) return doGetSimple(mongoTemplate, nosql, parameterMap);
else return doGetComplex(mongoTemplate, nosql, parameterMap);
}
private Boolean isSimpleType(String type) {
return StringUtils.isEmpty(type) || type.equals("simple");
}
简单查询框架
private JSON doGetSimple(MongoTemplate mongoTemplate, JSONObject nosql, Map<String, String> parameterMap) throws Exception {
String condition = StringUtility.rebuildQuery(nosql.getString("condition"), parameterMap);
log.debug("condition: " + condition);
Criteria criteria = parseCondition(parseCondition(condition));
Query query = (criteria == null) ? new Query() : new Query(criteria);
if (nosql.containsKey("sorts")) {
JSONArray sorts = nosql.getJSONArray("sorts");
if (sorts.size() > 0) {
List<Sort.Order> orders = new ArrayList<Sort.Order>();
for (Object sort : sorts) {
Direction direction = ((JSONObject) sort).getString("direction").equals("asc") ? Direction.ASC : Direction.DESC;
orders.add(new Sort.Order(direction, ((JSONObject) sort).getString("name")));
}
query = query.with(Sort.by(orders));
}
}
if (nosql.containsKey("skip")) query = query.skip(nosql.getLong("skip"));
if (nosql.containsKey("limit")) query = query.limit(nosql.getInteger("limit"));
List<JSONObject> results = mongoTemplate.find(query, JSONObject.class, nosql.getString("collection_name"));
List<Object> collection = doSubGet(mongoTemplate, nosql, parameterMap, results);
return postGet(nosql, collection);
}
解析字符串条件
private JSONObject parseCondition(String condition) throws Exception {
condition = condition.trim();
if (condition.startsWith("{")) return parseConditionJoint(condition);
else return parseConditionOne(condition);
}
解析连接词
private JSONObject parseConditionJoint(String condition) throws Exception {
JSONObject result = null;
int closeIndex = -1, count = 0;
for (int i = 0; i < condition.length(); i++) {
if (condition.charAt(i) == '{') {
count++;
} else if (condition.charAt(i) == '}') {
if (count < 1) {
throw new Exception("Error condition expression! Near: " + condition.substring(i));
} else if (count == 1) {
count--;
closeIndex = i;
result = parseCondition(condition.substring(1, closeIndex));
condition = condition.substring(closeIndex + 1).trim();
if (condition.isEmpty()) {
break;
} else if (condition.startsWith("||") || condition.startsWith("&&")) {
result = new JSONObject().fluentPut("left", result.clone()).fluentPut("symbol", condition.substring(0, 2)).fluentPut("right",
parseCondition(condition.substring(2)));
break;
} else {
throw new Exception("Error condition expression! Near: " + condition.substring(i));
}
} else {
count--;
}
}
}
if (count > 0) throw new Exception("Error condition expression! {} mismatching！");
return result;
}
解析单个查询条件表达式
private JSONObject parseConditionOne(String condition) throws Exception {
JSONObject result = new JSONObject();
if (StringUtils.isEmpty(condition)) return result;
String symbol = null;
if (checkNotOpCondition(condition, " in ")) {
symbol = "in";
result.fluentPut("right", JSON.parseArray(condition.substring(condition.indexOf(" in ") + 4).trim(), Object.class));
} else if (checkNotOpCondition(condition, " nin ")) {
symbol = "nin";
result.fluentPut("right", JSON.parseArray(condition.substring(condition.indexOf(" nin ") + 5).trim(), Object.class));
} else if (checkNotOpCondition(condition, " regex ")) {
symbol = "regex";
result.fluentPut("right", JSON.parseObject(condition.substring(condition.indexOf(" regex ") + 7).trim(), String.class));
}
if (symbol != null) result.fluentPut("left", condition.substring(0, condition.indexOf(symbol)).trim()).fluentPut("symbol", symbol);
else result.fluentPut("exp", expCondition(condition)).fluentPut("symbol", "exp");
return result;
}
判断是否为非运算（exp）条件
private Boolean checkNotOpCondition(String condition, String symbol) {
if (!(condition.contains(symbol))) return false;
Integer leftEnd = condition.indexOf(symbol);
if (!(StringUtility.isJavaIdentifier(condition.substring(0, leftEnd).trim()))) return false;
return true;
}
处理运算条件
private String expCondition(String condition) {
if (StringUtils.isEmpty(condition)) return "";
int i, j;
for (i = 0; i < condition.length(); i++) {
if (condition.charAt(i) == '\"' || condition.charAt(i) == '\'') {
Character character = condition.charAt(i);
for (j = i + 1; j < condition.length() && (condition.charAt(j) != character || condition.charAt(j - 1) == '\\'); j++);
i = j + 1;
} else if (Character.isJavaIdentifierStart(condition.charAt(i))) {
j = condition.indexOf(' ', i);
j = j > 0 ? j : condition.length();
if (condition.substring(i, j).matches("null|false|true")) {
i = j;
} else {
condition = condition.substring(0, i) + "this." + condition.substring(i);
i = j + 5;
}
}
}
return condition;
}
解析JSON条件
private Criteria parseCondition(JSONObject condition) {
log.debug("condition: " + condition);
if (condition == null || condition.getString("symbol") == null) return null;
switch (condition.getString("symbol")) {
case "||":
return parseCondition(condition.getJSONObject("left")).orOperator(parseCondition(condition.getJSONObject("right")));
case "&&":
return parseCondition(condition.getJSONObject("left")).andOperator(parseCondition(condition.getJSONObject("right")));
case "in":
return new Criteria(condition.getString("left")).in(condition.get("right"));
case "nin":
return new Criteria(condition.getString("left")).nin(condition.get("right"));
case "regex":
return new Criteria(condition.getString("left")).regex(condition.getString("right"));
case "exp":
return new Criteria() {
@Override
public Document getCriteriaObject() {
Document document = new Document();
document.put("$where", condition.getString("exp"));
return document;
}
};
default:
return null;
}
}
复杂查询框架
private JSON doGetComplex(MongoTemplate mongoTemplate, JSONObject nosql, Map<String, String> parameterMap) throws Exception {
String pipeline = StringUtility.rebuildQuery(nosql.getString("pipeline"), parameterMap);
String command = "{\"aggregate\":\"" + nosql.getString("collection_name") + "\",\"pipeline\":" + pipeline + ",\"cursor\":{}}";
List<Document> documents = ((Document) (mongoTemplate.executeCommand(command).get("cursor"))).getList("firstBatch", Document.class);
List<JSONObject> results = new ArrayList<JSONObject>();
for (Document document : documents) results.add(new JSONObject(document));
List<Object> collection = doSubGet(mongoTemplate, nosql, parameterMap, results);
return postGet(nosql, collection);
}
子查询框架
private List<Object> doSubGet(MongoTemplate mongoTemplate, JSONObject nosql, Map<String, String> parameterMap, List<JSONObject> results)
throws Exception {
List<Object> collection = new ArrayList<Object>();
for (JSONObject result : results) {
Boolean checkSub = true;
for (Object subMap : nosql.getJSONArray("map")) {
JSONObject subNosql = (JSONObject) (((JSONObject) subMap).clone());
if (isSimpleType(subNosql.getString("type")))
subNosql.put("condition", StringUtility.rebuildQuery(subNosql.getString("condition"), result, "parent__"));
else subNosql.put("pipeline", StringUtility.rebuildQuery(subNosql.getString("pipeline"), result, "parent__"));
JSON json = doGet(mongoTemplate, subNosql, parameterMap);
if (json == null && !(subNosql.getBooleanValue("nullable"))) {
checkSub = false;
break;
} else {
if (subNosql.getBooleanValue("join")) {
result.putAll((JSONObject) json);
} else {
String subCollectionName = subNosql.getString("collection_name");
subCollectionName = subNosql.getBooleanValue("single") ? subCollectionName : subCollectionName + "_list";
if (subNosql.containsKey("map_name")) subCollectionName = subNosql.getString("map_name");
result.put(subCollectionName, json);
}
}
}
if (checkSub) collection.add(result);
}
return collection;
}
查询后处理
private JSON postGet(JSONObject nosql, List<Object> collection) {
for (Object object : collection) ((JSONObject) object).remove("_id");
if (nosql.getBooleanValue("single")) return collection.isEmpty() ? null : (JSONObject) collection.get(0);
else return new JSONArray(collection);
}
配置示例
{
"connection_string":"mongodb://192.168.17.53:27017",
"database_name":"test",
"nosql":{
"collection_name":"computer",
"condition":"{id > ${id}}",
"skip":0,
"limit":2,
"single":false,
"nullable":true,
"sorts":[
{
"direction":"asc",
"name":"price"
}
],
"map":[
{
"collection_name":"screen",
"condition":"{id == ${parent__screen_id}}",
"map":[
{
"collection_name":"gpu",
"condition":"{id == ${parent__gpu_id}}",
"map":[ ],
"single":true,
"nullable":true
}
],
"single":true,
"nullable":true
},
{
"collection_name":"mouse",
"condition":"{id == ${parent__mouse_id}}",
"map":[ ],
"single":true,
"nullable":true
},
{
"collection_name":"os",
"condition":"{computer_id == ${parent__id}}",
"map":[
{
"collection_name":"company",
"condition":"{id == ${parent__company_id}}",
"map":[ ],
"single":true,
"nullable":true
}
],
"single":false,
"nullable":true
},
{
"type":"complex",
"collection_name":"user",
"map":[ ],
"pipeline":"[{"$match":{"age":{"$gte":119}}},{"$limit":3}]",
"single":false,
"nullable":true
}
]
}
}
结果示例
{
"code": 200,
"data": [
{
"mouse": {
"createTime": "2020-05-13T06:54:38.946+0000",
"name": "mouse4",
"weight": 34.0,
"id": 4.0
},
"createTime": "2020-05-13T06:41:45.970+0000",
"price": 0.0,
"os_list": [
{
"computer_id": 7.0,
"company_id": 1.0,
"createTime": "2020-05-13T07:55:52.298+0000",
"name": "os3",
"company": {
"createTime": "2020-05-13T07:59:02.648+0000",
"name": "company1",
"id": 1.0
},
"id": 3.0
}
],
"name": "computer7",
"screen": {
"createTime": "2020-05-13T06:46:11.945+0000",
"name": "screen0",
"width": 939.0,
"gpu_id": 1.0,
"id": 0.0,
"gpu": {
"performance": 87.0,
"createTime": "2020-05-13T06:51:25.494+0000",
"name": "gpu1",
"id": 1.0
},
"height": 774.0
},
"screen_id": 0.0,
"id": 7.0,
"mouse_id": 4.0,
"user_list": [
{
"id": 1.0,
"name": "user1",
"age": 119.0,
"createTime": "2020-05-08T08:57:18.502+0000"
},
{
"id": 145.0,
"name": "user145",
"age": 119.0,
"createTime": "2020-05-08T08:57:18.647+0000"
},
{
"id": 172.0,
"name": "user172",
"age": 119.0,
"createTime": "2020-05-08T08:57:18.683+0000"
}
]
},
{
"mouse": {
"createTime": "2020-05-13T06:54:38.832+0000",
"name": "mouse1",
"weight": 37.0,
"id": 1.0
},
"createTime": "2020-05-13T06:41:45.972+0000",
"price": 2.0,
"os_list": [
{
"computer_id": 8.0,
"company_id": 2.0,
"createTime": "2020-05-13T07:55:52.305+0000",
"name": "os8",
"company": {
"createTime": "2020-05-13T07:59:02.649+0000",
"name": "company2",
"id": 2.0
},
"id": 8.0
}
],
"name": "computer8",
"screen": {
"createTime": "2020-05-13T06:46:11.945+0000",
"name": "screen0",
"width": 939.0,
"gpu_id": 1.0,
"id": 0.0,
"gpu": {
"performance": 87.0,
"createTime": "2020-05-13T06:51:25.494+0000",
"name": "gpu1",
"id": 1.0
},
"height": 774.0
},
"screen_id": 0.0,
"id": 8.0,
"mouse_id": 1.0,
"user_list": [
{
"id": 1.0,
"name": "user1",
"age": 119.0,
"createTime": "2020-05-08T08:57:18.502+0000"
},
{
"id": 145.0,
"name": "user145",
"age": 119.0,
"createTime": "2020-05-08T08:57:18.647+0000"
},
{
"id": 172.0,
"name": "user172",
"age": 119.0,
"createTime": "2020-05-08T08:57:18.683+0000"
}
]
}
]
}
178
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
