Always reinventing the wheel
I fired up Eclipse yesterday, since I’m working with a j2ee developer who mentioned the Spring framework. Being the curious person that I am, I once again thought maybe it would be a good idea to dip my toe in the water.
Being – OK I admit it – middle-aged, I often forget stuff that I’ve done recently. So I was pleasantly surprised to see that I had a relatively recent version of Eclipse (Ganymede) up and running, and that I had even done some “HelloWorld” type stuff a few months ago. (Case in point, I went upstairs to fix the toilet chain, and found evidence that I had done so previously. Totally gone from my memory. Vanished. But based on the materials, I could tell it was me had done it. But I digress….)
So, I found I had written the following code, which is way cooler that HelloWorld:
package com.sendai77.hello;
import javax.servlet.*;
import javax.servlet.http.*;
import java.sql.*;
import javax.sql.*;
import javax.naming.*;
public class HelloDB extends HttpServlet {
DataSource pool;
public void init() throws ServletException {
Context env = null;
try {
env = (Context) new InitialContext().lookup("java:comp/env");
pool = (DataSource) env.lookup("jdbc/TestDB");
if (pool == null){
throw new ServletException ("null pool: TestDB is an unknown datasource");
}
}
catch (NamingException ne){
throw new ServletException(ne.getMessage());
}
}
public void doGet (HttpServletRequest req, HttpServletResponse resp) throws ServletException, java.io.IOException {
String theSQL = "select * from test_users";
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
//ResultSetMetaData rsm = null;
resp.setContentType("text/html");
java.io.PrintWriter out = resp.getWriter();
out.println("<html>");
out.println("<head><title>Hello Database</title></head>");
out.println("<body>");
out.println("<h2>Hello, Database!</h2>");
try {
// get the connection pool
conn = pool.getConnection();
// make the stmt
stmt = conn.createStatement();
// execute the query
rs = stmt.executeQuery(theSQL);
// get the metadata
//rsm = rs.getMetaData();
while(rs.next()){
out.println("<p>" + rs.getString(1) + " " + rs.getString(2) + " " + rs.getString(3) + "</p>");
}
}
catch (Exception e) {
throw new ServletException(e.getMessage());
}
finally {
try{
if (stmt != null){
stmt.close();
}
if (conn != null){
conn.close();
}
}
catch (Exception sqle) {
throw new ServletException(sqle.getMessage());
}
}
out.println("</body>");
out.println("</html>");
}
public void doPost (HttpServletRequest req, HttpServletResponse resp) throws ServletException, java.io.IOException {
doGet(req, resp);
}
}
Nothing terribly new and exciting here, except I couldn’t for the life of me recall how to set Tomcat up to resolve the jdbc call. So I’m writing this post to remind myself.
Looking in the META-INF folder for this webapp I find a file called “context.xml” which has the following content:
<?xml version="1.0" encoding="UTF-8"?>
<Context path="/hello" docBase="hello"
debug="5" reloadable="true" crossContext="true">
<!-- maxActive: Maximum number of dB connections in pool. Make sure you
configure your mysqld max_connections large enough to handle
all of your db connections. Set to 0 for no limit.
-->
<!-- maxIdle: Maximum number of idle dB connections to retain in pool.
Set to -1 for no limit. See also the DBCP documentation on this
and the minEvictableIdleTimeMillis configuration parameter.
-->
<!-- maxWait: Maximum time to wait for a dB connection to become available
in ms, in this example 10 seconds. An Exception is thrown if
this timeout is exceeded. Set to -1 to wait indefinitely.
-->
<!-- username and password: MySQL dB username and password for dB connections -->
<!-- driverClassName: Class name for the old mm.mysql JDBC driver is
org.gjt.mm.mysql.Driver - we recommend using Connector/J though.
Class name for the official MySQL Connector/J driver is com.mysql.jdbc.Driver.
-->
<!-- url: The JDBC connection url for connecting to your MySQL dB.
The autoReconnect=true argument to the url makes sure that the
mm.mysql JDBC Driver will automatically reconnect if mysqld closed the
connection. mysqld by default closes idle connections after 8 hours.
-->
<Resource name="jdbc/TestDB" auth="Container" type="javax.sql.DataSource"
maxActive="100" maxIdle="30" maxWait="10000"
username="tester" password="password" driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/test?autoReconnect=true"/>
</Context>
So there it is. The only mystery remaining to me is: did Eclipse generate this file? Did I cadge it from Tomcat? Did I google it up?….