Search in sources :

Example 61 with StringPlus

use of mom.trd.opentheso.bdd.tools.StringPlus in project opentheso by miledrousset.

the class NoteHelper method addConceptNote.

/**
 * Cette fonction permet d'ajouter une Note à un concept instert dans la
 * table Note
 *
 * @param ds
 * @param idConcept
 * @param idLang
 * @param idThesaurus
 * @param note
 * @param noteTypeCode
 * @param idUser
 * @return
 */
public boolean addConceptNote(HikariDataSource ds, String idConcept, String idLang, String idThesaurus, String note, String noteTypeCode, int idUser) {
    Connection conn;
    Statement stmt;
    boolean status = false;
    note = new StringPlus().convertString(note);
    try {
        // Get connection from pool
        conn = ds.getConnection();
        try {
            stmt = conn.createStatement();
            try {
                String query = "Insert into note " + "(notetypecode, id_thesaurus, id_concept, lang, lexicalvalue)" + " values (" + "'" + noteTypeCode + "'" + ",'" + idThesaurus + "'" + ",'" + idConcept + "'" + ",'" + idLang + "'" + ",'" + note + "')";
                stmt.executeUpdate(query);
                status = true;
            } finally {
                stmt.close();
            }
        } finally {
            conn.close();
        }
    } catch (SQLException sqle) {
        // Log exception
        log.error("Error while adding Note of Concept : " + idConcept, sqle);
    }
    addConceptNoteHistorique(ds, idConcept, idLang, idThesaurus, note, noteTypeCode, idUser);
    return status;
}
Also used : SQLException(java.sql.SQLException) Statement(java.sql.Statement) StringPlus(mom.trd.opentheso.bdd.tools.StringPlus) Connection(java.sql.Connection)

Example 62 with StringPlus

use of mom.trd.opentheso.bdd.tools.StringPlus in project opentheso by miledrousset.

the class FacetHelper method addFacetTraduction.

/**
 *  Cette fonction permet de rajouter une traduction à une facet existante.
 * @param ds
 * @param idFacet
 * @param idThesaurus
 * @param lexicalValue
 * @param idLang
 * @return
 */
public boolean addFacetTraduction(HikariDataSource ds, int idFacet, String idThesaurus, String lexicalValue, String idLang) {
    Connection conn;
    Statement stmt;
    boolean status = false;
    lexicalValue = new StringPlus().convertString(lexicalValue);
    try {
        // Get connection from pool
        conn = ds.getConnection();
        try {
            stmt = conn.createStatement();
            try {
                String query = "Insert into node_label " + "(facet_id, id_thesaurus, lexical_value, lang)" + " values (" + idFacet + ",'" + idThesaurus + "'" + ",'" + lexicalValue + "'" + ",'" + idLang + "')";
                stmt.executeUpdate(query);
                status = true;
            } finally {
                stmt.close();
            }
        } finally {
            conn.close();
        }
    } catch (SQLException sqle) {
        // Log exception
        if (!sqle.getMessage().contains("duplicate key value violates unique constraint")) {
            log.error("Error while adding traduction of Facet : " + idFacet, sqle);
        }
    }
    return status;
}
Also used : SQLException(java.sql.SQLException) Statement(java.sql.Statement) StringPlus(mom.trd.opentheso.bdd.tools.StringPlus) Connection(java.sql.Connection)

Example 63 with StringPlus

use of mom.trd.opentheso.bdd.tools.StringPlus 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;
}
Also used : SQLException(java.sql.SQLException) Statement(java.sql.Statement) StringPlus(mom.trd.opentheso.bdd.tools.StringPlus) Connection(java.sql.Connection) ResultSet(java.sql.ResultSet) ArrayList(java.util.ArrayList) NodeSearch(mom.trd.opentheso.bdd.helper.nodes.search.NodeSearch)

Example 64 with StringPlus

use of mom.trd.opentheso.bdd.tools.StringPlus 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;
}
Also used : SQLException(java.sql.SQLException) Statement(java.sql.Statement) StringPlus(mom.trd.opentheso.bdd.tools.StringPlus) Connection(java.sql.Connection) ResultSet(java.sql.ResultSet) NodeSearch(mom.trd.opentheso.bdd.helper.nodes.search.NodeSearch)

Example 65 with StringPlus

use of mom.trd.opentheso.bdd.tools.StringPlus in project opentheso by miledrousset.

the class WriteXml method writeLine.

private void writeLine(String colomne, String value) {
    StringPlus stringPlus = new StringPlus();
    value = stringPlus.normalizeStringForXml(value);
    xml.append("\n");
    xml.append("            ");
    xml.append("<" + colomne + ">");
    xml.append(value);
    xml.append("</" + colomne + ">");
}
Also used : StringPlus(mom.trd.opentheso.bdd.tools.StringPlus)

Aggregations

StringPlus (mom.trd.opentheso.bdd.tools.StringPlus)80 SQLException (java.sql.SQLException)63 Statement (java.sql.Statement)63 Connection (java.sql.Connection)56 ResultSet (java.sql.ResultSet)30 PreparedStatement (java.sql.PreparedStatement)19 ArrayList (java.util.ArrayList)12 NodeAutoCompletion (mom.trd.opentheso.bdd.helper.nodes.NodeAutoCompletion)6 NodeSearch (mom.trd.opentheso.bdd.helper.nodes.search.NodeSearch)6 NodeNote (mom.trd.opentheso.bdd.helper.nodes.notes.NodeNote)4 FacesMessage (javax.faces.application.FacesMessage)2 ConceptHelper (mom.trd.opentheso.bdd.helper.ConceptHelper)2 NodeBT (mom.trd.opentheso.bdd.helper.nodes.NodeBT)2 NodeEM (mom.trd.opentheso.bdd.helper.nodes.NodeEM)2 NodePermute (mom.trd.opentheso.bdd.helper.nodes.NodePermute)2 NodeConcept (mom.trd.opentheso.bdd.helper.nodes.concept.NodeConcept)2 PrefixString (com.k_int.IR.QueryModels.PrefixString)1 HikariDataSource (com.zaxxer.hikari.HikariDataSource)1 ConnexionTest (connexion.ConnexionTest)1 DcElement (fr.mom.arkeo.soap.DcElement)1