19

I'm working on a web project and I recently installed postgres 9.1.1

The postgresql server is up and running. I can connect via psql as usual and everything is loaded and properly saved from a dump of the db I made from 8.5.

So I also downloaded the JDBC4 driver for 9.1 postgres version here: http://jdbc.postgresql.org/download/postgresql-jdbc-9.1-901.src.tar.gz

I added it to the java build path using the project properties via eclipse.

This is the code I use to provide db connection to other classes (i.e. it's a singleton, I get a new connection only if the existing is either closed or null, from one object at a time only)

public abstract class DBConnection {
private static Connection connection = null;

public static void connect() {
    try {
        if (connection == null) {
            String host = "127.0.0.1";
            String database = "xxxxx";
            String username = "xxxxx";
            String password = "xxxxx";
            String url = "jdbc:postgresql://" + host + "/" + database;
            String driverJDBC = "org.postgresql.Driver";
            Class.forName(driverJDBC);
            connection = DriverManager.getConnection(url, username,
                    password); //line firing the class not found exception

        } else if (connection.isClosed()) {
            connection = null;
            connect();
        }
    } catch (SQLException e) {
        e.printStackTrace(System.err);
    } catch (Exception e) {
        e.printStackTrace(System.err);
    }
}

public static void disconnect() {
    if (connection != null) {
        try {
            connection.close();
        } catch (SQLException e) {
            Logger.getLogger(DBConnection.class.getName()).log(
                    Level.SEVERE, null, e);
        }
        }
    }

    public static Connection getConnection() {

        try {
            if (connection != null && !connection.isClosed()) {
                return connection;
            } else {
                connect();
                return connection;
            }
        } catch (SQLException e) {
            Logger.getLogger(DBConnection.class.getName()).log(Level.SEVERE,
                    null, e);
            return null;
        }
    }

    @Override
    public void finalize() {
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                Logger.getLogger(DBConnection.class.getName()).log(
                        Level.SEVERE, null, e);
            }
        }
    }

}

As I wrote in the title when I run the project and a class asks for a connection to this class I always get a Class Not Found Exception, Since it apparently can't load the org.postgresql.Driver.class The driver is located in a subfolder of the project ~/lib/org.postgresql-9.1-901.jdbc4.jar and as I said added to the build path via eclipse project properties.

I'm also providing a sample query to let see the usual behavior of my classes to access the DBConnection:

public static final User validateUserCredentials(String id, String pswd) {
    Connection connection = DBConnection.getConnection();
    Logger.getLogger(Credentials.class.getName()).log(Level.SEVERE, (connection!=null)?"connection not null":"connection null");
    Statement stmt = null;
    Logger.getLogger(Home.class.getName()).log(Level.SEVERE, "validating credentials for user: username : " + id + " password : " + pswd);
    String sql = "Select * from fuser where id = '" + id + "'";
    ResultSet resultset = null;
    try {
        stmt = connection.createStatement();
        resultset = stmt.executeQuery(sql);
        Logger.getLogger(Credentials.class.getName())
                .log(Level.SEVERE, sql);
        resultset.next();
        String password = resultset.getString("pswd");
        if (pswd.equals(password))
            return new User(id, pswd);
    } catch (SQLException ex) {

        Logger.getLogger(Credentials.class.getName()).log(Level.SEVERE,
                null, ex);
    } finally {
        if (stmt != null)
            stmt = null;

        if (resultset != null)
            resultset = null;
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {

            }
            connection = null;
        }
    }
    return null;
}
6
  • 1
    Recursion in your connect() method? Looks like a bad idea. Better is: if (connection == null || connection.isClosed()) { ... do connect ... }. Commented Oct 26, 2011 at 15:14
  • well it's not fancy to use, but I get the exception from the start and connection is initially null... I'm checking, though... I'm worried cause it used to work just fine until I upgraded to the version of postgres Commented Oct 26, 2011 at 15:22
  • I think you need to simplify; make connect() assume connection is null on input and make disconnect() set connection to null on output. Then add logic to getConnection() to check for connection == null or connection.isClosed() and call connect() and disconnect() as appropriate. The state of connection is then clearly 'owned' by getConnection(). Commented Oct 26, 2011 at 15:25
  • yeah but the problem is this line only: String driverJDBC = "org.postgresql.Driver"; Class.forName(driverJDBC); generating the exception the problem is ahead of managing the connection, it's on loading the driver in order to get it, even though I put it there and added it to java build path. I might have forgotten something in the configuration rather than coding. And yeah simplifyin my code ain't a bad idea too :) Commented Oct 26, 2011 at 15:41
  • I don't know Eclipse, but is the "build path" also used when running the application? Otherwise you might need to add the jar file to the "run path" as well. Commented Oct 26, 2011 at 15:55

4 Answers 4

28

I'm working on a web project and I recently installed postgres 9.1.1

...

I added it to the java build path using the project properties via eclipse.

That's the wrong way. That JAR has to be dropped straight in /WEB-INF/lib folder of the web project without fiddling with the Build Path in the project's properties. That folder is standard part of webapp's runtime classpath.


Unrelated to the concrete problem: you've a major design flaw in your DBConnection class. You've declared Connection as static which essentially makes your connection not threadsafe. Use a connection pool and never assign the Connection (nor Statement nor ResultSet) as a class/instance variable. They should be created and closed in the very same try-finally block as where you're executing the query. Further you've there also a SQL injection hole. Use PreparedStatement instead of concatenating user-controlled variables in the SQL string.

See also:

Sign up to request clarification or add additional context in comments.

4 Comments

thank you, I was dealing with so many changes I didn't notice. Yeah I know it's not threadsafe I needed something running in the short term thanks for the solution and the suggestions (It's my first web project an assignment of a course)!
You're welcome. I'd suggest to check the last one of the "See also" links for a more concrete example of whatever you're trying to achieve (you only have to replace dataSource.getConnection() by DriverManager.getConnection(url, username, password) if you don't intend using a connection pool yet).
@BalusC: **That JAR has to be dropped straight in /WEB-INF/lib folder **, what is there are more than one web application (war files), which are using this jar. Isin't it good to reduce the memory footprint by deploying the jar in server?
@Ankit: if you've full control over server, put it in server's common/shared lib.
5

Add this dependency in your pom:

    <dependency>
        <groupId>org.postgresql</groupId>
        <artifactId>postgresql</artifactId>
        <version>9.4-1203-jdbc4</version>
    </dependency>

Comments

3

The first thing I'd do is unpack the jar and confirm that the driver is really in there as org.postgresql.Driver. I notice when looking at jarfinder and related sites that there isn't a Postgres 9.x jar containing org.postgresql.Driver.

2 Comments

I've checked this already. Both from eclipse and via filemanager it's there, ~/org/postgresql/Driver.class is a 14,9 kB file :( thanks
Apparently jarfinder isn't looking very closely, because the corresponding jar files are easy to find: jdbc.postgresql.org/download.html
-1

If you have any issues where the jpa.properties.hibernate.format_sql isn't working either just make sure that you have your java jdk working so try running the project and IntelliJ will ask to fix it for you.

1 Comment

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.