如何用Java实现获得MySQL数据库中所有表的记录总数

如何用Java实现获得MySQL数据库中所有表的记录总数

在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();
 }
}