Examples with DBUtil msec.org.DBUtil used on opensource projects

Example 11 with DBUtil

use of msec.org.DBUtil in project MSEC by Tencent.

the class AddSecondLevelServiceIPInfo method exec.

public AddSecondLevelServiceIPInfoResponse exec(AddSecondLevelServiceIPInfoRequest request) {
    Logger logger = Logger.getLogger("AddSecondLevelServiceIPInfo");
    AddSecondLevelServiceIPInfoResponse response = new AddSecondLevelServiceIPInfoResponse();
    int instance_memory = request.getInstance_type() * INSTANCE_MEMORY_STEP;
    response.setMessage("unkown error.");
    response.setStatus(100);
    String result = checkIdentity();
    if (!result.equals("success")) {
        response.setStatus(99);
        response.setMessage(result);
        return response;
    }
    if (request.getIp() == null || request.getIp().equals("")) {
        response.setMessage("Some request field is  empty.");
        response.setStatus(100);
        return response;
    }
    ArrayList<String> ips = Tools.splitBySemicolon(request.getIp());
    Set<String> set_ips = new HashSet<>(ips);
    if (ips.size() < 2 || set_ips.size() != ips.size()) {
        response.setMessage("IP input error!");
        response.setStatus(100);
        return response;
    }
    if (ips.size() % request.getCopy() != 0) {
        response.setMessage("the number of IPs is not divisible by the number of copies!");
        response.setStatus(100);
        return response;
    }
    DBUtil util = new DBUtil();
    if (util.getConnection() == null) {
        response.setMessage("DB connect failed.");
        response.setStatus(100);
        return response;
    }
    try {
        //check if the IP has been installed in set...
        String sql = "select ip, port, group_id, memory, master, status from t_service_info where first_level_service_name=? and second_level_service_name=? and ip in (";
        List<Object> params = new ArrayList<>();
        params.add(request.getFirst_level_service_name());
        params.add(request.getSecond_level_service_name());
        for (int i = 0; i < ips.size(); i++) {
            if (i == 0) {
                params.add(ips.get(i));
                sql += "?";
            } else {
                params.add(ips.get(i));
                sql += ",?";
            }
        }
        sql += ")";
        try {
            List<ServerInfo> serverList = util.findMoreRefResult(sql, params, ServerInfo.class);
            if (serverList.size() > 0) {
                response.setStatus(101);
                response.setMessage("Input IP(s) is already installed.");
                return response;
            }
        } catch (Exception e) {
            response.setStatus(100);
            response.setMessage("db query exception!");
            e.printStackTrace();
            return response;
        }
        int group_id_index = 0;
        sql = "select COALESCE(max(group_id),0) as group_id from t_service_info where first_level_service_name=? and second_level_service_name=?";
        params.clear();
        params.add(request.getFirst_level_service_name());
        params.add(request.getSecond_level_service_name());
        try {
            Map<String, Object> result_map = util.findSimpleResult(sql, params);
            if (result_map.size() != 1) {
                response.setStatus(101);
                response.setMessage("db set error!");
                return response;
            }
            group_id_index = ((Long) result_map.get("group_id")).intValue();
        } catch (Exception e) {
            response.setStatus(100);
            response.setMessage("db query exception!");
            e.printStackTrace();
            return response;
        }
        ArrayList<AddSecondLevelServiceIPInfoResponse.IPInfo> addedIPs = new ArrayList<>();
        for (String ip : ips) {
            AddSecondLevelServiceIPInfoResponse.IPInfo info = response.new IPInfo();
            info.setIp(ip);
            RemoteShell remoteShell = new RemoteShell();
            StringBuffer output = new StringBuffer();
            result = remoteShell.SendCmdsToRunAndGetResultBack(ipinfo_cmd, ip, output);
            if (result == null || !result.equals("success")) {
                logger.info(String.format("remote error:%s|%s", ip, result));
                info.setStatus_message("shell");
            } else {
                logger.info(output);
                String[] ipinfo_results = output.toString().split(" ");
                if (ipinfo_results.length != 4) {
                    info.setStatus_message("shell");
                } else {
                    //core mhz mem cpu
                    info.setCpu_cores(Integer.parseInt(ipinfo_results[0]));
                    info.setCpu_mhz(Integer.parseInt(ipinfo_results[1]));
                    info.setMemory_avail(Integer.parseInt(ipinfo_results[2]) / 1024);
                    info.setCpu_load(Integer.parseInt(ipinfo_results[3]));
                }
            }
            addedIPs.add(info);
        }
        response.setAdded_ips(addedIPs, instance_memory);
        ArrayList<String> set_ids = new ArrayList<>();
        //get set ids
        int set_num = ips.size() / request.getCopy();
        sql = "select name from t_set_id_name where name not in (select distinct set_id from t_service_info where first_level_service_name=? and second_level_service_name=?) order by name asc limit ?";
        params.clear();
        params.add(request.getFirst_level_service_name());
        params.add(request.getSecond_level_service_name());
        params.add(set_num);
        try {
            ArrayList<Map<String, Object>> result_list = util.findModeResult(sql, params);
            if (result_list.size() != set_num) {
                response.setStatus(101);
                response.setMessage("db set error!");
                return response;
            }
            for (int i = 0; i < result_list.size(); i++) set_ids.add(result_list.get(i).get("name").toString());
        } catch (SQLException e) {
            response.setStatus(100);
            response.setMessage("db query exception!");
            e.printStackTrace();
            return response;
        }
        CalcAddedIPForServers(response, request.getCopy(), set_ids, group_id_index, instance_memory);
        if (response.getMessage().equals("success") && group_id_index == 0) {
            //update t_second_level_service
            logger.info(String.format("Create|Update copy_num & mem_per_instance|%d|%d", group_id_index, instance_memory));
            sql = "update t_second_level_service set copy_num=?, memory_per_instance=? where first_level_service_name=? and second_level_service_name=?";
            params.clear();
            params.add(request.getCopy());
            params.add(instance_memory);
            params.add(request.getFirst_level_service_name());
            params.add(request.getSecond_level_service_name());
            try {
                int addNum = util.updateByPreparedStatement(sql, params);
                if (addNum < 0) {
                    response.setMessage("failed to update table");
                    response.setStatus(100);
                    return response;
                }
            } catch (SQLException e) {
                response.setMessage("update status failed:" + e.toString());
                response.setStatus(100);
                e.printStackTrace();
                return response;
            }
        } else
            logger.info(group_id_index);
        return response;
    } finally {
        util.releaseConn();
    }
}

Example 12 with DBUtil

use of msec.org.DBUtil in project MSEC by Tencent.

the class RemoveServerProc method updateStatus.

private void updateStatus(String status) {
    Logger logger = Logger.getLogger(RemoveServerProc.class);
    DBUtil util = new DBUtil();
    if (util.getConnection() == null) {
        return;
    }
    try {
        String sql = "update t_install_plan set status=? where plan_id=? and ip=? and port=?";
        List<Object> params = new ArrayList<Object>();
        params.add(status);
        params.add(plan_id);
        params.add(server.getIp());
        params.add(server.getPort());
        int updNum = util.updateByPreparedStatement(sql, params);
        if (updNum != 1) {
            return;
        }
    } catch (Exception e) {
        e.printStackTrace();
        logger.error(e.getMessage());
        return;
    } finally {
        util.releaseConn();
    }
}

Example 13 with DBUtil

use of msec.org.DBUtil in project MSEC by Tencent.

the class QueryFirstLevelService method exec.

public QueryFirstLevelServiceResponse exec(QueryFirstLevelServiceRequest request) {
    QueryFirstLevelServiceResponse resp = new QueryFirstLevelServiceResponse();
    String result = checkIdentity();
    if (!result.equals("success")) {
        resp.setStatus(99);
        resp.setMessage(result);
        return resp;
    }
    DBUtil util = new DBUtil();
    if (util.getConnection() == null) {
        resp.setStatus(100);
        resp.setMessage("db connect failed!");
        return resp;
    }
    List<FirstLevelService> serviceList;
    String sql = "select first_level_service_name from t_first_level_service where type='standard' ";
    List<Object> params = new ArrayList<Object>();
    if (request.getService_name() != null && request.getService_name().length() > 0) {
        sql += " where first_level_service_name=? ";
        params.add(request.getService_name());
    }
    try {
        serviceList = util.findMoreRefResult(sql, params, FirstLevelService.class);
    } catch (Exception e) {
        resp.setStatus(100);
        resp.setMessage("db query exception!");
        e.printStackTrace();
        return resp;
    } finally {
        util.releaseConn();
    }
    resp.setService_list((ArrayList<FirstLevelService>) serviceList);
    resp.setMessage("success");
    resp.setStatus(0);
    return resp;
}

Example 14 with DBUtil

use of msec.org.DBUtil in project MSEC by Tencent.

the class QuerySecondLevelServiceDetail method exec.

public QuerySecondLevelServiceDetailResponse exec(QuerySecondLevelServiceDetailRequest request) {
    Logger logger = Logger.getLogger(QuerySecondLevelServiceDetail.class);
    QuerySecondLevelServiceDetailResponse resp = new QuerySecondLevelServiceDetailResponse();
    String result = checkIdentity();
    if (!result.equals("success")) {
        resp.setStatus(99);
        resp.setMessage(result);
        return resp;
    }
    DBUtil util = new DBUtil();
    if (util.getConnection() == null) {
        resp.setStatus(100);
        resp.setMessage("db connect failed!");
        return resp;
    }
    List<ServerInfo> serverList;
    ClusterInfo cluster_info;
    try {
        String sql = "select copy_num, memory_per_instance, plan_id from t_second_level_service where first_level_service_name=? and second_level_service_name=?";
        List<Object> params = new ArrayList<Object>();
        params.add(request.getFirst_level_service_name());
        params.add(request.getSecond_level_service_name());
        cluster_info = util.findSimpleRefResult(sql, params, ClusterInfo.class);
        if (!cluster_info.getPlan_id().isEmpty()) {
            ArrayList<ServerInfo> planList;
            sql = "select ip, port, set_id, group_id, memory, master, status, operation, recover_host from t_install_plan where plan_id = ? order by group_id asc, master desc";
            params.clear();
            params.add(cluster_info.getPlan_id());
            planList = util.findMoreRefResult(sql, params, ServerInfo.class);
            String operation = "";
            //0:"ok", 1: "in progress" or 2: "error"
            int server_status = 0;
            for (ServerInfo server : planList) {
                operation = server.getOperation();
                int status = 0;
                if (!server.getStatus().startsWith("Done")) {
                    if (server.getStatus().startsWith("[ERROR]")) {
                        status = 2;
                    } else
                        status = 1;
                }
                server_status = Math.max(status, server_status);
            }
            if (server_status != 1) {
                //not in progress
                if (server_status == 0) {
                    //ok, remove service_info first...TODO, needs to judge automatically
                    if (operation.equals("add")) {
                        for (ServerInfo server : planList) {
                            sql = "insert into t_service_info(first_level_service_name, second_level_service_name, ip, port, set_id, group_id, memory, master) values(?,?,?,?,?,?,?,?)";
                            params.clear();
                            params.add(request.getFirst_level_service_name());
                            params.add(request.getSecond_level_service_name());
                            params.add(server.getIp());
                            params.add(server.getPort());
                            params.add(server.getSet_id());
                            params.add(server.getGroup_id());
                            params.add(server.getMemory());
                            params.add(server.isMaster());
                            try {
                                int addNum = util.updateByPreparedStatement(sql, params);
                                if (addNum < 0) {
                                    resp.setMessage("failed to insert table");
                                    resp.setStatus(100);
                                    return resp;
                                }
                            } catch (SQLException e) {
                                resp.setMessage("add record failed:" + e.toString());
                                resp.setStatus(100);
                                e.printStackTrace();
                                return resp;
                            }
                        }
                    } else if (operation.equals("del")) {
                        sql = "delete from t_service_info where first_level_service_name=? and second_level_service_name=? and set_id=?";
                        params.clear();
                        params.add(request.getFirst_level_service_name());
                        params.add(request.getSecond_level_service_name());
                        params.add(planList.get(0).getSet_id());
                        int delNum = util.updateByPreparedStatement(sql, params);
                        if (delNum < 0) {
                            resp.setMessage("failed to delete table");
                            resp.setStatus(100);
                            return resp;
                        }
                    } else if (operation.equals("rec")) {
                        String[] ip_pair = planList.get(0).getRecover_host().split(":");
                        sql = "update t_service_info set ip=?, port=? where first_level_service_name=? and second_level_service_name=? and ip=? and port=?";
                        params.clear();
                        params.add(planList.get(0).getIp());
                        params.add(planList.get(0).getPort());
                        params.add(request.getFirst_level_service_name());
                        params.add(request.getSecond_level_service_name());
                        params.add(ip_pair[0]);
                        params.add(Integer.parseInt(ip_pair[1]));
                        int addNum = util.updateByPreparedStatement(sql, params);
                        if (addNum < 0) {
                            resp.setMessage("failed to update table");
                            resp.setStatus(100);
                            return resp;
                        }
                    }
                }
                sql = "update t_second_level_service set plan_id=? where first_level_service_name=? and second_level_service_name=?";
                params.clear();
                params.add("");
                params.add(request.getFirst_level_service_name());
                params.add(request.getSecond_level_service_name());
                int addNum = util.updateByPreparedStatement(sql, params);
                if (addNum < 0) {
                    resp.setMessage("failed to update table");
                    resp.setStatus(100);
                    return resp;
                }
                cluster_info.setPlan_id("");
            }
        }
        sql = "select ip, port, set_id, group_id, memory, master from t_service_info where first_level_service_name=? and second_level_service_name=? order by group_id asc, master desc";
        params.clear();
        params.add(request.getFirst_level_service_name());
        params.add(request.getSecond_level_service_name());
        serverList = util.findMoreRefResult(sql, params, ServerInfo.class);
        if (serverList.size() > 0 && cluster_info.getPlan_id().isEmpty()) {
            //working plan will cause config inconsistent...
            HashMap<String, ServerInfo> server_map = new HashMap<>();
            for (ServerInfo server : serverList) {
                server_map.put(server.getIp() + ":" + server.getPort(), server);
            }
            JedisHelper helper = new JedisHelper(server_map, request.getFirst_level_service_name(), request.getSecond_level_service_name(), cluster_info);
            helper.CheckStatusDetail();
            if (!helper.isOK()) {
                resp.setStatus(101);
                resp.setMessage(helper.getError_message());
                return resp;
            }
            if (helper.isChanged()) {
                serverList = util.findMoreRefResult(sql, params, ServerInfo.class);
            }
        }
    } catch (Exception e) {
        resp.setStatus(100);
        resp.setMessage("db query exception!");
        e.printStackTrace();
        return resp;
    } finally {
        util.releaseConn();
    }
    resp.setServers((ArrayList<ServerInfo>) serverList);
    resp.setCluster_info(cluster_info);
    resp.setMessage("success");
    resp.setStatus(0);
    return resp;
}

Example 15 with DBUtil

use of msec.org.DBUtil in project MSEC by Tencent.

the class QueryStaffList method exec.

public QueryStaffListResponse exec(QueryStaffListRequest request) {
    QueryStaffListResponse resp = new QueryStaffListResponse();
    String result = checkIdentity();
    if (!result.equals("success")) {
        resp.setStatus(99);
        resp.setMessage(result);
        return resp;
    }
    DBUtil util = new DBUtil();
    if (util.getConnection() == null) {
        resp.setStatus(100);
        resp.setMessage("db connect failed!");
        return resp;
    }
    List<StaffInfo> staffInfoList;
    //System.out.printf("name:%s, phone:%s\n", request.getStaff_name(), request.getStaff_phone());
    String sql = "select staff_name, staff_phone from t_staff ";
    List<Object> params = new ArrayList<Object>();
    if (request.getStaff_name() != null && request.getStaff_name().length() > 0) {
        sql += " where staff_name=? ";
        params.add(request.getStaff_name());
    } else if (request.getStaff_phone() != null && request.getStaff_phone().length() > 0) {
        sql += " where staff_phone=? ";
        params.add(request.getStaff_phone());
    }
    try {
        staffInfoList = util.findMoreRefResult(sql, params, StaffInfo.class);
    } catch (Exception e) {
        resp.setStatus(100);
        resp.setMessage("db query exception!");
        e.printStackTrace();
        return resp;
    } finally {
        util.releaseConn();
    }
    resp.setStaff_list((ArrayList<StaffInfo>) staffInfoList);
    resp.setMessage("success");
    resp.setStatus(0);
    return resp;
}