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