Get data from database and return it in form of pojo object

Question

I have a method whose return type is customer which is a pojo. When I get required customerId from database I want to return the customer object with the corresponding data of that customerId. This means it has customer name, address etc. How to proceed for this?

public class Customer verifyCustomerId(cutomerId){
    statement = connection.createStatement();

    resultSet = statement.executeQuery("select customerid from customer");

    while (resultSet.next()) {
        if (cutomerId == resultSet.getInt(1)) {

            // return data corresponding to this id in the form of object of pojo customer

        }
    }

    return null;
}

Do you have the customerId at the start of this logic or do you need to find it first? — JamesB, Feb 25 '17 at 11:10

score 3 · Answer 1 · edited May 23 '17 at 12:25

Your SQL statement needs to select the data you want to get back from the database. Your current statement will return the customerId of all rows in the customer table.

Change your statement to:

PreparedStatement ps = con.prepareStatement("select name from customer where customerId = ?");
ps.setInt(1, cutomerId);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
    // use ResultSet to populate Customer pojo
}

I have selected name from the customer table here but that assumes such a column exists. Modify it to select the columns you want.

Here is a tutorial on PreparedStatements: http://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html

And here is a reason to use them: How does a PreparedStatement avoid or prevent SQL injection?

Youcef LAIDANI · Accepted Answer · 2017-02-25T14:14:52.610

You can create a Customer object and set your attribute in it like this :

Customer custemer;

if (resultSet.next()) {
   customer = new Customer(resultSet.getInt("customerid"));
}

return custemer;

If you want to get one result you don't need to use while(..) you can make an if instead and your query should have a condition "select customerid from customer where ..." because your query can get multiple results, if you want to get a List you can use while like this :

List<Customer> listCustemer = new ArrayList<>();

while (resultSet.next()) {
   listCustemer.add(new Customer(resultSet.getInt("customerid")));
}

return listCustemer;

EDIT

You can change your constructor and sett your fields you want like name, address and ..., like this : Customer(int id, String name, String address, ...)

So you can use this constructor to create a new Object like so :

listCustemer.add(new Customer(resultSet.getInt("customerid"), 
                 resultSet.getString("name"), resultSet.getString("address"), ...));

Yes you are correct but I have many no of attributes in customer entity so is there any other way or i should go for this? — payal gala, Feb 25 '17 at 10:48
@payalgala you can create a constructor which take multiple values like this `new Customer(par, par2, par2, par2, par2..)` — Youcef LAIDANI, Feb 25 '17 at 10:57
I don't think this answers the question. No mention of getting the other customer fields like name and address. — JamesB, Feb 25 '17 at 14:10

N00b Pr0grammer · Answer 3 · 2017-02-25T10:59:52.373

I don't see anything that's stopping you from fetching those details, what you have to do is to edit your QUERY that you are trying to fetch from the Database.

public class Customer verifyCustomerId(cutomerId){
statement = connection.createStatement();
CustomerPoJo customer;
resultSet = statement.executeQuery("select * from customer");

while (resultSet.next()) {
    if (cutomerId == resultSet.getInt(1)) {

// Fill in the details accordingly
        String customername = resultSet.getString(2);
        String customeraddress = resultSet.getString(3);
        ...

        customer = new CustomerPoJo();
        customer.setCustomerId(customerId);
        customer.setCustomerName(customername);
        customer.setCustomerAddress(customeraddress);
        ...

    }
}
// Instead send your customer object
return customer;
}

Get data from database and return it in form of pojo object

3 Answers3