Reading data from the database and saving it in the "List of arrays" object

Question

Hi everyone, I want to display all the content of my database table in an html page. I try to first retrieve the record from the database and save it in an ArrayList , but when I return the list of arrays on the html page, it only displays the last record again, since it is an account of my database table. Here is the code below:

 public ArrayList<CustomerDTO> getAllCustomers() { ArrayList<CustomerDTO> customers = new ArrayList<CustomerDTO>(); CustomerDTO customer = null; Connection c; try { c = openConnection(); Statement statement = c.createStatement(); String s = "SELECT * FROM customer"; ResultSet rs = statement.executeQuery(s); int g =0; while (rs.next()) { customer.setId(rs.getInt("id")); customer.setName(rs.getString("name")); customer.setAddress(rs.getString("address")); customer.setPhone(rs.getString("phone")); customer.setEmail(rs.getString("email")); customer.setBountPoints(rs.getInt("bonuspoint")); customer.setTotalsale(rs.getInt("totalsale")); customers.add(customer); } rs.close(); } catch (Exception e) { System.out.println(e); } return customers; } 

Answer 1

Try using the following code

 public static ArrayList<Customer> getAllCustomer() throws ClassNotFoundException, SQLException { Connection conn=DBConnection.getDBConnection().getConnection(); Statement stm; stm = conn.createStatement(); String sql = "Select * From Customer"; ResultSet rst; rst = stm.executeQuery(sql); ArrayList<Customer> customerList = new ArrayList<>(); while (rst.next()) { Customer customer = new Customer(rst.getString("id"), rst.getString("name"), rst.getString("address"), rst.getDouble("salary")); customerList.add(customer); } return customerList; } 

this is my model class

 public class Customer { private String id; private String name; private String salary; private String address; public String getId() { return id; } public void setId(String id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getSalary() { return salary; } public void setSalary(String salary) { this.salary = salary; } public String getAddress() { return address; } public void setAddress(String address) { this.address = address; } } 

this is my view method

  private void reloadButtonActionPerformed(java.awt.event.ActionEvent evt) { try { ArrayList<Customer> customerList = null; try { try { customerList = CustomerController.getAllCustomer(); } catch (SQLException ex) { Logger.getLogger(veiwCustomerFrame.class.getName()).log(Level.SEVERE, null, ex); } } catch (Exception ex) { Logger.getLogger(ViewCustomerForm.class.getName()).log(Level.SEVERE, null, ex); } DefaultTableModel tableModel = (DefaultTableModel) customerTable.getModel(); tableModel.setRowCount(0); for (Customer customer : customerList) { Object rowData[] = {customer.getId(), customer.getName(), customer.getAddress(), customer.getSalary()}; tableModel.addRow(rowData); } } catch (Exception ex) { Logger.getLogger(ViewCustomerForm.class.getName()).log(Level.SEVERE, null, ex); } } 

Answer 2

You need to create a new client object at each iteration, and then add this newly created object to the ArrayList at the stage of your iteration.

Answer 3

Try creating a new client instance every time, for example.

  while (rs.next()) { Customer customer = new Customer(); customer.setId(rs.getInt("id")); customer.setName(rs.getString("name")); customer.setAddress(rs.getString("address")); customer.setPhone(rs.getString("phone")); customer.setEmail(rs.getString("email")); customer.setBountPoints(rs.getInt("bonuspoint")); customer.setTotalsale(rs.getInt("totalsale")); customers.add(customer); } 

Answer 4

You are reusing the customer link. Java works by reference for obeycts. Not for primitives.

What you do adds the same customer to the list, and then modifies it. Thus, the same values ​​are set for all objects. That is why you see the last. Because everyone is the same.

  while (rs.next()) { Customer customer = new Customer(); customer.setId(rs.getInt("id")); ... 

Answer 5

I try to retrieve the record from the database first and save it in an ArrayList but when I return the list of arrays on the html page, it only displays the last record several times as a count of my database table

This part was mostly covered by all the previous answers. Therefore, you need to create a new instance of your CustomerDTO in your while and add it to your ArrayList .

There is one more thing I wanted to comment on:

• Make sure you release all of your resources after you have finished using them. From the code you posted, you did not close the Statement or Connection objects (not so sure that you are joining your connection, in this case you will need to disconnect this connection from the pool)

So, when you consider these points, the structure of your code may look something like this:

 public ArrayList<CustomerDTO> getAllCustomers() { ArrayList<CustomerDTO> customers = new ArrayList<CustomerDTO>(); Connection c = null; Statement statement = null; ResultSet rs = null; try { c = openConnection(); statement = c.createStatement(); String s = "SELECT * FROM customer"; rs = statement.executeQuery(s); int g =0; while (rs.next()) { CustomerDTO customer = new CustomerDTO(); //Code to fill up your DTO customers.add(customer); } } catch (Exception e) { System.out.println(e); }finally{ //Code to release your resources } return customers; } 

Answer 6

If your client class has static variables, delete them so your class looks something like this.

 public class customer { private int id; private String name; private String DOB; public int getId() { return id; } public String getName() { return name; } public String getDOB() { return DOB; } public void setId(int id) { this.id = id; } public void setName(String name) { this.name = name; } public void setDOB(String dOB) { this.DOB = dOB; } 

instead of something like

 public class customer { private static int id; private static String name; private static String DOB; public static int getId() { return id; } public static String getName() { return name; } public static String getDOB() { return DOB; } public static void setId(int id) { custumer.id = id; } public static void setName(String name) { customer.name = name; } public static void setDOB(String dOB) { customer.DOB = dOB; } 

Answer 7

Instead of null , use CustomerDTO customers = new CustomerDTO () `;

 CustomerDTO customer = null; private static List<Author> getAllAuthors() { initConnection(); List<Author> authors = new ArrayList<Author>(); Author author = new Author(); try { stmt = (Statement) conn.createStatement(); String str = "SELECT * FROM author"; rs = (ResultSet) stmt.executeQuery(str); while (rs.next()) { int id = rs.getInt("nAuthorId"); String name = rs.getString("cAuthorName"); author.setnAuthorId(id); author.setcAuthorName(name); authors.add(author); System.out.println(author.getnAuthorId() + " - " + author.getcAuthorName()); } rs.close(); closeConnection(); } catch (Exception e) { System.out.println(e); } return authors; } 

Answer 8

  while (rs.next()) { customer.setId(rs.getInt("id")); customer.setName(rs.getString("name")); customer.setAddress(rs.getString("address")); customer.setPhone(rs.getString("phone")); customer.setEmail(rs.getString("email")); customer.setBountPoints(rs.getInt("bonuspoint")); customer.setTotalsale(rs.getInt("totalsale")); customers.add(customer); customer = null; } 

Try replacing the while loop code with the above code. Here's what we did by doing customers.add(customer) , we do customer = null; `

Answer 9

Create a CustomerDTO object each time inside the while loop

Check below code

  while (rs.next()) { Customer customer = new Customer(); customer.setId(rs.getInt("id")); customer.setName(rs.getString("name")); customer.setAddress(rs.getString("address")); customer.setPhone(rs.getString("phone")); customer.setEmail(rs.getString("email")); customer.setBountPoints(rs.getInt("bonuspoint")); customer.setTotalsale(rs.getInt("totalsale")); customers.add(customer); }