JDBC to PostgreSQL - How to Insert Record in PostgreSQL using Java JDBC driver

Introduction

This tutorial will explain how to perform an INSERT operation against a PostgreSQL database table with the JDBC to PostgreSQL. The Java Database Connectivity API, typically referred to as JDBC, driver is software that allows a Java application to connect and interact with a database. The JDBC driver establishes a connection to the database and executes the protocol for transferring data between the client and database.

Prerequisites for Connecting JDBC to PostgreSQL

  • The PostgreSQL server must be properly installed, configured and running. Download PostgreSQL for Linux and Windows systems here

  • ECLIPSE IDE must be properly installed and configured. Download Eclipse IDE for Linux and Windows systems here

  • A basic knowledge of Java syntax.

How to Create a Sample Database

This section will explain how to create a sample database that will be used in this tutorial.

First, login to the PostgreSQL server and create a database with the following command:

CREATE DATABASE cars;

Now connect to the database with the PostgreSQL meta-command \c followed by the database name, such as: \c cars;.

Next, create a table named tblcars with the following commands:

CREATE TABLE tblcars (
id SERIAL PRIMARY KEY,
carname TEXT NOT NULL,
brand TEXT NOT NULL
);

An INSERT operation can now be performed by executing the following commands:

INSERT INTO tblcars(carname,brand) VALUES
('fortuner', 'toyota'),
('wigo','toyota'),
('ertiga','suzuki'),
('city','honda');

The results should resemble the following:

id | carname | brand
----+----------+--------
1 | fortuner | toyota
2 | wigo | toyota
3 | ertiga | suzuki
4 | city | honda
(4 ROWS)

How to Insert Records in PostgreSQL using the PostgreSQL JDBC Driver

With a sample database created, insert a record into the PostgreSQL database from a Java application by executing the following steps in order:

Connect to PostgreSQL server via the Java application.

First, a function must be created to connect to the database. This function is composed of the connection string, the name of the user and the user’s password. To accomplish this, execute the following code:

// connection string
private final String conectionUrl = "jdbc:postgresql://localhost/testdatabase";

// the name of the user
private final String user_name = "postgres";

// the password of the user
private final String pwd = "1234";

Once the important details of the connection are identified, the details can then be used within the getConnection() method of the class DriverManager to make a connection to the PostgreSQL database server.

Next, create a dbcon() function that will call up the connection details, created in the previous step, by executing the following commands:

public Connection dbcon() {
Connection dbcon = null;

try {
dbcon = DriverManager.getConnection(conUrl, username, password);
System.out.println("You are now connected to the server");
}catch(SQLException e){
System.err.println(e.getMessage());
}

return dbcon;
}

Now, to test code, call the above function within the entry point of the Java application, meaning the function main(). Execute the following code within the function main() code block:

public static void main(String[] args) {

JdbcDemo app = new JdbcDemo();
app.conn();
}

Now press CTRL + F11 on the keyboard. The results displayed in the console should resemble the following:

image shows the result of the java application

Create a Java Bean

Next, create a Java Bean that will allow access to the attributes that will be defined in the below section. First, though, create a new Java class using the following code:

package com.pgjdbc.demo;

public class Car {
private String carname;

private String brand;

public Car(String carname, String brand) {
this.carname = carname;
this.brand = brand;
}

public Car() {

}

public String getCarname() {
return carname;
}

public void setCarname(String carname) {
this.carname = carname;
}

public String getBrand() {
return brand;
}

public void setBrand(String brand) {
this.brand = brand;
}
}

The above code creates setters and getters that can be used to access the Java application.

Performing the Java INSERT Operation to the PostgreSQL database using JDBC driver

The following code will perform an INSERT operation that will return the value of id, and can also be used for other purposes:

public long insertCar(Car car) {
String SQLinsert = "INSERT INTO tblcars(carname,brand) "
+ "VALUES(?,?)";

long id = 0;

try(Connection conn = conn();
PreparedStatement pstmt = conn.prepareStatement(SQLinsert, Statement.RETURN_GENERATED_KEYS)){
pstmt.setString(1, car.getCarname());
pstmt.setString(2, car.getBrand());

int rowsAffected = pstmt.executeUpdate();


// this will check the affected row(s)
if(rowsAffected > 0) {
// then we get the ID of the affected row(s)
try (ResultSet rs = pstmt.getGeneratedKeys()){
if (rs.next()) {
id = rs.getLong(1);
}
}catch (SQLException ex) {
System.out.println(ex.getMessage());
}
}


}catch (SQLException ex) {
System.out.println(ex.getMessage());
}
return id;
}

The above code will get the auto generated ‘id’ given to the newly inserted record.

Now test it by running the application. The results should resemble the following:

You are now connected to the server
Adventure, Mitsubishi car has been inserted with an id of 5

Note that the value of the ID of “5” of the newly inserted record was retrieved. A basic SELECT operation against the PostgreSQL database, via the shell, can also be performed.

The results should resemble the following:

cars=# SELECT * FROM tblcars;
id | carname | brand
----+-----------+------------
1 | fortuner | toyota
2 | wigo | toyota
3 | ertiga | suzuki
4 | city | honda
5 | Adventure | Mitsubishi
(5 ROWS)

Conclusion

This tutorial explained how to insert records in the PostgreSQL database using the JDBC to PostgreSQL. The article specifically covered the prerequisites for connecting JDBC to PostgreSQL, how to create a sample database, how to connect to PostgreSQL server, insert records in PostgreSQL using the PostgreSQL JDBC driver and how to test the code. The tutorial also covered how to create a Java Bean and perform the Java INSERT operation to the PostgreSQL database using JDBC driver. Remember that before connecting to PostgreSQL server via the Java application a function must be created to connect to the database.

Just the Code

Below is the entire working code that we used in this article.

import java.sql.Statement;

public class JdbcDemo {


private final String conUrl = "jdbc:postgresql://localhost/cars";
private final String username = "postgres";
private final String password = "1234";

public Connection dbcon() {
Connection dbcon = null;

try {
dbcon = DriverManager.getConnection(conUrl, username, password);
System.out.println("You are now connected to the server");
}catch(SQLException e){
System.err.println(e.getMessage());
}

return dbcon;
}


public static void main(String[] args) {

JdbcDemo app = new JdbcDemo();
Car car = new Car("Adventure","Mitsubishi");

long id = app.insertCar(car);

System.out.println(
String.format("%s, %s car has been inserted with an id of %d",
car.getCarname(), car.getBrand(), id));
}



public long insertCar(Car car) {
String SQLinsert = "INSERT INTO tblcars(carname,brand) "
+ "VALUES(?,?)";

long id = 0;

try(Connection dbcon = dbcon();
PreparedStatement prepareStatement = dbcon.prepareStatement(SQLinsert, Statement.RETURN_GENERATED_KEYS)){
prepareStatement.setString(1, car.getCarname());
prepareStatement.setString(2, car.getBrand());

int rowsAffected = prepareStatement.executeUpdate();

if(rowsAffected > 0) {

try (ResultSet rs = prepareStatement.getGeneratedKeys()){
if (rs.next()) {
id = rs.getLong(1);
}
}catch (SQLException ex) {
System.out.println(ex.getMessage());
}
}


}catch (SQLException ex) {
System.out.println(ex.getMessage());
}
return id;
}
}

Code of the Java Bean – Car

The working code for our Java Bean.

package com.pgjdbc.demo;

public class Car {
private String carname;

private String brand;

public Car(String carname, String brand) {
this.carname = carname;
this.brand = brand;
}

public Car() {

}

public String getCarname() {
return carname;
}

public void setCarname(String carname) {
this.carname = carname;
}

public String getBrand() {
return brand;
}

public void setBrand(String brand) {
this.brand = brand;
}
}

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.