Kohei Nozaki's blog 

Defining Embedded Derby as a DataSource of Tomcat 8


Posted on Wednesday Feb 11, 2015 at 12:16AM in Technology


Configuration

  1. Put derby.jar into $CATALINA_HOME/lib

  2. Define a Resource element inside Context element in $CATALINA_HOME/conf/context.xml as follows:

    <Resource name="jdbc/derby" auth="Container"
    		  driverClassName="org.apache.derby.jdbc.EmbeddedDriver"
    		  username="sa" password="sa"
    		  type="javax.sql.DataSource"
    		  url="jdbc:derby:/Users/kyle/tmp/sampledb;create=true"/>
  3. Clone derby-shutdown-listener, exec mvn clean package and put target/derby-shutdown-listener.jar into $CATALINA_HOME/lib

  4. Put following fragment into Server element in $CATALINA_HOME/conf/server.xml

    <Listener className="org.nailedtothex.derby.DerbyShutdownLifecycleListener" />

Test of lookup from Servlet

  1. Put following fragment into WEB-INF/web.xml of your web application

    <?xml version="1.0" encoding="UTF-8"?>
    <web-app xmlns="http://xmlns.jcp.org/xml/ns/javaee"
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_3_1.xsd"
             version="3.1">
        <resource-ref>
            <res-ref-name>jdbc/derby</res-ref-name>
            <res-type>javax.sql.DataSource</res-type>
            <res-auth>Container</res-auth>
        </resource-ref>
    </web-app>
  2. Put an example Servlet as follows

    @WebServlet(urlPatterns = "/")
    public class TestServlet extends HttpServlet {
    
        private Context context;
        private DataSource dataSource;
    
        @Override
        public void init() throws ServletException {
            try {
                context = new InitialContext();
                dataSource = (DataSource) context.lookup("java:comp/env/jdbc/derby");
            } catch (NamingException e) {
                throw new ServletException(e);
            }
        }
    
        @Override
        protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
            try (Connection cn = dataSource.getConnection();
                 Statement st = cn.createStatement();
                 ResultSet rs = st.executeQuery("SELECT CURRENT_TIMESTAMP FROM SYSIBM.SYSDUMMY1")) {
                while (rs.next()) {
                    resp.getWriter().write(rs.getTimestamp(1).toString());
                }
            } catch (SQLException e) {
                throw new ServletException(e);
            }
        }
    
        @Override
        public void destroy() {
            if (context != null) {
                try {
                    context.close();
                } catch (NamingException e) {
                    // no-op
                }
            }
        }
    }
  3. Access from your browser