queryDSL 중복 데이터 하나만 남기고 제거

 

 

요청사항 : 3개 테이블을 patrol 테이블 기준으로 left join 을 하고 on절은 car_no 기준으로 한다. car_no가 중복 되는 값들은 car 테이블 in_date 컬럼에 최고의 값으로 출력한다.

• patrol 테이블

• car 테이블

• season 테이블

 
/*
* patrol 테이블 entity 생성 
*/
@Entity
@Setter @Getter
@NoArgsConstructor
@AllArgsConstructor
public class Patrol {
	
	@Id
	@GeneratedValue
	@Column(name="patrol_id")
	private	Long id;
	
	@Column(name="car_no")
	private String carNo;
	
	private String patrol_name;
	
	private LocalDateTime patrol_time;

}

/*
* car 테이블 entity 생성
*/

@Entity
@Setter @Getter
public class Car {
	@Id
	@GeneratedValue
	@Column(name="car_id")
	private Long id;
	
	private String in_car_no;
	
	@Column(name = "in_date")
	private Long inDate;
	
	private int parking_type;
}

/*
* seasoncar 테이블 entity 생성
*/
@Entity
@Setter @Getter
@NoArgsConstructor
@AllArgsConstructor
public class Seasoncar {
	@Id
	@GeneratedValue
	@Column(name="seasoncar_id")
	private Long id;
	
	private String season_car_no;
	
	private String dept_name;
	
	private String team_name;
}

/*
* 테이블 데이터 넣기
*/

public void query2ingsave() {
		
		for(int i=0; i<5 ; i++) {
			Patrol patrol = new Patrol();
			
			patrol.setCarNo("32가111"+i);
			patrol.setPatrol_name("이춘삼"+i);
			patrol.setPatrol_time(LocalDateTime.now());
			em.persist(patrol);
		}
			
		for(int i=0; i<3; i++) {
			Car car = new Car();
			
			car.setIn_car_no("32가1111");
			car.setInDate(1658143030L+Long.valueOf(i));
			car.setParking_type(i);
			
			em.persist(car);
		}
			
			
		for(int i=0; i<2;i++) {
			Car car2 = new Car();
			
			car2.setIn_car_no("32가1113");
			car2.setInDate(1658143030L+Long.valueOf(i));
			car2.setParking_type(i);
			
			em.persist(car2);
		}
		
			
		Seasoncar season = new Seasoncar();
		season.setDept_name("101");
		season.setTeam_name("101");
		season.setSeason_car_no("32가1111");
		
		em.persist(season);
			
		for(int i=0; i<2 ; i++) {
			Seasoncar season1 = new Seasoncar();
			season1.setDept_name("10"+i);
			season1.setTeam_name("10"+i);
			season1.setSeason_car_no("32가1115");
			
			em.persist(season1);
		}
			
		
		
	}

//patrolDTO

@Data
public class PatrolDTO {
	
	private String carNo;
	
	private String patrol_name;
	
	private LocalDateTime patrol_time;
	
	private Long inDate;
	
	private int parking_type;
	
	private String dept_name;
	
	private String team_name;
}

• left join sql 쿼리 및 queryDSL

select 
	p.car_no ,
    p.patrol_name ,
    p.patrol_time ,
    c.in_date ,
    c.parking_type ,
    s.dept_name ,
    s.team_name 
from patrol p
left join car c
on p.car_no = c.in_car_no
left join seasoncar s 
on p.car_no = s.season_car_no;

/*
  쿼리 DSL
*/
		QCar car = QCar.car;
		QPatrol patrol = QPatrol.patrol;
		QSeasoncar seasoncar = QSeasoncar.seasoncar;
		
		JPAQueryFactory queryFactory = new JPAQueryFactory(em);
		
		BooleanBuilder builder = new BooleanBuilder();
		
		List<PatrolDTO> result = queryFactory.select(
                                                Projections.fields(PatrolDTO.class, 
                                                        patrol.carNo,
                                                        patrol.patrol_name,
                                                        patrol.patrol_time,
                                                        car.inDate,
                                                        car.parking_type,
                                                        seasoncar.dept_name,
                                                        seasoncar.team_name
                                                        )
                                            )
                                        .from(patrol)
                                        .leftJoin(car)
                                        .on(patrol.carNo.eq(car.in_car_no))
                                        .leftJoin(seasoncar)
                                        .on(patrol.carNo.eq(seasoncar.season_car_no))
                                        .fetch();

left join 결과 : 아래 있는 빨간 데이터 값만 추출하고 싶음

1. where 서브쿼리 방식

select 
	p.car_no ,
    p.patrol_name ,
    p.patrol_time ,
    c.in_date ,
    c.parking_type ,
    s.dept_name ,
    s.team_name 
from patrol p
left join car c
on p.car_no = c.in_car_no
left join seasoncar s 
on p.car_no = s.season_car_no
where c.in_date = ( select max(c2.in_date) 
					from car c2
					where c.in_car_no = c2.in_car_no);

//queryDSL

QCar car = QCar.car;
		QCar maxcar = new QCar("maxcar");
		QPatrol patrol = QPatrol.patrol;
		QSeasoncar seasoncar = QSeasoncar.seasoncar;
		
		JPAQueryFactory queryFactory = new JPAQueryFactory(em);
		
		BooleanBuilder builder = new BooleanBuilder();
		
		List<PatrolDTO> result = queryFactory.select(
                                         Projections.fields(PatrolDTO.class, 
                                                    patrol.carNo,
                                                    patrol.patrol_name,
                                                    patrol.patrol_time,
                                                    car.inDate,
                                                    car.parking_type,
                                                    seasoncar.dept_name,
                                                    seasoncar.team_name
                                                    )
                                        )
                                    .from(patrol)
                                    .leftJoin(car)
                                    .on(patrol.carNo.eq(car.in_car_no))
                                    .leftJoin(seasoncar)
                                    .on(patrol.carNo.eq(seasoncar.season_car_no))
                                    .where(car.inDate.eq(
                                                        JPAExpressions.select(maxcar.inDate.max()).from(maxcar).where(maxcar.in_car_no.eq(car.in_car_no))
                                                        ))
                                    .fetch();
		
		for(PatrolDTO item : result) {
			
			System.out.println(item);
			
		}

결과

null 값이 다 제거 됩니다. 원하는 결과 아님

• 서브쿼리 방식 설명 예제 (cross join)

//ps table
id | player_id | score | ...
1  | 1         |  10   | ...
2  | 2         |  21   | ...
3  | 3         |  9    | ...
4  | 1         |  30   | ...
5  | 3         |  2    | ...

//ps2 table
id | player_id | score | ...
1  | 1         |  10   | ...
2  | 2         |  21   | ...
3  | 3         |  9    | ...
4  | 1         |  30   | ...
5  | 3         |  2    | ...
cross join
  | player_id | score | ...      | player_id | score | ...
1  | 1         |  10   | ...      | 1         |  10   | ...
1  | 1         |  10   | ...      | 1         |  30   | ...
4  | 1         |  30   | ...      | 1         |  10   | ...
4  | 1         |  30   | ...      | 1         |  30   | ...
  | 2         |  21   | ...      | 2         |  21   | ...
  | 3         |  9    | ...      | 3         |  9    | ...
  | 3         |  9    | ...      | 3         |  2    | ...
  | 3         |  2    | ...      | 3         |  9    | ...
  | 3         |  2    | ...      | 3         |  2    | ...

//해당 쿼리

SELECT *
FROM player_score ps
WHERE ps.score = 
(
    SELECT max(ps2.score)
    FROM player_score ps2
    WHERE ps2.player_id = ps.player_id
)

id | player_id | score | ...
2  | 2         |  21   | ...
3  | 3         |  9    | ...
4  | 1         |  30   | ...

 

2. 그룹별 순번 지정

• queryDSL 에는 없는 문법 아래와 같이 코딩하였다.

//postgresql 쿼리
select *
from (select 
	p.car_no ,
    p.patrol_name ,
    p.patrol_time ,
    c.in_date ,
    c.parking_type ,
    s.dept_name ,
    s.team_name ,
    row_number() over(partition by p.car_no order by c.in_date desc) as rownum
from patrol p
left join car c
on p.car_no = c.in_car_no
left join seasoncar s 
on p.car_no = s.season_car_no) newta
where newta.rownum = 1;

//queryDSL 

QCar car = QCar.car;
QCar maxcar = new QCar("maxcar");
QPatrol patrol = QPatrol.patrol;
QSeasoncar seasoncar = QSeasoncar.seasoncar;

JPAQueryFactory queryFactory = new JPAQueryFactory(em);

//car_no 에 대한 max 값만 추출한다. 
List<Tuple> maxIndate = queryFactory.select(car.in_car_no,
                                        car.inDate.max())
                                        .from(car)
                                        .groupBy(car.in_car_no)
                                        .fetch();
		
		// patrol.carNo 기준으로 left join 한다.
		List<PatrolDTO> joinAll = queryFactory.select(
                                            Projections.fields(PatrolDTO.class, 
                                                    patrol.carNo,
                                                    patrol.patrol_name,
                                                    patrol.patrol_time,
                                                    car.inDate,
                                                    car.parking_type,
                                                    seasoncar.dept_name,
                                                    seasoncar.team_name
                                                    )
                                        )
                                    .from(patrol)
                                    .leftJoin(car)
                                    .on(patrol.carNo.eq(car.in_car_no))
                                    .leftJoin(seasoncar)
                                    .on(patrol.carNo.eq(seasoncar.season_car_no))
                                    .fetch();
		
		List<PatrolDTO> result = new ArrayList<>();
		
		// left join 결과 값을 for문 시작
		for(PatrolDTO item : joinAll ) {
			// car_no max 값
			for(Tuple item2 : maxIndate) {
				// left join 결과 값의 indate 와 car Indate max 값을 비교하여 데이터 추출
				if(item2.get(car.in_car_no) != null && String.valueOf(item2.get(car.in_car_no)).equals(item.getCarNo()) ) {
					if(Long.valueOf(item2.get(car.inDate.max())) <= item.getInDate() ) {
						result.add(item);
					}
				}
			}
			// left join 값중 indate값이  null이면 데이터 추출
			if(item.getInDate() == null) {
				result.add(item);
			}
			
		}

• 원하는 결과