Access database from java web applications?

[ash lester]

I want to start learning about this topic, but I'm having problems finding good tutorials out there.

I am using tomcat 6 and emacs

I would prefer to learn how to set up and access a database from a web-app using the command line and emacs, as clicking at an IDE doesn't teach me much

I find tutorials for tomcat 5.x and 4.x but I get part way through them and can't finish them because things have changed since they have been published

When I find forum posts, I find a lot of confused folk complaining about server.xml web.xml META-INF context.xml JNDI JDBC...

Where should I start? Can someone kick me into gear?

[Bear Bibeault]

Database access should be safely ensconced in a business layer that has nothing at all to do with the UI (e.g. Servlets and Tomcat et al). If you are looking for an ORM tool, Hibernate is a popular choice. There are others. If you want to roll your own, learn about JDBC.

After your business layer is created and tested, you can start to think about the UI tier.

[Bear Bibeault]

P.S. You might find this article helpful for learning best practice web app structure.

[David Newton]

Yay! An Emacs person!

That said, for Java stuff--no way; I used to use the JDE, as recently as a few years ago. Found myself going to it less and less for Java work, now essentially never. There are just too many tools available in the IDEs. (And, sadly, I find its Lisp no longer suits my Lisp preferences :(

That aside, you didn't TellTheDetails, so it's impossible to help: how are you trying to do it? Are you trying to configure a datasource?

Are you just trying to use the driver directly? What database?

[ash lester]

Yay! An Emacs person!

I love emacs. When I open it, a few quick C-x C-f, or maybe M-x make-directory, followed by a C-x 3 and flipping with C-x o, is just so... smooth. I hate using the mouse.

That said, for Java stuff--no way; I used to use the JDE, as recently as a few years ago. Found myself going to it less and less for Java work, now essentially never. There are just too many tools available in the IDEs. (And, sadly, I find its Lisp no longer suits my Lisp preferences :(

I like netbeans, but I was following a tutorial found here http://netbeans.org/kb/docs/web/quickstart-webapps.html and part way through it, I found i was clicking things, and not having a clue what I was doing.

That aside, you didn't TellTheDetails, so it's impossible to help: how are you trying to do it? Are you trying to configure a datasource?

Yeah, sorry, that is probably the worst post I've made in any forum, it was too general. I'm getting to that in a bit.

Are you just trying to use the driver directly? What database?

Apache Derby. How do you mean, use the driver directly? You mean hard coding everything in a servlet?

Database access should be safely ensconced in a business layer that has nothing at all to do with the UI (e.g. Servlets and Tomcat et al). If you are looking for an ORM tool, Hibernate is a popular choice. There are others. If you want to roll your own, learn about JDBC

Could you expand on this a bit? When you say roll your own, I just thought that JDBC was the api to interact with the vendor driver for your database? Is hibernate an alternative way of doing it? And yeah, I wasn't going to hard-code the servlet, I just didn't know the best place to put my post :)

Right.

I think my first specific question has to be about this paragraph in the tomcat link in David's post.

Next, modify the web application deployment descriptor (/WEB-INF/web.xml) to declare the JNDI name under which you will look up preconfigured data source. By convention, all such names should resolve to the jdbc subcontext (relative to the standard java:comp/env naming context that is the root of all provided resource factories. A typical web.xml entry might look like this:

As I understand it, JNDI is like a shop assistant, showing the customers (the server and the web app) the way to the fruit aisle (the database), going past different food on the way (different resources). Is that about right?

Now when they say data source, what do they mean? What is its purpose? Also, I don't understand the text in green. Can someone err... thickify it for me? :)

Now, this is in the web.xml, and uses JNDC (I think.)

<resource-ref>
  <description>
    Resource reference to a factory for java.sql.Connection
    instances that may be used for talking to a particular
    database that is configured in the <Context>
    configurartion for the web application.
  </description>
  <res-ref-name>
    [color=red]jdbc/myDatabaseName[/color]
  </res-ref-name>
  <res-type>
    javax.sql.DataSource
  </res-type>
  <res-auth>
    Container
  </res-auth>
</resource-ref>

In this, the only thing I need to change is the text in red. Yes? All the rest does is to say to the application, hey, when I want an instance of the Connection class, the class methods I use are with a DataSource object, and that is jdbc/myDatabaseName.
What directory does the database need to be in at this point? Or will the application know where it is because you copy the driver in to the /lib folder of the server?

[Rohit Garg]

As I understand it, JNDI is like a shop assistant, showing the customers (the server and the web app) the way to the fruit aisle (the database), going past different food on the way (different resources). Is that about right?

Nice way of putting things together ...

JNDI is totally and entirely linked to the configurational side of the server. You won't be needing additional code to make beans on the webserver get shared across different applications. It behaves just like a TreeMap which has string vs an object. If you lookup for a string, you will get the object.

Now when they say data source, what do they mean? What is its purpose? Also, I don't understand the text in green. Can someone err... thickify it for me?

A Datasource is like a wrapper to the connection. As you might know that there a lot of vendors working on creating datasources, at times they miss out on things in their drivers that can change the way your application interacts with your database. For example you might want to configure that a ConnectionFactory only passes 5 connections and not more than that at any point in time. It would become your headache to code that thing. Now what the containers have provided to you is the same thing but in an externalized manner (kind of property/xml file driven). They only say that you have the freedom to create database connections inside your application, but i have provided you an API via which you will be able to manage them outside of your application.

As far as the text in green is concerned, it just says that you need to make entries to your datasource to another file (usually server.xml) and just add a reference to it in your descriptor (usually web.xml). Then what it would do is that put an instance of the datasource object given by container to the TreeMap provided by JNDI and the string to access would be java:comp/env/<datasource_name> (something like java:comp/env/jdbc/myDatabaseName).

Hope that helps!!

[ash lester]

OK Thanks for the explanation, I get it a bit more now, the fog is starting to clear a bit. What I'll do is code a really simple webpage which connects to a database and I'll report back if I can't get it right. Thanks so much for your help!

[Rohit Garg]

No issues man... Do post back when you arr done..

Thanks so much for your help!

No issues man. We are all here to GET and PUT help!!

[ash lester]

I keep getting compile errors... I think there might be an error in my context.xml, or I havent put the correct jar in the lib directory, or maybe there is something missing from my javac command. How would you compile this?

In tomcat, structure is like this in my webapps folder:

TestDB > META-INF
> WEB-INF > classes > com > test > web
> model
My context.xml

<Context>
  <Resource name="jdbc/users"
	    auth="Container"
	    type="javax.sql.DataSource"
	    username="app"
	    password=""
	    driverClassName="org.apache.derby.jdbc.EmbeddedDriver"
	    url="jdbc:derby://localhost:1527/users"
	    maxActive="8"
	    maxIdle="4" />
</Context>

My web.xml

<web-app 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/web-app_2_4.xsd" 
	 version="2.4">

<resource-ref>
  <res-ref-name>
    jdbc/users
  </res-ref-name>
  <res-type>
    javax.sql.DataSource
  </res-type>
  <res-auth>
    Container
  </res-auth>
</resource-ref>

<servlet>
  <servlet-name>
    GetInfo
  </servlet-name>
  <servlet-class>
    com.test.web.ProcessForm
  </servlet-class>
</servlet>

<servlet-mapping>
  <servlet-name>
    GetInfo
  </servlet-name>
  <url-pattern>
    /FormProcess.do
  </url-pattern>
</servlet-mapping>

</web-app>

And this is my home page:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
        "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" lang="en" xml:lang="en">

<head>
  <title>
    Test
  </title>
</head>

<body>
  <form method="post" action="FormProcess.do">
    <input type="text" name="name" />
    <input type="submit" />
  </form>
</body>

</html>

And my servlet

package com.test.web;
import com.test.model.*;
import javax.servlet.*;
import javax.servlet.http.*;
import java.io.*;

public class ProcessForm extends HttpServlet
{
    public void doPost(HttpServletRequest request, HttpServletResponse response) throws
	                                                IOException, ServletException
    {
	String name = request.getParameter("name");
	request.setAttribute("fromServlet", name);
	RequestDispatcher view = request.getRequestDispatcher("result.jsp");
	view.forward(request, response);
    }
}

<html>
<head><title>JSP</title>
<body>
<%
String name = (String) request.getAttribute("fromServlet");
out.print("You typed " + name);
%>
</body>
</html>

If the class accessing the database didn't have anything in it, all this does is write out whatever you typed in the text box in the html page. It works, simple stuff really. Now lets say I try and initiate connections to my database, it doesn't compile. If i tried something really simple like this:

package com.test.model;
import com.test.web.*;
import java.sql.*;
import javax.sql.*;

public class AccessDB
{
    Context initCtx = new InitialContext();
}

when I compile it using this

javac -classpath .:classes:/home/ash/tomcat/lib/servlet-api.jar -d classes src/com/test/model/AccessDB.java

it gives the error

src/com/test/model/AccessDB.java:8: cannot find symbol
symbol  : class Context
location: class com.test.model.AccessDB
    Context initCtx = new InitialContext();
    ^
src/com/test/model/AccessDB.java:8: cannot find symbol
symbol  : class InitialContext
location: class com.test.model.AccessDB
    Context initCtx = new InitialContext();
                          ^
2 errors

[Bear Bibeault]

A 5-second Google search reveals which package needs to be imported.

[David Newton]

"Meet hot initial contexts in your area now"?!?!

[Bear Bibeault]

David Newton wrote:"Meet hot initial contexts in your area now"?!?!

I didn't say it was the highest-ranked link!

[ash lester]

Would that be uh,

import javax.naming.*

by any chance?

It is slightly annoying that the code on the tomcat site doesn't mention imports or that you need to try/catch / throw exceptions all over the place...

But I guess they aren't there to spoon feed us.

I'll post back with my next problem I'm sure...