package org.genemania.dw.db;

import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.TreeMap;
import org.genemania.dw.entity.EnsemblGene;
import org.genemania.dw.entity.EntrezGene;
import org.genemania.dw.entity.ExtResource;
import org.genemania.dw.entity.ExtResourceGene;
import org.genemania.dw.util.DWUtil;
import org.genemania.dw.util.DefParams;
import org.genemania.dw.util.GenUtil;

/**
 * The interface with the local mirror of the Ensembl core database. It 
 * encapsulates all the queries from that database. 
 * Note that the Ensembl tables are not mapped to DW entity objects, since those
 * tables represent an external resource with a different data model than DW.
 * The method param loadEntrezXref allows some flexibility in loading, if
 * needed.
 * Using External_DB_* IDs to avoid an extra join.
 * 
 * @author rashadbadrawi
 */

public class EnsemblMirrorTables {

    //tables of interest
    //private static final String EXTERNAL_DB_TABLE = "external_db";
    private static final String EXTERNAL_SYN_TABLE = "external_synonym";
    private static final String GENE_TABLE = "gene";
    private static final String GENE_STABLE_ID_TABLE = "gene_stable_id";
    private static final String OBJECT_XREF_TABLE = "object_xref";
    private static final String TRANSCRIPT_TABLE = "transcript";
    private static final String TRANSCRIPT_STABLE_ID_TABLE = "transcript_stable_id";
    private static final String TRANSLATION_TABLE = "translation";
    private static final String TRANSLATION_STABLE_ID_TABLE = "translation_stable_id";
    private static final String XREF_TABLE = "xref";
            
    //table columns of interest
    private static final String EXTERNAL_DB_ID_COL = "external_db_id"; //shared
    //private static final String EXTERNAL_DB_NAME_COL = "db_name";
    //external_synonym table
    private static final String SYN_COL = "synonym";
    //gene table
    private static final String GENE_ID_COL = "gene_id";     //shared
    private static final String BIOTYPE_COL = "biotype";
    private static final String DISPLAY_XREF_ID_COL = "display_xref_id";
    private static final String DESC_COL = "description";
    //gene_stable_id table
    private static final String STABLE_ID_COL = "stable_id";        //shared
    //object_xref table 
    private static final String ENS_ID_COL = "ensembl_id";
    private static final String ENS_OBJECT_TYPE_COL = "ensembl_object_type";
    //transcript table
    private static final String TRANSCRIPT_ID_COL = "transcript_id";  //shared
    //translation table
    private static final String TRANSLATION_ID_COL = "translation_id"; //shared
    //xref table
    private static final String XREF_ID_COL = "xref_id";     //shared
    private static final String DBPRIMARY_ACC_ID_COL = "dbprimary_acc";
    private static final String DISPLAY_LABEL_COL = "display_label";
    
    private static String XREF_TYPE_TRANSLATE = "Translation";
    private static String XREF_TYPE_TRANSCRIPT = "Transcript";
    private static String XREF_TYPE_GENE = "Gene";
    private static int EXTERNAL_DB_ENTREZ = 1300;
    private static int EXTERNAL_DB_UNIPROT_SWISS = 2200;
    private static int EXTERNAL_DB_MGI_SYMBOL = 1400;
    private static int EXTERNAL_DB_MGI_GENE_CURATED = 12550;
    private static int EXTERNAL_DB_MGI_GENE_AUTO = 12555;

    private static PrintWriter log = GenUtil.getDefaultLog();
    private static String DBName;          //handy for current schema name
    
    private EnsemblMirrorTables () {}        //no instances allowed
        
    public static TreeMap <String, ExtResource> loadAll (String speciesName,
            boolean loadEntrezXRef) throws SQLException {
        
        TreeMap <String, ExtResource> ensGeneMap = new TreeMap <String, 
                ExtResource> ();
        setDBName (speciesName);
        System.out.println ("loading all Ensembl genes...");
            String loadQuery = "select " + 
            DBName + "." + GENE_STABLE_ID_TABLE + "." + STABLE_ID_COL + ", " +
            DBName + "." + GENE_TABLE + "." + GENE_ID_COL + ", " + 
            DBName + "." + GENE_TABLE + "." + BIOTYPE_COL + ", " + 
            DBName + "." + GENE_TABLE + "." + DESC_COL + ", " +
            DBName + "." + XREF_TABLE + "." + XREF_ID_COL + ", " +
            DBName + "." + XREF_TABLE + "." + DISPLAY_LABEL_COL +
            " from " + DBName + "." + GENE_TABLE + 
                       " inner join " +
                       DBName + "." + GENE_STABLE_ID_TABLE + " on " +
                       DBName + "." + GENE_TABLE + "." + GENE_ID_COL + " = " +
                       DBName + "." + GENE_STABLE_ID_TABLE + "." + GENE_ID_COL +
                       " left outer join " +
                       DBName + "." + XREF_TABLE + " on " +
                       DBName + "." + GENE_TABLE + "." + DISPLAY_XREF_ID_COL + " = " +
                       DBName + "." + XREF_TABLE + "." + XREF_ID_COL +
            " order by " + 
            DBName + "." + GENE_STABLE_ID_TABLE + "." + GENE_ID_COL;
        log.println (DBUtil.LOAD + ": " + loadQuery);

        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            con = DBUtil.getConnection ();
            ps = con.prepareStatement (loadQuery);
            rs = ps.executeQuery();
            int cnt = 0;
            EnsemblGene ensGene = null;
            while (rs.next ()) {
                ensGene = new EnsemblGene ();
                ensGene.setID(rs.getString(STABLE_ID_COL));
                ensGene.setType(rs.getString (BIOTYPE_COL));
                if (rs.getString (DISPLAY_LABEL_COL) != null &&
                    rs.getString (DISPLAY_LABEL_COL).length() > 0) {
                    ensGene.setName(rs.getString (DISPLAY_LABEL_COL));
                }
                if (rs.getString (DESC_COL) != null &&
                    rs.getString (DESC_COL).length() > 0) {
                    ensGene.setDefinition(rs.getString (DESC_COL));
                }
                ensGene.setSpeciesName(speciesName);
                loadTranscripts (ensGene, rs.getString (GENE_ID_COL));
                int xRefID = rs.getInt(XREF_ID_COL);
                ArrayList <String> synList = getSynsByXref (xRefID);
                for (int i = 0; i < synList.size (); i++) {
                    ensGene.addSyn (synList.get (i));
                }
                ensGeneMap.put (ensGene.getID(), ensGene);
                cnt++;
            }
            System.out.println ("Loaded: " + cnt + " Ensembl entries.");
        } catch (SQLException e) {
            throw e;
        } finally {
            if (ps != null) {
                ps.close ();
            }
            if (con != null && !con.isClosed ()) {
                con.close ();
            }
        }
        if (loadEntrezXRef) {
            matchXRef(ensGeneMap, 
                   new int [] {EXTERNAL_DB_ENTREZ, EXTERNAL_DB_UNIPROT_SWISS});
        } else {
            matchAllUniprot (ensGeneMap);
        }
        loadSpeciesSpecificIDs (speciesName, ensGeneMap);

        return ensGeneMap;
    }
    
    private static void loadTranscripts (EnsemblGene ensGene, String DBID) throws 
            SQLException {
        
        String loadQuery = "select " + 
            DBName + "." + TRANSCRIPT_TABLE + "." + TRANSCRIPT_ID_COL + ", " +
            DBName + "." + TRANSCRIPT_STABLE_ID_TABLE + "." + STABLE_ID_COL +
            " from " + DBName + "." + TRANSCRIPT_TABLE + ", " + 
                       DBName + "." + TRANSCRIPT_STABLE_ID_TABLE + 
            " where " +
            DBName + "." + TRANSCRIPT_TABLE + "." + TRANSCRIPT_ID_COL + " = " +
            DBName + "." + TRANSCRIPT_STABLE_ID_TABLE + "." + TRANSCRIPT_ID_COL 
            + " and  " + 
            DBName + "." + TRANSCRIPT_TABLE + "." + GENE_ID_COL + " = ?";
        //log.println (DBUtil.LOAD + ": " + loadQuery);

        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            con = DBUtil.getConnection ();
            ps = con.prepareStatement (loadQuery);
            ps.setString(1, DBID);
            rs = ps.executeQuery();
            int cnt = 0;
            while (rs.next ()) {  //transcriptID variable is the stable ID.
                String transcriptID = rs.getString (STABLE_ID_COL);
                ensGene.addTranscriptID(transcriptID);
                loadProteins (ensGene, rs.getString(TRANSCRIPT_ID_COL));
                cnt++;
            }
            //System.out.println ("Loaded: " + cnt + " transcripts for "
            //+ ensGene.getID());
        } catch (SQLException e) {
            throw e;
        } finally {
            if (ps != null) {
                ps.close ();
            }
        }    
    }
    
    private static void loadProteins (EnsemblGene ensGene, String DBID) 
            throws SQLException {
        
        String loadQuery = "select " + 
            DBName + "." + TRANSLATION_TABLE + "." + TRANSLATION_ID_COL + ", " +
            DBName + "." + TRANSLATION_STABLE_ID_TABLE + "." + STABLE_ID_COL +
            " from " + DBName + "." + TRANSLATION_TABLE + ", " + 
                       DBName + "." + TRANSLATION_STABLE_ID_TABLE + 
            " where " +
            DBName + "." + TRANSLATION_TABLE + "." + TRANSLATION_ID_COL + " = " +
            DBName + "." + TRANSLATION_STABLE_ID_TABLE + "." + TRANSLATION_ID_COL 
            + " and  " + 
            DBName + "." + TRANSLATION_TABLE + "." + TRANSCRIPT_ID_COL + " = ?";
        //log.println (DBUtil.LOAD + ": " + loadQuery);

        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            con = DBUtil.getConnection ();
            ps = con.prepareStatement (loadQuery);
            ps.setString(1, DBID);
            rs = ps.executeQuery();
            int cnt = 0;
            while (rs.next ()) {  //translationID variable is the stable ID.
                String translationID = rs.getString (STABLE_ID_COL);
                ensGene.addProteinID(translationID);
                cnt++;
            }
            //System.out.println ("Loaded: " + cnt + " proteins for "
            //+ ensGene.getID());
        } catch (SQLException e) {
            throw e;
        } finally {
            if (ps != null) {
                ps.close ();
            }
        }    
    }
    
    private static void setDBName (String speciesName) {
       
        GenUtil.validateString(speciesName);
        DBName = DefParams.getDefaultProp(speciesName);
    }
    
    private static ArrayList <String> getSynsByXref (int xRefID) 
                    throws SQLException {
        
        ArrayList <String> synList = new ArrayList <String> ();
        
        //System.out.println ("checking syns for xRef: " + xRefID);
        String synQuery = "select " + 
            DBName + "." + EXTERNAL_SYN_TABLE + "." + SYN_COL +
            " from " + DBName + "." + EXTERNAL_SYN_TABLE + 
            " where " +
            DBName + "." + EXTERNAL_SYN_TABLE + "." + XREF_ID_COL + " = ?";
        
        //log.println (DBUtil.LOAD + ": " + synQuery);
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            con = DBUtil.getConnection ();
            ps = con.prepareStatement (synQuery);
            ps.setInt(1, xRefID);
            rs = ps.executeQuery();
            int cnt = 0;
            while (rs.next ()) {
                synList.add (rs.getString (SYN_COL));
                cnt++;
            }
            //System.out.println ("Loaded: " + cnt + " syns for xref: " + xRefID);
        } catch (SQLException e) {
            throw e;
        } finally {
            if (ps != null) {
                ps.close ();
            }
        }
        
        return synList;
    }

    private static void loadSpeciesSpecificIDs (String speciesName,
           TreeMap <String, ExtResource> ensGeneMap) throws SQLException {

        //mouse specific
        if (speciesName.equals(DWUtil.SP_MOUSE)) {
            matchAllMGD (ensGeneMap);
        }
    }

    private static void matchAllEntrez (TreeMap <String, ExtResource> ensMap) 
            throws SQLException {
    
        matchXRef (ensMap, new int [] {EXTERNAL_DB_ENTREZ});
    }

    private static void matchAllUniprot (TreeMap <String, ExtResource> ensMap) 
            throws SQLException {
    
        matchXRef (ensMap, new int [] {EXTERNAL_DB_UNIPROT_SWISS});
    }

    private static void matchAllMGD (TreeMap <String, ExtResource> ensMap)
            throws SQLException {

        matchXRef (ensMap, new int [] {EXTERNAL_DB_MGI_SYMBOL, EXTERNAL_DB_MGI_GENE_AUTO,
                                       EXTERNAL_DB_MGI_GENE_CURATED});
        matchXRefTranscript (ensMap, new int [] {EXTERNAL_DB_MGI_SYMBOL, EXTERNAL_DB_MGI_GENE_AUTO,
                                       EXTERNAL_DB_MGI_GENE_CURATED});
        matchXRefGene (ensMap, new int [] {EXTERNAL_DB_MGI_SYMBOL, EXTERNAL_DB_MGI_GENE_AUTO,
                                       EXTERNAL_DB_MGI_GENE_CURATED});
    }
    
    //Helper method that handles querying for xrefs
    private static void matchXRef (TreeMap <String, ExtResource> ensMap, 
            int [] extDBIDArr) throws SQLException {
        

        System.out.println ("Mapping X-refs: " + XREF_TYPE_TRANSLATE);
        String loadQuery = "select distinct " + 
            DBName + "." + GENE_STABLE_ID_TABLE + "." + STABLE_ID_COL + ", " +
            DBName + "." + XREF_TABLE + "." + EXTERNAL_DB_ID_COL + ", " +
            DBName + "." + XREF_TABLE + "." + DBPRIMARY_ACC_ID_COL + ", " +
            DBName + "." + XREF_TABLE + "." + DISPLAY_LABEL_COL + 
            " from " + DBName + "." + GENE_STABLE_ID_TABLE + ", " +
            DBName + "." + TRANSCRIPT_TABLE + ", " + 
            DBName + "." + TRANSLATION_TABLE + ", " + 
            DBName + "." + OBJECT_XREF_TABLE + ", " +  
            DBName + "." + XREF_TABLE +  
            " where " +
            DBName + "." + GENE_STABLE_ID_TABLE + "." + GENE_ID_COL + " = " + 
            DBName + "." + TRANSCRIPT_TABLE + "." + GENE_ID_COL + " and " +
            DBName + "." + TRANSCRIPT_TABLE + "." + TRANSCRIPT_ID_COL + " = " +
            DBName + "." + TRANSLATION_TABLE + "." + TRANSCRIPT_ID_COL + " and " + 
            DBName + "." + TRANSLATION_TABLE + "." + TRANSLATION_ID_COL + " = " +
            DBName + "." + OBJECT_XREF_TABLE + "." + ENS_ID_COL + " and " +
            DBName + "." + OBJECT_XREF_TABLE + "." + ENS_OBJECT_TYPE_COL + " = ? and " +
            DBName + "." + OBJECT_XREF_TABLE + "." + XREF_ID_COL + " = " +
            DBName + "." + XREF_TABLE + "." + XREF_ID_COL + " and " +
            DBName + "." + XREF_TABLE + "." + EXTERNAL_DB_ID_COL  + " in (";
        for (int i = 0; i < extDBIDArr.length; i++) {
           loadQuery += "?";
           if (i < extDBIDArr.length - 1) {
               loadQuery += ",";
           }
           System.out.print (extDBIDArr [i] + " ");
        }
        System.out.println ();
        loadQuery += ") order by " +
             DBName + "." + GENE_STABLE_ID_TABLE + "." + STABLE_ID_COL + ", " +
             DBName + "." + XREF_TABLE + "." + EXTERNAL_DB_ID_COL;
                
        log.println (DBUtil.LOAD + ": " + loadQuery);
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            con = DBUtil.getConnection ();
            ps = con.prepareStatement (loadQuery);
            ps.setString (1, XREF_TYPE_TRANSLATE);
            for (int i = 0; i < extDBIDArr.length; i++) {
                ps.setInt (i+2, extDBIDArr [i]);
            }
            rs = ps.executeQuery();
            int cnt = 0;
            while (rs.next ()) {
                String ensGeneID = rs.getString (STABLE_ID_COL);
                int extDBID = rs.getInt (EXTERNAL_DB_ID_COL);
                ExtResourceGene ensGene = (ExtResourceGene)ensMap.get (ensGeneID);
                if (extDBID == EXTERNAL_DB_UNIPROT_SWISS) {  
                    ensGene.addUniprotID (rs.getString (DISPLAY_LABEL_COL));
                    ensGene.addUniprotID(rs.getString (DBPRIMARY_ACC_ID_COL));
                } else if (extDBID == EXTERNAL_DB_ENTREZ) {
                    EntrezGene entGene = new EntrezGene ();
                    entGene.setID(rs.getString (DBPRIMARY_ACC_ID_COL));
                    entGene.setSpeciesName(ensGene.getSpeciesName());
                    if (rs.getString (DISPLAY_LABEL_COL) != null &&
                        rs.getString (DISPLAY_LABEL_COL).length () != 0) {
                        entGene.setName(rs.getString (DISPLAY_LABEL_COL));
                    }
                    ensGene.addXRef(ExtResource.RES_ENTREZ, entGene);
                } else if (extDBID == EXTERNAL_DB_MGI_SYMBOL ||
                           extDBID == EXTERNAL_DB_MGI_GENE_AUTO ||
                           extDBID == EXTERNAL_DB_MGI_GENE_CURATED) {
                    ensGene.addMGDID (rs.getString (DBPRIMARY_ACC_ID_COL));
                }
                cnt++;                    //counts the total list
            }
            System.out.println ("Mapped Xref to Ensembl: " + cnt);
        } catch (SQLException e) {
            throw e;
        } finally {
            if (ps != null) {
                ps.close ();
            }
            if (con != null && !con.isClosed ()) {
                con.close ();
            }
        }
    }

    //Similar helper method
    private static void matchXRefTranscript (TreeMap <String, ExtResource> ensMap,
            int [] extDBIDArr) throws SQLException {

        System.out.println ("Mapping X-refs: " + XREF_TYPE_TRANSCRIPT);
        String loadQuery = "select distinct " +
            DBName + "." + GENE_STABLE_ID_TABLE + "." + STABLE_ID_COL + ", " +
            DBName + "." + XREF_TABLE + "." + EXTERNAL_DB_ID_COL + ", " +
            DBName + "." + XREF_TABLE + "." + DBPRIMARY_ACC_ID_COL + ", " +
            DBName + "." + XREF_TABLE + "." + DISPLAY_LABEL_COL +
            " from " + DBName + "." + GENE_STABLE_ID_TABLE + ", " +
            DBName + "." + TRANSCRIPT_TABLE + ", " +
            DBName + "." + OBJECT_XREF_TABLE + ", " +
            DBName + "." + XREF_TABLE +
            " where " +
            DBName + "." + GENE_STABLE_ID_TABLE + "." + GENE_ID_COL + " = " +
            DBName + "." + TRANSCRIPT_TABLE + "." + GENE_ID_COL + " and " +
            DBName + "." + TRANSCRIPT_TABLE + "." + TRANSCRIPT_ID_COL + " = " +
            DBName + "." + OBJECT_XREF_TABLE + "." + ENS_ID_COL + " and " +
            DBName + "." + OBJECT_XREF_TABLE + "." + ENS_OBJECT_TYPE_COL + " = ? and " +
            DBName + "." + OBJECT_XREF_TABLE + "." + XREF_ID_COL + " = " +
            DBName + "." + XREF_TABLE + "." + XREF_ID_COL + " and " +
            DBName + "." + XREF_TABLE + "." + EXTERNAL_DB_ID_COL  + " in (";
        for (int i = 0; i < extDBIDArr.length; i++) {
           loadQuery += "?";
           if (i < extDBIDArr.length - 1) {
               loadQuery += ",";
           }
           System.out.print (extDBIDArr [i] + " ");
        }
        System.out.println ();
        loadQuery += ") order by " +
             DBName + "." + GENE_STABLE_ID_TABLE + "." + STABLE_ID_COL + ", " +
             DBName + "." + XREF_TABLE + "." + EXTERNAL_DB_ID_COL;

        log.println (DBUtil.LOAD + ": " + loadQuery);
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            con = DBUtil.getConnection ();
            ps = con.prepareStatement (loadQuery);
            ps.setString (1, XREF_TYPE_TRANSCRIPT);
            for (int i = 0; i < extDBIDArr.length; i++) {
                ps.setInt (i+2, extDBIDArr [i]);
            }
            rs = ps.executeQuery();
            int cnt = 0;
            while (rs.next ()) {
                String ensGeneID = rs.getString (STABLE_ID_COL);
                int extDBID = rs.getInt (EXTERNAL_DB_ID_COL);
                ExtResourceGene ensGene = (ExtResourceGene)ensMap.get (ensGeneID);
                if (extDBID == EXTERNAL_DB_UNIPROT_SWISS) {
                    ensGene.addUniprotID (rs.getString (DISPLAY_LABEL_COL));
                    ensGene.addUniprotID(rs.getString (DBPRIMARY_ACC_ID_COL));
                } else if (extDBID == EXTERNAL_DB_ENTREZ) {
                    EntrezGene entGene = new EntrezGene ();
                    entGene.setID(rs.getString (DBPRIMARY_ACC_ID_COL));
                    entGene.setSpeciesName(ensGene.getSpeciesName());
                    if (rs.getString (DISPLAY_LABEL_COL) != null &&
                        rs.getString (DISPLAY_LABEL_COL).length () != 0) {
                        entGene.setName(rs.getString (DISPLAY_LABEL_COL));
                    }
                    ensGene.addXRef(ExtResource.RES_ENTREZ, entGene);
                } else if (extDBID == EXTERNAL_DB_MGI_SYMBOL ||
                           extDBID == EXTERNAL_DB_MGI_GENE_AUTO ||
                           extDBID == EXTERNAL_DB_MGI_GENE_CURATED) {
                    ensGene.addMGDID (rs.getString (DBPRIMARY_ACC_ID_COL));
                }
                cnt++;                    //counts the total list
            }
            System.out.println ("Mapped Xref to Ensembl: " + cnt);
        } catch (SQLException e) {
            throw e;
        } finally {
            if (ps != null) {
                ps.close ();
            }
            if (con != null && !con.isClosed ()) {
                con.close ();
            }
        }
    }

    //similar helper method
    private static void matchXRefGene (TreeMap <String, ExtResource> ensMap,
            int [] extDBIDArr) throws SQLException {

        System.out.println ("Mapping X-refs: " + XREF_TYPE_GENE);
        String loadQuery = "select distinct " +
            DBName + "." + GENE_STABLE_ID_TABLE + "." + STABLE_ID_COL + ", " +
            DBName + "." + XREF_TABLE + "." + EXTERNAL_DB_ID_COL + ", " +
            DBName + "." + XREF_TABLE + "." + DBPRIMARY_ACC_ID_COL + ", " +
            DBName + "." + XREF_TABLE + "." + DISPLAY_LABEL_COL +
            " from " + DBName + "." + GENE_STABLE_ID_TABLE + ", " +
            DBName + "." + OBJECT_XREF_TABLE + ", " +
            DBName + "." + XREF_TABLE +
            " where " +
            DBName + "." + GENE_STABLE_ID_TABLE + "." + GENE_ID_COL + " = " +
            DBName + "." + OBJECT_XREF_TABLE + "." + ENS_ID_COL + " and " +
            DBName + "." + OBJECT_XREF_TABLE + "." + ENS_OBJECT_TYPE_COL + " = ? and " +
            DBName + "." + OBJECT_XREF_TABLE + "." + XREF_ID_COL + " = " +
            DBName + "." + XREF_TABLE + "." + XREF_ID_COL + " and " +
            DBName + "." + XREF_TABLE + "." + EXTERNAL_DB_ID_COL  + " in (";
        for (int i = 0; i < extDBIDArr.length; i++) {
           loadQuery += "?";
           if (i < extDBIDArr.length - 1) {
               loadQuery += ",";
           }
           System.out.print (extDBIDArr [i] + " ");
        }
        System.out.println ();
        loadQuery += ") order by " +
             DBName + "." + GENE_STABLE_ID_TABLE + "." + STABLE_ID_COL + ", " +
             DBName + "." + XREF_TABLE + "." + EXTERNAL_DB_ID_COL;

        log.println (DBUtil.LOAD + ": " + loadQuery);
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            con = DBUtil.getConnection ();
            ps = con.prepareStatement (loadQuery);
            ps.setString (1, XREF_TYPE_GENE);
            for (int i = 0; i < extDBIDArr.length; i++) {
                ps.setInt (i+2, extDBIDArr [i]);
            }
            rs = ps.executeQuery();
            int cnt = 0;
            while (rs.next ()) {
                String ensGeneID = rs.getString (STABLE_ID_COL);
                int extDBID = rs.getInt (EXTERNAL_DB_ID_COL);
                ExtResourceGene ensGene = (ExtResourceGene)ensMap.get (ensGeneID);
                if (extDBID == EXTERNAL_DB_UNIPROT_SWISS) {
                    ensGene.addUniprotID (rs.getString (DISPLAY_LABEL_COL));
                    ensGene.addUniprotID(rs.getString (DBPRIMARY_ACC_ID_COL));
                } else if (extDBID == EXTERNAL_DB_ENTREZ) {
                    EntrezGene entGene = new EntrezGene ();
                    entGene.setID(rs.getString (DBPRIMARY_ACC_ID_COL));
                    entGene.setSpeciesName(ensGene.getSpeciesName());
                    if (rs.getString (DISPLAY_LABEL_COL) != null &&
                        rs.getString (DISPLAY_LABEL_COL).length () != 0) {
                        entGene.setName(rs.getString (DISPLAY_LABEL_COL));
                    }
                    ensGene.addXRef(ExtResourceGene.RES_ENTREZ, entGene);
                } else if (extDBID == EXTERNAL_DB_MGI_SYMBOL ||
                           extDBID == EXTERNAL_DB_MGI_GENE_AUTO ||
                           extDBID == EXTERNAL_DB_MGI_GENE_CURATED) {
                    ensGene.addMGDID (rs.getString (DBPRIMARY_ACC_ID_COL));
                }
                cnt++;                    //counts the total list
            }
            System.out.println ("Mapped Xref to Ensembl: " + cnt);
        } catch (SQLException e) {
            throw e;
        } finally {
            if (ps != null) {
                ps.close ();
            }
            if (con != null && !con.isClosed ()) {
                con.close ();
            }
        }
    }
}
