[java] Reading Data From Database and storing in Array List object

Hello all i want to display entire content of my database table on html page.I am trying to fetch record from database first and store in ArrayList but when i return array list on html page it display only last record repeatedly as count 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;
}

This question is related to java jdbc

The answer is


Try with 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);
    }

} 

Instead ofnull, 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;
}

Create CustomerDTO Object every time inside while loop

Check the 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);
}

You have to create a new customer object in every iteration and then add that newly created object into the ArrayList at the lase of your iteration.


Try creating new instance of customer every time e.g.

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


    }

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

What you are doing is adding to the list the same customer and then modifying it. Thus setting the same values for all of objects. That's why you see the last. Because all are the same.

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

        ...

Also If you want you result set data in list .please use below LOC:

public List<String> dbselect(String query)
  {
      List<String> dbdata=new ArrayList<String>();
      try {
        dbResult=statement.executeQuery(query);
        ResultSetMetaData metadata=dbResult.getMetaData();
        for(int i=0;i>=metadata.getColumnCount();i++)
        {
            dbdata.add(dbResult.getString(i));
        }
        return dbdata;
    } catch (SQLException e) {
        return null;
    }
      
      
  }

I am trying to fetch record from database first and store in ArrayList but when i return array list on html page it display only last record repeatedly as count of my database table

This part has mostly been covered by all the previous answers. So you would need to create a new instance of your CustomerDTO within your while loop and add it to your ArrayList.

There's one more thing that I wanted to comment about:

  • Make sure that you release all your resources after you're done using them. From the code that you've posted, you've not closed your Statement or your Connection objects (not so sure if you're pooling your connection, in that case you would need to release this connection to the pool)

So, when you consider these points, the structure of your code might 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;
}

 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 your while loop code with above mentioned code. Here what we have done is after doing customers.add(customer) we are doing customer = null;`


try this

import java.sql.ResultSet;
import java.util.ArrayList;

import com.rcb.dbconnection.DbConnection;
import com.rcb.model.Docter;


public class DocterService {



public ArrayList<Docter> getAllDocters() {
    ArrayList<Docter> docters = new ArrayList<Docter>();

    try {
        String sql = "SELECT tbl_docters";

        ResultSet rs = db.getData(sql);
        while (rs.next()) {
            Docter docter = new Docter();

            docter.setD_id(rs.getInt("d_id"));
            docter.setD_FName(rs.getString("d_fname"));
            docter.setD_LName(rs.getString("d_lname"));

            docters.add(docter);

        }

    } catch (Exception e) {
        System.out.println("getAllDocters()");
        e.printStackTrace();
    }

    return (docters);
}

public static void main(String args[]) {
    DocterService ds = new DocterService();
    ArrayList<Docter> doctersList = ds.getAllDocters();
    String s[] = null;
    for (int i = 0; i < doctersList.size(); i++) {

        System.out.println(doctersList.get(i).getD_id());
        System.out.println(doctersList.get(i).getD_FName());
    }

  }
}

If your customer class has static variables remove them so your class should look 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;
    }