Java實現獲得MySQL數據庫中所有表的記錄總數可行方法

2024-07-24 13:01:48

字體：大中小

來源：轉載

供稿：網友

在MySQL中，可以通過SELECT COUNT(*) FROM table_name查詢某個表中有多少條記錄。如果想知道某個數據庫中所有別的記錄總數應該怎么做呢？本文給出兩種可行的Java程序，解決該問題。

1. 首先確定數據庫中有多少個表，然后對每個表執行SELECT COUNT(*) FROM table_name

復制代碼代碼如下:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
public class Test {
private static String driver = "com.mysql.jdbc.Driver";
private static String url = "jdbc:mysql://127.0.0.1/";
private static String db = "test";
private static String user = "root";
private static String pass = "test";
static Connection conn = null;
static Statement statement = null;
static PreparedStatement ps = null;
static ResultSet rs = null;

static List<String> tables = new ArrayList<String>();

public static void startMySQLConn() {
try {
Class.forName(driver).newInstance();
conn = DriverManager.getConnection(url+db, user, pass);
if (!conn.isClosed()) {
System.out.println("Succeeded connecting to MySQL!");
}

statement = conn.createStatement();
} catch (Exception e) {
e.printStackTrace();
}
}

public static void closeMySQLConn() {
if(conn != null){
try {
conn.close();
System.out.println("Database connection terminated!");
} catch (SQLException e) {
e.printStackTrace();
}
}
}

public static void getTables() {
String sql = "show tables;";
try {
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
while (rs.next()) {
tables.add(rs.getString(1));
}
} catch (Exception e) {
e.printStackTrace();
}
}

public static long getDbSum() {
long sum = 0;
String sql = "select count(*) from ";
try {
for(String tblName: tables) {
ps = conn.prepareStatement(sql + tblName + ";");
rs = ps.executeQuery();
while (rs.next()) {
sum += rs.getInt(1);
}
}
} catch (Exception e) {
e.printStackTrace();
}
return sum;
}

public static void main(String[] args) {
startMySQLConn();
getTables();
System.out.println(getDbSum());
closeMySQLConn();
}
}

2. 借助information_schema庫的tables表

復制代碼代碼如下:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
public class Test {
private static String driver = "com.mysql.jdbc.Driver";
private static String url = "jdbc:mysql://127.0.0.1/";
private static String db = "test";
private static String user = "root";
private static String pass = "test";
static Connection conn = null;
static Statement statement = null;
static PreparedStatement ps = null;
static ResultSet rs = null;

public static void startMySQLConn() {
try {
Class.forName(driver).newInstance();
conn = DriverManager.getConnection(url+db, user, pass);
if (!conn.isClosed()) {
System.out.println("Succeeded connecting to MySQL!");
}

statement = conn.createStatement();
} catch (Exception e) {
e.printStackTrace();
}
}

public static void closeMySQLConn() {
if(conn != null){
try {
conn.close();
System.out.println("Database connection terminated!");
} catch (SQLException e) {
e.printStackTrace();
}
}
}

public static void useDB() {
String sql = "use information_schema;";
try {
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
} catch (Exception e) {
e.printStackTrace();
}
}

public static long getDbSum() {
long sum = 0;
String sql = "select table_name,table_rows from tables where TABLE_SCHEMA = '" +
db + "' order by table_rows desc;";
//System.out.println(sql);
try {
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
while (rs.next()) {
sum += rs.getInt(2);
}
} catch (Exception e) {
e.printStackTrace();
}
return sum;
}

public static void main(String[] args) {
startMySQLConn();
useDB();
System.out.println(getDbSum());
closeMySQLConn();
}
}