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

package org.medias.eccad.persistance.jdbc;

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.persistance.dao.CodeDAO;
import org.medias.eccad.persistance.exception.PersistanceException;

public class CodeDAOjdbc extends GeneriqueDAOjdbc implements CodeDAO {

        public List<String> getListCodeByGrille(long id_grille) throws PersistanceException {
                Statement requete;
                String sql;
                Connection conn = getConnection();
                List<String> code = new LinkedList<String>();
                
                try {
                        requete = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
//                      sql = " select num_code,legend_code" 
//                              + " from grille join codification using (id_codification)"
//                              + " join code using (id_codification) where id_grille = " + id_grille + " order by num_code;";
                        sql = " select number_code,legend_code" 
                        + " from grille left join code on id_grille = num_code"
                        + " where id_grille = " + id_grille + " order by number_code;";

                        ResultSet resultat = requete.executeQuery(sql);
                        String legend = "";
                        while (resultat.next()) {
                                legend = resultat.getString("legend_code");
                                code.add(resultat.getString("number_code")+"-"+legend);
                        }
                        if (code.size() == 1) {
                                code.set(0, "1-"+legend);
                        }
                }
                catch (SQLException sqle) {
                        throw new PersistanceException(sqle, "getListCodeByGrille");
                }
                
                finally {
                        closeConnection();
                }
                
                return code;
        }

        public Long getNumberCode(long id_grille) throws PersistanceException {
                Statement requete;
                String sql;
                Connection conn = getConnection();
                Long code = null;
                
                try {
                        requete = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
                        sql = " select number_code" 
                                + " from code where num_code = " + id_grille + ";";
                        
                        ResultSet resultat = requete.executeQuery(sql);
                        
                        if (resultat.next()) {
                                code = resultat.getLong("number_code");
                        }
                }
                catch (SQLException sqle) {
                        throw new PersistanceException(sqle, "getListCodeByGrille");
                }
                
                finally {
                        closeConnection();
                }
                
                return code;
        }

}