Table of Contents

Here is sample code for a java program that connects to a PostgreSQL database.

YOU WILL NEED TO CHANGE <username> AND <password> TO MATCH YOUR PostgreSQL DATABASE USERNAME AND PASSWORD. Your username is your uid, the same name as you use logging into the Linux lab with, unless you are given a different username.

Note that the class name is DBCon, so it would have to be in a file called DBCon.java, unless you want to change the name.

This program connects to the database, runs the query, and iterates through a ResultSet containing the query results and displays the first item of each result to the screen.

You can find the PostgreSQL JDBC library at the main postgres JDBC library page

Make sure to have the appropriate PostgreSQL JDBC library in your CLASSPATH or loaded into your Java IDE. Details on including libraries in your Netbeans and Eclipse project are located here. 

import java.io.*;
import java.sql.*;

public class DBCon
{

        public static void main(String[] args)
        throws SQLException, ClassNotFoundException
        {

                //load the PostgreSQL driver
                Class.forName("org.postgresql.Driver");


                //create a connection to the database
                Connection conn = DriverManager.getConnection("jdbc:postgresql://shinji.cs.ndsu.nodak.edu:5432/<databasename>", "<username>", "<password>") ;
                
                //create a query statement
                Statement stmt = conn.createStatement();
                String query = "<QUERY>"; //replace this with your query

                //run the query, return a result set        
                ResultSet rset = stmt.executeQuery(query);

                //for each result
                while(rset.next())
                {
                        //print the first item in each result
                        System.out.println(rset.getString(1));
                }
               
                //clean up database classes
                stmt.close();
                rset.close();
                conn.close();

        }
}

This code should compile once the username, password, database, and query are replaced.

Sample Compilation

Given the file above saved as DBCon.java, with username and password changed, you can compile in the lab using the following: 

javac -cp "./:/path/to/postgresql-jdbc.jar" DBCon.java
java -cp "./:/path/to/postgresql-jdbc.jar" DBCon

Make sure to set the classpath variable or the classpath in the `javac` and `java` commands to point to both the current directory and the postgresql JDBC .jar file so that your program can use it at run time.

Setting the CLASSPATH environment variable

helsene@lab18:~$ CLASSPATH="./:/home/helsene/postgresql-9.0-801.jdbc4.jar"
helsene@lab18:~$ javac DBCon.java
helsene@lab18:~$ java DBCon
123
234
helsene@lab18:~$ exit

Setting the classpath explicitly

Should that not work, you can explicitly set the classpath for the `javac` and `java` commands as such: 

java -classpath "./:/path/to/postgresql-9.0-801-jdbc4.jar" DBCon.java
 - and -
javac -classpath "./:/path/to/postgresql-9.0-801-jdbc4.jar" DBCon

You use the -classpath switch to set the classpath. The classpath parameter is then a colon-delimited list of directories and .jar files that you would like to have included. The directory that your program resides in needs to be specified as well as the path to the postgresql JDBC .jar file. Using './' refers to the current directory.

Sample Compilation

Given the file above saved as DBCon.java, with username and password changed, you can compile in the lab using the following: 

javac -cp "./:/path/to/postgresql-jdbc.jar" DBCon.java
java -cp "./:/path/to/postgresql-jdbc.jar" DBCon

Make sure to set the classpath variable or the classpath in the `javac` and `java` commands to point to both the current directory and the postgresql JDBC .jar file so that your program can use it at run time.

Setting the CLASSPATH environment variable

helsene@lab18:~$ CLASSPATH="./:/home/helsene/postgresql-9.0-801.jdbc4.jar"
helsene@lab18:~$ javac DBCon.java
helsene@lab18:~$ java DBCon
123
234
helsene@lab18:~$ exit

Setting the classpath explicitly

Should that not work, you can explicitly set the classpath for the `javac` and `java` commands as such: 

java -classpath "./:/path/to/postgresql-9.0-801-jdbc4.jar" DBCon.java
 - and -
javac -classpath "./:/path/to/postgresql-9.0-801-jdbc4.jar" DBCon

You use the -classpath switch to set the classpath. The classpath parameter is then a colon-delimited list of directories and .jar files that you would like to have included. The directory that your program resides in needs to be specified as well as the path to the postgresql JDBC .jar file. Using './' refers to the current directory.