欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2024-03-31 12:21:04
在mysql中,可以通过select count(*) from table_name查询某个表中有多少条记录。如果想知道某个数据库中所有别的记录总数应该怎么做呢?本文给出...
在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();
}
}