PostgreSQL - Installing JDBC driver

Question

I'm having a hard time working out how I should be installing the JDBC driver for PostgreSQL on my debian 6.0 server. I have moved the driver .jar into the following directory:

/usr/local/pgsql/share/java/postgresql.jar. 

Then the tutorials talk about using this code:

Class.forName("org.postgresql.Driver");

However, since I am new to postgreSQL I have no idea where I should be putting this line, or if this is even correct.

My question is, short of moving the jar file to this location, what do I actually need to do in order to install the JDBC driver on my postgreSQL installation?

---

EDIT: This is my setup:

Server 1: Tomcat + SOLR

Server 2: PostgreSQL with JDBC driver

SOLR on server 1 queries postgreSQL on server 2 via the JDBC driver

Answer 1

Install all packages:

# apt-get install libpostgresql-jdbc-java libpostgresql-jdbc-java-doc

To set Java Environment for all users, add/edit /etc/environment:

JAVA_HOME="/usr/lib/jvm/java-8-openjdk-amd64"
CLASSPATH=".:/usr/share/java/mysql.jar:/usr/share/java/postgresql-jdbc4.jar"

Note: change /usr/lib/jvm/java-8-openjdk-amd64 with your JDK

Note: if you prefered to use postgresql-jdbc3, replace /usr/share/java/postgresql-jdbc4.jar with /usr/share/java/postgresql.jar

Test your connection using this code:

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

import java.util.Properties;

class TestDB {

    /*

      /usr/share/java

      http://dev.mysql.com/doc/connector-j/5.1/en/

      https://jdbc.postgresql.org/documentation/documentation.html

    */

   static Connection conn = null;   

   public static void main(String[] args) {
      // PostgreSQL

      try {

         System.out.println("Loading Class org.postgresql.Driver");

         Class.forName("org.postgresql.Driver");

         System.out.println("Loading org.postgresql.Driver Successful");

         String url = "jdbc:postgresql://localhost/database";

         Properties props = new Properties();

         props.setProperty("user","user");

         props.setProperty("password","password");

         props.setProperty("ssl","true");

         conn = DriverManager.getConnection(url, props); 

         // or

         url = "jdbc:postgresql://localhost/database?user=user&password=password&ssl=true";

         Connection conn = DriverManager.getConnection(url);

         // Do something with the Connection

         System.out.println("Test Connection Successful");

      } catch (SQLException ex) {

         // handle any errors

         System.out.println("SQLException: " + ex.getMessage());

         System.out.println("SQLState: " + ex.getSQLState());

         System.out.println("VendorError: " + ex.getErrorCode());

      } catch (ClassNotFoundException ex) {

         System.out.println("Class Not Found: " + ex.getMessage());

      }

   }

}

Note: change database, user and passwrod with your configuration

http://www.garasiku.web.id/web/joomla/index.php/java/112-debian-jessie-installing-openjdk-8-mysql-jdbc-and-postgresql-jdbc

Answer 2

It is best to install your PostgreSQL driver into tomcat\lib folder. Just copy the driver jar to PATH_TO_TOMCAT\lib

It is not a good idea to add things to the system CLASSPATH because you can end in class loader hell. Here is an example of how you end up in jar / classpath hell.

• Suppose the current app uses postgres 9.1 and you setup the driver on the system CLASSPATH
• You decide to run another app on that box which talks to a newer version of postgres lets say version 9.2
• Because you are using the system classpath app 2 will end up using the old driver because the SYSTEM classpath tends to take precedence over an applications classpath unless the app launcher script sets CLASSPATH="" to empty out the system classpath or uses a custom class loader that does not do parent-first class loading.

See http://tomcat.apache.org/tomcat-7.0-doc/jndi-datasource-examples-howto.html

Answer 3

That driver has to be on your classpath. You can use this command

java -cp /usr/local/pgsql/share/java/postgresql.jar my.app.MainClass

or you can copy the library into your project structure.

Then you can create connections as the tutorials say...