服务器之家:专注于服务器技术及软件下载分享
分类导航

Mysql|Sql Server|Oracle|Redis|MongoDB|PostgreSQL|Sqlite|DB2|mariadb|Access|数据库技术|

服务器之家 - 数据库 - Mysql - Java实现获得MySQL数据库中所有表的记录总数可行方法

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

2019-12-30 15:37MYSQL教程网 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<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(); 

延伸 · 阅读

精彩推荐