The application will contain a simple index.html file, a simple index.jsp file and two servlets DBConnect and JNDIConnectDB. The first servlet will create and connect to a database directly using sql commands. The second servlet will connect to a database using a configured JNDI entry in Apache Tomcat.
Create a simple servlet
Right click on the source folder in the project and create a new servlet as follows:
Java Resources: src (right-click)
new -> other -> web -> servlet
next
set the following in the Create Servlet window:
java package: servlets
class name: JNDIConnectDB
and click next to navigate to the next screen in the wizard.
In the URL Mappings window, click on /JNDIConnectDB and click the edit button
URL Mappings
/JNDIConnectDB
Edit
change the pattern from /JNDIConnectDB to /servlet/JNDIConnectDB
/JNDIConnectDB (change to) /servlet/JNDIConnectDB
click ok and then click finish
ok
finish
Enter the following code into JNDIConnectDB.java
package servlets;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;
/**
* Servlet implementation class DBConnect
*/
public class JNDIConnectDB extends HttpServlet
{
private static final long serialVersionUID = 1L;
private static String driver = "org.apache.derby.jdbc.ClientDriver";
private static String protocol = "jdbc:derby://localhost:1527/";
private static String dbName = "mydb";
private Statement s;
/**
* @see HttpServlet#HttpServlet()
*/
public JNDIConnectDB()
{
super();
// TODO Auto-generated constructor stub
}
private void process(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException
{
try
{
PrintWriter out = response.getWriter();
Context ctx = new InitialContext( );
DataSource ds = (DataSource)
ctx.lookup("java:comp/env/jdbc/myDB");
Connection conn = ds.getConnection( );
Properties props = new Properties();
String create = request.getParameter("create");
s = conn.createStatement();
if(create!=null && create.equals("true"))
{
execute("DROP TABLE test");
execute("CREATE TABLE test (id INTEGER NOT NULL,name VARCHAR(32) NOT NULL)");
execute("ALTER TABLE test ADD PRIMARY KEY (id)");
execute("INSERT INTO test(id,name) VALUES(1,'john')");
execute("INSERT INTO test(id,name) VALUES(2,'richard')");
}
ResultSet rs = s.executeQuery("select * from test");
out.println("<staff>");
while(rs.next())
{
ResultSetMetaData rsmd = rs.getMetaData();
out.println("<employee>");
for(int i=1;i<=rsmd.getColumnCount();i++)
{
String colName = rsmd.getColumnName(i);
out.println("<"+colName+">");
out.println(rs.getObject(i));
out.println("</"+colName+">");
}
out.println("</employee>");
}
out.println("</staff>");
conn.close( );
}
catch (SQLException e)
{
throw new ServletException(e);
}
catch (NamingException e)
{
throw new ServletException(e);
}
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse
* response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException
{
process(request, response);
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse
* response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException,
IOException
{
process(request, response);
}
private void execute(String query)
{
try
{
s.execute(query);
System.out.println("executed: "+query);
}
catch(SQLException e)
{
System.err.println(query);
System.err.println(e.getMessage());
}
}
}
Create a context.xml file in your WebContent/META-INF folder by right-clicking on META-INF and selecting new xml file as follows:
WebContent/META-INF (right-click on META-INF)
new -> other -> xml -> xml
File name: context.xml
finish
click finish to close the popup window and then enter the following into the context.xml file:
<?xml version="1.0" encoding="UTF-8"?>
<Context>
<Resource
name="jdbc/myDB"
type="javax.sql.DataSource"
auth="Container"
username="john"
password="password"
driverClassName="org.apache.derby.jdbc.ClientDriver"
url="jdbc:derby://localhost:1527/mydb"
maxActive="8"
/>
</Context>
Start the Server and Test the Servlet
First of all make certain that your Derby database is running (see earlier blog entries).
Right click on the project (Tomcat1) and click run on server
project (Tomcat1) - (right click)
run on server
The server will start.
Now open your browser and navigate to the servlet by entering the following url into your browser:
http://localhost:8080/Tomcat1/servlet/JNDIConnectDB
The output you should see in your browser window is below:
<staff>
<employee>
<ID>
1
</ID>
<NAME>
john
</NAME>
</employee>
<employee>
<ID>
2
</ID>
<NAME>
richard
</NAME>
</employee>
</staff>
