1 package fr.ifremer.quadrige2.core.dao.administration.program;
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 import com.google.common.base.Joiner;
27 import com.google.common.base.Preconditions;
28 import com.google.common.collect.Lists;
29 import com.google.common.collect.Maps;
30 import fr.ifremer.quadrige2.core.dao.technical.jdbc.OptionalDataSourceJdbcDaoSupport;
31 import fr.ifremer.quadrige2.core.exception.Quadrige2TechnicalException;
32 import fr.ifremer.quadrige2.core.vo.administration.program.ProgramVO;
33 import fr.ifremer.quadrige2.core.vo.administration.strategy.StrategyVO;
34 import org.apache.commons.io.IOUtils;
35 import org.apache.commons.lang3.StringUtils;
36 import org.springframework.beans.factory.InitializingBean;
37 import org.springframework.beans.factory.annotation.Autowired;
38 import org.springframework.context.annotation.Lazy;
39 import org.springframework.dao.DataAccessException;
40 import org.springframework.jdbc.core.ResultSetExtractor;
41 import org.springframework.jdbc.core.RowMapper;
42 import org.springframework.stereotype.Repository;
43
44 import javax.annotation.Resource;
45 import javax.sql.DataSource;
46 import java.io.IOException;
47 import java.io.InputStream;
48 import java.sql.ResultSet;
49 import java.sql.SQLException;
50 import java.util.List;
51 import java.util.Map;
52 import java.util.Properties;
53
54
55
56
57 @Repository("programStrategyJdbcDao")
58 @Lazy
59 public class ProgramStrategyJdbcDaoImpl
60 extends OptionalDataSourceJdbcDaoSupport
61 implements ProgramStrategyJdbcDao, InitializingBean {
62
63 private final static String QUERIES_FILE_PATH = "queries.jdbc.xml";
64
65 @Resource(name = "queriesJdbcProperties")
66 protected Properties queriesJdbcProperties;
67
68 protected Properties connectionProperties;
69
70
71
72
73
74
75
76 @Autowired
77 public ProgramStrategyJdbcDaoImpl(DataSource dataSource) {
78 super(dataSource);
79 this.connectionProperties = null;
80 }
81
82
83
84
85 public ProgramStrategyJdbcDaoImpl() {
86 this((Properties) null);
87 }
88
89
90
91
92
93
94
95 public ProgramStrategyJdbcDaoImpl(Properties connectionProperties) {
96 super();
97 this.connectionProperties = connectionProperties;
98
99
100 Properties properties = new Properties();
101 InputStream is = null;
102 try {
103 is = getClass().getClassLoader().getResourceAsStream(QUERIES_FILE_PATH);
104 properties.loadFromXML(is);
105
106 this.queriesJdbcProperties = properties;
107 } catch (IOException e) {
108 throw new Quadrige2TechnicalException(
109 String.format("Unable to read file [%s] from classpath", QUERIES_FILE_PATH),
110 e);
111 } finally {
112 IOUtils.closeQuietly(is);
113 }
114
115
116 checkAllQueriesExists();
117 }
118
119
120 @Override
121 public void afterPropertiesSet() throws Exception {
122
123 checkAllQueriesExists();
124 }
125
126
127 @Override
128 public List<ProgramVO> getProgramsByCodes(List<String> codes) {
129 String sql = queriesJdbcProperties.getProperty("programsByCodes");
130
131 sql = sql.replace(":codes", "'" + Joiner.on("','").skipNulls().join(codes) + "'");
132
133 Map<String, Object> paramMap = Maps.newHashMap();
134 return query(connectionProperties, sql, paramMap, new RowMapper<ProgramVO>() {
135 @Override
136 public ProgramVO mapRow(ResultSet rs, int rowNum) throws SQLException, DataAccessException {
137 return toProgramVO(rs);
138 }
139 });
140 }
141
142
143 @Override
144 public ProgramVO getProgramByCode(String code) {
145 String sql = queriesJdbcProperties.getProperty("programsByCodes");
146
147 Map<String, Object> paramMap = Maps.newHashMap();
148 paramMap.put("codes", code);
149
150 return query(connectionProperties, sql, paramMap, new ResultSetExtractor<ProgramVO>() {
151 @Override
152 public ProgramVO extractData(ResultSet rs) throws SQLException, DataAccessException {
153 return toProgramVO(rs);
154 }
155 });
156 }
157
158
159 @Override
160 public List<StrategyVO> getStrategiesByIds(List<Integer> ids) {
161 String sql = queriesJdbcProperties.getProperty("strategiesByIds");
162
163 sql = sql.replace(":ids", Joiner.on(",").skipNulls().join(ids));
164
165 Map<String, Object> paramMap = Maps.newHashMap();
166 return query(connectionProperties, sql, paramMap, new RowMapper<StrategyVO>() {
167 @Override
168 public StrategyVO mapRow(ResultSet rs, int rowNum) throws SQLException, DataAccessException {
169 return toStrategyVO(rs);
170 }
171 });
172 }
173
174
175 @Override
176 public StrategyVO getStrategyById(int id) {
177 String sql = queriesJdbcProperties.getProperty("strategiesByIds");
178
179 Map<String, Object> paramMap = Maps.newHashMap();
180 paramMap.put("ids", id);
181
182 return query(connectionProperties, sql, paramMap, new ResultSetExtractor<StrategyVO>() {
183 @Override
184 public StrategyVO extractData(ResultSet rs) throws SQLException, DataAccessException {
185 return toStrategyVO(rs);
186 }
187 });
188 }
189
190
191 @Override
192 public List<ProgramVO> getWritableProgramsByUserId(int userId) {
193 return getWritableProgramsByUserId(connectionProperties, userId);
194 }
195
196
197 @Override
198 public List<ProgramVO> getWritableProgramsByUserId(Properties connectionProperties, int userId) {
199
200 String sql = queriesJdbcProperties.getProperty("writableProgramsByQuserId");
201
202 Map<String, Object> paramMap = Maps.newHashMap();
203 paramMap.put("quserId", userId);
204 paramMap.put("viewerProgPrivId", ProgramPrivilegeIds.VIEWER.getValue());
205
206 final List<ProgramVO> result = Lists.newArrayList();
207
208
209 query(connectionProperties, sql, paramMap, source -> {
210 ProgramVO target = toProgramVO(source);
211 result.add(target);
212 });
213
214 return result;
215 }
216
217
218 @Override
219 public List<ProgramVO> getManagedProgramsByUserId(int userId) {
220 return getManagedProgramsByUserId(connectionProperties, userId);
221 }
222
223
224 @Override
225 public List<ProgramVO> getManagedProgramsByUserId(Properties connectionProperties, int userId) {
226
227 String sql = queriesJdbcProperties.getProperty("managedProgramsByQuserId");
228
229 Map<String, Object> paramMap = Maps.newHashMap();
230 paramMap.put("quserId", userId);
231 paramMap.put("managerProgPrivId", ProgramPrivilegeIds.MANAGER.getValue());
232
233 final List<ProgramVO> result = Lists.newArrayList();
234
235
236 query(connectionProperties, sql, paramMap, source -> {
237 ProgramVO target = toProgramVO(source);
238 result.add(target);
239 });
240
241 return result;
242 }
243
244
245
246
247
248
249 protected void checkAllQueriesExists() {
250 Preconditions.checkNotNull(queriesJdbcProperties);
251
252 checkQueryExists("programsByCodes");
253 checkQueryExists("strategiesByIds");
254 checkQueryExists("writableProgramsByQuserId");
255 checkQueryExists("managedProgramsByQuserId");
256 }
257
258
259
260
261
262
263
264 protected void checkQueryExists(String queryName) {
265 if (StringUtils.isBlank(queriesJdbcProperties.getProperty(queryName))) {
266 throw new Quadrige2TechnicalException(String.format("Property with name [%s] not exists on JDBC queries file", queryName));
267 }
268 }
269
270
271
272
273
274
275
276
277
278
279
280
281 protected ProgramVO toProgramVO(ResultSet rs) throws SQLException {
282 ProgramVO target = new ProgramVO();
283
284 int index = 1;
285 target.setProgCd(rs.getString(index++));
286 target.setProgNm(rs.getString(index++));
287
288 return target;
289 }
290
291
292
293
294
295
296
297
298
299
300
301
302 protected StrategyVO toStrategyVO(ResultSet rs) throws SQLException {
303 StrategyVO target = new StrategyVO();
304
305 int index = 1;
306 target.setStratId(rs.getInt(index++));
307 target.setStratNm(rs.getString(index++));
308
309
310 ProgramVO program = new ProgramVO();
311 program.setProgCd(rs.getString(index++));
312 program.setProgNm(rs.getString(index++));
313 target.setProgCd(program.getProgCd());
314
315 return target;
316 }
317 }