Retrieve DB data with Java servlet and HTML form

Question

I created a simple form and a small servlet to execute a SQL statement from browser. When I click "Submit Query" button on the form, then the returinig page shows nothing retrieved from the DB. It only shows "Database Results" ( tag part) on the browser. Please advice me what is wrong with my code.

SQLTestForm.java

import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;
import java.sql.*;

public class SQLTestForm extends HttpServlet {
    public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        response.setContentType("text/html");
        PrintWriter out = response.getWriter();
        String title = "Results";
        String docType = 
            "<!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 4.0 " + "Transitional//EN\"\n";
            out.print(docType + "<HTML>\n" + "<HEAD><TITLE>" + title + "</TITLE></HEAD>\n" + "<BODY>" + "<H1>Database Results</H1>\n");

        String driver = request.getParameter("driver");
        String url = request.getParameter("url");
        String username = request.getParameter("username");
        String password = request.getParameter("password");
        String qry = request.getParameter("query");
        showTable(driver, url, username, password, qry, out);
        out.println("</BODY></HTML>");
    }

  public void showTable(String driver, String url, String username, String password, String qry, PrintWriter out) {
    try {
    Class.forName(driver);
    Connection conn = DriverManager.getConnection(url, username, password);
    DatabaseMetaData dbMetaData = conn.getMetaData();
    out.println("<UL>");
    String productName = dbMetaData.getDatabaseProductName();
    String productVersion = dbMetaData.getDatabaseProductVersion();
    out.println(" <LI><B>Database:</B> " + productName + " <LI><B>Version:</B> " + productVersion + "</UL>");
    Statement stmt = conn.createStatement();
    ResultSet rs = stmt.executeQuery(qry);
    out.println("<TABLE BORDER=1>");
    ResultSetMetaData rm = rs.getMetaData();
    int cnum = rm.getColumnCount();
    out.println("<TR>");
        for(int i=1; i <= cnum; i++) {
            out.print("<TH>" + rm.getColumnName(i));
        }
        out.println();
        while(rs.next()) {
            out.println("<TR>");
                for(int i=1; i <= cnum; i++) {
                    out.print("<TD>" + rs.getString(i));
                }
            out.println();
        }
        out.println("</TABLE>");
        conn.close();
    } catch (ClassNotFoundException cnfe) {
        System.err.println("Error loading driver: " + cnfe);
    } catch (SQLException se) {
        System.err.println("Error connecting: " + se);
    } catch(Exception e) {
        System.err.println("Error with input: " + e);   }  } }

SQLTestForm.html

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
 <HEAD>
  <TITLE>SQL Test Form</TITLE>
 </HEAD>
 <BODY>
  <H2>Query Input:</H2>
  <FORM ACTION="/SQLTestForm" METHOD="POST">
  <TABLE>
   <TR><TD>Driver:
   <TD><INPUT TYPE="TEXT" NAME="driver" VALUE="com.jdbc.mysql.Driver" SIZE="45">
   <TR><TD>URL:
   <TD><INPUT TYPE="TEXT" NAME="url"
   VALUE="jdbc:mysql://localhost:3306/test" SIZE="45">
   <TR><TD>Username:
   <TD><INPUT TYPE="TEXT" NAME="username">
   <TR><TD>Password:
   <TD><INPUT TYPE="PASSWORD" NAME="password">
   <TR><TD VALIGN="TOP">Query:
   <TD><TEXTAREA ROWS="5" COLS="35" NAME="query"></TEXTAREA>
   <TR><TD COLSPAN="2" ALIGN="CENTER"><INPUT TYPE="SUBMIT">
  </TABLE>
  </FORM>
 </BODY>
</HTML>

Are there any Exceptions? Change System.out.println(""); to out.println(""), to see the result. — Christian Kuetbach
– Christian Kuetbach, Commented Jan 21, 2013 at 21:08
Christain, Thank you for the comment. After changing the lines it put out "Error loading driver: java.lang.ClassNotFoundException: com.djbc.mysql.Driver" on the browser. My Classpath or my Tomcat setting is wrong? — Pankaj Ashok
– Pankaj Ashok, Commented Jan 22, 2013 at 18:49

Christian Kuetbach · Accepted Answer · 2013-01-22 19:04:15Z

1

Your classpath is missing the jdbc Driver. This may have two causes:

You have forgotten to deliver the deiver in you war-file or the shared/commons-lib folder.
You misspelled the drivers Classname (in you comment you wrote com.djbc and not com.jdbc

You know that you open a backdoor to the database? The Database engine checks the source of database requests. Now the requests are from your webserver. Every single computer that has access to you webpage will get a connection to your database.

You should change your code:

Never use uncheck parameter and pass them to the database.
Use PreparedStatement instead of Statement
Don't make String concatenations to create a query

answered Jan 22, 2013 at 19:04

Christian Kuetbach

16.1k5 gold badges45 silver badges80 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Pankaj Ashok Over a year ago

Christian, I appreciate your advice. I am aware the backdoor thing. I will update the code in the near future to make it secure. Regarding the MySQL driver, I will double check my war file. mysql-connector-java-5.1.22-bin.jar file is placed both in shared lib folder and my app's WEB-INF\lib folder. Also no typo of drivers classname (dispite of the one I created in my previous comment).

Collectives™ on Stack Overflow

Retrieve DB data with Java servlet and HTML form

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related