获取mysql可行方法_Java实现获得MySQL数据库中所有表的记录总数可行方法

可以通过SELECT COUNT(*) FROM table_name查询某个表中有多少条记录。本文给出两种可行的Java程序查询所有别的记录方法，感兴趣朋友可以了解下

在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 tables = new ArrayList();  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();  }  }