JavaScript - how to execute sql statement using if statement in jsp

Asked By Aina Syafiqah on 16-Jun-12 09:32 AM

hi. i want to write a code to execute the sql statement.. to complete this task.. i was thinking to use IF statement..

this is the example of the queries.

select member_id,Fname,LName,age,state,emailfrom member;
where bmi_value = &bmi_value

select member_id,Fname,LName,age,state,email
from member;
where bmi_value < 18.50

select member_id,Fname,LName,age,state,email
from member;
where bmi_value between 18.50 AND 24.99

select member_id,Fname,LName,age,state,email
from member;
where bmi_value between 25.00 AND 29.99

select member_id,Fname,LName,age,state,email
from member;
where bmi_value between 30 AND 34.99

select member_id,Fname,LName,age,state,email
from member;
where bmi_value between 35 AND 39.99

select member_id,Fname,LName,age,state,email
from member;
where bmi_value > 40

my idea is, when i insert 35 in the search text field.. let say..

if(bmi_value==35)
{ then it will execute this query and display result under the search box.
select member_id,Fname,LName,age,state,email
from member;
where bmi_value between 35 AND 39.99
}

is it possible to do this? i don't know the right way to write this code. please help. thank you :)

Jitendra Faye replied to Aina Syafiqah on 18-Jun-12 09:28 AM

refer this sample code-

Reference from-

http://www.roseindia.net/jsp/prepared-statement-query.shtml

Solution-

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" 
"http://www.w3.org/TR/html4/loose.dtd" > 
      <%@ page import="java.sql.*" %> 
<%@ page import="java.io.*" %> 
<HTML>
<HEAD>
  <TITLE>insert data using prepared statement </TITLE>
</HEAD>
    <BODY bgcolor="#ffffcc">
  <font size="+3" color="green"><br>Welcome in www.roseindia.net !</font>
  <FORM action="prepared_statement_query.jsp" method="get">
    <TABLE style="background-color: #ECE5B6;" WIDTH="30%" >
         <TR>
              <TH width="50%">Name</TH>
                  <TD width="50%"><INPUT TYPE="text" NAME="name"></TD>
          </tr>
      <TR>
             <TH width="50%">City</TH>
                 <TD width="50%"><INPUT TYPE="text" NAME="city"></TD>
          </tr>
          <TR>
             <TH width="50%">Phone</TH>
                 <TD width="50%"><INPUT TYPE="text" NAME="phone"></TD>
          </tr>
                  <TR>
              <TH></TH>
                  <TD width="50%"><INPUT TYPE="submit" VALUE="submit"></TD>
          </tr>
   </TABLE>
<%
   String name = request.getParameter("name");
   String city = request.getParameter("city");
   String phone = request.getParameter("phone");
   /* Create string of connection url within specified 
   format with machine name, 
    port number and database name. Here machine name id 
    localhost and database name is student. */
    String connectionURL = "jdbc:mysql://localhost:3306/student";
          // declare a connection by using Connection interface 
    Connection connection = null;
        // declare object of Statement interface that uses for 
    executing sql statements.
     PreparedStatement pstatement = null;
         // Load JBBC driver "com.mysql.jdbc.Driver"
     Class.forName("com.mysql.jdbc.Driver").newInstance();
          int updateQuery = 0;
     
         // check if the text box is empty
         if(name!=null && city!=null && phone!=null){
                         // check if the text box having only blank spaces
             if(name!="" && city!="" && phone!="") {
                         try {
              /* Create a connection by using getConnection()
              method that takes parameters of string type 
              connection url, user name and password to connect 
                to database. */
              connection = DriverManager.getConnection
              (connectionURL, "root", "root");
                            // sql query to insert values in the secified table.
              String queryString = "INSERT INTO stu_info(Name,
              Address,Phone) VALUES (?, ?, ?)";
                      /* createStatement() is used for create statement
              object that is used for 
                sending sql statements to the specified database. */
              pstatement = connection.prepareStatement(queryString);
              pstatement.setString(1, name);
                          pstatement.setString(2, city);
                          pstatement.setString(3, phone);
              updateQuery = pstatement.executeUpdate();
                            if (updateQuery != 0) { %>
                   <br>
                   <TABLE style="background-color: #E3E4FA;" 
                   WIDTH="30%" border="1">
                      <tr><th>Data is inserted successfully 
                    in database.</th></tr>
                   </table>
              <%
              }
            } 
            catch (Exception ex) {
            out.println("Unable to connect to batabase.");
   
               }
            finally {
                // close all the connections.
                pstatement.close();
                connection.close();
            }
          }
        }
%>
  </FORM>
 </body> 
</html>