How to update in JSP and Mysql Using sql sentence IN

Question

How to update in JSP and Mysql Using sql sentence IN

Navigation

#1 by (0 votes)

0

I have a question regarding the update of records with jsp and mysql, using queries with IN sentence, the problem that I present is that if I select only 1 record, the system updates me correctly, but if I select several records, then the query it does not run.

Here is the method of the class that stores the sql query.

public class GuiasDao {
ResultSet res;
Statement st;
PreparedStatement pst;

public ResultSet moverCarga(Vuelo v,Folio f)
{
    try {
        Connection conn=ConexionMysqlCargomove_db.getInstance().getConnection();

         String SQLUpdate="";

         SQLUpdate="UPDATE tvas t\n" +
                  "INNER JOIN rvas r\n" +
                  "ON(t.rvas_cod_seq_rvas = r.cod_seq_rvas)\n" +
                  "INNER JOIN awb a\n" +
                  "on(r.awb_cod_seq_awb=a.cod_seq_awb)\n" +
                  "SET t.vlos_seq_vlos ='"+v.getIdVuelo()+"'\n" +
                  "WHERE a.awb in('"+f.getNumeroFolioCompleto()+"');";   
         pst=(PreparedStatement)conn.prepareStatement(SQLUpdate);
         pst.executeUpdate(SQLUpdate);

    } catch (SQLException ex) {
        java.util.logging.Logger.getLogger(GuiasDao.class.getName()).log(Level.SEVERE, null, ex.getStackTrace());
    }
    return res;

}
}

Here the Servlet that attends the request and that requests access to the GuiasDao class to obtain the information from the database.

    protected void processRequest(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {
    response.setContentType("text/html;charset=UTF-8");

    Vuelo vuelo1=new Vuelo();
    Folio f=new Folio();
    PrintWriter out = response.getWriter();
    Connection con=ConexionMysqlCargomove_db.getInstance().getConnection();
    GuiasDao gdao = new GuiasDao();
    ArrayList<Guia> guia = new ArrayList<Guia>();
    Guia  g=new Guia();

    vuelo1.setIdVuelo(Integer.parseInt(request.getParameter("txt_id_vuelo_disp")));
    f.setNumeroFolioCompleto((request.getParameter("txt_mostrar_guias").toString()));
    PreparedStatement ps=(PreparedStatement)gdao.moverCarga(vuelo1, f);


   try {

    if(request.getParameter("txt_mostrar_guias")==null ||request.getParameter("txt_id_vuelo_disp")==null)
    {

        out.println("<!DOCTYPE html>");
        out.println("<html>");
        out.println("<head>"
                + "<link rel='stylesheet' href='bootstrap/css/bootstrap.min.css'>");
        out.println("<title>Servlet Mover carga</title>");
        out.println("</head>");
        out.println("<body>");

        out.println("<form>");
        out.println("<div class='form-row'>");

        out.println("<p> Error Debe seleccionar las guias que desea mover</p>");
        out.println("<input type='button' value='volver' onclik='location.href='movimientosCarga2.jsp''> Error Debe seleccionar las guias que desea mover</p>");   
    }
    else
    {

        out.println("<p> Registro Actualizado con exito </p>");
        out.println("<p> se ha asignado el vuelo :'"+vuelo1.getIdVuelo()+"'</p>");
        out.println("<p> Las guias afectadas son las siguientes:"+f.getNumeroFolioCompleto()+"</p>");
        out.println("<input type='button' class='btn btn-primary' value='volver' ONCLICK=location.href='movimientosCarga2.jsp'>");
    }

        out.println("</div>");
        out.println("</form>"); 

    }
    catch(java.lang.NumberFormatException ex)
    {
        out.println("<h1>'"+ex.getMessage()+"'</h1>");
    }
    finally {

        out.println("</body>");
        out.println("</html>");
        out.close();
    }

}

Here's the html form that has 3 tables the div that says assigned guides is the one that shows the records in a text field called txt_show_guides, the records are of type String and are shown separated by commas next to each other thanks to a function with javascript.

<div class="content container-fluid col-sm-8">
    <div class=" panel-default">
        <div class="title panel-heading">Resultado Vuelos Disponibles</div>
        <div class="panel-body panel panel-primary">
    <form method="POST" action="moverCarga2.do">

       <table class="table table-hover table table-responsive">
            <tr>
                <th class="bg bg-primary">Código</th>
                <th class="bg bg-primary">Iata</th>
                <th class="bg bg-primary">Número</th>
                <th class="bg bg-primary">Fecha</th>
                <th class="bg bg-primary">Estado</th>
                <th class="bg bg-primary">Transito</th>
                <th class="bg bg-primary">Ruta</th>

            </tr>
<%
for(int i=0; i<vuelomovidis.size(); i++)
           {   
               out.println("<tr>");
               out.println("<td><input type='text' size=1 name='txt_id_vuelo_disp' class='form-control' value='"+vuelomovidis.get(i).getIdVuelo()+"'></td>");
               out.println("<td><input type='text' size=10 class='form-control'value='"+vuelomovidis.get(i).getIataVuelo()+"'></td>");
               out.println("<td><input type='text' size=10 class='form-control'value='"+vuelomovidis.get(i).getNumeroVuelo()+"'></td>");
               out.println("<td><input type='text' class='form-control'value='"+vuelomovidis.get(i).getFechaVuelo()+"'></td>");
               out.println("<td><input type='text' class='form-control'value='"+vuelomovidis.get(i).getEstadoVuelo()+"'></td>");
               out.println("<td><input type='text' size=10 class='form-control'value='"+vuelomovidis.get(i).getTipoTransitoVuelo()+"'></td>");
               //out.println("<td><input type='text' class='form-control'value='"+vuelomovi.get(i).getFechaCreacionVuelo()+"'></td>");
               out.println("<td><input type='text' class='form-control'value='"+vuelomovidis.get(i).getRutaVuelo()+"'></td>");
               //out.println("<td><input type='submit' formtarget='frame_vuelo' class='btn btn-success'value='Ver Guías'></td>");


           }
     }
     catch(java.lang.NullPointerException ex)

            {
                out.println("<tr>");
            }


%> 
        </table> 
</div>
        <br>
    <br>
    </div>

<div class="content container-fluid col-lg-10">
    <div class="left panel-default">
        <div class="title panel-heading">Seleccionar guías a mover</div>
        <div class="panel-body panel panel-primary">    

                <table class="table table-hover table table-responsive col-sm-6" id="tabla1">
                   <tbody>
                   <tr id="a1">

                       <th class="bg bg-primary">Awb</th>
                       <th class="bg bg-primary">Origen</th>
                       <th class="bg bg-primary">Destino</th>
                       <th class="bg bg-primary">Piezas</th>
                       <th class="bg bg-primary">Kilos</th>
                       <th class="bg bg-primary">Volumen</th>
                       <th class="bg bg-primary">Id Vuelo</th>
                       <th class="bg bg-primary">Tramo</th>
                       <th class="bg bg-primary">Mover</th>

                   </tr>



<% 



{
    for(int i=0; i<guia.size(); i++)
           {


                        out.println("<tr id='a2'>");
                        out.println("<td class='cantidad'><input type='text' name='txt_guia'  class='cantidad form-control' value='"+guia.get(i).getNumeroGuia()+"'></td>");

                        out.println("<td class='check'>"+guia.get(i).getOrigenGuia()+"</td>");
                        out.println("<td class='check'>"+guia.get(i).getDestinoGuia()+"</td>");
                        //out.println("<td>"+guia.get(i).getOrigenVueloReserva()+"</td>");

                        out.println("<td class='check'>"+guia.get(i).getPiezasGuia()+"</td>");
                        out.println("<td class='check'>"+guia.get(i).getKilosGuia()+"</td>");
                        out.println("<td class='check'>"+guia.get(i).getVolumenGuia()+"</td>");
                        out.println("<td class='check'>"+guia.get(i).getIdVueloGuia()+"</td>");
                        out.println("<td class='check'>"+guia.get(i).getIdTramoGuia()+"</td>");

                        out.println("<td><input type='checkbox' name='check_guia' class='form-control checkbox' value='"+guia.get(i).getNumeroGuia()+"'></td>");



           }     

}
catch(java.lang.NullPointerException ex)
{
    out.println("<p>'"+ex.getMessage()+"'</p>");
    out.println("</tr>");
}

%>

  </tbody>
                </table>
        </div>
    </div>      
    </div>


 <div class="content container-fluid col-lg-10">
    <div class="right panel-default">
        <div class="title panel-heading">Guías Asignadas</div>
        <div class="panel-body panel panel-primary">    
                <table class="table table-hover table table-responsive col-sm-6" id="tabla2">
                   <tbody>
                   <tr>

                       <th class="bg bg-primary">Awb</th>
                       <th class="bg bg-primary">Origen</th>
                       <th class="bg bg-primary">Destino</th>
                       <th class="bg bg-primary">Piezas</th>
                       <th class="bg bg-primary">Kilos</th>
                       <th class="bg bg-primary">Volumen</th>
                       <th class="bg bg-primary">Id Vuelo</th>
                       <th class="bg bg-primary">Tramo</th>
                       <th class="bg bg-primary">Acción</th>
                   </tr>

                </tbody>  
               </table>
            <input type="text" id="txt_mostrar" class="form-control" name="txt_mostrar_guias">

<br><br><br><br>
<input type="submit" class="btn btn-primary" value="Asignar" id="boton01">

<input type="resert" class="btn btn-primary" value="Limpiar">

</form> 

</div>
    </div>
    </div>

Here the function that shows me the records in the text field side by side separated by commas.

<script>
$(document).ready(function(){

$ ('input [type = checkbox]'). click (function () { if ($ (this) .is (": checked")) {
var tr = $ (this) .parents ("tr"). appendTo ("# table2 tbody"); var data = $ (this) .parents ('tr'). find ('input: text [name="txt_guia"]'). val (); var value = $ ('# txt_show'). val (); if (value == '') { $ ('# txt_show'). val (data); } else { $ ('# txt_show'). val (value + ',' + data); }

}else{
    // el checkbox esta desmarcado
    // movemos la columna a la tabla1
    var tr=$(this).parents("tr").appendTo("#tabla1 tbody");
    $('#txt_mostrar').val("");
    var valor = '';
    $('input[type=checkbox]').each(function(i, check) {
      if($(check).is(":checked")) {
        var dato = $(this).parents('tr').find('input:text[name="txt_guia"]').val();
        var valor = $('#txt_mostrar').val();
        if (valor == '') {
          $('#txt_mostrar').val(dato);
        } else {
          $('#txt_mostrar').val(valor + ',' + dato);
        }
      }
    });
}

});

});

</script>

Here is an image of when the guides are selected

please your help to know if the query is wrong or some other code, since what I want is to update the records that you select, be it 1 or several.

mysql java-ee jsp

asked by Simón Pereira Vigouroux 08.08.2018 в 16:53

source

1 answer

Fatal error: Uncaught ArgumentCountError Blade - Go through two arrays

score 0 · Answer 1

After a long conversation in the comments, it was identified that the problem was in how the Strings arrive at the servlet or rather, how they are being sent to the query

SQLUpdate="UPDATE tvas t\n" +
              "INNER JOIN rvas r\n" +
              "ON(t.rvas_cod_seq_rvas = r.cod_seq_rvas)\n" +
              "INNER JOIN awb a\n" +
              "on(r.awb_cod_seq_awb=a.cod_seq_awb)\n" +
              "SET t.vlos_seq_vlos ='"+v.getIdVuelo()+"'\n" +
              "WHERE a.awb in('"+f.getNumeroFolioCompleto()+"');";

The problem is that the checkboxes that are filled send the information of the form 'numero1, numero2, numero3' therefore the form of the in would be as follows:

IN ('NUM1, NUM2,NUM3') , which would not work, IN should have the values in single quote separated by comma, depending on the RDBM that is occupied.

Then the problem is transferred to one of

One way is that you can take the same string and transform it to the structure you need.

Snippet or function

public static void main(String[] args) {
    // Tu String, por supuesto lo obtienes como un parámetro en
    // tu función una vez que establezcas donde dejar, nombre, etc
    String str =  "045-1234567,045-4522344";

    // Similar a PHP explode function, explotar el string en la coma
    // para generar un array de string
    String [] arrOfStr = str.split(",", 5);

    // El string que se generará
    String strret= "";

    // For-each loop
    for (String a : arrOfStr){
                // vamos agregando cada string envuelto en comillas
                // y dejar una coma al final
                strret = strret+ "'"+a+"',";
    }

    // remover la coma al final, o sumale + "0"
    if (strret.charAt(strret.length() - 1) == ',') {
        strret = strret.substring(0, strret.length() - 1);
    }

    System.out.println(strret);
    //o return strret;
}

Exit

  '045-1234567','045-4522344'

Now this string is passed to the query

   String query = "UPDATE tablita "
                + "SET valor = 'algo' "
                + "WHERE alguna_columna IN ("+resultado_de_la_función+")";