前言

公司需要统计一年内，客户那边的数据增量数据。
由于有多个微服务，多个数据库及表，于是便写了一个程序 使用 jdbc 遍历统计各个库中表的数据

代码

import java.sql.*;
import java.util.*;

public class StatiscWx1 {
    // MySQL 8.0 以下版本 - JDBC 驱动名及数据库 URL
    static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    static final String DB_URL = "jdbc:mysql://xxx.cn:3306/";
    // 数据库的用户名与密码，需要根据自己的设置
    static final String USER = "name";
    static final String PASS = "";


    static final String DB_CONF = "?serverTimezone=GMT&useUnicode=true&characterEncoding=utf8";






    public static void main(String[] args) {

        System.out.println("开始统计");
        Map map = new HashMap();


        List<String> list = new ArrayList();
        list.add("WUXI_Base");
        list.add("WUXI_ConstructionChecking");
        list.add("WUXI_DesignChecking");
        list.add("WUXI_DeviceManage");
        list.add("WUXI_Document");
        list.add("WUXI_Fabricate");
        list.add("WUXI_FileService");
        list.add("WUXI_ModelApproval");
        list.add("WUXI_Monitor");
        list.add("WUXI_OsdmDevice");
        list.add("WUXI_PainSpot");
        list.add("WUXI_Port");
        list.add("WUXI_ProgressPlan");
        list.add("WUXI_Pushpin");
        list.add("WUXI_Quality");
        list.add("WUXI_QuartzJob");
        list.add("WUXI_RaiseDust");
        list.add("WUXI_RiskSource");
        list.add("WUXI_RiskSource_new");
        list.add("WUXI_Safety");
        list.add("WUXI_VideoMonitor");


        for (String o : list) {
            Long countSql = 0L;
            map.put(o,countSql);
            getCount(o,map);
        }

        Long aCount = 0L;
        Set set = map.keySet();
        for (Object o : set) {
            Long o1 = (Long) map.get(o);
            aCount = aCount + o1;
        }

        System.out.println("一年内总数："+ aCount);
    }

    private static void getCount(String dataBase,Map map) {
        Long countSql = (Long) map.get(dataBase);

        Connection conn = null;
        Statement stmt = null;
        try{
            // 注册 JDBC 驱动
            Class.forName(JDBC_DRIVER);

            // 打开链接
            conn = DriverManager.getConnection(DB_URL+DB_CONF,USER,PASS);

            // 执行查询
            stmt = conn.createStatement();


            String sql;


            // 查询所有表
            List<String> list = selectAllTables(stmt);

            for (String tableName : list) {
                if (!tableName.equals("ErrorLog")){
                    try {
                        sql = "select count(1) num from "+ dataBase+ "." +tableName + " where createTime > '2021-11-07'";
                        ResultSet rs = stmt.executeQuery(sql);

                        // 展开结果集数据库
                        while(rs.next()){
                            // 通过字段检索
                            int num  = rs.getInt("num");
                            countSql = countSql + num;
                        }
                        // 完成后关闭
                        rs.close();
                    } catch (Exception e){}

                    try {
                        sql = "select count(1) num from "+ dataBase+ "." +tableName + " where createDateTime > '2021-11-07'";
                        ResultSet rs = stmt.executeQuery(sql);

                        // 展开结果集数据库
                        while(rs.next()){
                            // 通过字段检索
                            int num  = rs.getInt("num");
                            countSql = countSql + num;
                        }
                        // 完成后关闭
                        rs.close();
                    } catch (Exception e){}
                }

            }


            System.out.println(dataBase+"一年内总数："+ countSql);

            map.put(dataBase,countSql);


            stmt.close();
            conn.close();
        }catch(SQLException se){
            // 处理 JDBC 错误
            se.printStackTrace();
        }catch(Exception e){
            // 处理 Class.forName 错误
            e.printStackTrace();
        }finally{
            // 关闭资源
            try{
                if(stmt!=null) stmt.close();
            }catch(SQLException se2){
            }// 什么都不做
            try{
                if(conn!=null) conn.close();
            }catch(SQLException se){
                se.printStackTrace();
            }
        }
    }

    private static List<String> selectAllTables(Statement stmt) throws SQLException {
        List<String> tables = new ArrayList<>();
        try{

                String sql;
                sql = "select table_name from information_schema.tables where table_schema='WUXI_Base'";
                ResultSet rs = stmt.executeQuery(sql);
                // 展开结果集数据库
                while(rs.next()){
                    // 通过字段检索
                    String tableName  = rs.getString("table_name");
                    tables.add(tableName);
                }
                // 完成后关闭
                rs.close();

            } catch (Exception e){

            }

        return tables;
    }
}