StationRouteCacheRepository.java
7.02 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
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
package com.bsth.repository;
import com.bsth.entity.StationRouteCache;
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 org.springframework.transaction.annotation.Transactional;
import java.util.List;
/**
*
* @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)
public void stationRouteCacheDel(String lineCode,Integer dir);
@Modifying
@Query(value="delete from bsth_c_stationroute_cache where line = ?1 and directions = ?2 ", nativeQuery=true)
public void stationRouteCacheDel(Integer lineId,Integer dir);
@Query(value = "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.directions AS 'stationRoute.directions'," +
"r.distances AS 'stationRoute.distances'," +
"r.to_time AS 'stationRoute.toTime'," +
"r.descriptions AS 'stationRoute.descriptions'," +
"r.versions AS 'stationRoute.versions'," +
"r.shapes_type AS stationShapesType,"+
"r.radius AS stationRadius," +
"ST_AsText(r.g_polygon_grid) AS stationGPloyonGrid," +
"ST_AsText(r.b_polygon_grid) AS stationBPolyonGrid," +
"b.id AS 'station.id'," +
"b.code AS 'station.stationCod'," +
"b.db_type AS 'station.dbType'," +
"b.b_jwpoints AS 'station.bJwpoints'," +
"b.g_lonx AS 'station.gLonx'," +
"b.g_lonx AS 'station.gLaty', " +
"r.destroy AS destroy " +
"FROM bsth_c_stationroute_cache r " +
"LEFT JOIN bsth_c_station b " +
"ON r.station = b.id WHERE r.line = ?1 and r.directions = ?2 and r.destroy=0 ORDER BY r.station_route_code 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);
/**
* @Description :TODO(站点添加)
*/
@Transactional
@Modifying
@Query(value="INSERT INTO bsth_c_stationroute_cache (line, line_code, station, station_code, station_name, station_route_code," +
" b_polygon_grid, g_polygon_grid, radius, shapes_type, station_mark, directions, distances, to_time, destroy, versions) " +
" VALUES(?1,?2,?3,?4,?5,?6,ST_GeomFromText(?7),ST_GeomFromText(?8),?9,?10,?11,?12,?13,?14,?15,?16)", nativeQuery=true)
public void stationRouteCacheAdd(Integer line, String line_code, Integer station, String station_code, String station_name, Integer station_route_code,
String b_polygon_grid, String g_polygon_grid, Integer radius, String shapes_type,
String station_mark ,Integer directions, double distances,double to_time, Integer destroy, Integer versions);
/**
* @Description :TODO(站点更新)
*/
@Transactional
@Modifying
@Query(value="UPDATE bsth_c_stationroute SET " +
"line = ?1 , " +
"station = ?2 , " +
"station_code = ?3 , " +
"station_name = ?4 , " +
"line_code = ?5 , " +
"b_polygon_grid = ST_GeomFromText(?6) , " +
"g_polygon_grid = ST_GeomFromText(?7) , " +
"radius = ?8 , " +
"shapes_type = ?9 , " +
"station_route_code = ?10 , " +
"station_mark = ?11 , " +
"destroy = ?12 , " +
"directions = ?13 , " +
"distances = ?14 , " +
"to_time = ?15 , " +
"versions = ?16 " +
" WHERE id = ?17 ", nativeQuery=true)
public void stationRouteCacheUpdate(Integer line, Integer station, String station_code, String station_name, String line_code,
String b_polygon_grid, String g_polygon_grid, Integer radius, String shapes_type, Integer station_route_code,
String station_mark, Integer destroy ,Integer directions, double distances,double to_time, Integer versions, Integer id);
}