Java tutorial
/* * 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; } }); } }