Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- //package searchbook;
- import java.io.*;
- import javax.servlet.*;
- import javax.servlet.http.*;
- import java.sql.*;
- import java.util.*;
- public class SearchBook extends HttpServlet {
- public void doPost(HttpServletRequest request, HttpServletResponse response)
- throws ServletException, IOException {
- response.setContentType("text/html");
- HttpSession session = request.getSession(true);
- List booklist = new ArrayList();
- Connection con = null;
- String url = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=" + "C:\users\ppreeti\executive_db.accdb";
- String driver = "sun.jdbc.odbc.JdbcOdbcDriver";
- String user = "";
- String pass = "";
- String category = "";
- category = request.getParameter("input");
- String sqlquery = "select Index1.link_id "
- + "FROM Index1 "
- + " WHERE Index1.index_name LIKE '%" + category + "%' ";
- String sqlResult = null;
- try {
- Class.forName(driver);
- con = DriverManager.getConnection(url, user, pass);
- try {
- Statement st = con.createStatement();
- System.out.println("Connection created 1");
- ResultSet rs = st.executeQuery(sqlquery);
- while (rs.next()) {
- sqlResult = rs.getString(1);
- }
- System.out.println("Result retreived 1");
- //System.out.println('"sqlquery"');
- } catch (SQLException s) {
- System.out.println("SQL statement is not executed! " + s);
- }
- } catch (Exception e) {
- e.printStackTrace();
- }
- System.out.println("************");
- String sqlq = "";
- int flag = 0;
- /*if(sqlResult.equals("1"))
- {
- flag=1;
- System.out.println("entered if block for section!");
- sqlq="select Report.Report_Name,Report.Report_ID,Report.Section_ID,Report.Contact_ID,Report.link_id FROM Report "
- + " where Report.Report_Name LIKE '%"+category+"%' ";
- sqlq="select Section.Section_Name , Report.Report_Name , Report.Link, Contact.Contact_Name "
- + "FROM Section , Report , Contact"
- + " WHERE Report.Section_ID=Section.Section_ID and Contact.Contact_ID=Report.Report_ID "
- + "and Section.Section_Name = '"+category+"' ";
- } */
- if (sqlResult.equals("1")) {
- flag = 1;
- System.out.println("entered if block for section!");
- /*sqlq="select Report.Report_Name,Report.Report_ID,Report.Section_ID,Report.Contact_ID,Report.link_id FROM Report "
- + " where Report.Report_Name LIKE '%"+category+"%' ";*/
- sqlq = "select distinct Section.Section_Name , Report.Report_Name , Report.Link, Contact.Contact_Name "
- + "FROM Section , Report , Contact"
- + " WHERE Report.Section_ID=Section.Section_ID and Contact.Contact_ID=Report.Contact_ID and Section.Section_Name LIKE '%" + category + "%' ";
- // + "and Section.Section_Name = '"+category+"' ";
- }
- if (sqlResult.equals("2")) {
- flag = 1;
- System.out.println("entered if block for report!");
- /*sqlq="select Report.Report_Name,Report.Report_ID,Report.Section_ID,Report.Contact_ID,Report.link_id FROM Report "
- + " where Report.Report_Name LIKE '%"+category+"%' ";*/
- sqlq = "select distinct Section.Section_Name , Report.Report_Name , Report.Link, Contact.Contact_Name "
- + "FROM Section , Report , Contact"
- + " WHERE Report.Section_ID=Section.Section_ID and Contact.Contact_ID=Report.Contact_ID and Report.Report_Name LIKE '%" + category + "%' ";
- // + "and Report.Report_Name = '"+category+"' ";
- }
- if (sqlResult.equals("3")) {
- flag = 1;
- System.out.println("entered if block for metrics !");
- /*sqlq="select Report.Report_Name,Report.Report_ID,Report.Section_ID,Report.Contact_ID,Report.link_id FROM Report "
- + " where Report.Report_Name LIKE '%"+category+"%' "*/
- ;
- sqlq = "select distinct Section.Section_Name , Report.Report_Name , Report.Link, Contact.Contact_Name "
- + "FROM Section , Report , Contact,Metrics"
- + " WHERE Report.Section_ID=Section.Section_ID and Contact.Contact_ID=Report.Contact_ID and Metrics.Report_ID=Report.Report_ID "
- + "and Metrics.Metric_Name LIKE '%" + category + "%' ";
- }
- if (sqlResult.equals("4")) {
- flag = 1;
- System.out.println("entered if block for contact name!");
- /*sqlq="select Section.Section_Name , Report.Report_Name , Report.Link, Contact.Contact_Name, Metrics.Metric_Name "
- + "FROM Section , Report , Contact, Metrics"
- + " WHERE Report.Section_ID=Section.Section_ID and Metrics.Report_ID=Report.Report_ID "
- + "and Report.Report_ID IN (SELECT Report.Report_ID FROM Report WHERE "
- + "Contact.Contact_ID=Report.Contact_ID and Contact.Contact_Name LIKE '%"+category+"%' and Metrics.Metric_Segment = 'M') ORDER BY Report_Name ";*/
- sqlq = "select distinct Section.Section_Name , Report.Report_Name , Report.Link, Contact.Contact_Name "
- + "FROM Section , Report , Contact"
- + " WHERE Report.Section_ID=Section.Section_ID and Contact.Contact_ID=Report.Contact_ID "
- + "and Contact.Contact_Name LIKE '%" + category + "%' ";
- }
- if (flag == 1) {
- try {
- Class.forName(driver);
- con = DriverManager.getConnection(url, user, pass);
- try {
- Statement st = con.createStatement();
- System.out.println("Connection created");
- ResultSet rs = st.executeQuery(sqlq);
- System.out.println("Result retreived for 2nd query ");
- while (rs.next()) {
- List<String> book = new ArrayList<String>();
- String Name = rs.getString("Section_Name");
- String reportName = rs.getString("Report_Name");
- String link = rs.getString("Link");
- String contactName = rs.getString("Contact_Name");
- /* String metricName=rs.getString("Metric_Name");*/
- //String reportId=rs.getString("Report_ID");
- book.add(Name);
- book.add(reportName);
- book.add(link);
- book.add(contactName);
- /* book.add(metricName);*/
- //book.add(reportId);
- /* book.add(ind_id);
- book.add(ind_name);*/
- booklist.add(book);
- }
- } catch (SQLException s) {
- s.printStackTrace();
- System.out.println("SQL statement is not executed in 2nd query! " + s);
- }
- } catch (Exception e) {
- e.printStackTrace();
- }
- }
- System.out.println("And it came here lastly !");
- request.setAttribute("booklist", booklist);
- RequestDispatcher dispatcher = getServletContext().getRequestDispatcher("/searchbook.jsp");
- dispatcher.forward(request, response);
- System.out.println("***************************************************************************************");
- }
- }
Add Comment
Please, Sign In to add comment