SectionRouteCacheRepository.java
5.3 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.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import com.bsth.entity.SectionRoute;
import com.bsth.entity.SectionRouteCache;
/**
*
* @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 SectionRouteCacheRepository extends BaseRepository<SectionRouteCache, 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 sectionSpeedLimet ,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_cache 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[]> getSectionRouteCache(int lineId, int directions);
/**
* @Description :TODO(查询路段信息)
*
* @param map <id:路段路由ID>
*
* @return List<Object[]>
*/
@Query(value ="SELECT a.sectionRouteId," +
"a.sectionRouteLineCode," +
"a.sectionRouteCode," +
"a.sectionRouteDirections," +
"a.sectionRouteLine," +
"a.sectionRouteSection," +
"a.sectionRouteDescriptions," +
"a.sectionRouteCreateBy," +
"a.sectionRouteCreateDate," +
"a.sectionRouteUpdateBy," +
"a.sectionRouteUpdateDate," +
"a.sectionRouteVersions," +
"a.sectionRouteDestroy," +
"b.id AS sectionId," +
"b.section_code AS sectionCode," +
"b.section_name AS sectionName," +
"b.road_coding AS sectionRoadCoding," +
"b.end_node AS sectionEndCode," +
"b.start_node AS sectionStartNode," +
"b.middle_node AS sectionMiddleNode," +
"b.section_type AS sectionType," +
"ST_AsText(b.csection_vector) AS sectionCsectionVector," +
"ST_AsText(b.bsection_vector) AS sectionBsectionVector," +
"ST_AsText(b.gsection_vector) AS sectionGsectionVector," +
"b.section_distance AS sectionDistance," +
"b.section_time AS sectionTime," +
"b.db_type AS sectionDbtype," +
"b.speed_limit AS sectionSpeedLimit," +
"b.descriptions AS sectionDescriptions," +
"b.create_by AS sectionCreateBy," +
"b.create_date AS sectionCreateDate," +
"b.update_by AS sectionUpdateBy," +
"b.update_date AS sectionUpdateDate," +
"b.versions AS sectionVersion , a.isRoadeSpeed FROM (" +
" SELECT s.id AS sectionRouteId," +
"s.line_code AS sectionRouteLineCode," +
"s.sectionroute_code AS sectionRouteCode," +
"s.directions AS sectionRouteDirections," +
"s.line AS sectionRouteLine," +
"s.section AS sectionRouteSection," +
"s.descriptions AS sectionRouteDescriptions," +
"s.create_by AS sectionRouteCreateBy," +
"s.create_date AS sectionRouteCreateDate," +
"s.update_by AS sectionRouteUpdateBy," +
"s.update_date AS sectionRouteUpdateDate," +
"s.versions AS sectionRouteVersions," +
"s.destroy AS sectionRouteDestroy, s.is_roade_speed AS isRoadeSpeed " +
" FROM bsth_c_sectionroute s where s.id =?1) a " +
" LEFT JOIN bsth_c_section b on a.sectionRouteSection = b.id", nativeQuery=true)
List<Object[]> findSectionRouteInfoFormId(int id);
/**
* 更新路线删除线路缓存路段
*
* @param line
* @param dir
*/
@Modifying
@Query(value="delete from bsth_c_sectionroute_cache where line = ?1 and directions = ?2", nativeQuery=true)
public void sectionRouteCacheDel(Integer line,Integer dir);
}