Postgresql & JDBC - Requesting an Example

[Lowell Alleman]

Can someone give me a full JDBC example that accesses a Postgrsql sever?
Postgresql's documentation for the JDBC interface is rather vague.

I'm new to Java, and to postgresql...... So I'm having a hard time making
them talk together.

Anybody have a short & sweet "FULL" example to show me? (I'm
particularly interested in the part where the postgresql.driver is
loaded......)

Thanks!

Lowell Alleman
lca...@psu.edu

[Simon Brooke]

The only tricky bit is getting your pg_hba.conf set up right (that's
postgres' security permissions file) and ensuring you're running
postgres in net listening mode (that's the '-i' option to the
postmaster).

Here's the relevent bits from my pg_hba.conf:
----------------------------- cut here -------------------------------
host all 127.0.0.1 255.255.255.255 trust
host all 62.6.156.74 255.255.255.255 password
passwd
----------------------------- cut here -------------------------------

This says 'trust everyone from the local host; verify everyone
connecting from host 62.6.156.74 against the password file called
passwd'; read up on postgres security for more detail on this. To check
whether
you've got it right, try

psql -h your.db.host

If this gives you a password prompt, and, when you type your postgres
password, lets you in, then it will let your Java application in, too.

Then all you have to do in your JDBC application is to use
'org.postgresql.Driver' as the driver name, and
'jdbc://your.db.host/yourdb' as the database URL, and everything will
work.

--
si...@jasmine.org.uk (Simon Brooke) http://www.jasmine.org.uk/~simon/

'You cannot put "The Internet" into the Recycle Bin.'

[root]

/*
Here is the small piece of code that got me pointed in the right direction;
HTH.
(My apologies to whomever wrote this originally; I didn't get the name, but
Thanks!)
Notice that PostgreSQL uses port 5432. I use JDBC drivers from both the
Linux side and Win9x. This has been tested on PostgreSQL 7.03, and works on
my test "Customer" database which has an "address" table.
This is ONLY AN EXAMPLE! Don't be offended by any lack of detail.
Shan
*/
===========================================================
import java.sql.*;

class testdb {

private static final String defDriverName = "postgresql.Driver";
private static final String defUrl =
"jdbc:postgresql://linRH61:5432/Customer";
private static final String defUser = "anon";
private static final String defPwd = "guest";

public static void main(String[] args) throws ClassNotFoundException,
SQLException {
System.out.println("Attempting to load the JDBC driver...");

String driverName=defDriverName;
loadDriver(driverName);

System.out.println("Creating the db connection...");
java.sql.Connection conn = getConnection();

System.out.println("Making a query...");
Statement st = conn.createStatement();
ResultSet rs = st.executeQuery("SELECT * FROM address");
int ii = 0;
while(rs.next()) {
System.out.print("Row " + ++ii + " returned ");
System.out.print(rs.getString(1));
}
rs.close();
st.close();
conn.close();
}

private static boolean loadDriver(String driverName)
throws java.lang.ClassNotFoundException {
Class.forName(driverName);
boolean returnValue=false;
try {
DriverManager.registerDriver(new postgresql.Driver());
returnValue=true;
} catch (Exception e) {
returnValue=false;
}
return returnValue;
}

private static Connection getConnection
(String url, String user, String pwd) {
Connection conn=null;
try {
conn = DriverManager.getConnection(url,user,pwd);
} catch (java.sql.SQLException e) {
System.out.println("Cannot create connection: "+e);
}
return conn;
}

private static Connection getConnection(String url) {
return getConnection(url, defUser, defPwd);
}

private static Connection getConnection() {
return getConnection(defUrl, defUser, defPwd);
}

} // class testdb ends

===============================================================
Lowell Alleman wrote:

--
Shan Gill
shangill at polson.net
Software Development and Documentation
Get a hold on the future - get a grip on reality!

[Lowell Alleman]

Simon Brooke wrote:

actually, i got that remote thing to work. (the whole 'pg_hba.conf' deal),
i am just unable to have java load the correct driver.... using the

Class.forName("driver_name_goes_here");

stuff..

I'll post my java file, and let me know if you see any problems.....

[Lowell Alleman]

<posted & mailed>

Here is my Java file. It's just a modification of Sun's JDBC example from
their online documentation.

Listing: "Select.java"
---------------------->>>

import java.sql.*;

public class Select {

public static void main(String argv[]) {

try {
Class.forName("postgresql.Driver");
// I've tried "org.postgresql.Driver" also.
}
catch (ClassNotFoundException e)
{
// please excuse my "creative" error messages.....
System.out.print("Yo, dude.... like, we couldn't
find the stuff, dude!! ?\n");
e.printStackTrace();
}

try {

// Create a URL specifying an ODBC data source name.
String url = "jdbc:postgresql://laptop/auction";

// Connect to the database at that URL.
Connection con = DriverManager.getConnection(url,
"cbic","");

// Execute a SELECT statement
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT ZipCode,
City, State FROM Locations");

// Step through the result rows.
System.out.println("Got results:");
while (rs.next()) {
// get the values from the current row:
int ZipCode = rs.getInt(1);
String City = rs.getString(2);
String State = rs.getString(3);

// Now print out the results:
System.out.print("Zip = " + ZipCode);
System.out.print("State = " + State);
System.out.print("City = " + City);
System.out.print("\n");
}

stmt.close();
con.close();
} catch (java.lang.Exception ex) {
ex.printStackTrace();
}
}
}

<<<----------------------

I downloaded the JDBC package from the postgresql web site and compiled it
using make, as instructed. I then copied the postgresql.jar file to the
current directory, with the rest of my source code (Select.java).

Here are the commands I entered:

[lowell@lowell Auction]$ export CLASSPATH=.:/usr/java/jdk1.3/lib/
[lowell@lowell Auction]$ javac Select.java
[lowell@lowell Auction]$ java Select
Yo, dude.... like, we couldn't find the stuff, dude!!?
java.lang.ClassNotFoundException: postgresql/Driver
at java.lang.Class.forName(Class.java:native)
at java.lang.Class.forName(Class.java:52)
at Select.main(Select.java:9)
java.sql.SQLException: driver not found: jdbc:postgresql://laptop/auction
at java.lang.Throwable.fillInStackTrace(Throwable.java:native)
at java.lang.Throwable.<init>(Throwable.java:38)
at java.lang.Exception.<init>(Exception.java:24)
at java.sql.SQLException.<init>(SQLException.java:22)
at java.sql.SQLException.<init>(SQLException.java:33)
at java.sql.DriverManager.getConnection(DriverManager.java:67)
at java.sql.DriverManager.getConnection(DriverManager.java:49)
at Select.main(Select.java:24)
[lowell@lowell Auction]$

When I try using the postgresql-x.xx.jar (x-xx represents various version
numbers), which was include with my distro (/usr/lib/pgsql/), I get a
slightly different error message.

After changing the CLASSPATH , I try to run it again, and this time I get
this error message:

Internal error: caught an unexpected exception.
Please check your CLASSPATH and your installation.
java/lang/ClassNotFoundException: Select
at java.lang.Class.forName(Class.java:native)
at java.lang.Class.forName(Class.java:52)
Aborted

Any thoughts? I'm great with C++, but this "new-fangled" Java stuff is
just too complicated!!! Am I just misinterpreting the error messages? Am
I SPELLING something wrong!?!?!

Thanks again!

Lowell C. Alleman
lca...@psu.edu

[tewcs]

>actually, i got that remote thing to work. (the
whole 'pg_hba.conf' deal),
>i am just unable to have java load the correct driver.... using
the
>
> Class.forName("driver_name_goes_here");
>

hi, when i run my java codes in Linux, i use
Class.forName("postgresql.Driver");
and it works fine.

but running from PC windows (on tomcat) i have to use
Class.forName("org.postgresql.Driver");

just need 'org' infront - it works for me.

[Lowell Alleman]

tewcs wrote:

I tried that too, but it didn't seem to work either. (I haven't tried any
of this stuff from windows yet, so that may work.. but it has to work from
linux sometime... and this is driver is a JDBC class 3 (is 3 right?),
which means it's fully implemented in Java, so it should very
cross-platform capable.

would you mind taking a look at my other post? (i put it up higher in the
thread), any ideas on the error message(s) I'm getting?

[brl...@my-deja.com]

Lowell Alleman <lca...@psu.edu> writes:

> java/lang/ClassNotFoundException: Select

This probably means that . was not in your CLASSPATH. Try it with both
the postgresql jar and . in your CLASSPATH.

--
Bruce R. Lewis http://brl.sourceforge.net/
I rarely read mail sent to brl...@my-deja.com

[Shan J. Gill]

OK, just checkin' to be sure we start at the same place, so two things:
1) when you dump the postgresql.jar file, what is the path name for
Driver.class? My Windows JDBC driver has a path of "postgresql", while the
Linux one has a path of "org.postgresql", so that is something to look for -
I guess it depends on the layout of the classes on the system where the
driver was compiled.
2) not sure if this applies in your case - can't remember just now - but
postgresql uses port 5432 to broadcast on, so the line for the database
connection usually contains that port number following the database host
name, like this:
String url = "jdbc:postgresql://localhost:5432/auction";
or if the machine's name is "laptop", then:
String url = "jdbc:postgresql://laptop:5432/auction";

And on a related note, if you are accessing the database from a different
machine, you will need to start the postmaster with the "-i" option to get
the TCP/IP stuff working.

Shan

Lowell Alleman <lca...@psu.edu> wrote in message
news:kyBr6.34$hT7.13...@twister2.starband.net...

[Lowell Alleman]

brl...@my-deja.com wrote:

> Lowell Alleman <lca...@psu.edu> writes:
>
>> java/lang/ClassNotFoundException: Select
>
> This probably means that . was not in your CLASSPATH. Try it with both
> the postgresql jar and . in your CLASSPATH.
>

ok, i tried that... shown here:

[lowell@lowell Auction]$ echo $CLASSPATH
.:/usr/java/jdk1.3/lib:postgresql.jar
[lowell@lowell Auction]$

(I also tried using the full path "/home/lowell/Auction/postgresql.jar",
but I'm still getting the exact same results as before.)

java.lang.ClassNotFoundException: postgresql.Driver
at java.net.URLClassLoader$1.run(URLClassLoader.java:200)
at java.security.AccessController.doPrivileged(Native Method)
at java.net.URLClassLoader.findClass(URLClassLoader.java:188)
at java.lang.ClassLoader.loadClass(ClassLoader.java:297)
at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:286)
at java.lang.ClassLoader.loadClass(ClassLoader.java:253)
at java.lang.ClassLoader.loadClassInternal(ClassLoader.java:313)
at java.lang.Class.forName0(Native Method)
at java.lang.Class.forName(Class.java:120)
at Select.main(Select.java:9)
java.sql.SQLException: No suitable driver
at java.sql.DriverManager.getConnection(DriverManager.java:537)
at java.sql.DriverManager.getConnection(DriverManager.java:177)
at Select.main(Select.java:24)
[lowell@lowell Auction]$ echo $CLASSPATH
.:/usr/java/jdk1.3/lib:postgresql.jar:/home/lowell/Auction/postgresql.jar
[lowell@lowell Auction]$

[optimusprimal]

Hi, have you got your driver problem solved? Cause if not then I
might be able to help you out

Regards

[brl...@my-deja.com]

Lowell Alleman <lca...@psu.edu> writes:

> [lowell@lowell Auction]$ echo $CLASSPATH
> .:/usr/java/jdk1.3/lib:postgresql.jar
> [lowell@lowell Auction]$
>
> (I also tried using the full path "/home/lowell/Auction/postgresql.jar",
> but I'm still getting the exact same results as before.)
>
> java.lang.ClassNotFoundException: postgresql.Driver

Perhaps the class is org.postgresql.Driver

Try this

jar tvf postgresql.jar | grep Driver.class

If you get org/postgresql/Driver, then you should use the class
org.postgresql.Driver