How to enter a sql query in java, replacing hard code of a map?

0

I hope and they are well, I have a problem, what I need is to replace the hard code of a java map, for the results that an sql query returns to me, the hard code is this:

{
        this.correspondencia=new HashMap<String, String>();
        correspondencia.put("A","101");
        correspondencia.put("B","102");
        correspondencia.put("B1","102");
        correspondencia.put("B2","102");
        correspondencia.put("B3","102");
        correspondencia.put("B4","102");
        correspondencia.put("BPR","102");
        correspondencia.put("C","102");
        correspondencia.put("DF","102");
        correspondencia.put("EFA","102");
        correspondencia.put("EXF","102");
        correspondencia.put("GPF","102");
        correspondencia.put("MB","102");
        correspondencia.put("MFA","102");
        correspondencia.put("P","102");
        correspondencia.put("PPR","102");
        correspondencia.put("PV","102");
        correspondencia.put("TF","102");
        correspondencia.put("UHN","102");
        correspondencia.put("B","103");
        correspondencia.put("B-1","103");
        correspondencia.put("B-2","103");
        correspondencia.put("B-3","103");
        correspondencia.put("B-4","103");
        correspondencia.put("B-5","103");
        correspondencia.put("B-6","103");
        correspondencia.put("DM","103");
        correspondencia.put("E","103");
        correspondencia.put("E1","103");
        correspondencia.put("E2","103");
        correspondencia.put("EC","103");
        correspondencia.put("EXM","103");
        correspondencia.put("IF","103");
        correspondencia.put("TM","103");
        correspondencia.put("TM1","103");
        correspondencia.put("DNC","104");
        correspondencia.put("NC","104");
        correspondencia.put("NC1","104");
        correspondencia.put("NC2","104");
        correspondencia.put("NC3","104");
        correspondencia.put("NC4","104");
        correspondencia.put("NC5","104");
        correspondencia.put("NC6","104");
        correspondencia.put("NCC","104");
        correspondencia.put("TNC","104");
        correspondencia.put("TNC1","104");
        correspondencia.put("B","105");
        correspondencia.put("EX","105");
        correspondencia.put("F","107");
        correspondencia.put("GB","108");

    }


    private double calculaInversion(String nb_serie,List<VivVAACInversion> catalogoInversion) {
        String clave=this.correspondencia.get(nb_serie);
        double id=0;

        for (VivVAACInversion vivVAACInversion : catalogoInversion) {
            if(clave.equalsIgnoreCase(vivVAACInversion.getNombreClave())){
                id=vivVAACInversion.getIdInversion();
            }
        }

        return id;
    }

And this is my SQL query:

SELECT
    tviv055_intiserie.nb_serie,
    tviv053_inversion.nb_clave
FROM
    ( tviv056_relserie
    INNER JOIN tviv055_intiserie ON tviv056_relserie.cd_serie = tviv055_intiserie.cd_serie )
    INNER JOIN tviv053_inversion ON tviv056_relserie.cd_inversion = tviv053_inversion.cd_inversion

I have some examples of code that more or less meet what I need:

public Map<String, String[]> getProductos() {
        ResultSet rs = null;
        Map<String, String[]> map = new HashMap<String, String[]>();
        try {
                if (conn != null) { //verifico que mi conexión existe
                        String sql = "SELECT * FROM PRODUCTOS";
                        Statement stmt0 = conn.createStatement();
                        rs = stmt0.executeQuery(sql);
                        while ( rs.next() ) {
                                String arreglo[] = {rs.getString("SKU"),  rs.getString("PRECIO"), rs.getString("IMPUESTO")};
                                map.put(rs.getString("UPC"), arreglo);

                        }
                }
        } catch (SQLException e) {
                log.info( ".: SQL exception:. " + e.getMessage());
                return null;
        }
}

Note: I have already modified the code to how I need it, and everything completed is as follows:

So I leave the final code:

 private Map<String, String> correspondencia;

    {
        this.correspondencia=new HashMap<String, String>();
        //Se reemplaza código duro por dinámico.
        ResultSet rs = null;
        Connection conn = null;
        Map<String, String[]>map = new HashMap<String, String[]>();
        try {
            if(conn !=null) {
                String sql = "SELECT\r\n" + 
                        "    tviv055_intiserie.nb_serie,\r\n" + 
                        "    tviv053_inversion.nb_clave\r\n" + 
                        "FROM\r\n" + 
                        "    ( tviv056_relserie\r\n" + 
                        "    INNER JOIN tviv055_intiserie ON tviv056_relserie.cd_serie = tviv055_intiserie.cd_serie )\r\n" + 
                        "    INNER JOIN tviv053_inversion ON tviv056_relserie.cd_inversion = tviv053_inversion.cd_inversion";
                Statement stmt0 = conn.createStatement();
                while (rs.next()) {
                    //Se llena el map con el resultado de la consulta.
                    correspondencia.put(rs.getString("NB_SERIE"), rs.getString("NB_CLAVE"));
                }
            }
        }catch(SQLException e) {
            System.out.println( ".: SQL exception:. " + e.getMessage());
            }

    }


    private double calculaInversion(String nb_serie,List<VivVAACInversion> catalogoInversion) {
         String clave=this.correspondencia.get(nb_serie);
         double id=0;

         for (VivVAACInversion vivVAACInversion : catalogoInversion) {
              if(clave.equalsIgnoreCase(vivVAACInversion.getNombreClave())){
                   id=vivVAACInversion.getIdInversion();
              }
         }
         return id;
    }

And if I comment the this this happens:

I would practically already test if all the code would work, I hope and help me with the last thing, the one that helped me adapt the code was the user J.Castro

Greetings and I'm waiting for you. :)

    
asked by JUAN JOSE BUSTAMANTE SOLIS 26.04.2018 в 16:55
source

1 answer

1

Adapting your code a bit would be something like this:

private static Map<String, String> correspondencia;

public static void correspondencia(){


    correspondencia=new HashMap<>();
    //Se reemplaza código duro por dinámico.
    ResultSet rs = null;
    Connection conn = null;
    try {
        if(conn !=null) {
            String sql = "SELECT\r\n" + 
                    "    tviv055_intiserie.nb_serie,\r\n" + 
                    "    tviv053_inversion.nb_clave\r\n" + 
                    "FROM\r\n" + 
                    "    ( tviv056_relserie\r\n" + 
                    "    INNER JOIN tviv055_intiserie ON tviv056_relserie.cd_serie = tviv055_intiserie.cd_serie )\r\n" + 
                    "    INNER JOIN tviv053_inversion ON tviv056_relserie.cd_inversion = tviv053_inversion.cd_inversion";
            Statement stmt0 = conn.createStatement();
            while (rs.next()) {
                //Se llena el map con el resultado de la consulta.
                correspondencia.put(rs.getString("NB_SERIE"), rs.getString("NB_CLAVE"));
            }
        }
    }catch(SQLException e) {
        System.out.println( "SQL exception: " + e.getMessage());  
    }

}

private double calculaInversion(String nb_serie,List<VivVAACInversion> catalogoInversion) {
    String clave=this.correspondencia.get(nb_serie);
    double id=0;

    for (VivVAACInversion vivVAACInversion : catalogoInversion) {
        if(clave.equalsIgnoreCase(vivVAACInversion.getNombreClave())){
            id=vivVAACInversion.getIdInversion();
        }
    }

    return id;
}

The static fill is replaced by the dynamic one that is brought in BD .

    
answered by 26.04.2018 в 17:05