Search in sources :

Example 16 with StringPlus

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

the class PreferencesHelper method updateAllPreferenceUser.

/**
 * Permet de mettre à jour toutes les préférence
 *
 * @param ds
 * @param np
 * @param idThesaurus
 * @return
 */
public boolean updateAllPreferenceUser(HikariDataSource ds, NodePreference np, String idThesaurus) {
    Connection conn;
    Statement stmt;
    boolean status = false;
    StringPlus stringPlus = new StringPlus();
    np = normalizeDatas(np);
    try {
        conn = ds.getConnection();
        try {
            stmt = conn.createStatement();
            try {
                String query = "update preferences set " + "source_lang='" + stringPlus.convertString(np.getSourceLang()) + "'" + // + ", alert_cdt='" + np.isAlertCdt() + "'"
                ", identifier_type='" + np.getIdentifierType() + "'" + // Ark
                ", use_ark='" + np.isUseArk() + "'" + ", server_ark='" + stringPlus.convertString(np.getServeurArk()) + "'" + ", id_naan='" + np.getIdNaan() + "'" + ", prefix_ark ='" + np.getPrefixArk() + "'" + ", user_ark='" + np.getUserArk() + "'" + // + ", pass_ark='" + MD5Password.getEncodedPassword(np.getPassArk()) + "'"
                ", pass_ark='" + np.getPassArk() + "'" + // Handle
                ", use_handle = '" + np.isUseHandle() + "'" + ", user_handle = '" + np.getUserHandle() + "'" + ", pass_handle = '" + np.getPassHandle() + "'" + ", path_key_handle = '" + np.getPathKeyHandle() + "'" + ", path_cert_handle = '" + np.getPathCertHandle() + "'" + ", url_api_handle = '" + np.getUrlApiHandle() + "'" + ", prefix_handle = '" + np.getPrefixIdHandle() + "'" + ", private_prefix_handle = '" + np.getPrivatePrefixHandle() + "'" + ", path_image='" + stringPlus.convertString(np.getPathImage()) + "'" + ", dossier_resize='" + stringPlus.convertString(np.getDossierResize()) + "'" + ", bdd_active='" + np.isBddActive() + "'" + ", bdd_use_id='" + np.isBddUseId() + "'" + ", url_bdd='" + stringPlus.convertString(np.getUrlBdd()) + "'" + ", url_counter_bdd='" + stringPlus.convertString(np.getUrlCounterBdd()) + "'" + ", z3950actif='" + np.isZ3950actif() + "'" + ", collection_adresse='" + stringPlus.convertString(np.getCollectionAdresse()) + "'" + ", notice_url='" + stringPlus.convertString(np.getNoticeUrl()) + "'" + ", url_encode='" + stringPlus.convertString(np.getUrlEncode()) + "'" + ", path_notice1='" + stringPlus.convertString(np.getPathNotice1()) + "'" + ", path_notice2='" + stringPlus.convertString(np.getPathNotice2()) + "'" + ", chemin_site='" + stringPlus.convertString(np.getCheminSite()) + "'" + ", webservices='" + np.isWebservices() + "'" + " WHERE" + " id_thesaurus = '" + idThesaurus + "'";
                stmt.executeUpdate(query);
                status = true;
            } finally {
                stmt.close();
            }
        } finally {
            conn.close();
        }
    } catch (SQLException ex) {
        Logger.getLogger(UserHelper.class.getName()).log(Level.SEVERE, null, ex);
    }
    return status;
}
Also used : SQLException(java.sql.SQLException) Statement(java.sql.Statement) StringPlus(mom.trd.opentheso.bdd.tools.StringPlus) Connection(java.sql.Connection)

Example 17 with StringPlus

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

the class FacetHelper method addNewFacet.

/**
 * Cette focntion permet d'ajouter une nouvelle Facette
 *
 * @param ds
 * @param idThesaurus
 * @param idConceptParent
 * @param lexicalValue
 * @param idLang
 * @param notation
 * @return Id of Facet
 */
public int addNewFacet(HikariDataSource ds, String idThesaurus, String idConceptParent, String lexicalValue, String idLang, String notation) {
    int idFacet = -1;
    Connection conn;
    Statement stmt;
    ResultSet resultSet;
    lexicalValue = new StringPlus().convertString(lexicalValue);
    try {
        // Get connection from pool
        conn = ds.getConnection();
        try {
            stmt = conn.createStatement();
            try {
                String query = "select max(facet_id) from thesaurus_array where" + " id_thesaurus='" + idThesaurus + "'";
                stmt.executeQuery(query);
                resultSet = stmt.getResultSet();
                resultSet.next();
                idFacet = resultSet.getInt(1);
                idFacet = idFacet + 1;
                query = "Insert into thesaurus_array " + "(facet_id, id_thesaurus, id_concept_parent, " + " notation)" + " values (" + idFacet + ",'" + idThesaurus + "'" + ",'" + idConceptParent + "'" + ",'" + notation + "')";
                stmt.executeUpdate(query);
                addFacetTraduction(ds, idFacet, idThesaurus, lexicalValue, idLang);
            } 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 Facet with value : " + lexicalValue, sqle);
        }
    }
    return idFacet;
}
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)

Example 18 with StringPlus

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

the class FacetHelper method updateFacetTraduction.

/**
 * Cette fonction permet de mettre à jour une facette
 *
 * @param ds
 * @param idFacet
 * @param idThesaurus
 * @param idLang
 * @param lexicalValue
 * @return
 */
public boolean updateFacetTraduction(HikariDataSource ds, int idFacet, String idThesaurus, String idLang, String lexicalValue) {
    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 = "UPDATE node_label set" + " lexical_value = '" + lexicalValue + "'," + " modified = current_date" + " WHERE facet_id = " + idFacet + " AND id_thesaurus = '" + idThesaurus + "'" + " AND lang = '" + idLang + "'";
                stmt.executeUpdate(query);
                status = true;
            } finally {
                stmt.close();
            }
        } finally {
            conn.close();
        }
    } catch (SQLException sqle) {
        // Log exception
        log.error("Error while updating Facet Traduction of FacetId: " + 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 19 with StringPlus

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

the class TermHelper method getAutoCompletionTerm.

/**
 * Cette fonction permet de récupérer une liste de termes pour
 * l'autocomplétion avec les synonymes
 *
 * @param ds
 * @param idThesaurus
 * @param text
 * @param idLang
 * @return Objet class Concept
 */
public List<NodeAutoCompletion> getAutoCompletionTerm(HikariDataSource ds, String idThesaurus, String idLang, String text) {
    Connection conn;
    Statement stmt;
    ResultSet resultSet;
    List<NodeAutoCompletion> nodeAutoCompletionList = new ArrayList<>();
    text = new StringPlus().convertString(text);
    try {
        // Get connection from pool
        conn = ds.getConnection();
        try {
            stmt = conn.createStatement();
            try {
                // cette partie permettait de se placer directement sur un terme, mais quand on tappe le temre exacte, on a le resultat avec le teme en double
                /*       String query = 
                            "SELECT DISTINCT term.lexical_value, concept.id_concept, concept.id_group " +
                            "FROM preferred_term, term, concept WHERE " +
                            "preferred_term.id_term = term.id_term AND " +
                            "preferred_term.id_thesaurus = term.id_thesaurus AND " +
                            "concept.id_concept = preferred_term.id_concept AND " +
                            "concept.id_thesaurus = preferred_term.id_thesaurus AND " +
                            "term.id_thesaurus = '" + idThesaurus + "' AND " +
                            "term.lexical_value iLIKE '" + text + "' AND " +
                            "term.lang = '" + idLang + "' AND " +
                            "concept.status != 'hidden'";
                    
                    stmt.executeQuery(query);
                    resultSet = stmt.getResultSet();
                    if (resultSet != null) {

                        while (resultSet.next()) {
                            if (resultSet.getRow() != 0) {
                                NodeAutoCompletion nodeAutoCompletion = new NodeAutoCompletion();
                                nodeAutoCompletion.setIdConcept(resultSet.getString("id_concept"));
                                nodeAutoCompletion.setTermLexicalValue(resultSet.getString("lexical_value"));
                                nodeAutoCompletion.setGroupLexicalValue(
                                        new GroupHelper().getLexicalValueOfGroup(ds, resultSet.getString("id_group"), idThesaurus, idLang));
                                        //resultSet.getString("lexicalvalue"));
                                nodeAutoCompletion.setIdGroup(resultSet.getString("id_group"));
                                nodeAutoCompletionList.add(nodeAutoCompletion);
                            }
                        }
                    }*/
                String query = "SELECT DISTINCT term.lexical_value, concept.id_concept, concept_group_concept.idgroup " + "FROM preferred_term, term, concept,concept_group_concept " + "WHERE " + "idThesaurus = concept.id_thesaurus AND " + "concept_group_concept.idconcept = concept.id_concept AND " + "preferred_term.id_term = term.id_term AND " + "preferred_term.id_thesaurus = term.id_thesaurus AND " + "concept.id_concept = preferred_term.id_concept AND " + "concept.id_thesaurus = preferred_term.id_thesaurus AND " + "term.id_thesaurus = '" + idThesaurus + "' AND " + "term.lang = '" + idLang + "' AND " + "concept.status != 'hidden' AND " + "unaccent_string(term.lexical_value) ILIKE unaccent_string('" + text + "%')" + " ORDER BY term.lexical_value ASC LIMIT 20";
                /*
                    query = "SELECT DISTINCT term.lexical_value, concept.id_concept,"
                            + " concept_group_label.lexicalvalue, concept_group_label.idgroup FROM"
                            + " concept, preferred_term, term, concept_group_label"
                            + " WHERE concept.id_concept = preferred_term.id_concept"
                            + " AND concept.id_group = concept_group_label.idgroup"
                            + " AND preferred_term.id_term = term.id_term"
                            + " AND term.id_thesaurus = concept.id_thesaurus"
                            + " AND concept.status != 'hidden'"
                            + " AND term.id_thesaurus = '" + idThesaurus + "'"
                            + " AND term.lang = '" + idLang + "'"
                            + " AND concept_group_label.lang = '" + idLang + "'"
                            + " AND unaccent_string(term.lexical_value) ILIKE unaccent_string('" + text + "%')"
                            + " ORDER BY term.lexical_value ASC LIMIT 20";
                     */
                stmt.executeQuery(query);
                resultSet = stmt.getResultSet();
                if (resultSet != null) {
                    while (resultSet.next()) {
                        if (resultSet.getRow() != 0) {
                            NodeAutoCompletion nodeAutoCompletion = new NodeAutoCompletion();
                            nodeAutoCompletion.setIdConcept(resultSet.getString("id_concept"));
                            nodeAutoCompletion.setTermLexicalValue(resultSet.getString("lexical_value"));
                            nodeAutoCompletion.setGroupLexicalValue(new GroupHelper().getLexicalValueOfGroup(ds, resultSet.getString("idgroup"), idThesaurus, idLang));
                            nodeAutoCompletion.setIdGroup(resultSet.getString("idgroup"));
                            // if(!nodeAutoCompletionList.contains(nodeAutoCompletion))
                            nodeAutoCompletion.setIsAltLabel(false);
                            nodeAutoCompletionList.add(nodeAutoCompletion);
                        }
                    }
                }
                query = "SELECT DISTINCT " + "non_preferred_term.lexical_value," + "concept.id_concept," + "concept_group_concept.idgroup" + " FROM preferred_term, non_preferred_term, concept,concept_group_concept" + "  WHERE" + "  concept_group_concept.idthesaurus = concept.id_thesaurus " + "  AND" + "   concept_group_concept.idconcept = concept.id_concept" + " 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 " + " AND non_preferred_term.id_thesaurus = '" + idThesaurus + "'" + " AND non_preferred_term.lang = '" + idLang + "'" + " AND concept.status != 'hidden' " + " AND unaccent_string(non_preferred_term.lexical_value) ILIKE unaccent_string('" + text + "%') ORDER BY non_preferred_term.lexical_value ASC LIMIT 20";
                stmt.executeQuery(query);
                resultSet = stmt.getResultSet();
                while (resultSet.next()) {
                    NodeAutoCompletion nodeAutoCompletion = new NodeAutoCompletion();
                    nodeAutoCompletion.setIdConcept(resultSet.getString("id_concept"));
                    nodeAutoCompletion.setTermLexicalValue(resultSet.getString("lexical_value"));
                    nodeAutoCompletion.setGroupLexicalValue(new GroupHelper().getLexicalValueOfGroup(ds, resultSet.getString("idgroup"), idThesaurus, idLang));
                    nodeAutoCompletion.setIdGroup(resultSet.getString("idgroup"));
                    // if(!nodeAutoCompletionList.contains(nodeAutoCompletion))
                    nodeAutoCompletion.setIsAltLabel(true);
                    nodeAutoCompletionList.add(nodeAutoCompletion);
                }
            } finally {
                stmt.close();
            }
        } finally {
            conn.close();
        }
    } catch (SQLException sqle) {
        // Log exception
        log.error("Error while getting List of autocompletion of Text : " + text, sqle);
    }
    return nodeAutoCompletionList;
}
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) NodeAutoCompletion(mom.trd.opentheso.bdd.helper.nodes.NodeAutoCompletion)

Example 20 with StringPlus

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

the class TermHelper method addUSEHistorique.

/**
 * @param conn
 * @param term
 * @param idUser
 * @param action
 * @return idTerm
 */
private boolean addUSEHistorique(Connection conn, Term term, int idUser, String action) {
    boolean status = false;
    // Connection conn;
    Statement stmt;
    term.setLexical_value(new StringPlus().convertString(term.getLexical_value()));
    try {
        try {
            stmt = conn.createStatement();
            try {
                String query = "Insert into non_preferred_term_historique " + "(id_term, lexical_value, lang, " + "id_thesaurus, source, status, id_user, action)" + " values (" + "'" + term.getId_term() + "'" + ",'" + term.getLexical_value() + "'" + ",'" + term.getLang() + "'" + ",'" + term.getId_thesaurus() + "'" + ",'" + term.getSource() + "'" + ",'" + term.getStatus() + "'" + ",'" + idUser + "'" + ",'" + action + "')";
                stmt.executeUpdate(query);
                status = true;
            } finally {
                stmt.close();
            }
        } finally {
        // conn.close();
        }
    } catch (SQLException sqle) {
        // Log exception
        if (!sqle.getSQLState().equalsIgnoreCase("23505")) {
            status = false;
        }
    }
    return status;
}
Also used : SQLException(java.sql.SQLException) Statement(java.sql.Statement) 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