【Postopia Dev Log】Week 4

之前都是在mac上进行开发，现在换到Windows上，跑不起来

原来是因为自定义query方法里的字段设置不对（应与entity中字段而非表字段保持一致）

之前在mac上用code跑项目，运行时并没有查出这个错误，现在在win上用idea跑检查出来了

经过接近一个月（生活琐事与开学前的休息）再重新打开项目，忘了该从哪里继续项目，以后要注意记录开发进度

进度：

like, dislike post, comment

query like status in post

update user nickname

to do:

query comment (recursive query, complex)

spring boot application configure port

resource/application.properties

server.port=8081

i defined “void likePost(Long id, @AuthenticationPrincipal User user);” in an interface, should its implementation method signature be “void likePost(Long id, @AuthenticationPrincipal User user)” or “void likePost(Long id, User user);” is enough?

The implementation method signature should match the interface declaration exactly, including any annotations.

The @AuthenticationPrincipal annotation is significant and should be retained in the implementation for the following reasons:

Consistency: The implementation should match the interface definition to maintain consistency and avoid confusion.
Functionality: The @AuthenticationPrincipal annotation is typically used in Spring Security to automatically resolve the currently authenticated user. Removing it might affect how the user object is populated or handled by the framework.
Clarity: Keeping the annotation in the implementation makes it clear to other developers that this method expects the authenticated user to be injected.

idea auto implemenation doesn’t retain the @AuthenticationPrincipal annotation

You’re right to notice that discrepancy. IDEs like IntelliJ IDEA sometimes don’t automatically retain certain annotations when generating method implementations. This is a known limitation in some IDE auto-implementation features.

Understand the issue: The IDE’s auto-implementation feature is dropping the @AuthenticationPrincipal annotation, which is not the desired behavior.

Correct approach: You should manually add the annotation back to the implementation method.

CASE in sql

Case statements in SQL allow you to add conditional logic to your queries. They’re useful for categorizing data or performing different calculations based on specific conditions.

The general syntax is:

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE default_result
END

Key points:

You can have multiple WHEN-THEN pairs.
The ELSE clause is optional. If omitted and no conditions are met, it returns NULL.
CASE statements can be used in SELECT, WHERE, and ORDER BY clauses.

Here’s a simple example:

SELECT 
    product_name,
    price,
    CASE
        WHEN price < 20 THEN 'Low'
        WHEN price >= 20 AND price < 50 THEN 'Medium'
        ELSE 'High'
    END AS price_category
FROM products;

This query categorizes products based on their price.

JOIN and LEFT JOIN

JOIN and LEFT JOIN are not the same. They are different types of SQL join operations that produce different results. Let me explain the key differences:

JOIN (or INNER JOIN):
- Returns only the rows that have matching values in both tables.
- If a row doesn’t have a match in the other table, it’s excluded from the result set.
LEFT JOIN (or LEFT OUTER JOIN):
- Returns all rows from the left table, and the matched rows from the right table.
- If there’s no match in the right table, the result still contains the row from the left table, with NULL values for the right table’s columns.

Here’s an example to illustrate:

Suppose we have two tables:

Employees:          Projects:
ID  Name            ID  ProjectName  EmployeeID
--  ----            --  -----------  ----------
1   Alice           1   Alpha        1
2   Bob             2   Beta         1
3   Charlie         3   Gamma        2
                    4   Delta        4

Using INNER JOIN:

SELECT e.Name, p.ProjectName
FROM Employees e
JOIN Projects p ON e.ID = p.EmployeeID;

Result:

Name    ProjectName
----    -----------
Alice   Alpha
Alice   Beta
Bob     Gamma

Using LEFT JOIN:

SELECT e.Name, p.ProjectName
FROM Employees e
LEFT JOIN Projects p ON e.ID = p.EmployeeID;

Result:

Name    ProjectName
----    -----------
Alice   Alpha
Alice   Beta
Bob     Gamma
Charlie NULL

Key differences in the results:

INNER JOIN doesn’t include Charlie because he has no matching project.
LEFT JOIN includes Charlie with a NULL project.
Neither join includes the Delta project because it has no matching employee.

In summary, use JOIN when you only want results with matches in both tables, and use LEFT JOIN when you want all rows from the left table, regardless of whether there’s a match in the right table.

Would you like me to elaborate on any part of this explanation or provide more examples?

extra query for user opinions

will using an extra query to get user opinion information rather than querying it by above method be more efficient?

That’s an excellent question. The efficiency of using a single query versus multiple queries depends on several factors. Let’s analyze both approaches:

Single Query Approach (current method):
- Pros:
  - Reduces the number of database round trips
  - Can be more efficient for smaller result sets
- Cons:
  - More complex query
  - Potential for larger result set due to the LEFT JOIN
  - May be slower for large datasets due to the join operation
Separate Queries Approach:
- Pros:
  - Simpler, more focused queries
  - Can be more efficient for large datasets
  - Allows for more granular caching strategies
- Cons:
  - Multiple database round trips
  - Potential for N+1 query problem if not implemented carefully

To determine which approach is more efficient, consider the following factors:

Dataset size: For smaller datasets, the single query approach might be faster. For larger datasets, separate queries could perform better.
Query complexity: If the join operation significantly complicates the query or slows down execution, separate queries might be preferable.
Caching strategies: Separate queries allow for more granular caching of post information and user opinions.
Network latency: If network latency between your application and database is high, fewer round trips (single query) might be beneficial.
Database indexes: Proper indexing can significantly impact the performance of both approaches.

Given these considerations, here’s a potential implementation using separate queries:

@Service
public class PostService {
    
    private final PostRepository postRepository;
    private final OpinionRepository opinionRepository;

    // Constructor injection...

    public Page<PostInfo> getPostsInSpace(Long spaceId, Long userId, int page, int size) {
        Pageable pageable = PageRequest.of(page, size);
        Page<PostInfo> posts = postRepository.findPostsInSpace(spaceId, pageable);
        
        List<Long> postIds = posts.getContent().stream()
                                  .map(PostInfo::getId)
                                  .collect(Collectors.toList());
        
        Map<Long, UserOpinion> opinions = opinionRepository.findUserOpinionsByPostIds(userId, postIds);
        
        posts.getContent().forEach(post -> 
            post.setUserOpinion(opinions.getOrDefault(post.getId(), UserOpinion.NO_OPINION))
        );
        
        return posts;
    }
}

@Repository
public interface PostRepository extends JpaRepository<Post, Long> {
    @Query("SELECT NEW com.heslin.postopia.dto.post.PostInfo(p.id, p.subject, p.content, p.positiveCount, p.negativeCount, p.commentCount, u.username, u.nickname, u.avatar) FROM Post p JOIN p.user u JOIN p.space s WHERE s.id = :spaceId ORDER BY p.createdAt DESC")
    Page<PostInfo> findPostsInSpace(@Param("spaceId") Long spaceId, Pageable pageable);
}

@Repository
public interface OpinionRepository extends JpaRepository<Opinion, Long> {
    @Query("SELECT o.post.id as postId, CASE WHEN o.isPositive = true THEN 'LIKE' WHEN o.isPositive = false THEN 'DISLIKE' ELSE 'NO_OPINION' END as opinion FROM Opinion o WHERE o.user.id = :userId AND o.post.id IN :postIds")
    List<Object[]> findUserOpinionsByPostIds(@Param("userId") Long userId, @Param("postIds") List<Long> postIds);

    default Map<Long, UserOpinion> findUserOpinionsByPostIds(Long userId, List<Long> postIds) {
        return findUserOpinionsByPostIds(userId, postIds).stream()
            .collect(Collectors.toMap(
                arr -> (Long) arr[0],
                arr -> UserOpinion.valueOf((String) arr[1])
            ));
    }
}

This approach allows you to:

Fetch posts without the complexity of joining with the opinions table.
Retrieve user opinions for only the fetched posts in a separate query.
Combine the results in memory.

To determine which method is more efficient for your specific use case, I recommend:

Implement both approaches.
Create performance tests with various dataset sizes and query patterns.
Profile the application to measure actual performance differences.
Consider the maintainability and readability of each approach in your codebase.

Remember, premature optimization can lead to unnecessary complexity. Start with the approach that’s most straightforward for your team to implement and maintain, then optimize if and when performance becomes an issue based on real-world usage patterns.

Common Table Expressions

Common Table Expressions (CTEs) in SQL are temporary named result sets that you can reference within a SELECT, INSERT, UPDATE, DELETE, or MERGE statement. They are defined using a WITH clause at the beginning of a query. Here’s a brief explanation of CTEs:

Syntax:

WITH cte_name AS (
    SELECT ...
)
SELECT ... FROM cte_name;

Key features:
- Improve readability by breaking complex queries into simpler, named parts
- Can be referenced multiple times within the main query
- Useful for recursive queries

Example:

WITH sales_summary AS (
    SELECT 
        product_id,
        SUM(quantity) as total_quantity,
        SUM(price * quantity) as total_revenue
    FROM sales
    GROUP BY product_id
)
SELECT 
    p.product_name,
    s.total_quantity,
    s.total_revenue
FROM products p
JOIN sales_summary s ON p.product_id = s.product_id;

CTEs are particularly useful for complex queries, hierarchical data, and when you need to reference the same subquery multiple times.

Would you like me to elaborate on any specific aspect of Common Table Expressions?

spring boot application configure port

@AuthorizedPrincipal related

CASE in sql

JOIN and LEFT JOIN

extra query for user opinions

Common Table Expressions