CODEWITHSUNDEEP
javadatabaselistdto
user1759247
user1759247

Reputation: 101

Creating a list with database values and store in DTO

I have a my main class, a DTO, and a DAO. what I want to do is to read the database table CUSTOMER(two fields NAME, SURNAME) then write it to a txt file. I cant seem to get it to store the values in the DTO so that my main class can get it.

My DTO exist of two fields, NAME and SURNAME with the getters and setters. The problem lies in my DAO with the List

*please keep in mind I'm a student.

This is what I have done so far: result, writes to file like this: [name surname, name surname, name surname,] I need it to write to the file pipe deliminated " | "

public CustomerDTO getDetails(Connection conn) throws SQLException {

    CustomerDTO customerDTO = new CustomerDTO();
    ResultSet rs = null;
    PreparedStatement pstmnt = null;

    try {
        // SELECT NAME, SURNAME FROM CUSTOMER
        StringBuffer stringBuffer = new StringBuffer("SELECT " + DBConstants.CUST_NAME + ", ");
        stringBuffer.append(DBConstants.CUST_SURNAME);
        stringBuffer.append(" FROM " + "CUSTOMER");
        //build string
        String query = stringBuffer.toString();
        //prepared statement
        pstmnt = conn.prepareStatement(query);
        //execute preparestatement
        rs = pstmnt.executeQuery();
        //keep reading next line in table - .next() method means next line
        List myList = new ArrayList();

        while (rs.next()) {
            String ss = rs.getString("NAME");
            String sss = rs.getString("SURNAME");
            myList.add(ss + " " + sss);
            customerDTO.setName(myList.toString());
            customerDTO.setSurname(myList.toString());
        }
    } catch (Exception e) {
        e.getMessage();
    }
    rs.close();
    //return DTO with details.
    return customerDTO;
}

Upvotes: 2

Views: 7852

Answers (3)

Thobias Bergqvist
Thobias Bergqvist

Reputation: 111

here's a complete example:

public void caller() {
    Connection conn = null;
    // TODO: setup connection
    List list = getDetails(conn);
    for (int i=0; i<list.size(); i++) {
      CustomerDTO dto = list.get(i);
      write(dto.getName(), dto.getSurname());
    }
}

public List getDetails(Connection conn) throws SQLException {

    List myList = new ArrayList();
    ResultSet rs = null;
    PreparedStatement pstmnt = null;

    try {
        // SELECT NAME, SURNAME FROM CUSTOMER
        StringBuffer stringBuffer = new StringBuffer("SELECT " + DBConstants.CUST_NAME     + ", ");
        stringBuffer.append(DBConstants.CUST_SURNAME);
        stringBuffer.append(" FROM " + "CUSTOMER");
        //build string
        String query = stringBuffer.toString();
        //prepared statement
        pstmnt = conn.prepareStatement(query);
        //execute preparestatement
        rs = pstmnt.executeQuery();
        //keep reading next line in table - .next() method means next line

        while (rs.next()) {
            String ss = rs.getString("NAME");
            String sss = rs.getString("SURNAME");
            CustomerDTO customerDTO = new CustomerDTO();
            customerDTO.setName(ss);
            customerDTO.setSurname(sss);
            myList.add(customerDTO);
        }
    } catch (Exception e) {
        e.getMessage();
    }
    rs.close();
    //return list of DTOs.
    return myList;
}

I leave the implementation of the write method to you. Also. consider using Generics if your java allows it.

Upvotes: 0

JB Nizet
JB Nizet

Reputation: 692081

The method should be declared as 

public List<CustomerDTO> getDetails(Connection conn) throws SQLException {
    ...
}

since its goal is to return a list of customers (one per row in the table).

Inside the method, you should have the following:

// create the result list, initially empty
List<CustomerDTO> result = new ArrayList<CustomerDTO>();

// iterate through the rows
while (rs.next()) {

    // TODO: create a new CustomerDTO, and populate it with the row's data
    // TODO: add this DTO to the result list
}

return result;

Then, the caller of this method will iterate through the list of CustomerDTOs, and write them to a file. Each method has its responsibility: the DAO handles the database retrieval, but doesn't handle the file IO.

Upvotes: 2

9ee1
9ee1

Reputation: 1078

I am not sure where or how you are writing this file is but in tracing the code, here is what I think might be wrong.

In this WHILE LOOP:

while (rs.next()) {
    String ss = rs.getString("NAME");
    String sss = rs.getString("SURNAME");

    // You'r adding an element here that would have a value like this: "John Doe"
    myList.add(ss + " " + sss);


    // Single instance of a DTO whose value. State values will always be replaced with the
    // string represenation of the myList variable.
    //
    // Both properties will always contain the same value no matter what.
    customerDTO.setName(myList.toString());
    customerDTO.setSurname(myList.toString());
}

I think what you should really be doing is something like this:

// Notice the use of Generics (Look it up). It is more compile time safe and better practice.
ArrayList<CustomerDTO> customerDTOS = new ArrayList<CustomerDTO>();
while (rs.next()) {
    String ss = rs.getString("NAME");
    String sss = rs.getString("SURNAME");

    CustomerDTO customerDTO = new CustomerDTO();
    customerDTO.setName(ss);
    customerDTO.setSurName(sss);

    customerDTOS.Add(customerDTO);
}

return customerDTOS;

In short, your method should return a list of CustomerDTOs and then you can use them to write to your file.

Good Luck.

Upvotes: 3

Related Questions