Postgres Connect to Database

Introduction to Postgres Connect to Database

While operating any database, the first and the most step after installation of pre-requisites is to connect to the database. While connecting to the database, there are various GUI’s or interfaces that we can use. In the case of the PostgreSQL database, the most used method to interact with is through the command line using psql. Other than this there is an IDE called pgadmin which can be used for the same. While using databases in any application, we need to use APIs to connect to database. In this article, we will see how we can connect to the Postgres database using psql and then to a java application using JDBC APIs.

Connecting to Postgres using psql –

• The first step is to open the terminal and the login to Postgres using a command prompt with a user using the following command –

sudo su – postgres

Here, I am logging in as Postgres using sudo. After that enter the password of the superuser.

• The next step is to connect to a psql prompt that has the following syntax –

psql databaseName nameOfUser

where databaseName is the name of the database you wish to connect to and nameOfUser is the user using which you want to connect to the database.

For example, if you want to connect to the database named Postgres and have the username as Postgres then the connecting statement becomes –

psql postgres postgres

that gives the output as follows –

sudo su – postgres
psql postgres postgres

When you finally connect to a particular database, you will see the name of the database on the shell of the terminal. In the above case, postgres=# shows that now we are connected to the Postgres database. Now, you can fire any psql command you wish to \du is the command to list out the user. You can exit from the output screen using \q and if you want to check all the available databases then type \l command which results in all databases present in our database server as follows –

Now to switch to the educba database and connect to the same we can type the command

\c educba
or
\connect educba

that gives the output –

Now, you can see the shell changing from postgres=# to educba=# that tells we are now connected to the educba database.

JDBC Connectivity-

While creating a database-based application and using any of the databases in your java application, you will need to follow certain steps to use the JDBC (Java Database Connectivity) which is an API i.e Application Programming Interface that helps us to communicate between our java application and our database. The database can be any database like MySQL, PostgreSQL, etc. In this article, we will learn how we can connect our java application with our PostgreSQL database with the help of the JDBC driver. Note that JDBC drivers and URLs are different for different relational databases.

Pre-requisites

While creating a database-based java application, it is required to have a few things installed in your system which is as follows –

• Java JDK toolkit

It can be checked by typing the command java -version in your command which will provide you with the output specifying the version of java available in your system like the following.

java -version

If not present, you should firstly install java. Mine is java version “1.8.0_201”.

• PostgreSQL and psql should be installed on your machine that can be checked by typing the command psql -V that should give output as follows –

psql -V

If not available install it before proceeding with JDBC driver installation. Mine is psql (PostgreSQL) 12.2 (Ubuntu 12.2-2.pgdg18.04+1).

• The last thing is the JDBC driver for PostgreSQL jar file which can be downloaded from the link https://jdbc.postgresql.org/download.html. We will use it further while establishing the JDBC connection.

Steps for JDBC initialization

• We first need to import JDBC using the import statement –

import java.sql.*;

You should be careful here, you should not import org.postgresql package in your application as doing so will confuse the javac for compiling the source file.

• The second step is where you will need to load your JDBC driver. This can be done in two ways. One by using Class.forName() method and other by passing your driver as a parameter to the JVM while initializing. The second step is more preferable as in case if in your future your application needs to change its database server then it can be done without changing the connection related code easily.

In the first method, we will use the Class.forName() method in the following way –

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

where org.postgresql.Driver specifies your PostgreSQL JDBC driver usage. This method can give ClassNotFoundException if our driver is not found. This method is most often used in JDBC applications.

The second method consists of passing your JDBC driver as a parameter to the initialization string using -D option as follows –

java -D jdbc.drivers=org.postgresql.Driver example.ImageViewer

• Connecting to database – Now is the time to connect to the database. The database is represented by a URL (Uniform Resource Locator) in JDBC applications which can be in either of the three forms while using the PostgreSQL database.

jdbc:postgresql:databaseName
jdbc:postgresql://hostName/databaseName
jdbc:postgresql://hostName:portNumber/databaseName

where

the hostName is localhost by default and if remote is the Ipv6 address of the machine.

portNumber is socket/port address which is by default 5432 for PostgreSQL.

DatabaseName is the name of the database you want to connect to for your JDBC application.

Finally, you can now connect using the statement

Connection dbConnectionObject= DriverManager.getConnection(url, username, password);

• The last step is closing your JDBC connection once you are done with performing all your database related manipulations which can simply be done by closing your Connection object as follows –

dbConnectionObject.close();

Example of Postgres Connect to Database

Let us consider one example of a JDBC application with PostgreSQL. For this, We will first need to create a database in our PostgreSQL database server. We will create a database named educba and will connect to it using our JDBC program in java.

createdb educba;

Create a new file named EducbaJdbcExample .java which will contain a program like this –

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class EducbaJdbcExample {
public static void main(String[] args) {
try (Connection myConnection = DriverManager.getConnection(
"jdbc:postgresql://127.0.0.1:5432/educba", "postgres", "a")) {
if (myConnection != null) {
System.out.println("Successfully connected to educba database!");
} else {
System.out.println("Sorry!Failed to establish JDBC connection");
}
} catch (SQLException e) {
System.err.format("Current SQL status: %s\n%s", e.getSQLState(), e.getMessage());
} catch (Exception e) {
e.printStackTrace();
}
}
}

The first three statements in our program are for importing the packages required for using the JDBC related methods. The next thing is that we are trying to establish the connection with our PostgreSQL using the following statement –

Connection myConnection = DriverManager.getConnection(
"jdbc:postgresql://127.0.0.1:5432/educba", "postgres", "a")

where 127.0.0.1 stands for localhost i.e same machine address and 5432 is the PostgreSQL port and I want to connect to educba database name and my username and password are ‘a’ and ‘a’ respectively. Here, I have inserted this statement in try because if any exception arises while establishing the connection it will give an immediate exception and display the message related to the exception. If the connection is established then the message saying “Successfully connected to educba database!” will get displayed else “Sorry! Failed to establish JDBC connection” message will be displayed.

After saving, compiling, and running the application if it gives the exception like the following –

In case, if you are using Eclipse IDE and creating a java class file for your program and by right-clicking your file ->Run As -> Java application option you can run your program. In that case, it will give the following output. This is because there is no JDBC driver file of PostgreSQL which have been downloaded present in our current project.

This is because the JDBC driver is not loaded. In such applications, we will need to load our JDBC driver manually using cp. For this, it is required that your program EducbaJdbcExample .java and the downloaded jar file of JDBC driver for PostgreSQL are stored in the same path if you are running through the command line or you have imported jar file in your current project if you are using Eclipse IDE as follows –

Then you can run your application as a java application if you are using eclipse –

As can be seen the message on the console is now “Successfully connected to educba database!”. So, we are connected to our database now.

Conclusion

We can connect with our PostgreSQL database from our java application by following all the above steps in a proper manner.

This is a guide to Postgres Connect to Database. Here we discuss introduction, syntax, format, and examples with code implementation. You may also have a look at the following articles to learn more –

1. PostgreSQL EXCEPT
2. Postgres Delete Cascade
3. PostgreSQL Roles
4. PostgreSQL SET