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(EnzymeCommentMapper.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.debug("No comment information found for the enzyme with ID "
92                + enzymeId);
93        return null;
94      }
95      return result;
96    }
97  
98    /**
99     * Exports all comments which are displayed in the ENZYME view.
100    *
101    * Affected table rows will be locked.
102    *
103    * @param enzymeId The enzyme ID used to retreive the related comments.
104    * @param con The database connection.
105    * @return an {@link java.util.ArrayList} of comments or <code>null</code> if no comment could be found.
106    * @throws SQLException if a database error occured.
107    * @throws NullPointerException if either of the parameters is <code>null</code>.
108    */
109   public List<EnzymeComment> exportSibComments(Long enzymeId, Connection con) throws SQLException {
110     if (enzymeId == null) throw new NullPointerException("Parameter 'enzymeId' must not be null.");
111     if (con == null) throw new NullPointerException("Parameter 'con' must not be null.");
112 
113     PreparedStatement findStatement = null;
114     ResultSet rs = null;
115     List<EnzymeComment> result = new ArrayList<EnzymeComment>();
116     boolean noResult = true;
117 
118     try {
119       findStatement = con.prepareStatement(exportSibCommentsStatement());
120       findStatement.setLong(1, enzymeId.longValue());
121       findStatement.setString(2, EnzymeViewConstant.INTENZ.toString());
122       findStatement.setString(3, EnzymeViewConstant.IUBMB_SIB.toString());
123       findStatement.setString(4, EnzymeViewConstant.SIB.toString());
124       findStatement.setString(5, EnzymeViewConstant.SIB_INTENZ.toString());
125       rs = findStatement.executeQuery();
126       while (rs.next()) {
127         noResult = false;
128         EnzymeComment enzymeComment = doLoad(rs);
129         result.add(enzymeComment);
130       }
131     } finally {
132     	if (rs != null) rs.close();
133         if (findStatement != null) findStatement.close();
134     }
135 
136     if (noResult) return null;
137     return result;
138   }
139 
140   public void reload(List<EnzymeComment> comments, Long enzymeId, Status status, Connection con)
141           throws SQLException {
142     deleteAll(enzymeId, con);
143     insert(comments, enzymeId, status, con);
144   }
145 
146   /**
147    * Deletes all comments related to an enzyme instance.
148    *
149    * @param enzymeId Enzyme ID of the enzyme instance.
150    * @param con      A database connection.
151    * @throws SQLException if a database error occurs.
152    * @throws NullPointerException if any of the parameter is <code>null</code>.
153    */
154   public void deleteAll(Long enzymeId, Connection con) throws SQLException {
155     if (enzymeId == null) throw new NullPointerException("Parameter 'enzymeId' must not be null.");
156     if (con == null) throw new NullPointerException("Parameter 'con' must not be null.");
157 
158     PreparedStatement deleteAllStatement = null;
159 
160     try {
161       deleteAllStatement = con.prepareStatement(deleteAllStatement());
162       deleteAllStatement.setLong(1, enzymeId.longValue());
163       deleteAllStatement.execute();
164     } finally {
165       if (deleteAllStatement != null) deleteAllStatement.close();
166     }
167   }
168 
169   /**
170    * Stores the given comment into the database.
171    *
172    * @param comments The enzyme's comment.
173    * @param enzymeId The enzyme ID.
174    * @param status   ...
175    * @param con      ...
176    * @throws SQLException
177    */
178   public void insert(List<EnzymeComment> comments, Long enzymeId, Status status, Connection con)
179           throws SQLException {
180     if (comments == null) throw new NullPointerException("Parameter 'comments' must not be null.");
181     if (enzymeId == null) throw new NullPointerException("Parameter 'enzymeId' must not be null.");
182     if (status == null) throw new NullPointerException("Parameter 'status' must not be null.");
183     if (con == null) throw new NullPointerException("Parameter 'con' must not be null.");
184 
185     PreparedStatement insertStatement = null;
186     try {
187       insertStatement = con.prepareStatement(insertStatement());
188       for (int iii = 0; iii < comments.size(); iii++) {
189         EnzymeComment enzymeComment = comments.get(iii);
190         doInsert(enzymeComment, enzymeId, (iii+1), status, insertStatement);
191         insertStatement.execute();
192       }
193     } finally {
194       if (insertStatement != null) insertStatement.close();
195     }
196   }
197 
198   public void update(EnzymeComment comment, Long enzymeId, Status status, int orderIn, Connection con) throws SQLException {
199     if (comment == null) throw new NullPointerException("Parameter 'comment' must not be null.");
200     if (enzymeId == null) throw new NullPointerException("Parameter 'enzymeId' must not be null.");
201     if (status == null) throw new NullPointerException("Parameter 'status' must not be null.");
202     if (orderIn < 1) throw new IllegalArgumentException("Parameter 'orderIn' must not be > 0.");
203     if (con == null) throw new NullPointerException("Parameter 'con' must not be null.");
204 
205     PreparedStatement updateStatement = null;
206     try {
207       updateStatement = con.prepareStatement(updateStatement());
208       doUpdate(comment, enzymeId, status, orderIn, updateStatement);
209       updateStatement.execute();
210     } finally {
211       if (updateStatement != null) updateStatement.close();
212     }
213   }
214 
215   // ------------------- PRIVATE METHODS ------------------------
216 
217   /**
218    * Creates the <code>EnzymeComment</code> object from the given result set.
219    *
220    * @param rs The result set object.
221    * @return an <code>EnzymeComment</code> instance.
222    * @throws SQLException
223    */
224   private EnzymeComment doLoad(ResultSet rs) throws SQLException {
225     assert rs != null : "Parameter 'rs' must not be null.";
226 
227     String comment = "";
228     String source = "";
229     String webView = "";
230     if (rs.getString("comment_text") != null) comment = rs.getString("comment_text");
231     if (rs.getString("source") != null) source = rs.getString("source");
232     if (rs.getString("web_view") != null) webView = rs.getString("web_view");
233 
234     EnzymeComment enzymeComment = new EnzymeComment(comment, EnzymeSourceConstant.valueOf(source),
235                                                     EnzymeViewConstant.valueOf(webView));
236 
237     return enzymeComment;
238   }
239 
240   /**
241    * Sets the parameters of the prepared statement.
242    *
243    * @param comment         ...
244    * @param enzymeId        ...
245    * @param status          ...
246    * @param insertStatement ...
247    * @throws SQLException
248    */
249   private void doInsert(EnzymeComment comment, Long enzymeId, int orderIn, Status status,
250                         PreparedStatement insertStatement)
251           throws SQLException {
252     assert comment != null : "Parameter 'comment' must not be null.";
253     assert enzymeId != null : "Parameter 'enzymeId' must not be null.";
254     assert status != null : "Parameter 'status' must not be null.";
255     assert insertStatement != null : "Parameter 'insertStatement' must not be null.";
256 
257     insertStatement.setLong(1, enzymeId.longValue());
258     insertStatement.setString(2, comment.getCommentText());
259     insertStatement.setInt(3, orderIn);
260     insertStatement.setString(4, status.getCode());
261     insertStatement.setString(5, comment.getSource().toString());
262     insertStatement.setString(6, comment.getView().toString());
263   }
264 
265   private void doUpdate(EnzymeComment comment, Long enzymeId, Status status, int orderIn,
266                         PreparedStatement updateStatement)
267           throws SQLException {
268     assert comment != null : "Parameter 'comment' must not be null.";
269     assert enzymeId != null : "Parameter 'enzymeId' must not be null.";
270     assert status != null : "Parameter 'status' must not be null.";
271     assert orderIn > 0 : "Parameter 'orderIn' must not be > 0.";
272     assert updateStatement != null : "Parameter 'insertStatement' must not be null.";
273 
274     updateStatement.setString(1, comment.getCommentText());
275     updateStatement.setInt(2, orderIn);
276     updateStatement.setString(3, status.getCode());
277     updateStatement.setString(4, comment.getSource().toString());
278     updateStatement.setString(5, comment.getView().toString());
279     updateStatement.setLong(6, enzymeId.longValue());
280   }
281 }