Java: Retrieving data from MySQL database table

Example demonstrates how we can retrieve data from a MySQL table in Java. The table used in this example is named "Customer" and contains customer data.

We will write a Customer class which will contain the data retrieved from the database.

public class Customer
{
private int accountNumber;

private String name;

public Customer(int acn, String name)
{
accountNumber = acn;
this.name = name;
}

public int getACN()
{
return accountNumber;
}

public String getName()
{
return name;
}
}


Now we will write a CustomerHandler class which will retrieve the data from the database. 

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

public class CustomerHandler
{
public CustomerHandler()
{
}

public ArrayList<Customer> getAllCustomers()
{
ArrayList<Customer> customerList = null;

DBConnection db = new DBConnection();
Connection conn = db.getConnection();
try
{
Statement stmt = conn.createStatement();

ResultSet rs = stmt.executeQuery("SELECT * FROM customer");
if (rs != null)
{
customerList = new ArrayList<Customer>();
while (rs.next())
{
int acNumber = rs.getInt(1);
String name = rs.getString(2);
Customer customer = new Customer(acNumber, name);
customerList.add(customer);
}
}
}
catch (SQLException e)
{
e.printStackTrace();
}
return customerList;
}

}

For getting the Connection con check the Connecting Java and MySQL link. The DBConnection class provided in that link can be used to get the database connection.

Finally there is a Driver class which calls the CustomerHandler and displays the data:

import java.util.ArrayList;

public class Driver

{

public static void main(String[] args)

{

CustomerHandler custHandler = new CustomerHandler();

ArrayList<Customer> customerList = custHandler.getAllCustomers();

if (customerList == null || customerList.isEmpty()) 

                      {

                             System.out.println("No records found");

                             return;

                      }

for (Customer c : customerList)

{

System.out.println("Customer Name: " + c.getName());

System.out.println("Account Number: " + c.getACN());

}

}

}