StationRouteCacheRepository.java
6.16 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
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
package com.bsth.repository;
import java.util.List;
import org.springframework.data.jpa.repository.EntityGraph;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;
import com.bsth.entity.StationRouteCache;
/**
*
* @Interface: StationRouteRepository(站点路由Repository数据持久层接口)
*
* @Extends : BaseRepository
*
* @Description: TODO(站点路由Repository数据持久层接口)
*
* @Author bsth@lq
*
* @Date 2016年5月03日 上午9:21:17
*
* @Version 公交调度系统BS版 0.1
*
*/
@Repository
public interface StationRouteCacheRepository extends BaseRepository<StationRouteCache, Integer> {
/**
* @Description :TODO(站点中心点坐标查询)
*
* @param map <lineId:线路ID； direction：方向>
*
* @return List<Object[]>
*/
/*@Query(value = "SELECT s.b_jwpoints,s.station_name FROM (" +
"SELECT b.station FROM bsth_c_stationroute b where b.line =?1 and b.directions = ?2 and b.destroy=0) r " +
"LEFT JOIN bsth_c_station s on r.station = s.id", nativeQuery=true)*/
@Query(value = "SELECT s.b_jwpoints,r.station_name,r.station_route_code FROM (" +
"SELECT b.station,b.station_route_code,b.station_name FROM bsth_c_stationroute_cache b where b.line =?1 and b.directions = ?2 and b.destroy=0) r " +
"LEFT JOIN bsth_c_station s on r.station = s.id order by r.station_route_code asc", nativeQuery=true)
List<Object[]> getSelectStationRouteCenterPoints(Integer lineId,Integer direction);
/**
* 查询站点路由
*/
@Query("select r from StationRouteCache r where r.line.id=?1 and r.directions=?2 and r.destroy=0 order by r.stationRouteCode")
public List<StationRouteCache> findstationRoute(Integer lineId,Integer dir);
/**
* 更新路线删除线路缓存站点
*
* @param lineCode
* @param dir
*/
@Modifying
@Query(value="delete from bsth_c_stationroute_cache where line_code = ?1 and directions = ?2 ", nativeQuery=true)
void stationRouteCacheDel(String lineCode,Integer dir);
@Modifying
@Query(value="delete from bsth_c_stationroute_cache where line = ?1 and directions = ?2 ", nativeQuery=true)
void stationRouteCacheDel(Integer lineId,Integer dir);
@Query(value = "SELECT a.`stationRoute.id`," +
"a.`stationRoute.line`," +
"a.`stationRoute.station`," +
"a.`stationRoute.stationName`," +
"a.`stationRoute.stationRouteCode`," +
"a.`stationRoute.lineCode`," +
"a.`stationRoute.stationMark`," +
"a.`stationRoute.outStationNmber`," +
"a.`stationRoute.directions`," +
"a.`stationRoute.distances`," +
"a.`stationRoute.toTime`," +
"a.`stationRoute.firstTime`," +
"a.`stationRoute.endTime`," +
"a.`stationRoute.descriptions`," +
"a.`stationRoute.versions`," +
"b.id AS 'station.id'," +
"b.station_cod AS 'station.stationCod'," +
"b.station_name AS 'station.stationName'," +
"b.road_coding AS 'station.roadCoding'," +
"b.db_type AS 'station.dbType'," +
"b.b_jwpoints AS 'station.bJwpoints'," +
"b.g_lonx AS 'station.gLonx'," +
"b.g_lonx AS 'station.gLaty'," +
"b.x AS 'station.x'," +
"b.y AS 'station.y'," +
"b.shapes_type AS 'station.shapesType'," +
"b.radius AS 'station.radius'," +
"ST_AsText(b.g_polygon_grid) AS 'station.gPolygonGrid'," +
"ST_AsText(b.b_polygon_grid) AS 'station.bPolygonGrid'," +
"b.destroy AS 'station.destroy'," +
"b.versions AS 'station.versions'," +
"b.descriptions AS 'station.descriptions'," +
"a.`industryCode` " +
" FROM (" +
"SELECT r.id AS 'stationRoute.id'," +
" r.line AS 'stationRoute.line'," +
"r.station AS 'stationRoute.station'," +
"r.station_name AS 'stationRoute.stationName'," +
"r.station_route_code as 'stationRoute.stationRouteCode'," +
"r.line_code AS 'stationRoute.lineCode'," +
"r.station_mark AS 'stationRoute.stationMark'," +
"r.out_station_nmber AS 'stationRoute.outStationNmber'," +
"r.directions AS 'stationRoute.directions'," +
"r.distances AS 'stationRoute.distances'," +
"r.to_time AS 'stationRoute.toTime'," +
"r.first_time AS 'stationRoute.firstTime'," +
"r.end_time AS 'stationRoute.endTime'," +
"r.descriptions AS 'stationRoute.descriptions'," +
"r.versions AS 'stationRoute.versions',r.industry_code AS industryCode FROM bsth_c_stationroute_cache r WHERE r.line = ?1 and r.directions = ?2 and r.destroy=0) a " +
"LEFT JOIN bsth_c_station b " +
"ON a.`stationRoute.station` = b.id ORDER BY a.`stationRoute.stationRouteCode` ASC", nativeQuery=true)
List<Object[]> findCachePoint(int lineId, int dir);
/**
* @Description :TODO(查询线线路方向下的站点路由)
*/
@EntityGraph(value = "stationRoute_station_cache", type = EntityGraph.EntityGraphType.FETCH)
@Query(value="select sr from StationRouteCache sr where sr.line.id = ?1 and sr.directions = ?2 and sr.destroy = ?3 ORDER BY sr.stationRouteCode ASC")
List<StationRouteCache> cacheList(Integer lineId, Integer directions, Integer destroy);
/**
* @Description :TODO(查询线路某方向下的站点序号与类型)
*
* @param map <lineId:线路ID； direction：方向；stationRouteCode：站点编码>
*
* @return List<Map<String, Object>>
*/
@Query(value = "select t.station_route_code,t.station_mark from bsth_c_stationroute_cache t where " +
" t.station_route_code =(" +
"select MAX(station_route_code) as stationRouteCode from bsth_c_stationroute_cache r WHERE " +
"r.line=?1 and r.directions =?2 and station_route_code< ?3 and r.destroy = 0 ) and t.line=?1 and t.directions = ?2 AND t.destroy = 0", nativeQuery=true)
List<Object[]> findUpStationRouteCode(Integer lineId,Integer direction,Integer stationRouteCode);
@Modifying
@Query(value="UPDATE bsth_c_stationroute_cache set station_route_code = (station_route_code+10) where line = ?1 and directions = ?2 and station_route_code >=?3 and destroy = 0", nativeQuery=true)
public void stationUpdStationRouteCode(Integer line,Integer dir,Integer routeCod);
}