View Javadoc

1   package uk.ac.ebi.intenz.mapper;
2   
3   import java.sql.Connection;
4   import java.sql.PreparedStatement;
5   import java.sql.ResultSet;
6   import java.sql.SQLException;
7   import java.util.ArrayList;
8   import java.util.List;
9   
10  import org.apache.log4j.Logger;
11  
12  import uk.ac.ebi.intenz.domain.constants.EnzymeSourceConstant;
13  import uk.ac.ebi.intenz.domain.constants.EnzymeViewConstant;
14  import uk.ac.ebi.intenz.domain.constants.Status;
15  import uk.ac.ebi.intenz.domain.enzyme.EnzymeComment;
16  
17  /**
18   * Maps link information to the corresponding database tables.
19   * <p/>
20   *
21   * @author Michael Darsow
22   * @version $Revision: 1.3 $ $Date: 2009/05/26 14:59:09 $
23   */
24  public class EnzymeCommentMapper {
25  
26    private static final String COLUMNS = "enzyme_id, comment_text, order_in, status, source, web_view";
27  
28    private static final Logger LOGGER =
29  	  Logger.getLogger(EnzymeCofactorMapper.class.getName());
30  
31    public EnzymeCommentMapper() {
32    }
33  
34    private String findStatement() {
35      return "SELECT " + COLUMNS +
36             " FROM comments" +
37             " WHERE enzyme_id = ? ORDER BY order_in";
38    }
39  
40    private String exportSibCommentsStatement() {
41      return "SELECT " + COLUMNS +
42             " FROM comments WHERE enzyme_id = ?" +
43             " AND (web_view = ? OR web_view = ? OR web_view = ? OR web_view = ?)" +
44             " FOR UPDATE ORDER BY order_in";
45    }
46  
47    private String insertStatement() {
48      return "INSERT INTO comments (enzyme_id, comment_text, order_in, status, source, web_view) VALUES (?, ?, ?, ?, ?, ?)";
49    }
50  
51    private String updateStatement() {
52      return "UPDATE comments SET comment_text = ?, order_in = ?, status = ?, source = ?, web_view = ? WHERE enzyme_id = ?";
53    }
54  
55    private String deleteAllStatement() {
56      return "DELETE comments WHERE enzyme_id = ?";
57    }
58  
59    /**
60     * Tries to find comment information about an enzyme.
61     *
62     * @param enzymeId Enzyme ID of entry.
63     * @param con      The logical connection.
64     * @return a <code>String</code> representing the comment.
65     * @throws SQLException
66     */
67    public List<EnzymeComment> find(Long enzymeId, Connection con) throws SQLException {
68      if (enzymeId == null) throw new NullPointerException("Parameter 'enzymeId' must not be null.");
69      if (con == null) throw new NullPointerException("Parameter 'con' must not be null.");
70  
71      PreparedStatement findStatement = null;
72      ResultSet rs = null;
73      List<EnzymeComment> result = new ArrayList<EnzymeComment>();
74      boolean noResult = true;
75  
76      try {
77        findStatement = con.prepareStatement(findStatement());
78        findStatement.setLong(1, enzymeId.longValue());
79        rs = findStatement.executeQuery();
80        while (rs.next()) {
81          noResult = false;
82          EnzymeComment enzymeComment = doLoad(rs);
83          result.add(enzymeComment);
84        }
85      } finally {
86      	if (rs != null) rs.close();
87        if (findStatement != null) findStatement.close();
88      }
89  
90      if (noResult) {
91        LOGGER.info("No comment information found for the enzyme with ID " + enzymeId);
92        return null;
93      }
94      return result;
95    }
96  
97    /**
98     * Exports all comments which are displayed in the ENZYME view.
99     *
100    * Affected table rows will be locked.
101    *
102    * @param enzymeId The enzyme ID used to retreive the related comments.
103    * @param con The database connection.
104    * @return an {@link java.util.ArrayList} of comments or <code>null</code> if no comment could be found.
105    * @throws SQLException if a database error occured.
106    * @throws NullPointerException if either of the parameters is <code>null</code>.
107    */
108   public List<EnzymeComment> exportSibComments(Long enzymeId, Connection con) throws SQLException {
109     if (enzymeId == null) throw new NullPointerException("Parameter 'enzymeId' must not be null.");
110     if (con == null) throw new NullPointerException("Parameter 'con' must not be null.");
111 
112     PreparedStatement findStatement = null;
113     ResultSet rs = null;
114     List<EnzymeComment> result = new ArrayList<EnzymeComment>();
115     boolean noResult = true;
116 
117     try {
118       findStatement = con.prepareStatement(exportSibCommentsStatement());
119       findStatement.setLong(1, enzymeId.longValue());
120       findStatement.setString(2, EnzymeViewConstant.INTENZ.toString());
121       findStatement.setString(3, EnzymeViewConstant.IUBMB_SIB.toString());
122       findStatement.setString(4, EnzymeViewConstant.SIB.toString());
123       findStatement.setString(5, EnzymeViewConstant.SIB_INTENZ.toString());
124       rs = findStatement.executeQuery();
125       while (rs.next()) {
126         noResult = false;
127         EnzymeComment enzymeComment = doLoad(rs);
128         result.add(enzymeComment);
129       }
130     } finally {
131     	if (rs != null) rs.close();
132         if (findStatement != null) findStatement.close();
133     }
134 
135     if (noResult) return null;
136     return result;
137   }
138 
139   public void reload(List<EnzymeComment> comments, Long enzymeId, Status status, Connection con)
140           throws SQLException {
141     deleteAll(enzymeId, con);
142     insert(comments, enzymeId, status, con);
143   }
144 
145   /**
146    * Deletes all comments related to an enzyme instance.
147    *
148    * @param enzymeId Enzyme ID of the enzyme instance.
149    * @param con      A database connection.
150    * @throws SQLException if a database error occurs.
151    * @throws NullPointerException if any of the parameter is <code>null</code>.
152    */
153   public void deleteAll(Long enzymeId, Connection con) throws SQLException {
154     if (enzymeId == null) throw new NullPointerException("Parameter 'enzymeId' must not be null.");
155     if (con == null) throw new NullPointerException("Parameter 'con' must not be null.");
156 
157     PreparedStatement deleteAllStatement = null;
158 
159     try {
160       deleteAllStatement = con.prepareStatement(deleteAllStatement());
161       deleteAllStatement.setLong(1, enzymeId.longValue());
162       deleteAllStatement.execute();
163     } finally {
164       if (deleteAllStatement != null) deleteAllStatement.close();
165     }
166   }
167 
168   /**
169    * Stores the given comment into the database.
170    *
171    * @param comments The enzyme's comment.
172    * @param enzymeId The enzyme ID.
173    * @param status   ...
174    * @param con      ...
175    * @throws SQLException
176    */
177   public void insert(List<EnzymeComment> comments, Long enzymeId, Status status, Connection con)
178           throws SQLException {
179     if (comments == null) throw new NullPointerException("Parameter 'comments' must not be null.");
180     if (enzymeId == null) throw new NullPointerException("Parameter 'enzymeId' must not be null.");
181     if (status == null) throw new NullPointerException("Parameter 'status' must not be null.");
182     if (con == null) throw new NullPointerException("Parameter 'con' must not be null.");
183 
184     PreparedStatement insertStatement = null;
185     try {
186       insertStatement = con.prepareStatement(insertStatement());
187       for (int iii = 0; iii < comments.size(); iii++) {
188         EnzymeComment enzymeComment = comments.get(iii);
189         doInsert(enzymeComment, enzymeId, (iii+1), status, insertStatement);
190         insertStatement.execute();
191       }
192     } finally {
193       if (insertStatement != null) insertStatement.close();
194     }
195   }
196 
197   public void update(EnzymeComment comment, Long enzymeId, Status status, int orderIn, Connection con) throws SQLException {
198     if (comment == null) throw new NullPointerException("Parameter 'comment' must not be null.");
199     if (enzymeId == null) throw new NullPointerException("Parameter 'enzymeId' must not be null.");
200     if (status == null) throw new NullPointerException("Parameter 'status' must not be null.");
201     if (orderIn < 1) throw new IllegalArgumentException("Parameter 'orderIn' must not be > 0.");
202     if (con == null) throw new NullPointerException("Parameter 'con' must not be null.");
203 
204     PreparedStatement updateStatement = null;
205     try {
206       updateStatement = con.prepareStatement(updateStatement());
207       doUpdate(comment, enzymeId, status, orderIn, updateStatement);
208       updateStatement.execute();
209     } finally {
210       if (updateStatement != null) updateStatement.close();
211     }
212   }
213 
214   // ------------------- PRIVATE METHODS ------------------------
215 
216   /**
217    * Creates the <code>EnzymeComment</code> object from the given result set.
218    *
219    * @param rs The result set object.
220    * @return an <code>EnzymeComment</code> instance.
221    * @throws SQLException
222    */
223   private EnzymeComment doLoad(ResultSet rs) throws SQLException {
224     assert rs != null : "Parameter 'rs' must not be null.";
225 
226     String comment = "";
227     String source = "";
228     String webView = "";
229     if (rs.getString("comment_text") != null) comment = rs.getString("comment_text");
230     if (rs.getString("source") != null) source = rs.getString("source");
231     if (rs.getString("web_view") != null) webView = rs.getString("web_view");
232 
233     EnzymeComment enzymeComment = new EnzymeComment(comment, EnzymeSourceConstant.valueOf(source),
234                                                     EnzymeViewConstant.valueOf(webView));
235 
236     return enzymeComment;
237   }
238 
239   /**
240    * Sets the parameters of the prepared statement.
241    *
242    * @param comment         ...
243    * @param enzymeId        ...
244    * @param status          ...
245    * @param insertStatement ...
246    * @throws SQLException
247    */
248   private void doInsert(EnzymeComment comment, Long enzymeId, int orderIn, Status status,
249                         PreparedStatement insertStatement)
250           throws SQLException {
251     assert comment != null : "Parameter 'comment' must not be null.";
252     assert enzymeId != null : "Parameter 'enzymeId' must not be null.";
253     assert status != null : "Parameter 'status' must not be null.";
254     assert insertStatement != null : "Parameter 'insertStatement' must not be null.";
255 
256     insertStatement.setLong(1, enzymeId.longValue());
257     insertStatement.setString(2, comment.getCommentText());
258     insertStatement.setInt(3, orderIn);
259     insertStatement.setString(4, status.getCode());
260     insertStatement.setString(5, comment.getSource().toString());
261     insertStatement.setString(6, comment.getView().toString());
262   }
263 
264   private void doUpdate(EnzymeComment comment, Long enzymeId, Status status, int orderIn,
265                         PreparedStatement updateStatement)
266           throws SQLException {
267     assert comment != null : "Parameter 'comment' must not be null.";
268     assert enzymeId != null : "Parameter 'enzymeId' must not be null.";
269     assert status != null : "Parameter 'status' must not be null.";
270     assert orderIn > 0 : "Parameter 'orderIn' must not be > 0.";
271     assert updateStatement != null : "Parameter 'insertStatement' must not be null.";
272 
273     updateStatement.setString(1, comment.getCommentText());
274     updateStatement.setInt(2, orderIn);
275     updateStatement.setString(3, status.getCode());
276     updateStatement.setString(4, comment.getSource().toString());
277     updateStatement.setString(5, comment.getView().toString());
278     updateStatement.setLong(6, enzymeId.longValue());
279   }
280 }