// 傳統 JDBC 方式
String sql = "SELECT id, name, email FROM users WHERE id = ?";
PreparedStatement stmt = connection.prepareStatement(sql);
stmt.setInt(1, userId);
ResultSet rs = stmt.executeQuery();
if (rs.next()) {
    User user = new User();
    user.setId(rs.getInt("id"));
    user.setName(rs.getString("name"));
    user.setEmail(rs.getString("email"));
}

// ORM 方式
User user = entityManager.find(User.class, userId);

@Entity
@Table(name = "users")
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    
    @Column(name = "username", unique = true, nullable = false)
    private String username;
    
    @Column(name = "email")
    private String email;
    
    @Temporal(TemporalType.TIMESTAMP)
    @Column(name = "created_at")
    private Date createdAt;
    
    // 建構函式、getter、setter...
}

from sqlalchemy import Column, Integer, String, DateTime
from sqlalchemy.ext.declarative import declarative_base
from datetime import datetime

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    
    id = Column(Integer, primary_key=True, autoincrement=True)
    username = Column(String(50), unique=True, nullable=False)
    email = Column(String(100))
    created_at = Column(DateTime, default=datetime.utcnow)

@PersistenceContext
private EntityManager entityManager;

public void saveUser(User user) {
    entityManager.persist(user);
}

public User findUser(Long id) {
    return entityManager.find(User.class, id);
}

from sqlalchemy.orm import sessionmaker

Session = sessionmaker(bind=engine)
session = Session()

def save_user(user):
    session.add(user)
    session.commit()

def find_user(user_id):
    return session.query(User).filter(User.id == user_id).first()

@Transactional
public void transferMoney(Long fromId, Long toId, BigDecimal amount) {
    Account fromAccount = accountRepository.findById(fromId);
    Account toAccount = accountRepository.findById(toId);
    
    fromAccount.withdraw(amount);
    toAccount.deposit(amount);
    
    accountRepository.save(fromAccount);
    accountRepository.save(toAccount);
    // 如果任何步驟失敗，整個交易將回滾
}

def transfer_money(from_id, to_id, amount):
    try:
        from_account = session.query(Account).filter(Account.id == from_id).first()
        to_account = session.query(Account).filter(Account.id == to_id).first()
        
        from_account.withdraw(amount)
        to_account.deposit(amount)
        
        session.commit()
    except Exception as e:
        session.rollback()
        raise e

@Entity
public class User {
    @OneToMany(mappedBy = "user", fetch = FetchType.LAZY)
    private List<Order> orders; // 只有在實際存取時才載入
}

// 使用時
User user = userRepository.findById(1L);
// 此時 orders 尚未載入
List<Order> orders = user.getOrders(); // 現在才執行 SQL 載入 orders

@Entity
public class User {
    @OneToMany(mappedBy = "user", fetch = FetchType.EAGER)
    private List<Order> orders; // 立即載入
}

// 使用時
User user = userRepository.findById(1L); // orders 同時載入

// 傳統 JPA Repository
@Repository
public class UserRepository {
    @PersistenceContext
    private EntityManager entityManager;
    
    public List<User> findByEmail(String email) {
        return entityManager
            .createQuery("SELECT u FROM User u WHERE u.email = :email", User.class)
            .setParameter("email", email)
            .getResultList();
    }
}

// Spring Data JPA Repository
public interface UserRepository extends JpaRepository<User, Long> {
    List<User> findByEmail(String email); // 方法名稱自動生成查詢
    
    @Query("SELECT u FROM User u WHERE u.username LIKE %?1%")
    List<User> findByUsernameContaining(String username);
}

# 宣告式模型
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.orm import relationship

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    username = Column(String(50))
    orders = relationship("Order", back_populates="user")

class Order(Base):
    __tablename__ = 'orders'
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey('users.id'))
    user = relationship("User", back_populates="orders")

# Django 模型
from django.db import models

class User(models.Model):
    username = models.CharField(max_length=50, unique=True)
    email = models.EmailField()
    created_at = models.DateTimeField(auto_now_add=True)
    
    class Meta:
        db_table = 'users'

# 使用方式
users = User.objects.filter(username__startswith='john')
user = User.objects.get(id=1)

public class User
{
    public int Id { get; set; }
    public string Username { get; set; }
    public List<Order> Orders { get; set; }
}

public class ApplicationDbContext : DbContext
{
    public DbSet<User> Users { get; set; }
    public DbSet<Order> Orders { get; set; }
}

@Entity()
export class User {
    @PrimaryGeneratedColumn()
    id: number;

    @Column({ unique: true })
    username: string;

    @OneToMany(() => Order, order => order.user)
    orders: Order[];
}

<!-- pom.xml -->
<project>
    <properties>
        <java.version>17</java.version>
        <spring.boot.version>3.1.0</spring.boot.version>
    </properties>
    
    <dependencies>
        <!-- Spring Boot Starter -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        
        <!-- 資料庫驅動 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        
        <!-- H2 資料庫 (測試用) -->
        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
            <scope>test</scope>
        </dependency>
        
        <!-- Validation -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-validation</artifactId>
        </dependency>
    </dependencies>
</project>

# application.yml
spring:
  datasource:
    url: jdbc:mysql://localhost:3306/tutorial_db?serverTimezone=UTC
    username: tutorial_user
    password: tutorial_password
    driver-class-name: com.mysql.cj.jdbc.Driver
  
  jpa:
    hibernate:
      ddl-auto: update  # 開發環境用，生產環境建議用 validate
    show-sql: true      # 顯示 SQL 語句
    format-sql: true    # 格式化 SQL 語句
    properties:
      hibernate:
        dialect: org.hibernate.dialect.MySQL8Dialect
        format_sql: true
        use_sql_comments: true
  
  logging:
    level:
      org.hibernate.SQL: DEBUG
      org.hibernate.type.descriptor.sql.BasicBinder: TRACE

-- schema.sql
CREATE DATABASE IF NOT EXISTS tutorial_db 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

CREATE USER IF NOT EXISTS 'tutorial_user'@'localhost' 
IDENTIFIED BY 'tutorial_password';

GRANT ALL PRIVILEGES ON tutorial_db.* 
TO 'tutorial_user'@'localhost';

FLUSH PRIVILEGES;

# 建立虛擬環境
python -m venv orm_tutorial
source orm_tutorial/bin/activate  # Linux/Mac
# 或
orm_tutorial\Scripts\activate     # Windows

# 安裝必要套件
pip install sqlalchemy
pip install mysql-connector-python  # MySQL 驅動
pip install alembic                 # 資料庫遷移工具
pip install python-dotenv           # 環境變數管理

sqlalchemy==2.0.19
mysql-connector-python==8.1.0
alembic==1.11.1
python-dotenv==1.0.0
pytest==7.4.0
pytest-cov==4.1.0

# database.py
import os
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from dotenv import load_dotenv

load_dotenv()

# 資料庫 URL 設定
DATABASE_URL = os.getenv(
    "DATABASE_URL", 
    "mysql+mysqlconnector://tutorial_user:tutorial_password@localhost:3306/tutorial_db"
)

# 建立引擎
engine = create_engine(
    DATABASE_URL,
    echo=True,  # 顯示 SQL 語句
    pool_pre_ping=True,  # 連線檢查
    pool_recycle=3600    # 連線回收時間
)

# Session 工廠
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

# 基礎模型類別
Base = declarative_base()

# 依賴注入函數
def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()

# .env
DATABASE_URL=mysql+mysqlconnector://tutorial_user:tutorial_password@localhost:3306/tutorial_db
DEBUG=True
LOG_LEVEL=INFO

// DatabaseConnectionTest.java
@SpringBootTest
public class DatabaseConnectionTest {
    
    @Autowired
    private DataSource dataSource;
    
    @Test
    public void testDatabaseConnection() throws SQLException {
        try (Connection connection = dataSource.getConnection()) {
            assertThat(connection).isNotNull();
            assertThat(connection.isValid(1)).isTrue();
        }
    }
}

# test_database.py
import pytest
from database import engine, SessionLocal, Base

def test_database_connection():
    """測試資料庫連線"""
    try:
        # 測試連線
        with engine.connect() as connection:
            result = connection.execute("SELECT 1")
            assert result.fetchone()[0] == 1
        
        # 測試 Session
        session = SessionLocal()
        try:
            session.execute("SELECT 1")
        finally:
            session.close()
            
    except Exception as e:
        pytest.fail(f"資料庫連線失敗: {e}")

if __name__ == "__main__":
    test_database_connection()
    print("資料庫連線測試成功！")

// User.java
@Entity
@Table(name = "users")
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    
    @Column(name = "username", unique = true, nullable = false, length = 50)
    private String username;
    
    @Column(name = "email", nullable = false, length = 100)
    private String email;
    
    @Column(name = "full_name", length = 100)
    private String fullName;
    
    @Enumerated(EnumType.STRING)
    @Column(name = "status")
    private UserStatus status = UserStatus.ACTIVE;
    
    @CreationTimestamp
    @Column(name = "created_at", nullable = false, updatable = false)
    private LocalDateTime createdAt;
    
    @UpdateTimestamp
    @Column(name = "updated_at")
    private LocalDateTime updatedAt;
    
    // 建構函式
    public User() {}
    
    public User(String username, String email, String fullName) {
        this.username = username;
        this.email = email;
        this.fullName = fullName;
    }
    
    // Getter 和 Setter
    // ... 省略 getter/setter 方法
    
    @Override
    public String toString() {
        return String.format("User{id=%d, username='%s', email='%s'}", 
                           id, username, email);
    }
}

// UserStatus.java
public enum UserStatus {
    ACTIVE, INACTIVE, SUSPENDED
}

# models.py
from sqlalchemy import Column, Integer, String, DateTime, Enum
from sqlalchemy.ext.declarative import declarative_base
from datetime import datetime
import enum

Base = declarative_base()

class UserStatus(enum.Enum):
    ACTIVE = "active"
    INACTIVE = "inactive"
    SUSPENDED = "suspended"

class User(Base):
    __tablename__ = 'users'
    
    id = Column(Integer, primary_key=True, autoincrement=True)
    username = Column(String(50), unique=True, nullable=False)
    email = Column(String(100), nullable=False)
    full_name = Column(String(100))
    status = Column(Enum(UserStatus), default=UserStatus.ACTIVE)
    created_at = Column(DateTime, default=datetime.utcnow, nullable=False)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    
    def __init__(self, username, email, full_name=None):
        self.username = username
        self.email = email
        self.full_name = full_name
    
    def __repr__(self):
        return f"<User(id={self.id}, username='{self.username}', email='{self.email}')>"
    
    def to_dict(self):
        return {
            'id': self.id,
            'username': self.username,
            'email': self.email,
            'full_name': self.full_name,
            'status': self.status.value if self.status else None,
            'created_at': self.created_at.isoformat() if self.created_at else None,
            'updated_at': self.updated_at.isoformat() if self.updated_at else None
        }

// UserRepository.java
@Repository
public interface UserRepository extends JpaRepository<User, Long> {
    Optional<User> findByUsername(String username);
    Optional<User> findByEmail(String email);
    List<User> findByStatus(UserStatus status);
}

// UserService.java
@Service
@Transactional
public class UserService {
    
    @Autowired
    private UserRepository userRepository;
    
    public User createUser(String username, String email, String fullName) {
        // 檢查用戶名是否已存在
        if (userRepository.findByUsername(username).isPresent()) {
            throw new IllegalArgumentException("用戶名已存在: " + username);
        }
        
        // 檢查郵箱是否已存在
        if (userRepository.findByEmail(email).isPresent()) {
            throw new IllegalArgumentException("郵箱已存在: " + email);
        }
        
        User user = new User(username, email, fullName);
        return userRepository.save(user);
    }
    
    public List<User> createUsers(List<User> users) {
        return userRepository.saveAll(users);
    }
}

// 使用範例
@RestController
@RequestMapping("/api/users")
public class UserController {
    
    @Autowired
    private UserService userService;
    
    @PostMapping
    public ResponseEntity<User> createUser(@RequestBody @Valid CreateUserRequest request) {
        User user = userService.createUser(
            request.getUsername(), 
            request.getEmail(), 
            request.getFullName()
        );
        return ResponseEntity.status(HttpStatus.CREATED).body(user);
    }
}

# user_service.py
from sqlalchemy.orm import Session
from sqlalchemy.exc import IntegrityError
from models import User, UserStatus
from database import SessionLocal

class UserService:
    def __init__(self):
        self.db = SessionLocal()
    
    def create_user(self, username: str, email: str, full_name: str = None) -> User:
        """建立新用戶"""
        try:
            # 檢查用戶名是否已存在
            existing_user = self.db.query(User).filter(User.username == username).first()
            if existing_user:
                raise ValueError(f"用戶名已存在: {username}")
            
            # 檢查郵箱是否已存在
            existing_email = self.db.query(User).filter(User.email == email).first()
            if existing_email:
                raise ValueError(f"郵箱已存在: {email}")
            
            # 建立新用戶
            user = User(username=username, email=email, full_name=full_name)
            self.db.add(user)
            self.db.commit()
            self.db.refresh(user)  # 重新載入以獲取生成的 ID
            return user
            
        except IntegrityError as e:
            self.db.rollback()
            raise ValueError(f"資料完整性錯誤: {str(e)}")
        except Exception as e:
            self.db.rollback()
            raise e
    
    def create_users_batch(self, users_data: list) -> list:
        """批次建立用戶"""
        try:
            users = [User(**user_data) for user_data in users_data]
            self.db.add_all(users)
            self.db.commit()
            return users
        except Exception as e:
            self.db.rollback()
            raise e
    
    def __del__(self):
        if hasattr(self, 'db'):
            self.db.close()

# 使用範例
def example_create_user():
    user_service = UserService()
    
    # 建立單一用戶
    try:
        user = user_service.create_user(
            username="john_doe",
            email="john@example.com",
            full_name="John Doe"
        )
        print(f"用戶建立成功: {user}")
    except ValueError as e:
        print(f"建立失敗: {e}")

// UserService.java (查詢方法)
@Service
@Transactional(readOnly = true)
public class UserService {
    
    // 根據 ID 查詢
    public Optional<User> findById(Long id) {
        return userRepository.findById(id);
    }
    
    // 根據用戶名查詢
    public Optional<User> findByUsername(String username) {
        return userRepository.findByUsername(username);
    }
    
    // 查詢所有用戶
    public List<User> findAllUsers() {
        return userRepository.findAll();
    }
    
    // 分頁查詢
    public Page<User> findUsersWithPagination(int page, int size) {
        Pageable pageable = PageRequest.of(page, size, Sort.by("createdAt").descending());
        return userRepository.findAll(pageable);
    }
    
    // 條件查詢
    public List<User> findActiveUsers() {
        return userRepository.findByStatus(UserStatus.ACTIVE);
    }
    
    // 複雜查詢
    @Query("SELECT u FROM User u WHERE u.email LIKE %:domain%")
    public List<User> findUsersByEmailDomain(@Param("domain") String domain);
    
    // 統計查詢
    public long countActiveUsers() {
        return userRepository.countByStatus(UserStatus.ACTIVE);
    }
}

// 使用範例
@RestController
@RequestMapping("/api/users")
public class UserController {
    
    @GetMapping("/{id}")
    public ResponseEntity<User> getUserById(@PathVariable Long id) {
        return userService.findById(id)
                .map(user -> ResponseEntity.ok(user))
                .orElse(ResponseEntity.notFound().build());
    }
    
    @GetMapping
    public ResponseEntity<Page<User>> getUsers(
            @RequestParam(defaultValue = "0") int page,
            @RequestParam(defaultValue = "10") int size) {
        Page<User> users = userService.findUsersWithPagination(page, size);
        return ResponseEntity.ok(users);
    }
}

# user_service.py (查詢方法)
from typing import List, Optional
from sqlalchemy import func, desc

class UserService:
    
    def find_by_id(self, user_id: int) -> Optional[User]:
        """根據 ID 查詢用戶"""
        return self.db.query(User).filter(User.id == user_id).first()
    
    def find_by_username(self, username: str) -> Optional[User]:
        """根據用戶名查詢用戶"""
        return self.db.query(User).filter(User.username == username).first()
    
    def find_all_users(self) -> List[User]:
        """查詢所有用戶"""
        return self.db.query(User).all()
    
    def find_users_with_pagination(self, page: int = 1, size: int = 10) -> dict:
        """分頁查詢用戶"""
        offset = (page - 1) * size
        users = (self.db.query(User)
                .order_by(desc(User.created_at))
                .offset(offset)
                .limit(size)
                .all())
        
        total = self.db.query(func.count(User.id)).scalar()
        
        return {
            'users': users,
            'total': total,
            'page': page,
            'size': size,
            'total_pages': (total + size - 1) // size
        }
    
    def find_active_users(self) -> List[User]:
        """查詢活躍用戶"""
        return self.db.query(User).filter(User.status == UserStatus.ACTIVE).all()
    
    def find_users_by_email_domain(self, domain: str) -> List[User]:
        """根據郵箱域名查詢用戶"""
        return self.db.query(User).filter(User.email.like(f'%{domain}%')).all()
    
    def count_active_users(self) -> int:
        """統計活躍用戶數量"""
        return self.db.query(func.count(User.id)).filter(User.status == UserStatus.ACTIVE).scalar()
    
    def search_users(self, keyword: str) -> List[User]:
        """搜尋用戶（用戶名或全名）"""
        return (self.db.query(User)
                .filter(
                    (User.username.like(f'%{keyword}%')) |
                    (User.full_name.like(f'%{keyword}%'))
                )
                .all())

# 使用範例
def example_read_users():
    user_service = UserService()
    
    # 查詢單一用戶
    user = user_service.find_by_id(1)
    if user:
        print(f"找到用戶: {user}")
    else:
        print("用戶不存在")
    
    # 分頁查詢
    result = user_service.find_users_with_pagination(page=1, size=5)
    print(f"總共 {result['total']} 位用戶，當前頁面有 {len(result['users'])} 位")
    
    # 搜尋用戶
    users = user_service.search_users("john")
    print(f"搜尋結果: {len(users)} 位用戶")

// UserService.java (更新方法)
@Service
@Transactional
public class UserService {
    
    public User updateUser(Long id, String email, String fullName) {
        User user = userRepository.findById(id)
                .orElseThrow(() -> new EntityNotFoundException("用戶不存在: " + id));
        
        // 檢查郵箱是否被其他用戶使用
        userRepository.findByEmail(email)
                .filter(existingUser -> !existingUser.getId().equals(id))
                .ifPresent(existingUser -> {
                    throw new IllegalArgumentException("郵箱已被使用: " + email);
                });
        
        user.setEmail(email);
        user.setFullName(fullName);
        
        return userRepository.save(user);
    }
    
    public User updateUserStatus(Long id, UserStatus status) {
        User user = userRepository.findById(id)
                .orElseThrow(() -> new EntityNotFoundException("用戶不存在: " + id));
        
        user.setStatus(status);
        return userRepository.save(user);
    }
    
    // 批次更新
    public List<User> updateUsersStatus(List<Long> userIds, UserStatus status) {
        List<User> users = userRepository.findAllById(userIds);
        users.forEach(user -> user.setStatus(status));
        return userRepository.saveAll(users);
    }
    
    // 條件更新
    @Modifying
    @Query("UPDATE User u SET u.status = :status WHERE u.createdAt < :cutoffDate")
    public int updateInactiveUsers(@Param("status") UserStatus status, 
                                 @Param("cutoffDate") LocalDateTime cutoffDate) {
        return userRepository.updateInactiveUsers(status, cutoffDate);
    }
}

// 使用範例
@PutMapping("/{id}")
public ResponseEntity<User> updateUser(@PathVariable Long id, 
                                     @RequestBody @Valid UpdateUserRequest request) {
    try {
        User updatedUser = userService.updateUser(id, request.getEmail(), request.getFullName());
        return ResponseEntity.ok(updatedUser);
    } catch (EntityNotFoundException e) {
        return ResponseEntity.notFound().build();
    }
}

# user_service.py (更新方法)
class UserService:
    
    def update_user(self, user_id: int, email: str = None, full_name: str = None) -> User:
        """更新用戶資訊"""
        try:
            user = self.db.query(User).filter(User.id == user_id).first()
            if not user:
                raise ValueError(f"用戶不存在: {user_id}")
            
            # 檢查郵箱是否被其他用戶使用
            if email:
                existing_user = (self.db.query(User)
                               .filter(User.email == email, User.id != user_id)
                               .first())
                if existing_user:
                    raise ValueError(f"郵箱已被使用: {email}")
                user.email = email
            
            if full_name is not None:
                user.full_name = full_name
            
            # 更新時間會自動設定
            self.db.commit()
            self.db.refresh(user)
            return user
            
        except Exception as e:
            self.db.rollback()
            raise e
    
    def update_user_status(self, user_id: int, status: UserStatus) -> User:
        """更新用戶狀態"""
        try:
            user = self.db.query(User).filter(User.id == user_id).first()
            if not user:
                raise ValueError(f"用戶不存在: {user_id}")
            
            user.status = status
            self.db.commit()
            self.db.refresh(user)
            return user
            
        except Exception as e:
            self.db.rollback()
            raise e
    
    def update_users_status_batch(self, user_ids: List[int], status: UserStatus) -> int:
        """批次更新用戶狀態"""
        try:
            updated_count = (self.db.query(User)
                           .filter(User.id.in_(user_ids))
                           .update({User.status: status}, synchronize_session=False))
            self.db.commit()
            return updated_count
            
        except Exception as e:
            self.db.rollback()
            raise e
    
    def update_inactive_users(self, cutoff_date, status: UserStatus) -> int:
        """條件更新：將特定日期前的用戶設為非活躍"""
        try:
            updated_count = (self.db.query(User)
                           .filter(User.created_at < cutoff_date)
                           .update({User.status: status}, synchronize_session=False))
            self.db.commit()
            return updated_count
            
        except Exception as e:
            self.db.rollback()
            raise e

# 使用範例
def example_update_user():
    user_service = UserService()
    
    try:
        # 更新用戶資訊
        updated_user = user_service.update_user(
            user_id=1,
            email="new_email@example.com",
            full_name="New Full Name"
        )
        print(f"用戶更新成功: {updated_user}")
        
        # 更新用戶狀態
        user_service.update_user_status(1, UserStatus.INACTIVE)
        print("用戶狀態更新成功")
        
    except ValueError as e:
        print(f"更新失敗: {e}")

// UserService.java (刪除方法)
@Service
@Transactional
public class UserService {
    
    public void deleteUser(Long id) {
        if (!userRepository.existsById(id)) {
            throw new EntityNotFoundException("用戶不存在: " + id);
        }
        userRepository.deleteById(id);
    }
    
    public void deleteUserSoft(Long id) {
        User user = userRepository.findById(id)
                .orElseThrow(() -> new EntityNotFoundException("用戶不存在: " + id));
        
        user.setStatus(UserStatus.INACTIVE);
        userRepository.save(user);
    }
    
    // 批次刪除
    public void deleteUsers(List<Long> userIds) {
        List<User> users = userRepository.findAllById(userIds);
        if (users.size() != userIds.size()) {
            throw new IllegalArgumentException("部分用戶不存在");
        }
        userRepository.deleteAll(users);
    }
    
    // 條件刪除
    @Modifying
    @Query("DELETE FROM User u WHERE u.status = :status AND u.createdAt < :cutoffDate")
    public int deleteInactiveUsers(@Param("status") UserStatus status, 
                                 @Param("cutoffDate") LocalDateTime cutoffDate) {
        return userRepository.deleteInactiveUsers(status, cutoffDate);
    }
}

// 使用範例
@DeleteMapping("/{id}")
public ResponseEntity<Void> deleteUser(@PathVariable Long id) {
    try {
        userService.deleteUser(id);
        return ResponseEntity.noContent().build();
    } catch (EntityNotFoundException e) {
        return ResponseEntity.notFound().build();
    }
}

# user_service.py (刪除方法)
class UserService:
    
    def delete_user(self, user_id: int) -> bool:
        """刪除用戶"""
        try:
            user = self.db.query(User).filter(User.id == user_id).first()
            if not user:
                raise ValueError(f"用戶不存在: {user_id}")
            
            self.db.delete(user)
            self.db.commit()
            return True
            
        except Exception as e:
            self.db.rollback()
            raise e
    
    def delete_user_soft(self, user_id: int) -> User:
        """軟刪除用戶（設為非活躍狀態）"""
        return self.update_user_status(user_id, UserStatus.INACTIVE)
    
    def delete_users_batch(self, user_ids: List[int]) -> int:
        """批次刪除用戶"""
        try:
            deleted_count = (self.db.query(User)
                           .filter(User.id.in_(user_ids))
                           .delete(synchronize_session=False))
            self.db.commit()
            return deleted_count
            
        except Exception as e:
            self.db.rollback()
            raise e
    
    def delete_inactive_users(self, cutoff_date) -> int:
        """條件刪除：刪除特定日期前的非活躍用戶"""
        try:
            deleted_count = (self.db.query(User)
                           .filter(
                               User.status == UserStatus.INACTIVE,
                               User.created_at < cutoff_date
                           )
                           .delete(synchronize_session=False))
            self.db.commit()
            return deleted_count
            
        except Exception as e:
            self.db.rollback()
            raise e

# 使用範例
def example_delete_user():
    user_service = UserService()
    
    try:
        # 刪除單一用戶
        success = user_service.delete_user(1)
        if success:
            print("用戶刪除成功")
        
        # 批次刪除
        deleted_count = user_service.delete_users_batch([2, 3, 4])
        print(f"成功刪除 {deleted_count} 位用戶")
        
    except ValueError as e:
        print(f"刪除失敗: {e}")

// UserCrudService.java
@Service
@Transactional
@Slf4j
public class UserCrudService {
    
    @Autowired
    private UserRepository userRepository;
    
    // CREATE
    public User create(CreateUserRequest request) {
        log.info("建立用戶: {}", request.getUsername());
        
        validateUniqueUsername(request.getUsername());
        validateUniqueEmail(request.getEmail());
        
        User user = new User(request.getUsername(), request.getEmail(), request.getFullName());
        User savedUser = userRepository.save(user);
        
        log.info("用戶建立成功: {}", savedUser.getId());
        return savedUser;
    }
    
    // READ
    @Transactional(readOnly = true)
    public UserDto getById(Long id) {
        User user = userRepository.findById(id)
                .orElseThrow(() -> new EntityNotFoundException("用戶不存在: " + id));
        return convertToDto(user);
    }
    
    // UPDATE
    public UserDto update(Long id, UpdateUserRequest request) {
        log.info("更新用戶: {}", id);
        
        User user = userRepository.findById(id)
                .orElseThrow(() -> new EntityNotFoundException("用戶不存在: " + id));
        
        if (!user.getEmail().equals(request.getEmail())) {
            validateUniqueEmail(request.getEmail());
            user.setEmail(request.getEmail());
        }
        
        user.setFullName(request.getFullName());
        User updatedUser = userRepository.save(user);
        
        log.info("用戶更新成功: {}", id);
        return convertToDto(updatedUser);
    }
    
    // DELETE
    public void delete(Long id) {
        log.info("刪除用戶: {}", id);
        
        if (!userRepository.existsById(id)) {
            throw new EntityNotFoundException("用戶不存在: " + id);
        }
        
        userRepository.deleteById(id);
        log.info("用戶刪除成功: {}", id);
    }
    
    // 工具方法
    private void validateUniqueUsername(String username) {
        if (userRepository.findByUsername(username).isPresent()) {
            throw new IllegalArgumentException("用戶名已存在: " + username);
        }
    }
    
    private void validateUniqueEmail(String email) {
        if (userRepository.findByEmail(email).isPresent()) {
            throw new IllegalArgumentException("郵箱已存在: " + email);
        }
    }
    
    private UserDto convertToDto(User user) {
        return new UserDto(
            user.getId(),
            user.getUsername(),
            user.getEmail(),
            user.getFullName(),
            user.getStatus(),
            user.getCreatedAt(),
            user.getUpdatedAt()
        );
    }
}

# user_crud_service.py
import logging
from typing import Optional, List
from datetime import datetime
from models import User, UserStatus
from database import SessionLocal

logger = logging.getLogger(__name__)

class UserCrudService:
    def __init__(self):
        self.db = SessionLocal()
    
    def create(self, username: str, email: str, full_name: str = None) -> dict:
        """建立用戶"""
        logger.info(f"建立用戶: {username}")
        
        try:
            self._validate_unique_username(username)
            self._validate_unique_email(email)
            
            user = User(username=username, email=email, full_name=full_name)
            self.db.add(user)
            self.db.commit()
            self.db.refresh(user)
            
            logger.info(f"用戶建立成功: {user.id}")
            return self._to_dict(user)
            
        except Exception as e:
            self.db.rollback()
            logger.error(f"建立用戶失敗: {str(e)}")
            raise e
    
    def get_by_id(self, user_id: int) -> dict:
        """根據 ID 查詢用戶"""
        user = self.db.query(User).filter(User.id == user_id).first()
        if not user:
            raise ValueError(f"用戶不存在: {user_id}")
        return self._to_dict(user)
    
    def update(self, user_id: int, email: str = None, full_name: str = None) -> dict:
        """更新用戶"""
        logger.info(f"更新用戶: {user_id}")
        
        try:
            user = self.db.query(User).filter(User.id == user_id).first()
            if not user:
                raise ValueError(f"用戶不存在: {user_id}")
            
            if email and email != user.email:
                self._validate_unique_email(email, exclude_id=user_id)
                user.email = email
            
            if full_name is not None:
                user.full_name = full_name
            
            self.db.commit()
            self.db.refresh(user)
            
            logger.info(f"用戶更新成功: {user_id}")
            return self._to_dict(user)
            
        except Exception as e:
            self.db.rollback()
            logger.error(f"更新用戶失敗: {str(e)}")
            raise e
    
    def delete(self, user_id: int) -> bool:
        """刪除用戶"""
        logger.info(f"刪除用戶: {user_id}")
        
        try:
            user = self.db.query(User).filter(User.id == user_id).first()
            if not user:
                raise ValueError(f"用戶不存在: {user_id}")
            
            self.db.delete(user)
            self.db.commit()
            
            logger.info(f"用戶刪除成功: {user_id}")
            return True
            
        except Exception as e:
            self.db.rollback()
            logger.error(f"刪除用戶失敗: {str(e)}")
            raise e
    
    # 工具方法
    def _validate_unique_username(self, username: str):
        existing_user = self.db.query(User).filter(User.username == username).first()
        if existing_user:
            raise ValueError(f"用戶名已存在: {username}")
    
    def _validate_unique_email(self, email: str, exclude_id: int = None):
        query = self.db.query(User).filter(User.email == email)
        if exclude_id:
            query = query.filter(User.id != exclude_id)
        
        existing_user = query.first()
        if existing_user:
            raise ValueError(f"郵箱已存在: {email}")
    
    def _to_dict(self, user: User) -> dict:
        return user.to_dict()
    
    def __del__(self):
        if hasattr(self, 'db'):
            self.db.close()

// User.java
@Entity
@Table(name = "users")
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    
    @Column(unique = true, nullable = false)
    private String username;
    
    // 一對一關聯：用戶檔案
    @OneToOne(mappedBy = "user", cascade = CascadeType.ALL, fetch = FetchType.LAZY)
    private UserProfile profile;
    
    // 一對一關聯：用戶設定
    @OneToOne(cascade = CascadeType.ALL, fetch = FetchType.LAZY)
    @JoinColumn(name = "user_settings_id")
    private UserSettings settings;
    
    // getter/setter...
}

// UserProfile.java
@Entity
@Table(name = "user_profiles")
public class UserProfile {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    
    @Column(name = "first_name")
    private String firstName;
    
    @Column(name = "last_name")
    private String lastName;
    
    @Column(name = "phone_number")
    private String phoneNumber;
    
    @Lob
    private String bio;
    
    // 反向關聯
    @OneToOne
    @JoinColumn(name = "user_id", nullable = false)
    private User user;
    
    // getter/setter...
}

// UserSettings.java
@Entity
@Table(name = "user_settings")
public class UserSettings {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    
    @Column(name = "email_notifications")
    private Boolean emailNotifications = true;
    
    @Column(name = "theme")
    private String theme = "light";
    
    @Column(name = "language")
    private String language = "zh-TW";
    
    // getter/setter...
}

# models.py
from sqlalchemy import Column, Integer, String, Boolean, Text, ForeignKey
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    
    id = Column(Integer, primary_key=True)
    username = Column(String(50), unique=True, nullable=False)
    
    # 一對一關聯：用戶檔案
    profile = relationship("UserProfile", back_populates="user", uselist=False, cascade="all, delete-orphan")
    
    # 一對一關聯：用戶設定
    settings = relationship("UserSettings", uselist=False, cascade="all, delete-orphan")

class UserProfile(Base):
    __tablename__ = 'user_profiles'
    
    id = Column(Integer, primary_key=True)
    first_name = Column(String(50))
    last_name = Column(String(50))
    phone_number = Column(String(20))
    bio = Column(Text)
    
    # 外鍵
    user_id = Column(Integer, ForeignKey('users.id'), nullable=False)
    
    # 反向關聯
    user = relationship("User", back_populates="profile")

class UserSettings(Base):
    __tablename__ = 'user_settings'
    
    id = Column(Integer, primary_key=True)
    email_notifications = Column(Boolean, default=True)
    theme = Column(String(20), default='light')
    language = Column(String(10), default='zh-TW')
    
    # 外鍵
    user_id = Column(Integer, ForeignKey('users.id'), nullable=False)
    
    # 關聯
    user = relationship("User", back_populates="settings")

// User.java
@Entity
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    
    private String username;
    
    // 一對多：用戶的訂單
    @OneToMany(mappedBy = "user", cascade = CascadeType.ALL, fetch = FetchType.LAZY)
    private List<Order> orders = new ArrayList<>();
    
    // 一對多：用戶的評論
    @OneToMany(mappedBy = "user", cascade = CascadeType.ALL, orphanRemoval = true)
    private List<Comment> comments = new ArrayList<>();
    
    // 便利方法
    public void addOrder(Order order) {
        orders.add(order);
        order.setUser(this);
    }
    
    public void removeOrder(Order order) {
        orders.remove(order);
        order.setUser(null);
    }
    
    // getter/setter...
}

// Order.java
@Entity
@Table(name = "orders")
public class Order {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    
    @Column(name = "order_number", unique = true)
    private String orderNumber;
    
    @Enumerated(EnumType.STRING)
    private OrderStatus status;
    
    @Column(name = "total_amount")
    private BigDecimal totalAmount;
    
    @CreationTimestamp
    @Column(name = "created_at")
    private LocalDateTime createdAt;
    
    // 多對一：訂單屬於一個用戶
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "user_id", nullable = false)
    private User user;
    
    // 一對多：訂單項目
    @OneToMany(mappedBy = "order", cascade = CascadeType.ALL, orphanRemoval = true)
    private List<OrderItem> orderItems = new ArrayList<>();
    
    // getter/setter...
}

// OrderItem.java
@Entity
@Table(name = "order_items")
public class OrderItem {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    
    @Column(name = "product_name")
    private String productName;
    
    private Integer quantity;
    
    @Column(name = "unit_price")
    private BigDecimal unitPrice;
    
    // 多對一：訂單項目屬於一個訂單
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "order_id", nullable = false)
    private Order order;
    
    // getter/setter...
}

# models.py
from sqlalchemy import Column, Integer, String, Numeric, DateTime, ForeignKey, Enum
from sqlalchemy.orm import relationship
from decimal import Decimal
import enum

class OrderStatus(enum.Enum):
    PENDING = "pending"
    CONFIRMED = "confirmed"
    SHIPPED = "shipped"
    DELIVERED = "delivered"
    CANCELLED = "cancelled"

class User(Base):
    __tablename__ = 'users'
    
    id = Column(Integer, primary_key=True)
    username = Column(String(50), unique=True, nullable=False)
    
    # 一對多：用戶的訂單
    orders = relationship("Order", back_populates="user", cascade="all, delete-orphan")
    
    # 一對多：用戶的評論
    comments = relationship("Comment", back_populates="user", cascade="all, delete-orphan")
    
    def add_order(self, order):
        """添加訂單"""
        self.orders.append(order)
        order.user = self
    
    def remove_order(self, order):
        """移除訂單"""
        if order in self.orders:
            self.orders.remove(order)
            order.user = None

class Order(Base):
    __tablename__ = 'orders'
    
    id = Column(Integer, primary_key=True)
    order_number = Column(String(50), unique=True)
    status = Column(Enum(OrderStatus), default=OrderStatus.PENDING)
    total_amount = Column(Numeric(10, 2))
    created_at = Column(DateTime, default=datetime.utcnow)
    
    # 外鍵
    user_id = Column(Integer, ForeignKey('users.id'), nullable=False)
    
    # 關聯
    user = relationship("User", back_populates="orders")
    order_items = relationship("OrderItem", back_populates="order", cascade="all, delete-orphan")
    
    def add_item(self, item):
        """添加訂單項目"""
        self.order_items.append(item)
        item.order = self
    
    def calculate_total(self):
        """計算總金額"""
        total = sum(item.quantity * item.unit_price for item in self.order_items)
        self.total_amount = total
        return total

class OrderItem(Base):
    __tablename__ = 'order_items'
    
    id = Column(Integer, primary_key=True)
    product_name = Column(String(100))
    quantity = Column(Integer)
    unit_price = Column(Numeric(10, 2))
    
    # 外鍵
    order_id = Column(Integer, ForeignKey('orders.id'), nullable=False)
    
    # 關聯
    order = relationship("Order", back_populates="order_items")
    
    @property
    def subtotal(self):
        """計算小計"""
        return self.quantity * self.unit_price if self.quantity and self.unit_price else Decimal('0')

@Entity
@Table(name = "students")
public class Student {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    
    @Column(name = "student_name", nullable = false)
    private String name;
    
    @Column(name = "email", unique = true)
    private String email;
    
    // 多對多關聯：一個學生可以選修多門課程
    @ManyToMany(fetch = FetchType.LAZY)
    @JoinTable(
        name = "student_courses",                    // 中間表名稱
        joinColumns = @JoinColumn(name = "student_id"),        // 當前實體的外鍵
        inverseJoinColumns = @JoinColumn(name = "course_id")   // 關聯實體的外鍵
    )
    private Set<Course> courses = new HashSet<>();
    
    // 建構子、getter、setter
    public Student() {}
    
    public Student(String name, String email) {
        this.name = name;
        this.email = email;
    }
    
    // 便利方法：添加課程
    public void addCourse(Course course) {
        courses.add(course);
        course.getStudents().add(this);
    }
    
    // 便利方法：移除課程
    public void removeCourse(Course course) {
        courses.remove(course);
        course.getStudents().remove(this);
    }
    
    // getter 和 setter...
    public Long getId() { return id; }
    public void setId(Long id) { this.id = id; }
    
    public String getName() { return name; }
    public void setName(String name) { this.name = name; }
    
    public String getEmail() { return email; }
    public void setEmail(String email) { this.email = email; }
    
    public Set<Course> getCourses() { return courses; }
    public void setCourses(Set<Course> courses) { this.courses = courses; }
}

@Entity
@Table(name = "courses")
public class Course {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    
    @Column(name = "course_name", nullable = false)
    private String name;
    
    @Column(name = "course_code", unique = true)
    private String code;
    
    @Column(name = "credits")
    private Integer credits;
    
    // 多對多關聯的反向端
    @ManyToMany(mappedBy = "courses", fetch = FetchType.LAZY)
    private Set<Student> students = new HashSet<>();
    
    // 建構子、getter、setter
    public Course() {}
    
    public Course(String name, String code, Integer credits) {
        this.name = name;
        this.code = code;
        this.credits = credits;
    }
    
    // getter 和 setter...
    public Long getId() { return id; }
    public void setId(Long id) { this.id = id; }
    
    public String getName() { return name; }
    public void setName(String name) { this.name = name; }
    
    public String getCode() { return code; }
    public void setCode(String code) { this.code = code; }
    
    public Integer getCredits() { return credits; }
    public void setCredits(Integer credits) { this.credits = credits; }
    
    public Set<Student> getStudents() { return students; }
    public void setStudents(Set<Student> students) { this.students = students; }
}

from sqlalchemy import Table, Column, Integer, String, ForeignKey
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

# 多對多關聯表
book_authors = Table(
    'book_authors', Base.metadata,
    Column('book_id', Integer, ForeignKey('books.id'), primary_key=True),
    Column('author_id', Integer, ForeignKey('authors.id'), primary_key=True)
)

class Book(Base):
    __tablename__ = 'books'
    
    id = Column(Integer, primary_key=True)
    title = Column(String(200), nullable=False)
    isbn = Column(String(13), unique=True)
    publication_year = Column(Integer)
    
    # 多對多關聯：一本書可以有多個作者
    authors = relationship(
        "Author",
        secondary=book_authors,
        back_populates="books",
        lazy="select"  # 預設為延遲載入
    )
    
    def __init__(self, title, isbn=None, publication_year=None):
        self.title = title
        self.isbn = isbn
        self.publication_year = publication_year
    
    def add_author(self, author):
        """添加作者的便利方法"""
        if author not in self.authors:
            self.authors.append(author)
    
    def remove_author(self, author):
        """移除作者的便利方法"""
        if author in self.authors:
            self.authors.remove(author)
    
    def __repr__(self):
        return f"<Book(id={self.id}, title='{self.title}')>"

class Author(Base):
    __tablename__ = 'authors'
    
    id = Column(Integer, primary_key=True)
    name = Column(String(100), nullable=False)
    birth_year = Column(Integer)
    nationality = Column(String(50))
    
    # 多對多關聯的反向端
    books = relationship(
        "Book",
        secondary=book_authors,
        back_populates="authors",
        lazy="select"
    )
    
    def __init__(self, name, birth_year=None, nationality=None):
        self.name = name
        self.birth_year = birth_year
        self.nationality = nationality
    
    def add_book(self, book):
        """添加書籍的便利方法"""
        if book not in self.books:
            self.books.append(book)
            book.add_author(self)
    
    def __repr__(self):
        return f"<Author(id={self.id}, name='{self.name}')>"

// 中間實體：學生選課記錄
@Entity
@Table(name = "student_course_enrollments")
public class Enrollment {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "student_id")
    private Student student;
    
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "course_id")
    private Course course;
    
    @Column(name = "enrollment_date")
    private LocalDate enrollmentDate;
    
    @Column(name = "grade")
    private String grade;  // A, B, C, D, F
    
    @Column(name = "semester")
    private String semester;
    
    // 建構子
    public Enrollment() {}
    
    public Enrollment(Student student, Course course, LocalDate enrollmentDate, String semester) {
        this.student = student;
        this.course = course;
        this.enrollmentDate = enrollmentDate;
        this.semester = semester;
    }
    
    // getter 和 setter...
    public Long getId() { return id; }
    public void setId(Long id) { this.id = id; }
    
    public Student getStudent() { return student; }
    public void setStudent(Student student) { this.student = student; }
    
    public Course getCourse() { return course; }
    public void setCourse(Course course) { this.course = course; }
    
    public LocalDate getEnrollmentDate() { return enrollmentDate; }
    public void setEnrollmentDate(LocalDate enrollmentDate) { this.enrollmentDate = enrollmentDate; }
    
    public String getGrade() { return grade; }
    public void setGrade(String grade) { this.grade = grade; }
    
    public String getSemester() { return semester; }
    public void setSemester(String semester) { this.semester = semester; }
}

// 更新 Student 實體
@Entity
@Table(name = "students")
public class Student {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    
    private String name;
    private String email;
    
    // 改為一對多關聯到中間實體
    @OneToMany(mappedBy = "student", cascade = CascadeType.ALL, fetch = FetchType.LAZY)
    private Set<Enrollment> enrollments = new HashSet<>();
    
    // 便利方法：選課
    public void enrollInCourse(Course course, String semester) {
        Enrollment enrollment = new Enrollment(this, course, LocalDate.now(), semester);
        enrollments.add(enrollment);
        course.getEnrollments().add(enrollment);
    }
    
    // 便利方法：退課
    public void withdrawFromCourse(Course course) {
        enrollments.removeIf(enrollment -> enrollment.getCourse().equals(course));
        course.getEnrollments().removeIf(enrollment -> enrollment.getStudent().equals(this));
    }
    
    // getter 和 setter...
}

// 更新 Course 實體
@Entity
@Table(name = "courses")
public class Course {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    
    private String name;
    private String code;
    private Integer credits;
    
    @OneToMany(mappedBy = "course", cascade = CascadeType.ALL, fetch = FetchType.LAZY)
    private Set<Enrollment> enrollments = new HashSet<>();
    
    // getter 和 setter...
}

from sqlalchemy import Column, Integer, String, ForeignKey, DateTime, Numeric
from sqlalchemy.orm import relationship
from datetime import datetime

class OrderItem(Base):
    """訂單商品中間實體"""
    __tablename__ = 'order_items'
    
    id = Column(Integer, primary_key=True)
    
    # 外鍵關聯
    order_id = Column(Integer, ForeignKey('orders.id'), nullable=False)
    product_id = Column(Integer, ForeignKey('products.id'), nullable=False)
    
    # 額外屬性
    quantity = Column(Integer, nullable=False, default=1)
    unit_price = Column(Numeric(10, 2), nullable=False)
    discount_rate = Column(Numeric(5, 2), default=0)  # 折扣率
    
    # 關聯
    order = relationship("Order", back_populates="items")
    product = relationship("Product", back_populates="order_items")
    
    def __init__(self, order, product, quantity, unit_price, discount_rate=0):
        self.order = order
        self.product = product
        self.quantity = quantity
        self.unit_price = unit_price
        self.discount_rate = discount_rate
    
    @property
    def subtotal(self):
        """計算小計（含折扣）"""
        base_amount = self.quantity * self.unit_price
        discount_amount = base_amount * (self.discount_rate / 100)
        return base_amount - discount_amount

class Order(Base):
    __tablename__ = 'orders'
    
    id = Column(Integer, primary_key=True)
    order_number = Column(String(50), unique=True, nullable=False)
    order_date = Column(DateTime, default=datetime.now)
    customer_id = Column(Integer, ForeignKey('customers.id'))
    
    # 一對多關聯到中間實體
    items = relationship("OrderItem", back_populates="order", cascade="all, delete-orphan")
    customer = relationship("Customer", back_populates="orders")
    
    def add_product(self, product, quantity, unit_price, discount_rate=0):
        """添加商品到訂單"""
        item = OrderItem(self, product, quantity, unit_price, discount_rate)
        self.items.append(item)
        return item
    
    @property
    def total_amount(self):
        """計算訂單總金額"""
        return sum(item.subtotal for item in self.items)

class Product(Base):
    __tablename__ = 'products'
    
    id = Column(Integer, primary_key=True)
    name = Column(String(200), nullable=False)
    sku = Column(String(50), unique=True)
    price = Column(Numeric(10, 2), nullable=False)
    
    # 一對多關聯到中間實體
    order_items = relationship("OrderItem", back_populates="product")

// ✅ 推薦：使用 Set 避免重複
@ManyToMany
private Set<Course> courses = new HashSet<>();

// ❌ 避免：List 可能導致重複和效能問題
@ManyToMany
private List<Course> courses = new ArrayList<>();

public void addCourse(Course course) {
    courses.add(course);
    course.getStudents().add(this);
}

public void removeCourse(Course course) {
    courses.remove(course);
    course.getStudents().remove(this);
}

// 預設使用 LAZY
@ManyToMany(fetch = FetchType.LAZY)
private Set<Course> courses = new HashSet<>();

// 需要時使用 EntityGraph 載入
@EntityGraph(attributePaths = {"courses"})
Student findByIdWithCourses(Long id);

@Repository
public interface StudentRepository extends JpaRepository<Student, Long> {
    
    // 查找選修特定課程的所有學生
    @Query("SELECT s FROM Student s JOIN s.courses c WHERE c.code = :courseCode")
    List<Student> findStudentsByCourseCode(@Param("courseCode") String courseCode);
    
    // 查找選修課程數量超過指定數量的學生
    @Query("SELECT s FROM Student s WHERE SIZE(s.courses) > :minCourses")
    List<Student> findStudentsWithManyCourses(@Param("minCourses") int minCourses);
    
    // 使用 EntityGraph 載入學生和課程
    @EntityGraph(attributePaths = {"courses"})
    @Query("SELECT s FROM Student s WHERE s.id = :id")
    Optional<Student> findByIdWithCourses(@Param("id") Long id);
}

from sqlalchemy.orm import selectinload

# 查找特定作者的所有書籍
def find_books_by_author(session, author_name):
    return session.query(Book).join(Book.authors).filter(
        Author.name == author_name
    ).all()

# 查找有多個作者的書籍
def find_books_with_multiple_authors(session):
    return session.query(Book).join(Book.authors).group_by(Book.id).having(
        func.count(Author.id) > 1
    ).all()

# 使用 selectinload 預載關聯資料
def find_author_with_books(session, author_id):
    return session.query(Author).options(
        selectinload(Author.books)
    ).filter(Author.id == author_id).first()

# 查找在特定年份出版且有特定作者的書籍
def find_books_by_year_and_author(session, year, author_name):
    return session.query(Book).join(Book.authors).filter(
        and_(
            Book.publication_year == year,
            Author.name.like(f"%{author_name}%")
        )
    ).all()

// ❌ 產生 N+1 問題的程式碼
@Service
public class BadUserService {
    
    public List<UserOrderSummary> getUserOrderSummaries() {
        List<User> users = userRepository.findAll(); // 1 query
        
        return users.stream()
            .map(user -> new UserOrderSummary(
                user.getId(),
                user.getUsername(),
                user.getOrders().size(), // N queries - 每個用戶一次查詢
                calculateTotalAmount(user.getOrders()) // 觸發載入
            ))
            .collect(Collectors.toList());
    }
}

// ✅ 解決方案 1: 使用 JOIN FETCH
@Repository
public interface UserRepository extends JpaRepository<User, Long> {
    
    @Query("SELECT DISTINCT u FROM User u LEFT JOIN FETCH u.orders")
    List<User> findAllWithOrders();
}

// ✅ 解決方案 2: 使用 EntityGraph
@Repository
public interface UserRepository extends JpaRepository<User, Long> {
    
    @EntityGraph(attributePaths = {"orders", "orders.orderItems"})
    List<User> findAllWithOrdersAndItems();
}

// ✅ 解決方案 3: 使用批次載入
@Entity
public class User {
    
    @OneToMany(mappedBy = "user", fetch = FetchType.LAZY)
    @BatchSize(size = 10) // 批次載入，一次載入 10 個用戶的訂單
    private List<Order> orders;
}

// ✅ 解決方案 4: 使用投影查詢
@Repository
public interface UserRepository extends JpaRepository<User, Long> {
    
    @Query("SELECT new com.example.UserOrderSummary(u.id, u.username, COUNT(o), COALESCE(SUM(o.totalAmount), 0)) " +
           "FROM User u LEFT JOIN u.orders o " +
           "GROUP BY u.id, u.username")
    List<UserOrderSummary> findUserOrderSummaries();
}

// ✅ 最佳化的服務實作
@Service
public class OptimizedUserService {
    
    public List<UserOrderSummary> getUserOrderSummaries() {
        // 直接使用聚合查詢，避免 N+1 問題
        return userRepository.findUserOrderSummaries();
    }
    
    public List<User> getUsersWithOrders() {
        // 使用 EntityGraph 一次載入所有關聯資料
        return userRepository.findAllWithOrders();
    }
}

# ❌ 產生 N+1 問題的程式碼
def get_user_order_summaries_bad():
    users = session.query(User).all()  # 1 query
    summaries = []
    
    for user in users:  # N queries
        order_count = len(user.orders)  # 觸發載入
        total_amount = sum(order.total_amount for order in user.orders)
        summaries.append({
            'user_id': user.id,
            'username': user.username,
            'order_count': order_count,
            'total_amount': total_amount
        })
    
    return summaries

# ✅ 解決方案 1: 使用 joinedload
from sqlalchemy.orm import joinedload

def get_users_with_orders_joined():
    return (session.query(User)
            .options(joinedload(User.orders))
            .all())

# ✅ 解決方案 2: 使用 selectinload (推薦用於一對多關聯)
from sqlalchemy.orm import selectinload

def get_users_with_orders_selectin():
    return (session.query(User)
            .options(selectinload(User.orders))
            .all())

# ✅ 解決方案 3: 使用 subqueryload
from sqlalchemy.orm import subqueryload

def get_users_with_orders_subquery():
    return (session.query(User)
            .options(subqueryload(User.orders))
            .all())

# ✅ 解決方案 4: 使用聚合查詢
def get_user_order_summaries_optimized():
    from sqlalchemy import func
    
    result = (session.query(
        User.id,
        User.username,
        func.count(Order.id).label('order_count'),
        func.coalesce(func.sum(Order.total_amount), 0).label('total_amount')
    )
    .outerjoin(Order)
    .group_by(User.id, User.username)
    .all())
    
    return [
        {
            'user_id': row.id,
            'username': row.username,
            'order_count': row.order_count,
            'total_amount': float(row.total_amount)
        }
        for row in result
    ]

# ✅ 解決方案 5: 巢狀載入
def get_users_with_nested_data():
    return (session.query(User)
            .options(
                selectinload(User.orders).selectinload(Order.order_items),
                joinedload(User.profile)
            )
            .all())

# 載入策略比較
class LoadingStrategyComparison:
    
    def compare_loading_strategies(self):
        """比較不同載入策略的效能"""
        import time
        
        strategies = {
            'lazy': self.lazy_loading,
            'joined': self.joined_loading,
            'selectin': self.selectin_loading,
            'subquery': self.subquery_loading
        }
        
        results = {}
        
        for name, strategy in strategies.items():
            start_time = time.time()
            
            # 清除 session 確保公平比較
            session.expunge_all()
            
            users = strategy()
            
            # 觸發載入以測量實際效能
            for user in users:
                _ = len(user.orders)
            
            end_time = time.time()
            results[name] = end_time - start_time
            
            print(f"{name}: {results[name]:.4f} seconds")
        
        return results
    
    def lazy_loading(self):
        return session.query(User).all()
    
    def joined_loading(self):
        return session.query(User).options(joinedload(User.orders)).all()
    
    def selectin_loading(self):
        return session.query(User).options(selectinload(User.orders)).all()
    
    def subquery_loading(self):
        return session.query(User).options(subqueryload(User.orders)).all()

// 使用 Spring Cache 的多層快取
@Configuration
@EnableCaching
public class CacheConfig {
    
    @Bean
    public CacheManager cacheManager() {
        RedisCacheManager.Builder builder = RedisCacheManager
            .RedisCacheManagerBuilder
            .fromConnectionFactory(redisConnectionFactory())
            .cacheDefaults(cacheConfiguration());
        
        return builder.build();
    }
    
    private RedisCacheConfiguration cacheConfiguration() {
        return RedisCacheConfiguration.defaultCacheConfig()
            .entryTtl(Duration.ofMinutes(30))
            .serializeKeysWith(RedisSerializationContext.SerializationPair
                .fromSerializer(new StringRedisSerializer()))
            .serializeValuesWith(RedisSerializationContext.SerializationPair
                .fromSerializer(new GenericJackson2JsonRedisSerializer()));
    }
}

@Service
public class UserCacheService {
    
    @Cacheable(value = "users", key = "#id")
    public User findById(Long id) {
        return userRepository.findById(id).orElse(null);
    }
    
    @Cacheable(value = "userProfiles", key = "#userId")
    public UserProfile findUserProfile(Long userId) {
        return userProfileRepository.findByUserId(userId);
    }
    
    @CacheEvict(value = {"users", "userProfiles"}, key = "#user.id")
    public User updateUser(User user) {
        return userRepository.save(user);
    }
    
    @CacheEvict(value = "users", allEntries = true)
    public void clearAllUserCache() {
        // 清除所有用戶快取
    }
    
    // 條件快取
    @Cacheable(value = "expensiveCalculations", key = "#userId", 
               condition = "#userId > 100", unless = "#result == null")
    public UserStatistics calculateUserStatistics(Long userId) {
        // 昂貴的計算操作
        return performExpensiveCalculation(userId);
    }
    
    // 手動快取管理
    @Autowired
    private CacheManager cacheManager;
    
    public void evictUserCache(Long userId) {
        Cache userCache = cacheManager.getCache("users");
        if (userCache != null) {
            userCache.evict(userId);
        }
    }
    
    public User getCachedUser(Long userId) {
        Cache userCache = cacheManager.getCache("users");
        if (userCache != null) {
            Cache.ValueWrapper wrapper = userCache.get(userId);
            if (wrapper != null) {
                return (User) wrapper.get();
            }
        }
        return null;
    }
}

// 自定義快取註解
@Target(ElementType.METHOD)
@Retention(RetentionPolicy.RUNTIME)
@Cacheable(value = "userOperations")
public @interface UserCache {
    String key() default "";
    int expireAfterWrite() default 3600; // 秒
}

@Component
public class CacheableUserService {
    
    @UserCache(key = "#userId", expireAfterWrite = 1800)
    public UserDto getUserWithOrders(Long userId) {
        // 複雜的用戶資料組裝
        return assembleUserData(userId);
    }
}

# 使用 Redis 的快取裝飾器
import redis
import json
import functools
from typing import Optional, Any

class CacheManager:
    def __init__(self, redis_url: str = "redis://localhost:6379/0"):
        self.redis_client = redis.from_url(redis_url)
        self.default_timeout = 3600  # 1 小時
    
    def cache_key(self, prefix: str, *args, **kwargs) -> str:
        """生成快取鍵"""
        key_parts = [prefix] + [str(arg) for arg in args]
        if kwargs:
            key_parts.extend([f"{k}:{v}" for k, v in sorted(kwargs.items())])
        return ":".join(key_parts)
    
    def get(self, key: str) -> Optional[Any]:
        """從快取獲取資料"""
        try:
            value = self.redis_client.get(key)
            return json.loads(value) if value else None
        except (redis.RedisError, json.JSONDecodeError):
            return None
    
    def set(self, key: str, value: Any, timeout: Optional[int] = None) -> bool:
        """設定快取"""
        try:
            timeout = timeout or self.default_timeout
            return self.redis_client.setex(
                key, 
                timeout, 
                json.dumps(value, default=str)
            )
        except (redis.RedisError, json.JSONEncodeError):
            return False
    
    def delete(self, key: str) -> bool:
        """刪除快取"""
        try:
            return bool(self.redis_client.delete(key))
        except redis.RedisError:
            return False
    
    def cache_decorator(self, prefix: str, timeout: Optional[int] = None):
        """快取裝飾器"""
        def decorator(func):
            @functools.wraps(func)
            def wrapper(*args, **kwargs):
                # 生成快取鍵
                cache_key = self.cache_key(
                    f"{prefix}:{func.__name__}", 
                    *args, 
                    **kwargs
                )
                
                # 嘗試從快取獲取
                cached_result = self.get(cache_key)
                if cached_result is not None:
                    return cached_result
                
                # 執行函數並快取結果
                result = func(*args, **kwargs)
                if result is not None:
                    self.set(cache_key, result, timeout)
                
                return result
            return wrapper
        return decorator

# 全域快取管理器
cache_manager = CacheManager()

class CachedUserService:
    def __init__(self):
        self.db = SessionLocal()
    
    @cache_manager.cache_decorator("user", timeout=1800)
    def get_user_by_id(self, user_id: int) -> Optional[dict]:
        """快取用戶查詢"""
        user = self.db.query(User).filter(User.id == user_id).first()
        return user.to_dict() if user else None
    
    @cache_manager.cache_decorator("user_stats", timeout=3600)
    def get_user_statistics(self, user_id: int) -> Optional[dict]:
        """快取用戶統計資料"""
        # 複雜的統計計算
        user = self.db.query(User).filter(User.id == user_id).first()
        if not user:
            return None
        
        stats = {
            'user_id': user_id,
            'order_count': len(user.orders),
            'total_spent': sum(order.total_amount for order in user.orders),
            'avg_order_value': 0,
            'last_order_date': None
        }
        
        if stats['order_count'] > 0:
            stats['avg_order_value'] = stats['total_spent'] / stats['order_count']
            stats['last_order_date'] = max(order.created_at for order in user.orders)
        
        return stats
    
    def update_user(self, user_id: int, **kwargs) -> Optional[dict]:
        """更新用戶並清除相關快取"""
        user = self.db.query(User).filter(User.id == user_id).first()
        if not user:
            return None
        
        # 更新用戶資料
        for key, value in kwargs.items():
            if hasattr(user, key):
                setattr(user, key, value)
        
        self.db.commit()
        self.db.refresh(user)
        
        # 清除相關快取
        self._evict_user_cache(user_id)
        
        return user.to_dict()
    
    def _evict_user_cache(self, user_id: int):
        """清除用戶相關快取"""
        cache_patterns = [
            f"user:get_user_by_id:{user_id}",
            f"user_stats:get_user_statistics:{user_id}"
        ]
        
        for pattern in cache_patterns:
            cache_manager.delete(pattern)

# 快取預熱策略
class CacheWarmupService:
    def __init__(self):
        self.user_service = CachedUserService()
    
    def warmup_active_users(self):
        """預熱活躍用戶快取"""
        # 查詢最近活躍的用戶
        active_users = (self.user_service.db.query(User.id)
                       .filter(User.status == UserStatus.ACTIVE)
                       .limit(100)
                       .all())
        
        for user in active_users:
            # 預載入用戶資料和統計
            self.user_service.get_user_by_id(user.id)
            self.user_service.get_user_statistics(user.id)
        
        print(f"預熱了 {len(active_users)} 個用戶的快取")
    
    def warmup_popular_data(self):
        """預熱熱門資料"""
        # 這裡可以根據業務需求預載入常用資料
        pass

// Java SQL 監控配置
@Component
public class SqlLoggingInterceptor implements Interceptor {
    
    private static final Logger logger = LoggerFactory.getLogger(SqlLoggingInterceptor.class);
    
    @Override
    public boolean onLoad(Object entity, Serializable id, Object[] state, String[] propertyNames, Type[] types) {
        logger.debug("Loading entity: {} with id: {}", entity.getClass().getSimpleName(), id);
        return false;
    }
    
    @Override
    public void afterTransactionCompletion(Transaction tx) {
        if (tx.wasCommitted()) {
            logger.debug("Transaction committed successfully");
        } else {
            logger.warn("Transaction was rolled back");
        }
    }
}

// 查詢效能監控
@Component
public class QueryPerformanceMonitor {
    
    private static final Logger perfLogger = LoggerFactory.getLogger("PERFORMANCE");
    
    @EventListener
    public void handleHibernateStatistics(ApplicationReadyEvent event) {
        SessionFactory sessionFactory = entityManagerFactory.unwrap(SessionFactory.class);
        Statistics stats = sessionFactory.getStatistics();
        stats.setStatisticsEnabled(true);
        
        // 定期記錄統計資訊
        scheduleStatisticsLogging(stats);
    }
    
    private void scheduleStatisticsLogging(Statistics stats) {
        ScheduledExecutorService executor = Executors.newScheduledThreadPool(1);
        
        executor.scheduleAtFixedRate(() -> {
            perfLogger.info("Hibernate Statistics: " +
                "Queries executed: {}, " +
                "Cache hit ratio: {}, " +
                "Average query time: {} ms",
                stats.getQueryExecutionCount(),
                stats.getQueryCacheHitCount() * 100.0 / Math.max(1, stats.getQueryExecutionCount()),
                stats.getQueryExecutionMaxTime());
            
            // 記錄慢查詢
            if (stats.getQueryExecutionMaxTime() > 1000) {
                perfLogger.warn("Slow query detected: {} ms", stats.getQueryExecutionMaxTime());
            }
            
        }, 0, 60, TimeUnit.SECONDS);
    }
}

# SQL 執行時間監控
import time
import logging
from sqlalchemy import event
from sqlalchemy.engine import Engine

# 設定效能日誌
perf_logger = logging.getLogger('performance')
perf_logger.setLevel(logging.INFO)

class QueryPerformanceMonitor:
    def __init__(self):
        self.query_stats = {}
        self.slow_query_threshold = 1.0  # 1 秒
    
    def setup_monitoring(self, engine):
        """設定 SQLAlchemy 引擎監控"""
        event.listen(engine, "before_cursor_execute", self.before_cursor_execute)
        event.listen(engine, "after_cursor_execute", self.after_cursor_execute)
    
    def before_cursor_execute(self, conn, cursor, statement, parameters, context, executemany):
        """查詢執行前"""
        context._query_start_time = time.time()
        context._query_statement = statement
    
    def after_cursor_execute(self, conn, cursor, statement, parameters, context, executemany):
        """查詢執行後"""
        if hasattr(context, '_query_start_time'):
            total = time.time() - context._query_start_time
            
            # 記錄查詢統計
            self._record_query_stats(statement, total)
            
            # 記錄慢查詢
            if total > self.slow_query_threshold:
                perf_logger.warning(
                    f"Slow query detected: {total:.3f}s\n"
                    f"SQL: {statement[:200]}..."
                )
            
            perf_logger.debug(f"Query executed in {total:.3f}s: {statement[:100]}...")
    
    def _record_query_stats(self, statement: str, execution_time: float):
        """記錄查詢統計"""
        # 簡化 SQL 語句用於統計
        query_type = statement.strip().split()[0].upper()
        
        if query_type not in self.query_stats:
            self.query_stats[query_type] = {
                'count': 0,
                'total_time': 0,
                'min_time': float('inf'),
                'max_time': 0
            }
        
        stats = self.query_stats[query_type]
        stats['count'] += 1
        stats['total_time'] += execution_time
        stats['min_time'] = min(stats['min_time'], execution_time)
        stats['max_time'] = max(stats['max_time'], execution_time)
    
    def get_performance_report(self) -> dict:
        """獲取效能報告"""
        report = {}
        
        for query_type, stats in self.query_stats.items():
            if stats['count'] > 0:
                report[query_type] = {
                    'count': stats['count'],
                    'total_time': round(stats['total_time'], 3),
                    'avg_time': round(stats['total_time'] / stats['count'], 3),
                    'min_time': round(stats['min_time'], 3),
                    'max_time': round(stats['max_time'], 3)
                }
        
        return report
    
    def reset_stats(self):
        """重置統計資料"""
        self.query_stats.clear()

# 使用監控器
monitor = QueryPerformanceMonitor()
monitor.setup_monitoring(engine)

# 查詢分析工具
class QueryAnalyzer:
    def __init__(self, session):
        self.session = session
    
    def analyze_query(self, query_func, *args, **kwargs):
        """分析查詢效能"""
        # 清除 session 快取確保從資料庫載入
        self.session.expunge_all()
        
        # 記錄開始時間
        start_time = time.time()
        
        # 執行查詢
        result = query_func(*args, **kwargs)
        
        # 強制載入所有資料
        if hasattr(result, '__iter__'):
            list(result)  # 觸發載入
        
        # 計算執行時間
        execution_time = time.time() - start_time
        
        return {
            'result': result,
            'execution_time': execution_time,
            'query_count': self._get_query_count()
        }
    
    def _get_query_count(self):
        """獲取 session 中的查詢次數（簡化實作）"""
        # 實際實作需要使用 SQLAlchemy 的統計功能
        return "N/A"
    
    def compare_strategies(self, strategies: dict):
        """比較不同查詢策略的效能"""
        results = {}
        
        for name, strategy in strategies.items():
            print(f"測試策略: {name}")
            
            # 重置 session
            self.session.rollback()
            
            # 分析效能
            analysis = self.analyze_query(strategy)
            results[name] = analysis
            
            print(f"  執行時間: {analysis['execution_time']:.3f}s")
            print(f"  查詢次數: {analysis['query_count']}")
        
        return results

# 使用範例
def performance_test_example():
    analyzer = QueryAnalyzer(session)
    
    strategies = {
        'lazy_loading': lambda: session.query(User).all(),
        'eager_loading': lambda: session.query(User).options(selectinload(User.orders)).all(),
        'joined_loading': lambda: session.query(User).options(joinedload(User.orders)).all()
    }
    
    results = analyzer.compare_strategies(strategies)
    
    # 輸出比較結果
    best_strategy = min(results.items(), key=lambda x: x[1]['execution_time'])
    print(f"\n最佳策略: {best_strategy[0]} ({best_strategy[1]['execution_time']:.3f}s)")

// ✅ 好的命名範例
@Entity
@Table(name = "users")  // 表格名稱使用複數、snake_case
public class User {     // 類別名稱使用單數、PascalCase
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    
    @Column(name = "first_name")  // 欄位使用 snake_case
    private String firstName;     // 屬性使用 camelCase
    
    @Column(name = "email_address")
    private String emailAddress;
    
    @Column(name = "created_at")
    private LocalDateTime createdAt;
}

// ❌ 避免的命名方式
@Entity
@Table(name = "User")  // 避免：表格名稱不應使用大寫
public class user {    // 避免：類別名稱應該大寫開頭
    
    @Column(name = "FirstName")  // 避免：欄位名稱不應使用 PascalCase
    private String first_name;   // 避免：Java 屬性不應使用 snake_case
}

// Repository 命名規則
public interface UserRepository extends JpaRepository<User, Long> {
    // 查詢方法命名要清晰表達意圖
    Optional<User> findByEmail(String email);
    List<User> findByStatusAndCreatedAtAfter(UserStatus status, LocalDateTime date);
    
    // 使用 @Query 時保持方法名稱與查詢意圖一致
    @Query("SELECT u FROM User u WHERE u.email LIKE %:domain%")
    List<User> findUsersByEmailDomain(@Param("domain") String domain);
}

// Service 命名規則
@Service
public class UserService {
    
    // 業務方法使用動詞開頭
    public User createUser(CreateUserRequest request);
    public User updateUserProfile(Long userId, UpdateProfileRequest request);
    public void deactivateUser(Long userId);
    
    // 查詢方法可以使用 find/get 開頭
    public UserDto findUserById(Long id);
    public List<UserDto> getActiveUsers();
    public Page<UserDto> searchUsers(UserSearchCriteria criteria, Pageable pageable);
}

// 自定義異常類別
public class UserNotFoundException extends RuntimeException {
    private final Long userId;
    
    public UserNotFoundException(Long userId) {
        super(String.format("User with id %d not found", userId));
        this.userId = userId;
    }
    
    public Long getUserId() {
        return userId;
    }
}

public class UserValidationException extends RuntimeException {
    private final List<String> validationErrors;
    
    public UserValidationException(List<String> errors) {
        super("User validation failed: " + String.join(", ", errors));
        this.validationErrors = errors;
    }
    
    public List<String> getValidationErrors() {
        return validationErrors;
    }
}

// Service 層錯誤處理
@Service
@Transactional
public class UserService {
    
    private static final Logger logger = LoggerFactory.getLogger(UserService.class);
    
    public User createUser(CreateUserRequest request) {
        try {
            // 驗證請求
            validateCreateUserRequest(request);
            
            // 檢查用戶是否已存在
            if (userRepository.findByEmail(request.getEmail()).isPresent()) {
                throw new UserValidationException(
                    Arrays.asList("Email already exists: " + request.getEmail())
                );
            }
            
            User user = new User();
            user.setUsername(request.getUsername());
            user.setEmail(request.getEmail());
            
            User savedUser = userRepository.save(user);
            logger.info("User created successfully with id: {}", savedUser.getId());
            
            return savedUser;
            
        } catch (DataIntegrityViolationException e) {
            logger.error("Data integrity violation while creating user", e);
            throw new UserValidationException(
                Arrays.asList("User data violates database constraints")
            );
        } catch (Exception e) {
            logger.error("Unexpected error while creating user", e);
            throw new RuntimeException("Failed to create user", e);
        }
    }
    
    public User findUserById(Long id) {
        return userRepository.findById(id)
            .orElseThrow(() -> new UserNotFoundException(id));
    }
    
    private void validateCreateUserRequest(CreateUserRequest request) {
        List<String> errors = new ArrayList<>();
        
        if (StringUtils.isBlank(request.getUsername())) {
            errors.add("Username is required");
        }
        
        if (StringUtils.isBlank(request.getEmail())) {
            errors.add("Email is required");
        } else if (!EmailValidator.getInstance().isValid(request.getEmail())) {
            errors.add("Invalid email format");
        }
        
        if (!errors.isEmpty()) {
            throw new UserValidationException(errors);
        }
    }
}

// 全域異常處理器
@ControllerAdvice
public class GlobalExceptionHandler {
    
    private static final Logger logger = LoggerFactory.getLogger(GlobalExceptionHandler.class);
    
    @ExceptionHandler(UserNotFoundException.class)
    public ResponseEntity<ErrorResponse> handleUserNotFound(UserNotFoundException e) {
        logger.warn("User not found: {}", e.getMessage());
        
        ErrorResponse error = new ErrorResponse(
            "USER_NOT_FOUND",
            e.getMessage(),
            LocalDateTime.now()
        );
        
        return ResponseEntity.status(HttpStatus.NOT_FOUND).body(error);
    }
    
    @ExceptionHandler(UserValidationException.class)
    public ResponseEntity<ErrorResponse> handleUserValidation(UserValidationException e) {
        logger.warn("User validation failed: {}", e.getMessage());
        
        ErrorResponse error = new ErrorResponse(
            "VALIDATION_ERROR",
            e.getMessage(),
            LocalDateTime.now()
        );
        error.setDetails(e.getValidationErrors());
        
        return ResponseEntity.status(HttpStatus.BAD_REQUEST).body(error);
    }
    
    @ExceptionHandler(DataIntegrityViolationException.class)
    public ResponseEntity<ErrorResponse> handleDataIntegrityViolation(DataIntegrityViolationException e) {
        logger.error("Data integrity violation", e);
        
        ErrorResponse error = new ErrorResponse(
            "DATA_INTEGRITY_ERROR",
            "Data violates database constraints",
            LocalDateTime.now()
        );
        
        return ResponseEntity.status(HttpStatus.CONFLICT).body(error);
    }
}

# 自定義異常類別
class UserServiceException(Exception):
    """用戶服務基礎異常"""
    pass

class UserNotFoundException(UserServiceException):
    """用戶不存在異常"""
    def __init__(self, user_id: int):
        self.user_id = user_id
        super().__init__(f"User with id {user_id} not found")

class UserValidationException(UserServiceException):
    """用戶驗證異常"""
    def __init__(self, errors: list):
        self.errors = errors
        super().__init__(f"User validation failed: {', '.join(errors)}")

class DatabaseOperationException(UserServiceException):
    """資料庫操作異常"""
    pass

# Service 層錯誤處理
import logging
from typing import Optional, Dict, Any
from sqlalchemy.exc import IntegrityError, SQLAlchemyError

logger = logging.getLogger(__name__)

class UserService:
    def __init__(self):
        self.db = SessionLocal()
    
    def create_user(self, user_data: dict) -> dict:
        """建立用戶並處理各種異常"""
        try:
            # 驗證資料
            self._validate_user_data(user_data)
            
            # 檢查用戶是否已存在
            existing_user = (self.db.query(User)
                           .filter(User.email == user_data['email'])
                           .first())
            
            if existing_user:
                raise UserValidationException(['Email already exists'])
            
            # 建立用戶
            user = User(**user_data)
            self.db.add(user)
            self.db.commit()
            self.db.refresh(user)
            
            logger.info(f"User created successfully with id: {user.id}")
            return user.to_dict()
            
        except UserValidationException:
            self.db.rollback()
            raise  # 重新拋出驗證異常
            
        except IntegrityError as e:
            self.db.rollback()
            logger.error(f"Database integrity error: {str(e)}")
            raise DatabaseOperationException("User data violates database constraints")
            
        except SQLAlchemyError as e:
            self.db.rollback()
            logger.error(f"Database error while creating user: {str(e)}")
            raise DatabaseOperationException("Database operation failed")
            
        except Exception as e:
            self.db.rollback()
            logger.error(f"Unexpected error while creating user: {str(e)}")
            raise UserServiceException("Failed to create user")
    
    def get_user_by_id(self, user_id: int) -> dict:
        """根據 ID 獲取用戶"""
        try:
            user = self.db.query(User).filter(User.id == user_id).first()
            if not user:
                raise UserNotFoundException(user_id)
            
            return user.to_dict()
            
        except UserNotFoundException:
            raise  # 重新拋出用戶不存在異常
            
        except SQLAlchemyError as e:
            logger.error(f"Database error while fetching user {user_id}: {str(e)}")
            raise DatabaseOperationException("Failed to fetch user")
    
    def _validate_user_data(self, user_data: dict):
        """驗證用戶資料"""
        errors = []
        
        if not user_data.get('username'):
            errors.append('Username is required')
        
        email = user_data.get('email')
        if not email:
            errors.append('Email is required')
        elif not self._is_valid_email(email):
            errors.append('Invalid email format')
        
        if errors:
            raise UserValidationException(errors)
    
    def _is_valid_email(self, email: str) -> bool:
        """簡單的郵箱驗證"""
        import re
        pattern = r'^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$'
        return re.match(pattern, email) is not None
    
    def __del__(self):
        if hasattr(self, 'db'):
            self.db.close()

# Flask 錯誤處理（如果使用 Flask）
from flask import Flask, jsonify, request

app = Flask(__name__)

@app.errorhandler(UserNotFoundException)
def handle_user_not_found(e):
    logger.warning(f"User not found: {str(e)}")
    return jsonify({
        'error': 'USER_NOT_FOUND',
        'message': str(e),
        'user_id': e.user_id
    }), 404

@app.errorhandler(UserValidationException)
def handle_user_validation(e):
    logger.warning(f"User validation failed: {str(e)}")
    return jsonify({
        'error': 'VALIDATION_ERROR',
        'message': str(e),
        'details': e.errors
    }), 400

@app.errorhandler(DatabaseOperationException)
def handle_database_error(e):
    logger.error(f"Database operation failed: {str(e)}")
    return jsonify({
        'error': 'DATABASE_ERROR',
        'message': str(e)
    }), 500

@app.errorhandler(Exception)
def handle_unexpected_error(e):
    logger.error(f"Unexpected error: {str(e)}")
    return jsonify({
        'error': 'INTERNAL_ERROR',
        'message': 'An unexpected error occurred'
    }), 500

// 單元測試
@ExtendWith(MockitoExtension.class)
class UserServiceTest {
    
    @Mock
    private UserRepository userRepository;
    
    @InjectMocks
    private UserService userService;
    
    @Test
    void createUser_WithValidData_ShouldReturnUser() {
        // Given
        CreateUserRequest request = new CreateUserRequest("john_doe", "john@example.com");
        User savedUser = new User();
        savedUser.setId(1L);
        savedUser.setUsername("john_doe");
        savedUser.setEmail("john@example.com");
        
        when(userRepository.findByEmail(request.getEmail())).thenReturn(Optional.empty());
        when(userRepository.save(any(User.class))).thenReturn(savedUser);
        
        // When
        User result = userService.createUser(request);
        
        // Then
        assertThat(result).isNotNull();
        assertThat(result.getId()).isEqualTo(1L);
        assertThat(result.getUsername()).isEqualTo("john_doe");
        verify(userRepository).findByEmail(request.getEmail());
        verify(userRepository).save(any(User.class));
    }
    
    @Test
    void createUser_WithExistingEmail_ShouldThrowException() {
        // Given
        CreateUserRequest request = new CreateUserRequest("john_doe", "john@example.com");
        User existingUser = new User();
        
        when(userRepository.findByEmail(request.getEmail())).thenReturn(Optional.of(existingUser));
        
        // When & Then
        assertThrows(UserValidationException.class, () -> userService.createUser(request));
        verify(userRepository, never()).save(any(User.class));
    }
}

// 整合測試
@SpringBootTest
@Transactional
@TestPropertySource(properties = {
    "spring.datasource.url=jdbc:h2:mem:testdb",
    "spring.jpa.hibernate.ddl-auto=create-drop"
})
class UserServiceIntegrationTest {
    
    @Autowired
    private UserService userService;
    
    @Autowired
    private UserRepository userRepository;
    
    @Test
    void createUser_IntegrationTest() {
        // Given
        CreateUserRequest request = new CreateUserRequest("integration_user", "integration@example.com");
        
        // When
        User createdUser = userService.createUser(request);
        
        // Then
        assertThat(createdUser.getId()).isNotNull();
        
        Optional<User> foundUser = userRepository.findById(createdUser.getId());
        assertThat(foundUser).isPresent();
        assertThat(foundUser.get().getUsername()).isEqualTo("integration_user");
    }
    
    @Test
    void findUserById_WithNonExistentId_ShouldThrowException() {
        // When & Then
        assertThrows(UserNotFoundException.class, () -> userService.findUserById(999L));
    }
}

// Repository 測試
@DataJpaTest
class UserRepositoryTest {
    
    @Autowired
    private TestEntityManager entityManager;
    
    @Autowired
    private UserRepository userRepository;
    
    @Test
    void findByEmail_WithExistingEmail_ShouldReturnUser() {
        // Given
        User user = new User();
        user.setUsername("test_user");
        user.setEmail("test@example.com");
        entityManager.persistAndFlush(user);
        
        // When
        Optional<User> found = userRepository.findByEmail("test@example.com");
        
        // Then
        assertThat(found).isPresent();
        assertThat(found.get().getUsername()).isEqualTo("test_user");
    }
    
    @Test
    void findByEmail_WithNonExistentEmail_ShouldReturnEmpty() {
        // When
        Optional<User> found = userRepository.findByEmail("nonexistent@example.com");
        
        // Then
        assertThat(found).isEmpty();
    }
}

# 測試配置
import pytest
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from models import Base, User, UserStatus

# 測試資料庫設定
TEST_DATABASE_URL = "sqlite:///:memory:"

@pytest.fixture(scope="function")
def test_db():
    """測試資料庫 fixture"""
    engine = create_engine(TEST_DATABASE_URL)
    Base.metadata.create_all(engine)
    
    TestSessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
    session = TestSessionLocal()
    
    yield session
    
    session.close()
    Base.metadata.drop_all(engine)

@pytest.fixture
def sample_user():
    """範例用戶資料"""
    return {
        'username': 'test_user',
        'email': 'test@example.com',
        'full_name': 'Test User'
    }

# 單元測試
class TestUserService:
    
    def test_create_user_success(self, test_db, sample_user):
        """測試成功建立用戶"""
        # Arrange
        service = UserService()
        service.db = test_db
        
        # Act
        result = service.create_user(**sample_user)
        
        # Assert
        assert result['username'] == sample_user['username']
        assert result['email'] == sample_user['email']
        assert result['id'] is not None
        
        # 驗證資料庫中確實建立了用戶
        user_in_db = test_db.query(User).filter(User.email == sample_user['email']).first()
        assert user_in_db is not None
        assert user_in_db.username == sample_user['username']
    
    def test_create_user_duplicate_email(self, test_db, sample_user):
        """測試重複郵箱建立用戶應該失敗"""
        # Arrange
        service = UserService()
        service.db = test_db
        
        # 先建立一個用戶
        service.create_user(**sample_user)
        
        # Act & Assert
        with pytest.raises(ValueError, match="郵箱已存在"):
            service.create_user(**sample_user)
    
    def test_get_user_by_id_success(self, test_db, sample_user):
        """測試成功獲取用戶"""
        # Arrange
        service = UserService()
        service.db = test_db
        
        created_user = service.create_user(**sample_user)
        user_id = created_user['id']
        
        # Act
        result = service.get_user_by_id(user_id)
        
        # Assert
        assert result['id'] == user_id
        assert result['username'] == sample_user['username']
    
    def test_get_user_by_id_not_found(self, test_db):
        """測試獲取不存在的用戶"""
        # Arrange
        service = UserService()
        service.db = test_db
        
        # Act & Assert
        with pytest.raises(ValueError, match="用戶不存在: 999"):
            service.get_user_by_id(999)
    
    def test_update_user_success(self, test_db, sample_user):
        """測試成功更新用戶"""
        # Arrange
        service = UserService()
        service.db = test_db
        
        created_user = service.create_user(**sample_user)
        user_id = created_user['id']
        
        # Act
        updated_data = {
            'email': 'updated@example.com',
            'full_name': 'Updated Name'
        }
        result = service.update_user(user_id, **updated_data)
        
        # Assert
        assert result['email'] == updated_data['email']
        assert result['full_name'] == updated_data['full_name']
        assert result['username'] == sample_user['username']  # 未更新的欄位保持不變

# 整合測試
class TestUserServiceIntegration:
    
    def test_user_lifecycle(self, test_db):
        """測試用戶完整生命週期"""
        service = UserService()
        service.db = test_db
        
        # 建立用戶
        user_data = {
            'username': 'lifecycle_user',
            'email': 'lifecycle@example.com',
            'full_name': 'Lifecycle User'
        }
        
        created_user = service.create_user(**user_data)
        user_id = created_user['id']
        
        # 驗證建立
        assert created_user['username'] == user_data['username']
        
        # 更新用戶
        updated_user = service.update_user(user_id, full_name='Updated Lifecycle User')
        assert updated_user['full_name'] == 'Updated Lifecycle User'
        
        # 刪除用戶
        success = service.delete_user(user_id)
        assert success is True
        
        # 驗證刪除
        with pytest.raises(ValueError):
            service.get_user_by_id(user_id)

# 效能測試
class TestUserServicePerformance:
    
    def test_batch_create_performance(self, test_db):
        """測試批次建立用戶的效能"""
        import time
        
        service = UserService()
        service.db = test_db
        
        # 準備測試資料
        users_data = [
            {
                'username': f'user_{i}',
                'email': f'user_{i}@example.com',
                'full_name': f'User {i}'
            }
            for i in range(100)
        ]
        
        # 測試批次建立
        start_time = time.time()
        
        for user_data in users_data:
            service.create_user(**user_data)
        
        end_time = time.time()
        execution_time = end_time - start_time
        
        # 驗證結果
        user_count = test_db.query(User).count()
        assert user_count == 100
        
        # 效能斷言（根據實際情況調整）
        assert execution_time < 5.0  # 應該在 5 秒內完成
        
        print(f"批次建立 100 個用戶耗時: {execution_time:.2f} 秒")

# 執行測試的指令範例
if __name__ == "__main__":
    # pytest test_user_service.py -v
    # pytest test_user_service.py::TestUserService::test_create_user_success -v
    # pytest test_user_service.py --cov=user_service --cov-report=html
    pass

// ❌ 常見錯誤：在 Session 關閉後存取延遲載入的屬性
@Service
public class UserService {
    
    @Transactional
    public User findUser(Long id) {
        return userRepository.findById(id).orElse(null);
    }
    
    // 問題：在方法外存取 orders 會拋出 LazyInitializationException
    public void processUserOrders(Long userId) {
        User user = findUser(userId); // Transaction 結束，Session 關閉
        List<Order> orders = user.getOrders(); // ❌ LazyInitializationException
    }
}

// ✅ 解決方案 1: 在 Transaction 內完成所有操作
@Service
public class UserService {
    
    @Transactional
    public void processUserOrders(Long userId) {
        User user = userRepository.findById(userId).orElse(null);
        if (user != null) {
            List<Order> orders = user.getOrders(); // ✅ 在 Transaction 內存取
            // 處理訂單邏輯
        }
    }
}

// ✅ 解決方案 2: 使用 EAGER 載入
@Entity
public class User {
    @OneToMany(mappedBy = "user", fetch = FetchType.EAGER)
    private List<Order> orders;
}

// ✅ 解決方案 3: 使用 JOIN FETCH
@Repository
public interface UserRepository extends JpaRepository<User, Long> {
    
    @Query("SELECT u FROM User u LEFT JOIN FETCH u.orders WHERE u.id = :id")
    Optional<User> findByIdWithOrders(@Param("id") Long id);
}

// ✅ 解決方案 4: 使用 EntityGraph
@Repository
public interface UserRepository extends JpaRepository<User, Long> {
    
    @EntityGraph(attributePaths = {"orders"})
    Optional<User> findById(Long id);
}

// ✅ 解決方案 5: 使用 DTO 模式
@Service
public class UserService {
    
    @Transactional(readOnly = true)
    public UserWithOrdersDto findUserWithOrders(Long userId) {
        User user = userRepository.findById(userId).orElse(null);
        if (user == null) {
            return null;
        }
        
        // 在 Transaction 內轉換為 DTO
        return new UserWithOrdersDto(
            user.getId(),
            user.getUsername(),
            user.getOrders().stream()
                .map(this::convertToOrderDto)
                .collect(Collectors.toList())
        );
    }
}

# ❌ 常見錯誤：Session 關閉後存取關聯資料
def get_user_orders_bad(user_id):
    session = SessionLocal()
    try:
        user = session.query(User).filter(User.id == user_id).first()
        return user
    finally:
        session.close()

# 在外部存取會出錯
user = get_user_orders_bad(1)
orders = user.orders  # ❌ DetachedInstanceError

# ✅ 解決方案 1: 在 Session 內完成操作
def get_user_orders_good(user_id):
    session = SessionLocal()
    try:
        user = session.query(User).filter(User.id == user_id).first()
        if user:
            # 強制載入關聯資料
            orders = user.orders
            return {
                'user_id': user.id,
                'username': user.username,
                'orders': [order.to_dict() for order in orders]
            }
        return None
    finally:
        session.close()

# ✅ 解決方案 2: 使用 eager loading
def get_user_with_orders_eager(user_id):
    session = SessionLocal()
    try:
        user = (session.query(User)
                .options(selectinload(User.orders))
                .filter(User.id == user_id)
                .first())
        return user
    finally:
        session.close()

// 啟用 SQL 日誌來識別 N+1 問題
# application.yml
logging:
  level:
    org.hibernate.SQL: DEBUG
    org.hibernate.type.descriptor.sql.BasicBinder: TRACE

spring:
  jpa:
    show-sql: true
    properties:
      hibernate:
        format_sql: true
        generate_statistics: true

# Python 中識別 N+1 問題
import logging

# 啟用 SQL 日誌
logging.basicConfig()
logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO)

# 使用查詢計數器
class QueryCounter:
    def __init__(self):
        self.query_count = 0
    
    def count_queries(self, conn, cursor, statement, parameters, context, executemany):
        self.query_count += 1
        print(f"Query #{self.query_count}: {statement[:100]}...")

# 監控查詢次數
from sqlalchemy import event
counter = QueryCounter()
event.listen(engine, "before_cursor_execute", counter.count_queries)

// Java 連線池最佳化
spring:
  datasource:
    hikari:
      maximum-pool-size: 20
      minimum-idle: 5
      idle-timeout: 300000
      connection-timeout: 20000
      leak-detection-threshold: 60000
      max-lifetime: 1800000

# Python 連線池設定
from sqlalchemy import create_engine

engine = create_engine(
    DATABASE_URL,
    pool_size=10,          # 基本連線數
    max_overflow=20,       # 最大溢出連線
    pool_pre_ping=True,    # 連線檢查
    pool_recycle=3600,     # 連線回收時間（秒）
    echo=False             # 生產環境關閉 SQL 日誌
)

// Java 慢查詢監控
@Component
public class SlowQueryLogger {
    
    private static final Logger logger = LoggerFactory.getLogger("SLOW_QUERY");
    
    @EventListener
    public void handleSlowQuery(StatementInspector.SlowQueryEvent event) {
        if (event.getExecutionTime() > 1000) { // 超過 1 秒
            logger.warn("Slow query detected: {} ms\nSQL: {}", 
                       event.getExecutionTime(), 
                       event.getSql());
        }
    }
}

# Python 慢查詢監控
import time
from sqlalchemy import event

class SlowQueryLogger:
    def __init__(self, threshold_ms=1000):
        self.threshold_ms = threshold_ms
    
    def before_cursor_execute(self, conn, cursor, statement, parameters, context, executemany):
        context._query_start_time = time.time()
    
    def after_cursor_execute(self, conn, cursor, statement, parameters, context, executemany):
        if hasattr(context, '_query_start_time'):
            total = time.time() - context._query_start_time
            if total * 1000 > self.threshold_ms:
                print(f"SLOW QUERY: {total*1000:.2f}ms\n{statement[:200]}...")

# 註冊監聽器
slow_query_logger = SlowQueryLogger()
event.listen(engine, "before_cursor_execute", slow_query_logger.before_cursor_execute)
event.listen(engine, "after_cursor_execute", slow_query_logger.after_cursor_execute)

// Java 交易隔離等級設定
@Transactional(isolation = Isolation.READ_COMMITTED)
public void updateUserBalance(Long userId, BigDecimal amount) {
    // 防止髒讀，但允許不可重複讀
}

@Transactional(isolation = Isolation.REPEATABLE_READ)
public void generateReport(Long userId) {
    // 防止髒讀和不可重複讀
}

# Python 交易隔離等級
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

# 設定隔離等級
engine = create_engine(DATABASE_URL, isolation_level="READ_COMMITTED")

# 或在 session 層級設定
def with_isolation_level(isolation_level):
    connection = engine.connect()
    trans = connection.begin()
    try:
        connection.execute(f"SET TRANSACTION ISOLATION LEVEL {isolation_level}")
        session = sessionmaker(bind=connection)()
        yield session
        trans.commit()
    except:
        trans.rollback()
        raise
    finally:
        connection.close()

// ❌ 錯誤：沒有正確關閉 EntityManager
@Service
public class BadUserService {
    
    @PersistenceContext
    private EntityManager entityManager;
    
    public void processLargeDataset() {
        for (int i = 0; i < 10000; i++) {
            User user = new User();
            entityManager.persist(user);
            // ❌ 沒有 flush 和 clear，導致一級快取堆積
        }
    }
}

// ✅ 正確：定期清理一級快取
@Service
public class GoodUserService {
    
    @PersistenceContext
    private EntityManager entityManager;
    
    @Transactional
    public void processLargeDataset() {
        int batchSize = 50;
        
        for (int i = 0; i < 10000; i++) {
            User user = new User();
            entityManager.persist(user);
            
            if (i % batchSize == 0) {
                entityManager.flush();  // 強制執行 SQL
                entityManager.clear();  // 清除一級快取
            }
        }
    }
}

// 題目 1: 以下程式碼會輸出什麼？
@Entity
public class Product {
    @Id
    @GeneratedValue
    private Long id;
    private String name;
    // getter/setter...
}

@Service
public class ProductService {
    @PersistenceContext
    private EntityManager em;
    
    @Transactional
    public void testEntityStates() {
        Product p1 = new Product();  // 狀態: ?
        p1.setName("Laptop");
        
        em.persist(p1);             // 狀態: ?
        System.out.println("ID after persist: " + p1.getId());
        
        em.flush();                 // 狀態: ?
        System.out.println("ID after flush: " + p1.getId());
        
        em.detach(p1);             // 狀態: ?
        p1.setName("Desktop");
        
        em.merge(p1);              // 狀態: ?
    }
}

// 題目 2: 修正以下實體類別的對映錯誤
@Entity
@Table(name = "employees")
public class Employee {
    @Id
    private String id;  // 錯誤 1: 缺少生成策略
    
    @Column(name = "emp_name")
    private String employeeName;
    
    private Date birthDate;  // 錯誤 2: 缺少時間精度
    
    @Enumerated
    private Status status;   // 錯誤 3: 缺少列舉策略
    
    private byte[] photo;    // 錯誤 4: 大型物件未標註
}

enum Status {
    ACTIVE, INACTIVE, SUSPENDED
}

@Entity
@Table(name = "employees")
public class Employee {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)  // 修正 1
    private Long id;  // 改為 Long 類型
    
    @Column(name = "emp_name", nullable = false, length = 100)
    private String employeeName;
    
    @Temporal(TemporalType.DATE)  // 修正 2
    private Date birthDate;
    
    @Enumerated(EnumType.STRING)  // 修正 3
    private Status status;
    
    @Lob  // 修正 4
    private byte[] photo;
}

// 題目 3: 設計一個部落格系統的實體關聯
// 需求：
// - 用戶可以寫多篇文章
// - 文章可以有多個標籤
// - 文章可以有多個評論
// - 評論屬於特定文章和用戶

// 請完成以下實體類別：

@Entity
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String username;
    
    // TODO: 添加與 Post 的關聯
    // TODO: 添加與 Comment 的關聯
}

@Entity
public class Post {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String title;
    private String content;
    
    // TODO: 添加與 User 的關聯
    // TODO: 添加與 Tag 的關聯
    // TODO: 添加與 Comment 的關聯
}

@Entity
public class Tag {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String name;
    
    // TODO: 添加與 Post 的關聯
}

@Entity
public class Comment {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String content;
    
    // TODO: 添加與 User 和 Post 的關聯
}

@Entity
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String username;
    
    @OneToMany(mappedBy = "author", cascade = CascadeType.ALL, fetch = FetchType.LAZY)
    private List<Post> posts = new ArrayList<>();
    
    @OneToMany(mappedBy = "user", cascade = CascadeType.ALL, fetch = FetchType.LAZY)
    private List<Comment> comments = new ArrayList<>();
}

@Entity
public class Post {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String title;
    private String content;
    
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "author_id", nullable = false)
    private User author;
    
    @ManyToMany(fetch = FetchType.LAZY)
    @JoinTable(
        name = "post_tags",
        joinColumns = @JoinColumn(name = "post_id"),
        inverseJoinColumns = @JoinColumn(name = "tag_id")
    )
    private Set<Tag> tags = new HashSet<>();
    
    @OneToMany(mappedBy = "post", cascade = CascadeType.ALL, orphanRemoval = true)
    private List<Comment> comments = new ArrayList<>();
}

@Entity
public class Tag {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String name;
    
    @ManyToMany(mappedBy = "tags", fetch = FetchType.LAZY)
    private Set<Post> posts = new HashSet<>();
}

@Entity
public class Comment {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String content;
    
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "user_id", nullable = false)
    private User user;
    
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "post_id", nullable = false)
    private Post post;
}

// 題目 4: 寫出 JPQL 查詢來滿足以下需求

@Repository
public interface UserRepository extends JpaRepository<User, Long> {
    
    // 1. 查找用戶名包含特定字串的用戶
    @Query("SELECT u FROM User u WHERE ?")
    List<User> findByUsernameContaining(String keyword);
    
    // 2. 查找發佈文章數量超過指定數量的用戶
    @Query("SELECT u FROM User u WHERE ?")
    List<User> findUsersWithPostCountGreaterThan(int count);
    
    // 3. 查找最近 30 天內活躍的用戶（有發佈文章或評論）
    @Query("SELECT DISTINCT u FROM User u WHERE ?")
    List<User> findActiveUsersInLast30Days();
    
    // 4. 統計每個用戶的文章和評論總數
    @Query("SELECT u.username, COUNT(p), COUNT(c) FROM User u " +
           "LEFT JOIN u.posts p LEFT JOIN u.comments c " +
           "GROUP BY u.id, u.username")
    List<Object[]> getUserActivityStats();
}

@Repository
public interface UserRepository extends JpaRepository<User, Long> {
    
    // 1. 查找用戶名包含特定字串的用戶
    @Query("SELECT u FROM User u WHERE u.username LIKE %:keyword%")
    List<User> findByUsernameContaining(@Param("keyword") String keyword);
    
    // 2. 查找發佈文章數量超過指定數量的用戶
    @Query("SELECT u FROM User u WHERE SIZE(u.posts) > :count")
    List<User> findUsersWithPostCountGreaterThan(@Param("count") int count);
    
    // 3. 查找最近 30 天內活躍的用戶
    @Query("SELECT DISTINCT u FROM User u WHERE " +
           "EXISTS (SELECT p FROM Post p WHERE p.author = u AND p.createdAt >= :date) OR " +
           "EXISTS (SELECT c FROM Comment c WHERE c.user = u AND c.createdAt >= :date)")
    List<User> findActiveUsersInLast30Days(@Param("date") LocalDateTime date);
    
    // 4. 統計每個用戶的文章和評論總數
    @Query("SELECT new com.example.UserActivityDto(u.username, COUNT(DISTINCT p), COUNT(DISTINCT c)) " +
           "FROM User u LEFT JOIN u.posts p LEFT JOIN u.comments c " +
           "GROUP BY u.id, u.username")
    List<UserActivityDto> getUserActivityStats();
}

# 題目 5: 以下程式碼的輸出是什麼？

from sqlalchemy.orm import sessionmaker

Session = sessionmaker(bind=engine)
session = Session()

# 創建新用戶
user = User(username='test_user', email='test@example.com')
print(f"1. user in session: {user in session}")

session.add(user)
print(f"2. user in session: {user in session}")
print(f"3. user.id: {user.id}")

session.flush()
print(f"4. user.id: {user.id}")

session.commit()
print(f"5. user.id: {user.id}")

session.expunge(user)
print(f"6. user in session: {user in session}")

# 題目 6: 修正以下 SQLAlchemy 模型的錯誤

class Author(Base):
    __tablename__ = 'authors'
    
    id = Column(Integer, primary_key=True)
    name = Column(String(100))
    
    # 錯誤：缺少正確的關聯設定
    books = relationship("Book")

class Book(Base):
    __tablename__ = 'books'
    
    id = Column(Integer, primary_key=True)
    title = Column(String(200))
    
    # 錯誤：缺少外鍵
    author = relationship("Author")

class Category(Base):
    __tablename__ = 'categories'
    
    id = Column(Integer, primary_key=True)
    name = Column(String(50))
    
    # 錯誤：多對多關聯缺少聯結表
    books = relationship("Book")

# 聯結表定義
book_categories = Table(
    'book_categories', Base.metadata,
    Column('book_id', Integer, ForeignKey('books.id'), primary_key=True),
    Column('category_id', Integer, ForeignKey('categories.id'), primary_key=True)
)

class Author(Base):
    __tablename__ = 'authors'
    
    id = Column(Integer, primary_key=True)
    name = Column(String(100))
    
    # 修正：添加 back_populates
    books = relationship("Book", back_populates="author")

class Book(Base):
    __tablename__ = 'books'
    
    id = Column(Integer, primary_key=True)
    title = Column(String(200))
    
    # 修正：添加外鍵和 back_populates
    author_id = Column(Integer, ForeignKey('authors.id'))
    author = relationship("Author", back_populates="books")
    
    # 修正：多對多關聯
    categories = relationship("Category", secondary=book_categories, back_populates="books")

class Category(Base):
    __tablename__ = 'categories'
    
    id = Column(Integer, primary_key=True)
    name = Column(String(50))
    
    # 修正：多對多關聯
    books = relationship("Book", secondary=book_categories, back_populates="categories")

/**
 * 綜合題：設計一個線上學習平台的資料模型
 * 
 * 需求：
 * 1. 用戶可以註冊多個課程
 * 2. 課程包含多個章節
 * 3. 用戶可以完成章節並記錄進度
 * 4. 課程有分類
 * 5. 用戶可以對課程評分和評論
 * 
 * 請設計完整的實體模型，並回答以下問題：
 * a) 如何避免 N+1 查詢問題？
 * b) 如何實作軟刪除？
 * c) 如何確保資料一致性？
 */

// 請完成實體設計...

"""
綜合題：實作一個電商系統的訂單管理

需求：
1. 設計 User、Product、Order、OrderItem 實體
2. 實作購物車功能
3. 實作庫存管理
4. 確保訂單建立的原子性
5. 實作查詢最佳化

請實作以下功能：
a) 添加商品到購物車
b) 建立訂單（扣除庫存）
c) 查詢用戶訂單歷史
d) 統計商品銷售數據
"""

# 請完成實作...

// 問題：Session 已關閉後存取延遲載入屬性
// 解決方案1：在 @Transactional 方法內完成所有操作
@Transactional
public void processUser(Long userId) {
    User user = userRepository.findById(userId);
    user.getOrders().size(); // 在交易內觸發載入
}

// 解決方案2：使用 EntityGraph
@EntityGraph(attributePaths = {"orders"})
Optional<User> findByIdWithOrders(Long id);

# 問題：迴圈中執行查詢
users = session.query(User).all()  # 1 query
for user in users:
    print(len(user.orders))  # N queries

# 解決方案：使用 selectinload
users = session.query(User).options(selectinload(User.orders)).all()

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/mydb
    username: ${DB_USER:user}
    password: ${DB_PASSWORD:password}
  jpa:
    hibernate:
      ddl-auto: validate
    show-sql: false
    properties:
      hibernate:
        format_sql: true
        dialect: org.hibernate.dialect.MySQL8Dialect

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

DATABASE_URL = "mysql+pymysql://user:password@localhost:3306/mydb"

engine = create_engine(
    DATABASE_URL,
    echo=False,  # 生產環境設為 False
    pool_size=10,
    max_overflow=20,
    pool_recycle=3600
)

SessionLocal = sessionmaker(bind=engine)