platform-bi-server/src/main/java/com/server/ImplementSqlService.java

package com.server;

import com.dao.DataColumnMapper;
import com.dao.DataConnectorMapper;
import com.model.bo.ToSql;
import com.model.po.Databases;
import com.model.pojo.RepCode;
import com.model.pojo.RepEntity;
import com.model.vo.dataVo.ColumnTypeInfo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.*;

@Service
public class ImplementSqlService {
    @Autowired
    DataColumnMapper dataColumnMapper;
    @Autowired
    DataConnectorMapper dataConnectorMapper;

    /*
    执行数据源
     */
    public RepEntity implementSql(ToSql toSql) {
        String sqlStr = toSql.getStrSql();
        System.out.println(sqlStr);
        if ("".equals(sqlStr) || sqlStr == null) {
            return new RepEntity(RepCode.ChartsNameNull);
        }

        List<LinkedHashMap<String, Object>> columnData = dataColumnMapper.getColumn(sqlStr.toUpperCase());
        LinkedHashMap<String, String> tarValue = getColumnType(columnData);

        //取列名
        List<ColumnTypeInfo> isList = new ArrayList<>();
        LinkedHashMap<String, Object> columnDataKey = columnData.get(0);
        Iterator<String> iter = columnDataKey.keySet().iterator();
        while (iter.hasNext()){
            String key = iter.next();
            String vaul = tarValue.get(key);
            ColumnTypeInfo columnTypeInfo = new ColumnTypeInfo();
            columnTypeInfo.setColumnName(key);
            columnTypeInfo.setColumnType(vaul);
            isList.add(columnTypeInfo);
        }

        return new RepEntity(RepCode.success,isList);
    }

    /*
    判断列类型
     */
    public LinkedHashMap<String, String> getColumnType(List<LinkedHashMap<String, Object>> columnData){
        LinkedHashMap<String, String> tarValue = new LinkedHashMap<>();
        for (int i = 0; i < columnData.size(); i++){
            LinkedHashMap<String, Object> columnDataMap = columnData.get(i);
            Iterator<String> cm = columnDataMap.keySet().iterator();
            while (cm.hasNext()){
                String key = cm.next();
                if (tarValue.containsKey(key)){
                    continue;
                }else {
                    Object value = columnDataMap.get(key);
                    if (value == null) {
                        continue;
                    }else {
                        String values = getType(value);
                        tarValue.put(key, values);
                    }
                    if (tarValue.size() == columnDataMap.size()){
                        break;
                    }
                }
            }
        }
        return tarValue;
    }

    /*
    判断值类型
     */
    public String getType(Object obj){
        if(obj instanceof String){
            return "String";
        }else if (obj instanceof Double){
            return "Double";
        }else if (obj instanceof Integer){
            return "Integer";
        }else if (obj instanceof Date){
            return "Date";
        }else if (obj instanceof Boolean){
            return "Boolean";
        }else if(obj instanceof Byte){
            return "Byte";
        }else if (obj instanceof Short){
            return "Short";
        }else if (obj instanceof BigDecimal){
            return "BigDecimal";
        }else if (obj instanceof Object){
            return "Object";
        }else {
            return "";
        }
    }



    /*
    判断数据库连接
     */
    public RepEntity connectTest(Databases databases) {
        String password = "";
        String passwords = databases.getPassWord();
        int id = databases.getId();
        String pw = dataConnectorMapper.getBasesById(id);
        if (passwords != null && !"".equals(passwords)){
            password = passwords;
        }else {
            password = pw;
        }

        String url = "jdbc:oracle:thin:@" + databases.getAddrass() + ":" + databases.getPort() + ":" + databases
                .getDataName();
        String username = databases.getUserName();
//        String password = databases.getPassWord();
        Connection con = null;
        try {
            DriverManager.setLoginTimeout(8);
            con = DriverManager.getConnection(url, username, password);
            System.out.println("数据库连接成功");
            con.close();
        } catch (SQLException se) {
            System.out.println("数据库连接失败！");
            se.printStackTrace();
            return new RepEntity(RepCode.Null);
        }
        return new RepEntity(RepCode.success);
    }
}