How do I make stuff show on a web page?

[Annette L'Heureux]

Sorry if this sounds very basic, but I'm writting a servlet that will connect to a database and show results of queries on a web page.
What is the code to display the result set? Do I need to have a java server page that calls the servlet?
Annette

[William Brogden]

You can do everything from within a servlet. Don't try to get fancy. If you have not done any database work before, I suggest you try to get the query and response handling working with a regular Java application first, then move to servlets.
Output of results is just printing to an output stream, but the composition of the page will have to use HTML of course.
Bill

------------------
author of:

• Java 2 Exam Prep
  
• Java 2 (Exam Cram)
  
• Java Developer's Guide to Servlets and JSP
  
• Java Developer's Guide to E-Commerce with XML and JS
  

[Thomas Paul]

This code will print out all the rows in your ResultSet with column headings in a nice grid.

        
        /* result is the ResultSet object */
        ResultSetMetaData rsmd = null;
        int numOfCols = 0;
        
        try {
            rsmd = result.getMetaData();
            numOfCols = rsmd.getColumnCount();            
        } catch (SQLException e) {}
        
        out.println("<table border=2>");
        
        try {
            out.println("<tr>");
            for (int i=1; i<= numOfCols; i++) {
                out.println("<td><b>" + rsmd.getColumnName(i) + "</b></td>");
            }
            out.println("</tr>");
        } catch (SQLException e) {}
        
        try {
            while (result.next()) {
                out.println("<tr>");
                for (int i=1; i<= numOfCols; i++) {
                    out.println("<td>" + result.getString(i) + "</td>");
                }
                out.println("</tr>");
            }
        } catch (SQLException e) {}
        out.println("</table>");

[This message has been edited by Thomas Paul (edited December 07, 2000).]

[Annette L'Heureux]

Is it necessary to use metadata if I will only be dealing with one database? or is this the best way to do it.
Annette

[Thomas Paul]

The ResultSetMetaData object gives you information about the ResultSet. In the example, it tells us how many columns are in the ResultSet and the names of the columns. Therefore, the example above is actually reusable since it can be used with any ResultSet.

[Annette L'Heureux]

Thanks! I'll try it and let you know.
Annette

[Annette L'Heureux]

Ok, I've tried it and I need to declare out (I'm assuming it's the PrintWriter). How do I do that? The only code I've seen with that in it has a doGet method, and everything is done in that method. Does this sound familiar?
annette

[Bosun Bello]

Annette, Yes, out could be printWriter as in...
PrintWriter out = response.getWriter()
response is the servlet's response. It is used to return data to the client.
Bosun

[Thomas Paul]

Correct. The code above would be in a service(), doGet(), or doPost() method. The "out" represents the response.getWriter() just as Bosum said.
You could take the above code and move it to a separate class and have it return a String or StringBuffer instead of writing to out. I will leave that as an excercise for anyone interested.
i.e. create a method with a signature of:
public static StringBuffer buildResultGrid(ResultSet rs)

[Annette L'Heureux]

ok, this is starting to make my brain hurt! Doesn't anyone have a simple example of a servlet that accesses a database and displays the results of a simple sql statement (select * from <table> ) on a web page?
I don't imagine it should be that complicated, but when you've never done it before and the only resources you have are a couple of books and this website, it can be pretty overwhelming (especially when your boss would like to see something soon!).
Anyway, one day I'll get there. So basically I call doGet() from main() which is supposed to display the results of my query?

[Thomas Paul]

import javax.servlet.*;
import javax.servlet.http.*;
import java.io.*;
public class ServletTest extends javax.servlet.http.HttpServlet {
    public void service(HttpServletRequest req, HttpServletResponse res)
    throws ServletException, IOException {
        //get Output Stream
        PrintWriter out = res.getWriter();
        //set content type for writing the output
        res.setContentType("text/html");
        out.println("<html><head></head><body>");
        // conect to the database
        // create your statement object
        // run your statement and get the ResultSet
        Connection connection = null;
        Statement statement = null;
        ResultSet result = null;
        try {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            connection = DriverManager.getConnection("jdbc:odbc:OdbcName","userId","password");
            statement = connection.createStatement();
            result = statement.executeQuery("select * from table1");
        }
        catch (Exception e) { }
        /* result is the ResultSet object */
        ResultSetMetaData rsmd = null;
        int numOfCols = 0;
        
        try {
            rsmd = result.getMetaData();
            numOfCols = rsmd.getColumnCount();            
        } catch (SQLException e) {}
        
        out.println("<table border=2>");
        
        try {
            out.println("<tr>");
            for (int i=1; i<= numOfCols; i++) {
                out.println("<td><b>" + rsmd.getColumnName(i) + "</b></td>");
            }
            out.println("</tr>");
        } catch (SQLException e) {}
        
        try {
            while (result.next()) {
                out.println("<tr>");
                for (int i=1; i<= numOfCols; i++) {
                    out.println("<td>" + result.getString(i) + "</td>");
                }
                out.println("</tr>");
            }
        } catch (SQLException e) {}
        result.close();
        statement.close();
        connection.close();
        out.println("</table>");
        out.println("</body></html>");
        return;
    }
}

Notes: there is no main() method in a servlet. Servlets are executed through the web server/servlet engine.


[This message has been edited by Thomas Paul (edited December 08, 2000).]