package mobvista.dmp.util; import mobvista.dmp.datasource.rtdmp.entity.KV; 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 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"); utime = rs.getTimestamp("utime"); } 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 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"); 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")); // System.out.println(update("dwh", "audience_merge", "2021072913")); } }