- Timestamp:
- 04/26/10 19:12:49 (14 years ago)
- Location:
- ether_eccad/trunk/ECCAD_INTERFACE/WEB-INF/src/org/medias/eccad/persistance/jdbc
- Files:
-
- 9 edited
Legend:
- Unmodified
- Added
- Removed
-
ether_eccad/trunk/ECCAD_INTERFACE/WEB-INF/src/org/medias/eccad/persistance/jdbc/CatParametreDAOjdbc.java
r68 r70 8 8 import java.util.List; 9 9 10 import org.medias.eccad.helpers.LoggerPerso;11 10 import org.medias.eccad.modele.CatParametre; 12 11 import org.medias.eccad.persistance.dao.CatParametreDAO; … … 33 32 conn = getConnection(); 34 33 35 try { LoggerPerso.log(CatParametreDAOjdbc.class, LoggerPerso.DEBUG, "DAOjdbc youhou!!");34 try { 36 35 requete = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); 37 sql = "select distinct id_categorieparametre, fullname_categorieparametre from categorieparametre join parametre using (id_categorieparametre) join grille using (id_param);"; 38 36 // sql = "select distinct id_categorieparametre, fullname_categorieparametre, order_categorieparametre" + 37 // " from categorieparametre join parametre using (id_categorieparametre)" + 38 // " join grille using (id_param) order by order_categorieparametre;"; 39 sql = "select distinct id_categorieparametre, fullname_categorieparametre" + 40 ", order_categorieparametre from categorieparametre" + 41 " where id_categorieparametre in (select id_categorieparametre from parametre" + 42 " where id_param in (select id_param from grille)) order by order_categorieparametre;"; 39 43 resultat = requete.executeQuery(sql); 40 44 … … 51 55 } 52 56 53 LoggerPerso.log(SGBD_jdbc.class, LoggerPerso.DEBUG, "getListCatParametre::taille::"+liste_catParam.size());57 // LoggerPerso.log(SGBD_jdbc.class, LoggerPerso.DEBUG, "getListCatParametre::taille::"+liste_catParam.size()); 54 58 55 59 return liste_catParam; -
ether_eccad/trunk/ECCAD_INTERFACE/WEB-INF/src/org/medias/eccad/persistance/jdbc/CodeDAOjdbc.java
r68 r70 21 21 try { 22 22 requete = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); 23 sql = " select legend_code" 24 + " from grille join codification using (id_codification)" 25 + " join code using (id_codification) where id_grille = " + id_grille + " order by id_code;"; 26 23 // sql = " select num_code,legend_code" 24 // + " from grille join codification using (id_codification)" 25 // + " join code using (id_codification) where id_grille = " + id_grille + " order by num_code;"; 26 sql = " select number_code,legend_code" 27 + " from grille left join code on id_grille = num_code" 28 + " where id_grille = " + id_grille + " order by number_code;"; 29 27 30 ResultSet resultat = requete.executeQuery(sql); 28 31 String legend = ""; 29 32 while (resultat.next()) { 30 code.add(resultat.getString("legend_code").toLowerCase()); 33 legend = resultat.getString("legend_code"); 34 code.add(resultat.getString("number_code")+"-"+legend); 35 } 36 if (code.size() == 1) { 37 code.set(0, "1-"+legend); 31 38 } 32 39 } -
ether_eccad/trunk/ECCAD_INTERFACE/WEB-INF/src/org/medias/eccad/persistance/jdbc/ColorTabDAOjdbc.java
r68 r70 9 9 import java.util.List; 10 10 11 12 import org.medias.eccad.helpers.LoggerPerso;13 11 import org.medias.eccad.modele.ColorMap; 14 12 import org.medias.eccad.modele.ColorTab; … … 17 15 18 16 public class ColorTabDAOjdbc extends GeneriqueDAOjdbc implements ColorTabDAO { 19 20 21 22 23 public ColorTab getColorMapByGrille(long id_grille) throws PersistanceException { 17 18 public ColorTab getColorMapByGrille() throws PersistanceException { 24 19 ColorTab colortab = new ColorTab(); 25 colortab = getTableDefault( id_grille,colortab);20 colortab = getTableDefault(colortab); 26 21 //colortab = getMinMax(oid, colortab); 27 22 … … 29 24 } 30 25 31 public List<ColorMap> getListColorTab() throws PersistanceException { 32 String sql = " select cm_name, " 33 + " from colormap " 34 + " order by cm_id;"; 35 //+ " join grille using (id_param) " 36 37 38 Statement requete = null; 39 ResultSet resultat = null; 40 Connection conn = getConnection(); 41 42 LoggerPerso.log(ColorTabDAOjdbc.class, LoggerPerso.DEBUG, " récupération liste de colortab " + sql); 26 public List<ColorMap> getListClassMap() throws PersistanceException { 27 String sql = " select clm_id, clm_name, num_order from class_map order by clm_id"; 28 29 Statement requete = null; 30 ResultSet resultat = null; 31 Connection conn = getConnection(); 32 33 // LoggerPerso.log(ColorTabDAOjdbc.class, LoggerPerso.DEBUG, " récupération liste de colortab " + sql); 43 34 44 35 try { … … 54 45 try { 55 46 while (resultat.next()) { 56 liste_colormap.add(new ColorMap(resultat.getLong("c m_id"), resultat.getString("cm_name"), resultat.getInt("pc_defaut")));47 liste_colormap.add(new ColorMap(resultat.getLong("clm_id"), resultat.getString("clm_name"), resultat.getInt("num_order"))); 57 48 } 58 49 } … … 67 58 } 68 59 69 public List<ColorMap> getListColorTabByGrille( long id_grille) throws PersistanceException {70 String sql = " select distinct cm_id, cm_name, pc_defaut"60 public List<ColorMap> getListColorTabByGrille() throws PersistanceException { 61 /*String sql = " select distinct cm_id, cm_name, pc_defaut" 71 62 + " from colormap join param_color using (cm_id) " 72 63 + " join grille using (id_param) " 73 + " where id_grille = " + id_grille + " order by pc_defaut desc;"; 74 75 Statement requete = null; 76 ResultSet resultat = null; 77 Connection conn = getConnection(); 78 79 LoggerPerso.log(ColorTabDAOjdbc.class, LoggerPerso.DEBUG, " récupération liste de colortab " + sql); 64 + " where id_grille = " + id_grille + " order by pc_defaut desc;";*/ 65 66 String sql = " select clm_id as cm_id, clm_name as cm_name, num_order as pc_defaut from class_map order by clm_id"; 67 68 Statement requete = null; 69 ResultSet resultat = null; 70 Connection conn = getConnection(); 71 72 // LoggerPerso.log(ColorTabDAOjdbc.class, LoggerPerso.DEBUG, " récupération liste de colortab " + sql); 80 73 81 74 try { … … 104 97 } 105 98 106 private ColorTab getTableDefault( long id_grille,ColorTab colortab) throws PersistanceException {107 Statement requete = null; 108 String sql = " select distinct col_r, col_g, col_b, ci_min, ci_max"99 private ColorTab getTableDefault(ColorTab colortab) throws PersistanceException { 100 Statement requete = null; 101 /*String sql = " select distinct col_r, col_g, col_b, ci_min, ci_max" 109 102 + " from color join color_index using (col_id)" 110 103 + " join param_color using (cm_id) " 111 104 + " join grille using (id_param) " 112 + " where id_grille = " + id_grille +" and pc_defaut = 1 order by ci_max;"; 113 Connection conn = getConnection(); 114 115 ResultSet resultat = null; 116 117 118 LoggerPerso.log(ColorTabDAOjdbc.class, LoggerPerso.DEBUG, "*************************************REQUETE = " + sql); 105 + " where id_grille = " + id_grille +" and pc_defaut = 1 order by ci_max;";*/ 106 107 String sql = " select distinct col_r, col_g, col_b, class_index.num_order" 108 + " from color join class_index using (col_id)" 109 + " join class_map using (clm_id) " 110 + " where class_map.num_order=1 order by class_index.num_order;"; 111 Connection conn = getConnection(); 112 113 ResultSet resultat = null; 114 115 // LoggerPerso.log(ColorTabDAOjdbc.class, LoggerPerso.DEBUG, "*************************************REQUETE = " + sql); 119 116 try { 120 117 requete = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); … … 129 126 int i=0; 130 127 131 128 float ci_min=-1; 129 float ci_max=0; 132 130 133 131 try { 134 132 while (resultat.next()) { 135 133 liste_couleur.add(new Color(resultat.getInt("col_r"), resultat.getInt("col_g"), resultat.getInt("col_b"))); 136 liste_min.add(resultat.getFloat("ci_min")); 137 colortab.setMax(resultat.getFloat("ci_max")); 134 //liste_min.add(resultat.getFloat("ci_min")); 135 //colortab.setMax(resultat.getFloat("ci_max")); 136 137 liste_min.add(ci_min); 138 colortab.setMax(ci_max); 139 140 if (ci_min==-1) ci_min=0; 141 else ci_min=ci_min+10; 142 143 ci_max=ci_max+10; 138 144 i++; 139 145 } … … 164 170 } 165 171 166 public ColorTab getColorMapByID(long id ) throws PersistanceException {172 public ColorTab getColorMapByID(long id, long idClass) throws PersistanceException { 167 173 Statement requete = null; 168 174 ColorTab colortab = new ColorTab(); 169 175 170 String sql = " select distinct col_r, col_g, col_b, ci_min, ci_max, cm_name"176 /*String sql = " select distinct col_r, col_g, col_b, ci_min, ci_max, cm_name" 171 177 + " from color join color_index using (col_id) join colormap using (cm_id)" 172 + " where cm_id = " + id +" order by ci_min;"; 173 Connection conn = getConnection(); 174 175 ResultSet resultat = null; 176 177 178 LoggerPerso.log(ColorTabDAOjdbc.class, LoggerPerso.DEBUG, "REQUETE = " + sql); 178 + " where cm_id = " + id +" order by ci_min;";*/ 179 180 String sql="select distinct col_r, col_g, col_b, clm_name as cm_name, ci.num_order, val_min, val_max" 181 +" from color as co, class_index as ci, class_map as cm, class_distribution as cd" 182 +" where co.col_id=ci.col_id and ci.clm_id=cm.clm_id and cm.clm_id="+id+" and cd.scl_id="+idClass 183 +" and cd.num_order=ci.num_order order by ci.num_order;"; 184 Connection conn = getConnection(); 185 186 ResultSet resultat = null; 187 188 // LoggerPerso.log(ColorTabDAOjdbc.class, LoggerPerso.DEBUG, "REQUETE = " + sql); 179 189 try { 180 190 requete = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); … … 189 199 int i=0; 190 200 191 192 193 try { 194 201 try { 195 202 while (resultat.next()) { 196 203 colortab.setName(resultat.getString("cm_name")); 197 204 liste_couleur.add(new Color(resultat.getInt("col_r"), resultat.getInt("col_g"), resultat.getInt("col_b"))); 198 liste_min.add(resultat.getFloat("ci_min")); 199 colortab.setMax(resultat.getFloat("ci_max")); 205 liste_min.add(resultat.getFloat("val_min")); 206 colortab.setMax(resultat.getFloat("val_max")); 207 // LoggerPerso.log(ColorTabDAOjdbc.class, LoggerPerso.DEBUG, "NAME="+resultat.getString("cm_name")+", RANGE = " +resultat.getFloat("val_min")+" - "+resultat.getFloat("val_max")); 200 208 i++; 201 209 } … … 221 229 } 222 230 223 public List<ColorMap> getListColorMapDynamique() throws PersistanceException {231 /* public List<ColorMap> getListColorMapDynamique() throws PersistanceException { 224 232 String sql = " select distinct clm_id, clm_name" 225 + " from class_map " 226 + " where clm_name ilike '%exponential%' or clm_name ilike '%linear%' or clm_name ilike '%logarithm%' order by clm_name;"; 227 228 229 230 Statement requete = null; 231 ResultSet resultat = null; 232 Connection conn = getConnection(); 233 234 LoggerPerso.log(ColorTabDAOjdbc.class, LoggerPerso.DEBUG, " récupération liste de colortab " + sql); 233 + " from class_map order by clm_id"; 234 //+ " where clm_name like '%exponential%' or clm_name like '%linear%' or clm_name like '%logarithm%' order by clm_name;"; 235 236 237 Statement requete = null; 238 ResultSet resultat = null; 239 Connection conn = getConnection(); 240 241 // LoggerPerso.log(ColorTabDAOjdbc.class, LoggerPerso.DEBUG, " récupération liste de colortab " + sql); 235 242 236 243 try { … … 246 253 try { 247 254 while (resultat.next()) { 248 liste_colormap.add(new ColorMap(resultat.getLong("c m_id"), resultat.getString("cm_name"), 0));255 liste_colormap.add(new ColorMap(resultat.getLong("clm_id"), resultat.getString("clm_name"), 0)); 249 256 } 250 257 } … … 257 264 258 265 return liste_colormap; 259 } 266 }*/ 260 267 261 268 } -
ether_eccad/trunk/ECCAD_INTERFACE/WEB-INF/src/org/medias/eccad/persistance/jdbc/FactoryDAOjdbc.java
r68 r70 4 4 import org.medias.eccad.persistance.dao.GrilleDAO; 5 5 import org.medias.eccad.persistance.dao.ParametreDAO; 6 import org.medias.eccad.persistance.dao.SecteurDAO; 6 7 import org.medias.eccad.persistance.dao.ProduitDAO; 7 8 … … 24 25 return new ParametreDAOjdbc(sgbd); 25 26 } 27 28 public SecteurDAO getSecteurDAO() { 29 return new SecteurDAOjdbc(sgbd); 30 } 26 31 27 32 public ProduitDAO getProduitDAO() { -
ether_eccad/trunk/ECCAD_INTERFACE/WEB-INF/src/org/medias/eccad/persistance/jdbc/GeneriqueDAOjdbc.java
r68 r70 4 4 import java.sql.SQLException; 5 5 6 7 import org.medias.eccad.helpers.LoggerPerso;8 6 import org.medias.eccad.persistance.exception.PersistanceException; 9 7 … … 40 38 throw new PersistanceException(sqle, "impossible de se connecter au sgbd :: ParametreDAOjdbc.getListParametre"); 41 39 } 42 LoggerPerso.log(GeneriqueDAOjdbc.class, LoggerPerso.DEBUG, "-- jdbc connexion -- ");43 40 return conn; 44 41 } 45 42 46 43 protected void closeConnection() { 47 LoggerPerso.log(GeneriqueDAOjdbc.class, LoggerPerso.DEBUG, "-- jdbc deconnexion -- ");48 44 sgbd.deconnexion(); 49 45 } -
ether_eccad/trunk/ECCAD_INTERFACE/WEB-INF/src/org/medias/eccad/persistance/jdbc/GrilleDAOjdbc.java
r68 r70 2 2 3 3 import java.sql.Connection; 4 import java.sql.Date; 4 5 import java.sql.ResultSet; 5 6 import java.sql.SQLException; 6 7 import java.sql.Statement; 8 import java.util.ArrayList; 9 import java.util.Collections; 7 10 import java.util.LinkedList; 8 11 import java.util.List; 9 12 10 11 13 import org.medias.eccad.helpers.Donnees; 12 import org.medias.eccad.helpers.LoggerPerso;13 14 import org.medias.eccad.modele.Grille; 14 15 import org.medias.eccad.persistance.jdbc.SGBD_jdbc; … … 28 29 } 29 30 30 public List<Grille> getListGrille(long id_produit,long id_parametre, String date_debut, String date_fin) throws PersistanceException{ 31 public List<Grille> getListGrille(long id_produit,long id_parametre, long id_secteur, long id_rcp, String date_debut, String date_fin, String nomProd, String legend, String typeProd) throws PersistanceException{ 32 Statement requete; 33 String sql; 34 List<Grille> liste_gril = new LinkedList<Grille>(); 35 Connection conn = getConnection(); 36 37 try { 38 requete = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); 39 sql = "select distinct date_grille, id_grille, legend_code, number_code" + 40 " from grille" + 41 " left join code on ((id_grille = num_code) and (grille.id_codification = code.id_codification))" + 42 " where id_produit = "+id_produit+" and id_param = "+id_parametre; 43 if (nomProd.equals("IPCC")) { 44 if (id_secteur > 0) { 45 sql+=" and id_secteur="+id_secteur; 46 } 47 if (id_rcp > 0 ) { 48 sql+=" and id_rcp="+id_rcp; 49 } else { 50 sql+=" and id_rcp is null"; 51 } 52 if (legend != null) { 53 sql+=" and legend_code = '"+legend+"'"; 54 } 55 } 56 if (date_debut != null) { 57 sql += " and date_grille >= '" + date_debut + "' and date_grille <= '" + date_fin + "'"; 58 if (typeProd.equals("monoplan")) { 59 sql += " order by id_grille, number_code"; 60 } else { 61 sql += " order by date_grille, number_code"; 62 } 63 } else { 64 sql += " order by id_grille"; 65 } 66 sql += ";"; 67 //LoggerPerso.log(SGBD_jdbc.class, LoggerPerso.DEBUG, "getListGrille::requete grille :: " + sql); 68 ResultSet resultat = requete.executeQuery(sql); 69 70 Date date; 71 long idGrille, idGrillePrec = 0; 72 String datePrec = ""; 73 int numberCode; 74 // NB: On ne stocke qu'une seule grille : 75 // - par plan (dans le cas d'un produit multiplan) 76 // - par date (dans le cas d'un produit temporel) 77 // - par masque (dans le cas d'un produit monoplan) 78 while (resultat.next()) { 79 date = resultat.getDate("date_grille"); 80 idGrille = resultat.getLong("id_grille"); 81 numberCode = resultat.getInt("number_code"); 82 if (typeProd.equals("multiplan") || 83 !date.toString().equals(datePrec) || 84 (typeProd.equals("monoplan") && idGrille != idGrillePrec)) { 85 // Cas monoplan: on place la grille contenant l'ensemble des masques en premiÚre position 86 // if (typeProd.equals("monoplan") && numberCode == 0) { 87 // liste_gril.add(0,new Grille(idGrille, date, resultat.getString("legend_code"),numberCode)); 88 // } else { 89 liste_gril.add(new Grille(idGrille, date, resultat.getString("legend_code"),numberCode)); 90 // } 91 } 92 datePrec = date.toString(); 93 idGrillePrec = idGrille; 94 } 95 // Pour un produit monoplan, on trie les grilles par numéro de code 96 if (typeProd.equals("monoplan")) { 97 Collections.sort(liste_gril,new Grille().new ComparatorNumberCode()); 98 } 99 } 100 catch (SQLException sqle) { 101 throw new PersistanceException(sqle, "erreur lors de l'execution de la requete :: GrilleDAOjdbc.getListGrille"); 102 } 103 finally { 104 closeConnection(); 105 } 106 //System.out.println("nbgrilles="+liste_gril.size()); 107 return liste_gril; 108 } 109 110 public List<Grille> getListGrille(long id_produit, String date_debut, String date_fin) throws PersistanceException{ 31 111 Statement requete; 32 112 String sql; … … 36 116 try { 37 117 requete = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); 38 sql = "select distinct id_grille, valeurs_grille, date_grille, legend_code from grille left join code on ((id_grille = num_code) and (grille.id_codification = code.id_codification)) where id_produit = " + id_produit+" and id_param = "+id_parametre; 118 sql = "select distinct id_grille, valeurs_grille, date_grille, legend_code, number_code from grille" + 119 // " inner join codification using (id_codification)" + 120 // " inner join type_codification using (id_typecodification)" + 121 " left join code on ((id_grille = num_code)" + 122 " and (grille.id_codification = code.id_codification)) where id_produit = " + id_produit; 123 // " inner join codification using (id_codification) left join code using (id_codification)" + 124 // " where id_produit = " + id_produit; 39 125 40 126 if (date_debut != null) 41 sql += " and date_grille >= '" + date_debut + "' and date_grille <= '" + date_fin + "' order by date_grille ";127 sql += " and date_grille >= '" + date_debut + "' and date_grille <= '" + date_fin + "' order by date_grille, number_code"; 42 128 else 43 sql += " order by id_grille";129 sql += " order by number_code, id_grille"; 44 130 45 131 sql += ";"; 46 132 47 LoggerPerso.log(SGBD_jdbc.class, LoggerPerso.DEBUG, "getListGrille::requete :: " + sql);133 // LoggerPerso.log(SGBD_jdbc.class, LoggerPerso.DEBUG, "getListGrille2::requete :: " + sql); 48 134 ResultSet resultat = requete.executeQuery(sql); 49 135 50 136 while (resultat.next()) { 51 liste_param.add(new Grille(resultat.getLong("id_grille"), resultat.getDate("date_grille"), resultat.getString("legend_code") ));137 liste_param.add(new Grille(resultat.getLong("id_grille"), resultat.getDate("date_grille"), resultat.getString("legend_code"), resultat.getInt("number_code"))); 52 138 } 53 139 } … … 58 144 closeConnection(); 59 145 } 60 61 LoggerPerso.log(SGBD_jdbc.class, LoggerPerso.DEBUG, "getListGrille::taille::"+liste_param.size() + "id_produit::" + id_produit);146 147 // LoggerPerso.log(SGBD_jdbc.class, LoggerPerso.DEBUG, "getListGrille::taille::"+liste_param.size() + "id_produit::" + id_produit); 62 148 return liste_param; 63 149 } 64 150 65 66 67 public List<Grille> getListGrille(long id_produit, String date_debut, String date_fin) throws PersistanceException{ 68 Statement requete; 69 String sql; 70 List<Grille> liste_param = new LinkedList<Grille>(); 71 Connection conn = getConnection(); 72 73 try { 74 requete = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); 75 sql = "select distinct id_grille, valeurs_grille, date_grille, legend_code from grille left join code on ((id_grille = num_code) and (grille.id_codification = code.id_codification)) where id_produit = " + id_produit; 76 151 public List<String> getListAlt(long id_secteur, String date_debut) throws PersistanceException{ 152 Statement requete; 153 String sql; 154 List<String> liste_alt = new LinkedList<String>(); 155 Connection conn = getConnection(); 156 157 try { 158 // On récupÚre les altitudes 159 requete = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); 160 //sql = "select distinct cast(legend_code as decimal) as altitude from grille" + 161 sql = "select distinct legend_code, number_code from grille" + 162 " left join code on ((id_grille = num_code) and (grille.id_codification = code.id_codification))" + 163 " where id_secteur = "+id_secteur; 77 164 if (date_debut != null) 78 sql += " and date_grille >= '" + date_debut + "' and date_grille <= '" + date_fin + "' order by date_grille"; 79 else 80 sql += " order by id_grille"; 81 82 sql += ";"; 83 84 LoggerPerso.log(SGBD_jdbc.class, LoggerPerso.DEBUG, "getListGrille::requete :: " + sql); 85 ResultSet resultat = requete.executeQuery(sql); 86 165 sql += " and date_grille = '" + date_debut+"'"; 166 sql += " order by number_code;"; 167 ResultSet resultat = requete.executeQuery(sql); 87 168 while (resultat.next()) { 88 liste_param.add(new Grille(resultat.getLong("id_grille"), resultat.getDate("date_grille"), resultat.getString("legend_code"))); 89 } 90 } 91 catch (SQLException sqle) { 92 throw new PersistanceException(sqle, "erreur lors de l'execution de la requete :: GrilleDAOjdbc.getListGrille"); 93 } 94 finally { 95 closeConnection(); 96 } 97 98 LoggerPerso.log(SGBD_jdbc.class, LoggerPerso.DEBUG, "getListGrille::taille::"+liste_param.size() + "id_produit::" + id_produit); 99 return liste_param; 100 } 101 102 103 169 liste_alt.add(resultat.getString("legend_code")); 170 } 171 } 172 catch (SQLException sqle) { 173 throw new PersistanceException(sqle, "erreur lors de l'execution de la requete :: GrilleDAOjdbc.getListAlt"); 174 } 175 finally { 176 closeConnection(); 177 } 178 return liste_alt; 179 } 180 181 public List<Grille> getListMasques(String codifMasque) throws PersistanceException{ 182 Statement requete; 183 String sql; 184 List<Grille> liste_reg = new LinkedList<Grille>(); 185 Connection conn = getConnection(); 186 187 try { 188 // On récupÚre les régions 189 requete = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); 190 sql = "select distinct legend_code, number_code, id_grille from grille" + 191 " inner join codification using (id_codification) left join code on (id_grille = num_code)" + 192 " where nom_codification = '"+codifMasque+"' order by id_grille, number_code;"; 193 ResultSet resultat = requete.executeQuery(sql); 194 long idGrille, idGrillePrec = 0; 195 while (resultat.next()) { 196 idGrille = resultat.getLong("id_grille"); 197 if (idGrille != idGrillePrec) { 198 liste_reg.add(new Grille(idGrille, null, resultat.getString("legend_code"),resultat.getInt("number_code"))); 199 } 200 idGrillePrec = idGrille; 201 } 202 Collections.sort(liste_reg,new Grille().new ComparatorNumberCode()); 203 } 204 catch (SQLException sqle) { 205 throw new PersistanceException(sqle, "erreur lors de l'execution de la requete :: GrilleDAOjdbc.getListAlt"); 206 } 207 finally { 208 closeConnection(); 209 } 210 return liste_reg; 211 } 104 212 105 213 public float[][] getGrilleValue(int oid) throws PersistanceException { … … 184 292 try { 185 293 requete = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); 186 sql = " select name_type Codification" +""294 sql = " select name_typecodification" 187 295 + " from grille join codification using (id_codification)" 188 296 + " join typecodification using (id_typeCodification) where id_grille = " + id_grille + ";"; 189 297 190 298 ResultSet resultat = requete.executeQuery(sql); 191 LoggerPerso.log(GrilleDAOjdbc.class, LoggerPerso.DEBUG, "getCodification :: " + sql);299 // LoggerPerso.log(GrilleDAOjdbc.class, LoggerPerso.DEBUG, "getCodification :: " + sql); 192 300 if (resultat.next()) { 193 301 codification = resultat.getString("name_typeCodification").toLowerCase(); … … 216 324 217 325 ResultSet resultat = requete.executeQuery(sql); 218 LoggerPerso.log(GrilleDAOjdbc.class, LoggerPerso.DEBUG, "getCodification :: " + sql);326 // LoggerPerso.log(GrilleDAOjdbc.class, LoggerPerso.DEBUG, "getCodification :: " + sql); 219 327 if (resultat.next()) { 220 328 date = resultat.getString("date_grille").toLowerCase(); … … 237 345 238 346 public Grille getGrille(long id_grille) throws PersistanceException { 239 String sql = "select * from grille where id_grille = " + id_grille + ";"; 347 348 String sql = "select * from grille"; 349 sql += " left join code on id_grille = num_code"; 350 sql += " where id_grille = " + id_grille + ";"; 240 351 Statement requete; 241 352 Connection conn = getConnection(); … … 252 363 grille.setId_param(resultat.getLong("id_param")); 253 364 grille.setId_produit(resultat.getLong("id_produit")); 365 grille.setId_secteur(resultat.getLong("id_secteur")); 366 grille.setId_rcp(resultat.getLong("id_rcp")); 367 grille.setLegend(resultat.getString("legend_code")); 254 368 } 255 369 } … … 275 389 if (resultat.next()) { 276 390 resol = (float)360/resultat.getLong("ncol_grille"); 277 resultat.getLong("nlign_grille");391 // resultat.getLong("nlign_grille"); 278 392 } 279 393 } … … 299 413 if (resultat.next()) { 300 414 resol = (float)360/resultat.getLong("ncol_grille"); 301 resultat.getLong("nlign_grille");415 // resultat.getLong("nlign_grille"); 302 416 } 303 417 } … … 310 424 return resol; 311 425 } 312 313 426 427 public String getTypeCodif(long idGrille) throws PersistanceException { 428 String sql = "select name_typecodification from grille" + 429 " inner join codification using (id_codification)" + 430 " inner join typecodification using (id_typecodification)" + 431 " where id_grille = " + idGrille + ";"; 432 Statement requete; 433 Connection conn = getConnection(); 434 String typeCodif = null; 435 try { 436 requete = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); 437 438 439 ResultSet resultat = requete.executeQuery(sql); 440 441 if (resultat.next()) { 442 typeCodif = resultat.getString("name_typecodification"); 443 } 444 } 445 catch (SQLException sqle) { 446 throw new PersistanceException(sqle, "getTypeCodif " + sql); 447 } 448 finally { 449 closeConnection(); 450 } 451 return typeCodif; 452 } 453 454 public ArrayList<String> getDates(long idProduit, long idParametre, long idSecteur, long idRcp, String nomProd) throws PersistanceException { 455 Statement requete; 456 String sql; 457 ArrayList<String> dates = new ArrayList<String>(); 458 Connection conn = getConnection(); 459 460 try { 461 requete = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); 462 // sql = "select min(date_grille) as min, max(date_grille) as max from grille" + 463 sql = "select min(date_grille) as min, (cast(max(date_grille+deltadate_grille) as date)-1) as max, min(deltadate_grille) as delta from grille" + 464 " where id_produit = "+idProduit+" and id_param = "+idParametre; 465 if (nomProd.equals("IPCC")) { 466 if (idSecteur > 0) { 467 sql+=" and id_secteur="+idSecteur; 468 } 469 if (idRcp > 0 ) { 470 sql+=" and id_rcp="+idRcp; 471 } else { 472 sql+=" and id_rcp is null"; 473 } 474 } 475 sql += ";"; 476 ResultSet resultat = requete.executeQuery(sql); 477 if (resultat.next()) { 478 dates.add(resultat.getDate("min").toString()); 479 dates.add(resultat.getDate("max").toString()); 480 dates.add(resultat.getString("delta")); 481 } 482 } 483 catch (SQLException sqle) { 484 throw new PersistanceException(sqle, "erreur lors de l'execution de la requete :: GrilleDAOjdbc.getDates"); 485 } 486 finally { 487 closeConnection(); 488 } 489 return dates; 490 } 314 491 } -
ether_eccad/trunk/ECCAD_INTERFACE/WEB-INF/src/org/medias/eccad/persistance/jdbc/ParametreDAOjdbc.java
r68 r70 9 9 import java.util.List; 10 10 11 12 import org.medias.eccad.helpers.LoggerPerso;13 11 import org.medias.eccad.modele.Parametre; 14 12 import org.medias.eccad.persistance.jdbc.SGBD_jdbc; … … 62 60 } 63 61 64 LoggerPerso.log(SGBD_jdbc.class, LoggerPerso.DEBUG, "getListParametre::taille::"+liste_param.size());62 // LoggerPerso.log(SGBD_jdbc.class, LoggerPerso.DEBUG, "getListParametre::taille::"+liste_param.size()); 65 63 66 64 return liste_param; … … 84 82 try { 85 83 requete = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); 86 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;"; 87 84 // sql = "select distinct id_param, fullname_param, shortname_param, order_param from parametre" + 85 // " join grille using (id_param) join produit using (id_produit)" + 86 // " join typeproduit using (id_typeproduit)" + 87 // " where id_categorieparametre= "+id_CatParam+ 88 // " and name_typeproduit not ilike 'array' order by order_param;"; 89 sql = "select distinct id_param, fullname_param, shortname_param, order_param from parametre" + 90 " where id_param in (select id_param from grille join produit using (id_produit)" + 91 " join typeproduit using (id_typeproduit)" + 92 " where name_typeproduit not ilike 'array')" + 93 " and id_categorieparametre="+id_CatParam+" order by order_param;"; 88 94 resultat = requete.executeQuery(sql); 89 95 90 96 91 97 while (resultat.next()) { 92 if (resultat.getString("shortname_param").equals("NULL") )98 if (resultat.getString("shortname_param").equals("NULL") || resultat.getString("shortname_param").equals("")) 93 99 liste_param.add(new Parametre(resultat.getInt("id_param"), resultat.getString("fullname_param"))); 94 100 else … … 103 109 } 104 110 105 LoggerPerso.log(SGBD_jdbc.class, LoggerPerso.DEBUG, "getListParametre::taille::"+liste_param.size());111 // LoggerPerso.log(SGBD_jdbc.class, LoggerPerso.DEBUG, "getListParametre::taille::"+liste_param.size()); 106 112 107 113 return liste_param; … … 126 132 try { 127 133 requete = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); 128 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;"; 129 LoggerPerso.log(SGBD_jdbc.class, LoggerPerso.DEBUG, "sql getParametreById="+sql); 134 sql = "select distinct id_param, fullname_param, shortname_param, order_param, shortName_unite, clm_default_id, scl_default_id" + 135 " from parametre p" + 136 " inner join unite using (id_unite)" + 137 " where id_param = " + id_param + " order by order_param;"; 130 138 131 139 ResultSet resultat = requete.executeQuery(sql); 132 140 133 141 if (resultat.next()) { 134 param = new Parametre(resultat.getInt("id_param"), resultat.getString("fullname_param"), resultat.getString("shortname_param"),resultat.getString("order_param")); 142 param = new Parametre(resultat.getInt("id_param"), resultat.getString("fullname_param") 143 , resultat.getString("shortname_param"),resultat.getString("order_param")); 135 144 param.setUnite(resultat.getString("shortName_unite")); 145 param.setColormapDefault(resultat.getInt("clm_default_id")); 146 param.setClassdisDefault(resultat.getInt("scl_default_id")); 136 147 } 137 148 } … … 142 153 closeConnection(); 143 154 } 144 145 LoggerPerso.log(SGBD_jdbc.class, LoggerPerso.DEBUG, "getParametreById::nom::"+ param.getNom());155 156 // LoggerPerso.log(SGBD_jdbc.class, LoggerPerso.DEBUG, "getParametreById::nom::"+ param.getNom()); 146 157 147 158 return param; … … 159 170 try { 160 171 requete = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); 161 sql = "select distinct id_param, fullname_param, shortname_param, order_param, short Name_unite from parametre join grille using (id_param) join unite using (id_unite) where id_grille = " + id_grille + " order by order_param;";172 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;"; 162 173 163 174 ResultSet resultat = requete.executeQuery(sql); … … 168 179 String unite = null; 169 180 try { 170 temp = resultat.getString("short Name_unite").getBytes("ISO-8859-1");171 } catch (UnsupportedEncodingException e) { 181 temp = resultat.getString("shortname_unite").getBytes("ISO-8859-1"); 182 } catch (UnsupportedEncodingException e) {e.printStackTrace();} 172 183 173 184 if (temp != null) … … 182 193 closeConnection(); 183 194 } 184 185 LoggerPerso.log(SGBD_jdbc.class, LoggerPerso.DEBUG, "getParametreById::nom::"+ param.getNom());195 196 // LoggerPerso.log(SGBD_jdbc.class, LoggerPerso.DEBUG, "getParametreById::nom::"+ param.getNom()); 186 197 187 198 return param; … … 213 224 closeConnection(); 214 225 } 215 216 LoggerPerso.log(SGBD_jdbc.class, LoggerPerso.DEBUG, "getParametreById::nom::"+ param.getNom());226 227 // LoggerPerso.log(SGBD_jdbc.class, LoggerPerso.DEBUG, "getParametreById::nom::"+ param.getNom()); 217 228 218 229 return param; -
ether_eccad/trunk/ECCAD_INTERFACE/WEB-INF/src/org/medias/eccad/persistance/jdbc/ProduitDAOjdbc.java
r68 r70 10 10 11 11 import org.medias.eccad.helpers.CharactereHelper; 12 import org.medias.eccad.helpers.LoggerPerso;13 12 import org.medias.eccad.modele.Produit; 14 13 import org.medias.eccad.persistance.jdbc.SGBD_jdbc; … … 34 33 try { 35 34 requete = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); 36 sql = "select distinct id_produit, title_produit, begintime_produit, endtime_produit, header_produit from produit join grille using (id_produit) where id_param = " + id_param + ";"; 37 35 sql = "select distinct id_produit, title_produit, begintime_produit, endtime_produit, header_produit, order_produit from produit join grille using (id_produit) where id_param = " + id_param + " order by order_produit;"; 38 36 ResultSet resultat = requete.executeQuery(sql); 39 37 … … 55 53 } 56 54 57 LoggerPerso.log(SGBD_jdbc.class, LoggerPerso.DEBUG, "getListProduit::taille::"+liste_param.size()+"\nSQL::" + sql);58 55 return liste_param; 59 56 } … … 89 86 try { 90 87 requete = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); 91 sql = "select distinct id_produit, title_produit, begintime_produit, endtime_produit, header_produit, temporalRes_produit, name_typeproduit from produit join typeproduit using (id_typeproduit) where id_produit = " + id_produit + ";"; 88 sql = "select distinct id_produit, title_produit, begintime_produit" + 89 ", endtime_produit, header_produit, temporalRes_produit, name_typeproduit" + 90 ", textcitation_produit, useconst_produit, informpub_produit" + 91 " from produit join typeproduit using (id_typeproduit)" + 92 " where id_produit = " + id_produit + ";"; 92 93 93 94 ResultSet resultat = requete.executeQuery(sql); … … 101 102 produit.setTemporalres(CharactereHelper.getEncodage(resultat.getString("temporalRes_produit"), "ISO-8859-1")); 102 103 produit.setType(CharactereHelper.getEncodage(resultat.getString("name_typeproduit"), "ISO-8859-1")); 104 produit.setCitation(CharactereHelper.getEncodage(resultat.getString("textcitation_produit"), "ISO-8859-1")); 105 produit.setUseconst(CharactereHelper.getEncodage(resultat.getString("useconst_produit"), "ISO-8859-1")); 106 produit.setInformpub(CharactereHelper.getEncodage(resultat.getString("informpub_produit"), "ISO-8859-1")); 103 107 } 104 108 } … … 109 113 closeConnection(); 110 114 } 111 LoggerPerso.log(SGBD_jdbc.class, LoggerPerso.DEBUG, "getProduitById::nom::"+ produit.getNom() + "::date::" + produit.getBeginDate());115 // LoggerPerso.log(SGBD_jdbc.class, LoggerPerso.DEBUG, "getProduitById::nom::"+ produit.getNom() + "::date::" + produit.getBeginDate()); 112 116 113 117 return produit; … … 122 126 try { 123 127 requete = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); 124 sql = "select distinct id_produit, title_produit, begintime_produit, endtime_produit, header_produit from produit join lienproduit using (id_produit) 128 sql = "select distinct id_produit, title_produit, begintime_produit, endtime_produit, header_produit from produit join lienproduit using (id_produit)"; 125 129 126 130 if (id_produit != null) { … … 149 153 } 150 154 151 LoggerPerso.log(SGBD_jdbc.class, LoggerPerso.DEBUG, "getListProductByLink::taille::"+liste_produit.size());155 // LoggerPerso.log(SGBD_jdbc.class, LoggerPerso.DEBUG, "getListProductByLink::taille::"+liste_produit.size()); 152 156 return liste_produit; 153 157 } -
ether_eccad/trunk/ECCAD_INTERFACE/WEB-INF/src/org/medias/eccad/persistance/jdbc/SGBD_jdbc.java
r68 r70 16 16 public SGBD_jdbc(InfoConnectBean n_info) { 17 17 con = null; 18 LoggerPerso.log(SGBD_jdbc.class, LoggerPerso.DEBUG, "construction connection");18 // LoggerPerso.log(SGBD_jdbc.class, LoggerPerso.DEBUG, "construction connection"); 19 19 if (pooling == null) { 20 20 info = n_info; 21 LoggerPerso.log(SGBD_jdbc.class, LoggerPerso.DEBUG, "creation pooling :: " + info);21 // LoggerPerso.log(SGBD_jdbc.class, LoggerPerso.DEBUG, "creation pooling :: " + info); 22 22 pooling = new PGPoolingDataSource(); 23 23 pooling.setDatabaseName(info.getDb_name());
Note: See TracChangeset
for help on using the changeset viewer.