如何使用Spring Projections和Join實現DTO？

banq發表於2019-02-16
SpringProject

SQL JOINS和DTO是我們打擊N + 1問題的好辦法，可參考DTO標籤看看其他方式，這裡，我們透過使用Spring Projections（DTO）和透過JPQL和本機SQL（用於MySQL）編寫各種Join聯接的概念證明。

1. 定義多個實體（例如，Tournament以及Player的雙向@OneToMany關係）


@Entity
@Table(name = "tournaments")
public class Tournament implements Serializable {

    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(name = "tournament_name")
    private String tname;

    @OneToMany(cascade = CascadeType.ALL,
            mappedBy = "tournament", orphanRemoval = true)
    private List<Player> players = new ArrayList<Player>();




@Entity
@Table(name = "players")
public class Player implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(name = "player_rank")
    private int prank;

    @Column(name = "player_name")
    private String pname;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "tournament_id")
    private Tournament tournament;



2. application配置：

spring.datasource.url=jdbc:mysql://localhost:3306/db_tennis?createDatabaseIfNotExist=true
spring.datasource.username=root
spring.datasource.password=root

spring.jpa.hibernate.ddl-auto=create
spring.jpa.show-sql=true

spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL5Dialect

spring.datasource.initialization-mode=always
spring.datasource.platform=mysql

spring.jpa.open-in-view=false

使用一些測試資料填充資料庫（例如，檢查檔案resources/data-mysql.sql）

3.編寫介面（projection）包含了需要從資料庫相關欄位的getters方法（例如TournamentPlayerNameDto，PlayerRankNameDto，TournamentIdNameDto）


public interface PlayerRankNameDto {
    
    String getPname();
    
    int getPrank();    
}

public interface TournamentPlayerNameDto {
            
    String getPname();
    
    String getTname();
    
}

public interface TournamentIdNameDto {
    
    Long getId();
    
    String getTname();    
}


4. 使用JPQL / native SQL編寫內部聯接查詢：
- 查詢所有玩家的錦標賽
     （localhost:8080/tournamentsOfPlayersNamesInnerJoinJpql）
    - 查詢所有排名小於或等於“ 排名 ”的玩家的錦標賽
     （localhost:8080/tournamentsIdNameByRankInnerJoinSql）

對應倉儲的內聯SQL:

@Repository
@Transactional(readOnly = true)
public interface TournamentRepository extends JpaRepository<Tournament, Long> {

    //Inner Joins
    // Query the tournaments of all players (JPQL)
    @Query(value = "SELECT a.pname as pname, b.tname as tname "
            + "FROM Player a JOIN a.tournament b")
    List<TournamentPlayerNameDto> fetchTournamentsOfPlayersNamesInnerJoinJpql();

    // Query the tournaments of all players (SQL)
    @Query(value = "SELECT a.player_name as pname, b.tournament_name as tname "
            + "FROM tournaments b INNER JOIN players a ON b.id = a.tournament_id",
            nativeQuery = true)
    List<TournamentPlayerNameDto> fetchTournamentsOfPlayersNamesInnerJoinSql();

    // Query all tournaments that have players with rank smaller or equal to "rank" (JPQL)
    // Note: HINT_PASS_DISTINCT_THROUGH - doesn't work with Spring Projections
    @Query(value = "SELECT DISTINCT b.id as id, b.tname as tname "
            + "FROM Player a JOIN a.tournament b WHERE a.prank <= ?1")    
    List<TournamentIdNameDto> fetchTournamentsIdNameByRankInnerJoinJpql(int rank);
    
        // Query all tournaments that have players with rank smaller or equal to "rank" (SQL)
    // Note: HINT_PASS_DISTINCT_THROUGH - doesn't work with Spring Projections
    @Query(value = "SELECT DISTINCT b.id as id, b.tournament_name as tname "
            + "FROM tournaments b INNER JOIN players a ON b.id = a.tournament_id "
            + "WHERE a.player_rank <= ?1",
            nativeQuery = true)    
    List<TournamentIdNameDto> fetchTournamentsIdNameByRankInnerJoinSql(int rank);
}

原始碼可以在這裡找到  。


左連線 :
@Repository
@Transactional(readOnly = true)
public interface TournamentRepository extends JpaRepository<Tournament, Long> {

    //Left Joins
    // Query all tournaments even if they don't have players (JPQL)
    @Query(value = "SELECT a.pname as pname, b.tname as tname "
            + "FROM Tournament b LEFT JOIN b.players a")
    List<TournamentPlayerNameDto> fetchAllTournamentsLeftJoinJpql();

    // Query all tournaments even if they don't have players (SQL)
    @Query(value = "SELECT a.player_name as pname, b.tournament_name as tname "
            + "FROM tournaments b LEFT JOIN players a ON b.id = a.tournament_id",
            nativeQuery = true)
    List<TournamentPlayerNameDto> fetchAllTournamentsLeftJoinSql();

}

測試原始碼

右聯

@Repository
@Transactional(readOnly = true)
public interface TournamentRepository extends JpaRepository<Tournament, Long> {

    //Right Joins
    // Query all tournaments even if they don't have players (JPQL)
    @Query(value = "SELECT a.pname as pname, b.tname as tname "
            + "FROM Player a RIGHT JOIN a.tournament b")
    List<TournamentPlayerNameDto> fetchAllTournamentsRightJoinJpql();
    
    // Query all tournaments even if they don't have players (SQL)
    @Query(value = "SELECT a.player_name as pname, b.tournament_name as tname "
            + "FROM players a RIGHT JOIN tournaments b ON b.id = a.tournament_id",
            nativeQuery = true)
    List<TournamentPlayerNameDto> fetchAllTournamentsRightJoinSql();
}


完全Join

@Repository
@Transactional(readOnly = true)
public interface TournamentRepository extends JpaRepository<Tournament, Long> {

    //Full Joins
    // Query all tournaments and players (JPQL)
    @Query(value = "SELECT a.pname as pname, b.tname as tname "
            + "FROM Tournament b FULL JOIN b.players a")
    List<TournamentPlayerNameDto> fetchAllTournamentsAndPlayersFullJoinJpql();
    
    // Query all tournaments and players (SQL)
    @Query(value = "SELECT a.player_name as pname, b.tournament_name as tname "
            + "FROM tournaments b FULL JOIN players a ON b.id = a.tournament_id",
            nativeQuery = true)
    List<TournamentPlayerNameDto> fetchAllTournamentsAndPlayersFullJoinSql();
}


左側排除連線

@Repository
@Transactional(readOnly = true)
public interface TournamentRepository extends JpaRepository<Tournament, Long> {

    //Left Excluding Joins
    // Query all tournaments that don't have players (JPQL)
    @Query(value = "SELECT a.pname as pname, b.tname as tname "
            + "FROM Tournament b LEFT JOIN b.players a WHERE a.id IS NULL")
    List<TournamentPlayerNameDto> fetchAllTournamentsLeftExcludingJoinJpql();

    // Query all tournaments that don't have players (SQL)
    @Query(value = "SELECT a.player_name as pname, b.tournament_name as tname "
            + "FROM tournaments b LEFT JOIN players a ON b.id = a.tournament_id WHERE a.id IS NULL",
            nativeQuery = true)
    List<TournamentPlayerNameDto> fetchAllTournamentsLeftExcludingJoinSql();
}


右排除Joins


@Repository
@Transactional(readOnly = true)
public interface TournamentRepository extends JpaRepository<Tournament, Long> {

    //Right Excluding Joins
    // Query all tournaments that don't have players (JPQL)
    @Query(value = "SELECT a.pname as pname, b.tname as tname "
            + "FROM Player a RIGHT JOIN a.tournament b WHERE a.id IS NULL")
    List<TournamentPlayerNameDto> fetchAllTournamentsRightExcludingJoinJpql();
    
    // Query all tournaments that don't have players (SQL)
    @Query(value = "SELECT a.player_name as pname, b.tournament_name as tname "
            + "FROM players a RIGHT JOIN tournaments b ON b.id = a.tournament_id WHERE a.id IS NULL",
            nativeQuery = true)
    List<TournamentPlayerNameDto> fetchAllTournamentsRightExcludingJoinSql();
}


Outer Excluding Joins


@Repository
@Transactional(readOnly = true)
public interface TournamentRepository extends JpaRepository<Tournament, Long> {

    // Outer Excluding Joins
    // Query all tournaments that don't have players 
    // and all players that don't participate in tournaments (JPQL)
    @Query(value = "SELECT a.pname as pname, b.tname as tname "
            + "FROM Player a FULL JOIN a.tournament b WHERE a.id IS NULL OR b.id IS NULL")
    List<TournamentPlayerNameDto> fetchAllTournamentsWithoutPlayersAndViceversaOuterExcludingJoinJpql();
    
    // Query all tournaments that don't have players 
    // and all players that don't participate in tournaments (SQL)
    @Query(value = "SELECT a.player_name as pname, b.tournament_name as tname "
            + "FROM players a FULL JOIN tournaments b ON b.id = a.tournament_id "
            + "WHERE a.id IS NULL OR b.id IS NULL",
            nativeQuery = true)
    List<TournamentPlayerNameDto> fetchAllTournamentsWithoutPlayersAndViceversaOuterExcludingJoinSql();
    
}

 

相關文章