PostgreSQL 入门到精通 + Java & Spring Boot 实战教程

伯灵

发布于 2026-01-21 09:55:54

1360

1️⃣ PostgreSQL 基础

-- 创建数据库
CREATE DATABASE demo_db;

-- 用户表
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 文章表
CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    content TEXT,
    user_id INT REFERENCES users(id),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

2️⃣ SQL 基础操作（示例）

-- 插入
INSERT INTO users(username, email) VALUES('Alice', 'alice@example.com');

-- 查询
SELECT * FROM users;

-- 更新
UPDATE users SET email='alice_new@example.com' WHERE id=1;

-- 删除
DELETE FROM users WHERE id=1;

3️⃣ Java JDBC 实战

import java.sql.*;

public class PostgresJDBCDemo {
    public static void main(String[] args) throws SQLException {
        String url = "jdbc:postgresql://localhost:5432/demo_db";
        String user = "postgres";
        String password = "yourpassword";

        try (Connection conn = DriverManager.getConnection(url, user, password)) {
            System.out.println("Connected!");

            // 插入
            String insertSQL = "INSERT INTO users(username, email) VALUES (?, ?)";
            try (PreparedStatement pstmt = conn.prepareStatement(insertSQL)) {
                pstmt.setString(1, "Bob");
                pstmt.setString(2, "bob@example.com");
                pstmt.executeUpdate();
            }

            // 查询
            try (Statement stmt = conn.createStatement();
                 ResultSet rs = stmt.executeQuery("SELECT * FROM users")) {
                while (rs.next()) {
                    System.out.println(rs.getInt("id") + ": " + rs.getString("username"));
                }
            }
        }
    }
}

4️⃣ Spring Boot 入门 + 数据源配置

pom.xml 依赖：

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.6.0</version>
</dependency>
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
</dependency>

application.properties：

spring.datasource.url=jdbc:postgresql://localhost:5432/demo_db
spring.datasource.username=postgres
spring.datasource.password=yourpassword
spring.jpa.hibernate.ddl-auto=update
spring.jpa.show-sql=true
spring.jpa.database-platform=org.hibernate.dialect.PostgreSQLDialect

5️⃣ Spring Boot JPA 实战

User 实体类：

import jakarta.persistence.*;
import lombok.Data;
import java.time.LocalDateTime;

@Data
@Entity
@Table(name="users")
public class User {
    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    private Long id;

    @Column(nullable=false, unique=true)
    private String username;

    private String email;

    private LocalDateTime createdAt = LocalDateTime.now();
}

Repository 接口：

import org.springframework.data.jpa.repository.JpaRepository;
public interface UserRepository extends JpaRepository<User, Long> {
    User findByUsername(String username);
}

Service & Controller：

@Service
public class UserService {
    private final UserRepository repo;
    public UserService(UserRepository repo) { this.repo = repo; }

    public User createUser(String username, String email) {
        User u = new User();
        u.setUsername(username);
        u.setEmail(email);
        return repo.save(u);
    }

    public List<User> listUsers() { return repo.findAll(); }

    @Transactional
    public void updateEmail(Long id, String newEmail) {
        User u = repo.findById(id).orElseThrow();
        u.setEmail(newEmail);
    }
}

@RestController
@RequestMapping("/users")
public class UserController {
    private final UserService service;
    public UserController(UserService service) { this.service = service; }

    @PostMapping
    public User create(@RequestParam String username, @RequestParam String email) {
        return service.createUser(username, email);
    }

    @GetMapping
    public List<User> list() { return service.listUsers(); }

    @PutMapping("/{id}/email")
    public void updateEmail(@PathVariable Long id, @RequestParam String email) {
        service.updateEmail(id, email);
    }
}

6️⃣ 高级 PostgreSQL 特性 + Spring Boot 实战

事务 @Transactional

批量插入 saveAll()

分页 & 排序

Page<User> page = userRepository.findAll(PageRequest.of(0, 10, Sort.by("createdAt").descending()));

自定义查询

@Query("SELECT u FROM User u WHERE u.username LIKE %:name%")
List<User> searchByName(@Param("name") String name);

触发器/函数

CREATE OR REPLACE FUNCTION update_timestamp()
RETURNS TRIGGER AS $$
BEGIN
   NEW.created_at = NOW();
   RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_users_time
BEFORE UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION update_timestamp();