Connect to PostgreSQL with JDBC driver

In this post, I am going to explain how to create, insert and query the table using JDBC.

 

Prerequisite

Setup Postgresql.

 

Dependencies used

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.4.1</version>
</dependency>

 

Create a test database

Open a terminal and execute below command to create ‘test’ database.

createdb -U postgres -h localhost test

 

Refer this link (https://self-learning-java-tutorial.blogspot.com/2019/05/postgres-create-database.html)  for more details.

 

JDBC to connect to test database and interact with a table

Step 1: Get an instance of database connection object.

final String url = "jdbc:postgresql://127.0.0.1:5432/test";
final String userName = "postgres";
final String pasword = "postgres";

final Connection conn = DriverManager.getConnection(url, userName, pasword);

Step 2: Get a Statement object.

final Statement stmt = conn.createStatement();

Step 3: Create a table by executing the query,

String query = "CREATE TABLE employee (id int, name varchar(30), PRIMARY KEY(id))";
stmt.execute(query);

Step 4: Insert some records into employee table.

query = "INSERT INTO employee values(1, 'Krishna')";
stmt.execute(query);
query = "INSERT INTO employee values(2, 'Arjun')";
stmt.execute(query);

Step 5: Query employee table and print the information.

query = "SELECT * FROM employee";

try (final ResultSet rs = stmt.executeQuery(query)) {
    while (rs.next()) {
        int id = rs.getInt("id");
        String name = rs.getString("name");
        System.out.println(id + " " + name);
    }
}

Find the below working application.

 

HelloWorld.java

package com.sample.app;

/*Import sql package entities */
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

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

        /* Open connection to database */
        System.out.println("Connecting to database");
        final String url = "jdbc:postgresql://127.0.0.1:5432/test";
        final String userName = "postgres";
        final String pasword = "postgres";

        try (final Connection conn = DriverManager.getConnection(url, userName, pasword);
                final Statement stmt = conn.createStatement();) {

            /* Query to create employee table */
            String query = "CREATE TABLE employee (id int, name varchar(30), PRIMARY KEY(id))";
            stmt.execute(query);

            /* Insert data to employee table */
            query = "INSERT INTO employee values(1, 'Krishna')";
            stmt.execute(query);
            query = "INSERT INTO employee values(2, 'Arjun')";
            stmt.execute(query);

            query = "SELECT * FROM employee";

            try (final ResultSet rs = stmt.executeQuery(query)) {
                while (rs.next()) {
                    int id = rs.getInt("id");
                    String name = rs.getString("name");
                    System.out.println(id + " " + name);
                }
            }
        }

    }
}

Run the application, you will see below messages in the console.

Connecting to database
1 Krishna
2 Arjun

Query the table from psql console and confirm the same.

$psql -U postgres -h localhost --dbname=test
Password for user postgres: 
psql (14.4)
Type "help" for help.

test-# \d
          List of relations
 Schema |   Name   | Type  |  Owner   
--------+----------+-------+----------
 public | employee | table | postgres
(1 row)

test-# 
test=# SELECT * FROM employee;
 id |  name   
----+---------
  1 | Krishna
  2 | Arjun
(2 rows)

Complete maven project structure looks like below.

 

 

You can download complete working application from this link.

Previous                                                 Next                                                 Home

Get Generated id in jdbc

Using 'getGeneratedKeys()' method of PreparedStatement we can get the generated keys.

Example

private static void createEmployee(Employee emp) throws SQLException {
 String stmtToInsert = "INSERT INTO employee(firstName, lastName) values (?, ?)";
 try (PreparedStatement statement = connection.prepareStatement(stmtToInsert,
   Statement.RETURN_GENERATED_KEYS);) {
  statement.setString(1, emp.getFirstName());
  statement.setString(2, emp.getLastName());

  int affectedRows = statement.executeUpdate();

  if (affectedRows == 0) {
   throw new SQLException("Creating Employee failed, no rows affected.");
  }

  try (ResultSet generatedKeys = statement.getGeneratedKeys()) {
   if (generatedKeys.next()) {
    emp.setId(generatedKeys.getInt(1));
   } else {
    throw new SQLException("Creating Employee failed, no ID obtained.");
   }
  }
 }
}

Find the below working application.

App.java

package com.sample.app;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

import com.sample.app.model.Employee;

public class App {

 // Initialize url username and password
 private static final String URL = "jdbc:mysql://localhost/sample";
 private static final String USERNAME = "krishna";
 private static final String PASSWORD = "krishna";

 private static Connection connection = null;

 private static void createEmployee(Employee emp) throws SQLException {
  String stmtToInsert = "INSERT INTO employee(firstName, lastName) values (?, ?)";
  try (PreparedStatement statement = connection.prepareStatement(stmtToInsert,
    Statement.RETURN_GENERATED_KEYS);) {
   statement.setString(1, emp.getFirstName());
   statement.setString(2, emp.getLastName());

   int affectedRows = statement.executeUpdate();

   if (affectedRows == 0) {
    throw new SQLException("Creating Employee failed, no rows affected.");
   }

   try (ResultSet generatedKeys = statement.getGeneratedKeys()) {
    if (generatedKeys.next()) {
     emp.setId(generatedKeys.getInt(1));
    } else {
     throw new SQLException("Creating Employee failed, no ID obtained.");
    }
   }
  }
 }

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

  /* Open connection to database */
  System.out.println("Connecting to database");

  connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);

  /* Create table employee */
  String query = "CREATE TABLE employee (id int NOT NULL AUTO_INCREMENT, firstName varchar(30), lastName varchar(30), PRIMARY KEY(id))";
  Statement stmt = connection.createStatement();
  stmt.execute(query);

  /* Insert data to employee table */
  Employee emp1 = new Employee("Krishna", "Gurram");
  Employee emp2 = new Employee("Gopi", "Battu");

  createEmployee(emp1);
  createEmployee(emp2);

  System.out.println("Employee1 id : " + emp1.getId());
  System.out.println("Employee2 id : " + emp2.getId());

  query = "SELECT id, firstName, lastName FROM employee";
  ResultSet resultSet = stmt.executeQuery(query);
  ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
  String columnName1 = resultSetMetaData.getColumnName(1);
  String columnName2 = resultSetMetaData.getColumnName(2);
  String columnName3 = resultSetMetaData.getColumnName(3);

  System.out.println(columnName1 + " " + columnName2 + " " + columnName3);

  while (resultSet.next()) {
   int id = resultSet.getInt("id");
   String firstName = resultSet.getString("firstName");
   String lastName = resultSet.getString("lastName");
   System.out.println(id + " " + firstName + " " + lastName);
  }

  System.out.println("Dropping the table employee");

  String sql = "DROP TABLE employee";

  stmt.executeUpdate(sql);

  resultSet.close();
  connection.close();
  connection.close();
 }
}

Output

Connecting to database
Employee1 id : 1
Employee2 id : 2
id firstName lastName
1 Krishna Gurram
2 Gopi Battu
Dropping the table employee

Previous                                                    Next                                                    Home

Jdbc: Retrieve column names from ResultSet

‘ResultSetMetaData’ class is used to get the column names.

Example

query = "SELECT id, firstName, lastName FROM employee";
ResultSet resultSet = stmt.executeQuery(query);
ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
String columnName1 = resultSetMetaData.getColumnName(1);
String columnName2 = resultSetMetaData.getColumnName(2);
String columnName3 = resultSetMetaData.getColumnName(3);

App.java

package com.sample.app;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

public class App {

 // Initialize url username and password
 private static final String URL = "jdbc:mysql://localhost/sample";
 private static final String USERNAME = "krishna";
 private static final String PASSWORD = "krishna";

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

  /* Open connection to database */
  System.out.println("Connecting to database");

  Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);

  /* Create table employee */
  String query = "CREATE TABLE employee (id int, firstName varchar(30), lastName varchar(30), PRIMARY KEY(id))";
  Statement stmt = conn.createStatement();
  stmt.execute(query);

  /* Insert data to employee table */
  query = "INSERT INTO employee values(1, \"Krishna\",  \"Gurram\")";
  stmt.execute(query);
  query = "INSERT INTO employee values(2, \"Gopi\",  \"Battu\")";
  stmt.execute(query);

  query = "SELECT id, firstName, lastName FROM employee";
  ResultSet resultSet = stmt.executeQuery(query);
  ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
  String columnName1 = resultSetMetaData.getColumnName(1);
  String columnName2 = resultSetMetaData.getColumnName(2);
  String columnName3 = resultSetMetaData.getColumnName(3);
  
  System.out.println(columnName1 + " " + columnName2 + " " + columnName3);

  while (resultSet.next()) {
   int id = resultSet.getInt("id");
   String firstName = resultSet.getString("firstName");
   String lastName = resultSet.getString("lastName");
   System.out.println(id + " " + firstName + " " + lastName);
  }

  System.out.println("Dropping the table employee");

  String sql = "DROP TABLE employee";

  stmt.executeUpdate(sql);

  resultSet.close();
  conn.close();
  conn.close();
 }
}

Output

Connecting to database
id firstName lastName
1 Krishna Gurram
2 Gopi Battu
Dropping the table employee

Previous                                                    Next                                                    Home

Spring boot jdbc: NamedParameter example

NamedParameterJdbcTemplate used to perform CRUD operation using named parameters instead of ? placeholders.

Example

public void delete(int id) {

         NamedParameterJdbcTemplate namedTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);

         Map<String, Object> data = new HashMap<>();

         data.put("employeeId", id);

         namedTemplate.update("DELETE FROM employees WHERE id = :employeeId", data);

}

Find the below working application.

App.java

package com.sample.app;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.Bean;
import org.springframework.jdbc.core.JdbcTemplate;

import com.sample.app.entity.Employee;
import com.sample.app.repository.EmployeeRepository;

@SpringBootApplication
public class App {
 private static final Logger log = LoggerFactory.getLogger(App.class);

 @Autowired
 JdbcTemplate jdbcTemplate;

 @Autowired
 EmployeeRepository employeeRepository;

 public static void main(String args[]) {
  SpringApplication.run(App.class, args);
 }

 @Bean
 public CommandLineRunner demo() {
  return (args) -> {
   log.info("Creating tables");

   jdbcTemplate.execute("DROP TABLE employees IF EXISTS");
   jdbcTemplate.execute(
     "CREATE TABLE employees (id serial, first_name VARCHAR(255), last_name VARCHAR(255), PRIMARY KEY(id)) ");

   Employee emp1 = new Employee("Ram", "Gurram");
   Employee emp2 = new Employee("Moutwika", "Maj");
   Employee emp3 = new Employee("Anusha", "R");

   emp1 = employeeRepository.save(emp1);
   employeeRepository.save(emp2);
   employeeRepository.save(emp3);

   employeeRepository.printAll();

   System.out.println("Deleting an employee with id " + emp1.getId());
   employeeRepository.delete(emp1.getId());

   employeeRepository.printAll();
  };
 }

}

Employee.java

package com.sample.app.entity;

public class Employee {
 private int id;
 private String firstName;
 private String lastName;

 public Employee(int id, String firstName, String lastName) {
  this.id = id;
  this.firstName = firstName;
  this.lastName = lastName;
 }
 
 public Employee(String firstName, String lastName) {
  this.firstName = firstName;
  this.lastName = lastName;
 }
 
 public Employee() {}

 public int getId() {
  return id;
 }

 public void setId(int id) {
  this.id = id;
 }

 public String getFirstName() {
  return firstName;
 }

 public void setFirstName(String firstName) {
  this.firstName = firstName;
 }

 public String getLastName() {
  return lastName;
 }

 public void setLastName(String lastName) {
  this.lastName = lastName;
 }

 @Override
 public String toString() {
  StringBuilder builder = new StringBuilder();
  builder.append("Employee [id=").append(id).append(", firstName=").append(firstName).append(", lastName=")
    .append(lastName).append("]");
  return builder.toString();
 }

}

EmployeeRepository.java

package com.sample.app.repository;

import java.util.List;

import com.sample.app.entity.Employee;

public interface EmployeeRepository {

 public Employee save(Employee emp);
 
 public List<Employee> all();
 
 public Employee get(int id);
 
 public Employee update(Employee emp);
 
 public void update(List<Object[]> emps);
 
 public void printAll();
 
 public void delete(int id);
}

EmployeeRepositoryImpl.java

package com.sample.app.repository.impl;

import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.stereotype.Repository;

import com.sample.app.entity.Employee;
import com.sample.app.repository.EmployeeRepository;

@Repository
public class EmployeeRepositoryImpl implements EmployeeRepository {

 @Autowired
 JdbcTemplate jdbcTemplate;

 private static final RowMapper<Employee> EMPLOYEE_ROW_MAPPER = (rs, rowNum) -> {
  Employee emp1 = new Employee();
  emp1.setId(rs.getInt("id"));
  emp1.setFirstName(rs.getString("first_name"));
  emp1.setLastName(rs.getString("last_name"));

  return emp1;
 };

 @Override
 public Employee save(Employee emp) {
  SimpleJdbcInsert simpleJdbcInsert = new SimpleJdbcInsert(jdbcTemplate);
  simpleJdbcInsert.setGeneratedKeyName("id");

  List<String> columns = Arrays.asList("first_name", "last_name");

  simpleJdbcInsert.setTableName("employees");
  simpleJdbcInsert.setColumnNames(columns);

  Map<String, Object> data = new HashMap<>();
  data.put("first_name", emp.getFirstName());
  data.put("last_name", emp.getLastName());

  int id = (Integer) simpleJdbcInsert.executeAndReturnKey(data);

  Employee emp1 = new Employee();
  jdbcTemplate.query("SELECT * FROM employees WHERE id = " + id, rs -> {
   emp1.setId(rs.getInt("id"));
   emp1.setFirstName(rs.getString("first_name"));
   emp1.setLastName(rs.getString("last_name"));
  });
  return emp1;
 }

 @Override
 public List<Employee> all() {
  return jdbcTemplate.query("SELECT * FROM employees", EMPLOYEE_ROW_MAPPER);
 }

 @Override
 public Employee get(int id) {
  try {

   return jdbcTemplate.queryForObject("SELECT * FROM employees WHERE id=?", EMPLOYEE_ROW_MAPPER, id);
  } catch (EmptyResultDataAccessException e) {
   return null;
  }

 }

 @Override
 public Employee update(Employee emp) {
  jdbcTemplate.update("UPDATE employees SET first_name=?, last_name=? WHERE id=?", emp.getFirstName(),
    emp.getLastName(), emp.getId());
  return emp;
 }

 @Override
 public void update(List<Object[]> emps) {
  jdbcTemplate.batchUpdate("UPDATE employees SET first_name=?, last_name=? WHERE id=?", emps);
 }

 @Override
 public void printAll() {
  System.out.println("\nPrinting All Employees\n");
  System.out.println("*********************************");

  List<Employee> emps = this.all();
  emps.forEach(customer -> System.out.println(customer));
  System.out.println("*********************************");
 }

 @Override
 public void delete(int id) {
  NamedParameterJdbcTemplate namedTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);

  Map<String, Object> data = new HashMap<>();
  data.put("employeeId", id);

  namedTemplate.update("DELETE FROM employees WHERE id = :employeeId", data);
 }

}

application.properties

logging.level.root=WARN

## H2 specific properties
spring.h2.console.enabled=true
spring.h2.console.path=/h2

spring.datasource.url=jdbc:h2:file:~/db/myOrg.db;DB_CLOSE_ON_EXIT=FALSE;DB_CLOSE_DELAY=-1;

spring.datasource.username=krishna
spring.datasource.password=password123

spring.datasource.driverClassName=org.h2.Driver

## Database connection pooling properties
# Number of ms to wait before throwing an exception if no connection is available.
spring.datasource.max-wait=10000

# Maximum number of active connections that can be allocated from this pool at the same time.
spring.datasource.tomcat.max-active=10
spring.datasource.tomcat.max-idle=5
spring.datasource.tomcat.min-idle=3

pom.xml

<project xmlns="http://maven.apache.org/POM/4.0.0"
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
 <modelVersion>4.0.0</modelVersion>
 <groupId>springJDBC</groupId>
 <artifactId>springJDBC</artifactId>
 <version>1</version>

 <parent>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-parent</artifactId>
  <version>2.1.6.RELEASE</version>
 </parent>

 <properties>
  <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
 </properties>

 <dependencies>
  <dependency>
   <groupId>org.springframework.boot</groupId>
   <artifactId>spring-boot-starter-jdbc</artifactId>
  </dependency>

  <dependency>
   <groupId>com.h2database</groupId>
   <artifactId>h2</artifactId>
  </dependency>
 </dependencies>

</project>

Total project structure looks like below.

Run App.java, you can see below messages in console.

  .   ____          _            __ _ _
 /\\ / ___'_ __ _ _(_)_ __  __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
 \\/  ___)| |_)| | | | | || (_| |  ) ) ) )
  '  |____| .__|_| |_|_| |_\__, | / / / /
 =========|_|==============|___/=/_/_/_/
 :: Spring Boot ::        (v2.1.6.RELEASE)


Printing All Employees

*********************************
Employee [id=1, firstName=Ram, lastName=Gurram]
Employee [id=2, firstName=Moutwika, lastName=Maj]
Employee [id=3, firstName=Anusha, lastName=R]
*********************************
Deleting an employee with id 1

Printing All Employees

*********************************
Employee [id=2, firstName=Moutwika, lastName=Maj]
Employee [id=3, firstName=Anusha, lastName=R]
*********************************

You can download Complete working application from this link.

   

Previous                                                    Next                                                    Home