Java Servlet - Showing Column Names

Question

I've been working on a Java Servlet which selects all the data from a table input by the user in index.html. However I've encountered a problem, because I have a DB with numerous different IDs and thus I would like to also show the column names in a table - right now it just shows the data without any column names and I've been seemingly unable to find a solution or come up with one on my own

start.java:

package start;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.sql.SQLException;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

/**
 * Servlet implementation class Start
 */
@WebServlet("/Start")
public class Start extends HttpServlet {
    private static final long serialVersionUID = 1L;
    public static void getColumnNames(ResultSet rs) throws SQLException {
        if (rs == null) {
          return;
        }
        ResultSetMetaData rsMetaData = rs.getMetaData();
        int numberOfColumns = rsMetaData.getColumnCount();


        for (int i = 1; i < numberOfColumns + 1; i++) {
          String columnName = rsMetaData.getColumnName(i);
          String tableName = rsMetaData.getTableName(i);
          System.out.println("column name=" + columnName + " table=" +     tableName);
    }
  }

/**
 * @see HttpServlet#HttpServlet()
 */
public Start() {
    super();
    // TODO Auto-generated constructor stub
}

/**
 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse
 *      response)
 */
protected void doGet(HttpServletRequest request, HttpServletResponse respond) throws ServletException, IOException {
    PrintWriter pw = respond.getWriter();
    respond.setContentType("text/html");
    String tb = request.getParameter("table");
    try {

        Class.forName("com.mysql.jdbc.Driver").newInstance();
        Connection con = DriverManager.getConnection("jdbc:mysql://localhost/music","root","1234");
        Statement st = con.createStatement();
        System.out.println("connection established successfully!");
        ResultSet rs = st.executeQuery("SELECT * FROM " + tb);

        pw.println("<table border=1>");
        while (rs.next()) {
            pw.println("<tr><td>" + rs.getInt(1) + "</td>" + "<td>" + rs.getString(2) + "</td>" + "<td>" + rs.getString(3)
                    + "</td></tr>");
        }
        pw.println("</table>");
        pw.close();
    } catch (Exception e) {
        e.printStackTrace();
    }
    // TODO Auto-generated method stub
    // response.getWriter().append("Served at:
    // ").append(request.getContextPath());
}

/**
 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse
 *      response)
 */
protected void doPost(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {
    // TODO Auto-generated method stub
    doGet(request, response);
    }
}

This is what a result looks like: Any help would be greatly appreciated!

You seem to be getting the column names from the `ResultSetMetaData`. Can you explain what exactly is the problem with that code? — Mureinik, May 28 '16 at 15:59
It does not display any of the column names - just the data retrieved by the query, I'll update the main post with the result of a query. — Seteris, May 28 '16 at 16:01

Yassin Hajaj · Accepted Answer · 2016-05-28T16:20:08.013

1

You should use ResultSetMetaData to retrieve the meta data from your result. You'll retrieve the column names using ResultSetMetaData#getColumnName(int)

ResultSetMetaData rsmd = rs.getMetaData();
pw.println("<table border=1>");
pw.println("<tr>");
for (int i = 1 ; i <= rsmd.getColumnCount() ; i++) {
    pw.println("<th>" + rsmd.getColumnName(i) + "</th>");
}
pw.println("</tr>");
while (rs.next()) {
    pw.println("<tr><td>" + rs.getInt(1) + "</td>" + "<td>" + rs.getString(2) + "</td>" + "<td>" + rs.getString(3)
            + "</td></tr>");
}
pw.println("</table>");

edited May 28 '16 at 16:20

answered May 28 '16 at 16:09

Yassin Hajaj

21,337
9
51
89

This worked perfectly fine, Thank you very much! (However there is a missing n in rsmd.getColumnName) – Seteris May 28 '16 at 16:19
@Seteris You're welcome; Thanks I edited it. Do not forget to accept/upvote to confirm the question is answered. :) – Yassin Hajaj May 28 '16 at 16:20

score 0 · Answer 2 · answered May 28 '16 at 16:20

Try this for get table column :

String dbname="test";
String tablename="emp"; 
Statement st=mySqlCon.createStatement();
 int i=st.executeUpdate("use "+dbname);
 ResultSet clmnRs=st.executeQuery("desc "+tablename);
 while(clmnRs.next())
{
 System.out.println(clmnRs.getString(1));
}

Java Servlet - Showing Column Names

2 Answers2