Example usage for org.hibernate SQLQuery setString

Introduction

In this page you can find the example usage for org.hibernate SQLQuery setString.

Prototype

@Deprecated
@SuppressWarnings("unchecked")
default Query<R> setString(int position, String val) 

Source Link

Document

Bind a positional String-valued parameter.

Usage

From source file:edu.vt.vbi.patric.dao.DBDisease.java

License:Apache License

public List<ResultType> getCTDGADGraphData(String ncbi_taxon_id) {

    String sql = "";

    sql += "select distinct z.gene_sym gene_sym, z.gene_name gene_name,  z.disease_id disease_id, z.gene_disease_rel evidence, z.pubmed_ids pubmed "
            + "    from (select distinct pd.disease_id disease_id, pd.disease_name disease_name "
            + "   from sres.taxon t, diseasedb.pathogen_disease pd " + "   where t.ncbi_tax_id = pd.taxon_id "
            + "   and t.taxon_id in  "
            + "   (select distinct taxon_id from sres.taxon  connect by prior taxon_id = parent_id start with ncbi_tax_id = ?) "
            + "   ) x, diseasedb.ctd_summary z " + "   where x.disease_name = z.disease_name ";

    Session session = factory.getCurrentSession();
    session.beginTransaction();/*from   w  ww.ja v  a 2  s .co m*/
    SQLQuery q = session.createSQLQuery(sql);
    q.setString(0, ncbi_taxon_id);
    List rset = q.list();
    session.getTransaction().commit();

    List<ResultType> results = new ArrayList<>();

    for (Object aRset1 : rset) {
        Object[] obj = (Object[]) aRset1;
        ResultType row = new ResultType();
        row.put("gene_sym", obj[0]);
        row.put("gene_name", obj[1]);
        row.put("disease_id", obj[2]);
        row.put("evidence", obj[3]);
        row.put("pubmed", obj[4]);

        results.add(row);
    }

    sql = "select distinct z.gene_sym gene_sym, z.gene_name gene_name,  x.disease_id disease_id, z.pubmed_id pubmed "
            + "    from (select distinct pd.disease_id disease_id, pd.disease_name disease_name "
            + "   from sres.taxon t, diseasedb.pathogen_disease pd " + "   where t.ncbi_tax_id = pd.taxon_id "
            + "   and t.taxon_id in  "
            + "   (select distinct taxon_id from sres.taxon  connect by prior taxon_id = parent_id start with ncbi_tax_id = ?) "
            + "   ) x, diseasedb.gad_summary z " + "   where x.disease_name = z.disease_name";

    session = factory.getCurrentSession();
    session.beginTransaction();
    q = session.createSQLQuery(sql);
    q.setString(0, ncbi_taxon_id);
    rset = q.list();
    session.getTransaction().commit();

    for (Object aRset : rset) {
        Object[] obj = (Object[]) aRset;
        ResultType row = new ResultType();
        row.put("gene_sym", obj[0]);
        row.put("gene_name", obj[1]);
        row.put("disease_id", obj[2]);
        row.put("evidence", "gad");
        row.put("pubmed", obj[3]);

        results.add(row);
    }

    return results;

}

---

From source file:edu.vt.vbi.patric.dao.DBDisease.java

License:Apache License

public List<ResultType> getVFDBGraphData(String ncbi_taxon_id) {

    String sql = "";

    sql += "   select vf.ncbi_tax_id, vf.rank, vf.parent_id, df.genome_name, df.accession, vf.na_feature_id, df.source_id, df.product, vf.vfg_id, vf.vf_id, vf.gene_name "
            + "   from diseasedb.vf_summary vf, app.dnafeature df " + "   where vf.ncbi_tax_id in ( "
            + "   select ncbi_tax_id " + "   from sres.taxon " + "   connect by prior taxon_id = parent_id "
            + "   start with ncbi_tax_id = ? " + "   )and vf.na_feature_id = df.na_feature_id"
            + "   and vf.algorithm = 'ID Mapping'";

    Session session = factory.getCurrentSession();
    session.beginTransaction();//from  w  w w  .  jav  a2  s.co  m
    SQLQuery q = session.createSQLQuery(sql);
    q.setString(0, ncbi_taxon_id);
    List rset = q.list();
    session.getTransaction().commit();

    List<ResultType> results = new ArrayList<>();

    for (Object aRset : rset) {
        Object[] obj = (Object[]) aRset;
        ResultType row = new ResultType();
        row.put("ncbi_tax_id", obj[0]);
        row.put("rank", obj[1]);
        row.put("parent_id", obj[2]);
        row.put("genome_name", obj[3]);
        row.put("accession", obj[4]);
        row.put("na_feature_id", obj[5]);
        row.put("source_id", obj[6]);
        row.put("product", obj[7]);
        row.put("vfg_id", obj[8]);
        row.put("vf_id", obj[9]);
        row.put("gene_name", obj[10]);

        results.add(row);
    }

    return results;
}

---

From source file:edu.vt.vbi.patric.dao.DBPRC.java

License:Apache License

public int getPRCCount(String taxonId, String filter) {

    String sql = "select count(*) cnt from (select distinct experiment_id, description, speciesname, processing_type, summary, pubmed_id, count(distinct sample_id) from app.post_genomic";
    if (filter.equals("MS"))
        sql += " where processing_type = 'Mass spectrometry'";
    else if (filter.equals("MA"))
        sql += " where processing_type = 'Microarray'";
    else//from   w w w.ja  v  a  2  s .co m
        sql += " where processing_type = 'Protein interaction'";
    sql += " and taxon_id in (" + DBSummary.getTaxonIdsInTaxonSQL(":taxonId") + ")";
    sql += " group by experiment_id, description, speciesname, processing_type, summary, pubmed_id)";

    Session session = factory.getCurrentSession();
    session.beginTransaction();
    SQLQuery q = session.createSQLQuery(sql).addScalar("cnt", Hibernate.INTEGER);
    q.setCacheable(true);
    q.setTimeout(SQL_TIMEOUT);
    q.setString("taxonId", taxonId);

    Object obj = q.uniqueResult();
    session.getTransaction().commit();

    return Integer.parseInt(obj.toString());
}

---

From source file:edu.vt.vbi.patric.dao.DBPRC.java

License:Apache License

public ArrayList<ResultType> getPRCData(String taxonId, String filter, int start, int end, String sort,
        String dir) {//w  w  w .j a  va2s .c o  m

    String sql = "select distinct experiment_id, description, speciesname, processing_type, summary, pubmed_id, count(distinct sample_id) from app.post_genomic";
    if (filter.equals("MS"))
        sql += " where processing_type = 'Mass spectrometry'";
    else if (filter.equals("MA"))
        sql += " where processing_type = 'Microarray'";
    else
        sql += " where processing_type = 'Protein interaction'";
    sql += " and taxon_id in (" + DBSummary.getTaxonIdsInTaxonSQL(":taxonId") + ")";
    sql += " group by experiment_id, description, speciesname, processing_type, summary, pubmed_id";
    sql += " order by " + sort + " " + dir;

    Session session = factory.getCurrentSession();
    session.beginTransaction();
    SQLQuery q = session.createSQLQuery(sql);

    q.setTimeout(SQL_TIMEOUT);
    q.setString("taxonId", taxonId);

    ScrollableResults scr = null;

    try {
        scr = q.scroll();
    } catch (Exception ex) {
        System.out.println("[SQL error]" + taxonId);
        ex.printStackTrace();
        return null;
    }

    ArrayList<ResultType> results = new ArrayList<ResultType>();
    Object[] obj = null;
    if (start > 1) {
        scr.setRowNumber(start - 1);
    } else {
        scr.beforeFirst();
    }

    for (int i = start; (end > 0 && i < end && scr.next() == true) || (end == -1 && scr.next() == true); i++) {
        obj = scr.get();
        ResultType row = new ResultType();

        row.put("experiment_id", obj[0]);
        row.put("description", obj[1]);
        row.put("speciesname", obj[2]);
        row.put("experimenttype", obj[3]);
        row.put("summary", obj[4]);
        row.put("pubmed_id", obj[5]);
        row.put("samples", obj[6]);
        results.add(row);
    }
    session.getTransaction().commit();
    return results;
}

---

From source file:edu.vt.vbi.patric.dao.DBSummary.java

License:Apache License

/**
 * Retrieves RNA detail info. This query on app.dnafeature and dots.nafeaturecomment tables.
 * /*from ww  w.j  ava2  s .co  m*/
 * @param id na_feature_id
 * @return RNA info (na_feature_id, gene, label, anticodon, product, comment_string)
 */
public ResultType getRNAInfo(String id) {
    String sql = "select nf.na_feature_id, nf.gene, nf.label, nf.anticodon, nf.product, nfc.comment_string "
            + "   from app.dnafeature nf, dots.nafeaturecomment nfc where nf.na_feature_id = ? "
            + "      and nf.na_feature_id = nfc.na_feature_id(+)";

    Session session = factory.getCurrentSession();
    session.beginTransaction();
    SQLQuery q = session.createSQLQuery(sql);
    q.setString(0, id);

    ResultType result = new ResultType();
    for (Object aRset : q.list()) {
        Object[] obj = (Object[]) aRset;

        result.put("na_feature_id", obj[0]);
        result.put("gene", obj[1]);
        result.put("label", obj[2]);
        result.put("anticodon", obj[3]);
        result.put("product", obj[4]);

        try {
            SerializableClob clobComment = (SerializableClob) obj[5];
            String strComment = IOUtils.toString(clobComment.getAsciiStream(), "UTF-8");
            result.put("comment_string", strComment);
        } catch (NullPointerException ex) {
            // this can be null
        } catch (Exception ex) {
            LOGGER.error("Problem in retrieving comments for RNA: {}", ex.getMessage(), ex);
        }
    }
    return result;
}

---

From source file:edu.vt.vbi.patric.dao.DBSummary.java

License:Apache License

/**
 * Identifies species that match PRIDE database for a given taxon. This is used for Experiment data API call.
 * //w  w  w  .j a v  a2s  . c o m
 * @param id ncbi_tax_id
 * @return list of species name
 */
public String getPRIDESpecies(String id) {
    String sql = "select pr.species, pr.ncbi_tax_id from app.pride pr, ("
            + getTaxonIdsInTaxonSQL(":ncbi_taxon_id") + ") tx " + "   where pr.ncbi_tax_id = tx.ncbi_tax_id ";

    Session session = factory.getCurrentSession();
    session.beginTransaction();
    SQLQuery q = session.createSQLQuery(sql);
    q.setString("ncbi_taxon_id", id);

    List<?> rset = q.list();
    session.getTransaction().commit();

    StringBuilder results = new StringBuilder();
    Object[] obj = null;
    for (Object aRset : rset) {
        obj = (Object[]) aRset;
        if (results.length() > 0) {
            results.append("," + obj[0].toString());
        } else {
            results.append(obj[0].toString());
        }
    }

    return results.toString();
}

---

From source file:edu.vt.vbi.patric.dao.DBSummary.java

License:Apache License

/**
 * Finds taxonomy rank "Genus" for a given taxon node.
 * /*from   w ww.  j av a  2 s  . co  m*/
 * @param refseq_locus_tag RefSeq Locus Tag
 * @return comments
 */
public List<Map<String, Object>> getTBAnnotation(String refseq_locus_tag) {
    String sql = "select distinct locus_tag, property, value, evidence_code, comments, source"
            + "   from app.tbcap_annotation "
            + "   where locus_tag = :refseq_locus_tag and property != 'Interaction'"
            + "   order by property asc, evidence_code asc ";

    Session session = factory.getCurrentSession();
    session.beginTransaction();
    SQLQuery q = session.createSQLQuery(sql);
    q.setString("refseq_locus_tag", refseq_locus_tag);

    q.addScalar("locus_tag", Hibernate.STRING).addScalar("property", Hibernate.STRING).addScalar("value",
            Hibernate.STRING);
    q.addScalar("evidence_code", Hibernate.STRING).addScalar("comments", Hibernate.STRING).addScalar("source",
            Hibernate.STRING);

    List<Object[]> rset = q.list();

    List<Map<String, Object>> results = new ArrayList<>();
    for (Object[] obj : rset) {

        Map<String, Object> row = new HashMap<>();
        row.put("locus", obj[0]);
        row.put("property", obj[1]);
        row.put("value", obj[2]);
        row.put("evidencecode", obj[3]);
        row.put("comment", obj[4]);
        row.put("source", obj[5]);

        results.add(row);
    }

    // get Interactions
    sql = "select distinct locus_tag, property, value, evidence_code, comments, source"
            + "   from app.tbcap_annotation "
            + "   where locus_tag = :refseq_locus_tag and property = 'Interaction' "
            + "   order by value asc, evidence_code asc ";

    q = session.createSQLQuery(sql);
    q.setString("refseq_locus_tag", refseq_locus_tag);

    q.addScalar("locus_tag", Hibernate.STRING).addScalar("property", Hibernate.STRING).addScalar("value",
            Hibernate.STRING);
    q.addScalar("evidence_code", Hibernate.STRING).addScalar("comments", Hibernate.STRING).addScalar("source",
            Hibernate.STRING);

    rset = q.list();

    for (Object[] obj : rset) {

        Map<String, Object> row = new HashMap<>();
        row.put("locus", obj[0]);
        row.put("property", obj[1]);
        row.put("value", obj[2]);
        row.put("evidencecode", obj[3]);
        row.put("comment", obj[4]);
        row.put("source", obj[5]);

        results.add(row);
    }

    return results;
}

---

From source file:edu.wustl.cab2b.server.user.UserOperations.java

License:BSD License

/**
 * This method returns user from database with given user name
 * @param name user name/*w w  w  .java  2  s.c om*/
 * @return User
 */
public UserInterface getUserByName(String value) {
    List<UserInterface> userList = null;

    final String queryStr = "Select {User.*} from cab2b_user User where name COLLATE latin1_bin=?";
    try {
        SQLQuery sqlQuery = DBUtil.currentSession().createSQLQuery(queryStr);
        sqlQuery.setString(0, value);
        userList = sqlQuery.addEntity("User", edu.wustl.cab2b.common.user.User.class).list();
    } catch (HibernateException hbe) {
        logger.error(hbe.getMessage(), hbe);
        throw new RuntimeException("Error occurred while fetching User", ErrorCodeConstants.UR_0003);
    } finally {
        DBUtil.closeSession();
    }
    UserInterface user = null;
    if (userList != null && !userList.isEmpty()) {
        user = userList.get(0);
    }
    return user;
}

---

From source file:es.emergya.bbdd.dao.HistoricoGPSHome.java

License:Open Source License

/**
 * Dado una lista de zonas y un usuario, devuelve la lista de recursos que
 * el que usuario puede ver por su rol y cuya ltima posicin en la base de
 * datos est en alguna de las zonas pasadas.
 * /*from   www.  ja v a2s .  c  o m*/
 * @param zonas
 * @param u
 * @return
 */
@Transactional(propagation = Propagation.REQUIRES_NEW, readOnly = true, rollbackFor = Throwable.class)
private List<String> calculateRecursosUltimasPosiciones(Usuario u) {
    int i;

    StringBuffer sb = new StringBuffer();
    sb.append("select distinct r.nombre as nombreRecurso ")
            .append("from recursos r inner join flotas f on r.flota_x_flota=f.x_flota ")
            .append("inner join roles_x_flotas rxf on rxf.x_flota = f.x_flota ")
            .append("inner join roles rol on rxf.x_rol=rol.x_rol ")
            .append("inner join usuarios u on u.fk_roles = rol.x_rol ")
            .append("inner join historico_gps h on r.fk_historico_gps = h.x_historico ");

    sb.append("and u.nombre_usuario=:USUARIO ");

    sb.append("order by nombreRecurso");

    SQLQuery q = getSession().createSQLQuery(sb.toString());

    q.addScalar("nombreRecurso", Hibernate.STRING);
    q.setString("USUARIO", u.getNombreUsuario());
    if (log.isDebugEnabled()) {
        log.debug(sb.toString());
    }

    List<String> result = q.list();
    return result;
}

---

From source file:fr.gael.dhus.database.dao.FileScannerDao.java

License:Open Source License

public int deleteCollectionReferences(final Collection collection) {
    return getHibernateTemplate().execute(new HibernateCallback<Integer>() {
        public Integer doInHibernate(Session session) throws HibernateException, SQLException {
            String sql = "DELETE FROM FILESCANNER_COLLECTIONS s " + " WHERE s.COLLECTIONS_UUID = :cid";
            SQLQuery query = session.createSQLQuery(sql);
            query.setString("cid", collection.getUUID());
            return query.executeUpdate();
        }/* ww  w.  jav  a 2 s  . com*/
    });
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• »