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

发布时间：2020-12-12 01:13:40 所属栏目：MySql教程来源：网络整理

导读：《Mysql学习Java实现获得MySQL数据库中所有表的记录总数可行方法》要点：本文介绍了Mysql学习Java实现获得MySQL数据库中所有表的记录总数可行方法，希望对您有用。如果有疑问，可以联系我们。在MySQL中,可以通过SELECT COUNT(*) FROM table_name查询某个表

《Mysql学习Java实现获得MySQL数据库中所有表的记录总数可行方法》要点：
本文介绍了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,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();
}
}

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!