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