Displaying mysql table in a jsp

Question

This is my first time asking so im sorry in advance if i messed up.Ok so here it goes

i have this mysql table that i want to display in a jsp page and i actually got it to work but its not really a good approach since all the code(connecting to the database etc) is in the JSP.So i made a java bean that controls the connecting to database and executing sql query part , and a separate JSP page where i can display the table.

main.jsp

<%@ page import="java.sql.*" %>
<?xml version='1.0' encoding='UTF-8' ?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml"
      xmlns:h="http://java.sun.com/jsf/html">


<head>
<title>Welcome to Car Rental System</title>
<link rel="stylesheet" href="//code.jquery.com/ui/1.11.1/themes/smoothness/jquery-ui.css"></link>
<script src="//ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script>
<script src="//code.jquery.com/ui/1.11.1/jquery-ui.js"></script>
<script src="jquery.backstretch.min.js"></script>
<link rel="stylesheet" href="style.css"></link>
<link href="https://fonts.googleapis.com/css?family=Source+Sans+Pro" rel="stylesheet"> 



</head>

    <body>

        <p><div><center><h1><b>Car Rental System</b></h1></center></div></p>  

         <% 

<jsp:useBean id="displayCarList" class="displayCarList.displayCarList" >
<jsp:setProperty name="displayCarList" property="*"/>
</jsp:useBean>        
     <%
     String carbookId = request.getParameter("carbookId");
     String manufacturername = request.getParameter("manufacturername");
     String modelname = request.getParameter("modelname");
     String price_day = request.getParameter("price_day");
     %>

     <table border='3' align='center'>
         <tr>
         <td><b>Car ID</td>
         <td><b>Manufacturer</td>
         <td><b>Model name</td>
         <td><b>Price per day</td>
         </tr>



<tr>
<td><%=displayCarList.getCarbookid()%></td>
<td><%=displayCarList.getManufacturername()%></td>     
<td><%=displayCarList.getModelname()%></td>
<td><%=displayCarList.getPriceday()%></td>
</tr> 
</table>
<br></br>

<form action=secondpage.jsp method="post">
<fieldset><legend>Fill in the form below :</legend>

<p><b>Name:</b> <input type="text" name="name" size="20" maxlength="40" /></p>
<p><b>Telephone number</b> <input type="text" name="phonenumber" th:field="*{phonenumber}" size="20" maxlength="40" /></p>
<p><b>Rental date:</b> <input type="date" th:field="*{rentaldate}"  name="rentaldate"></p>
<p><b>Return date:</b> <input type="date" th:field="*{returndate}" name="returndate"></p>
<p><b>Car ID:</b> <input type="text" name="carID" th:field="*{carID}" size="20" maxlength="40" /></p>

<div align="left"><input type="submit" name="submit" value="Submit " /></div>

</form>
     <script>

$.backstretch("background2.jpg");
</script>
    </body>
</html>

The following is the java bean, displayCarList.java

package displayCarList;

import java.io.*;
import static java.lang.System.out;
import java.util.*;
import javax.sql.*;
import javax.servlet.*;
import javax.servlet.http.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;


public class displayCarList {

  private String carbookId, manufacturername, modelname, price_day;

  Connection con = null;  
  Statement stmt = null;
  ResultSet rs = null;

    public displayCarList(){

  try {
      try {
          Class.forName("com.mysql.jdbc.Driver");
      } catch (ClassNotFoundException ex) {
          Logger.getLogger(displayCarList.class.getName()).log(Level.SEVERE, null, ex);
      }
  con =DriverManager.getConnection ("jdbc:mysql://localhost:3306/rentalcar","root","");
  stmt = con.createStatement();
  rs = stmt.executeQuery("SELECT * FROM carlist");
  while(rs.next()){
       carbookId = rs.getString(1);
       manufacturername= rs.getString(2);
        modelname = rs.getString(3);
        price_day = rs.getString(4);
        }
stmt.close();
con.close();
  // displaying record
  } catch (SQLException e) {
 System.out.println("couldnotconnecYO");
    }
    }
public String getCarbookid(){
    return carbookId;
}

public void setCarbookid(){
    this.carbookId = carbookId;
}

public String getManufacturername(){
    return manufacturername;
}
public void setManufacturername(){
    this.manufacturername = manufacturername;
}

public String getModelname(){
    return modelname;
}

public void setModelname(){
    this.modelname = modelname;
}
public String getPriceday(){
    return price_day;
}
public void setPriceday(){
    this.price_day = price_day;
}
}

But for some reason, only one record is displayed which is the last one , where it shouldve returned everything in the table(supposedly)

enter image description here

So i would appreciate if you guys could help me and point out what i did wrong , and help me get all the records in the table displayed like this one

enter image description here

thanks again

UPDATE

As per the instructions by Shuddh i made a few changes to the codes and yet it still doesnt work for some bloody reason

public ArrayList displayCarList(){

 try {
  try {
      Class.forName("com.mysql.jdbc.Driver");
  } catch (ClassNotFoundException ex) {
      Logger.getLogger(displayCarList.class.getName()).log(Level.SEVERE, null, ex);
  }
  con =DriverManager.getConnection ("jdbc:mysql://localhost:3306/rentalcar","root","");
  stmt = con.createStatement();
  rs = stmt.executeQuery("SELECT * FROM carlist");
  while(rs.next()){
     int i = 1;
     while(i <= 12){
         carlist.add(rs.getString(i++));
     }  
  }
  stmt.close();
  con.close();
  // displaying record
  } catch (SQLException e) {
       System.out.println("couldnotconnecYO");
  }
 return carlist;
        }

its now returning null in every colum

Shuddh · Answer 1 · 2017-05-30T09:09:39.167

The reason you are getting just last row is because you are not storing the other rows anywhere. So it overwrites the variables and this last row is being captured. Store the local variable as list and that will do the trick.

Create a List and add every resultset into that

try {
  try {
      Class.forName("com.mysql.jdbc.Driver");
  } catch (ClassNotFoundException ex) {
      Logger.getLogger(displayCarList.class.getName()).log(Level.SEVERE, null, ex);
  }
  con =DriverManager.getConnection ("jdbc:mysql://localhost:3306/rentalcar","root","");
  stmt = con.createStatement();
  rs = stmt.executeQuery("SELECT * FROM carlist");
  while(rs.next()){
     carbookId = rs.getString(1);
     manufacturername= rs.getString(2);
     modelname = rs.getString(3);
     price_day = rs.getString(4);
     // add to list here.
  }
  stmt.close();
  con.close();
  // displaying record
  } catch (SQLException e) {
       System.out.println("couldnotconnecYO");
  }
return List. // If you have no idea about list in java google it and learn how to use it

Write your above code in a method and not in constructor.

im at my wits end tbh im so sorry but can you give me some example as to how i achieve that — mimo, May 30 '17 at 04:33

Displaying mysql table in a jsp

1 Answers1