IF conditional in SQL query

Question

IF conditional in SQL query

Navigation

#1 by (4 votes)
#2 by (3 votes)

2

I am developing a Java web application in NETBEANS and I have a query which shows numerical values only, when I execute my query it shows them in an organized table, everything works perfectly.

What I want to do are 3 conditions "IF" with the values that I pull from my Database:

If the values are less than 50 the values are painted red.
If the values are greater than 50 but less than 95 are painted orange.
If the values are greater than 96, they are painted green.

It is the final part of my development and I would really help your help, Thank you very much.

I leave the code of my query below. The rs.getString() are the values of the query.

    <%
consulta conexion = new consulta ("jdbc:oracle:thin:@localhost:1521:XE","pasaportes","oppasaportes");
    String consulta = "SELECT  DELEGACION, id_delegacion, CONTRATO   , prerequisitos_tecnologicos, equipamiento, sistema_operacion, privilegios, capacitacion, citas FROM delegaciones";

       ResultSet rs = conexion.ejeSelect(consulta);
         ResultSetMetaData rsmd = rs.getMetaData();
               int col= rsmd.getColumnCount();           
                     for(int k=1;k<=col;k++){          
                %>                                       
                <td><h9> <%= rsmd.getColumnName(k) %></h9></td>
                <%
                                }      
                %>                               
            </tr>         

            <%
             while(rs.next()){
            %>
            <tr> 
                <td><h6><%= rs.getString(1) %></h6> </td>
                <td><h6><%= rs.getString(2) %></h6> </td>
                <td><h6><%= rs.getString(3) %></h6> </td>
                <td><h6><%= rs.getString(4) %></h6> </td>
                <td><h6><%= rs.getString(5) %></h6> </td>
                <td><h6><%= rs.getString(6) %></h6> </td>
                <td><h6><%= rs.getString(7) %></h6> </td>
                <td><h6><%= rs.getString(8) %></h6> </td>
                <td><h6><%= rs.getString(9) %></h6> </td>
            </tr>
            <%
                       }
            %>

sql java if

asked by Manny Brenes 17.04.2017 в 18:46

source

2 answers

3

seria algo asi

    <%
if(rs.getInt(1)<50){
%>
         <td style="background-color:red;"><h6><%= rs.getString(1) %></h6> </td>
    <%
}else if (rs.getInt(1)<95){
%>
         <td style="background-color:orange;"><h6><%= rs.getString(1) %></h6> </td>
    <%
}else{
%>
         <td style="background-color:green;"><h6><%= rs.getString(1) %></h6> </td>
    <%
}
%>

answered by 17.04.2017 в 19:39

How to replace tags with JavaScript? Is there a way to summarize all these else if?

score 4 · Accepted Answer

I think it is not very good to use java code in the views but rather to send objects to the views and to handle them using JSTL. But I will rely on the code that you have already raised in your question.

 while(rs.next()){

         String color = "";

         //Fila 1
         if(rs.getInt(1) < 50){
             color = "style='background-color:red'";
         }else if ((rs.getInt(1) >= 50) && (rs.getInt(1) < 95)){
             color = "style='background-color:orange'";
         }else if(rs.getInt(1) >= 95){
             color = "style='background-color:green'";
         }

         %>
         <td <%= color %> ><h6><%= rs.getString(1) %></h6> </td>
         <%

         //Fila 2
         if(rs.getInt(2) < 50){
             color = "style='background-color:red'";
         }else if ((rs.getInt(2) >= 50) && (rs.getInt(2) <= 95)){
             color = "style='background-color:orange'";
         }else if(rs.getInt(2) > 95){
             color = "style='background-color:green'";
         }

         %>
         <td <%= color %> ><h6><%= rs.getString(2) %></h6> </td>
         <%

         //etc. etc. Así con cada fila que siga o con la que tu quieras
}

I assume that what you want to do is row by row, there you decide which are the rows that you are interested in painting. Note that to make numerical comparisons you must get the data as "getInt" not as "getString".

Greetings!