Java implementation to obtain the total number of records in the Mysql database a feasible method _mysql

In MySQL, you can query how many records are in a table from the Select COUNT (*) from TABLE_NAME. What should you do if you want to know the total number of records in a database? In this paper, two feasible Java programs are given to solve the problem.

1. First determine how many tables are in the database, and then execute select COUNT (*) from table_name for each table

Copy Code code as follows:

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. Tables using the INFORMATION_SCHEMA library

Copy Code code as follows:

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