MySQL Connector/J 5.1 Developer Guide
This section shows how to deploy a simple JSP application on GlassFish, that connects to a MySQL database.
This example assumes you have already set up a suitable
Connection Pool and JDBC Resource, as explained in the preceding
sections. It is also assumed you have a sample database
installed, such as world
.
The main application code, index.jsp
is
presented here:
<%@ page import="java.sql.*, javax.sql.*, java.io.*, javax.naming.*" %> <html> <head><title>Hello world from JSP</title></head> <body> <% InitialContext ctx; DataSource ds; Connection conn; Statement stmt; ResultSet rs; try { ctx = new InitialContext(); ds = (DataSource) ctx.lookup("java:comp/env/jdbc/MySQLDataSource"); //ds = (DataSource) ctx.lookup("jdbc/MySQLDataSource"); conn = ds.getConnection(); stmt = conn.createStatement(); rs = stmt.executeQuery("SELECT * FROM Country"); while(rs.next()) { %> <h3>Name: <%= rs.getString("Name") %></h3> <h3>Population: <%= rs.getString("Population") %></h3> <% } } catch (SQLException se) { %> <%= se.getMessage() %> <% } catch (NamingException ne) { %> <%= ne.getMessage() %> <% } %> </body> </html>
In addition two XML files are required:
web.xml
, and
sun-web.xml
. There may be other files
present, such as classes and images. These files are organized
into the directory structure as follows:
index.jsp WEB-INF | - web.xml - sun-web.xml
The code for web.xml
is:
<?xml version="1.0" encoding="utf-8"?> <web-app version="2.4" xmlns="http://java.sun.com/xml/ns/j2ee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd"> <display-name>HelloWebApp</display-name> <distributable/> <resource-ref> <res-ref-name>jdbc/MySQLDataSource</res-ref-name> <res-type>javax.sql.DataSource</res-type> <res-auth>Container</res-auth> <res-sharing-scope>Shareable</res-sharing-scope> </resource-ref> </web-app>
The code for sun-web.xml
is:
<?xml version="1.0" encoding="utf-8"?> <!DOCTYPE sun-web-app PUBLIC "-//Sun Microsystems, Inc.//DTD Application Server 8.1 Servlet 2.4//EN" "http://www.sun.com/software/appserver/dtds/sun-web-app_2_4-1.dtd"> <sun-web-app> <context-root>HelloWebApp</context-root> <resource-ref> <res-ref-name>jdbc/MySQLDataSource</res-ref-name> <jndi-name>jdbc/MySQLDataSource</jndi-name> </resource-ref> </sun-web-app>
These XML files illustrate a very important aspect of running
JDBC applications on GlassFish. On GlassFish it is important to
map the string specified for a JDBC resource to its JNDI name,
as set up in the GlassFish administration console. In this
example, the JNDI name for the JDBC resource, as specified in
the GlassFish Administration console when creating the JDBC
Resource, was jdbc/MySQLDataSource
. This must
be mapped to the name given in the application. In this example
the name specified in the application,
jdbc/MySQLDataSource
, and the JNDI name,
happen to be the same, but this does not necessarily have to be
the case. Note that the XML element <res-ref-name> is used
to specify the name as used in the application source code, and
this is mapped to the JNDI name specified using the
<jndi-name> element, in the file
sun-web.xml
. The resource also has to be
created in the web.xml
file, although the
mapping of the resource to a JNDI name takes place in the
sun-web.xml
file.
If you do not have this mapping set up correctly in the XML files you will not be able to lookup the data source using a JNDI lookup string such as:
ds = (DataSource) ctx.lookup("java:comp/env/jdbc/MySQLDataSource");
You will still be able to access the data source directly using:
ds = (DataSource) ctx.lookup("jdbc/MySQLDataSource");
With the source files in place, in the correct directory structure, you are ready to deploy the application:
In the navigation tree, navigate to Applications - the Applications frame will be displayed. Click .
You can now deploy an application packaged into a single WAR file from a remote client, or you can choose a packaged file or directory that is locally accessible to the server. If you are simply testing an application locally you can simply point GlassFish at the directory that contains your application, without needing to package the application into a WAR file.
Now select the application type from the
Type drop-down listbox, which in this
example is Web application
.
Click OK.
Now, when you navigate to the Applications
frame, you will have the option to Launch,
Redeploy, or Restart
your application. You can test your application by clicking
Launch. The application will connection to
the MySQL database and display the Name and Population of
countries in the Country
table.