PostgreSQL/Eclipse/Tomcat - How to connect to DB?

[Thomas Hoheneder]

Hello,

I am new to JDBC and Postgres and I want to get a connection to a Postgres
database from a Java servlet.
I have done the following things.
1) Installed Postgres 8.0 Beta 2
2) Copied the JDBC driver "pg74.215.jdbc3.jar" into the WEB-INF\lib
directory under my application path in Tomcat
3) Added the "pg74.215.jdbc3.jar" to my Eclipse project
4) Created a Java class "DatabaseConnectivityServlet" within my project (see
code below)
I use a Class.forName() and a DriverManager.getConnection() instruction, but
both do not compile well. I am not sure if my arguments to these functions
are right. Of cource, the last two arguments of getConnection() must be the
db user name and password. And template1 is my database name for the first
step.

The main problem now is, that my code doesn't ccompile well, and so I don't
have a connection to the Postgres DB.
Why? What do I have to do now?
Any help to this would be very appreciated. Thanks in advance.

Nice greetings from
Thomas


DatabaseConnectivityServlet.java
-----------------------------------------------
package de.th.test;

import javax.servlet.*;
import javax.servlet.http.*;
import java.io.*;
import java.sql.*;

public class DatabaseConnectivityServlet extends HttpServlet {

public void doGet(HttpServletRequest request,
HttpServletResponse response) throws ServletException,
IOException {
Class.forName("org.postgresql.Driver");
Connection connection = DriverManager.getConnection("" +
"jdbcostgresql://127.0.0.1:5432/template1", "", "");
}
}

 

[John C. Bollinger]

I am new to JDBC and Postgres and I want to get a connection to a Postgres
database from a Java servlet.
I have done the following things.
1) Installed Postgres 8.0 Beta 2
Fine.

2) Copied the JDBC driver "pg74.215.jdbc3.jar" into the WEB-INF\lib
directory under my application path in Tomcat
Good.

3) Added the "pg74.215.jdbc3.jar" to my Eclipse project

Probably not necessary, but shouldn't cause a problem.

4) Created a Java class "DatabaseConnectivityServlet" within my project (see
code below)
I use a Class.forName() and a DriverManager.getConnection() instruction, but
both do not compile well.

Define "do not compile well". Do you get a compilation error? If so,
then something is dreadfully wrong.

I am not sure if my arguments to these functions
are right. Of cource, the last two arguments of getConnection() must be the
db user name and password. And template1 is my database name for the first
step.

The documentation for the PostgreSQL JDBC driver should indicate which
is the appropriate driver class. The name you are using looks
reasonable, but that doesn't mean it's right.

The driver documentation should also indicate the correct form for the
DB URL to pass to DriverManager.getConnection(). If you do not use a
form that the driver recognizes then your connection attempt will surely
fail. Furthermore, you must be sure that you are attempting to connect
to the IP port that Postgres is running on, which will be part of the
server configuration.

Also, most database servers support multiple databases, so I am not
entirely sure what you mean when you say that "template1" is a database
name from server installation time. Did the installer offer you an
option to create a database? Otherwise, you will probably have to use
the Postgres tools to create the database you want to use before you can
connect to it.

The main problem now is, that my code doesn't ccompile well, and so I don't
have a connection to the Postgres DB.

Again with the not compiling well. Post the error message(s) you get if
you want any specific help.

Why? What do I have to do now?
Any help to this would be very appreciated. Thanks in advance.

It's not clear to me whether or not you are successfully compiling the
servlet, but the code you provide looks okay to me. It will not,
however, do anything with any DB connection it manages to obtain. How
are you testing?

DatabaseConnectivityServlet.java
-----------------------------------------------
package de.th.test;

import javax.servlet.*;
import javax.servlet.http.*;
import java.io.*;
import java.sql.*;

public class DatabaseConnectivityServlet extends HttpServlet {

public void doGet(HttpServletRequest request,
HttpServletResponse response) throws ServletException,
IOException {
Class.forName("org.postgresql.Driver");

You do not need to execute Class.forName() on each request. Put it in
the servlet's init() method instead. The form of the method invocation
appears right, though, and the multiple invocations will not prevent
obtaining a connection.

Connection connection = DriverManager.getConnection("" +
"jdbcostgresql://127.0.0.1:5432/template1", "", "");

If you do not need to provide username and password then you should
probably use the one-argument version of getConnection().

}
}

You really haven't given us much to go on. It would help a lot if we
could see the relevant error messages from the console and / or log file.


John Bollinger
(e-mail address removed)

 

[Thomas Hoheneder]

Define "do not compile well". Do you get a compilation error? If so,

then something is dreadfully wrong.

In eclipse it's the standard behaviour that code compiles always
"automaticallly" when the source file is saved. When something could not be
compiled well or could not be recognized as correct code, then Eclipse
underlines the appropriate section in red colour. This I have meant by "does
not compile well".

Also, most database servers support multiple databases, so I am not
entirely sure what you mean when you say that "template1" is a database
name from server installation time. Did the installer offer you an
option to create a database? Otherwise, you will probably have to use
the Postgres tools to create the database you want to use before you can
connect to it.

Within the installation process of Postgres 8.0 two sample databases are
automatically created and they are named "template0" and "template1". To
test my db connection, I first wanted to connect to "template1" before
creating an own database.

Again with the not compiling well. Post the error message(s) you get if
you want any specific help.

In the meantime I got some information from Eclipse when moving the mouse
cursor above the red underlined code. Eclipse showed me a tool tip where it
said "Unhandeled exception type ClassNotFoundException" at the forName()
call and "Unhandeled exception type SQLException" at the getConnection()
call. This brought me to the idea that I should use a try catch block for
both instructions and catch the appropriate exceptions. And - this was the
solution. Now it works. And now I did it with an own database named
"registration".
But nevertheless thank you very much for your help.

The code now is:

try {
Class.forName("org.postgresql.Driver");
Connection con = DriverManager.getConnection("" +
"jdbcostgresql://127.0.0.1:5432/registration",
"<username>", "<password>");
}
catch (ClassNotFoundException cnfe) {
System.out.println(cnfe.toString());
}
catch (SQLException sqle) {
System.out.println(sqle.toString());
}

Nice greetings from
Thomas

 

[Bryan Castillo]

Thomas Hoheneder wrote:

try {
Class.forName("org.postgresql.Driver");
Connection con = DriverManager.getConnection("" +
"jdbcostgresql://127.0.0.1:5432/registration",
"<username>", "<password>");
}
catch (ClassNotFoundException cnfe) {
System.out.println(cnfe.toString());
}
catch (SQLException sqle) {
System.out.println(sqle.toString());
}

I'm sure you got that to compile, but did it work?
I didn't think that JDBC drivers registered themselves properly when
their classes are loaded from a class loader different from the system
class loader. (Since you put the postgress jar in WEB-INF/lib it is
probably under a custom class loader created by your J2EE container.)
I believe Tomcat docs say to put jdbc driver jars into one of tomcats
lib directories, I forget which one now.

Anyway, you might want to read about Tomcats connection pooling.
You can set up a database connection pool through its UI and
get connections through JNDI. Thats the probably a better way
to retrieve database connections for your servlet. It will also
keep hardcoded usernames and passwords out of your source files.

 

[Thomas Hoheneder]

try {

I'm sure you got that to compile, but did it work?

Yes, this worked. My only mistake was that I forgot to catch the
ClassNotFoundException and SQLEsception.

I didn't think that JDBC drivers registered themselves properly when
their classes are loaded from a class loader different from the system
class loader. (Since you put the postgress jar in WEB-INF/lib it is
probably under a custom class loader created by your J2EE container.)
I believe Tomcat docs say to put jdbc driver jars into one of tomcats
lib directories, I forget which one now.

Hmm... I have a book and it said to put the jdbc driver jar into the
WEB-INF\lib directory - and this workes fine.

Anyway, you might want to read about Tomcats connection pooling.
You can set up a database connection pool through its UI and
get connections through JNDI. Thats the probably a better way
to retrieve database connections for your servlet. It will also
keep hardcoded usernames and passwords out of your source files.

Ok, connection pooling is another chapter in my book as far as I have seen.
For the first step I do it with hardcoded user name and password. But you're
right, this is of cource no professional way.

Nice greetings from
Thomas