There are so many people online who have provided a solution to getting the number of rows from an SQLite database table using Java. Some of them work some don’t, this article is going to illustrate the most efficient way to get the number of rows from a table of an SQLite database using Java

This article marks the end of Java Database tutorial series, please make sure you read all tutorials under this series

There are many reasons you would want to count the number of rows from an SQLite Database table, for example, you wanted to print the number of users in your database and display  something like: “6 Active Users”

If you are looking for an efficient way to get the number of rows from an SQLite database using Java, you are in the right place.

How to Get the Number of Rows From a Table of  an SQLite Database Using Java Programming Language

Well, to get the number of rows from SQLite Database, we are going to use the COUNT() function of SQL. COUNT() returns the number of rows in form of an Integer, satisfying the criteria in WHERE Clause. If there is no match, the COUNT() returns zero.

This makes COUNT() as a perfect and most efficient way to get the number of rows from an SQLite Database using Java

The Following Code Illustrates How to Get The Number of Rows From a Table of an SQLite Database in Java Programming Using COUNT():

private static void getNumberOfUsers() {
    // lets do this...
    Connection con = DbConnection.connect(); 
    PreparedStatement ps = null; 
    ResultSet rs = null; 
    
    try {
      
      String sql  = "select count(firstName) from users "; 
      ps = con.prepareStatement(sql); 
      rs = ps.executeQuery(); 
      
      // this will give us the count
      int size = rs.getInt(1);
      System.out.println("You have "+size+" users");
      
      
    } catch(SQLException e) {
      System.out.println(e.toString());
    } finally {
      // close connection
      try {
        rs.close();
        ps.close();
        con.close(); 
      } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
      } 
    }
    
  }

Java Database Tutorial Series

1. Java Database Connection
2. How to Insert Data into SQLite database Using Java
3. The best way to fetch data from SQLite database using Java
4. How to Update Data Into SQLite Database using Java Programming 
5. How to delete a row (a record) from SQLite database using Java Programming
6. How to get the number of rows in an SQLite Database Table Using Java