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

Example 16 with DBUtil

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

the class RemovePlan method exec.

public RemovePlanResponse exec(RemovePlanRequest request) {
    RemovePlanResponse resp = new RemovePlanResponse();
    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;
    }
    JedisHelper helper = null;
    List<ServerInfo> serverList;
    ServerInfo ok_server;
    try {
        String sql;
        List<Object> params = new ArrayList<Object>();
        //get plan_id;
        sql = "select copy_num, memory_per_instance, plan_id from t_second_level_service where first_level_service_name=? and second_level_service_name=?";
        params.add(request.getFirst_level_service_name());
        params.add(request.getSecond_level_service_name());
        try {
            ClusterInfo cluster_info = util.findSimpleRefResult(sql, params, ClusterInfo.class);
            if (!cluster_info.getPlan_id().isEmpty()) {
                resp.setStatus(101);
                resp.setMessage("Please wait until the ongoing plan finishes.");
                return resp;
            }
        } catch (Exception e) {
            resp.setStatus(100);
            resp.setMessage("db query exception!");
            e.printStackTrace();
            return resp;
        }
        sql = "select ip, port, set_id, group_id, memory, master, status from t_service_info where first_level_service_name=? and second_level_service_name=? and master=1 and status='OK' limit 1";
        try {
            ok_server = util.findSimpleRefResult(sql, params, ServerInfo.class);
        } catch (Exception e) {
            resp.setStatus(100);
            resp.setMessage("OK server not found!");
            e.printStackTrace();
            return resp;
        }
        sql = "select ip, port, set_id, group_id, memory, master, 'Planning' as status from t_service_info where first_level_service_name=? and second_level_service_name=? and set_id=? order by group_id asc, master desc";
        params.add(request.getSet_id());
        try {
            serverList = util.findMoreRefResult(sql, params, ServerInfo.class);
        } catch (Exception e) {
            resp.setStatus(100);
            resp.setMessage("db query exception!");
            e.printStackTrace();
            return resp;
        }
        if (serverList == null || serverList.size() == 0) {
            resp.setStatus(100);
            resp.setMessage("set id error!");
            return resp;
        }
        String plan_id = AddSecondLevelServiceIPInfo.newPlanID();
        for (ServerInfo server : serverList) {
            sql = "insert into t_install_plan(plan_id, first_level_service_name, second_level_service_name, ip, port, set_id, group_id, memory, master, status, operation) values(?,?,?,?,?,?,?,?,?,?,?)";
            params.clear();
            params.add(plan_id);
            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());
            params.add(server.getStatus());
            params.add("del");
            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;
            }
        }
        sql = "update t_second_level_service set plan_id=? where first_level_service_name=? and second_level_service_name=?";
        params.clear();
        params.add(plan_id);
        params.add(request.getFirst_level_service_name());
        params.add(request.getSecond_level_service_name());
        try {
            int addNum = util.updateByPreparedStatement(sql, params);
            if (addNum < 0) {
                resp.setMessage("Failed to insert plan.");
                resp.setStatus(100);
                return resp;
            }
        } catch (SQLException e) {
            resp.setStatus(100);
            resp.setMessage("db query exception!");
            e.printStackTrace();
            return resp;
        }
        int copy_num = 0;
        ArrayList<String> ip_port_list = new ArrayList<>();
        for (ServerInfo server : serverList) {
            if (server.getSet_id().equals(serverList.get(0).getSet_id()) && server.getGroup_id() == serverList.get(0).getGroup_id())
                copy_num++;
            if (server.isMaster())
                ip_port_list.add(server.getIp() + ":" + Integer.toString(server.getPort()));
        }
        try {
            helper = new JedisHelper(plan_id, ok_server.getIp(), ok_server.getPort(), copy_num);
            new Thread(new ClusterProc(helper, ip_port_list, "remove", getServlet().getServletContext())).start();
        } catch (Exception e) {
            resp.setStatus(101);
            resp.setMessage("Redis cluster exception!");
            e.printStackTrace();
            return resp;
        }
        resp.setMessage("success");
        resp.setPlan_id(plan_id);
        resp.setStatus(0);
        return resp;
    } finally {
        util.releaseConn();
        if (helper != null)
            helper.close();
    }
}

Example 17 with DBUtil

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

the class GetSalt method exec.

public GetSaltResponse exec(LoginRequest request) {
    GetSaltResponse resp = new GetSaltResponse();
    if (request.getStaff_name() == null || request.getStaff_name().length() < 1) {
        resp.setStatus(100);
        resp.setMessage("login name empty!");
        return resp;
    }
    DBUtil util = new DBUtil();
    if (util.getConnection() == null) {
        resp.setStatus(100);
        resp.setMessage("db connect failed!");
        return resp;
    }
    List<StaffInfo> saltList;
    String sql = "select salt from t_staff where  staff_name=? ";
    List<Object> params = new ArrayList<Object>();
    params.add(request.getStaff_name());
    try {
        saltList = util.findMoreRefResult(sql, params, StaffInfo.class);
        if (saltList.size() != 1) {
            resp.setMessage("query salt failed");
            resp.setStatus(100);
            return resp;
        }
        String salt = saltList.get(0).getSalt();
        String challenge = geneChallenge();
        resp.setMessage("success");
        resp.setChallenge(challenge);
        resp.setSalt(salt);
        resp.setStatus(0);
        return resp;
    } catch (Exception e) {
        resp.setStatus(100);
        resp.setMessage("db query exception!");
        e.printStackTrace();
        return resp;
    } finally {
        util.releaseConn();
    }
}

Example 18 with DBUtil

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

the class InstallPlan method exec.

public InstallPlanResponse exec(InstallPlanRequest request) {
    Logger logger = Logger.getLogger(InstallPlan.class);
    InstallPlanResponse resp = new InstallPlanResponse();
    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;
    }
    ServerInfo helper_server = null;
    try {
        if (request.getAdded_servers() == null || request.getAdded_servers().size() == 0) {
            resp.setStatus(101);
            resp.setMessage("Request field error.");
            return resp;
        }
        Collections.sort(request.getAdded_servers(), compByGroupId());
        //get cluster info;
        ClusterInfo cluster_info = null;
        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());
        try {
            cluster_info = util.findSimpleRefResult(sql, params, ClusterInfo.class);
            if (cluster_info == null || !cluster_info.getPlan_id().isEmpty()) {
                resp.setStatus(101);
                resp.setMessage("Please wait until the ongoing plan finishes.");
                return resp;
            }
        } catch (Exception e) {
            resp.setStatus(100);
            resp.setMessage("db query exception!");
            e.printStackTrace();
            return resp;
        }
        List<ServerInfo> serviceList;
        ArrayList<String> ip_port_list = new ArrayList<>();
        HashMap<String, IPInfo> ip_map = new HashMap<>();
        //get current redis cluster ips
        sql = "select ip, port, status from t_service_info where first_level_service_name=? and second_level_service_name=? order by group_id asc, master desc limit 1";
        params.clear();
        params.add(request.getFirst_level_service_name());
        params.add(request.getSecond_level_service_name());
        try {
            serviceList = util.findMoreRefResult(sql, params, ServerInfo.class);
        } catch (Exception e) {
            resp.setStatus(100);
            resp.setMessage("db query exception!");
            e.printStackTrace();
            return resp;
        }
        if (serviceList.size() > 0) {
            for (ServerInfo info : serviceList) {
                if (info.getStatus().equals("OK")) {
                    helper_server = info;
                    break;
                }
            }
            if (helper_server == null) {
                //没有可用机器
                resp.setStatus(101);
                resp.setMessage("Cluster state error!");
                return resp;
            }
        }
        String plan_id = AddSecondLevelServiceIPInfo.newPlanID();
        for (ServerInfo server : request.getAdded_servers()) {
            sql = "insert into t_install_plan(plan_id, first_level_service_name, second_level_service_name, ip, port, set_id, group_id, memory, master, status, operation) values(?,?,?,?,?,?,?,?,?,?,?)";
            params.clear();
            params.add(plan_id);
            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());
            params.add("Planning");
            params.add("add");
            try {
                int addNum = util.updateByPreparedStatement(sql, params);
                if (addNum < 0) {
                    resp.setMessage("Failed to insert plan.");
                    resp.setStatus(100);
                    return resp;
                }
            } catch (SQLException e) {
                resp.setMessage("add record failed:" + e.toString());
                resp.setStatus(100);
                e.printStackTrace();
                return resp;
            }
            ip_port_list.add(server.getIp() + ":" + Integer.toString(server.getPort()));
            if (ip_map.containsKey(server.getIp())) {
                ip_map.get(server.getIp()).getPort_status_map().put(server.getPort(), server.getStatus());
            } else {
                IPInfo ipinfo = new IPInfo(server.getIp(), server.getMemory());
                ipinfo.getPort_status_map().put(server.getPort(), server.getStatus());
                ip_map.put(server.getIp(), ipinfo);
            }
        }
        sql = "update t_second_level_service set plan_id=? where first_level_service_name=? and second_level_service_name=?";
        params.clear();
        params.add(plan_id);
        params.add(request.getFirst_level_service_name());
        params.add(request.getSecond_level_service_name());
        try {
            int addNum = util.updateByPreparedStatement(sql, params);
            if (addNum < 0) {
                resp.setMessage("Failed to insert plan.");
                resp.setStatus(100);
                return resp;
            }
        } catch (SQLException e) {
            resp.setStatus(100);
            resp.setMessage("db query exception!");
            e.printStackTrace();
            return resp;
        }
        cluster_info.setPlan_id(plan_id);
        cluster_info.setIp_port_list(ip_port_list);
        logger.info(ip_port_list);
        new Thread(new InstallServerProc(new ArrayList<IPInfo>(ip_map.values()), helper_server, cluster_info, "create", getServlet().getServletContext())).start();
        /*
                try {
                    JedisHelper helper = null;
                    if (serviceList.size() > 0) {
                        helper = new JedisHelper(request.getPlan_id(), serviceList.get(0).getIp(), serviceList.get(0).getPort(), copy_num);
                        new Thread(new ClusterProc(helper, ip_port_list, "add", getServlet().getServletContext())).start();
                    } else {//CreateSet
                        helper = new JedisHelper(request.getPlan_id(), serverList.get(0).getIp(), serverList.get(0).getPort(), copy_num);
                        new Thread(new ClusterProc(helper, ip_port_list, "create", getServlet().getServletContext())).start();
                    }
                } catch (Exception e) {
                    resp.setStatus(101);
                    resp.setMessage("Redis cluster exception!");
                    e.printStackTrace();
                    return resp;
                }
*/
        resp.setPlan_id(plan_id);
        resp.setMessage("success");
        resp.setStatus(0);
        return resp;
    } finally {
        util.releaseConn();
    }
}

Example 19 with DBUtil

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

the class ESHelper method updateStatus_waitforcluster.

private void updateStatus_waitforcluster(String status) {
    Logger logger = Logger.getLogger(InstallServerProc.class);
    DBUtil util = new DBUtil();
    if (util.getConnection() == null) {
        return;
    }
    try {
        String sql = "update t_install_plan set status=? where plan_id=? and status=?";
        List<Object> params = new ArrayList<Object>();
        params.add(status);
        params.add(cluster_info.getPlan_id());
        params.add(waitforcluster_message);
        int updNum = util.updateByPreparedStatement(sql, params);
        if (updNum < 0) {
            return;
        }
    } catch (Exception e) {
        logger.error(e);
        logger.error(e);
        return;
    } finally {
        util.releaseConn();
    }
}

Example 20 with DBUtil

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

the class JedisHelper method insertInfo.

private void insertInfo(String ip, int port, String master_host) {
    changed = true;
    Logger logger = Logger.getLogger(InstallServerProc.class);
    DBUtil util = new DBUtil();
    if (util.getConnection() == null) {
        return;
    }
    try {
        if (master_host.isEmpty()) {
            String 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 1";
            List<Object> params = new ArrayList<Object>();
            params.add(first_level_service_name);
            params.add(second_level_service_name);
            Map<String, Object> result = util.findSimpleResult(sql, params);
            String set_id = result.get("name").toString();
            sql = "select COALESCE(max(group_id),0) as group_id from t_service_info where first_level_service_name=? and second_level_service_name=?";
            result = util.findSimpleResult(sql, params);
            if (result.size() != 1) {
                logger.error("get_group_id ERROR");
                return;
            }
            int group_id = ((Long) result.get("group_id")).intValue() + 1;
            ServerInfo server_info = new ServerInfo();
            server_info.setIp(ip);
            server_info.setPort(port);
            server_info.setSet_id(set_id);
            server_info.setGroup_id(group_id);
            server_info.setMemory(cluster_info.getMemory_per_instance());
            server_info.setMaster(true);
            sql = "insert into t_service_info(first_level_service_name, second_level_service_name, ip, port, set_id, group_id, memory, master) values(?,?,?,?,?,?,?,?)";
            params.add(ip);
            params.add(port);
            params.add(set_id);
            params.add(group_id);
            params.add(cluster_info.getMemory_per_instance());
            params.add(true);
            int updNum = util.updateByPreparedStatement(sql, params);
            if (updNum < 0) {
                logger.error(String.format("insert_master ERROR|%s|%d", ip, port));
                return;
            }
            host_map.put(ip + ":" + port, server_info);
        } else {
            ServerInfo server_info = host_map.get(master_host);
            if (server_info == null) {
                logger.error(String.format("insert_master ERROR|Master %s not found", master_host));
            } else {
                ServerInfo slave_info = new ServerInfo(server_info);
                slave_info.setIp(ip);
                slave_info.setPort(port);
                slave_info.setMaster(false);
                String sql = "insert into t_service_info(first_level_service_name, second_level_service_name, ip, port, set_id, group_id, memory, master) values(?,?,?,?,?,?,?,?)";
                List<Object> params = new ArrayList<Object>();
                params.add(first_level_service_name);
                params.add(second_level_service_name);
                params.add(ip);
                params.add(port);
                params.add(slave_info.getSet_id());
                params.add(slave_info.getGroup_id());
                params.add(cluster_info.getMemory_per_instance());
                params.add(false);
                int updNum = util.updateByPreparedStatement(sql, params);
                if (updNum < 0) {
                    logger.error(String.format("insert_slave ERROR|%s|%d|%s", ip, port, master_host));
                    return;
                }
                host_map.put(ip + ":" + port, slave_info);
            }
        }
    } catch (Exception e) {
        e.printStackTrace();
        logger.error(e.getMessage());
        return;
    } finally {
        util.releaseConn();
    }
}