Postgres Pro
Downloads
Home   >   mailing lists

Thread: jdbc servlets and jsp

jdbc servlets and jsp

From
Luke Vanderfluit
Date:
Hi,

I'm having a few probs (fun) getting jdbc to work in servlets and jsp,
tomcat in other words.

I've successfully got jdbc working with postgresql in a regular java
class.

I have tried using the same code adapted to a servlet and jsp to get a
database connection happening from there, however no luck,

Is there anything I need to set up in server.xml or web.xml before it
can work?

here is my jsp and servlet code:
################################
jsp file
-=-=-=-=
<html>
<head>
</head>
<%@ page language="java" import="java.sql.*" %>
<body>
<%

Class.forName("org.postgresql.Driver");
Connection myConn=DriverManager.getConnection("jdbc:postgresql:mboard",
"luke", "");

%>
</body>
</html>
=-=-=-=-=-=-=-=-=-=-=-=-=-=
servlet code
=-=-=-=-=-=-=-=-=-=-=-=-=-=
import javax.servlet.*;
import javax.servlet.http.*;
import java.io.*;
import java.sql.*;
import java.text.DateFormat;

/**
 * ShowEmployees creates an HTML table containing a list of all
 * employees (sorted by last name) and the departments to which
 * they belong.
 */
public class ShowEmployees extends HttpServlet
{
  Connection dbConn = null;

  /**
   * Establishes a connection to the database.
   */
  public void init() throws ServletException
  {
    String jdbcDriver = "org.postgresql.Driver";
    String dbURL = "\"jdbc:postgresql:mboard\", \"luke\", \"\"";

    try
    {
      Class.forName("org.postgresql.Driver").newInstance(); //load
driver
      dbConn = DriverManager.getConnection("jdbc:postgresql:megaboard",
"luke", ""); //connect
    }
    catch (ClassNotFoundException e)
    {
      throw new UnavailableException("JDBC driver not found:" +
        jdbcDriver);
    }
    catch (SQLException e)
    {
      throw new UnavailableException("Unable to connect to: " +
        dbURL);
    }
    catch (Exception e)
    {
      throw new UnavailableException("Error: " + e);
    }
  }

  /**
   * Displays the employees table.
   */
  public void service(HttpServletRequest request,
    HttpServletResponse response) throws ServletException,
    IOException
  {
    response.setContentType("text/html");

    PrintWriter out = response.getWriter();

    try
    {
      //join EMPLOYEE and DEPARTMENT tables to get all data
      String sql = "select * from message;";

      Statement stmt = dbConn.createStatement();
      ResultSet rs = stmt.executeQuery(sql);

      out.println("<HTML>");
      out.println("<HEAD><TITLE>Show Employees</TITLE></HEAD>");
      out.println("<BODY>");
      out.println("<TABLE BORDER=\"1\" CELLPADDING=\"3\">");
      out.println("<TR>");
      out.println("<TH>Name</TH>");
      out.println("<TH>Department</TH>");
      out.println("<TH>Phone</TH>");
      out.println("<TH>Email</TH>");
      out.println("<TH>Hire Date</TH>");
      out.println("</TR>");

      while (rs.next())
      {
        out.println("<TR>");

        out.println("<TD>" + rs.getString("resusername") + "</td>");

        out.println("</TR>");
      }

      out.println("</TABLE>");
      out.println("</BODY></HTML>");

      rs.close();
      stmt.close();
    }
    catch (SQLException e)
    {
      out.println("<H2>Database currently unavailable.</H2>");
    }

    out.close();
  }
}

any help would be greatly appreciated.
thanks,
kind regards
Luke

--

====================================
"when my computer smiles, I'm happy"
===============================.~ ~,
Luke Vanderfluit               |'/']
Mobile: 0421 276 282            \~/`

Re: jdbc servlets and jsp

From
Paul Thomas
Date:
On 08/09/2003 20:52 Luke Vanderfluit wrote:
> Hi,
>
> I'm having a few probs (fun) getting jdbc to work in servlets and jsp,
> tomcat in other words.
>
> I've successfully got jdbc working with postgresql in a regular java
> class.
>
> I have tried using the same code adapted to a servlet and jsp to get a
> database connection happening from there, however no luck,
>
> Is there anything I need to set up in server.xml or web.xml before it
> can work?
[snip]

I'm not sure why you would want to load the JDBC driver in your JSP. JSPs
are part of the view layer. Your JDBC stuff should be way back in the
persistence layer. Even servlets are really too far forward for this - use
then more as controllers. Personally, I use JSP/Struts for the
view/controller and obtain back-end connections using JNDI. The datasource
name is provided as a servlet parameter in web.xml and is passed to the
data layer on session creation. You might find it useful to look at the
j2ee design patterns at java.sun.com.

HTH

--
Paul Thomas
+------------------------------+---------------------------------------------+
| Thomas Micro Systems Limited | Software Solutions for the Smaller
Business |
| Computer Consultants         |
http://www.thomas-micro-systems-ltd.co.uk   |
+------------------------------+---------------------------------------------+

Re: jdbc servlets and jsp

From
Luke Vanderfluit
Date:
Hi Paul,

thanks for your reply :-)
I'm a student trying to get java talking to a postgresql database.
My first step is to get the several components talking to each other.
So far I have got regular java classes talking to the database,
my question was about getting jsp and servlets talking to the database
using tomcat. That should be possible, though not desirable in a
production environment perhaps, as you pointed out.

Interesting to hear about your setup,

kind regards,
Luke

> I'm not sure why you would want to load the JDBC driver in your JSP. JSPs
> are part of the view layer. Your JDBC stuff should be way back in the
> persistence layer. Even servlets are really too far forward for this - use
> then more as controllers. Personally, I use JSP/Struts for the
> view/controller and obtain back-end connections using JNDI. The datasource
> name is provided as a servlet parameter in web.xml and is passed to the
> data layer on session creation. You might find it useful to look at the
> j2ee design patterns at java.sun.com.

--
====================================
"when my computer smiles, I'm happy"
===============================.~ ~,
Luke Vanderfluit               |'/']
Mobile: 0421 276 282            \~/`

Re: jdbc servlets and jsp

From
"Nick Fankhauser"
Date:
Luke-

Are you getting an error message? Any clues other than "just don't work"?

One common problem in moving from interactive java code at the command line
to servlets/JSP is that people forget that the tomcat code will execute as
the web user (typically www_data in apache installs)

This means you have to make sure that this user has access to the database &
has the jdbc driver in the classpath, etc...

I have a tiny little FAQ for postgresql jdbc & tomcat that addresses two
common problems here:

http://www.fankhausers.com/tomcat/jdbc/


-Nick

> -----Original Message-----
> From: pgsql-jdbc-owner@postgresql.org
> [mailto:pgsql-jdbc-owner@postgresql.org]On Behalf Of Luke Vanderfluit
> Sent: Monday, September 08, 2003 2:52 PM
> To: pgsql-jdbc@postgresql.org
> Subject: [JDBC] jdbc servlets and jsp
>
>
> Hi,
>
> I'm having a few probs (fun) getting jdbc to work in servlets and jsp,
> tomcat in other words.
>
> I've successfully got jdbc working with postgresql in a regular java
> class.
>
> I have tried using the same code adapted to a servlet and jsp to get a
> database connection happening from there, however no luck,
>
> Is there anything I need to set up in server.xml or web.xml before it
> can work?
>
> here is my jsp and servlet code:
> ################################
> jsp file
> -=-=-=-=
> <html>
> <head>
> </head>
> <%@ page language="java" import="java.sql.*" %>
> <body>
> <%
>
> Class.forName("org.postgresql.Driver");
> Connection myConn=DriverManager.getConnection("jdbc:postgresql:mboard",
> "luke", "");
>
> %>
> </body>
> </html>
> =-=-=-=-=-=-=-=-=-=-=-=-=-=
> servlet code
> =-=-=-=-=-=-=-=-=-=-=-=-=-=
> import javax.servlet.*;
> import javax.servlet.http.*;
> import java.io.*;
> import java.sql.*;
> import java.text.DateFormat;
>
> /**
>  * ShowEmployees creates an HTML table containing a list of all
>  * employees (sorted by last name) and the departments to which
>  * they belong.
>  */
> public class ShowEmployees extends HttpServlet
> {
>   Connection dbConn = null;
>
>   /**
>    * Establishes a connection to the database.
>    */
>   public void init() throws ServletException
>   {
>     String jdbcDriver = "org.postgresql.Driver";
>     String dbURL = "\"jdbc:postgresql:mboard\", \"luke\", \"\"";
>
>     try
>     {
>       Class.forName("org.postgresql.Driver").newInstance(); //load
> driver
>       dbConn = DriverManager.getConnection("jdbc:postgresql:megaboard",
> "luke", ""); //connect
>     }
>     catch (ClassNotFoundException e)
>     {
>       throw new UnavailableException("JDBC driver not found:" +
>         jdbcDriver);
>     }
>     catch (SQLException e)
>     {
>       throw new UnavailableException("Unable to connect to: " +
>         dbURL);
>     }
>     catch (Exception e)
>     {
>       throw new UnavailableException("Error: " + e);
>     }
>   }
>
>   /**
>    * Displays the employees table.
>    */
>   public void service(HttpServletRequest request,
>     HttpServletResponse response) throws ServletException,
>     IOException
>   {
>     response.setContentType("text/html");
>
>     PrintWriter out = response.getWriter();
>
>     try
>     {
>       //join EMPLOYEE and DEPARTMENT tables to get all data
>       String sql = "select * from message;";
>
>       Statement stmt = dbConn.createStatement();
>       ResultSet rs = stmt.executeQuery(sql);
>
>       out.println("<HTML>");
>       out.println("<HEAD><TITLE>Show Employees</TITLE></HEAD>");
>       out.println("<BODY>");
>       out.println("<TABLE BORDER=\"1\" CELLPADDING=\"3\">");
>       out.println("<TR>");
>       out.println("<TH>Name</TH>");
>       out.println("<TH>Department</TH>");
>       out.println("<TH>Phone</TH>");
>       out.println("<TH>Email</TH>");
>       out.println("<TH>Hire Date</TH>");
>       out.println("</TR>");
>
>       while (rs.next())
>       {
>         out.println("<TR>");
>
>         out.println("<TD>" + rs.getString("resusername") + "</td>");
>
>         out.println("</TR>");
>       }
>
>       out.println("</TABLE>");
>       out.println("</BODY></HTML>");
>
>       rs.close();
>       stmt.close();
>     }
>     catch (SQLException e)
>     {
>       out.println("<H2>Database currently unavailable.</H2>");
>     }
>
>     out.close();
>   }
> }
>
> any help would be greatly appreciated.
> thanks,
> kind regards
> Luke
>
> --
>
> ====================================
> "when my computer smiles, I'm happy"
> ===============================.~ ~,
> Luke Vanderfluit               |'/']
> Mobile: 0421 276 282            \~/`
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>       joining column's datatypes do not match
>

Re: jdbc servlets and jsp

From
Paul Thomas
Date:
On 09/09/2003 04:16 Luke Vanderfluit wrote:
> thanks for your reply :-)
> I'm a student trying to get java talking to a postgresql database.
> My first step is to get the several components talking to each other.
> So far I have got regular java classes talking to the database,
> my question was about getting jsp and servlets talking to the database
> using tomcat. That should be possible, though not desirable in a
> production environment perhaps, as you pointed out.

Well, if your doing something simpler that a full-blown j2ee application,
I'd suggest loading the driver in your servlet's init method. Copy (or
symlink) the JDBC driver into Tomcat's common/lib directory and you should
be fine. I'd suggest putting the db url/userid/password in web.xml as
parameters to your servlet.

HTH

--
Paul Thomas
+------------------------------+---------------------------------------------+
| Thomas Micro Systems Limited | Software Solutions for the Smaller
Business |
| Computer Consultants         |
http://www.thomas-micro-systems-ltd.co.uk   |
+------------------------------+---------------------------------------------+