metamodel-mongodb查询工具

使用metaModel查询mongdb的所有列及相关条件查询工具

package com.kdgcsoft.zw.service;

import com.alibaba.fastjson.JSONObject;
import com.kdgcsoft.zw.entity.ColumnInfo;
import com.kdgcsoft.zw.entity.MetaSource;
import com.kdgcsoft.zw.entity.ResponseResult;
import com.kdgcsoft.zw.entity.SourceCode;
import com.kdgcsoft.zw.util.DBHelper;
import com.kdgcsoft.zw.util.QueryCondition;
import com.kdgcsoft.zw.util.StringUtil;
import com.mongodb.*;
// import org.apache.metamodel.data.DataSet;
import org.eobjects.metamodel.data.FirstRowDataSet;
import org.eobjects.metamodel.data.Row;
import org.eobjects.metamodel.data.DataSet;
import org.eobjects.metamodel.mongodb.MongoDbDataContext;
import org.eobjects.metamodel.query.OperatorType;
import org.eobjects.metamodel.query.Query;
import org.eobjects.metamodel.schema.Column;
import org.eobjects.metamodel.schema.ColumnType;
import org.eobjects.metamodel.schema.Table;

import java.sql.Connection;
import java.util.*;

/**
 * @program: MetaDataAgent
 * @Date: 2020/9/1 18:02
 * @Author: Mr.Zzc
 * @Description:
 */
public class MongoMetaModelUtil {
    /**
     * MongoClient mongoClient = new MongoClient(new MongoClientURI("mongodb://assad:123@127.0.0.1:27017/?authSource=testdb&ssl=false"));
     *
     * 其中 mongo uri 的验证连接格式如下：
     * mongodb://userName:password@host/?authSource=databaseName&ssh=true;
     *
     *
     * @param source
     * @return
     */
    public static MongoClient getMongoClientWithCredential(MetaSource source){
        String verifyDatabase = source.getVerifyDatabase();
        String user = source.getDbUsername();
        String password = source.getDbPassword();
        String ip = source.getDbIp();
        int port = source.getDbPort();

        ArrayList<MongoCredential> mongoCredentials = new ArrayList<>();
        MongoCredential credential = MongoCredential.createCredential(user,verifyDatabase,password.toCharArray());   //验证对象
        MongoClientOptions options = MongoClientOptions.builder().sslEnabled(false).build();     //连接操作对象
        mongoCredentials.add(credential);
        MongoClient mongoClient = new MongoClient(new ServerAddress(ip,port),mongoCredentials,options);
        return mongoClient;
    }

    public static MongoClient getMongoClient(MetaSource source){
        // String url = source.getDbUrl();
        // String url = "mongodb://guest:Guest123!@172.20.2.166/?authSource=admin&ssh=true;";
        // return  new MongoClient(new MongoClientURI(url));
        String verifyDatabase = source.getVerifyDatabase();
        String user = source.getDbUsername();
        String password = source.getDbPassword();
        String ip = source.getDbIp();
        int port = source.getDbPort();

        // ArrayList<MongoCredential> mongoCredentials = new ArrayList<>();
        // MongoCredential credential = MongoCredential.createCredential(user,verifyDatabase,password.toCharArray());   //验证对象
        MongoClientOptions options = MongoClientOptions.builder().sslEnabled(false).build();     //连接操作对象
        // mongoCredentials.add(credential);
        MongoClient mongoClient = new MongoClient(new ServerAddress(ip,port),options);
        return mongoClient;
    }


    public static MongoDbDataContext getMongoDbContext(MetaSource source){
        MongoClient mongoClient = getMongoClient(source);
        DB db = new DB(mongoClient,source.getDbName());
        // DB db = null;
        // db = source.getDbSchema().equals(source.getDbName())?new DB(mongo,source.getDbSchema()):new DB(mongo,source.getDbSchema());

        MongoDbDataContext mc = new MongoDbDataContext(db);
        return mc;
    }


    public static MongoDbDataContext getMongoDbContextWithCredential(MetaSource source){
        MongoClient mongoClient = getMongoClientWithCredential(source);
        Mongo mongo = mongoClient;
        DB db = new DB(mongo,source.getDbName());
        // DB db = null; //使用schema连接
        // db = source.getDbSchema().equals(source.getDbName())?new DB(mongo,source.getDbSchema()):new DB(mongo,source.getDbSchema());
        MongoDbDataContext mc = new MongoDbDataContext(db);
        return mc;
    }

    /**
     * 获取芒果客户端
     * @param source
     * @return
     */
    public static MongoClient getMClient(MetaSource source){
        return (StringUtil.isEmpty(source.getDbUsername())||StringUtil.isEmpty(source.getDbUsername()))?getMongoClient(source): getMongoClientWithCredential(source);
    }

    /**
     * 获取芒果上下文
     * @param source
     * @return
     */
    public static MongoDbDataContext getMContext(MetaSource source){
        return (StringUtil.isEmpty(source.getDbUsername())||StringUtil.isEmpty(source.getDbUsername()))?getMongoDbContext(source): getMongoDbContextWithCredential(source);
    }


    public static ArrayList<String> getColumn(MetaSource source){
        String dbObjName = source.getDbObjName();
        MongoDbDataContext mContext = getMContext(source);
        Table table = mContext.getTableByQualifiedLabel(dbObjName);
        Column[] columns = table.getColumns();
        ArrayList<String> list = new ArrayList<>();
        for (Column column : columns) {
            ColumnType type = column.getType();
            String name = type.name();
            list.add(column.getName()+","+name);
        }
        return list;
    }

    /**
     * 芒果获取集合字段 及其 类型（备注:获取的类型为统一metaModel自有类型）
     * @param source
     * @return
     */
    public static ArrayList<ColumnInfo> getMongoDbColumn(MetaSource source){
        String dbObjName = source.getDbObjName();
        MongoDbDataContext mContext = getMContext(source);
        Table table = mContext.getTableByQualifiedLabel(dbObjName);
        Column[] columns = table.getColumns();
        ArrayList<ColumnInfo> list = new ArrayList<>();
        for (Column column : columns) {
            ColumnInfo columnInfo = new ColumnInfo();
            columnInfo.setColName(column.getName());
            columnInfo.setColType(column.getType().name());
            list.add(columnInfo);
        }
        return list;
    }


    public static ArrayList<String> getColumnName(MetaSource source){
        String dbObjName = source.getDbObjName();
        MongoDbDataContext mContext = getMContext(source);
        Table table = mContext.getTableByQualifiedLabel(dbObjName);
        Column[] columns = table.getColumns();
        ArrayList<String> list = new ArrayList<>();
        for (Column column : columns) {
            list.add(column.getName());
        }
        return list;
    }









    /**
     * 芒果查询数据
     * @param source
     * @param condition
     * @return
     */
    public static ResponseResult getDataOnMongoDbWithCondition(MetaSource source, QueryCondition condition){
        ResponseResult result = new ResponseResult();
        Connection connection = null;
        MongoDbDataContext mc= null;
        try {
            mc = MongoMetaModelUtil.getMContext(source);
        } catch (Exception e) {
            result.setData("连接失败:"+e.getMessage());
            return  result;
        }
        if (mc == null) {
            result.setData("连接失败");
            return result;
        }
        ArrayList<String> colNames = condition.getColNames();
        if (colNames==null||colNames.size()==0){//如果列名为空 则默认查询全部
            colNames = getColumnName(source);
        }
        String orderBy = condition.getOrderBy();
        List whereCondition = condition.getWhereCondition();
        String aggregateFunction = condition.getAggregateFunction();//设想支持一个聚合函数
        String groupBy = condition.getGroupBy();
        String having = condition.getHaving();
        int currentPage = condition.getCurrentPage();
        int size = condition.getSize();
        int firstRow = currentPage==1?1:(currentPage-1)*size+1;
        int maxRows = size;

        String tableName = source.getDbObjName();
        String schemaName = source.getDbSchema();
        String dbType = source.getDbType();
        // if (SourceCode.MYSQL_CODE.equalsIgnoreCase(dbType)){
        //     tableName = schemaName + "." + tableName;
        // }else if (SourceCode.ORACLE_CODE.equalsIgnoreCase(dbType)){
        //     tableName = schemaName + ".\"" + tableName+"\"";
        // }else if (SourceCode.SQLSERVER_CODE.equals(dbType)){
        //     tableName = schemaName + "." + tableName;
        // }

        List<HashMap<String, Object>> AllDataList = new ArrayList<>();//用于封装主数据
        try {
            int rowsToMaterialize;

            if (firstRow == 1) {
                rowsToMaterialize = maxRows;
            } else {
                rowsToMaterialize = maxRows + (firstRow - 1);
            }


            Table table = mc.getTableByQualifiedLabel(tableName);
            if (table==null){
                result.setData("该集合："+tableName+" 不存在");
                return result;
            }

            Query query = new Query();
            for (String colName : colNames) {
                //TODO test
                /*if (colName.equalsIgnoreCase("electric")){
                    colName = "count(electric)";
                    query.select(colName);
                }else {
                }*/
                query = query.select(table.getColumnByName(colName));

            }
            //TODO 测试
            // colNames.clear();
            // colNames.add("nm");
            // colNames.add("electric");
            // query.select(table.getColumnByName("nm"))
            //         .select("AVG(electric)");
            query.from(table);
            if (whereCondition!=null&&whereCondition.size()!=0){
                //Column column, OperatorType operatorType, Object operand
                // query.where(table.getColumnByName("FullName"), OperatorType.LIKE,"'%广场%'");
                for (Object o : whereCondition) {
                    if (StringUtil.isEmpty(o)) continue;
                    OperatorType operatorType = getMetaModel3_5OperatorType(o.toString());
                    query = operatorType==null?
                            query.where(o.toString()):
                            query.where(table.getColumnByName(o.toString().split(operatorType.toSql())[0].trim()),
                                    operatorType,
                                    o.toString().split(operatorType.toSql())[1].toString().trim());
                }
            }
            query = orderBy==null?query:query.orderBy(orderBy);
            // query = groupBy==null?query:query.groupBy(groupBy);
            // query = having==null?query:query.having(having);
            query.setMaxRows(rowsToMaterialize);
            DataSet dataSet = mc.executeQuery(query);
            if (firstRow > 1) {
                dataSet = new FirstRowDataSet((DataSet)dataSet, firstRow);
            }
            // dataSet = (firstRow == 1)?dataSet:(new FirstRowDataSet((DataSet)dataSet, firstRow));

            List<Row> rows = dataSet.toRows();
            // final LinkedHashMap<String, Object> mapModel = listToMapKey(colNames);

            for (Row row : rows) {
                LinkedHashMap<String, Object> map = new LinkedHashMap<>();
                Object[] values = row.getValues();
                map = listKeyArrayValueToMap(colNames, values);
                AllDataList.add(map);
                // String s = "";
                // for (Object value : values) {
                //     s+=value+" ";
                // }
                // System.out.println(s);
            }
            String resultData = JSONObject.toJSONString(AllDataList);
            result.setSuccess(true);
            result.setData("查询成功！");
            result.setReturnValue(resultData);
        } catch (Exception e) {
            result.setData("执行出错："+e.getMessage());
            e.printStackTrace();
        }finally {
            DBHelper.closeQuietly(connection);
        }

        return result;

    }

    /**
     * 根据字符串表达式获取3.5版本 获取包含的 OperatorType类型
     * 注: metaModel 5版本 不兼容低版本3.5
     * @param whereStr
     * @return
     */
    public static OperatorType getMetaModel3_5OperatorType(String whereStr){
        OperatorType type = null;
        for (OperatorType value : OperatorType.values()) {
            if (whereStr.contains("<>")){
                type = OperatorType.DIFFERENT_FROM;
            }
            else if (whereStr.contains(value.toSql())){
                type = value;
                System.out.println(value.name());
            }
        }
        return type;
    }




    public static LinkedHashMap<String,Object> listToMapKey(List<String> list){
        LinkedHashMap<String, Object> map = new LinkedHashMap<>();
        for (String key : list) {
            map.put(key,"");
        }
        return map;
    }

    /**
     * 将list值作为key 数组作为value 顺序存储 要确保list的key与数据value值一一对应
     * @param list
     * @param objects
     * @return
     */
    public static LinkedHashMap<String,Object> listKeyArrayValueToMap(List<String> list,Object[] objects){
        LinkedHashMap<String, Object> map = new LinkedHashMap<>();
        for (int i = 0; i < list.size(); i++) {
            for (int j = 0; j < objects.length; j++) {
                if (i==j){
                    map.put(list.get(i),objects[j]);
                }
            }
        }
        return map;
    }

}

相关依赖

<!--metaModel关系型数据库驱动包-->
		<dependency>
			<groupId>org.apache.metamodel</groupId>
			<artifactId>MetaModel-jdbc</artifactId>
			<version>5.3.2</version>
		</dependency>

		<dependency>
			<groupId>org.eobjects.metamodel</groupId>
			<artifactId>MetaModel-core</artifactId>
			<version>3.4.3</version>
		</dependency>


		<!--mongodb-->
		<dependency>
			<groupId>org.apache.metamodel</groupId>
			<artifactId>metamodel-mongodb</artifactId>
			<version>3.4.5</version>
			<scope>system</scope>
<!--			<systemPath>${project.basedir}/src/main/resources/lib/metamodel-mongodb-3.4.5.jar</systemPath>-->
			<systemPath>${project.basedir}/src/main/resources/lib/metamodel-mongodb-3.4.5.jar</systemPath>
		</dependency>
<!--		<dependency>-->
<!--			<groupId>org.apache.metamodel</groupId>-->
<!--			<artifactId>MetaModel-mongodb</artifactId>-->
<!--			<version>4.6.0</version>-->
<!--			<type>pom</type>-->
<!--		</dependency>-->

		<!--<dependency>
			<groupId>org.mongodb</groupId>
			<artifactId>mongo-java-driver</artifactId>
			<version>3.3.0</version>
		</dependency>-->
		<dependency>
			<groupId>org.mongodb</groupId>
			<artifactId>mongo-java-driver</artifactId>
			<version>3.9.1</version>
		</dependency>

其中 metamodel-mongodb 引用的是本地jar包
下载地址：https://download.csdn.net/download/qq_27794563/12840322