第六章：JDBC 資料庫連接
6.1 前言：資料庫連接的基礎
在這一章節中，我們將學習如何使用 Spring Boot 連接到 PostgreSQL 資料庫，並執行基本的資料庫操作。資料庫連接是大多數 Web 應用程式的核心功能之一，Spring Boot 提供了多種方式來簡化這個過程。
我們將使用 Spring 的 JdbcTemplate 來進行資料庫操作，這是一個輕量級且靈活的工具，適合學習和理解 JDBC 的運作原理。
6.2 添加 PostgreSQL 依賴
首先，我們需要在 pom.xml 中添加 PostgreSQL 驅動依賴。如果你使用 Spring Initializr 時已經添加了「PostgreSQL Driver」，則不需要手動添加。
<!-- pom.xml -->
<dependencies>
<!-- Spring Boot Starter JDBC -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<!-- PostgreSQL Driver -->
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<scope>runtime</scope>
</dependency>
<!-- 其他依賴 -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
</dependencies>
6.3 配置資料來源
在 application.properties 中配置 PostgreSQL 連接：
# src/main/resources/application.properties
# PostgreSQL 資料庫連接配置
spring.datasource.url=jdbc:postgresql://localhost:5432/myapp_db
spring.datasource.username=myapp_user
spring.datasource.password=your_password
spring.datasource.driver-class-name=org.postgresql.Driver
# 連接池配置（HikariCP）
spring.datasource.hikari.maximum-pool-size=10
spring.datasource.hikari.minimum-idle=5
spring.datasource.hikari.connection-timeout=30000
spring.datasource.hikari.idle-timeout=600000
spring.datasource.hikari.max-lifetime=1800000
# JPA/Hibernate 配置（如果你使用 JPA）
spring.jpa.hibernate.ddl-auto=update
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect
# 伺服器端口
server.port=8080
# 日誌配置
logging.level.com.zaxxer.hikari=INFO
logging.level.org.springframework.jdbc=DEBUG
或者使用 application.yml 格式：
# src/main/resources/application.yml
spring:
datasource:
url: jdbc:postgresql://localhost:5432/myapp_db
username: myapp_user
password: your_password
driver-class-name: org.postgresql.Driver
hikari:
maximum-pool-size: 10
minimum-idle: 5
connection-timeout: 30000
idle-timeout: 600000
max-lifetime: 1800000
jpa:
hibernate:
ddl-auto: update
show-sql: true
properties:
hibernate:
format_sql: true
dialect: org.hibernate.dialect.PostgreSQLDialect
server:
port: 8080
logging:
level:
com.zaxxer.hikari: INFO
org.springframework.jdbc: DEBUG
6.4 創建資料表
登入 PostgreSQL 並創建用戶表：
-- 登入 PostgreSQL
psql -U myapp_user -d myapp_db
-- 創建 users 表
CREATE TABLE users (
id SERIAL PRIMARY KEY,
username VARCHAR(50) NOT NULL UNIQUE,
email VARCHAR(100) NOT NULL UNIQUE,
password VARCHAR(255) NOT NULL,
full_name VARCHAR(100),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
active BOOLEAN DEFAULT TRUE
);
-- 創建索引
CREATE INDEX idx_users_username ON users(username);
CREATE INDEX idx_users_email ON users(email);
-- 插入測試數據
INSERT INTO users (username, email, password, full_name, active) VALUES
('john', 'john@example.com', 'password123', 'John Doe', TRUE),
('jane', 'jane@example.com', 'password456', 'Jane Smith', TRUE),
('bob', 'bob@example.com', 'password789', 'Bob Johnson', FALSE);
-- 查詢數據
SELECT * FROM users;
6.5 使用 JdbcTemplate 操作資料庫
讓我們創建一個完整的 User 實體和 Repository 類：
package com.example.myfirstapp.entity;
import java.time.LocalDateTime;
/**
* User 實體類
* 對應資料庫中的 users 表
*/
public class User {
private Long id;
private String username;
private String email;
private String password;
private String fullName;
private LocalDateTime createdAt;
private LocalDateTime updatedAt;
private Boolean active;
// 構造函數
public User() {}
public User(Long id, String username, String email, String password,
String fullName, LocalDateTime createdAt, LocalDateTime updatedAt, Boolean active) {
this.id = id;
this.username = username;
this.email = email;
this.password = password;
this.fullName = fullName;
this.createdAt = createdAt;
this.updatedAt = updatedAt;
this.active = active;
}
// Getters and Setters
public Long getId() { return id; }
public void setId(Long id) { this.id = id; }
public String getUsername() { return username; }
public void setUsername(String username) { this.username = username; }
public String getEmail() { return email; }
public void setEmail(String email) { this.email = email; }
public String getPassword() { return password; }
public void setPassword(String password) { this.password = password; }
public String getFullName() { return fullName; }
public void setFullName(String fullName) { this.fullName = fullName; }
public LocalDateTime getCreatedAt() { return createdAt; }
public void setCreatedAt(LocalDateTime createdAt) { this.createdAt = createdAt; }
public LocalDateTime getUpdatedAt() { return updatedAt; }
public void setUpdatedAt(LocalDateTime updatedAt) { this.updatedAt = updatedAt; }
public Boolean isActive() { return active; }
public void setActive(Boolean active) { this.active = active; }
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", email='" + email + '\'' +
", fullName='" + fullName + '\'' +
", active=" + active +
'}';
}
}
package com.example.myfirstapp.repository;
import com.example.myfirstapp.entity.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import java.time.LocalDateTime;
import java.util.List;
import java.util.Optional;
@Repository
public class UserRepository {
private final JdbcTemplate jdbcTemplate;
@Autowired
public UserRepository(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}
/**
* 查詢所有用戶
*/
public List<User> findAll() {
String sql = "SELECT id, username, email, password, full_name, " +
"created_at, updated_at, active FROM users ORDER BY id";
return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(User.class));
}
/**
* 根據 ID 查詢用戶
*/
public Optional<User> findById(Long id) {
String sql = "SELECT id, username, email, password, full_name, " +
"created_at, updated_at, active FROM users WHERE id = ?";
try {
User user = jdbcTemplate.queryForObject(sql,
new BeanPropertyRowMapper<>(User.class), id);
return Optional.ofNullable(user);
} catch (Exception e) {
return Optional.empty();
}
}
/**
* 根據用戶名查詢用戶
*/
public Optional<User> findByUsername(String username) {
String sql = "SELECT id, username, email, password, full_name, " +
"created_at, updated_at, active FROM users WHERE username = ?";
try {
User user = jdbcTemplate.queryForObject(sql,
new BeanPropertyRowMapper<>(User.class), username);
return Optional.ofNullable(user);
} catch (Exception e) {
return Optional.empty();
}
}
/**
* 根據電子郵件查詢用戶
*/
public Optional<User> findByEmail(String email) {
String sql = "SELECT id, username, email, password, full_name, " +
"created_at, updated_at, active FROM users WHERE email = ?";
try {
User user = jdbcTemplate.queryForObject(sql,
new BeanPropertyRowMapper<>(User.class), email);
return Optional.ofNullable(user);
} catch (Exception e) {
return Optional.empty();
}
}
/**
* 檢查用戶名是否存在
*/
public boolean existsByUsername(String username) {
String sql = "SELECT COUNT(*) FROM users WHERE username = ?";
Integer count = jdbcTemplate.queryForObject(sql, Integer.class, username);
return count != null && count > 0;
}
/**
* 檢查電子郵件是否存在
*/
public boolean existsByEmail(String email) {
String sql = "SELECT COUNT(*) FROM users WHERE email = ?";
Integer count = jdbcTemplate.queryForObject(sql, Integer.class, email);
return count != null && count > 0;
}
/**
* 插入新用戶
*/
public User save(User user) {
if (user.getId() == null) {
// 插入新用戶
String sql = "INSERT INTO users (username, email, password, full_name, " +
"created_at, updated_at, active) VALUES (?, ?, ?, ?, ?, ?, ?)";
LocalDateTime now = LocalDateTime.now();
jdbcTemplate.update(sql,
user.getUsername(),
user.getEmail(),
user.getPassword(),
user.getFullName(),
now,
now,
user.isActive() != null ? user.isActive() : true
);
// 獲取生成的 ID
String currSql = "SELECT currval(pg_get_serial_sequence('users', 'id'))";
Long id = jdbcTemplate.queryForObject(currSql, Long.class);
user.setId(id);
user.setCreatedAt(now);
user.setUpdatedAt(now);
} else {
// 更新現有用戶
String sql = "UPDATE users SET username = ?, email = ?, password = ?, " +
"full_name = ?, updated_at = ?, active = ? WHERE id = ?";
jdbcTemplate.update(sql,
user.getUsername(),
user.getEmail(),
user.getPassword(),
user.getFullName(),
LocalDateTime.now(),
user.isActive(),
user.getId()
);
user.setUpdatedAt(LocalDateTime.now());
}
return user;
}
/**
* 刪除用戶
*/
public void deleteById(Long id) {
String sql = "DELETE FROM users WHERE id = ?";
jdbcTemplate.update(sql, id);
}
/**
* 根據名稱搜索用戶
*/
public List<User> searchByName(String name) {
String sql = "SELECT id, username, email, password, full_name, " +
"created_at, updated_at, active FROM users " +
"WHERE username ILIKE ? OR full_name ILIKE ? OR email ILIKE ? " +
"ORDER BY id";
String searchPattern = "%" + name + "%";
return jdbcTemplate.query(sql,
new BeanPropertyRowMapper<>(User.class),
searchPattern, searchPattern, searchPattern);
}
/**
* 統計用戶數量
*/
public int count() {
String sql = "SELECT COUNT(*) FROM users";
Integer count = jdbcTemplate.queryForObject(sql, Integer.class);
return count != null ? count : 0;
}
/**
* 查詢活躍用戶數量
*/
public int countActive() {
String sql = "SELECT COUNT(*) FROM users WHERE active = TRUE";
Integer count = jdbcTemplate.queryForObject(sql, Integer.class);
return count != null ? count : 0;
}
}
6.6 Service 層實作
package com.example.myfirstapp.service;
import com.example.myfirstapp.config.ResourceNotFoundException;
import com.example.myfirstapp.entity.User;
import com.example.myfirstapp.repository.UserRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
@Service
public class UserService {
private final UserRepository userRepository;
@Autowired
public UserService(UserRepository userRepository) {
this.userRepository = userRepository;
}
public List<User> findAll() {
return userRepository.findAll();
}
public User findById(Long id) {
return userRepository.findById(id)
.orElseThrow(() -> new ResourceNotFoundException("User", id));
}
public User findByUsername(String username) {
return userRepository.findByUsername(username)
.orElseThrow(() -> new ResourceNotFoundException("User not found: " + username));
}
public List<User> searchByName(String name) {
return userRepository.searchByName(name);
}
public User save(User user) {
return userRepository.save(user);
}
public void deleteById(Long id) {
if (!userRepository.findById(id).isPresent()) {
throw new ResourceNotFoundException("User", id);
}
userRepository.deleteById(id);
}
public boolean existsByUsername(String username) {
return userRepository.existsByUsername(username);
}
public boolean existsByEmail(String email) {
return userRepository.existsByEmail(email);
}
public int count() {
return userRepository.count();
}
public int countActive() {
return userRepository.countActive();
}
}
6.7 完整的 Controller 實作
package com.example.myfirstapp.controller;
import com.example.myfirstapp.dto.ApiResponse;
import com.example.myfirstapp.dto.CreateUserRequest;
import com.example.myfirstapp.dto.UserResponse;
import com.example.myfirstapp.entity.User;
import com.example.myfirstapp.service.UserService;
import jakarta.validation.Valid;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;
import java.util.List;
import java.util.stream.Collectors;
@RestController
@RequestMapping("/api/users")
public class UserController {
private final UserService userService;
public UserController(UserService userService) {
this.userService = userService;
}
@GetMapping
public ResponseEntity<ApiResponse<List<UserResponse>>> getAllUsers() {
List<User> users = userService.findAll();
List<UserResponse> responses = users.stream()
.map(this::convertToResponse)
.collect(Collectors.toList());
return ResponseEntity.ok(ApiResponse.success(responses));
}
@GetMapping("/{id}")
public ResponseEntity<ApiResponse<UserResponse>> getUser(@PathVariable Long id) {
User user = userService.findById(id);
return ResponseEntity.ok(ApiResponse.success(convertToResponse(user)));
}
@PostMapping
public ResponseEntity<ApiResponse<UserResponse>> createUser(
@Valid @RequestBody CreateUserRequest request) {
// 檢查用戶名是否存在
if (userService.existsByUsername(request.getUsername())) {
return ResponseEntity
.status(HttpStatus.BAD_REQUEST)
.body(ApiResponse.error("用戶名已存在"));
}
// 檢查電子郵件是否存在
if (userService.existsByEmail(request.getEmail())) {
return ResponseEntity
.status(HttpStatus.BAD_REQUEST)
.body(ApiResponse.error("電子郵件已存在"));
}
// 創建用戶
User user = new User();
user.setUsername(request.getUsername());
user.setEmail(request.getEmail());
user.setPassword(request.getPassword());
user.setFullName(request.getFullName());
user.setActive(true);
User saved = userService.save(user);
return ResponseEntity
.status(HttpStatus.CREATED)
.body(ApiResponse.success(convertToResponse(saved)));
}
@PutMapping("/{id}")
public ResponseEntity<ApiResponse<UserResponse>> updateUser(
@PathVariable Long id,
@Valid @RequestBody CreateUserRequest request) {
User existing = userService.findById(id);
// 更新用戶信息
existing.setUsername(request.getUsername());
existing.setEmail(request.getEmail());
existing.setPassword(request.getPassword());
existing.setFullName(request.getFullName());
User updated = userService.save(existing);
return ResponseEntity.ok(ApiResponse.success(convertToResponse(updated)));
}
@DeleteMapping("/{id}")
public ResponseEntity<ApiResponse<Void>> deleteUser(@PathVariable Long id) {
userService.deleteById(id);
return ResponseEntity.ok(ApiResponse.success(null));
}
@GetMapping("/search")
public ResponseEntity<ApiResponse<List<UserResponse>>> searchUsers(
@RequestParam(name = "name", required = false) String name) {
List<User> users;
if (name != null && !name.isEmpty()) {
users = userService.searchByName(name);
} else {
users = userService.findAll();
}
List<UserResponse> responses = users.stream()
.map(this::convertToResponse)
.collect(Collectors.toList());
return ResponseEntity.ok(ApiResponse.success(responses));
}
@GetMapping("/count")
public ResponseEntity<ApiResponse<Integer>> getUserCount() {
return ResponseEntity.ok(ApiResponse.success(userService.count()));
}
@GetMapping("/count/active")
public ResponseEntity<ApiResponse<Integer>> getActiveUserCount() {
return ResponseEntity.ok(ApiResponse.success(userService.countActive()));
}
private UserResponse convertToResponse(User user) {
UserResponse response = new UserResponse();
response.setId(user.getId());
response.setUsername(user.getUsername());
response.setEmail(user.getEmail());
response.setFullName(user.getFullName());
response.setCreatedAt(user.getCreatedAt());
response.setStatus(user.isActive() ? "ACTIVE" : "INACTIVE");
return response;
}
}
Top comments (0)