org.ensembl.gti.seqstore.database.JdbcSeqStore.java Source code

Java tutorial

Introduction

Here is the source code for org.ensembl.gti.seqstore.database.JdbcSeqStore.java

Source

/*
 * Copyright 2015 EMBL-European Bioinformatics Institute
 * 
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 * 
 *      http://www.apache.org/licenses/LICENSE-2.0
 * 
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

package org.ensembl.gti.seqstore.database;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Date;

import javax.sql.DataSource;

import org.ensembl.gti.seqstore.Exon;
import org.ensembl.gti.seqstore.Gene;
import org.ensembl.gti.seqstore.Genome;
import org.ensembl.gti.seqstore.GenomeSequence;
import org.ensembl.gti.seqstore.Transcript;
import org.ensembl.gti.seqstore.Translation;
import org.ensembl.gti.seqstore.utils.SeqStoreHashUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.dao.DuplicateKeyException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.TransactionCallback;
import org.springframework.transaction.support.TransactionTemplate;

/**
 * JDBC implementation of {@link SeqStore}
 * 
 * @author dstaines
 *
 */
public class JdbcSeqStore implements SeqStore {

    protected static final String INSERT_SESSION_SQL = "insert into session(client_id,start_date) values(?,NOW())";
    protected static final String GET_SESSION_CLIENT_SQL = "select client_id from session where session_id=?";
    protected static final String GET_SESSION_DATE_SQL = "select start_date from session where session_id=?";

    protected static final String STORE_SEQ_SQL = "insert ignore into sequence(seq_checksum,sequence,session_id) values(?,?,?)";
    protected static final String STORE_GENE_SQL = "insert into gene(stable_id,genome_id,"
            + "loc_str,loc_checksum,seq_checksum,session_id) " + "values(?,?,?,?,?,?)";
    protected static final String STORE_TRANSCRIPT_SQL = "insert into transcript(stable_id,genome_id,"
            + "loc_str,loc_checksum,seq_checksum,session_id) " + "values(?,?,?,?,?,?)";
    protected static final String STORE_TRANSLATION_SQL = "insert into translation(stable_id,genome_id,"
            + "seq_checksum,session_id) " + "values(?,?,?,?)";
    protected static final String STORE_EXON_SQL = "insert ignore into exon(stable_id,genome_id,"
            + "loc_str,loc_checksum,seq_checksum,session_id) " + "values(?,?,?,?,?,?)";

    protected static final String STORE_GENOME_SEQUENCE_SQL = "insert into genome_sequence(genome_id,stable_id,seq_checksum,session_id) values(?,?,?,?)";

    protected static final String STORE_GENOME_SQL = "insert into genome(name,assembly,genebuild,tax_id,session_id) values(?,?,?,?,?)";

    protected static final String GET_SEQ_SQL = "select sequence from sequence where seq_checksum=?";

    protected static final String CLEAR_SESSION_OBJ = "delete from OBJ where session_id=?";

    protected static final String CLEAR_GENOME_OBJ = "delete from OBJ where genome_id=?";

    protected final TransactionTemplate transactionTemplate;
    protected final JdbcTemplate template;
    protected final Logger log;

    public JdbcSeqStore(DataSource dataSource) {
        this.template = new JdbcTemplate(dataSource);
        this.transactionTemplate = new TransactionTemplate(new DataSourceTransactionManager(dataSource));
        this.log = LoggerFactory.getLogger(this.getClass());
    }

    public long startSession(String clientId) {
        log.debug("Starting session for " + clientId);
        long id = transactionTemplate.execute(new TransactionCallback<Long>() {
            @Override
            public Long doInTransaction(TransactionStatus status) {
                KeyHolder keyHolder = new GeneratedKeyHolder();
                template.update(new PreparedStatementCreator() {
                    public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
                        PreparedStatement pst = con.prepareStatement(INSERT_SESSION_SQL, new String[] { "id" });
                        pst.setString(1, clientId);
                        return pst;
                    }
                }, keyHolder);
                return (Long) keyHolder.getKey();
            }
        });
        log.debug("New session " + id);
        return id;
    }

    @Override
    public String clientForSession(long sessionId) {
        return template.queryForObject(GET_SESSION_CLIENT_SQL, String.class, sessionId);
    }

    @Override
    public Date dateForSession(long sessionId) {
        return template.queryForObject(GET_SESSION_DATE_SQL, Date.class, sessionId);
    }

    @Override
    public void storeGene(long sessionId, Gene gene) {
        log.debug("Storing gene " + gene.getStableId() + " in session " + sessionId);
        transactionTemplate.execute(new TransactionCallback<Void>() {
            @Override
            public Void doInTransaction(TransactionStatus status) {
                String chk = storeSequence(sessionId, gene.getSequence());
                String locStr = SeqStoreHashUtils.locationToString(gene);
                String modelHash = SeqStoreHashUtils.hashGeneModel(gene);
                try {
                    template.update(STORE_GENE_SQL, gene.getStableId(), gene.getGenomeId(), locStr, modelHash, chk,
                            sessionId);
                } catch (DuplicateKeyException e) {
                    String msg = "Cannot store gene " + gene.getStableId() + " as it already exists";
                    log.error(msg, e);
                    throw new DuplicateSeqObjException(msg, e);
                }
                for (Transcript t : gene.getTranscripts()) {
                    storeTranscript(sessionId, gene.getGenomeId(), t);
                }
                return null;
            }
        });
        for (Exon e : gene.getExons()) {
            storeExon(sessionId, gene.getGenomeId(), e);
        }
        log.debug("Completed storing gene " + gene.getStableId() + " in session " + sessionId);
    }

    @Override
    public void storeTranscript(long sessionId, int genomeId, Transcript transcript) {
        log.debug("Storing transcript " + transcript.getStableId() + " in session " + sessionId);
        String chk = storeSequence(sessionId, transcript.getSequence());
        String locStr = SeqStoreHashUtils.locationToString(transcript);
        String modelHash = SeqStoreHashUtils.hashTranscriptModel(transcript);
        try {
            template.update(STORE_TRANSCRIPT_SQL, transcript.getStableId(), genomeId, locStr, modelHash, chk,
                    sessionId);
        } catch (DuplicateKeyException e) {
            String msg = "Cannot store transcript " + transcript.getStableId() + " as it already exists";
            log.error(msg, e);
            throw new DuplicateSeqObjException(msg, e);
        }
        for (Translation tl : transcript.getTranslations()) {
            storeTranslation(sessionId, genomeId, tl);
        }
        log.debug("Completed storing transcript " + transcript.getStableId() + " in session " + sessionId);
    }

    @Override
    public void storeTranslation(long sessionId, int genomeId, Translation translation) {
        log.debug("Storing translation " + translation.getStableId() + " in session " + sessionId);
        String chk = storeSequence(sessionId, translation.getSequence());
        try {
            template.update(STORE_TRANSLATION_SQL, translation.getStableId(), genomeId, chk, sessionId);
        } catch (DuplicateKeyException e) {
            String msg = "Cannot store translation " + translation.getStableId() + " as it already exists";
            log.error(msg, e);
            throw new DuplicateSeqObjException(msg, e);
        }
        log.debug("Completed storing translation " + translation.getStableId() + " in session " + sessionId);

    }

    @Override
    public void storeExon(long sessionId, int genomeId, Exon exon) {
        log.debug("Storing exon " + exon.getStableId() + " in session " + sessionId);

        String chk = storeSequence(sessionId, exon.getSequence());
        String locStr = SeqStoreHashUtils.locationToString(exon);
        String modelHash = SeqStoreHashUtils.hashExonModel(exon);
        try {
            template.update(STORE_EXON_SQL, exon.getStableId(), genomeId, locStr, modelHash, chk, sessionId);
        } catch (DuplicateKeyException e) {
            String msg = "Cannot store exon " + exon.getStableId() + " as it already exists";
            log.error(msg, e);
            throw new DuplicateSeqObjException(msg, e);
        }
        log.debug("Completed storing exon " + exon.getStableId() + " in session " + sessionId);
    }

    @Override
    public String storeSequence(long sessionId, String sequence) {
        String chk = SeqStoreHashUtils.hashSequence(sequence);
        log.debug("Storing sequence with hash " + chk + " in session " + sessionId);
        template.update(STORE_SEQ_SQL, chk, sequence, sessionId);
        log.debug("Completed toring sequence with hash " + chk + " in session " + sessionId);
        return chk;
    }

    @Override
    public String getSequence(String checksum) {
        return template.queryForObject(GET_SEQ_SQL, String.class, checksum);
    }

    @Override
    public long storeGenome(long sessionId, Genome genome) {
        long id;
        try {
            id = transactionTemplate.execute(new TransactionCallback<Long>() {
                @Override
                public Long doInTransaction(TransactionStatus status) {
                    KeyHolder keyHolder = new GeneratedKeyHolder();
                    template.update(new PreparedStatementCreator() {
                        public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
                            PreparedStatement pst = con.prepareStatement(STORE_GENOME_SQL,
                                    new String[] { "name", "assembly", "genebuild", "tax_id", "session_id" });
                            pst.setString(1, genome.getGenomeName());
                            pst.setString(2, genome.getAssembly());
                            pst.setString(3, genome.getGenebuild());
                            pst.setInt(4, genome.getTaxId());
                            pst.setLong(5, sessionId);
                            return pst;
                        }
                    }, keyHolder);
                    return (Long) keyHolder.getKey();
                }
            });
        } catch (DuplicateKeyException e) {
            String msg = "Cannot store genome " + genome.toString() + " as it already exists";
            log.error(msg, e);
            throw new DuplicateSeqObjException(msg, e);
        }
        log.debug("New genome " + id);
        return id;
    }

    @Override
    public void storeGenomeSequence(long sessionId, GenomeSequence genomeSequence) {
        log.debug("Storing genome sequence  " + genomeSequence.getStableId());
        transactionTemplate.execute(new TransactionCallback<Void>() {
            @Override
            public Void doInTransaction(TransactionStatus status) {
                String chk = storeSequence(sessionId, genomeSequence.getSequence());
                template.update(STORE_GENOME_SEQUENCE_SQL, genomeSequence.getGenomeId(),
                        genomeSequence.getStableId(), chk, sessionId);
                return null;
            }
        });
    }

    @Override
    public void clearSession(long sessionId) {
        log.debug("Clearing all entries for session " + sessionId);
        transactionTemplate.execute(new TransactionCallback<Void>() {
            @Override
            public Void doInTransaction(TransactionStatus status) {
                for (String obj : new String[] { "translation", "exon", "transcript", "gene", "genome_sequence",
                        "genome" }) {
                    String sql = CLEAR_SESSION_OBJ.replaceAll("OBJ", obj);
                    template.update(sql, sessionId);
                }
                return null;
            }
        });
    }

    @Override
    public void clearGenome(long genomeId) {
        log.debug("Clearing all entries for genome " + genomeId);
        transactionTemplate.execute(new TransactionCallback<Void>() {
            @Override
            public Void doInTransaction(TransactionStatus status) {
                for (String obj : new String[] { "translation", "exon", "transcript", "gene", "genome_sequence",
                        "genome" }) {
                    String sql = CLEAR_GENOME_OBJ.replaceAll("OBJ", obj);
                    template.update(sql, genomeId);
                }
                return null;
            }
        });
    }

}