MySQLUtil.java

package mobvista.dmp.util;

import mobvista.dmp.datasource.rtdmp.entity.KV;
import org.apache.commons.lang3.StringUtils;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.Date;

/**
 * @package: mobvista.dmp.util
 * @author: wangjf
 * @date: 2019-11-27
 * @time: 18:26
 * @email: jinfeng.wang@mobvista.com
 * @phone: 152-1062-7698
 */
public class MySQLUtil {

    static SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy-MM-dd");
    static SimpleDateFormat sdf2 = new SimpleDateFormat("yyyyMMdd");

    static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    static final String URL = PropertyUtil.getProperty("config.properties", "mysql.dmp.url");
    static final String DB_URL = "jdbc:mysql://" + URL + ":3306/dmp?useUnicode=true&characterEncoding=utf8&useSSL=false";
    static final String USER = PropertyUtil.getProperty("config.properties", "mysql.dmp.user");
    static final String PASS = PropertyUtil.getProperty("config.properties", "mysql.dmp.password");

    public static String getLastPartition(String dbName, String tbName) {
        Connection conn = null;
        Statement stmt = null;
        String partition = "";

        try {
            Class.forName(JDBC_DRIVER);
            conn = DriverManager.getConnection(DB_URL, USER, PASS);

            stmt = conn.createStatement();
            String sql;
            //  last partition
            sql = "SELECT part FROM table_info WHERE db_name = '" + dbName + "' AND tb_name = '" + tbName + "' AND flag = 1 ORDER BY part DESC LIMIT 1";
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next()) {
                partition = rs.getString("part");
            }
            rs.close();
            stmt.close();
            conn.close();
        } catch (Exception se) {
            se.printStackTrace();
        } finally {
            try {
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException ignored) {
                try {
                    if (conn != null) {
                        conn.close();
                    }
                } catch (SQLException se) {
                    se.printStackTrace();
                }
            }
        }
        return partition;
    }

    public static KV getPartitionTime(String dbName, String tbName) {
        Connection conn = null;
        Statement stmt = null;
        String partition = "";
        Timestamp utime = new Timestamp(0L);

        try {
            Class.forName(JDBC_DRIVER);
            conn = DriverManager.getConnection(DB_URL, USER, PASS);

            stmt = conn.createStatement();
            String sql;
            //  last partition
            sql = "SELECT part,utime FROM dmp.table_info WHERE db_name = '" + dbName + "' AND tb_name = '" + tbName + "' AND flag = 1 ORDER BY part DESC LIMIT 1";
            System.out.println("getPartitionTime,SQL --->>> " + sql);
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next()) {
                partition = rs.getString("part");
                utime = rs.getTimestamp("utime");
                System.out.println("partition:" + partition + ",utime:" + utime);
            }
            if (StringUtils.isNotBlank(partition)) {
                System.out.println("getPartitionTime Null,SQL --->>> " + sql);
            }
            System.out.println("getPartitionTime,SQL --->>> " + sql);
            rs.close();
            stmt.close();
            conn.close();
        } catch (Exception se) {
            System.out.println(se.getMessage());
        } finally {
            try {
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException ignored) {
                try {
                    if (conn != null) {
                        conn.close();
                    }
                } catch (SQLException se) {
                    se.printStackTrace();
                }
            }
        }
        return new KV(partition, String.valueOf(utime.getTime() / 1000 + 28800));
    }

    public static boolean update(String dbName, String tbName, String partition) {
        Connection conn = null;
        boolean flag = false;

        try {
            Class.forName(JDBC_DRIVER);
            conn = DriverManager.getConnection(DB_URL, USER, PASS);
            // update last partition
            String sql = "REPLACE INTO table_info(db_name, tb_name, part, flag) VALUES('" + dbName + "','" + tbName + "','" + partition + "', true)";
            //  String lastPart = DateUtil.getDayByString(partition, "yyyyMMdd", -1);
            //  String lastSql = "REPLACE INTO table_info(db_name, tb_name, part, flag) VALUES('" + dbName + "','" + tbName + "','" + lastPart + "', false)";
            //  + conn.prepareStatement(lastSql).executeUpdate() == 2
            if (conn.prepareStatement(sql).executeUpdate() == 1) {
                flag = true;
            } else {
                flag = false;
            }
            conn.close();
        } catch (Exception se) {
            se.printStackTrace();
        } finally {
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return flag;
    }

    public static void main(String[] args) {
        KV kv = getPartitionTime("dwh", "audience_merge_v1");

        long nowTime = DateUtil.parse(DateUtil.format(new Date(), "yyyy-MM-dd HH:mm:ss"), "yyyy-MM-dd HH:mm:ss").getTime() / 1000;

        System.out.println(kv.getV());
        System.out.println(Long.parseLong(kv.getV()) > nowTime - 1200);
        //  System.out.println(getLastPartition("dwh", "audience_merge_v1"));
        //  System.out.println(update("dwh", "audience_merge_v1", "2021072913"));
    }
}