Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 508
  • Last Modified:

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

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
deepanp
Asked:
deepanp
  • 4
  • 3
2 Solutions
 
gheistCommented:
You have to add jdbc jar file somewhere in classpath...
0
 
earth man2Commented:
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
 
earth man2Commented:
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
deepanpAuthor Commented:
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
 
earth man2Commented:
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
 
deepanpAuthor Commented:
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
 
deepanpAuthor Commented:
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
 
deepanpAuthor Commented:
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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now