How to retrieve SQL records dynamically in JSP using servlet in Struts

Question

Currently i am retrieving sql records from jsp itself, for example when i want to view details of a specific person i will enter his id in a text box in jsp and record will be searched and displayed in the jsp itself. But what i want actually is, i want to search the record in servlet and display them through jsp. Moreover i am using struts framework for this application.

Below code is the controller and i retrieved all records in controller and stored them in a Collection object.

String driverName = "com.mysql.jdbc.Driver";  
String connectionUrl = "jdbc:mysql://235.4.23.34:3306/";
String dbName = "abc";
String userId = "root";
String password = "ipser"; 
Connection con = null;
Statement  stmt = con.createStatement(); 
ResultSet rs;

Class.forName(driverName);
con = DriverManager.getConnection(connectionUrl+dbName, userId, password); 

String s=" select * from contacts";
rs=stmt.executeQuery(s); 
while(rs.next()){ 

List agentList  = new ArrayList(); 

agentList.add(rs.getString("CUSTOMER_ID"));  
agentList.add(rs.getString("VENDOR"));  
agentList.add(rs.getString("sector"));  
agentList.add(rs.getString("AMOUNT"));  
agentList.add(rs.getString("total_AMOUNT"));  
agentList.add(rs.getString("STATUS"));  
agentList.add(rs.getString("delivery_STATUS")); 
}
request.setAttribute("data", agentList);

RequestDispatcher dispatcher = request.getRequestDispatcher("success.jsp");  
if (dispatcher != null){  
       dispatcher.forward(request, response);  
}

Then i passed the collection object to success.jsp and iterating the collection object using iterator as below

success.jsp:


      <% List mydata= (List)request.getAttribute("data");

        Iterator itr = mydata.iterator();
        while (itr.hasNext()) {
          Object element = itr.next();%> 

                  
      <%}%> 
     
      

   CUSTOMER_ID
   VENDOR
   sector
   AMOUNT
   total_AMOUNT
   STATUS
   delivery_STATUS
      
    
            <%=element%>

The problem is for single record this works fine. but for more than one record all the records are displayed in one row itself. I want to display seven fields of a record in one row only.Can somebody provide me solution?

Parth · Accepted Answer

Here's what you can do. Instead of adding the resultset in a list, create a POJO class and make a list of that and then iterate over that list instead, creating a new tag for every item in the list.

Here is what your Agent.java may look like

public class Agent {

    private String customerId;
    private String vendor;
    private String sector;
    private String amount;
    private String totalAmount;
    private String status;
    private String deliveryStatus;

    public String getCustomerId() {

        return customerId;
    }

    public void setCustomerId(String customerId) {

        this.customerId = customerId;
    }

    public String getVendor() {

        return vendor;
    }

    public void setVendor(String vendor) {

        this.vendor = vendor;
    }

    public String getSector() {

        return sector;
    }

    public void setSector(String sector) {

        this.sector = sector;
    }

    public String getAmount() {

        return amount;
    }

    public void setAmount(String amount) {

        this.amount = amount;
    }

    public String getTotalAmount() {

        return totalAmount;
    }

    public void setTotalAmount(String totalAmount) {

        this.totalAmount = totalAmount;
    }

    public String getStatus() {

        return status;
    }

    public void setStatus(String status) {

        this.status = status;
    }

    public String getDeliveryStatus() {

        return deliveryStatus;
    }

    public void setDeliveryStatus(String deliveryStatus) {

        this.deliveryStatus = deliveryStatus;
    }
}

Once you have this class then you need to create List and all the items in that list

List agentList = new ArrayList();

// for every row add the attributes to agent object and then add the agent object to list

for(looping condition) {

    Agent currentAgent = new Agent();
    currentAgent.setStatus(rs.getString("status"));
    ...
    ...
    ...

    agentList.add(currentAgent);
}

Now that you have that list you can modify your jsp code to do something like this.

<% List mydata= (List)request.getAttribute("data");

Iterator itr = mydata.iterator();
while (itr.hasNext()) {
    Agent currentAgent = itr.next();%> 
        
             <%=currentAgent.status%>
             <%=currentAgent.customerId%>
            ...
            ...
            ...
        
<%}%>

This should show your data into multiple rows.

How to retrieve SQL records dynamically in JSP using servlet in Struts

Answers (2)

Related Questions