Connecting to database in Servlets

A servlet can create one or more Connectionobject in its init() method and reuse them in its service(), doGet(), and doPost() methods. To demonstrate, Example 9-4 shows the phone lookup servlet rewritten to create its Connection objects in advance. It also uses HtmlSQLResult to display the results. Note that this servlet uses the Sybase JDBC driver.

Servlets Database Connection Using Mysql database

// File: ShowBedrock.java

/* A servlet to display the contents of the MySQL Bedrock database */

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

public class ShowBedrock extends HttpServlet
{
public String getServletInfo()
{
return "Servlet connects to MySQL database and displays result of a SELECT";
}

// Use http GET

public void doGet(HttpServletRequest request, HttpServletResponse response)
throws IOException, ServletException
{
String loginUser = "Dude1";
String loginPasswd = "SuperSecret";
String loginUrl = "jdbc:mysql://localhost:3306/bedrock";

response.setContentType("text/html"); // Response mime type

// Output stream to STDOUT
PrintWriter out = response.getWriter();

out.println("<html><head><title>Bedrock</title></head>");
out.println("<body><h1>Bedrock</h11>");

// Load the mm.MySQL driver
try
{
Class.forName("org.gjt.mm.mysql.Driver");
Connection dbcon = DriverManager.getConnection(loginUrl, loginUser, loginPasswd);
// Declare our statement
Statement statement = dbcon.createStatement();

String query = "select name, dept, ";
query += " jobtitle ";
query += "from employee ";

// Perform the query
ResultSet rs = statement.executeQuery(query);

out.println("<table border>");

// Iterate through each row of rs
while (rs.next())
{
String m_name = rs.getString("name");
String m_dept = rs.getString("dept");
String m_jobtitle = rs.getString("jobtitle");
out.println("<tr>" +
"<td>" + m_name + "</td>" +
"<td>" + m_dept + "</td>" +
"<td>" + m_jobtitle + "</td>" +
"</tr>");
}

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

rs.close();
statement.close();
dbcon.close();
}
catch (SQLException ex) {
while (ex != null) {
System.out.println ("SQL Exception: " + ex.getMessage ());
ex = ex.getNextException ();
} // end while
} // end catch SQLException

catch(java.lang.Exception ex)
{
out.println("<html>" +
"<head><title>" +
"Bedrock: Error" +
"</title></head>\n<body>" +
"<p>SQL error in doGet: " +
ex.getMessage() + "</p></body></html>");
return;
}
out.close();
}
}

Share And Enjoy:These icons link to social bookmarking sites where readers can share and discover new web pages.