Servlets - Database Access

Accessing Database from servlets through JDBC! Accessing Access Database From Servlet This article shows you how to access database from servlets.

Create Table

To create the Users table in TEST database, use the following steps:


Create Table
CREATE TABLE `user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `age` bigint(20) DEFAULT NULL,
  `gender` varchar(255) DEFAULT NULL,
  `Hobbies` varchar(255) DEFAULT NULL,
  `jobtype` varchar(255) DEFAULT NULL,
  `username` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1

We are assuming there are many records in this table. In this example, we are getting the data from the database in servlet and printing it. We are doing all the database logic in servlet for simplicity of the program. But it will be better to separate it from the servlet file.

Example of Fetching Result-
import java.io.*;
import java.util.Enumeration;
import javax.servlet.*;
import javax.servlet.http.*;
import java.sql.*;
import java.net.*;

public class ServletData extends HttpServlet{
   Connection theConnection;
   private ServletConfig config;

public void init(ServletConfig config)
  throws ServletException{
   this.config=config;
   }

public void service (HttpServletRequest req, HttpServletResponse res)
throws ServletException, IOException {

   HttpSession session = req.getSession(true);

   res.setContentType("text/html");

   PrintWriter out = res.getWriter();

   out.println("<HTML><HEAD><TITLE>Emai List.</TITLE>");

   out.println("</HEAD>");

   out.println("<BODY bgColor=blanchedalmond text=#008000 topMargin=0>");

   out.println("<P align=center><FONT face=Helvetica><FONT color=fuchsia style=\"BACKGROUND-COLOR: white\"><BIG><BIG>List of users.</BIG></BIG></FONT></P>");

  out.println("<P align=center>");

out.println("<TABLE align=center border=1 cellPadding=1 cellSpacing=1 width=\"75%\">");

 

  out.println("<TR>");

  out.println("<TD>ID</TD>");

  out.println("<TD>AGE</TD>");
out.println("<TD>GENDER</TD>");
out.println("<TD>Hobbies</TD>");
out.println("<TD>jobtype</TD>");
  out.println("<TD>username</TD></TR>");

try{


   //Loading Sun's JDBC ODBC Driver 
   Class.forName("com.mysql.jdbc.Driver");
 

   //Connect to emaildb Data source
   theConnection = DriverManager.getConnection("jdbc:mysql://localhost:3306/DOJDB", "root", "root");
 

   Statement theStatement=theConnection.createStatement();

   ResultSet theResult=theStatement.executeQuery("select * from user"); //Select all records from user table.

  //Fetch all the records and print in table
  while(theResult.next()){

   out.println();

   out.println("<TR>");

   out.println("<TD>" + theResult.getString(1) + "</TD>");

   out.println("<TD>" + theResult.getString(2) + "</TD>");

   String s=theResult.getString(3);

   out.println("<TD><a href=" + s + ">" + s + "</a></TD>");
 out.println("<TD>" + theResult.getString(4) + "</TD>");
 out.println("<TD>" + theResult.getString(5) + "</TD>");
 out.println("<TD>" + theResult.getString(6) + "</TD>");
   out.println("</TR>");

  }

  theResult.close();//Close the result set

  theStatement.close();//Close statement

  theConnection.close(); //Close database Connection

  }catch(Exception e){

   out.println(e.getMessage());//Print trapped error.

  }

  out.println("</TABLE></P>");

  out.println("<P>&nbsp;</P></FONT></BODY></HTML>");

 }

  public void destroy(){

  }

}


Servlets - Database Access




<<Previous <<   || Index ||   >>Next >>









No comments:

Post a Comment