Examples with Record massbank.Record used on opensource projects

Example 11 with Record

use of massbank.Record in project MassBank-web by MassBank.

the class DatabaseManager method getAccessionData.

/**
 * Returns the complete record TODO solve 1:1 relations by a single sql
 * statement with joins (PK_ANNOTATION_HEADER, acc.PK_NUM_PEAK, Compound stuff,
 * SP_SCIENTIFIC_NAME, SP_LINEAGE, AC_INSTRUMENT, AC_INSTRUMENT_TYPE)
 *
 * @param accessionId
 * @return Record
 */
public Record getAccessionData(String accessionId) {
    Record.Contributor Contributor = getContributorFromAccession(accessionId);
    if (Contributor == null)
        return null;
    String contributor = Contributor.SHORT_NAME;
    Record acc = new Record(contributor);
    try {
        this.statementSelectRECORD.setString(1, accessionId);
        ResultSet set = this.statementSelectRECORD.executeQuery();
        int compoundID = -1;
        int sampleID = -1;
        int instrumentID = -1;
        if (set.next()) {
            acc.ACCESSION(set.getString("ACCESSION"));
            acc.RECORD_TITLE1(set.getString("RECORD_TITLE"));
            acc.DATE(set.getString("DATE"));
            acc.AUTHORS(set.getString("AUTHORS"));
            acc.LICENSE(set.getString("LICENSE"));
            acc.COPYRIGHT(set.getString("COPYRIGHT"));
            acc.PUBLICATION(set.getString("PUBLICATION"));
            compoundID = set.getInt("CH");
            sampleID = set.getInt("SP");
            instrumentID = set.getInt("AC_INSTRUMENT");
            acc.AC_MASS_SPECTROMETRY_MS_TYPE(set.getString("AC_MASS_SPECTROMETRY_MS_TYPE"));
            acc.AC_MASS_SPECTROMETRY_ION_MODE(set.getString("AC_MASS_SPECTROMETRY_ION_MODE"));
            acc.PK_SPLASH(set.getString("PK_SPLASH"));
            this.statementSelectAC_CHROMATOGRAPHY.setString(1, set.getString("ACCESSION"));
            this.statementSelectAC_MASS_SPECTROMETRY.setString(1, set.getString("ACCESSION"));
            this.statementSelectMS_DATA_PROCESSING.setString(1, set.getString("ACCESSION"));
            this.statementSelectMS_FOCUSED_ION.setString(1, set.getString("ACCESSION"));
            this.statementSelectCOMMENT.setString(1, set.getString("ACCESSION"));
            this.statementSelectPEAK.setString(1, set.getString("ACCESSION"));
            // this.statementPK_NUM_PEAK.setString(1, set.getString("ACCESSION"));
            this.statementSelectANNOTATION_HEADER.setString(1, accessionId);
            ResultSet tmp = this.statementSelectAC_CHROMATOGRAPHY.executeQuery();
            List<Pair<String, String>> tmpList = new ArrayList<Pair<String, String>>();
            while (tmp.next()) tmpList.add(Pair.of(tmp.getString("SUBTAG"), tmp.getString("VALUE")));
            acc.AC_CHROMATOGRAPHY(tmpList);
            tmp = this.statementSelectAC_MASS_SPECTROMETRY.executeQuery();
            tmpList.clear();
            while (tmp.next()) tmpList.add(Pair.of(tmp.getString("SUBTAG"), tmp.getString("VALUE")));
            acc.AC_MASS_SPECTROMETRY(tmpList);
            tmp = this.statementSelectMS_DATA_PROCESSING.executeQuery();
            tmpList.clear();
            while (tmp.next()) tmpList.add(Pair.of(tmp.getString("SUBTAG"), tmp.getString("VALUE")));
            acc.MS_DATA_PROCESSING(tmpList);
            tmp = this.statementSelectMS_FOCUSED_ION.executeQuery();
            tmpList.clear();
            while (tmp.next()) tmpList.add(Pair.of(tmp.getString("SUBTAG"), tmp.getString("VALUE")));
            acc.MS_FOCUSED_ION(tmpList);
            tmp = this.statementSelectCOMMENT.executeQuery();
            List<String> tmpList2 = new ArrayList<String>();
            while (tmp.next()) tmpList2.add(tmp.getString("COMMENT"));
            acc.COMMENT(tmpList2);
            tmp = this.statementSelectANNOTATION_HEADER.executeQuery();
            // int PK_ANNOTATION_HEADER_numberOfTokens	= -1;
            if (tmp.next()) {
                String PK_ANNOTATION_HEADER = tmp.getString("HEADER");
                String[] PK_ANNOTATION_HEADER_tokens = PK_ANNOTATION_HEADER.split(" ");
                acc.PK_ANNOTATION_HEADER(Arrays.asList(PK_ANNOTATION_HEADER_tokens));
                // PK_ANNOTATION_HEADER_numberOfTokens	= PK_ANNOTATION_HEADER_tokens.length;
                this.statementSelectANNOTATION.setString(1, set.getString("ACCESSION"));
                tmp = this.statementSelectANNOTATION.executeQuery();
                while (tmp.next()) {
                    BigDecimal mz = (new BigDecimal(String.valueOf(tmp.getDouble("PK_PEAK_MZ")))).setScale(tmp.getInt("PK_PEAK_MZ_SIGNIFICANT"));
                    List<String> annotation = Arrays.asList(tmp.getString("PK_ANNOTATION").split(" "));
                    acc.PK_ANNOTATION_ADD_LINE(Pair.of(mz, annotation));
                }
            }
            tmp = this.statementSelectPEAK.executeQuery();
            while (tmp.next()) {
                BigDecimal mz = (new BigDecimal(String.valueOf(tmp.getDouble("PK_PEAK_MZ")))).setScale(tmp.getInt("PK_PEAK_MZ_SIGNIFICANT"));
                BigDecimal intensity = (new BigDecimal(String.valueOf(tmp.getDouble("PK_PEAK_INTENSITY")))).setScale(tmp.getInt("PK_PEAK_INTENSITY_SIGNIFICANT"));
                acc.PK_PEAK_ADD_LINE(Triple.of(mz, intensity, tmp.getInt("PK_PEAK_RELATIVE")));
            }
        } else {
            // try to find the ACCESSION in DEPRECATED_RECORD
            this.statementSelectDEPRECATED_RECORD.setString(1, accessionId);
            set = this.statementSelectDEPRECATED_RECORD.executeQuery();
            if (set.next()) {
                acc.ACCESSION(set.getString("ACCESSION"));
                acc.DEPRECATED(true);
                acc.DEPRECATED_CONTENT(set.getString("CONTENT"));
                return acc;
            } else
                throw new IllegalStateException("accessionId '" + accessionId + "' is not in database");
        }
        set.close();
        if (compoundID == -1)
            throw new IllegalStateException("compoundID is not set");
        this.statementSelectCOMPOUND.setInt(1, compoundID);
        set = this.statementSelectCOMPOUND.executeQuery();
        while (set.next()) {
            acc.CH_FORMULA(set.getString("CH_FORMULA"));
            BigDecimal exactMass = (new BigDecimal(String.valueOf(set.getDouble("CH_EXACT_MASS")))).setScale(set.getInt("CH_EXACT_MASS_SIGNIFICANT"));
            acc.CH_EXACT_MASS(exactMass);
            acc.CH_SMILES(set.getString("CH_SMILES"));
            acc.CH_IUPAC(set.getString("CH_IUPAC"));
        // TODO CH$CDK_DEPICT_SMILES
        // TODO CH$CDK_DEPICT_GENERIC_SMILES
        // TODO CH$CDK_DEPICT_STRUCTURE_SMILES
        // acc.add("CH$CDK_DEPICT_SMILES", null, set.getString("CH_CDK_DEPICT_SMILES"));
        // acc.add("CH$CDK_DEPICT_GENERIC_SMILES", null, set.getString("CH_CDK_DEPICT_GENERIC_SMILES"));
        // acc.add("CH$CDK_DEPICT_STRUCTURE_SMILES", null, set.getString("CH_CDK_DEPICT_STRUCTURE_SMILES"));
        }
        set.close();
        this.statementSelectCH_LINK.setInt(1, compoundID);
        set = this.statementSelectCH_LINK.executeQuery();
        LinkedHashMap<String, String> tmpMap = new LinkedHashMap<String, String>();
        while (set.next()) {
            tmpMap.put(set.getString("DATABASE_NAME"), set.getString("DATABASE_ID"));
        }
        acc.CH_LINK(tmpMap);
        this.statementSelectCOMPOUND_COMPOUND_CLASS.setInt(1, compoundID);
        set = this.statementSelectCOMPOUND_COMPOUND_CLASS.executeQuery();
        List<String> tmpList2 = new ArrayList<String>();
        while (set.next()) {
            this.statementSelectCOMPOUND_CLASS.setInt(1, set.getInt("CLASS"));
            ResultSet tmp = this.statementSelectCOMPOUND_CLASS.executeQuery();
            while (tmp.next()) {
                tmpList2.add(tmp.getString("CH_COMPOUND_CLASS"));
            }
        }
        acc.CH_COMPOUND_CLASS(tmpList2);
        this.statementSelectCOMPOUND_NAME.setInt(1, compoundID);
        set = this.statementSelectCOMPOUND_NAME.executeQuery();
        tmpList2.clear();
        while (set.next()) {
            int name = set.getInt("NAME");
            this.statementSelectNAME.setInt(1, name);
            // this.statementSelectNAME.setInt(1, set.getInt("NAME"));
            ResultSet tmp = this.statementSelectNAME.executeQuery();
            while (tmp.next()) {
                tmpList2.add(tmp.getString("CH_NAME"));
            }
        }
        acc.CH_NAME(tmpList2);
        this.statementSelectSAMPLE.setInt(1, sampleID);
        set = this.statementSelectSAMPLE.executeQuery();
        if (set.next()) {
            acc.SP_SCIENTIFIC_NAME(set.getString("SP_SCIENTIFIC_NAME"));
            acc.SP_LINEAGE(set.getString("SP_LINEAGE"));
        }
        this.statementSelectSP_LINK.setString(1, acc.ACCESSION());
        set = this.statementSelectSP_LINK.executeQuery();
        List<Pair<String, String>> tmpList = new ArrayList<Pair<String, String>>();
        while (set.next()) {
            String spLink = set.getString("SP_LINK");
            String[] tokens = spLink.split(" ");
            tmpList.add(Pair.of(tokens[0], tokens[1]));
        }
        acc.SP_LINK(tmpList);
        this.statementSelectSP_SAMPLE.setString(1, acc.ACCESSION());
        set = this.statementSelectSP_SAMPLE.executeQuery();
        tmpList2.clear();
        while (set.next()) {
            tmpList2.add(set.getString("SP_SAMPLE"));
        }
        acc.SP_SAMPLE(tmpList2);
        if (instrumentID == -1)
            throw new IllegalStateException("instrumentID is not set");
        this.statementSelectINSTRUMENT.setInt(1, instrumentID);
        set = this.statementSelectINSTRUMENT.executeQuery();
        if (set.next()) {
            acc.AC_INSTRUMENT(set.getString("AC_INSTRUMENT"));
            acc.AC_INSTRUMENT_TYPE(set.getString("AC_INSTRUMENT_TYPE"));
        } else
            throw new IllegalStateException("instrumentID is not in database");
    } catch (Exception e) {
        System.out.println("error: " + accessionId);
        e.printStackTrace();
        return null;
    }
    return acc;
}

Example 12 with Record

use of massbank.Record in project MassBank-web by MassBank.

the class AccessionList method search.

public Record[] search(DatabaseManager databaseManager) {
    // ###########################################################################################
    // fetch all accessions and corresponding contributors
    String sql = "SELECT RECORD.ACCESSION, CONTRIBUTOR.SHORT_NAME " + "FROM RECORD INNER JOIN CONTRIBUTOR INNER JOIN INSTRUMENT " + "WHERE RECORD.CONTRIBUTOR = CONTRIBUTOR.ID";
    StringBuilder sb = new StringBuilder();
    sb.append(sql);
    if (this.inst != null && this.ms != null && this.ion != null) {
        sb.append(" AND (");
        for (int i = 0; i < inst.length; i++) {
            sb.append("INSTRUMENT.AC_INSTRUMENT_TYPE = ?");
            if (i < inst.length - 1) {
                sb.append(" OR ");
            }
        }
        sb.append(") AND (");
        for (int i = 0; i < ms.length; i++) {
            sb.append("RECORD.AC_MASS_SPECTROMETRY_MS_TYPE = ?");
            if (i < ms.length - 1) {
                sb.append(" OR ");
            }
        }
        sb.append(")");
        if (Integer.parseInt(ion) != 0) {
            sb.append(" AND RECORD.AC_MASS_SPECTROMETRY_ION_MODE = ?");
        }
    } else {
        sb.append(")");
    }
    List<String> resList_accession = new ArrayList<String>();
    List<String> resList_contribut = new ArrayList<String>();
    try {
        PreparedStatement stmnt = databaseManager.getConnection().prepareStatement(sb.toString());
        int idx = 1;
        if (this.inst != null && this.ms != null && this.ion != null) {
            for (int i = 0; i < inst.length; i++) {
                stmnt.setString(idx, inst[i]);
                idx++;
            }
            for (int i = 0; i < ms.length; i++) {
                stmnt.setString(idx, ms[i]);
                idx++;
            }
            if (Integer.parseInt(ion) == 1) {
                stmnt.setString(idx, "POSITIVE");
            }
            if (Integer.parseInt(ion) == -1) {
                stmnt.setString(idx, "NEGATIVE");
            }
        }
        ResultSet res = stmnt.executeQuery();
        while (res.next()) {
            resList_accession.add(res.getString("RECORD.ACCESSION"));
            resList_contribut.add(res.getString("CONTRIBUTOR.SHORT_NAME"));
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
    // ###########################################################################################
    // fetch these records
    List<Record> resList_record = new ArrayList<Record>();
    for (int recordIdx = 0; recordIdx < resList_accession.size(); recordIdx++) {
        resList_record.add(databaseManager.getAccessionData(resList_accession.get(recordIdx)));
    }
    return resList_record.toArray(new Record[resList_record.size()]);
}

Example 13 with Record

use of massbank.Record in project MassBank-web by MassBank.

the class RecordToRIKEN_MSP method recordsToRIKEN_MSP.

/**
 * A wrapper to convert multiple Records and write to file.
 * @param file to write
 * @param records to convert
 * @throws CDKException
 */
public static void recordsToRIKEN_MSP(File file, List<Record> records) {
    // collect data
    List<String> list = new ArrayList<String>();
    for (Record record : records) {
        list.add(convert(record));
        list.add("");
    }
    BufferedWriter writer;
    try {
        writer = new BufferedWriter(new FileWriter(file));
        for (String line : list) {
            writer.write(line);
        }
        writer.close();
    } catch (IOException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
}