by Dinesh Thakur Category: Servlet with MySql Database

This function will show to the result according to Where Query in mysql.

Firstly, we need to make a table named 'inq' into a database named 'dbase'. And after that we have to call the java Packages from java library. Now to define a class named 'MySqlWhereClauseJavaServlet' which is been, extend 'HttpServlet'. And we need to be load the driver for database calling. To getting request we need to define serviceMethod().for whole procedure to produce output need to define some mandatory variables like connection (for making link between frontend and Backend).Resultset which represents the output table of data resulted from a SELECT query. preparedStatement the object of this statement will use to execute the query like executeQuery() it will return the object to resultset for produced output.preparedStatement will execute written query (SELECT emp_id, last_name,salary, manager_id FROM inq WHERE manager_id = 410").At the last step we need to call the doGet() for getting request output on a web browser.

On to the web browser, we need to use 'HTML' code and its tags to get Output in Tabular form or in a manner Look.

import java.io.IOException;

import java.io.PrintWriter;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
public class MySqlWhereClauseJavaServlet extends HttpServlet
 {
     public void service(HttpServletRequest rq, HttpServletResponse rp)throws IOException, ServletException
        {
             rp.setContentType("text/html");
             PrintWriter disp = rp.getWriter();
             String driver = "com.mysql.jdbc.Driver";
             String url = "jdbc:mysql://localhost/dbase";
             String uid = "root";
             String psw = "root";
             Connection con=null;
             PreparedStatement ps = null;
             ResultSet rs;
             try
              {
                   Class.forName(driver);
                   con = DriverManager.getConnection(url,uid,psw);
                   ps=con.prepareStatement("SELECT emp_id, last_name,salary, manager_id FROM inq WHERE manager_id = 410");
                   rs = ps.executeQuery();
                String title = "Info With Where Clause";
                  String docType ="<!doctype html public \"-//w3c//dtd html 4.0 " + "transitional//en\">\n";
                disp.println(docType + "<html>\n" + "<head><title>" + title + "</title></head>\n" +
                  "<body bgcolor=\"#f4efef\">\n" + "<h3 align=\"center\">" + title + "</h3>\n" + "<ul>\n" +
                "<table width=\"50%\" border=\"1\" align=\"center\">\n" + "<th>Employee Id</th><th>Last Name</th><th>Salary</th><th>Manager Id</th>\n"+ "</body> </html>");
                  while(rs.next())
                      {
                             int e_id = rs.getInt("emp_id");
                        String l_name = rs.getString("last_name");
                        int sal = rs.getInt("salary");
                        int m_id = rs.getInt("manager_id");
                          disp.println("<tr><td align=\"center\">"+ e_id +"<td align=\"center\">"+ l_name +"<td align=\"center\">"+ sal +         "<td align=\"center\">"+ m_id +"</td></tr>" );           
                      }     
             }        
                          catch(Exception e) 
                              {
                                   e.printStackTrace();
                              }
                                   disp.close();
     }
                         public void doPost(HttpServletRequest rq,HttpServletResponse rp)throws IOException,ServletException
                           {
                                     doGet(rq,rp);
                           }
 }
WEB.xml
 
<servlet>
   <servlet-name>MySqlWhereClauseJavaServlet</servlet-name>
   <servlet-class>MySqlWhereClauseJavaServlet</servlet-class>
</servlet>
<!-- servlet mapping -->
<servlet-mapping>
   <servlet-name>MySqlWhereClauseJavaServlet</servlet-name>
   <url-pattern>/MySqlWhereClauseJavaServlet</url-pattern>
</servlet-mapping>




About Dinesh Thakur

Dinesh ThakurDinesh Thakur holds an B.SC (Computer Science), MCSE, MCDBA, CCNA, CCNP, A+, SCJP certifications. Dinesh authors the hugely popular blog. Where he writes how-to guides around Computer fundamental , computer software, Computer programming, and web apps. For any type of query or something that you think is missing, please feel free to Contact us.



Related Articles