SectionRouteRepository.java
4.79 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
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.SectionRoute;
/**
*
* @Interface: SectionRouteRepository(路段路由Repository数据持久层接口)
*
* @Extends : BaseRepository
*
* @Description: TODO(路段路由Repository数据持久层接口)
*
* @Author bsth@lq
*
* @Date 2016年05月03日 上午9:21:17
*
* @Version 公交调度系统BS版 0.1
*
*/
@Repository
public interface SectionRouteRepository extends BaseRepository<SectionRoute, Integer> {
/**
* @Description :TODO(查询路段信息)
*
* @param map <lineId:线路ID； directions：方向>
*
* @return List<Object[]>
*/
@Query(value ="SELECT a.sectionrouteId," +
"a.sectionrouteLine," +
" a.sectionrouteLineCode," +
" a.sectionrouteSection," +
" a.sectionrouteSectionCode," +
" a.sectionrouteCode," +
" a.sectionrouteDirections," +
" b.id AS sectionId," +
" b.section_code AS sectionCode," +
" b.section_name AS sectionName," +
" b.croses_road AS sectionRoad," +
" b.end_node AS sectionEndNode," +
" b.start_node AS sectionStartNode," +
" b.middle_node AS sectionMiddleNode," +
" b.section_type AS sectionType," +
" b.csection_vector AS sectionCsectionVector," +
" ST_AsText(b.bsection_vector) AS sectionBsectionVector," +
" ST_AsText(b.gsection_vector) AS sectionGsectionVector," +
" b.road_coding AS sectionRoadCoding," +
" b.section_distance AS sectionDistance," +
" b.section_time AS sectionTime," +
" b.db_type AS sectiondbType," +
" b.speed_limit AS sectionSpeedLimit ,a.destroy,a.versions,a.descriptions,a.isRoadeSpeed FROM (" +
"SELECT r.id AS sectionrouteId," +
"r.line AS sectionrouteLine," +
"r.line_code AS sectionrouteLineCode," +
"r.section AS sectionrouteSection," +
"r.section_code AS sectionrouteSectionCode," +
"r.sectionroute_code AS sectionrouteCode," +
"r.directions AS sectionrouteDirections," +
"r.destroy AS destroy," +
"r.versions AS versions," +
"r.descriptions AS descriptions, r.is_roade_speed AS isRoadeSpeed" +
" FROM bsth_c_sectionroute r where r.line = ?1 and r.directions = ?2 and r.destroy=0 ) a " +
" LEFT JOIN bsth_c_section b ON a.sectionrouteSection = b.id order by a.sectionrouteCode asc", nativeQuery=true)
List<Object[]> getSectionRoute(int lineId, int directions);
/**
* @Description :TODO(查询线路某方向下的上一个路段序号)
*
* @param map <lineId:线路ID； direction：方向；sectionRouteCode：路段编码>
*
* @return List<Map<String, Object>>
*/
@Query(value = " select MAX(r.sectionroute_code) as sectionrouteCode from bsth_c_sectionroute r WHERE r.line=?1 and r.directions =?2 and r.sectionroute_code< ?3 and r.destroy = 0", nativeQuery=true)
List<Object[]> findUpSectionRouteCode(Integer lineId,Integer direction,Integer stationRouteCode);
/**
* @Description :TODO(获取路段路由信息)
*
* @param lineId：线路ID；directions：方向
*
* @return List<Map<String, Object>> {[0]：directions（方向）；[1]：bsection_vector(路段坐标)；[2]：speed_limit(限速)；[3]：section_name(路段名称)}
*/
@Query(value = "SELECT " +
"c.directions," +
"ST_AsText(s.gsection_vector) as gsection_vector," +
"s.speed_limit," +
"s.section_name " +
" FROM bsth_c_sectionroute c " +
" LEFT JOIN bsth_c_section s on c.section = s.id where c.line = ?1 and c.directions = ?2 and c.destroy = 0", nativeQuery=true)
List<Object[]> sectionRouteVector(Integer lineId,Integer directions);
@Modifying
@Query(value="UPDATE bsth_c_sectionroute set sectionroute_code = (sectionroute_code+1) where line = ?1 and directions = ?2 and sectionroute_code >=?3 and destroy = 0", nativeQuery=true)
void sectionUpdSectionRouteCode(Integer line,Integer dir,Integer routeCod);
@Query("select r from SectionRoute r where r.line.id=?1 and r.directions=?2 and r.destroy=0 order by r.sectionrouteCode")
List<SectionRoute> findByLine(Integer lineId, Integer dir);
/**
* 删除相应线路和版本的站点路由信息
* @param lineId
* @param version
*/
@Modifying
@Query(value="delete from SectionRoute sr where sr.line.id = ?1")
void deleteByLineAndVersion(Integer lineId);
/**
* 历史路由表变更之后从历史表批量更新当前版本至当前表
* @param lineId
* @param version
*/
@Modifying
@Query(value="insert into bsth_c_sectionroute select * from bsth_c_ls_sectionroute where line = ?1 and versions = ?2", nativeQuery = true)
void updateFromHistory(Integer lineId, Integer version);
}