How to obtain the total number of records of all tables in the MySQL database using Java _ MySQL

Java to obtain the total number of records of all tables in the MySQL database. bitsCN.com in MySQL, you can use select count (*) FROM table_name to query the number of records in a table. What should I do if I want to know the total number of all other records in a database? This article provides two feasible Java programs to solve this problem.

1. First, determine the number of tables in the database, and then execute select count (*) FROM table_name for each table

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.exe cuteQuery ();
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.exe cuteQuery ();
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. use the tables table of the information_schema database

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