Reading Data From Database and storing in Array List object

Question

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 lt CustomerDTO gt  getAllCustomers          ArrayList lt CustomerDTO gt  customers   new ArrayList lt CustomerDTO gt         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

User · Answer

Instead ofnull   use CustomerDTO customers  new CustomerDTO      CustomerDTO customer   null      private static List lt Author gt  getAllAuthors         initConnection        List lt Author gt  authors   new ArrayList lt Author gt         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

User · Answer

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 lt CustomerDTO gt  getAllCustomers          ArrayList lt CustomerDTO gt  customers   new ArrayList lt CustomerDTO gt         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

User · Answer

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

User · Answer

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

User · Answer

Also If you want you result set data in list  please use below LOC  public List lt String gt  dbselect String query            List lt String gt  dbdata new ArrayList lt String gt           try           dbResult statement executeQuery query           ResultSetMetaData metadata dbResult getMetaData            for int i 0 i gt  metadata getColumnCount   i                          dbdata add dbResult getString i                      return dbdata        catch  SQLException e            return null

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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 lt Docter gt  getAllDocters         ArrayList lt Docter gt  docters   new ArrayList lt Docter gt          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 lt Docter gt  doctersList   ds getAllDocters        String s     null      for  int i   0  i  lt  doctersList size    i               System out println doctersList get i  getD id             System out println doctersList get i  getD FName

User · Answer

Create CustomerDTO Object every time inside while loop Check the below code      while  rs next           Customer customer   new Customer         customer setId rs getInt  quot id quot         customer setName rs getString  quot name quot         customer setAddress rs getString  quot address quot         customer setPhone rs getString  quot phone quot         customer setEmail rs getString  quot email quot         customer setBountPoints rs getInt  quot bonuspoint quot         customer setTotalsale rs getInt  quot totalsale quot          customers add customer

User · Answer

Try with the following code  public static ArrayList lt Customer gt  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 lt Customer gt  customerList   new ArrayList lt  gt         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 lt Customer gt  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

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

Examples related to java

Examples related to jdbc