source: ether_eccad/trunk/ECCAD_INTERFACE/WEB-INF/src/org/medias/eccad/persistance/jdbc/ParametreDAOjdbc.java @ 68

package org.medias.eccad.persistance.jdbc;

import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.List;


import org.medias.eccad.helpers.LoggerPerso;
import org.medias.eccad.modele.Parametre;
import org.medias.eccad.persistance.jdbc.SGBD_jdbc;
import org.medias.eccad.persistance.dao.ParametreDAO;
import org.medias.eccad.persistance.exception.PersistanceException;

/**
 * 
 * @author pinaud
 *
 */
public class ParametreDAOjdbc extends GeneriqueDAOjdbc implements ParametreDAO {

        public ParametreDAOjdbc(SGBD_jdbc sgbd) {
                super (sgbd);
        }
        
        public ParametreDAOjdbc() {
                super();
        }
        
        /**
         * Retourne l'ensemble des paramÚtres en base de données
         * @return la liste des parametres
         */
        public List<Parametre> getListParametre() throws PersistanceException{
                Connection conn = null;
                Statement requete;
                String sql;
                List<Parametre> liste_param = new LinkedList<Parametre>();
                ResultSet resultat = null;
                
                conn = getConnection();
                
                try {
                        requete = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
                        sql = "select distinct id_param, fullname_param, shortname_param, order_param from parametre join grille using (id_param) join produit using (id_produit) join typeproduit using (id_typeproduit) where name_typeproduit not ilike 'array' order by order_param;";
                        
                        resultat = requete.executeQuery(sql);
                
                        
                        while (resultat.next()) {
                                liste_param.add(new Parametre(resultat.getInt("id_param"), resultat.getString("fullname_param"),resultat.getString("shortname_param"),resultat.getString("order_param") ));
                        }
                }
                catch (SQLException sqle){
                        throw new PersistanceException(sqle, "erreur lors de la requete :: ParametreDAOjdbc.getListParametre");
                }
                finally {
                        closeConnection();
                }
                
                LoggerPerso.log(SGBD_jdbc.class, LoggerPerso.DEBUG, "getListParametre::taille::"+liste_param.size());

                return liste_param;
        }
        
        
        
        /**
         * Retourne l'ensemble des paramÚtres en base de données d'une categorie
         * @return la liste des parametres
         */
        public List<Parametre> getListParamtByCatParam(int id_CatParam) throws PersistanceException{
                Connection conn = null;
                Statement requete;
                String sql;
                List<Parametre> liste_param = new LinkedList<Parametre>();
                ResultSet resultat = null;
                
                conn = getConnection();
                
                try {
                        requete = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
                        sql = "select distinct id_param, fullname_param, shortname_param, order_param from parametre join grille using (id_param) join produit using (id_produit) join typeproduit using (id_typeproduit) where id_categorieparametre= "+id_CatParam+" and name_typeproduit not ilike 'array' order by order_param;";
                        
                        resultat = requete.executeQuery(sql);
                
                        
                        while (resultat.next()) {
                                if (resultat.getString("shortname_param").equals("NULL"))
                                        liste_param.add(new Parametre(resultat.getInt("id_param"), resultat.getString("fullname_param")));
                                else
                                        liste_param.add(new Parametre(resultat.getInt("id_param"), resultat.getString("fullname_param")+" ("+resultat.getString("shortname_param")+") "));
                        }
                }
                catch (SQLException sqle){
                        throw new PersistanceException(sqle, "erreur lors de la requete :: ParametreDAOjdbc.getListParametre");
                }
                finally {
                        closeConnection();
                }
                
                LoggerPerso.log(SGBD_jdbc.class, LoggerPerso.DEBUG, "getListParametre::taille::"+liste_param.size());

                return liste_param;
        }
        
        
        
        
        /**
         * Retourne le parametre ayant l'identifiant transmis
         * @param id_param identifiant du parametre
         * @return le parametre
         */
        public Parametre getParametreById(int id_param) throws PersistanceException{
                Statement requete;
                String sql;
                Parametre param = null;
                Connection conn;
                
                conn = getConnection();
                
                try {
                        requete = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
                        sql = "select distinct id_param, fullname_param, shortname_param, order_param, shortName_unite from parametre join unite using (id_unite) where id_param = " + id_param + " order by order_param;";
                        LoggerPerso.log(SGBD_jdbc.class, LoggerPerso.DEBUG, "sql getParametreById="+sql);
                        
                        ResultSet resultat = requete.executeQuery(sql);
                        
                        if (resultat.next()) {
                                param = new Parametre(resultat.getInt("id_param"), resultat.getString("fullname_param"), resultat.getString("shortname_param"),resultat.getString("order_param"));
                                param.setUnite(resultat.getString("shortName_unite"));
                        }
                }
                catch (SQLException sqle) {
                        throw new PersistanceException(sqle, "erreur lors de la requete :: ParametreDAOjdbc.getParametreById");
                }
                finally {
                        closeConnection();
                }
                        
                LoggerPerso.log(SGBD_jdbc.class, LoggerPerso.DEBUG, "getParametreById::nom::"+ param.getNom());

                return param;
        }
        

        public Parametre getParametreByGrille(long id_grille) throws PersistanceException {
                Statement requete;
                String sql;
                Parametre param = null;
                Connection conn;
                
                conn = getConnection();
                
                try {
                        requete = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
                        sql = "select distinct id_param, fullname_param, shortname_param, order_param, shortName_unite from parametre join grille using (id_param) join unite using (id_unite) where id_grille = " + id_grille + " order by order_param;";
                        
                        ResultSet resultat = requete.executeQuery(sql);
                                                
                        if (resultat.next()) {
                                param = new Parametre(resultat.getInt("id_param"), resultat.getString("fullname_param"),resultat.getString("shortname_param"),resultat.getString("order_param"));
                                byte[] temp = null;
                                String unite = null;
                                try {
                                         temp = resultat.getString("shortName_unite").getBytes("ISO-8859-1");
                                } catch (UnsupportedEncodingException e) {      }
                                
                                if (temp != null)
                                        unite = new String(temp); 
                                param.setUnite(unite);
                        }
                }
                catch (SQLException sqle) {
                        throw new PersistanceException(sqle, "erreur lors de la requete :: ParametreDAOjdbc.getParametreById");
                }
                finally {
                        closeConnection();
                }
                        
                LoggerPerso.log(SGBD_jdbc.class, LoggerPerso.DEBUG, "getParametreById::nom::"+ param.getNom());

                return param;
        }

        public Parametre getParametreByName(String nom) throws PersistanceException {
                Statement requete;
                String sql;
                Parametre param = null;
                Connection conn;
                
                conn = getConnection();
                
                try {
                        requete = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
                        sql = "select distinct id_param, fullname_param, shortname_param, order_param, shortName_unite from parametre join unite using (id_unite) where fullname_param ilike '%" + nom + "%' order by order_param;";
                        
                        ResultSet resultat = requete.executeQuery(sql);
                        
                        if (resultat.next()) {
                                param = new Parametre(resultat.getInt("id_param"), resultat.getString("fullname_param"),resultat.getString("shortname_param"),resultat.getString("order_param")     );
                                param.setUnite(resultat.getString("shortName_unite"));
                        }
                }
                catch (SQLException sqle) {
                        throw new PersistanceException(sqle, "erreur lors de la requete :: ParametreDAOjdbc.getParametreById");
                }
                finally {
                        closeConnection();
                }
                        
                LoggerPerso.log(SGBD_jdbc.class, LoggerPerso.DEBUG, "getParametreById::nom::"+ param.getNom());

                return param;
        }

}