Home > Tutorial > h2 > Java SQL Query Count Records

Java SQL Query Count Records

If your Java application uses a database to store data then you need to understand some basic SQL queries in Java. One of those basic requirements is to check how many records you have in a database table.

The example source code used below is basic SQL query that can apply to most database software (Oracle, MySql, H2, etc). The main part of the SQL query is selecting the count(*) field from the database table.


Java SQL getRecordCount() Method

This example method creates a connection to our H2 database, creates a PreparedStatement object with the correct SQL query, and executes it. The result is printed to STDOUT. You can change “as count” to “as total” or whatever you like, which will be the field name returned.

	public static void getRecordCount() 
	{

    //Create H2 DB Connection Object
		Connection connection = getDBConnection();
				
		PreparedStatement prepStatement;
				
		try
		{
			prepStatement = connection.prepareStatement("select count(*) as count from workers");    
			ResultSet resultSet = prepStatement.executeQuery();
			
			while (resultSet.next()) 
			{
				System.out.println("Number of Records in Workers: "+resultSet.getInt("count"));
			}
			
			prepStatement.close();
		
		}
		catch(Exception ex) 
		{
			System.out.println(ex.toString());
		}
	
	}
Number of Records in Workers: 4


Java SQL Query Record Count

Below you will find the fill source code sample used to query database table for record count.

package com.jcd.java.tutorials.MavenProject;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class JavaH2Database 
{
	 private static final String DATABASE_DRIVER = "org.h2.Driver";
	 private static final String DATABASE_CONNECTION = "jdbc:h2:tcp://localhost/~/test";
	 private static final String DATABASE_USER = "sa";
	 private static final String DATABASE_PASSWORD = "XXXXXXXXXX";
	    
	public static void main(String[] args) 
	{
                //Call our getRecordCount() method
		getRecordCount();	
	}

    //Query the WORKERS table for number of records
	public static void getRecordCount() 
	{
		//Create H2 DB Connection Object
		Connection connection = getDBConnection();
				
		PreparedStatement prepStatement;
				
		try
		{
			prepStatement = connection.prepareStatement("select count(*) as count from workers");    
			ResultSet resultSet = prepStatement.executeQuery();
			
			while (resultSet.next()) 
			{
				System.out.println("Number of Records in Workers: "+resultSet.getInt("count"));
			}
			
			prepStatement.close();
		
		}
		catch(Exception ex) 
		{
			System.out.println(ex.toString());
		}
	
	}

    //Make a connection to the H2 Database
	private static Connection getDBConnection() 
	{
    
		Connection H2DBConnection = null;
		
        try 
        {
            Class.forName(DATABASE_DRIVER);
        } 
        catch (ClassNotFoundException ex) 
        {
            System.out.println(ex.toString());
        }
        try 
        {
        	H2DBConnection = DriverManager.getConnection(DATABASE_CONNECTION, DATABASE_USER, DATABASE_PASSWORD);
            
            return H2DBConnection;
        } 
        catch (SQLException ex) 
        {
        	System.out.println(ex.toString());
        }
        
        return H2DBConnection;
    }
}


Concluding Comments

Copy and paste this working sample code to query a database table to get record count. You may need to edit some of the private static variables at the top, including DATABASE_PASSWORD to fit your working environment.

Smedley
Posted by

Smedley

Smedley has been programming in Java for about twenty years. He enjoys sharing code samples to other fellow Java coders.

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *

Copyright © 2018 JavaCodeDepot