Java代码计算出数据库服务器上所有库的数据量

发布于:2023-01-04 ⋅ 阅读:(172) ⋅ 点赞:(0)

公司项目上需要对数据库中的数据量有个预估,对于数据量比较大的数据库要进行特殊处理,我想到可以用Java代码实现这一功能。(PS: 当然也可以写SQL实现。)
首先pom文件数据库连接导入依赖:

<dependencies>
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>8.0.17</version>
    </dependency>
  </dependencies>

代码如下:

/**
 * 计算数据库的数据量
 */
public class DataBaseCount {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //配置信息
        String url = "jdbc:mysql://192.168.**.**:3306/upsrod?useSSL=false&useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai";
        String username = "upsrdp"; // 账号
        String password = "******"; // 密码
        long sum = 0L;
        long start = System.currentTimeMillis();
        //1.加载驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        //2.连接数据库
        Connection connection = DriverManager.getConnection(url, username, password);
        Statement statement = connection.createStatement();
        //3.编写SQl
        String sql = "SHOW FULL TABLES";
        ResultSet resultSet = statement.executeQuery(sql);
        List<String> databasename = new ArrayList<>();
        while (resultSet.next()) {
            String Tables_in_upsrod = resultSet.getString("Tables_in_upsrod");
            databasename.add(Tables_in_upsrod);
        }
        for (String database : databasename) {
            String sql2 = "select count(1) from " + database;
            ResultSet resultSet1 = statement.executeQuery(sql2);
            while (resultSet1.next()) {
                String ret = resultSet1.getString("count(1)");
                if (Integer.parseInt(ret) > 1000000)
                    System.out.println(database + ":" + ret);
                sum = sum + Integer.parseInt(ret);
            }
        }
        System.out.println("数据总条数为:" + sum);
        long end = System.currentTimeMillis();
        System.out.println("运行总时长:" + (end - start) + "ms");
        resultSet.close();
        statement.close();
        connection.close();
    }
}

运行代码如下:
在这里插入图片描述


网站公告

今日签到

点亮在社区的每一天
去签到