MySQL Connector/J 5.1 Developer Guide
This section describes a simple servlet that can be used in the
GlassFish environment to access a MySQL database. As with the
previous section, this example assumes the sample database
world
is installed.
The project is set up with the following directory structure:
index.html WEB-INF | - web.xml - sun-web.xml - classes | - HelloWebServlet.java - HelloWebServlet.class
The code for the servlet, located in
HelloWebServlet.java
, is as follows:
import javax.servlet.http.*; import javax.servlet.*; import java.io.*; import java.sql.*; import javax.sql.*; import javax.naming.*; public class HelloWebServlet extends HttpServlet { InitialContext ctx = null; DataSource ds = null; Connection conn = null; PreparedStatement ps = null; ResultSet rs = null; String sql = "SELECT Name, Population FROM Country WHERE Name=?"; public void init () throws ServletException { try { ctx = new InitialContext(); ds = (DataSource) ctx.lookup("java:comp/env/jdbc/MySQLDataSource"); conn = ds.getConnection(); ps = conn.prepareStatement(sql); } catch (SQLException se) { System.out.println("SQLException: "+se.getMessage()); } catch (NamingException ne) { System.out.println("NamingException: "+ne.getMessage()); } } public void destroy () { try { if (rs != null) rs.close(); if (ps != null) ps.close(); if (conn != null) conn.close(); if (ctx != null) ctx.close(); } catch (SQLException se) { System.out.println("SQLException: "+se.getMessage()); } catch (NamingException ne) { System.out.println("NamingException: "+ne.getMessage()); } } public void doPost(HttpServletRequest req, HttpServletResponse resp){ try { String country_name = req.getParameter("country_name"); resp.setContentType("text/html"); PrintWriter writer = resp.getWriter(); writer.println("<html><body>"); writer.println("<p>Country: "+country_name+"</p>"); ps.setString(1, country_name); rs = ps.executeQuery(); if (!rs.next()){ writer.println("<p>Country does not exist!</p>"); } else { rs.beforeFirst(); while(rs.next()) { writer.println("<p>Name: "+rs.getString("Name")+"</p>"); writer.println("<p>Population: "+rs.getString("Population")+"</p>"); } } writer.println("</body></html>"); writer.close(); } catch (Exception e) { e.printStackTrace(); } } public void doGet(HttpServletRequest req, HttpServletResponse resp){ try { resp.setContentType("text/html"); PrintWriter writer = resp.getWriter(); writer.println("<html><body>"); writer.println("<p>Hello from servlet doGet()</p>"); writer.println("</body></html>"); writer.close(); } catch (Exception e) { e.printStackTrace(); } } }
In the preceding code a basic doGet()
method
is implemented, but is not used in the example. The code to
establish the connection with the database is as shown in the
previous example,
Section 14.1, “A Simple JSP Application with GlassFish, Connector/J and MySQL”,
and is most conveniently located in the servlet
init()
method. The corresponding freeing of
resources is located in the destroy method. The main
functionality of the servlet is located in the
doPost()
method. If the user enters into the
input form a country name that can be located in the database,
the population of the country is returned. The code is invoked
using a POST action associated with the input form. The form is
defined in the file index.html
:
<html> <head><title>HelloWebServlet</title></head> <body> <h1>HelloWebServlet</h1> <p>Please enter country name:</p> <form action="HelloWebServlet" method="POST"> <input type="text" name="country_name" length="50" /> <input type="submit" value="Submit" /> </form> </body> </html>
The XML files web.xml
and
sun-web.xml
are as for the example in the
preceding section,
Section 14.1, “A Simple JSP Application with GlassFish, Connector/J and MySQL”,
no additional changes are required.
When compiling the Java source code, you will need to specify
the path to the file javaee.jar
. On
Windows, this can be done as follows:
shell> javac -classpath c:\glassfishv3\glassfish\lib\javaee.jar HelloWebServlet.java
Once the code is correctly located within its directory structure, and compiled, the application can be deployed in GlassFish. This is done in exactly the same way as described in the preceding section, Section 14.1, “A Simple JSP Application with GlassFish, Connector/J and MySQL”.
Once deployed the application can be launched from within the GlassFish Administration Console. Enter a country name such as “England”, and the application will return “Country does not exist!”. Enter “France”, and the application will return a population of 59225700.