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>