jdbc - mysql

Servlet Using MySQL

This servlet uses a MySQL driver to query a MySQL database running on a remote machine. The database is presumed to already exist. In this "Hello, World" example, a single "canned" query is processed. A real application would include support for adding, deleting, searching on user-supplied data, updating, etc.

Note that the program uses a special MySql driver -- com.mysql.jdbc.Driver. To interact with a MySQL database from another machine, such as your local development machine, you must place the jar file that contains this file on the classpath used by your server (e.g., . . . /tomcat5.5/shared/lib). You can find a copy of this jar file in the course tools directory.

Interacting with a Database using JDBC, in general, and MySQL, in particular, involves some six basic steps:

Load a driver ( using Class.forName() ).
Create a Connection to your database ( using DriverManager ).
Create a Statement ( using the Connection ).
Create an SQL String.
Get a ResultSet by calling an appropriate method ( e.g., executeQuery or executeUpdate ) on the Statement, using the SQL String as an argument.
Transfer ResultSet data to DataBean or ColletionBean for return.

These basic steps are illustrated in the example servlet, below. You may wish to consult the JDBC API.

Servlet

import java.awt.*;
import java.awt.event.*;
import java.net.*;
import java.io.*;
import java.util.*;
import javax.servlet.*;
import javax.servlet.http.*;
import java.sql.*;

public class jbsJDBCServletMysql extends HttpServlet {


//*****  Servlet access to data base


    public void doGet (HttpServletRequest req, HttpServletResponse resp)
	throws ServletException, IOException
	{

    	String url   = "jdbc:mysql://tomcat5.cs.unc.edu/your_logineb2c";

    	String query = "SELECT * FROM Person "
        + "WHERE owner = 'jbs'";
						
	try {

		Class.forName  ("com.mysql.jdbc.Driver");

      		Connection con = DriverManager.getConnection 
		  ( url, "your_mysgl_login", "your_mysgl_password" );

            	Statement stmt = con.createStatement ();

            	ResultSet rs = stmt.executeQuery (query);

            	printResultSet ( resp, rs );

            	rs.close();
            	stmt.close();
            	con.close();

        }  // end try

        catch (SQLException ex) {
            
		PrintWriter out = resp.getWriter();
	        resp.setContentType("text/html");
			
		while (ex != null) {  
                	out.println ("SQL Exception:  " + ex.getMessage ());
                	ex = ex.getNextException ();  
              }  // end while

        }  // end catch SQLException

        catch (java.lang.Exception ex) {

      	PrintWriter out = resp.getWriter();
		resp.setContentType("text/html");	
		out.println ("Exception:  " + ex.getMessage ());
	  }

    }  // end doGet


    private void printResultSet ( HttpServletResponse resp, ResultSet rs )
        throws SQLException  {

        try  {

		PrintWriter out = resp.getWriter();

	        out.println("<html>");
	        out.println("<head><title>jbs jdbc/mysql servlet</title></head>");
	        out.println("<body>");
	        out.println("<center><font color=AA0000>");
	        out.println("<h3>jbsJDBCServlet</h3>");
	        out.println("<h3>Data Retrieved:</h3>");
	        
	        out.println("<table border='1'>");

           	int numCols = rs.getMetaData().getColumnCount ();
                while ( rs.next() ) {
		  out.println("<tr>");
               	  for (int i=1; i<=numCols; i++) {
                    out.print("<td>" + rs.getString(i) + "</td>" );
                  }  // end for
                  out.println("</tr>");
                }  // end while

	        out.println("</table>");
	        
	        out.println("</font></center>");
	        out.println("</body>");
	        out.println("</html>");
	        out.close();

	    }  // end try
        catch ( IOException except)  {
        }  // end catch

    }  // end returnHTML


}  // end jbsJDBCServlet