如何在 JDBC 中获取行数？

SQL Count() 函数可返回表中的行数。利用此函数可以获取表中的行数。

select count(*) from TABLE_NAME;

假设我们已通过 Statement 对象与 MySQL 建立连接，并在名为 mydatabase 的数据库中创建了一个表:

//Creating the Statement object
Statement stmt = con.createStatement();
//Query to create a table
String query = "CREATE TABLE Cricketers_Data( "
   + "First_Name VARCHAR(255), "
   + "Last_Name VARCHAR(255), "
   + "Date_Of_Birth Date, "
   + "Place_Of_Birth VARCHAR(255), "
   + "Country VARCHAR(255))";
//Executing the query
stmt.execute(query);
System.out.println("Table created......");

接着我们利用 prepared statement 向此表中插入 5 列:

//Query to insert values
Query = "INSERT INTO Cricketers_Data values (?, ?, ?, ?, ?)";
PreparedStatement pstmt = con.prepareStatement(query);

pstmt.setString(1, "Shikhar");
pstmt.setString(2, "Dhawan");
pstmt.setDate(3, new Date(376401869000L));
pstmt.setString(4, "Delhi");
pstmt.setString(5, "India");
pstmt.executeUpdate();

pstmt.setString(1, "Jonathan");
pstmt.setString(2, "Trott");
pstmt.setDate(3, new Date(356788333000L));
pstmt.setString(4, "CapeTown");
pstmt.setString(5, "SouthAfrica");
pstmt.executeUpdate();

pstmt.setString(1, "Kumara");
pstmt.setString(2, "Sangakkara");
pstmt.setDate(3, new Date(246801133000L));
pstmt.setString(4, "Matale");
pstmt.setString(5, "Srilanka");
pstmt.executeUpdate();

pstmt.setString(1, "Virat");
pstmt.setString(2, "Kohli");
pstmt.setDate(3, new Date(546781933000L));
pstmt.setString(4, "Delhi");
pstmt.setString(5, "India");
pstmt.executeUpdate();

pstmt.setString(1, "Rohit");
pstmt.setString(2, "Sharma");
pstmt.setDate(3, new Date(546781933000L));
pstmt.setString(4, "Nagpur");
pstmt.setString(5, "India");
pstmt.executeUpdate();

这样 Cricketers_Data 表的显示效果如下:

+------------+------------+---------------+----------------+-------------+
| First_Name | Last_Name  | Date_Of_Birth | Place_Of_Birth | Country     |
+------------+------------+---------------+----------------+-------------+
| Shikhar    | Dhawan     | 1981-12-05    | Delhi          | India       |
| Jonathan   | Trott      | 1981-04-22    | CapeTown       | SouthAfrica |
| Kumara     | Sangakkara | 1977-10-27    | Matale         | Srilanka    |
| Virat      | Kohli      | 1987-04-30    | Delhi          | India       |
| Rohit      | Sharma     | 1987-04-30    | Nagpur         | India       |
+------------+------------+---------------+----------------+-------------+

示例

以下 JDBC 程序建立与 MySQL 的连接，并显示名为 cricketers_data 的表中的行数。

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class Count_Example {
   public static void main(String args[]) throws Exception {
      //Registering the Driver
      DriverManager.registerDriver(new com.mysql.jdbc.Driver());
      //Getting the connection
      String mysqlUrl = "jdbc:mysql://127.0.0.1/mydatabase";
      Connection con = DriverManager.getConnection(mysqlUrl, "root", "password");
      System.out.println("Connection established......");
      //Creating the Statement object
      Statement stmt = con.createStatement();
      //Query to get the number of rows in a table
      String query = "select count(*) from Cricketers_Data";
      //Executing the query
      ResultSet rs = stmt.executeQuery(query);
      //Retrieving the result
      rs.next();
      int count = rs.getInt(1);
      System.out.println("Number of records in the cricketers_data table: "+count);
   }
}

输出

Connection established......
Number of records in the cricketers_data table: 5

更新于: 30-7-2019

6K+ 浏览

开启你的 职业生涯

完成课程以获得认证

开始
广告