StationRepository.java
2.24 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
package com.bsth.repository;
import java.util.List;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import com.bsth.entity.Station;
/**
*
* @Interface: StationRepository(站点Repository数据持久层接口)
*
* @Extends : BaseRepository
*
* @Description: TODO(站点Repository数据持久层接口)
*
* @Author bsth@lq
*
* @Date 2016年05月03日 上午9:21:17
*
* @Version 公交调度系统BS版 0.1
*
*/
@Repository
public interface StationRepository extends BaseRepository<Station, Integer> {
/**
* 查询最大站点ID
* @Description :TODO(查询最大站点ID)
* @return int <站点id>
*/
@Query(value = "SELECT IFNULL(MAX(id), 0) FROM bsth_c_station", nativeQuery = true)
int findLatestStationId();
@Query(value = " SELECT CONCAT(ST_X(t.center_point), ' ', ST_Y(t.center_point)) b_jwpoints,t.id FROM (" +
" SELECT b.id,b.center_point,b.station_name,a.directions FROM (" +
" SELECT s.station,s.station_name,s.directions FROM bsth_c_stationroute s where s.destroy = 0 and s.directions = ?1) a " +
" LEFT JOIN bsth_c_station b on a.station = b. id) t where t.station_name LIKE ?2"
, nativeQuery=true)
List<Object[]> findStationName(Integer dir , String stationName);
@Query(value = "SELECT ST_AsText(b.buffer_polygon_wgs) g_polygon_grid, b.shaped_type,CONCAT(ST_X(a.center_point), ' ', ST_Y(a.center_point)) g_center_point , b.radius, a.station_code, b.station_name FROM bsth_c_station a JOIN bsth_c_stationroute b ON a.id = b.station WHERE b.line_code = ?1 AND a.station_code = ?2", nativeQuery = true)
Object[][] findBufferArea(String lineCode, String stationCode);
/**
* 根据站点被引用情况生成站点的途径线路
*/
@Modifying
@Query(value="UPDATE bsth_c_station t4 JOIN (SELECT t1.id, GROUP_CONCAT(t3.name,'-',t2.directions,'[',t2.station_name,']') pass_lines FROM bsth_c_station t1 LEFT JOIN bsth_c_stationroute t2 ON t1.id = t2.station LEFT JOIN bsth_c_line t3 ON t2.line = t3.id WHERE t2.destroy = 0 GROUP BY t1.id) t5 ON t4.id = t5.id SET t4.pass_lines = t5.pass_lines", nativeQuery = true)
void generatePassLine();
}