Retrieve DB data with Java servlet and HTML form

java sql database servlets

10,523

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

10,523

Author by

Pankaj Ashok

Updated on June 04, 2022

Comments

Pankaj Ashok almost 2 years

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>

Pankaj Ashok over 11 years

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).