Examples with NodeSearch mom.trd.opentheso.bdd.helper.nodes.search.NodeSearch used on opensource projects

Example 11 with NodeSearch

use of mom.trd.opentheso.bdd.helper.nodes.search.NodeSearch in project opentheso by miledrousset.

the class SearchHelper method searchNotation.

/**
 * Cette fonction permet de faire une recherche par notation des Concepts uniquement
 *
 * @param ds
 * @param value
 * @param idLang
 * @param idThesaurus
 * @param idGroup
 * @return
 */
public ArrayList<NodeSearch> searchNotation(HikariDataSource ds, String value, String idLang, String idThesaurus, String idGroup) {
    Connection conn;
    Statement stmt;
    ResultSet resultSet;
    ArrayList<NodeSearch> nodeSearchList = null;
    value = new StringPlus().convertString(value);
    String query;
    String lang;
    String group;
    String notation = " and unaccent_string(concept.notation) ilike " + "unaccent_string('%" + value + "%')";
    // préparation de la requête en focntion du choix (toutes les langues ou langue donnée)
    if (idLang.isEmpty()) {
        lang = "";
    }
    // cas du choix d'un group
    if (idGroup.isEmpty()) {
        group = "";
    } else {
        group = " and idgroup = '" + idGroup + "'";
    }
    try {
        conn = ds.getConnection();
        try {
            stmt = conn.createStatement();
            try {
                query = "SELECT concept.id_concept, concept.id_thesaurus," + " concept.top_concept, idgroup," + " concept.notation " + " FROM concept " + "JOIN concept_group_concept ON concept.id_concept = concept_group_concept.idconcept " + "AND concept.id_thesaurus = concept_group_concept.idthesaurus " + " WHERE" + " concept.id_thesaurus = '" + idThesaurus + "'" + notation + group + " order by notation ASC LIMIT 200";
                resultSet = stmt.executeQuery(query);
                nodeSearchList = new ArrayList();
                while (resultSet.next()) {
                    NodeSearch nodeSearch = new NodeSearch();
                    nodeSearch.setLexical_value(resultSet.getString("notation"));
                    nodeSearch.setIdConcept(resultSet.getString("id_concept"));
                    // nodeSearch.setIdTerm(resultSet.getString("id_term"));
                    nodeSearch.setIdGroup(resultSet.getString("idgroup"));
                    nodeSearch.setIdLang(idLang);
                    nodeSearch.setIdThesaurus(idThesaurus);
                    nodeSearch.setTopConcept(resultSet.getBoolean("top_concept"));
                    nodeSearch.setPreferredLabel(true);
                    nodeSearchList.add(nodeSearch);
                }
            } finally {
                stmt.close();
            }
        } finally {
            conn.close();
        }
    } catch (SQLException ex) {
        Logger.getLogger(SearchHelper.class.getName()).log(Level.SEVERE, null, ex);
    }
    return nodeSearchList;
}

Example 12 with NodeSearch

use of mom.trd.opentheso.bdd.helper.nodes.search.NodeSearch in project opentheso by miledrousset.

the class SearchHelper method searchIdTerm.

/**
 * Cette fonction permet de faire une recherche par idTerme
 *
 * @param ds
 * @param id
 * @param idThesaurus
 * @param idLang
 * @return
 */
public ArrayList<NodeSearch> searchIdTerm(HikariDataSource ds, String id, String idThesaurus, String idLang) {
    Connection conn;
    Statement stmt;
    ResultSet resultSet;
    ArrayList<NodeSearch> nodeSearchList = null;
    try {
        conn = ds.getConnection();
        try {
            stmt = conn.createStatement();
            try {
                String query = "SELECT term.lexical_value, preferred_term.id_concept," + " term.lang, term.id_thesaurus," + " idgroup, concept.top_concept" + " FROM term, preferred_term, concept,concept_group_concept WHERE " + " concept_group_concept.idthesaurus = term.id_thesaurus AND " + "concept_group_concept.idconcept =preferred_term.id_concept AND" + " concept.id_concept = preferred_term.id_concept AND" + " concept.id_thesaurus = preferred_term.id_thesaurus AND" + " preferred_term.id_term = term.id_term AND" + " preferred_term.id_thesaurus = term.id_thesaurus" + " and term.id_term = '" + id + "'" + " and term.id_thesaurus = '" + idThesaurus + "'" + " and term.lang = '" + idLang + "'" + " order by lexical_value ASC LIMIT 100";
                resultSet = stmt.executeQuery(query);
                nodeSearchList = new ArrayList<>();
                while (resultSet.next()) {
                    NodeSearch nodeSearch = new NodeSearch();
                    nodeSearch.setLexical_value(resultSet.getString("lexical_value"));
                    nodeSearch.setIdConcept(resultSet.getString("id_concept"));
                    nodeSearch.setIdTerm(id);
                    nodeSearch.setIdGroup(resultSet.getString("idgroup"));
                    nodeSearch.setIdLang(idLang);
                    nodeSearch.setIdThesaurus(idThesaurus);
                    nodeSearch.setTopConcept(resultSet.getBoolean("top_concept"));
                    nodeSearch.setPreferredLabel(true);
                    nodeSearchList.add(nodeSearch);
                }
            } finally {
                stmt.close();
            }
        } finally {
            conn.close();
        }
    } catch (SQLException ex) {
        Logger.getLogger(SearchHelper.class.getName()).log(Level.SEVERE, null, ex);
    }
    return nodeSearchList;
}

Example 13 with NodeSearch

use of mom.trd.opentheso.bdd.helper.nodes.search.NodeSearch in project opentheso by miledrousset.

the class SearchHelper method searchTerm.

/**
 * Cette fonction permet de faire une recherche par value sur les termes
 * Préférés et les synonymes (la recherche porte sur les termes contenus dans une chaine)
 * exp : la recherche de "ceramiqu four" trouve la chaine (four à céramique)
 *
 * @param ds
 * @param value
 * @param idLang
 * @param idThesaurus
 * @param idGroup
 * @param startByOrContain // 1=contient 2=commence par
 * @param withNote
 * @return
 */
public ArrayList<NodeSearch> searchTerm(HikariDataSource ds, String value, String idLang, String idThesaurus, String idGroup, int startByOrContain, boolean withNote) {
    Connection conn;
    Statement stmt;
    ResultSet resultSet;
    ArrayList<NodeSearch> nodeSearchList = null;
    value = new StringPlus().convertString(value);
    String[] values = value.trim().split(" ");
    String query;
    String lang;
    String langSynonyme;
    String langNote;
    String group;
    String multivaluesTerm = "";
    String multivaluesSynonyme = "";
    String multivaluesNote = "";
    String notation = "";
    // préparation de la valeur à rechercher
    if (startByOrContain == 1) {
        // contient
        for (String value1 : values) {
            multivaluesTerm += " and unaccent_string(term.lexical_value) ilike" + " unaccent_string('%" + value1 + "%')";
            multivaluesSynonyme += " and unaccent_string(non_preferred_term.lexical_value) ilike" + " unaccent_string('%" + value1 + "%')";
            multivaluesNote += " and unaccent_string(note.lexicalvalue) ilike" + " unaccent_string('%" + value1 + "%')";
            notation = " and unaccent_string(concept.notation) ilike " + "unaccent_string('%" + value + "%')";
        }
    }
    if (startByOrContain == 2) {
        // commence par
        multivaluesTerm = " and (unaccent_string(term.lexical_value) ilike" + " unaccent_string('" + value + "%')" + " OR unaccent_string(term.lexical_value) ilike" + " unaccent_string('% " + value + "%'))";
        multivaluesSynonyme = " and (unaccent_string(non_preferred_term.lexical_value) ilike" + " unaccent_string('" + value + "%')" + " OR unaccent_string(non_preferred_term.lexical_value) ilike" + " unaccent_string('% " + value + "%'))";
        multivaluesNote = " and (unaccent_string(note.lexicalvalue) ilike" + " unaccent_string('" + value + "%')" + " OR unaccent_string(note.lexicalvalue) ilike" + " unaccent_string('% " + value + "%'))";
        notation = " and unaccent_string(concept.notation) ilike " + "unaccent_string('%" + value + "%')";
    }
    // préparation de la requête en focntion du choix (toutes les langues ou langue donnée)
    if (idLang.isEmpty()) {
        lang = "";
        langSynonyme = "";
        langNote = "";
    } else {
        lang = " and term.lang ='" + idLang + "'";
        langSynonyme = " and non_preferred_term.lang ='" + idLang + "'";
        langNote = " and note.lang ='" + idLang + "'";
    }
    // cas du choix d'un group
    if (idGroup.isEmpty()) {
        group = "";
    } else {
        group = " and idgroup = '" + idGroup + "'";
    }
    try {
        conn = ds.getConnection();
        try {
            stmt = conn.createStatement();
            try {
                query = "SELECT term.lexical_value, preferred_term.id_concept," + " preferred_term.id_term, term.lang, term.id_thesaurus," + " idgroup, concept.top_concept" + " FROM term, preferred_term, concept,concept_group_concept WHERE " + "concept_group_concept.idthesaurus  = term.id_thesaurus AND " + "concept_group_concept.idconcept = preferred_term.id_concept AND" + " concept.id_concept = preferred_term.id_concept AND" + " concept.id_thesaurus = preferred_term.id_thesaurus AND" + " preferred_term.id_term = term.id_term AND" + " preferred_term.id_thesaurus = term.id_thesaurus" + multivaluesTerm + " and term.id_thesaurus = '" + idThesaurus + "'" + lang + group + " order by lexical_value ASC LIMIT 200";
                resultSet = stmt.executeQuery(query);
                nodeSearchList = new ArrayList<>();
                while (resultSet.next()) {
                    NodeSearch nodeSearch = new NodeSearch();
                    nodeSearch.setLexical_value(resultSet.getString("lexical_value"));
                    nodeSearch.setIdConcept(resultSet.getString("id_concept"));
                    nodeSearch.setIdTerm(resultSet.getString("id_term"));
                    nodeSearch.setIdGroup(resultSet.getString("idgroup"));
                    nodeSearch.setIdLang(resultSet.getString("lang"));
                    nodeSearch.setIdThesaurus(idThesaurus);
                    nodeSearch.setTopConcept(resultSet.getBoolean("top_concept"));
                    nodeSearch.setPreferredLabel(true);
                    // cas où le terme recherché est égal au terme retrouvé, on le place en premier
                    if (value.trim().equalsIgnoreCase(nodeSearch.getLexical_value().trim())) {
                        nodeSearchList.add(0, nodeSearch);
                    } else {
                        nodeSearchList.add(nodeSearch);
                    }
                }
                /**
                 * recherche de Synonymes
                 */
                query = "SELECT non_preferred_term.id_term, non_preferred_term.lang," + " non_preferred_term.lexical_value, " + " idgroup, preferred_term.id_concept," + " concept.top_concept" + " FROM non_preferred_term, preferred_term,concept_group_concept, concept WHERE " + "concept_group_concept.idconcept = non_preferred_term.id_term AND " + "concept_group_concept.idthesaurus = preferred_term.id_thesaurus AND " + " preferred_term.id_term = non_preferred_term.id_term AND" + " preferred_term.id_thesaurus = non_preferred_term.id_thesaurus AND" + " concept.id_concept = preferred_term.id_concept AND" + " concept.id_thesaurus = preferred_term.id_thesaurus" + multivaluesSynonyme + " and non_preferred_term.id_thesaurus = '" + idThesaurus + "'" + langSynonyme + group + " order by lexical_value ASC LIMIT 200";
                resultSet = stmt.executeQuery(query);
                while (resultSet.next()) {
                    NodeSearch nodeSearch = new NodeSearch();
                    nodeSearch.setLexical_value(resultSet.getString("lexical_value"));
                    nodeSearch.setIdConcept(resultSet.getString("id_concept"));
                    nodeSearch.setIdTerm(resultSet.getString("id_term"));
                    nodeSearch.setIdGroup(resultSet.getString("idgroup"));
                    nodeSearch.setIdLang(resultSet.getString("lang"));
                    nodeSearch.setIdThesaurus(idThesaurus);
                    nodeSearch.setTopConcept(resultSet.getBoolean("top_concept"));
                    nodeSearch.setPreferredLabel(false);
                    // cas où le terme recherché est égal au terme retrouvé, on le place en premier
                    if (value.trim().equalsIgnoreCase(nodeSearch.getLexical_value().trim())) {
                        nodeSearchList.add(0, nodeSearch);
                    } else {
                        nodeSearchList.add(nodeSearch);
                    }
                }
                /**
                 * recherche aussi dans les notes
                 */
                if (withNote) {
                    query = "SELECT " + " concept.id_concept, concept.id_thesaurus," + " concept.top_concept, concept_group_concept.idgroup," + " note.lang, note.lexicalvalue," + " note.id_term " + " FROM term, preferred_term, note, concept,concept_group_concept" + " WHERE" + "  term.id_thesaurus = note.id_thesaurus AND" + "  term.lang = note.lang AND" + "  (term.id_term = note.id_term OR preferred_term.id_concept = note.id_concept) AND" + "  preferred_term.id_term = term.id_term AND" + "  preferred_term.id_thesaurus = term.id_thesaurus AND" + "  concept_group_concept.idthesaurus = concept.id_thesaurus AND" + "  concept_group_concept.idconcept = concept.id_concept AND" + "  concept.id_concept = preferred_term.id_concept AND" + "  concept.id_thesaurus = preferred_term.id_thesaurus AND" + " note.id_thesaurus = '" + idThesaurus + "'" + multivaluesNote + langNote + group + " order by lexicalvalue ASC LIMIT 200";
                    resultSet = stmt.executeQuery(query);
                    while (resultSet.next()) {
                        NodeSearch nodeSearch = new NodeSearch();
                        nodeSearch.setLexical_value(resultSet.getString("lexicalvalue"));
                        nodeSearch.setIdConcept(resultSet.getString("id_concept"));
                        nodeSearch.setIdTerm(resultSet.getString("id_term"));
                        nodeSearch.setIdGroup(resultSet.getString("idgroup"));
                        nodeSearch.setIdLang(resultSet.getString("lang"));
                        nodeSearch.setIdThesaurus(idThesaurus);
                        nodeSearch.setTopConcept(resultSet.getBoolean("top_concept"));
                        nodeSearch.setPreferredLabel(true);
                        nodeSearchList.add(nodeSearch);
                    }
                }
                /**
                 * recherche aussi dans les notations
                 */
                if (withNote) {
                    query = "SELECT concept.id_concept, concept.id_thesaurus," + " concept.top_concept, idgroup," + " concept.notation " + " FROM concept JOIN concept_group_concept ON concept.id_concept = concept_group_concept.idconcept " + "AND concept.id_thesaurus = concept_group_concept.idthesaurus" + " WHERE" + " concept.id_thesaurus = '" + idThesaurus + "'" + notation + group + " order by notation ASC LIMIT 200";
                    resultSet = stmt.executeQuery(query);
                    while (resultSet.next()) {
                        NodeSearch nodeSearch = new NodeSearch();
                        nodeSearch.setLexical_value(resultSet.getString("notation"));
                        nodeSearch.setIdConcept(resultSet.getString("id_concept"));
                        // nodeSearch.setIdTerm(resultSet.getString("id_term"));
                        nodeSearch.setIdGroup(resultSet.getString("idgroup"));
                        nodeSearch.setIdLang(idLang);
                        nodeSearch.setIdThesaurus(idThesaurus);
                        nodeSearch.setTopConcept(resultSet.getBoolean("top_concept"));
                        nodeSearch.setPreferredLabel(true);
                        nodeSearchList.add(nodeSearch);
                    }
                }
            } finally {
                stmt.close();
            }
        } finally {
            conn.close();
        }
    } catch (SQLException ex) {
        Logger.getLogger(SearchHelper.class.getName()).log(Level.SEVERE, null, ex);
    }
    return nodeSearchList;
}