如何使用Spring Projections和Join实现DTO?

19-02-16 banq
                   

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();
    
}