package com.liquidnet.service.kylin;

import com.liquidnet.commons.lang.util.IPUtil;
import com.liquidnet.service.kylin.dto.vo.mongo.KylinIpAreaVo;
import lombok.extern.slf4j.Slf4j;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.data.mongodb.core.MongoTemplate;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

/**
 * @author AnJiabin <anjiabin@zhengzai.tv>
 * @version V1.0
 * @Description: TODO
 * @class: TestMysqlToMongo
 * @Package com.liquidnet.service.kylin
 * @Copyright: LightNet @ Copyright (c) 2021
 * @date 2021/11/24 13:33
 */
@Slf4j
@RunWith(SpringJUnit4ClassRunner.class)
@SpringBootTest
public class TestMysqlToMongo {
    @Autowired
    private MongoTemplate mongoTemplate;

//    public void mysqlIpDataToMongo(){
//        String driverClassName = "com.mysql.cj.jdbc.Driver";    //启动驱动
////        String url = "jdbc:mysql://pc-2ze6z2hxkug559q79.mysql.polardb.rds.aliyuncs.com:3306/mall";    //设置连接路径
////        String url = "jdbc:mysql://zhengzai.mysql.polardb.rds.aliyuncs.com:3306/mall";
////        String username = "readonly";    //数据库用户名
////        String password = "ZWDsf8Fy";    //数据库连接密码
//
//        String url = "jdbc:mysql://localhost:3306/dev_ln_scene";    //设置连接路径
//        String username = "root";    //数据库用户名
//        String password = "root123456";    //数据库连接密码
//
//
//        Connection conn = null;        //连接
//        PreparedStatement pstmt = null;    //使用预编译语句
//        ResultSet rs = null;    //获取的结果集
//        String sqlStr = "insert into bi_ip_area (ip_begin, ip_end, area, province, city, county, address) values(?,?,?,?,?,?,?)";
//
//        int totalNum = recordArrayList.size();
//        int pageSize = 1000;
//        int pageNum = 0;
//        int totalPage = totalNum % pageSize == 0 ? totalNum / pageSize : (totalNum / pageSize) + 1;;
//        try {
//            try {
//                Class.forName(driverClassName); //执行驱动
//            } catch (ClassNotFoundException e) {
//                e.printStackTrace();
//            }
//            conn = DriverManager.getConnection(url, username, password); //获取连接
//            pstmt = conn.prepareStatement(sqlStr);
//
//            conn.setAutoCommit(false);//自动提交记得关了....
//
//            int count = 0;
//
//            long timeStart = System.currentTimeMillis();
//            for(String[] recordArray: recordArrayList){
//                pstmt.setString(1,recordArray[0]);
//                pstmt.setString(2,recordArray[1]);
//                pstmt.setString(3,recordArray[2]);
//                pstmt.setString(4,recordArray[4]);
//                pstmt.setString(5,recordArray[5]);
//                pstmt.setString(6,recordArray[6]);
//                pstmt.setString(7,recordArray[3]);
//
//                pstmt.addBatch();
//
//                count ++;
//
//                if(count % pageSize == 0){
//                    pageNum++;
//                    pstmt.executeBatch();
//
//                    conn.commit();
//
//                    pstmt.clearBatch();
//                    System.out.println("执行第"+pageNum+"页提交--->>>"+"执行第"+((pageNum-1)*pageSize+1)+"行-第"+pageNum*pageSize+"行  耗时"+(System.currentTimeMillis()-timeStart)/1000+"秒");
//                    timeStart = System.currentTimeMillis();
//                }
//            }
//            if(totalNum % pageSize > 0){
//                pageNum++;
//                System.out.println("执行第"+pageNum+"页提交--->>>"+"执行第"+((pageNum-1)*pageSize+1)+"行-第"+((pageNum-1)*pageSize+totalNum % pageSize)+"行  耗时"+(System.currentTimeMillis()-timeStart)/1000+"秒");
//                pstmt.executeBatch();
//
//                conn.commit();
//            }
//
//            pstmt.close();
//
//            System.out.println("共执行插入"+count);
//
//        } catch (SQLException e) {
//            e.printStackTrace();
//        } finally {
//            //关闭资源,倒关
//            try {
//                if (rs != null) rs.close();
//                if (pstmt != null) pstmt.close();
//                if (conn != null) conn.close();  //必须要关
//            } catch (Exception e) {
//                e.printStackTrace();
//                System.out.println("关闭连接异常");
//            }
//        }
//
//    }
    @Test
    public void excuteBatchMysqlToMongo(){
        String driverClassName = "com.mysql.cj.jdbc.Driver";    //启动驱动
//        String url = "jdbc:mysql://localhost:3306/dev_ln_scene";    //设置连接路径
//        String username = "root";    //数据库用户名
//        String password = "root123456";    //数据库连接密码

        String url = "jdbc:mysql://39.107.71.112:3308/dev_ln_scene";    //设置连接路径
        String username = "root";    //数据库用户名
        String password = "Zhengzai@rd2U#";    //数据库连接密码


        Connection con = null;        //连接
        PreparedStatement pstmt = null;    //使用预编译语句
        ResultSet rs = null;    //获取的结果集

        long timeStart = System.currentTimeMillis();
        try {
            Class.forName(driverClassName); //执行驱动
            con = DriverManager.getConnection(url, username, password); //获取连接
            String sqlAllCount = "select count(0) as 'allCount' from bi_ip_area t"; //设置的预编译语句格式
            pstmt = con.prepareStatement(sqlAllCount);
            ResultSet allCount = pstmt.executeQuery();
            int allCountResult = 0;//共多少条
            int rowCount = 0;//当前多少条
            boolean isBreak = false;//是否终端循环
            while (allCount.next()) {
                allCountResult = allCount.getInt("allCount");
            }
            allCount.close();
            pstmt.close();

            int count = 5000;
            int page = 0;

            do {
                long timeStart1 = System.currentTimeMillis();

                ArrayList<KylinIpAreaVo> kylinIpAreaVoArrayList = new ArrayList();
                KylinIpAreaVo kylinIpAreaVo = null;
                //获取ip
                String sql = "select * from bi_ip_area t limit " + count * page + " ," + count; //设置的预编译语句格式
                pstmt = con.prepareStatement(sql);
                ResultSet ipData = pstmt.executeQuery();
                while (ipData.next()) {
                    kylinIpAreaVo = KylinIpAreaVo.getNew();
                    kylinIpAreaVo.setMid(ipData.getObject("mid").toString());
                    kylinIpAreaVo.setIpBegin(ipData.getObject("ip_begin").toString());
                    kylinIpAreaVo.setIpBeginLong(IPUtil.ipToLong(ipData.getObject("ip_begin").toString()));
                    kylinIpAreaVo.setIpEnd(ipData.getObject("ip_end").toString());
                    kylinIpAreaVo.setIpEndLong(IPUtil.ipToLong(ipData.getObject("ip_end").toString()));
                    kylinIpAreaVo.setArea(ipData.getObject("area").toString());
                    kylinIpAreaVo.setProvince(ipData.getObject("province").toString());
                    kylinIpAreaVo.setCity(ipData.getObject("city").toString());
                    kylinIpAreaVo.setCounty(ipData.getObject("county").toString());
                    kylinIpAreaVo.setAddress(ipData.getObject("address").toString());
                    kylinIpAreaVo.setCreatedAt(ipData.getObject("created_at").toString());
                    kylinIpAreaVoArrayList.add(kylinIpAreaVo);
                }

                mongoTemplate.insert(kylinIpAreaVoArrayList, KylinIpAreaVo.class.getSimpleName());
                System.out.println("执行insert 第 "+(page+1)+"页 总耗时==="+(System.currentTimeMillis()-timeStart1)/1000+"秒");

                ipData.last();
                rowCount += ipData.getRow();
                if (allCountResult > rowCount) {
                    isBreak = true;
                    page += 1;
                } else {
                    isBreak = false;
                }

            } while (isBreak);
            System.out.println("执行insert总耗时==="+(System.currentTimeMillis()-timeStart)/1000+"秒");
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            //关闭资源,倒关
            try {
                if (rs != null) rs.close();
                if (pstmt != null) pstmt.close();
                if (con != null) con.close();  //必须要关
            } catch (Exception e) {
            }
        }
    }
}
