/**
* Returns a <code>List<String></code> of distinct MGI references suitable for autocomplete
* sourcing.
*
* @return a <code>List<String></code> of distinct MGI references suitable for autocomplete
* sourcing.
*/
public List<String> getMGIReferences() {
List<String> targetList = new ArrayList();
Session session = factory.getCurrentSession();
List sourceList = null;
try {
session.beginTransaction();
sourceList =
session
.createSQLQuery(
"SELECT DISTINCT mgi_ref FROM genes ORDER BY CAST(mgi_ref AS unsigned) ASC")
.list();
session.getTransaction().commit();
} catch (HibernateException e) {
session.getTransaction().rollback();
throw e;
}
if (sourceList != null) {
Iterator iterator = sourceList.iterator();
while (iterator.hasNext()) {
String mgiRef = (String) iterator.next();
targetList.add(Utils.wrap(mgiRef, "\""));
}
}
return targetList;
}
/**
* Save the given biblio record in the database. Transaction management is the caller's
* responsibility.
*
* @param bibliosDAO the data to be saved
*/
@Override
public void save(BibliosDAO bibliosDAO) {
java.sql.Date sqlDate = Utils.tryParseToDbDate(bibliosDAO.getLast_change());
final String statement =
"UPDATE biblios SET title = ?, author1 = ?, author2 = ?, year = ?, journal = ?, username = ?, "
+ "volume = ?, pages = ?, pubmed_id = ?, updated = ?, last_change = ?, notes = ?"
+ "WHERE id_biblio = ?";
jdbcTemplate.update(
statement,
new Object[] {
bibliosDAO.getTitle(),
bibliosDAO.getAuthor1(),
bibliosDAO.getAuthor2(),
bibliosDAO.getYear(),
bibliosDAO.getJournal(),
bibliosDAO.getUsername(),
bibliosDAO.getVolume(),
bibliosDAO.getPages(),
bibliosDAO.getPubmed_id(),
bibliosDAO.getUpdated(),
sqlDate,
bibliosDAO.getNotes(),
bibliosDAO.getId_biblio()
});
}
/**
* Given a <code>Filter</code> object describing any/all of the following fields:
*
* <ul>
* <li>gene chromosome
* <li>gene ID
* <li>gene name
* <li>gene symbol
* <li>MGI reference
* </ul>
*
* , this method performs a query, ANDing all non-empty fields in a WHERE clause against the genes
* table. The result is a <code>List<GenesDAO
* ></code> of qualifying results. A list is always returned, even if there are no results.
*
* @param filter values to filter by
* @return a list of <code>GenesDAO</code>.
*/
public List<GenesDAO> getFilteredGenesList(Filter filter) {
String chromosomeWhere = "";
String geneIdWhere = "";
String geneNameWhere = "";
String geneSymbolWhere = "";
String mgiReferenceWhere = "";
List<GenesDAO> targetList = new ArrayList();
int geneId = -1;
String queryString = "SELECT * FROM genes\nWHERE (1 = 1)";
if ((filter.getChromosome() != null) && (!filter.getChromosome().isEmpty())) {
chromosomeWhere = " AND (chromosome = :chromosome)\n";
queryString += chromosomeWhere;
}
Integer iGeneId = Utils.tryParseInt(filter.getGeneId());
if ((iGeneId != null) && (iGeneId.intValue() > 0)) {
geneId = iGeneId.intValue();
geneIdWhere = " AND (id_gene = :id_gene)\n";
queryString += geneIdWhere;
}
if ((filter.getGeneName() != null) && (!filter.getGeneName().isEmpty())) {
geneNameWhere = " AND (name LIKE :name)\n";
queryString += geneNameWhere;
}
if ((filter.getGeneSymbol() != null) && (!filter.getGeneSymbol().isEmpty())) {
geneSymbolWhere = " AND (symbol LIKE :symbol)\n";
queryString += geneSymbolWhere;
}
if ((filter.getMgiReference() != null) && (!filter.getMgiReference().isEmpty())) {
mgiReferenceWhere = " AND (mgi_ref LIKE :mgi_ref)\n";
queryString += mgiReferenceWhere;
}
queryString += "ORDER BY name\n";
Session session = factory.getCurrentSession();
try {
session.beginTransaction();
SQLQuery query = session.createSQLQuery(queryString);
if (!chromosomeWhere.isEmpty()) query.setParameter("chromosome", filter.getChromosome());
if (!geneIdWhere.isEmpty()) query.setParameter("id_gene", geneId);
if (!geneNameWhere.isEmpty()) query.setParameter("name", "%" + filter.getGeneName() + "%");
if (!geneSymbolWhere.isEmpty())
query.setParameter("symbol", "%" + filter.getGeneSymbol() + "%");
if (!mgiReferenceWhere.isEmpty())
query.setParameter("mgi_ref", "%" + filter.getMgiReference() + "%");
targetList = query.addEntity(GenesDAO.class).list();
session.getTransaction().commit();
} catch (HibernateException e) {
session.getTransaction().rollback();
throw e;
}
return targetList;
}
/**
* Given a <code>List<BibliosDAO></code>, this method returns a new list, which is a copy of
* the original list, with updates applied from the biblios web service for valid pubmed_id
* values.
*
* @param oldBiblioList the original list of biblios
* @return a new list, which is a copy of the original list, with updates applied from the biblios
* web service for valid pubmed_id values
*/
private List<BibliosDAO> createNewBiblioListFromWebService(List<BibliosDAO> oldBiblioList) {
String volume;
List<BibliosDAO> newBiblioList = new ArrayList<>();
DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String last_change = dateFormat.format(new Date());
for (BibliosDAO oldBiblio : oldBiblioList) {
BibliosDAO newBiblio = new BibliosDAO(); // Clone the original BibliosDAO.
newBiblio.setAuthor1(oldBiblio.getAuthor1());
newBiblio.setAuthor2(oldBiblio.getAuthor2());
newBiblio.setId_biblio(oldBiblio.getId_biblio());
newBiblio.setJournal(oldBiblio.getJournal());
newBiblio.setLast_change(last_change);
newBiblio.setNotes(oldBiblio.getNotes());
newBiblio.setPages(oldBiblio.getPages());
newBiblio.setPubmed_id(oldBiblio.getPubmed_id());
newBiblio.setTitle(oldBiblio.getTitle());
newBiblio.setUpdated(oldBiblio.getUpdated());
newBiblio.setUsername(oldBiblio.getUsername());
newBiblio.setVolume(oldBiblio.getVolume());
newBiblio.setYear(oldBiblio.getYear());
Integer pubmed_id =
Utils.tryParseInt(
oldBiblio
.getPubmed_id()); // If we have a valid pubmed_id, load the new BibliosDAO with
// the paper data.
if (pubmed_id != null) {
FetchBiblio paperFromWs = fetchPaper(pubmed_id);
if (paperFromWs != null) {
if (!paperFromWs.issue.isEmpty()) {
volume = paperFromWs.volume + "(" + paperFromWs.issue + ")";
} else {
volume = paperFromWs.volume;
}
newBiblio.setTitle(paperFromWs.title);
newBiblio.setAuthor1(paperFromWs.author1);
newBiblio.setAuthor2(paperFromWs.author2);
newBiblio.setYear("" + paperFromWs.year);
newBiblio.setJournal(paperFromWs.journal);
newBiblio.setVolume(volume);
newBiblio.setPages(paperFromWs.pages);
newBiblio.setUsername("EMMA");
newBiblio.setUpdated("Y");
newBiblioList.add(newBiblio);
}
}
}
return newBiblioList;
}
/**
* Returns a <code>List<String></code> of distinct gene ids suitable for autocomplete
* sourcing.
*
* @return a <code>List<String></code> of gene ids suitable for autocomplete sourcing.
*/
public List<String> getGeneIds() {
List<GenesDAO> genesDAOList = getGenes();
List<String> targetList = new ArrayList();
if (genesDAOList != null) {
for (GenesDAO genesDAO : genesDAOList) {
String sId_gene = Integer.toString(genesDAO.getId_gene());
targetList.add(Utils.wrap(sId_gene, "\""));
}
}
logger.debug("targetList count = " + targetList.size());
return targetList;
}
/**
* Saves the given <code>GenesDAO</code> gene
*
* @param gene the <code>GenesDAO</code> to be saved
*/
public void save(GenesDAO gene) {
Session session = factory.getCurrentSession();
Integer centimorgan = Utils.tryParseInt(gene.getCentimorgan());
gene.setCentimorgan(
centimorgan == null
? null
: centimorgan
.toString()); // Centimorgans are numeric, nullable in the database, so re-map any
// non-numeric values to null.
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String currentTimestamp = dateFormat.format(new Date());
gene.setLast_change(currentTimestamp);
gene.setUsername("EMMA");
try {
session.beginTransaction();
session.saveOrUpdate(gene);
session.getTransaction().commit();
} catch (HibernateException e) {
session.getTransaction().rollback();
throw e;
}
}
/**
* Returns a <code>List<String></code> of distinct gene symbols suitable for autocomplete
* sourcing.
*
* @return a <code>List<String></code> of distinct gene symbols suitable for autocomplete
* sourcing.
*/
public List<String> getSymbols() {
List<String> targetList = new ArrayList();
Session session = factory.getCurrentSession();
List sourceList = null;
try {
session.beginTransaction();
sourceList = session.createSQLQuery("SELECT DISTINCT symbol FROM genes").list();
session.getTransaction().commit();
} catch (HibernateException e) {
session.getTransaction().rollback();
throw e;
}
if (sourceList != null) {
Iterator iterator = sourceList.iterator();
while (iterator.hasNext()) {
String symbol = (String) iterator.next();
targetList.add(Utils.wrap(symbol, "\""));
}
}
return targetList;
}
