如何用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();
	}
}