Home > Database > Mysql Tutorial > Java实现获得MySQL数据库中所有表的记录总数可行方法_MySQL

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

WBOY
Release: 2016-06-01 13:24:52
Original
831 people have browsed it

bitsCN.com 在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();
}
}
bitsCN.com

Related labels:
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template