Solved

Error connecting to Postgresql via JDBC!!! PLEASE HELP, POINTS AWARDED!!

Posted on 2006-11-14
8
497 Views
Last Modified: 2008-01-09
Its bin a while since i have post an issue,:). Neways back to the problem. I have been trying to connect to my database in Postgresql via using JDBC. However i am finding it difficult:

>>I have installed the JDBC drivers and have a java code which simply tries to connect to the database.
>>Once connected the program should show that task was completed, however i get the following response after compiling and trying to run the java file.

Note "//" comments!

C:\>java HelloPostgresql postgres postgres ****** // this is password to database!
***Exception:
java.lang.ClassNotFoundException: org.postgresql.Driver
java.lang.ClassNotFoundException: org.postgresql.Driver
        at java.net.URLClassLoader$1.run(Unknown Source)
        at java.security.AccessController.doPrivileged(Native Method)
        at java.net.URLClassLoader.findClass(Unknown Source)
        at java.lang.ClassLoader.loadClass(Unknown Source)
        at sun.misc.Launcher$AppClassLoader.loadClass(Unknown Source)
        at java.lang.ClassLoader.loadClass(Unknown Source)
        at java.lang.ClassLoader.loadClassInternal(Unknown Source)
        at java.lang.Class.forName0(Native Method)
        at java.lang.Class.forName(Unknown Source)
        at HelloPostgresql.<init>(HelloPostgresql.java:20)
        at HelloPostgresql.main(HelloPostgresql.java:102)

The Java file Postgresql is as follows:

import java.sql.*;   // All we need for JDBC
import java.text.*;
import java.io.*;

public class HelloPostgresql
{
  Connection       db;        // A connection to the database
  Statement        sql;       // Our statement to run queries with
  DatabaseMetaData dbmd;      // This is basically info the driver delivers
                              // about the DB it just connected to. I use
                              // it to get the DB version to confirm the
                              // connection in this example.

  public HelloPostgresql(String argv[])
    throws ClassNotFoundException, SQLException
  {
    String database = argv[0];
    String username = argv[1];
    String password = argv[2];
    Class.forName("org.postgresql.Driver"); //load the driver
    db = DriverManager.getConnection("jdbc:postgresql:"+database,
                                     username,
                                     password); //connect to the db
    dbmd = db.getMetaData(); //get MetaData to confirm connection
    System.out.println("Connection to "+dbmd.getDatabaseProductName()+" "+
                       dbmd.getDatabaseProductVersion()+" successful.\n");
    sql = db.createStatement(); //create a statement that we can use later


    String sqlText = "create table jdbc_demo (code int, text varchar(20))";
    System.out.println("Executing this command: "+sqlText+"\n");
    sql.executeUpdate(sqlText);

 
    sqlText = "insert into jdbc_demo values (1,'One')";
    System.out.println("Executing this command: "+sqlText+"\n");
    sql.executeUpdate(sqlText);

 
    sqlText = "insert into jdbc_demo values (3,'Four')";
    System.out.println("Executing this command twice: "+sqlText+"\n");
    sql.executeUpdate(sqlText);
    sql.executeUpdate(sqlText);


    sqlText = "update jdbc_demo set text = 'Three' where code = 3";
    System.out.println("Executing this command: "+sqlText+"\n");
    sql.executeUpdate(sqlText);
    System.out.println (sql.getUpdateCount()+
                        " rows were update by this statement\n");


    System.out.println("\n\nNow demostrating a prepared statement...");
    sqlText = "insert into jdbc_demo values (?,?)";
    System.out.println("The Statement looks like this: "+sqlText+"\n");
    System.out.println("Looping three times filling in the fields...\n");
    PreparedStatement ps = db.prepareStatement(sqlText);
    for (int i=10;i<13;i++)
    {
      System.out.println(i+"...\n");
      ps.setInt(1,i);         //set column one (code) to i
      ps.setString(2,"HiHo"); //Column two gets a string
      ps.executeUpdate();
    }
    ps.close();

 
    System.out.println("Now executing the command: "+
                       "select * from jdbc_demo");
    ResultSet results = sql.executeQuery("select * from jdbc_demo");
    if (results != null)
    {
      while (results.next())
      {
        System.out.println("code = "+results.getInt("code")+
                           "; text = "+results.getString(2)+"\n");
      }
    }
    results.close();


    sqlText = "drop table jdbc_demo";
    System.out.println("Executing this command: "+sqlText+"\n");
    sql.executeUpdate(sqlText);


    db.close();
  }

  public static void correctUsage()
  {
    System.out.println("\nIncorrect number of arguments.\nUsage:\n "+
                       "java   \n");
    System.exit(1);
  }

  public static void main (String args[])
  {
    if (args.length != 3) correctUsage();
    try
    {
      HelloPostgresql demo = new HelloPostgresql(args);
    }
    catch (Exception ex)
    {
      System.out.println("***Exception:\n"+ex);
      ex.printStackTrace();
    }
  }
}


Thanks for the help in advance, will view replies at 12pm tomorrow.

Thanks Again Experts!!

:D
0
Comment
Question by:deepanp
  • 4
  • 3
8 Comments
 
LVL 61

Accepted Solution

by:
gheist earned 250 total points
ID: 17944819
You have to add jdbc jar file somewhere in classpath...
0
 
LVL 22

Assisted Solution

by:earth man2
earth man2 earned 250 total points
ID: 17945528
I don't know what your database url has been input as but it needs to include host and port eg.
If you specify CLASSPATH then you need to include the current directory - to find HelloPostgresql.class

db = DriverManager.getConnection("jdbc:postgresql://localhost:5432/"+database,
                                     username,
                                     password);

java  -cp .:$JAVA_HOME/jre/lib/ext/postgresql.jar HelloPostgresql mydbname myusername mypassword
Connection to PostgreSQL 8.2beta2 successful.

Executing this command: create table jdbc_demo (code int, text varchar(20))

Executing this command: insert into jdbc_demo values (1,'One')

Executing this command twice: insert into jdbc_demo values (3,'Four')

Executing this command: update jdbc_demo set text = 'Three' where code = 3

2 rows were update by this statement



Now demostrating a prepared statement...
The Statement looks like this: insert into jdbc_demo values (?,?)

Looping three times filling in the fields...

10...

11...

12...

Now executing the command: select * from jdbc_demo
code = 1; text = One

code = 3; text = Three

code = 3; text = Three

code = 10; text = HiHo

code = 11; text = HiHo

code = 12; text = HiHo

Executing this command: drop table jdbc_demo
0
 
LVL 22

Expert Comment

by:earth man2
ID: 17945732
Though if you're running on same machine where the database is at and are using the default port then the url specified will work just fine.
0
 

Author Comment

by:deepanp
ID: 17946995
yes i am only using the same machine for the database, and the port details are set to default. The classpath was added into the system as well with the jar jdbc. thanks earthman2, i will have a go and post the result in a few minutes.

Thanks again for the help!
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 22

Expert Comment

by:earth man2
ID: 17947117
To make the code portable between databases specify complete url  and omit the line
Class.forName("org.postgresql.Driver"); //load the driver

add command line option -Djdbc.Drivers

java -Djdbc.Drivers=org.postgresql.Driver  -cp .:$JAVA_HOME/jre/lib/ext/postgresql.jar HelloPostgresql jdbc:postgresql://localhost:5432/mydbname myusername mypassword
0
 

Author Comment

by:deepanp
ID: 17948762
earthman2,

I tried to execute the code with the line

 db = DriverManager.getConnection("jdbc:postgresql://localhost:5432/"+database,
                                     username,
                                     password);

>>included
but there was no luck.

Let me show my settings for the classpaths, following:

SYSTEM CLASSPATH: C:\Program Files\Java\jre1.5.0_06\lib\ext\QTJava.zip;C:\Program Files\PostgreSQL\8.1\jdbc\postgresql-8.1-405.jdbc3.jar
JAVAHOME: C:\Program Files\Java\jdk1.5.0_06
QTJAVA: C:\Program Files\Java\jre1.5.0_06\lib\ext\QTJava.zip
VARIABLE CLASSPATH.;.;.;C:\PROGRA~1\JMF21~1.1E\lib\sound.jar;C:\PROGRA~1\JMF21~1.1E\lib\jmf.jar;C:\PROGRA~1\JMF21~1.1E\lib;C:\Program Files\PostgreSQL\8.1\jdbc\postgresql-8.1-405.jdbc3

I Hope this helps.
The java file is located: C:/

Thanks, will keep trying now
0
 

Author Comment

by:deepanp
ID: 17949107
http://www.fankhausers.com/postgresql/jdbc/#driver_download

This is a link where i got a step by step set up from. hope this helps for all that is helping out.

Thanks again!
0
 

Author Comment

by:deepanp
ID: 17949793
Hey GUYS.. I would like to thank all the help that you have given.. however it was infact a CLASSPATH problem.. To Future reviews with the same problem, I would like to say that i had Tomcat 4.1 installed. The problem was that the CLASSPATH has to point to the file where the lib folder of the JDK folder is. i.e. C:\Program Files\Java\jdk1.5.0_06\lib\postgresql-8.1-407.jdbc3.jar. and you also have to include the same class path in the user variables.

This is what made it work for me..

Thanks again..

Althought not much help was given, i will still award points, however equally in this instance.

Thanks Experts!!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Best database to use for Maps is PostgreSQL. This is an open source database. Comes as a package with most Linux OS. For more info visit the following site: http://www.postgresql.org/ (http://www.postgresql.org/) This requires some add-o…
Many developers have database experience, but are new to PostgreSQL. It has some truly inspiring capabilities. I have several years' experience with Microsoft's SQL Server. When I began working with MySQL, I wanted a quick-reference to MySQL (htt…
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now