Java: Search a record from a database table

In this example we will search a customer's record from a database table named 'CUSTOMER'. The database used is MySQL and the code is written in Eclipse IDE.

To search the data from the table a database connection is needed. To get the connection to the database follow the instructions and use the code in this post -> Connecting Java and MySQL in Eclipse

Once the DBConnection  class is there we will make a CustomerHandler class that will query the database and return the searched record.

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 Customer searchCustomer(int customerAccountNumber) throws Exception
{
Customer c = null;
DBConnection db = new DBConnection();
Connection conn = db.getConnection();
PreparedStatement st = null;

if (conn == null)
{
throw new Exception("Unable to connect to the database. conection = null!");
}
try
{
st = conn.prepareStatement("Select * from Customer where account_number = ?");
st.setInt(1, customerAccountNumber);
ResultSet rs = st.executeQuery();

if (rs.next())
{
c = new Customer();
c.setAccountNumber(rs.getInt("account_number"));
c.setDate(rs.getDate("date"));
                                c.setName(rs.getString("name"));
}
catch (Exception e1)
{
Logger.getGlobal().severe("Unable to retrieve customer data from the database: " + e1.getMessage());
System.out.println("SQLException: " + e1.getMessage());
e1.printStackTrace();
throw new Exception("Unable to retrieve customer data from the database!" + e1.getMessage());
}
finally
{
try
{
if (conn != null)
{
conn.close();
}
if (st != null)
{
st.close();
}
}
catch (SQLException e1)
{
Logger.getGlobal().severe("Error occured while closing the connection or statement: " + e1.getMessage());
System.out.println("SQLException: " + e1.getMessage());
e1.printStackTrace();
throw new SQLException("Error occured while closing the connection or statement. " + e1.getMessage());
}
}
return c;

}

}

To check the working of our class we will write a driver class 

public class Driver

{

public static void main(String[] args)

{

CustomerHandler custHandler = new CustomerHandler();

int customerAccountNumber = 1;

try

{

custHandler.searchCustomer(customerAccountNumber);

}

catch (Exception e)

{

e.printStackTrace();

}

        }

}

Finally the Customer class to save the results.

import java.util.Date;

public class Customer
{
private int accountNumber;
private Date date;
private String customerName;

public Customer()
{
}

public int getAccountNumber()
{
return accountNumber;
}

public void setAccountNumber(int accountNumber)
{
this.accountNumber = accountNumber;
}

public Date getDate()
{
return date;
}

public void setDate(Date date)
{
this.date = date;
}

public String getCustomerName()
{
return customerName;
}

public void setCustomerName(String customerName)
{
this.customerName = customerName;
}

}