Retrieving data from a MySQL database and displaying it in html using java

Question

I have a MySQL database and I am trying to search it and display the results in a table with the same columns as the db in HTML on a web page. I am using JAVA for the servlet and I have the web page send the search string using JavaScript to the servlet. I know how to:

Connect to the database
Query db and retrieve appropriate data in a resultset

I am having trouble going from the resultset and sending it back to and displaying it on the web page in a table.

score 1 · Answer 1 · answered Jun 20 '14 at 13:36

1

This is is a pretty good tutorial (if a little old now). It covers getting data from the database, using an MVC type architecture, java beans and JSP with Expression language.

http://pdf.coreservlets.com/

Have a look at chapter 17.

answered Jun 20 '14 at 13:36

Jaydee

4,138
1
19
20

score 0 · Answer 2 · answered Oct 12 '13 at 07:14

Steps
1)suppose you fetched a list from database
2) set this list to http-request attribute .
(use setAttribute function)
3) and get RequestDispatcher from httpRequest.
4) on jsp use following

<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<Table>
<Tr>
     <td>col1</td> 
     <td>col2</td>
</tr>
<c:forEach var="i" items="${listName}">
  <tr>
      <td>
        <c:out value="${i['col1']}"/>
      </td>          
      <td>
        <c:out value="${i['col1']}"/>
      </td>
  <tr>
</c:forEach>
</table>

score 0 · Answer 3 · answered Jan 26 '12 at 19:50

0

You can display your data in HTML table by using ResultSetMetaData. I assumed that 'rs' is the variable for the result set of your query. The following should work for you

int rowCount = 0;
PrintWriter out = response.getWriter();

 out.println("<P ALIGN='center'><TABLE BORDER=1>");
 ResultSetMetaData rsmd = rs.getMetaData();
 int columnCount = rsmd.getColumnCount();
 // table header
 out.println("<TR>");
 for (int i = 0; i < columnCount; i++) {
   out.println("<TH>" + rsmd.getColumnLabel(i + 1) + "</TH>");
   }
 out.println("</TR>");
 // the data
 while (rs.next()) {
  rowCount++;
  out.println("<TR>");
  for (int i = 0; i < columnCount; i++) {
    out.println("<TD>" + rs.getString(i + 1) + "</TD>");
    }
  out.println("</TR>");
  }
 out.println("</TABLE></P>");
 return rowCount;
}

You can check this tutorial for detailed information.

answered Jan 26 '12 at 19:50

Korhan Ozturk

11,148
6
36
49

`ResultSetMetaData` - yes, that is a better way to get the column names rather than hard-coding that in the servlet. Hard-coding is practical only when you want to use different column names in the HTML table than what is in the corresponding database table. – Web User Jan 26 '12 at 19:53
True! Therefore I suggested using `ResultSetMetaData`. Quite good point @WebUser.. – Korhan Ozturk Jan 26 '12 at 19:59
Did you ask anything @ManBearPig? because I saw some comments in my stackexchange inbox but cannot read it here. – Korhan Ozturk Jan 26 '12 at 20:12
Ya i messed up the comment and retyped it. I am new to using JAVA and servlets so this my be a stupid question but does that code go in the servlet method the JavaScript is calling? I am calling the method on the web page like: 'Servlet.search(searchStr)' so that code would be in the 'search' method? I guess I am confused beause I see java and html together in them out.println() I am confused as to where on the page will it be calling the table? – Jan 26 '12 at 20:14
Although I don't know what's inside your 'search(searchStr)' method, I think you can place the code above anywhere you want since it's going to print the values of your result set into an HTML table. – Korhan Ozturk Jan 26 '12 at 20:18
I guess I am confused because I see java and html together in them out.println() lines I am confused as to where on the page will it be placing the table? I have my java code separate from the web sites JavaScript and HTML in a separate .java file. – Jan 26 '12 at 20:24

score 0 · Answer 4 · answered Jan 26 '12 at 19:51

Are you saying you are unable to iterate through the ResultSet object and generate the HTML in the servlet?

if (rs.next()) {

    out.print("<table><tr><th>column1</th><th>column2</th>...</tr>");

    do {

        out.print("<tr>");
        out.print(new StringBuilder("<td>").append(rs.getObject("column1"))append("</td>").toString());
        out.print(new StringBuilder("<td>").append(rs.getObject("column2"))append("</td>").toString());
        ...
        out.print("</tr>");
    } while (rs.next());

    out.print("</table>");
}

Retrieving data from a MySQL database and displaying it in html using java

4 Answers4