MySQLUtil.java

package mobvista.dmp.util;

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

/**
 * @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 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)";
            if (conn.prepareStatement(sql).executeUpdate() + conn.prepareStatement(lastSql).executeUpdate() == 2) {
                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) {
        System.out.println(getLastPartition("dwh", "ods_user_info"));
        System.out.println(update("dwh", "ods_user_info", "20191126"));
    }
}