使用Spring JPA，写原生SQL，关联多表，并且分页

public interface UserRepository extends JpaRepository<User, Long>, 
                                         JpaSpecificationExecutor<User> {

    @Query(value = """
        SELECT u.id, u.name, o.order_no, o.amount, p.product_name
        FROM users u
        LEFT JOIN orders o ON u.id = o.user_id
        LEFT JOIN order_products op ON o.id = op.order_id
        LEFT JOIN products p ON op.product_id = p.id
        WHERE u.status = :status
        AND (:name IS NULL OR u.name LIKE CONCAT('%', :name, '%'))
        ORDER BY u.create_time DESC
        """,
        countQuery = """
        SELECT COUNT(*)
        FROM users u
        LEFT JOIN orders o ON u.id = o.user_id
        LEFT JOIN order_products op ON o.id = op.order_id
        LEFT JOIN products p ON op.product_id = p.id
        WHERE u.status = :status
        AND (:name IS NULL OR u.name LIKE CONCAT('%', :name, '%'))
        """,
        nativeQuery = true)
    Page<Object[]> findUserOrderInfo(@Param("status") Integer status,
                                     @Param("name") String name,
                                     Pageable pageable);
}

@Service
public class UserService {
    
    public Page<UserOrderDTO> getUsersWithOrders(Integer status, String name, int page, int size) {
        Pageable pageable = PageRequest.of(page, size, Sort.by(Sort.Direction.DESC, "u.createTime"));
        
        Page<Object[]> result = userRepository.findUserOrderInfo(status, name, pageable);
        
        return result.map(row -> {
            UserOrderDTO dto = new UserOrderDTO();
            dto.setUserId(((Number) row[0]).longValue());
            dto.setUserName((String) row[1]);
            dto.setOrderNo((String) row[2]);
            dto.setAmount((BigDecimal) row[3]);
            dto.setProductName((String) row[4]);
            return dto;
        });
    }
}

@Repository
public class CustomUserRepositoryImpl implements CustomUserRepository {

    @PersistenceContext
    private EntityManager entityManager;

    @Override
    public Page<UserOrderDTO> searchUserOrders(UserSearchCriteria criteria, Pageable pageable) {
        // 构建基础 SQL
        StringBuilder selectSql = new StringBuilder("""
            SELECT u.id, u.name, o.order_no, o.amount, p.product_name
            FROM users u
            LEFT JOIN orders o ON u.id = o.user_id
            LEFT JOIN order_products op ON o.id = op.order_id
            LEFT JOIN products p ON op.product_id = p.id
            WHERE 1=1
            """);
        
        StringBuilder countSql = new StringBuilder("""
            SELECT COUNT(*)
            FROM users u
            LEFT JOIN orders o ON u.id = o.user_id
            LEFT JOIN order_products op ON o.id = op.order_id
            LEFT JOIN products p ON op.product_id = p.id
            WHERE 1=1
            """);

        // 动态条件
        List<String> conditions = new ArrayList<>();
        Map<String, Object> params = new HashMap<>();

        if (criteria.getStatus() != null) {
            conditions.add("AND u.status = :status");
            params.put("status", criteria.getStatus());
        }
        if (StringUtils.hasText(criteria.getName())) {
            conditions.add("AND u.name LIKE CONCAT('%', :name, '%')");
            params.put("name", criteria.getName());
        }

        String conditionStr = String.join(" ", conditions);
        selectSql.append(conditionStr).append(" ORDER BY u.create_time DESC");
        countSql.append(conditionStr);

        // 查询总数
        Query countQuery = entityManager.createNativeQuery(countSql.toString());
        params.forEach(countQuery::setParameter);
        long total = ((Number) countQuery.getSingleResult()).longValue();

        // 查询分页数据
        Query selectQuery = entityManager.createNativeQuery(selectSql.toString());
        params.forEach(selectQuery::setParameter);
        selectQuery.setFirstResult((int) pageable.getOffset());
        selectQuery.setMaxResults(pageable.getPageSize());

        List<Object[]> rows = selectQuery.getResultList();
        List<UserOrderDTO> dtos = rows.stream().map(this::convertToDTO).collect(Collectors.toList());

        return new PageImpl<>(dtos, pageable, total);
    }

    private UserOrderDTO convertToDTO(Object[] row) {
        UserOrderDTO dto = new UserOrderDTO();
        dto.setUserId(((Number) row[0]).longValue());
        dto.setUserName((String) row[1]);
        dto.setOrderNo((String) row[2]);
        dto.setAmount((BigDecimal) row[3]);
        dto.setProductName((String) row[4]);
        return dto;
    }
}

// 自定义 Repository 接口
public interface CustomUserRepository {
    Page<UserOrderDTO> searchUserOrders(UserSearchCriteria criteria, Pageable pageable);
}

// 主 Repository 继承自定义接口
public interface UserRepository extends JpaRepository<User, Long>, CustomUserRepository {
}

@SqlResultSetMapping(
    name = "UserOrderMapping",
    classes = @ConstructorResult(
        targetClass = UserOrderDTO.class,
        columns = {
            @ColumnResult(name = "id", type = Long.class),
            @ColumnResult(name = "name", type = String.class),
            @ColumnResult(name = "order_no", type = String.class),
            @ColumnResult(name = "amount", type = BigDecimal.class),
            @ColumnResult(name = "product_name", type = String.class)
        }
    )
)

@Query(nativeQuery = true, resultSetMapping = "UserOrderMapping")
Page<UserOrderDTO> findUserOrderInfo(...);

spring:
  jpa:
    database-platform: org.hibernate.dialect.MySQL8Dialect