Java JDBC: Getting all list of records using JDBC PreparedStatement Interface

In this article, we will use JDBC API to fetch all records from newly created table in MySQL database from Java application (.e.; using PreparedStatement Interface

 

1. Pre-requisite:

  • Java JDK 1.8.0_77
  • MySQL database 5.5.16
  • Eclipse Luna IDE 4.4.0
  • mysql-connector-java-5.1.38.jar file

 

2. Database parameters:

Let us move on and code an example to connect MySQL database from Java application to fetch all records using JDBC API. But before that, we will list down required things to connect database

  • database server IP or address (localhost)
  • sever port (3306)
  • database name (PLAYER_INFO)
  • username (root)
  • password (root@123)

Note: All bold are database values to connect MySQL database

 

3. Getting all list of records using JDBC PreparedStatement Interface

  • As we are ready with required things to connect MySQL database from Java application
  • We can use one of the methods from PreparedStatement Interface to execute “select list of records” SQL query
    1. execute();
    2. executeUpdate();
    3. executeQuery();
  • Let us code a simple example using PreparedStatement Interface

GetAllRecordUsingJDBCPreparedStatement.java

package in.bench.resources.mysql.db.example;

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

public class GetAllRecordUsingJDBCPreparedStatement {

	public static void main(String[] args) {

		// variables
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;

		// Step 1: Loading or registering MySQL JDBC driver class
		try {
			Class.forName("com.mysql.jdbc.Driver");
		}
		catch(ClassNotFoundException cnfex) {
			System.out.println("Problem in loading MySQL JDBC driver");
			cnfex.printStackTrace();
		}

		// Step 2: Opening database connection
		try {

			// Step 2.A: Create and get connection using DriverManager
			connection = DriverManager.getConnection(
         "jdbc:mysql://localhost:3306/PLAYER_INFO", "root", "root@123"); 

			// create SQL query to fetch all player records
			String sqlSelectQuery = "SELECT * FROM PLAYER";

			// Step 2.B: Creating JDBC Statement 
		preparedStatement = connection.prepareStatement(sqlSelectQuery);

			// Step 2.C: Executing SQL & retrieve data into ResultSet
			resultSet = preparedStatement.executeQuery();

			System.out.println("ID\tName\t\t\tAge\tMatches");
			System.out.println("==\t================\t===\t=======");

			// processing returned data and printing into console
			while(resultSet.next()) {
				System.out.println(resultSet.getInt(1) + "\t" + 
						resultSet.getString(2) + "\t" + 
						resultSet.getInt(3) + "\t" +
						resultSet.getInt(4));
			}
		}
		catch(SQLException sqlex){
			sqlex.printStackTrace();
		}
		finally {

			// Step 3: Closing database connection
			try {
				if(null != connection) {

					// cleanup resources, once after processing
					resultSet.close();
					preparedStatement.close();

					// and then finally close connection
					connection.close();
				}
			}
			catch (SQLException sqlex) {
				sqlex.printStackTrace();
			}
		}
	}
}

Output:

ID	Name			Age	Matches
==	================	===	=======
1	Sachin Tendulkar	43	200
2	Shane Warne		44	145
3	Kevin Pietersen	34	104
4	Shahid Afridi		35	27
5	Brian Lara		45	131
6	Graeme Smith		34	117
7	Mahela Jayawardene	37	145

Note: This demo example is to showcase that we can select all records using PreparedStatement Interface.

 

4. Download:

 

In next article, we will see how it helps to get faster results while using PreparedStatement instead of Statement Interface

 

Read Also:

 

References:

 

Happy Coding !!
Happy Learning !!

Java JDBC: Getting single record using JDBC PreparedStatement Interface
Java JDBC: Inserting a record using JDBC PreparedStatement Interface