使用数据库值创建列表并存储在DTO中

Question

我有一个主类，一个DTO和一个DAO。我想做的是读取数据库表 CUSTOMER（两个字段名称，姓氏），然后将其写入 txt 文件。我似乎无法让它将值存储在 DTO 中，以便我的主类可以获取它。

我的DTO存在两个字段，名和姓，getters和setters。问题出在我的刀与列表上

*请记住我是一名学生。

这是我到目前为止所做的：结果，像这样写入文件：[姓名姓氏，姓名姓氏，]我需要它写入分隔的文件管道“|”

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;
}

Answer 1

这里有一个完整的例子:

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;
}

我把写方法的实现留给你。也。如果您的java允许，请考虑使用泛型。

Answer 2

该方法应声明为

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

因为它的目标是返回一个客户列表(表中每行一个)。

在方法内部，您应该具有以下内容：

// 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;

然后，此方法的调用方将遍历CustomerDTO列表，并将其写入文件。每个方法都有自己的责任：DAO处理数据库检索，但不处理文件IO。

Answer 3

我不确定您在哪里或如何编写此文件，但在跟踪代码时，我认为可能是错误的。

在这个WHILE循环中：

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());
}

我认为你真正应该做的是这样的事情:

// 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;

简而言之，您的方法应返回 CustomerDTO 的列表，然后可以使用它们写入文件。

祝你好运。