Changeset 3167 for DASISH/t5.6/backend/annotator-backend/trunk/annotator-backend/src/main/java/eu/dasish/annotation/backend/dao/impl/JdbcAnnotationDao.java

Timestamp:

07/19/13 09:56:04 (11 years ago)

Author:

olhsha

Message:

completed Peter's work on refactroing sql requests vi using constants for field and table names. Now, if a table of field name is changed, you need to update the code only in one place: the corresponding string constant in JdbcReourceDao?.

File:

• DASISH/t5.6/backend/annotator-backend/trunk/annotator-backend/src/main/java/eu/dasish/annotation/backend/dao/impl/JdbcAnnotationDao.java (modified) (12 diffs)

DASISH/t5.6/backend/annotator-backend/trunk/annotator-backend/src/main/java/eu/dasish/annotation/backend/dao/impl/JdbcAnnotationDao.java

@@ -43 +43 @@
 public class JdbcAnnotationDao extends JdbcResourceDao implements AnnotationDao {
 
+    
     public JdbcAnnotationDao(DataSource dataSource) {
         setDataSource(dataSource);
@@ -71 +72 @@
                 
         String values = makeListOfValues(annotationIDs);
-        String sql = "SELECT annotation.* FROM annotation WHERE annotation.annotation_id  IN "+values;
+        String sql = "SELECT "+annotationStar+" FROM "+annotationTableName +" WHERE "+annotationAnnotation_id+"  IN "+values;
         return getSimpleJdbcTemplate().query(sql, annotationInfoRowMapper); 
     }
@@ -79 +80 @@
         public AnnotationInfo mapRow(ResultSet rs, int rowNumber) throws SQLException {
            AnnotationInfo annotationInfo = new AnnotationInfo();
-           annotationInfo.setOwner(getResourceREF(Integer.toString(rs.getInt("owner_id"))));
-           annotationInfo.setHeadline(rs.getString("headline")); 
-           annotationInfo.setTargetSources(getSources(rs.getString("body_xml")));
+           annotationInfo.setOwner(getResourceREF(Integer.toString(rs.getInt(owner_id))));
+           annotationInfo.setHeadline(rs.getString(headline)); 
+           annotationInfo.setTargetSources(getSources(rs.getString(body_xml)));
            return annotationInfo;
         }
@@ -109 +110 @@
         
         String values = makeListOfValues(annotationIDs);
-        String sql = "SELECT annotation.annotation_id FROM annotation WHERE annotation.annotation_id  IN "+values;
+        String sql = "SELECT "+annotationAnnotation_id+" FROM "+annotationTableName+" WHERE "+annotationAnnotation_id+"  IN "+values;
         return getSimpleJdbcTemplate().query(sql, annotationREFRowMapper); 
     }
@@ -117 +118 @@
         public ResourceREF mapRow(ResultSet rs, int rowNumber) throws SQLException {
            ResourceREF annotationREF = new ResourceREF();
-           annotationREF.setRef(Integer.toString(rs.getInt("annotation_id")));
+           annotationREF.setRef(Integer.toString(rs.getInt(annotation_id)));
            return annotationREF;
         }
@@ -136 +137 @@
             return null;
         }
-       String sql = "SELECT * FROM annotation WHERE annotation.annotation_id  = ?";
+       String sql = "SELECT "+annotationStar+" FROM "+annotationTableName+" WHERE "+annotationAnnotation_id  +"= ?";
        List<Annotation> result= getSimpleJdbcTemplate().query(sql, annotationRowMapper, annotationID); 
        
@@ -147 +148 @@
        
         if (result.size()>1) {
-           throw new SQLException("There are "+result.size()+" annotations with annotation_id "+annotationID);
+           throw new SQLException("There are "+result.size()+" annotations with "+ annotation_id + " "+annotationID);
        }
        return result.get(0);
@@ -156 +157 @@
         public Annotation mapRow(ResultSet rs, int rowNumber) throws SQLException {
            Annotation result = new Annotation();
-           result.setHeadline(rs.getString("headline"));
+           result.setHeadline(rs.getString(headline));
            
            ResourceREF ownerREF = new ResourceREF();
-           ownerREF.setRef(String.valueOf(rs.getInt("owner_id")));
+           ownerREF.setRef(String.valueOf(rs.getInt(owner_id)));
            result.setOwner(ownerREF);
            
@@ -173 +174 @@
            // TODO add external reference 
            
-           result.setBody(convertToAnnotationBody(rs.getString("body_xml")));
+           result.setBody(convertToAnnotationBody(rs.getString(body_xml)));
            return result;
         }
@@ -198 +199 @@
         }
         
-       String sql = "SELECT annotation.annotation_id FROM annotation WHERE annotation.external_id  = ?";
+       String sql = "SELECT "+annotationAnnotation_id+" FROM "+annotationTableName+" WHERE "+annotationExternal_id+"  = ?";
        List<Number> result= getSimpleJdbcTemplate().query(sql, annotationIDRowMapper, externalID.toString());
        if (result == null) {
@@ -208 +209 @@
        
        if (result.size()>1) {
-           throw new SQLException("There are "+result.size()+" annotations with external_id "+externalID);
+           throw new SQLException("There are "+result.size()+" annotations with"+ external_id +" "+externalID);
        }
        return result.get(0);
@@ -216 +217 @@
         @Override
         public Number mapRow(ResultSet rs, int rowNumber) throws SQLException {
-           Number result = rs.getInt("annotation_id");
+           Number result = rs.getInt(annotation_id);
            return result;
         }
     };
+      
+      
+     public int deleteNotebook(Number annotationId) throws SQLException{
+        String sqlAnnotation = "DELETE FROM " + annotationTableName + " where "+annotation_id + " = ?";
+        //String sqSources = "DELETE FROM " + sourceTableName + " where "+ notebook_id +"= ?";
+        int affectedAnnotations = getSimpleJdbcTemplate().update(sqlAnnotation, annotationId);
+        if (affectedAnnotations>1) {
+            throw new SQLException("There was more than one annotation ("+affectedAnnotations+") with the same ID "+annotationId);
+        }
+        return affectedAnnotations;
+        //TODO implement deleting sources (see the specification document and the interfaces' javadoc
+    }