title: How do I retrieve data from multiple tables (PostgreSQL) in Java Servlet?
body: <p>I having difficulties on retrieving data from multiple tables. It is going to be a search form and its a relational database. </p>
  
  <p><strong>Question1:</strong> How do I retrieve data from multiple tables now?</p>
  
  <p><strong>Question2:</strong> At the same time another problem I face is that I can not get any results if I try to search only by Name or Lastname. I get results only when I use date of birth. Why?</p>
  
  <pre><code>protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
  
          java.io.PrintWriter out = response.getWriter();
          Connection conn = null;{ // while there is no connections, proceed to the next step
          try {
              Class.forName("org.postgresql.Driver"); // importing the driver to use the getConnection method
              conn = DriverManager.getConnection(
                      "jdbc:postgresql://localhost:5432/caglar", "postgres", //?searchpath=cag
                      "abracadabra");
              System.out.println("Connected to the database"); // console message
              String  agent_name  = request.getParameter("givenname"); // variable - reads from user input
              String  agent_lastname  = request.getParameter("familyname"); // variable - reads from user input
              String  dob  = request.getParameter("birthyear"); // variable - reads from user input
  
              ArrayList al=null;
              ArrayList agent_list =new ArrayList();
  
              //Problem 1: If dob is not given, it is not searching by name or lastname.
  
              //String query = "select * from agent where givenname='"+agent_name+"' or familyname='"+agent_lastname+"' or birthyear='"+dob+"' order by givenname";
  
              String query = "select * from agent where givenname='"+agent_name+"' or familyname='"+agent_lastname+"' ";
              if(dob!=null &amp;&amp; !"".equals(dob)) // if date of birth fiel is left blank, it will still proceed to the results page
                query = query + " or birthyear='"+dob+"'"; // if date of birth exists, it will take it into account as well
  
              query = query+  "order by givenname"; // ordering by first name
  
  
              System.out.println("query" + query); // console message
              Statement st = conn.createStatement(); // connection statement
              ResultSet  rs = st.executeQuery(query); // executing our query
  
  
              while(rs.next())
              {
                  al  = new ArrayList();
  
                al.add(rs.getString(1));//id
                al.add(rs.getString(2));//dob
                al.add(rs.getString(3));//name
                al.add(rs.getString(4));//lastname
                System.out.println("al :: "+al);
                agent_list.add(al);
              }
  
              request.setAttribute("agentList",agent_list);
  </code></pre>