Coverage Report

Coverage Report - uk.ac.ebi.intenz.mapper.EnzymeCofactorMapper

Classes in this File

Line Coverage

Branch Coverage

Complexity

EnzymeCofactorMapper

30%

65/213

17%

27/152

 package uk.ac.ebi.intenz.mapper;
 
 import java.sql.Connection;
 import java.sql.PreparedStatement;
 import java.sql.ResultSet;
 import java.sql.SQLException;
 import java.sql.Statement;
 import java.sql.Types;
 import java.util.Collection;
 import java.util.Collections;
 import java.util.HashMap;
 import java.util.HashSet;
 import java.util.Hashtable;
 import java.util.Map;
 import java.util.Set;
 
 import org.apache.log4j.Logger;
 
 import uk.ac.ebi.biobabel.util.collections.OperatorSet;
 import uk.ac.ebi.intenz.domain.constants.EnzymeSourceConstant;
 import uk.ac.ebi.intenz.domain.constants.EnzymeViewConstant;
 import uk.ac.ebi.intenz.domain.constants.Status;
 import uk.ac.ebi.intenz.domain.enzyme.Cofactor;
 import uk.ac.ebi.rhea.domain.Compound;
 import uk.ac.ebi.rhea.domain.XRef.Availability;
 import uk.ac.ebi.rhea.mapper.MapperException;
 
 /**
  * Maps reaction information to the corresponding database tables.
  * <br>
  * <b>IMPORTANT</b>: after using instances of this class, call the
  * {@link #close()} method, otherwise the underlying Rhea mapper objects will
  * keep their statements open.
  * @author Michael Darsow
  * @version $Revision: 1.10 $ $Date: 2009/05/26 14:59:09 $
  */
 public class EnzymeCofactorMapper {
 
   private static final Logger LOGGER =
           Logger.getLogger(EnzymeCofactorMapper.class.getName());
 
   public EnzymeCofactorMapper(){
   }
   
   public void close(){
   }
 
           @Override
         protected void finalize() throws Throwable {
                 close();
         }
 
   private static final String COLUMNS =
           "enzyme_id, cofactor_text, order_in, source, status, web_view, compound_id, operator, op_grp";
 
   private static final String FIND_STM =
           "SELECT " + COLUMNS + " FROM cofactors WHERE enzyme_id = ? AND compound_id IS NOT NULL" +
           " ORDER BY order_in, op_grp ASC";
   
   private static final String FIND_BY_CHEBI_ID =
           "SELECT cd.compound_id, cd.name FROM compound_data cd"
           + " WHERE cd.accession = ?";
 
   private static final String FIND_SIB_COFACTORS_STM =
           "SELECT " + COLUMNS + " FROM cofactors" +
       " WHERE enzyme_id = ? AND compound_id IS NOT NULL AND (web_view = 'INTENZ' OR web_view LIKE '%SIB%')" +
       " ORDER BY order_in, op_grp ASC";
   
   private static final String FIND_ALL =
           "SELECT cf.compound_id, cf.enzyme_id, f_quad2string(e.ec1, e.ec2, e.ec3, e.ec4) ec" +
           " FROM cofactors cf, enzymes e WHERE cf.compound_id IS NOT NULL and cf.enzyme_id = e.enzyme_id" +
           " ORDER BY compound_id";
   
   private static final String LOAD_COMPOUND_STM =
           "SELECT accession, name, formula, charge, published"
           + " FROM compound_data WHERE compound_id = ?";
 
   private static final String INSERT_STM =
           "INSERT INTO cofactors (" + COLUMNS + ") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
 
   private static final String INSERT_COMPOUND_STM =
           "INSERT INTO compound_data"
           + " (compound_id, name, source, accession) VALUES"
           + " (s_compound_id.nextval, ?, 'CHEBI', ?)";
   
   private static final String DELETE_COMPOUND_STM =
           "DELETE FROM compound_data WHERE compound_id = ?";
   
   private static final String DELETE_ALL_STM =
           "DELETE cofactors WHERE enzyme_id = ?";
   
   private static final String UPDATE_STM =
           "UPDATE cofactors SET compound_id = ?, cofactor_text = ? WHERE compound_id = ?";
 
   /**
    * Tries to find cofactor information about an enzyme.
    *
    * @param enzymeId Enzyme ID of entry.
    * @param con      The logical connection.
    * @return a <code>Set</code> containing <code>Cofactor</code> instances,
    *         or <code>OperatorSet</code> instances filled with <code>Cofactor</code> objects,
    *         or <code>null</code> if nothing has been found.
    * @throws SQLException in case of a generic database problem.
  * @throws MapperException in case of problem retrieving the cofactors.
    */
   public Set<Object> find(Long enzymeId, Connection con)
   throws SQLException, MapperException {
     if (enzymeId == null) throw new NullPointerException("Parameter 'enzymeId' must not be null.");
     if (con == null) throw new NullPointerException("Parameter 'con' must not be null.");
 
     PreparedStatement findStatement = null;
     ResultSet rs = null;
     Set<Object> result = null;
 
     try {
       // Core information.
       findStatement = con.prepareStatement(FIND_STM);
       findStatement.setLong(1, enzymeId.longValue());
       rs = findStatement.executeQuery();
       result = getCofactors(rs);
     } finally {
             if (rs != null) rs.close();
       if (findStatement != null) findStatement.close();
     }
 
     if (result == null) {
       LOGGER.debug("No cofactor information found for the enzyme with ID "
               + enzymeId);
     }
     return result;
   }
   
   /**
    * Retrieves one cofactor from the IntEnz database by its ChEBI ID.
    * @param chebiId the ChEBI ID of the cofactor.
    * @param con a connection to IntEnz database.
    * @return a Compound, or <code>null</code> if it does not exist in IntEnz.
    * @throws SQLException in case of problem retrieving the cofactor.
    * @since 4.6.0
    */
   public Compound findByChebiId(String chebiId, Connection con)
   throws SQLException{
       Compound compound = null;
       PreparedStatement stm = null;
       ResultSet rs = null;
       try {
           stm = con.prepareStatement(FIND_BY_CHEBI_ID);
           stm.setString(1, chebiId);
           rs = stm.executeQuery();
           if (rs.next()){
               compound = new Compound(rs.getLong("compound_id"),
                       rs.getString("name"), "CHEBI", chebiId);
           }
       } finally {
           if (rs != null) rs.close();
           if (stm != null) stm.close();
       }
       return compound;
   }
 
   /**
    * Exports all cofactors which are displayed in the ENZYME view.
    *
    * Affected table rows will be locked.
    *
    * @param enzymeId The enzyme ID used to retreive the related cofactors.
    * @param con The database connection.
    * @return an {@link java.util.ArrayList} of cofactors or <code>null</code> if no cofactor could be found.
    * @throws SQLException in case of a generic database problem.
    * @throws MapperException in case of problem retrieving the cofactors.
    * @throws NullPointerException if either of the parameters is <code>null</code>.
    */
   public Set<Object> exportSibCofactors(Long enzymeId, Connection con)
   throws SQLException, MapperException {
     if (enzymeId == null) throw new NullPointerException("Parameter 'enzymeId' must not be null.");
     if (con == null) throw new NullPointerException("Parameter 'con' must not be null.");
 
     PreparedStatement findStatement = null;
     ResultSet rs = null;
     Set<Object> result = new HashSet<Object>();
 
     try {
       // Core information.
       findStatement = con.prepareStatement(FIND_SIB_COFACTORS_STM);
       findStatement.setLong(1, enzymeId.longValue());
       rs = findStatement.executeQuery();
       result = getCofactors(rs);
     } finally {
             if (rs != null) rs.close();
             if (findStatement != null) findStatement.close();
     }
 
     return result;
   }
   
   /**
    * Retrieves a list of compounds acting as cofactors in the database, along with
    * the identifiers for the enzymes they appear in.
    * @param con a database connection
    * @return a map of compounds (cofactors) to pairs enzyme ID/EC number.
    * @throws SQLException in case of a generic database problem.
    * @throws MapperException in case of problem retrieving compounds.
    * @throws MapperException
    */
   public Map<Compound, Map<Long, String>> findAll(Connection con)
   throws SQLException, MapperException {
           Map<Compound, Map<Long, String>> result = new Hashtable<Compound, Map<Long, String>>();
           Statement stm = null;
           ResultSet rs = null;
           try {
                   stm = con.createStatement();
                   rs = stm.executeQuery(FIND_ALL);
                   Long lastCompoundId = null;
                   Map<Long, String> lastMap = null;
                   while (rs.next()){
                           Long compoundId = rs.getLong("compound_id");
                           Long enzymeId = rs.getLong("enzyme_id");
                           String ec = rs.getString("ec");
                           if (!compoundId.equals(lastCompoundId)){
                                   // new in the result
                                   Compound compound = getCompound(con, compoundId);
                                   lastMap = new HashMap<Long, String>();
                                   result.put(compound, lastMap);
                           }
                           lastMap.put(enzymeId, ec);
                           lastCompoundId = compoundId;
                   }
           } finally {
                   if (rs != null) rs.close();
                   if (stm != null) stm.close();
           }
           return result;
   }
 
   /**
    * Stores the given list of cofactors into the database.
    *
    * @param cofactors The vector of cofactors.
    * @param enzymeId  The enzyme ID.
    * @param status
    * @param con       ...
    * @throws SQLException
    */
   public void insert(Collection<Object> cofactors, Long enzymeId, Status status, Connection con)
   throws SQLException {
           if (cofactors == null) throw new NullPointerException("Parameter 'cofactors' must not be null.");
           if (enzymeId == null) throw new NullPointerException("Parameter 'enzymeId' must not be null.");
           if (status == null) throw new NullPointerException("Parameter 'status' must not be null.");
           if (con == null) throw new NullPointerException("Parameter 'con' must not be null.");
 
           PreparedStatement insertStatement = null;
 
           try {
                   insertStatement = con.prepareStatement(INSERT_STM);
                   int iii = 1;
                   for (Object o : cofactors) {
                           if (o instanceof Cofactor){
                                   Cofactor cofactor = (Cofactor) o;
                                   doInsert(cofactor, enzymeId, (iii), status, null, null, insertStatement);
                                   insertStatement.execute();
                           } else if (o instanceof OperatorSet){
                                   OperatorSet os = (OperatorSet) o;
                                   doInsert(os, enzymeId, (iii), status, "0", 0, insertStatement);
                           }
                           iii++;
                   }
           } finally {
                   insertStatement.close();
           }
   }
 
   /**
    * Inserts one compound (cofactor) into the COMPOUND_DATA table.
    * @param compound a compound.
    * @param con a connection to the IntEnz database.
    * @throws SQLException in case of problem inserting the row.
    * @return the internal ID of the compound in the table.
    * @since 4.6.0
    */
   public Long insertCompound(Compound compound, Connection con)
   throws SQLException {
       Long compoundId = null;
       PreparedStatement stm = null;
       ResultSet rs = null;
       try {
           stm = con.prepareStatement(INSERT_COMPOUND_STM,
                   new String[]{ "compound_id" });
           stm.setString(1, compound.getXmlName());
           stm.setString(2, compound.getAccession());
           int result = stm.executeUpdate();
           if (result > 0){
               rs = stm.getGeneratedKeys();
               if (rs != null && rs.next()){
                   compoundId = rs.getLong(1);
                   compound.setId(compoundId);
               }
           }
           LOGGER.info("Inserted new cofactor: " + compound.getXmlName()
                   + " [" + compound.getAccession() + "]");
           return compoundId;
       } finally {
           if (rs != null) rs.close();
           if (stm != null) stm.close();
       }
   }
   
   /**
    * Deletes a cofactor from the COMPOUND_DATA table.
    * @param compoundId the internal ID of the compound.
    * @param con a connection to the IntEnz database.
    * @throws SQLException in case of problem with the database.
    * @since 4.6.0
    */
   public void deleteCompound(Long compoundId, Connection con)
   throws SQLException{
       PreparedStatement stm = null;
       try {
           stm = con.prepareStatement(DELETE_COMPOUND_STM);
           stm.setLong(1, compoundId);
           stm.execute();
           LOGGER.info("Deleted compound with internal ID " + compoundId);
       } finally {
           if (stm != null) stm.close();
       }
   }
   
   /**
    * Reloads the given list of cofactors into the database.
    *
    * @param cofactors The vector of cofactors.
    * @param enzymeId  The enzyme ID.
    * @param status
    * @param con       ...
    * @throws SQLException
    */
   public void reload(Collection<Object> cofactors, Long enzymeId, Status status, Connection con)
   throws SQLException {
     if (cofactors == null) throw new NullPointerException("Parameter 'cofactors' must not be null.");
     if (enzymeId == null) throw new NullPointerException("Parameter 'enzymeId' must not be null.");
     if (status == null) throw new NullPointerException("Parameter 'status' must not be null.");
     if (con == null) throw new NullPointerException("Parameter 'con' must not be null.");
 
     deleteAll(enzymeId, con);
     insert(cofactors, enzymeId, status, con);
   }
   
   /**
    * Updates the ChEBI mapping of a cofactor, and the name if needed too.
    * @param oldCf the old cofactor
    * @param newCf the new cofactor
    * @param con
    * @throws SQLException
    */
   public void update(Compound oldCf, Compound newCf, Connection con)
   throws SQLException{
           PreparedStatement stm = null;
           try {
                   stm = con.prepareStatement(UPDATE_STM);
                   stm.setLong(1, newCf.getId());
                   stm.setString(2, newCf.getName());
                   stm.setLong(3, oldCf.getId());
                   stm.execute();
           } finally {
                   if (stm != null) stm.close();
           }
   }
 
   /**
    * Deletes all cofactors related to one enzyme instance.
    *
    * @param enzymeId Enzyme ID of the enzyme instance.
    * @param con      ...
    * @throws SQLException
    */
   public void deleteAll(Long enzymeId, Connection con) throws SQLException {
     if (enzymeId == null) throw new NullPointerException("Parameter 'enzymeId' must not be null.");
     if (con == null) throw new NullPointerException("Parameter 'con' must not be null.");
 
     PreparedStatement deleteAllStatement = null;
 
     try {
       deleteAllStatement = con.prepareStatement(DELETE_ALL_STM);
       deleteAllStatement.setLong(1, enzymeId.longValue());
       deleteAllStatement.execute();
     } finally {
       deleteAllStatement.close();
     }
   }
 
 
   // ------------------- PRIVATE METHODS ------------------------
 
   /**
    * Creates the <code>Cofactor</code> object from the given result set.
    *
    * @param rs The result set object.
    * @return a <code>Cofactor</code> instance.
    * @throws SQLException if a generic database error occurs.
    */
   private Cofactor doLoad(ResultSet rs) throws SQLException {
     assert rs != null;
 
     String cofactorString = "";
     String source = "";
     String view = "";
     Long compoundId;
 
     if (rs.getString("cofactor_text") != null) cofactorString = rs.getString("cofactor_text");
     if (rs.getString("source") != null) source = rs.getString("source");
     if (rs.getString("web_view") != null) view = rs.getString("web_view");
     compoundId = rs.getLong("compound_id");
 
     Compound compound = null;
         if (compoundId == Compound.NO_ID_ASSIGNED){
                 compound = Compound.valueOf(compoundId, null, cofactorString, null,
                         null, null, Availability.N);
         } else {
             Connection connection = rs.getStatement().getConnection();
             compound = getCompound(connection, compoundId);
         }
     return Cofactor.valueOf(compound, EnzymeSourceConstant.valueOf(source), EnzymeViewConstant.valueOf(view));
   }
 
   /**
    * Retrieves a compound (cofactor) from the database.
    * @param connection
    * @param compoundId the internal compound ID.
    * @return a cofactor.
    * @throws SQLException
    */
     public Compound getCompound(Connection connection, Long compoundId)
     throws SQLException {
         Compound compound = null;
         PreparedStatement stm = null;
         ResultSet cofactorRs = null;
         try {
             stm = connection.prepareStatement(LOAD_COMPOUND_STM);
             stm.setLong(1, compoundId);
             cofactorRs = stm.executeQuery();
             if (cofactorRs.next()){
                 String accession = cofactorRs.getString("accession");
                 String name = cofactorRs.getString("name");
                 String formula = cofactorRs.getString("formula");
                 String charge = String.valueOf(cofactorRs.getInt("charge"));
                 String avail = cofactorRs.getString("published");
                 compound = Compound.valueOf(compoundId,
                         Long.valueOf(accession.replace("CHEBI:", "")),
                         name, formula, charge, accession,
                         avail == null? null : Availability.valueOf(avail));
             }
         } finally {
                 if (cofactorRs != null) cofactorRs.close();
                 if (stm != null) stm.close();
         }
         return compound;
     }
 
   @SuppressWarnings("unchecked")
   private Set<Object> getCofactors(ResultSet rs)
   throws SQLException, MapperException {
           Set result = null;
           int currentOrderIn = 0;
           OperatorSet.Builder osBuilder = null;
       while (rs.next()) {
               if (result == null) result = Collections.synchronizedSet(new HashSet());
               int orderIn = rs.getInt("order_in");
               if (orderIn > currentOrderIn){
                       if (osBuilder != null && !osBuilder.isEmpty()){
                               result.add(osBuilder.getOperatorSet());
                               osBuilder.clear();
                       }
                       currentOrderIn = orderIn;
               }
               Cofactor cofactor = doLoad(rs);
               if (rs.getString("op_grp") == null){
                       result.add(cofactor);
               } else {
                       if (osBuilder == null) osBuilder = new OperatorSet.Builder();
                       osBuilder.add(rs.getString("op_grp"), rs.getString("operator"), cofactor);
               }
       }
           if (osBuilder != null && !osBuilder.isEmpty()) result.add(osBuilder.getOperatorSet());
           return result;
   }
 
   private int doInsert(OperatorSet cofactors, Long enzymeId, int orderIn, Status status,
                   String operatorGroup, int currentGroup, PreparedStatement insertStatement)
   throws SQLException{
           for (Object o : cofactors) {
                   if (o instanceof Cofactor){
                           Cofactor cofactor = (Cofactor) o;
                           doInsert(cofactor, enzymeId, orderIn, status, cofactors.getOperator(),
                                           operatorGroup, insertStatement);
                           insertStatement.execute();
                   } else {
                           currentGroup++;
                           OperatorSet os = (OperatorSet) o;
                           currentGroup = doInsert(os, enzymeId, orderIn, status,
                                           operatorGroup+String.valueOf(currentGroup), currentGroup, insertStatement);
                   }
           }
           return currentGroup;
   }
 
   /**
    * Sets the parameters of the insert statement.
    *
    * @param cofactor        ...
    * @param enzymeId        ...
    * @param orderIn         ...
    * @param insertStatement ...
    * @throws java.sql.SQLException
    */
   private void doInsert(Cofactor cofactor, Long enzymeId, int orderIn, Status status,
                         String operator, String operatorGroup, PreparedStatement insertStatement)
           throws SQLException {
     assert cofactor != null : "Parameter 'cofactor' must not be null.";
     assert enzymeId != null : "Parameter 'enzymeId' must not be null.";
     assert orderIn > 0 : "Parameter 'orderIn' must be > 0.";
     assert status != null : "Parameter 'status' must not be null.";
     assert insertStatement != null : "Parameter 'insertStatement' must not be null.";
     assert !(operator == null ^ operatorGroup == null) : "No operator w/o operator group and viceversa";
 
     insertStatement.setLong(1, enzymeId.longValue());
     insertStatement.setString(2, cofactor.getCompound().getXmlName());
     insertStatement.setInt(3, orderIn);
     insertStatement.setString(4, cofactor.getSource().toString());
     insertStatement.setString(5, status.getCode());
     insertStatement.setString(6, cofactor.getView().toString());
     if (Compound.NO_ID_ASSIGNED.equals(cofactor.getCompound().getId())){
         insertStatement.setLong(7, insertCompound(
                 cofactor.getCompound(), insertStatement.getConnection()));
     } else {
         insertStatement.setLong(7, cofactor.getCompound().getId());
     }
     if (operatorGroup == null){
             insertStatement.setNull(8, Types.VARCHAR);
             insertStatement.setNull(9, Types.VARCHAR);
     } else {
             insertStatement.setString(8, operator);
             insertStatement.setString(9, operatorGroup);
     }
   }
 }

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.sql.Statement;
8		import java.sql.Types;
9		import java.util.Collection;
10		import java.util.Collections;
11		import java.util.HashMap;
12		import java.util.HashSet;
13		import java.util.Hashtable;
14		import java.util.Map;
15		import java.util.Set;
16
17		import org.apache.log4j.Logger;
18
19		import uk.ac.ebi.biobabel.util.collections.OperatorSet;
20		import uk.ac.ebi.intenz.domain.constants.EnzymeSourceConstant;
21		import uk.ac.ebi.intenz.domain.constants.EnzymeViewConstant;
22		import uk.ac.ebi.intenz.domain.constants.Status;
23		import uk.ac.ebi.intenz.domain.enzyme.Cofactor;
24		import uk.ac.ebi.rhea.domain.Compound;
25		import uk.ac.ebi.rhea.domain.XRef.Availability;
26		import uk.ac.ebi.rhea.mapper.MapperException;
27
28		/**
29		* Maps reaction information to the corresponding database tables.
30		* <br>
31		* <b>IMPORTANT</b>: after using instances of this class, call the
32		* {@link #close()} method, otherwise the underlying Rhea mapper objects will
33		* keep their statements open.
34		* @author Michael Darsow
35		* @version $Revision: 1.10 $ $Date: 2009/05/26 14:59:09 $
36		*/
37	1	public class EnzymeCofactorMapper {
38
39	1	private static final Logger LOGGER =
40		Logger.getLogger(EnzymeCofactorMapper.class.getName());
41
42	1	public EnzymeCofactorMapper(){
43	1	}
44
45		public void close(){
46	2	}
47
48		@Override
49		protected void finalize() throws Throwable {
50	1	close();
51	1	}
52
53		private static final String COLUMNS =
54		"enzyme_id, cofactor_text, order_in, source, status, web_view, compound_id, operator, op_grp";
55
56		private static final String FIND_STM =
57		"SELECT " + COLUMNS + " FROM cofactors WHERE enzyme_id = ? AND compound_id IS NOT NULL" +
58		" ORDER BY order_in, op_grp ASC";
59
60		private static final String FIND_BY_CHEBI_ID =
61		"SELECT cd.compound_id, cd.name FROM compound_data cd"
62		+ " WHERE cd.accession = ?";
63
64		private static final String FIND_SIB_COFACTORS_STM =
65		"SELECT " + COLUMNS + " FROM cofactors" +
66		" WHERE enzyme_id = ? AND compound_id IS NOT NULL AND (web_view = 'INTENZ' OR web_view LIKE '%SIB%')" +
67		" ORDER BY order_in, op_grp ASC";
68
69		private static final String FIND_ALL =
70		"SELECT cf.compound_id, cf.enzyme_id, f_quad2string(e.ec1, e.ec2, e.ec3, e.ec4) ec" +
71		" FROM cofactors cf, enzymes e WHERE cf.compound_id IS NOT NULL and cf.enzyme_id = e.enzyme_id" +
72		" ORDER BY compound_id";
73
74		private static final String LOAD_COMPOUND_STM =
75		"SELECT accession, name, formula, charge, published"
76		+ " FROM compound_data WHERE compound_id = ?";
77
78		private static final String INSERT_STM =
79		"INSERT INTO cofactors (" + COLUMNS + ") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
80
81		private static final String INSERT_COMPOUND_STM =
82		"INSERT INTO compound_data"
83		+ " (compound_id, name, source, accession) VALUES"
84		+ " (s_compound_id.nextval, ?, 'CHEBI', ?)";
85
86		private static final String DELETE_COMPOUND_STM =
87		"DELETE FROM compound_data WHERE compound_id = ?";
88
89		private static final String DELETE_ALL_STM =
90		"DELETE cofactors WHERE enzyme_id = ?";
91
92		private static final String UPDATE_STM =
93		"UPDATE cofactors SET compound_id = ?, cofactor_text = ? WHERE compound_id = ?";
94
95		/**
96		* Tries to find cofactor information about an enzyme.
97		*
98		* @param enzymeId Enzyme ID of entry.
99		* @param con The logical connection.
100		* @return a <code>Set</code> containing <code>Cofactor</code> instances,
101		* or <code>OperatorSet</code> instances filled with <code>Cofactor</code> objects,
102		* or <code>null</code> if nothing has been found.
103		* @throws SQLException in case of a generic database problem.
104		* @throws MapperException in case of problem retrieving the cofactors.
105		*/
106		public Set<Object> find(Long enzymeId, Connection con)
107		throws SQLException, MapperException {
108	1	if (enzymeId == null) throw new NullPointerException("Parameter 'enzymeId' must not be null.");
109	1	if (con == null) throw new NullPointerException("Parameter 'con' must not be null.");
110
111	1	PreparedStatement findStatement = null;
112	1	ResultSet rs = null;
113	1	Set<Object> result = null;
114
115		try {
116		// Core information.
117	1	findStatement = con.prepareStatement(FIND_STM);
118	1	findStatement.setLong(1, enzymeId.longValue());
119	1	rs = findStatement.executeQuery();
120	1	result = getCofactors(rs);
121		} finally {
122	1	if (rs != null) rs.close();
123	1	if (findStatement != null) findStatement.close();
124		}
125
126	1	if (result == null) {
127	0	LOGGER.debug("No cofactor information found for the enzyme with ID "
128		+ enzymeId);
129		}
130	1	return result;
131		}
132
133		/**
134		* Retrieves one cofactor from the IntEnz database by its ChEBI ID.
135		* @param chebiId the ChEBI ID of the cofactor.
136		* @param con a connection to IntEnz database.
137		* @return a Compound, or <code>null</code> if it does not exist in IntEnz.
138		* @throws SQLException in case of problem retrieving the cofactor.
139		* @since 4.6.0
140		*/
141		public Compound findByChebiId(String chebiId, Connection con)
142		throws SQLException{
143	0	Compound compound = null;
144	0	PreparedStatement stm = null;
145	0	ResultSet rs = null;
146		try {
147	0	stm = con.prepareStatement(FIND_BY_CHEBI_ID);
148	0	stm.setString(1, chebiId);
149	0	rs = stm.executeQuery();
150	0	if (rs.next()){
151	0	compound = new Compound(rs.getLong("compound_id"),
152		rs.getString("name"), "CHEBI", chebiId);
153		}
154		} finally {
155	0	if (rs != null) rs.close();
156	0	if (stm != null) stm.close();
157		}
158	0	return compound;
159		}
160
161		/**
162		* Exports all cofactors which are displayed in the ENZYME view.
163		*
164		* Affected table rows will be locked.
165		*
166		* @param enzymeId The enzyme ID used to retreive the related cofactors.
167		* @param con The database connection.
168		* @return an {@link java.util.ArrayList} of cofactors or <code>null</code> if no cofactor could be found.
169		* @throws SQLException in case of a generic database problem.
170		* @throws MapperException in case of problem retrieving the cofactors.
171		* @throws NullPointerException if either of the parameters is <code>null</code>.
172		*/
173		public Set<Object> exportSibCofactors(Long enzymeId, Connection con)
174		throws SQLException, MapperException {
175	0	if (enzymeId == null) throw new NullPointerException("Parameter 'enzymeId' must not be null.");
176	0	if (con == null) throw new NullPointerException("Parameter 'con' must not be null.");
177
178	0	PreparedStatement findStatement = null;
179	0	ResultSet rs = null;
180	0	Set<Object> result = new HashSet<Object>();
181
182		try {
183		// Core information.
184	0	findStatement = con.prepareStatement(FIND_SIB_COFACTORS_STM);
185	0	findStatement.setLong(1, enzymeId.longValue());
186	0	rs = findStatement.executeQuery();
187	0	result = getCofactors(rs);
188		} finally {
189	0	if (rs != null) rs.close();
190	0	if (findStatement != null) findStatement.close();
191		}
192
193	0	return result;
194		}
195
196		/**
197		* Retrieves a list of compounds acting as cofactors in the database, along with
198		* the identifiers for the enzymes they appear in.
199		* @param con a database connection
200		* @return a map of compounds (cofactors) to pairs enzyme ID/EC number.
201		* @throws SQLException in case of a generic database problem.
202		* @throws MapperException in case of problem retrieving compounds.
203		* @throws MapperException
204		*/
205		public Map<Compound, Map<Long, String>> findAll(Connection con)
206		throws SQLException, MapperException {
207	0	Map<Compound, Map<Long, String>> result = new Hashtable<Compound, Map<Long, String>>();
208	0	Statement stm = null;
209	0	ResultSet rs = null;
210		try {
211	0	stm = con.createStatement();
212	0	rs = stm.executeQuery(FIND_ALL);
213	0	Long lastCompoundId = null;
214	0	Map<Long, String> lastMap = null;
215	0	while (rs.next()){
216	0	Long compoundId = rs.getLong("compound_id");
217	0	Long enzymeId = rs.getLong("enzyme_id");
218	0	String ec = rs.getString("ec");
219	0	if (!compoundId.equals(lastCompoundId)){
220		// new in the result
221	0	Compound compound = getCompound(con, compoundId);
222	0	lastMap = new HashMap<Long, String>();
223	0	result.put(compound, lastMap);
224		}
225	0	lastMap.put(enzymeId, ec);
226	0	lastCompoundId = compoundId;
227	0	}
228		} finally {
229	0	if (rs != null) rs.close();
230	0	if (stm != null) stm.close();
231		}
232	0	return result;
233		}
234
235		/**
236		* Stores the given list of cofactors into the database.
237		*
238		* @param cofactors The vector of cofactors.
239		* @param enzymeId The enzyme ID.
240		* @param status
241		* @param con ...
242		* @throws SQLException
243		*/
244		public void insert(Collection<Object> cofactors, Long enzymeId, Status status, Connection con)
245		throws SQLException {
246	0	if (cofactors == null) throw new NullPointerException("Parameter 'cofactors' must not be null.");
247	0	if (enzymeId == null) throw new NullPointerException("Parameter 'enzymeId' must not be null.");
248	0	if (status == null) throw new NullPointerException("Parameter 'status' must not be null.");
249	0	if (con == null) throw new NullPointerException("Parameter 'con' must not be null.");
250
251	0	PreparedStatement insertStatement = null;
252
253		try {
254	0	insertStatement = con.prepareStatement(INSERT_STM);
255	0	int iii = 1;
256	0	for (Object o : cofactors) {
257	0	if (o instanceof Cofactor){
258	0	Cofactor cofactor = (Cofactor) o;
259	0	doInsert(cofactor, enzymeId, (iii), status, null, null, insertStatement);
260	0	insertStatement.execute();
261	0	} else if (o instanceof OperatorSet){
262	0	OperatorSet os = (OperatorSet) o;
263	0	doInsert(os, enzymeId, (iii), status, "0", 0, insertStatement);
264		}
265	0	iii++;
266	0	}
267		} finally {
268	0	insertStatement.close();
269	0	}
270	0	}
271
272		/**
273		* Inserts one compound (cofactor) into the COMPOUND_DATA table.
274		* @param compound a compound.
275		* @param con a connection to the IntEnz database.
276		* @throws SQLException in case of problem inserting the row.
277		* @return the internal ID of the compound in the table.
278		* @since 4.6.0
279		*/
280		public Long insertCompound(Compound compound, Connection con)
281		throws SQLException {
282	0	Long compoundId = null;
283	0	PreparedStatement stm = null;
284	0	ResultSet rs = null;
285		try {
286	0	stm = con.prepareStatement(INSERT_COMPOUND_STM,
287		new String[]{ "compound_id" });
288	0	stm.setString(1, compound.getXmlName());
289	0	stm.setString(2, compound.getAccession());
290	0	int result = stm.executeUpdate();
291	0	if (result > 0){
292	0	rs = stm.getGeneratedKeys();
293	0	if (rs != null && rs.next()){
294	0	compoundId = rs.getLong(1);
295	0	compound.setId(compoundId);
296		}
297		}
298	0	LOGGER.info("Inserted new cofactor: " + compound.getXmlName()
299		+ " [" + compound.getAccession() + "]");
300	0	return compoundId;
301		} finally {
302	0	if (rs != null) rs.close();
303	0	if (stm != null) stm.close();
304		}
305		}
306
307		/**
308		* Deletes a cofactor from the COMPOUND_DATA table.
309		* @param compoundId the internal ID of the compound.
310		* @param con a connection to the IntEnz database.
311		* @throws SQLException in case of problem with the database.
312		* @since 4.6.0
313		*/
314		public void deleteCompound(Long compoundId, Connection con)
315		throws SQLException{
316	0	PreparedStatement stm = null;
317		try {
318	0	stm = con.prepareStatement(DELETE_COMPOUND_STM);
319	0	stm.setLong(1, compoundId);
320	0	stm.execute();
321	0	LOGGER.info("Deleted compound with internal ID " + compoundId);
322		} finally {
323	0	if (stm != null) stm.close();
324		}
325	0	}
326
327		/**
328		* Reloads the given list of cofactors into the database.
329		*
330		* @param cofactors The vector of cofactors.
331		* @param enzymeId The enzyme ID.
332		* @param status
333		* @param con ...
334		* @throws SQLException
335		*/
336		public void reload(Collection<Object> cofactors, Long enzymeId, Status status, Connection con)
337		throws SQLException {
338	0	if (cofactors == null) throw new NullPointerException("Parameter 'cofactors' must not be null.");
339	0	if (enzymeId == null) throw new NullPointerException("Parameter 'enzymeId' must not be null.");
340	0	if (status == null) throw new NullPointerException("Parameter 'status' must not be null.");
341	0	if (con == null) throw new NullPointerException("Parameter 'con' must not be null.");
342
343	0	deleteAll(enzymeId, con);
344	0	insert(cofactors, enzymeId, status, con);
345	0	}
346
347		/**
348		* Updates the ChEBI mapping of a cofactor, and the name if needed too.
349		* @param oldCf the old cofactor
350		* @param newCf the new cofactor
351		* @param con
352		* @throws SQLException
353		*/
354		public void update(Compound oldCf, Compound newCf, Connection con)
355		throws SQLException{
356	0	PreparedStatement stm = null;
357		try {
358	0	stm = con.prepareStatement(UPDATE_STM);
359	0	stm.setLong(1, newCf.getId());
360	0	stm.setString(2, newCf.getName());
361	0	stm.setLong(3, oldCf.getId());
362	0	stm.execute();
363		} finally {
364	0	if (stm != null) stm.close();
365		}
366	0	}
367
368		/**
369		* Deletes all cofactors related to one enzyme instance.
370		*
371		* @param enzymeId Enzyme ID of the enzyme instance.
372		* @param con ...
373		* @throws SQLException
374		*/
375		public void deleteAll(Long enzymeId, Connection con) throws SQLException {
376	0	if (enzymeId == null) throw new NullPointerException("Parameter 'enzymeId' must not be null.");
377	0	if (con == null) throw new NullPointerException("Parameter 'con' must not be null.");
378
379	0	PreparedStatement deleteAllStatement = null;
380
381		try {
382	0	deleteAllStatement = con.prepareStatement(DELETE_ALL_STM);
383	0	deleteAllStatement.setLong(1, enzymeId.longValue());
384	0	deleteAllStatement.execute();
385		} finally {
386	0	deleteAllStatement.close();
387	0	}
388	0	}
389
390
391		// ------------------- PRIVATE METHODS ------------------------
392
393		/**
394		* Creates the <code>Cofactor</code> object from the given result set.
395		*
396		* @param rs The result set object.
397		* @return a <code>Cofactor</code> instance.
398		* @throws SQLException if a generic database error occurs.
399		*/
400		private Cofactor doLoad(ResultSet rs) throws SQLException {
401	2	assert rs != null;
402
403	2	String cofactorString = "";
404	2	String source = "";
405	2	String view = "";
406		Long compoundId;
407
408	2	if (rs.getString("cofactor_text") != null) cofactorString = rs.getString("cofactor_text");
409	2	if (rs.getString("source") != null) source = rs.getString("source");
410	2	if (rs.getString("web_view") != null) view = rs.getString("web_view");
411	2	compoundId = rs.getLong("compound_id");
412
413	2	Compound compound = null;
414	2	if (compoundId == Compound.NO_ID_ASSIGNED){
415	0	compound = Compound.valueOf(compoundId, null, cofactorString, null,
416		null, null, Availability.N);
417		} else {
418	2	Connection connection = rs.getStatement().getConnection();
419	2	compound = getCompound(connection, compoundId);
420		}
421	2	return Cofactor.valueOf(compound, EnzymeSourceConstant.valueOf(source), EnzymeViewConstant.valueOf(view));
422		}
423
424		/**
425		* Retrieves a compound (cofactor) from the database.
426		* @param connection
427		* @param compoundId the internal compound ID.
428		* @return a cofactor.
429		* @throws SQLException
430		*/
431		public Compound getCompound(Connection connection, Long compoundId)
432		throws SQLException {
433	2	Compound compound = null;
434	2	PreparedStatement stm = null;
435	2	ResultSet cofactorRs = null;
436		try {
437	2	stm = connection.prepareStatement(LOAD_COMPOUND_STM);
438	2	stm.setLong(1, compoundId);
439	2	cofactorRs = stm.executeQuery();
440	2	if (cofactorRs.next()){
441	2	String accession = cofactorRs.getString("accession");
442	2	String name = cofactorRs.getString("name");
443	2	String formula = cofactorRs.getString("formula");
444	2	String charge = String.valueOf(cofactorRs.getInt("charge"));
445	2	String avail = cofactorRs.getString("published");
446	2	compound = Compound.valueOf(compoundId,
447		Long.valueOf(accession.replace("CHEBI:", "")),
448		name, formula, charge, accession,
449		avail == null? null : Availability.valueOf(avail));
450		}
451		} finally {
452	2	if (cofactorRs != null) cofactorRs.close();
453	2	if (stm != null) stm.close();
454		}
455	2	return compound;
456		}
457
458		@SuppressWarnings("unchecked")
459		private Set<Object> getCofactors(ResultSet rs)
460		throws SQLException, MapperException {
461	1	Set result = null;
462	1	int currentOrderIn = 0;
463	1	OperatorSet.Builder osBuilder = null;
464	3	while (rs.next()) {
465	2	if (result == null) result = Collections.synchronizedSet(new HashSet());
466	2	int orderIn = rs.getInt("order_in");
467	2	if (orderIn > currentOrderIn){
468	1	if (osBuilder != null && !osBuilder.isEmpty()){
469	0	result.add(osBuilder.getOperatorSet());
470	0	osBuilder.clear();
471		}
472	1	currentOrderIn = orderIn;
473		}
474	2	Cofactor cofactor = doLoad(rs);
475	2	if (rs.getString("op_grp") == null){
476	0	result.add(cofactor);
477		} else {
478	2	if (osBuilder == null) osBuilder = new OperatorSet.Builder();
479	2	osBuilder.add(rs.getString("op_grp"), rs.getString("operator"), cofactor);
480		}
481	2	}
482	1	if (osBuilder != null && !osBuilder.isEmpty()) result.add(osBuilder.getOperatorSet());
483	1	return result;
484		}
485
486		private int doInsert(OperatorSet cofactors, Long enzymeId, int orderIn, Status status,
487		String operatorGroup, int currentGroup, PreparedStatement insertStatement)
488		throws SQLException{
489	0	for (Object o : cofactors) {
490	0	if (o instanceof Cofactor){
491	0	Cofactor cofactor = (Cofactor) o;
492	0	doInsert(cofactor, enzymeId, orderIn, status, cofactors.getOperator(),
493		operatorGroup, insertStatement);
494	0	insertStatement.execute();
495	0	} else {
496	0	currentGroup++;
497	0	OperatorSet os = (OperatorSet) o;
498	0	currentGroup = doInsert(os, enzymeId, orderIn, status,
499		operatorGroup+String.valueOf(currentGroup), currentGroup, insertStatement);
500		}
501	0	}
502	0	return currentGroup;
503		}
504
505		/**
506		* Sets the parameters of the insert statement.
507		*
508		* @param cofactor ...
509		* @param enzymeId ...
510		* @param orderIn ...
511		* @param insertStatement ...
512		* @throws java.sql.SQLException
513		*/
514		private void doInsert(Cofactor cofactor, Long enzymeId, int orderIn, Status status,
515		String operator, String operatorGroup, PreparedStatement insertStatement)
516		throws SQLException {
517	0	assert cofactor != null : "Parameter 'cofactor' must not be null.";
518	0	assert enzymeId != null : "Parameter 'enzymeId' must not be null.";
519	0	assert orderIn > 0 : "Parameter 'orderIn' must be > 0.";
520	0	assert status != null : "Parameter 'status' must not be null.";
521	0	assert insertStatement != null : "Parameter 'insertStatement' must not be null.";
522	0	assert !(operator == null ^ operatorGroup == null) : "No operator w/o operator group and viceversa";
523
524	0	insertStatement.setLong(1, enzymeId.longValue());
525	0	insertStatement.setString(2, cofactor.getCompound().getXmlName());
526	0	insertStatement.setInt(3, orderIn);
527	0	insertStatement.setString(4, cofactor.getSource().toString());
528	0	insertStatement.setString(5, status.getCode());
529	0	insertStatement.setString(6, cofactor.getView().toString());
530	0	if (Compound.NO_ID_ASSIGNED.equals(cofactor.getCompound().getId())){
531	0	insertStatement.setLong(7, insertCompound(
532		cofactor.getCompound(), insertStatement.getConnection()));
533		} else {
534	0	insertStatement.setLong(7, cofactor.getCompound().getId());
535		}
536	0	if (operatorGroup == null){
537	0	insertStatement.setNull(8, Types.VARCHAR);
538	0	insertStatement.setNull(9, Types.VARCHAR);
539		} else {
540	0	insertStatement.setString(8, operator);
541	0	insertStatement.setString(9, operatorGroup);
542		}
543	0	}
544		}