Changeset 3167


Ignore:
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?.

Location:
DASISH/t5.6/backend/annotator-backend/trunk/annotator-backend/src/main/java/eu/dasish/annotation/backend/dao
Files:
5 edited

Legend:

Unmodified
Added
Removed

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

    r3152 r3167  
    5858     */
    5959    Number getAnnotationID(AnnotationIdentifier externalID) throws SQLException;
     60   
     61    /**
     62     *
     63     * @param annotationId
     64     * @return the amount of deleted sources; removes _aid_ from the DB, together with its tagrget sources to
     65     * which no other annotations refers.
     66     */
     67   
     68    public int deleteNotebook(Number annotationId) throws SQLException;
     69
    6070}

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

    r3154 r3167  
    6363   
    6464   
     65   
    6566}

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

    r3154 r3167  
    4343public class JdbcAnnotationDao extends JdbcResourceDao implements AnnotationDao {
    4444
     45   
    4546    public JdbcAnnotationDao(DataSource dataSource) {
    4647        setDataSource(dataSource);
    … …  
    7172               
    7273        String values = makeListOfValues(annotationIDs);
    73         String sql = "SELECT annotation.* FROM annotation WHERE annotation.annotation_id  IN "+values;
     74        String sql = "SELECT "+annotationStar+" FROM "+annotationTableName +" WHERE "+annotationAnnotation_id+"  IN "+values;
    7475        return getSimpleJdbcTemplate().query(sql, annotationInfoRowMapper);
    7576    }
    … …  
    7980        public AnnotationInfo mapRow(ResultSet rs, int rowNumber) throws SQLException {
    8081           AnnotationInfo annotationInfo = new AnnotationInfo();
    81            annotationInfo.setOwner(getResourceREF(Integer.toString(rs.getInt("owner_id"))));
    82            annotationInfo.setHeadline(rs.getString("headline"));
    83            annotationInfo.setTargetSources(getSources(rs.getString("body_xml")));
     82           annotationInfo.setOwner(getResourceREF(Integer.toString(rs.getInt(owner_id))));
     83           annotationInfo.setHeadline(rs.getString(headline));
     84           annotationInfo.setTargetSources(getSources(rs.getString(body_xml)));
    8485           return annotationInfo;
    8586        }
    … …  
    109110       
    110111        String values = makeListOfValues(annotationIDs);
    111         String sql = "SELECT annotation.annotation_id FROM annotation WHERE annotation.annotation_id  IN "+values;
     112        String sql = "SELECT "+annotationAnnotation_id+" FROM "+annotationTableName+" WHERE "+annotationAnnotation_id+"  IN "+values;
    112113        return getSimpleJdbcTemplate().query(sql, annotationREFRowMapper);
    113114    }
    … …  
    117118        public ResourceREF mapRow(ResultSet rs, int rowNumber) throws SQLException {
    118119           ResourceREF annotationREF = new ResourceREF();
    119            annotationREF.setRef(Integer.toString(rs.getInt("annotation_id")));
     120           annotationREF.setRef(Integer.toString(rs.getInt(annotation_id)));
    120121           return annotationREF;
    121122        }
    … …  
    136137            return null;
    137138        }
    138        String sql = "SELECT * FROM annotation WHERE annotation.annotation_id  = ?";
     139       String sql = "SELECT "+annotationStar+" FROM "+annotationTableName+" WHERE "+annotationAnnotation_id  +"= ?";
    139140       List<Annotation> result= getSimpleJdbcTemplate().query(sql, annotationRowMapper, annotationID);
    140141       
    … …  
    147148       
    148149        if (result.size()>1) {
    149            throw new SQLException("There are "+result.size()+" annotations with annotation_id "+annotationID);
     150           throw new SQLException("There are "+result.size()+" annotations with "+ annotation_id + " "+annotationID);
    150151       }
    151152       return result.get(0);
    … …  
    156157        public Annotation mapRow(ResultSet rs, int rowNumber) throws SQLException {
    157158           Annotation result = new Annotation();
    158            result.setHeadline(rs.getString("headline"));
     159           result.setHeadline(rs.getString(headline));
    159160           
    160161           ResourceREF ownerREF = new ResourceREF();
    161            ownerREF.setRef(String.valueOf(rs.getInt("owner_id")));
     162           ownerREF.setRef(String.valueOf(rs.getInt(owner_id)));
    162163           result.setOwner(ownerREF);
    163164           
    … …  
    173174           // TODO add external reference
    174175           
    175            result.setBody(convertToAnnotationBody(rs.getString("body_xml")));
     176           result.setBody(convertToAnnotationBody(rs.getString(body_xml)));
    176177           return result;
    177178        }
    … …  
    198199        }
    199200       
    200        String sql = "SELECT annotation.annotation_id FROM annotation WHERE annotation.external_id  = ?";
     201       String sql = "SELECT "+annotationAnnotation_id+" FROM "+annotationTableName+" WHERE "+annotationExternal_id+"  = ?";
    201202       List<Number> result= getSimpleJdbcTemplate().query(sql, annotationIDRowMapper, externalID.toString());
    202203       if (result == null) {
    … …  
    208209       
    209210       if (result.size()>1) {
    210            throw new SQLException("There are "+result.size()+" annotations with external_id "+externalID);
     211           throw new SQLException("There are "+result.size()+" annotations with"+ external_id +" "+externalID);
    211212       }
    212213       return result.get(0);
    … …  
    216217        @Override
    217218        public Number mapRow(ResultSet rs, int rowNumber) throws SQLException {
    218            Number result = rs.getInt("annotation_id");
     219           Number result = rs.getInt(annotation_id);
    219220           return result;
    220221        }
    221222    };
     223     
     224     
     225     public int deleteNotebook(Number annotationId) throws SQLException{
     226        String sqlAnnotation = "DELETE FROM " + annotationTableName + " where "+annotation_id + " = ?";
     227        //String sqSources = "DELETE FROM " + sourceTableName + " where "+ notebook_id +"= ?";
     228        int affectedAnnotations = getSimpleJdbcTemplate().update(sqlAnnotation, annotationId);
     229        if (affectedAnnotations>1) {
     230            throw new SQLException("There was more than one annotation ("+affectedAnnotations+") with the same ID "+annotationId);
     231        }
     232        return affectedAnnotations;
     233        //TODO implement deleting sources (see the specification document and the interfaces' javadoc
     234    }
    222235     
    223236   

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

    r3151 r3167  
    2323import eu.dasish.annotation.backend.identifiers.NotebookIdentifier;
    2424import eu.dasish.annotation.backend.identifiers.UserIdentifier;
    25 import eu.dasish.annotation.schema.AnnotationInfo;
    2625import eu.dasish.annotation.schema.Annotations;
    2726import eu.dasish.annotation.schema.Notebook;
    … …  
    4140import org.springframework.beans.factory.annotation.Autowired;
    4241import org.springframework.dao.DataAccessException;
    43 import org.springframework.jdbc.core.simple.SimpleJdbcDaoSupport;
    4442import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
    4543
    … …  
    5351    @Autowired
    5452    private AnnotationDao jdbcAnnotationDao;
    55     final static private String notebookTableName = "notebook";
    56     final static private String notebooksAnnotationsTableName = "notebooks_annotations";
    57     final static private String notebook_id = "notebook_id";
     53   
    5854
    5955    public JdbcNotebookDao(DataSource dataSource) {
    … …  
    6359    @Override
    6460    public List<NotebookInfo> getNotebookInfos(UserIdentifier userID) {
    65         String sql = "SELECT notebook.title, notebook.external_id FROM notebook, principal where principal.principal_id = notebook.owner_id and principal.external_id = ?";
     61        String sql = "SELECT "+notebookTitle+", "+notebookExternal_id+" FROM "+notebookTableName+", "+principalTableName+" where "+principalPrincipal_id+" = "+notebookOwner_id+" and "+principalExternal_id+" = ?";
    6662        return getSimpleJdbcTemplate().query(sql, notebookInfoRowMapper, userID.toString());
    6763    }
    … …  
    6965    @Override
    7066    public List<Notebook> getUsersNotebooks(UserIdentifier userID) {
    71         String sql = "SELECT notebook.* FROM notebook, principal where principal_id = owner_id and principal.external_id = ?";
     67        String sql = "SELECT "+notebookStar+" FROM "+notebookTableName+", "+principalTableName+" where "+principal_id+" = "+owner_id+" and "+principalExternal_id+" = ?";
    7268        return getSimpleJdbcTemplate().query(sql, notebookRowMapper, userID.toString());
    7369    }
    … …  
    7773        try {
    7874            final NotebookIdentifier notebookIdentifier = new NotebookIdentifier();
    79             String sql = "INSERT INTO notebook (external_id, title, owner_id) VALUES (:notebookId, :title, (SELECT principal_id FROM principal WHERE principal.external_id = :userID))";
     75            String sql = "INSERT INTO "+notebookTableName+" ("+external_id+", "+this.title+","+ owner_id+") VALUES (:notebookId, :title, (SELECT "+principal_id+" FROM "+principalTableName+" WHERE "+principalExternal_id+" = :userID))";
    8076            Map<String, Object> params = new HashMap<String, Object>();
    8177            params.put("notebookId", notebookIdentifier.getUUID().toString());
    … …  
    9288        public NotebookInfo mapRow(ResultSet rs, int rowNumber) throws SQLException {
    9389            NotebookInfo notebookInfo = new NotebookInfo();
    94             notebookInfo.setRef(rs.getString("external_id")); // todo: what is ref? should it be the external id?
    95             notebookInfo.setTitle(rs.getString("title"));
     90            notebookInfo.setRef(rs.getString(external_id)); // todo: what is ref? should it be the external id?
     91            notebookInfo.setTitle(rs.getString(title));
    9692//            notebookInfo.setRef(rs.getString("URI"));
    9793            return notebookInfo;
    … …  
    10399            Notebook notebook = new Notebook();
    104100//          notebook.setId(rs.getInt("notebook_id"));
    105             notebook.setTitle(rs.getString("title"));
     101            notebook.setTitle(rs.getString(title));
    106102            GregorianCalendar calendar = new GregorianCalendar();
    107             calendar.setTime(rs.getTimestamp("time_stamp"));
     103            calendar.setTime(rs.getTimestamp(time_stamp));
    108104            try {
    109105                XMLGregorianCalendar gregorianCalendar = DatatypeFactory.newInstance().newXMLGregorianCalendar(calendar);
    … …  
    113109            }
    114110//            notebook.setURI(rs.getString("URI_ID"));
    115             notebook.setAnnotations(getAnnotations(rs.getInt("notebook_id")));
     111            notebook.setAnnotations(getAnnotations(rs.getInt(notebook_id)));
    116112            return notebook;
    117113        }
    … …  
    121117    @Override
    122118    public int deleteNotebook(NotebookIdentifier notebookId) {
    123         String sql1 = "DELETE FROM " + notebooksAnnotationsTableName + " where notebook_id = (SELECT notebook_id FROM notebook WHERE external_id = ?)";
     119        String sql1 = "DELETE FROM " + notebooksAnnotationsTableName + " where "+notebook_id +"= (SELECT "+notebook_id+" FROM "+notebookTableName+" WHERE "+external_id+" = ?)";
    124120        String sql2 = "DELETE FROM notebook where external_id = ?";
    125121        int affectedAnnotations = getSimpleJdbcTemplate().update(sql1, notebookId.getUUID().toString());
    … …  
    133129            SimpleJdbcInsert notebookInsert = new SimpleJdbcInsert(getDataSource()).withTableName(notebooksAnnotationsTableName);
    134130            Map<String, Object> params = new HashMap<String, Object>();
    135             params.put("notebook_id", notebookId);
    136             params.put("annotation_id", annotationId);
     131            params.put(notebook_id, notebookId);
     132            params.put(annotation_id, annotationId);
    137133            int rowsAffected = notebookInsert.execute(params);
    138134            return rowsAffected;
    … …  
    157153
    158154        if (isNotebookInTheDataBase(notebookID)) {
    159             String sql = "SELECT notebooks_annotations.annotation_id  FROM notebooks_annotations where notebook_id = ?";
     155            String sql = "SELECT "+notebooksAnnotationsTableNameAnnotation_id+"  FROM "+notebooksAnnotationsTableName+" where "+notebook_id+" = ?";
    160156            return getSimpleJdbcTemplate().query(sql, annotationIDRowMapper, notebookID.toString());
    161157        } else {

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

    r3146 r3167  
    3030 */
    3131public class JdbcResourceDao extends SimpleJdbcDaoSupport implements ResourceDao{
     32   
     33    // base string constants: table Names
     34    final static protected String notebookTableName = "notebook";
     35    final static protected String annotationTableName = "annotation";
     36    final static protected String sourceTableName = "source";     
     37    final static protected String principalTableName = "principal";   
     38    final static protected String notebooksAnnotationsTableName = "notebooks_annotations";
     39   
     40   
     41    // base string constants: field Names
     42    final static protected String annotation_id = "annotation_id";
     43    final static protected String notebook_id = "notebook_id";
     44    final static protected String source_id = "source_id";
     45    final static protected String external_id = "external_id";
     46    final static protected String owner_id = "owner_id";
     47    final static protected String headline = "headline";
     48    final static protected String body_xml = "body_xml";
     49    final static protected String title="title";
     50    final static protected String principal_id = "principal_id";
     51    final static protected String time_stamp = "time_stamp";
     52   
     53    // derived string constants: table+field names
     54   
     55    final static protected String annotationStar = annotationTableName+".*";
     56    final static protected String annotationAnnotation_id = annotationTableName+"."+annotation_id;
     57    final static protected String annotationExternal_id = annotationTableName+"."+external_id;
     58   
     59    final static protected String notebookStar = notebookTableName+".*";
     60    final static protected String notebookNotebook_id = notebookTableName+"."+notebook_id;
     61    final static protected String notebookTitle=notebookTableName+"."+title;
     62    final static protected String notebookExternal_id = notebookTableName+"."+external_id;
     63    final static protected String notebookOwner_id = notebookTableName+"."+owner_id;
     64   
     65    final static protected String notebooksAnnotationsTableNameAnnotation_id = "notebooks_annotations.annotation_id";
     66   
     67    final static protected String principalPrincipal_id = principalTableName+"."+principal_id;
     68    final static protected String principalExternal_id = principalTableName+"."+external_id;
     69   
    3270    //////////////////////////////////////////
    3371    /**
    … …  
    4381           return false;
    4482       }
    45        String sql = "SELECT notebook.notebook_id  FROM notebook where notebook_id = ?";
     83       String sql = "SELECT "+notebookNotebook_id+"  FROM notebook where "+notebook_id+" = ?";
    4684       List<Number> result=getSimpleJdbcTemplate().query(sql, isNotebookInTheDataBaseRowMapper, notebookID.toString());
    4785       if (result == null) {
    … …  
    5795        @Override
    5896        public Integer mapRow(ResultSet rs, int rowNumber) throws SQLException {
    59             Integer notebookId = rs.getInt("notebook_id");
     97            Integer notebookId = rs.getInt(notebook_id);
    6098            return notebookId;
    6199        }
Note: See TracChangeset for help on using the changeset viewer.