公司项目上需要对数据库中的数据量有个预估,对于数据量比较大的数据库要进行特殊处理,我想到可以用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();
}
}
运行代码如下: