Spring and JDBC With PostgreSQL

Introduction

Java applications connect to PostgreSQL through JDBC—the same API you use for MySQL with a different URL and driver. Spring Boot switches databases by changing spring.datasource properties and adding the PostgreSQL JDBC driver. This is a thin, optional chapter: deep JDBC and JPA tutorials live in JDBC and Spring Boot 3.

Prerequisites

• Installation and psql
• JDBC first program
• Spring Boot JDBC and datasource (if using Spring)

JDBC URL and Driver

Maven dependency:

<dependency>
  <groupId>org.postgresql</groupId>
  <artifactId>postgresql</artifactId>
  <scope>runtime</scope>
</dependency>

Gradle:

runtimeOnly("org.postgresql:postgresql")

Connection URL format:

jdbc:postgresql://HOST:5432/DATABASE

With parameters:

jdbc:postgresql://127.0.0.1:5432/hello_demo?sslmode=disable

Code explanation:

• Default port 5432 (MySQL uses 3306)
• sslmode=require or verify-full in production
• User/password via DriverManager or DataSource properties—not in URL when avoidable

Minimal JDBC Program

PgHello.java:

Parameterized query (always use ? placeholders):

import java.sql.PreparedStatement;
 
String sql = "SELECT id, display_name FROM users WHERE email = ?";
try (PreparedStatement ps = conn.prepareStatement(sql)) {
  ps.setString(1, "ada@example.com");
  try (ResultSet rs = ps.executeQuery()) {
    if (rs.next()) {
      System.out.println(rs.getString("display_name"));
    }
  }
}

Same JDBC patterns as MySQL—different driver JAR and URL.

Spring Boot Configuration

application.yml:

spring:
  datasource:
    url: jdbc:postgresql://127.0.0.1:5432/hello_demo
    username: postgres
    password: ${DB_PASSWORD:learnpass}
    driver-class-name: org.postgresql.Driver
  jpa:
    database-platform: org.hibernate.dialect.PostgreSQLDialect
    hibernate:
      ddl-auto: validate

Docker Compose service name as host:

spring:
  datasource:
    url: jdbc:postgresql://postgres:5432/hello_demo

Code explanation:

• PostgreSQLDialect maps Java types to Postgres types (JSONB, UUID, arrays)
• Prefer ddl-auto: validate or none in production—use Flyway/Liquibase migrations
• MySQL MySQLDialect → Postgres PostgreSQLDialect when switching databases

JdbcTemplate Example

Insert with returning id (Postgres-specific SQL):

Long id = jdbc.queryForObject(
    """
    INSERT INTO users (email, display_name)
    VALUES (?, ?)
    RETURNING id
    """,
    Long.class,
    "new@example.com",
    "New User"
);

RETURNING avoids a second query—Postgres feature highlighted in Insert and COPY.

Spring Data JPA Notes

Entity annotations are mostly portable. Watch dialect-specific types:

Switching from MySQL: review AUTO_INCREMENT entities—Postgres uses sequences/identity automatically with IDENTITY.

Full JPA tutorial: Spring Data JPA entities.

PostgreSQL vs MySQL JDBC Quick Reference

Security

• Dedicated app role—not superuser postgres—Roles and Permissions
• Externalize passwords—Spring ${DB_PASSWORD}, Kubernetes secrets
• pg_hba.conf and SSL for network auth—Installation chapter

FAQ

Do I need a different connection pool?

HikariCP works for both—change URL and driver only.

Hibernate ddl-auto update on Postgres?

Avoid in production—use migrations. update can surprise you on both databases.

JSONB in JPA?

Hibernate 6+ supports JSON mapping—vendor-specific; consider @JdbcTypeCode(SqlTypes.JSON) or native queries for complex JSONB.

Switch MySQL app to Postgres?

Audit SQL: IFNULL → COALESCE, upsert syntax, LIMIT in DELETE, boolean literals, date functions.

Where is full JDBC course?

JDBC track—this chapter is Postgres-specific wiring only.

Python equivalent?

psycopg basics and FastAPI integration.