Ecommerce Technology: An Overview

Exercise 11: Install and test the JDBC driver for mSQL using these instructions. Next, let's write a servlet that access a database:

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

       public class TestJDBCServlet extends HttpServlet {

         public void doGet (HttpServletRequest req, HttpServletResponse resp)
            throws ServletException, IOException
         {
          // Set the content type of the response.
          resp.setContentType ("text/html");

          // Create a PrintWriter to write the response.
          java.io.PrintWriter out = new PrintWriter (resp.getOutputStream());

          // The first part of the response.
          out.println ("");
          out.println ("");
          out.println ("");

          // The SQL query.
          String sql = "select * from emp";
          System.out.println("Executing: " + sql);

          // Create a driver instance.
          try {
            Class.forName("com.imaginary.sql.msql.MsqlDriver").newInstance();

            // Make a connection.
  	    String url = "jdbc:msql://localhost:8114/test";
	    Connection con = DriverManager.getConnection(url, "simha", "");
   	    if (con == null) {
	      System.out.println ("Connection unsuccessful");
	      System.exit(0);
	    }
	    else {
	      System.out.println ("Connection successful. Continuing ...");
	    }

	    // Make a statement.
 	    Statement s = con.createStatement();   
	    if (s == null) {
	      System.out.println ("Statement creation unsuccessful");
	      System.exit(0);
	    }
	    else {
	      System.out.println ("Statement creation successful ... continuing");
	    }

	    // Execute the SQL statement.
	    if ( s.execute (sql) ) {
	      ResultSet r = s.getResultSet();
	      ResultSetMetaData meta = r.getMetaData();
	      int cols = meta.getColumnCount();
	      int rownum = 0;
	      
              // Iterate over the rows - get one row at a time.
	      while( r.next() ) {
		rownum++;
		System.out.println("Row: " + rownum);
		for(int i=0; i");
          out.println ("");
          out.close();

          // Screen I/O
          System.out.println ("Inside servlet ... servlet complete");
        }

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

      }

  

Exercise 12: Compile and test the above servlet.

More About Servlets

There is a lot more to servlet programming than the above three examples. Here are some issues:

• Database issues:
  • Connection pooling:
    • Creating a database connection takes time.
    • Rather than create one for each request, create a whole bunch at system startup, and re-use connections.
    • Some database servers limit the number of "open" connections.
  • Transactions:
    • Many databases (e.g., mSQL) don't handle transactions.
    • It's easy to handle some aspects of transactions in Java using threads.
• Session issues:
  • User session tracking.
  • Cookies.
• Performance issues:
  • Caching database relations.
  • Caching meta data.