Changeset 4495 for DASISH/t5.6/backend/annotator-backend/trunk/annotator-backend/src/main/java/eu/dasish/annotation/backend/dao/impl/JdbcNotebookDao.java
- Timestamp:
- 02/11/14 18:01:34 (10 years ago)
- File:
-
- 1 edited
Legend:
- Unmodified
- Added
- Removed
-
DASISH/t5.6/backend/annotator-backend/trunk/annotator-backend/src/main/java/eu/dasish/annotation/backend/dao/impl/JdbcNotebookDao.java
r4493 r4495 28 28 import java.util.List; 29 29 import java.util.Map; 30 import java.util.UUID; 30 31 import javax.sql.DataSource; 31 32 import org.springframework.jdbc.core.RowMapper; … … 33 34 import javax.xml.datatype.DatatypeFactory; 34 35 import javax.xml.datatype.XMLGregorianCalendar; 36 import org.slf4j.Logger; 37 import org.slf4j.LoggerFactory; 35 38 36 39 /** … … 42 45 public class JdbcNotebookDao extends JdbcResourceDao implements NotebookDao { 43 46 44 // @Autowired 45 // private AnnotationDao jdbcAnnotationDao; 47 private final Logger loggerNotebookDao = LoggerFactory.getLogger(JdbcNotebookDao.class); 48 46 49 public JdbcNotebookDao(DataSource dataSource) { 47 50 setDataSource(dataSource); … … 52 55 /// GETTERS ///////// 53 56 //////////////////////////////////////////////// 54 @Override55 public List<Number> getNotebookIDs(Number userID, Permission acessMode) {56 Map<String, Object> params = new HashMap<String, Object>();57 params.put("principalID", userID);58 params.put("accessMode", acessMode.value());59 String sql = "SELECT " + notebook_id + " FROM " + notebookPermissionsTableName + " WHERE " + principalPrincipal_id + " = :principalID" + " AND " + permission + " = :accessMode";60 return getSimpleJdbcTemplate().query(sql, internalIDRowMapper, params);61 }62 63 57 //////////////////////////////////////////////// 64 58 @Override 65 public List<Number> getOwnerNotebookIDs(Number userID) { 66 String sql = "SELECT " + notebook_id + " FROM " + notebookTableName + " WHERE " + owner_id + " = ?"; 67 return getSimpleJdbcTemplate().query(sql, internalIDRowMapper, userID); 68 } 69 70 59 public Number getOwner(Number notebookID) { 60 if (notebookID == null) { 61 loggerNotebookDao.debug("notebookID: " + nullArgument); 62 return null; 63 } 64 StringBuilder sql = new StringBuilder("SELECT "); 65 sql.append(owner_id).append(" FROM ").append(notebookTableName).append(" WHERE "). 66 append(notebook_id).append(" = ?"); 67 List<Number> result = getSimpleJdbcTemplate().query(sql.toString(), principalIDRowMapper, notebookID); 68 if (result.isEmpty()) { 69 return null; 70 } else { 71 return result.get(0); 72 } 73 } 74 75 @Override 76 public List<Number> getNotebookIDs(Number principalID, Permission permission) { 77 78 if (principalID == null) { 79 loggerNotebookDao.debug("princiaplID: " + nullArgument); 80 return null; 81 } 82 83 if (permission == null) { 84 loggerNotebookDao.debug("permission: " + nullArgument); 85 return null; 86 } 87 88 Map<String, Object> params = new HashMap<String, Object>(); 89 params.put("principalID", principalID); 90 params.put("accessMode", permission.value()); 91 StringBuilder sql = new StringBuilder("SELECT "); 92 sql.append(notebook_id).append(" FROM ").append(notebookPermissionsTableName).append(" WHERE "). 93 append(principal_id).append(" = :principalID AND ").append(permission).append(" = :accessMode"); 94 return getSimpleJdbcTemplate().query(sql.toString(), internalIDRowMapper, params); 95 } 96 97 //////////////////////////////////////////////// 98 @Override 99 public List<Number> getNotebookIDsOwnedBy(Number principalID) { 100 101 if (principalID == null) { 102 loggerNotebookDao.debug("principalID: " + nullArgument); 103 return null; 104 } 105 106 StringBuilder sql = new StringBuilder("SELECT "); 107 sql.append(notebook_id).append(" FROM ").append(notebookTableName).append(" WHERE "). 108 append(owner_id).append(" = ?"); 109 return getSimpleJdbcTemplate().query(sql.toString(), internalIDRowMapper, principalID); 110 } 71 111 72 112 @Override 73 113 public List<Number> getPrincipalIDsWithPermission(Number notebookID, Permission permission) { 74 if (notebookID == null) { 75 return null; 76 } 77 Map<String, Object> params= new HashMap<String, Object>(); 114 if (notebookID == null) { 115 loggerNotebookDao.debug("notebookID: " + nullArgument); 116 return null; 117 } 118 119 if (permission == null) { 120 loggerNotebookDao.debug("permission: " + nullArgument); 121 return null; 122 } 123 Map<String, Object> params = new HashMap<String, Object>(); 78 124 params.put("notebookID", notebookID); 79 params.put("permission", permission.value()); 80 81 String sql = "SELECT " + owner_id + " FROM " + notebookPermissionsTableName + " WHERE " + notebook_id +" = :notebookID AND " +this.permission + " = :permission"; 82 return getSimpleJdbcTemplate().query(sql, principalIDRowMapper, params); 83 84 } 85 86 @Override 87 public NotebookInfo getNotebookInfo(Number notebookID) { 88 if (notebookID == null) { 89 return null; 90 } 91 String sql = "SELECT " + notebookExternal_id + "," + notebookTitle + " FROM " + notebookTableName + " where " + notebook_id + " = ? LIMIT 1"; 92 List<NotebookInfo> result = getSimpleJdbcTemplate().query(sql, notebookInfoRowMapper, notebookID); 125 params.put("accessMode", permission.value()); 126 127 StringBuilder sql = new StringBuilder("SELECT "); 128 sql.append(principal_id).append(" FROM ").append(notebookPermissionsTableName).append(" WHERE "). 129 append(notebook_id).append(" = :notebookID AND ").append(this.permission).append(" = :accessMode"); 130 131 return getSimpleJdbcTemplate().query(sql.toString(), principalIDRowMapper, params); 132 133 } 134 135 @Override 136 public NotebookInfo getNotebookInfoWithoutOwner(Number notebookID) { 137 if (notebookID == null) { 138 loggerNotebookDao.debug("notebookID: " + nullArgument); 139 return null; 140 } 141 142 StringBuilder sql = new StringBuilder("SELECT "); 143 sql.append(external_id).append(" , ").append(title). 144 append(" FROM ").append(notebookTableName).append(" WHERE "). 145 append(notebook_id).append(" = :notebookID"); 146 List<NotebookInfo> result = getSimpleJdbcTemplate().query(sql.toString(), notebookInfoRowMapper, notebookID); 93 147 return (!result.isEmpty() ? result.get(0) : null); 94 148 } … … 104 158 105 159 @Override 106 public Notebook getNotebookWithoutAnnotationsAndPermissions(Number notebookID) { 107 if (notebookID == null) { 108 return null; 109 } 110 String sql = "SELECT " + notebookExternal_id + "," + notebookTitle + "," + last_modified + " FROM " + notebookTableName + " where " + notebook_id + " = ? LIMIT 1"; 111 List<Notebook> result = getSimpleJdbcTemplate().query(sql, notebookRowMapper, notebookID); 160 public Notebook getNotebookWithoutAnnotationsAndPermissionsAndOwner(Number notebookID) { 161 if (notebookID == null) { 162 loggerNotebookDao.debug("notebookID: " + nullArgument); 163 return null; 164 } 165 StringBuilder sql = new StringBuilder("SELECT "); 166 sql.append(external_id).append(" , ").append(title).append(" , ").append(last_modified). 167 append(" FROM ").append(notebookTableName).append(" WHERE "). 168 append(notebook_id).append(" = :notebookID"); 169 List<Notebook> result = getSimpleJdbcTemplate().query(sql.toString(), notebookRowMapper, notebookID); 112 170 return (!result.isEmpty() ? result.get(0) : null); 113 171 } … … 129 187 } 130 188 }; 189 190 @Override 191 public List<Number> getAnnotations(Number notebookID) { 192 193 if (notebookID == null) { 194 loggerNotebookDao.debug("notebookID: " + nullArgument); 195 return null; 196 } 197 198 StringBuilder sql = new StringBuilder("SELECT "); 199 sql.append(annotation_id). 200 append(" FROM ").append(notebooksAnnotationsTableName).append(" WHERE "). 201 append(notebook_id).append(" = :notebookID"); 202 return getSimpleJdbcTemplate().query(sql.toString(), annotationIDRowMapper, notebookID); 203 204 } 205 206 /** 207 * 208 * UPDATERS 209 * 210 * 211 */ 212 /** 213 * 214 * @param notebookID 215 * @return true if updated, false otherwise. Logs the reason if the notebook 216 * is not updated. 217 */ 218 @Override 219 public boolean updateNotebookMetadata(Number notebookID, String title, Number ownerID) { 220 221 if (notebookID == null) { 222 loggerNotebookDao.debug("notebookID: " + nullArgument); 223 return false; 224 } 225 226 if (title == null) { 227 loggerNotebookDao.debug("title: " + nullArgument); 228 return false; 229 } 230 231 if (ownerID == null) { 232 loggerNotebookDao.debug("ownerID: " + nullArgument); 233 return false; 234 } 235 236 Map<String, Object> params = new HashMap<String, Object>(); 237 params.put("notebookID", notebookID); 238 params.put("title", title); 239 params.put("owner", ownerID); 240 241 StringBuilder sql = new StringBuilder("UPDATE "); 242 sql.append(notebookTableName).append(" SET "). 243 append(last_modified).append("= default,"). 244 append(this.title).append("= :title, "). 245 append(owner_id).append("= :owner"). 246 append(" WHERE ").append(notebook_id).append("= :notebookID"); 247 int affectedRows = getSimpleJdbcTemplate().update(sql.toString(), params); 248 if (affectedRows <= 0) { 249 logger.info("For some reason no rows in the table notebooks were updated. "); 250 return false; 251 } else { 252 if (affectedRows > 1) { 253 logger.info("For some reason more than 1 row in the table notebooks were updated. that's strange."); 254 return true; 255 } else { 256 return true; 257 } 258 } 259 } 131 260 132 133 @Override 134 public List<Number> getAnnotations(int maximumAnnotations, int startannotation, String orderedBy, boolean orderingMode){ 135 return null; 136 } 137 138 139 /** 140 * 141 * UPDATERS 142 * 143 * 261 @Override 262 public boolean setOwner(Number notebookID, Number ownerID) { 263 264 if (notebookID == null) { 265 loggerNotebookDao.debug("notebookID: " + nullArgument); 266 return false; 267 } 268 269 if (ownerID == null) { 270 loggerNotebookDao.debug("ownerID: " + nullArgument); 271 return false; 272 } 273 274 Map<String, Object> params = new HashMap<String, Object>(); 275 params.put("notebookID", notebookID); 276 params.put("ownerID", ownerID); 277 278 StringBuilder sql = new StringBuilder("UPDATE "); 279 sql.append(notebookTableName).append(" SET "). 280 append(last_modified).append("= default,"). 281 append(owner_id).append("= :ownerID"). 282 append(" WHERE ").append(notebook_id).append("= :notebookID"); 283 int affectedRows = getSimpleJdbcTemplate().update(sql.toString(), params); 284 if (affectedRows <= 0) { 285 logger.info("For some reason no rows in the table notebooks were updated. "); 286 return false; 287 } else { 288 if (affectedRows > 1) { 289 logger.info("For some reason more than 1 row in the table notebooks were updated. that's strange."); 290 return true; 291 } else { 292 return true; 293 } 294 } 295 } 296 297 @Override 298 public boolean updateUserPermissionForNotebook(Number notebookID, Number principalID, Permission permission) { 299 300 if (notebookID == null) { 301 loggerNotebookDao.debug("notebookID: " + nullArgument); 302 return false; 303 } 304 305 if (principalID == null) { 306 loggerNotebookDao.debug("principalID: " + nullArgument); 307 return false; 308 } 309 310 if (permission == null) { 311 loggerNotebookDao.debug("permission: " + nullArgument); 312 return false; 313 } 314 315 316 Map<String, Object> params = new HashMap<String, Object>(); 317 params.put("notebookID", notebookID); 318 params.put("principalID", principalID); 319 params.put("permission", permission.value()); 320 321 StringBuilder sql = new StringBuilder("UPDATE "); 322 sql.append(notebookPermissionsTableName).append(" SET "). 323 append(notebook_id).append("= :notebookID, "). 324 append(principal_id).append("= :principalID"). 325 append(this.permission).append("= :permission"). 326 append(" WHERE ").append(notebook_id).append("= :notebookID"); 327 int affectedRows = getSimpleJdbcTemplate().update(sql.toString(), params); 328 if (affectedRows <= 0) { 329 logger.info("For some reason no rows in the table notebooks-permissions were updated. "); 330 return false; 331 } else { 332 if (affectedRows > 1) { 333 logger.info("For some reason more than 1 row in the table notebooks-permissions were updated. that's strange."); 334 return true; 335 } else { 336 return true; 337 } 338 } 339 } 340 341 /** 342 * 343 * ADDERS 344 * 345 * 144 346 */ 145 146 147 347 @Override 348 public Number createNotebookWithoutPermissionsAndAnnotations(Notebook notebook, Number ownerID) { 349 if (notebook == null) { 350 loggerNotebookDao.debug("notebook: " + nullArgument); 351 return null; 352 } 353 354 if (ownerID == null) { 355 loggerNotebookDao.debug("ownerID: " + nullArgument); 356 return null; 357 } 358 359 UUID externalID = UUID.randomUUID(); 360 Map<String, Object> params = new HashMap<String, Object>(); 361 params.put("externalId", externalID.toString()); 362 params.put("owner", ownerID); 363 params.put("title", notebook.getTitle()); 364 365 StringBuilder sql = new StringBuilder("INSERT INTO "); 366 sql.append(notebookTableName).append("(").append(external_id).append(",").append(owner_id); 367 sql.append(",").append(title). 368 append(" ) VALUES (:externalId, :owner, :title)"); 369 int affectedRows = getSimpleJdbcTemplate().update(sql.toString(), params); 370 return ((affectedRows > 0) ? getInternalID(externalID) : null); 371 } 372 373 @Override 374 public boolean addAnnotationToNotebook(Number notebookID, Number annotationID) { 375 376 if (notebookID == null) { 377 loggerNotebookDao.debug("notebookID: " + nullArgument); 378 return false; 379 } 380 381 if (annotationID == null) { 382 loggerNotebookDao.debug("annotationID: " + nullArgument); 383 return false; 384 } 385 386 Map<String, Object> params = new HashMap<String, Object>(); 387 params.put("notebookID", notebookID); 388 params.put("annotationID", annotationID); 389 390 StringBuilder sql = new StringBuilder("INSERT INTO "); 391 sql.append(notebooksAnnotationsTableName).append("(").append(notebook_id).append(",").append(annotation_id); 392 sql.append(",").append(" ) VALUES (:notebookID, :annotationID)"); 393 int affectedRows = getSimpleJdbcTemplate().update(sql.toString(), params); 394 return (affectedRows > 0); 395 } 396 397 @Override 398 public boolean addPermissionToNotebook(Number notebookID, Number principalID, Permission permission) { 399 if (notebookID == null) { 400 loggerNotebookDao.debug("notebookID: " + nullArgument); 401 return false; 402 } 403 404 if (principalID == null) { 405 loggerNotebookDao.debug("principalID: " + nullArgument); 406 return false; 407 } 408 409 if (permission == null) { 410 loggerNotebookDao.debug("premission: " + nullArgument); 411 return false; 412 } 413 414 Map<String, Object> params = new HashMap<String, Object>(); 415 params.put("notebookID", notebookID); 416 params.put("principalID", principalID); 417 params.put("permission", permission.value()); 418 419 StringBuilder sql = new StringBuilder("INSERT INTO "); 420 sql.append(notebookPermissionsTableName).append("(").append(notebook_id).append(",").append(principal_id); 421 sql.append(",").append(this.permission). 422 append(" ) VALUES (:notebookID, :principalID, :permission)"); 423 int affectedRows = getSimpleJdbcTemplate().update(sql.toString(), params); 424 return (affectedRows > 0); 425 } 426 148 427 /** 149 * 150 * @param notebookID 151 * @return true if updated, false otherwise. Logs the reason if the notebook is not updated. 428 * 429 * DELETERS 430 * 431 * 152 432 */ 153 433 @Override 154 public boolean updateNotebookMetadata(Number notebookID){ 155 return false; 156 } 157 /** 158 * 159 * ADDERS 160 * 161 * 162 */ 163 164 @Override 165 public Number createNotebook(Notebook notebook){ 166 return null; 167 } 168 169 @Override 170 public boolean addAnnotationToNotebook(Number noteboookId, Number AnnotationID){ 171 return false; 172 } 173 174 175 /** 176 * 177 * DELETERS (ADDER) 178 * 179 * 180 */ 181 182 @Override 183 public boolean deleteannotationFromNotebook(Number notebookID, Number annotationID){ 184 return false; 185 } 186 187 @Override 188 public boolean deleteNotebook(Number notebookID){ 189 return false; 434 public boolean deleteAnnotationFromNotebook(Number notebookID, Number annotationID) { 435 if (notebookID != null) { 436 if (annotationID != null) { 437 Map<String, Number> params = new HashMap(); 438 params.put("notebookID", notebookID); 439 params.put("annotationID", annotationID); 440 StringBuilder sql = new StringBuilder("DELETE FROM "); 441 sql.append(notebooksAnnotationsTableName).append(" WHERE ").append(notebook_id).append(" = :notebookID AND "). 442 append(annotation_id).append(" = :annotationID"); 443 int affectedRows = getSimpleJdbcTemplate().update(sql.toString(), params); 444 return (affectedRows > 0); 445 } else { 446 loggerNotebookDao.debug("annotationID: " + nullArgument); 447 return false; 448 } 449 } else { 450 loggerNotebookDao.debug("notebookID: " + nullArgument); 451 return false; 452 } 453 } 454 455 @Override 456 public boolean deleteNotebookPrincipalPermission(Number notebookID, Number principalID) { 457 if (notebookID != null) { 458 if (principalID != null) { 459 Map<String, Number> params = new HashMap(); 460 params.put("notebookID", notebookID); 461 params.put("principalID", principalID); 462 StringBuilder sqlPermissions = new StringBuilder("DELETE FROM "); 463 sqlPermissions.append(notebookPermissionsTableName).append(" WHERE ").append(notebook_id).append(" = :notebookID AND "). 464 append(principal_id).append(" = :principalID"); 465 int affectedRows = getSimpleJdbcTemplate().update(sqlPermissions.toString(), params); 466 return (affectedRows > 0); 467 } else { 468 loggerNotebookDao.debug("principalID: " + nullArgument); 469 return false; 470 } 471 } else { 472 loggerNotebookDao.debug("notebookID: " + nullArgument); 473 return false; 474 } 475 } 476 477 @Override 478 public boolean deleteAllAnnotationsFromNotebook(Number notebookID) { 479 if (notebookID != null) { 480 StringBuilder sql = new StringBuilder("DELETE FROM "); 481 sql.append(notebooksAnnotationsTableName).append(" WHERE ").append(notebook_id).append(" = ? "); 482 int affectedRows = getSimpleJdbcTemplate().update(sql.toString(), notebookID); 483 return (affectedRows > 0); 484 } else { 485 loggerNotebookDao.debug("notebookID: " + nullArgument); 486 return false; 487 } 488 } 489 490 @Override 491 public boolean deleteAllPermissionsForNotebook(Number notebookID) { 492 if (notebookID != null) { 493 StringBuilder sqlPermissions = new StringBuilder("DELETE FROM "); 494 sqlPermissions.append(notebookPermissionsTableName).append(" WHERE ").append(notebook_id).append(" = ? "); 495 int affectedRows = getSimpleJdbcTemplate().update(sqlPermissions.toString(), notebookID); 496 return (affectedRows > 0); 497 } else { 498 loggerNotebookDao.debug("notebookID: " + nullArgument); 499 return false; 500 } 501 } 502 503 @Override 504 public boolean deleteNotebook(Number notebookID) { 505 if (notebookID != null) { 506 StringBuilder sql = new StringBuilder("DELETE FROM "); 507 sql.append(notebookTableName).append(" WHERE ").append(notebook_id).append(" = ? "); 508 int affectedRows = getSimpleJdbcTemplate().update(sql.toString(), notebookID); 509 return (affectedRows > 0); 510 } else { 511 loggerNotebookDao.debug("notebookID: " + nullArgument); 512 return false; 513 } 190 514 } 191 515 }
Note: See TracChangeset
for help on using the changeset viewer.