title: select from several where criteria jpa
body: <p>I'm developing software which allows a user to select dynamically one or several users from a database using jpa/Criteria.</p>
  
  <p>Edits/updates:
  -I now use a growing predicate with ands. The problem is that i'm not indicating the right field but instead just a string in which java is looking for what th user has entered.
  -I still have to check how i can work with pathes as i've been advised to do.</p>
  
  <p>When an user inputs d as PC_Name, d as Name and c as Vorname, the verbose eclipselink shows me this:</p>
  
  <pre><code>[EL Fine]: sql: 2012-07-25 15:44:13.173--ServerSession(24105143)--Connection(13480046)--Thread(Thread[main,5,main])--SELECT PERSONALNUMMER, GEBURTSTAG, GRUPPE, IP, MOBIL, NAME, PC_NAME, TELEFON, VORNAME FROM MITARBEITERTABELLE WHERE ((? LIKE ? AND ? LIKE ?) AND ? LIKE ?)
      bind =&gt; [PC_Name, %d%, Name, %b%, Vorname, %c%]
  </code></pre>
  
  <p>This confirms what i'm saying, when i input n as name, i get the entire database because n always belongs to name..</p>
  
  <p>End of edits/updates</p>
  
  <p>Here is the sql version which works:</p>
  
  <pre><code>try{
      String selectString ="SELECT  * FROM mitarbeitertabelle WHERE ";
      selectString=StringModulierung(selectString);
  
      PreparedStatement selectMitarbeiter = con.prepareStatement(selectString);
      int i =1;
      if(isNameSuche()){selectMitarbeiter.setString (i,this.wc+_Name+this.wc); i++;}
      if(isVornameSuche()){ selectMitarbeiter.setString (i, this.wc+_Vorname+this.wc); i++;}
      if(isPersonalnummerSuche()){selectMitarbeiter.setString (i, this.wc+_Personalnummer+this.wc); i++;}
      if(isPC_NameSuche()){selectMitarbeiter.setString (i, this.wc+_PC_Name+this.wc); i++;}
      if(isIPSuche()){selectMitarbeiter.setString (i, this.wc+_IP+this.wc); i++;}
      if(isTelefonSuche()){ selectMitarbeiter.setString (i, this.wc+_Telefon+this.wc); i++;}
      if(isGeburtstagSuche()){selectMitarbeiter.setString (i, this.wc+_Geburtstag+this.wc); i++;}
      if(isGruppeSuche()){ selectMitarbeiter.setString (i, this.wc+_Gruppe+this.wc); i++;}
      if(isMobilSuche()){ selectMitarbeiter.setString (i, this.wc+_Mobil+this.wc); i++;}
  
      System.out.println(selectMitarbeiter.toString());
      ResultSet ergebnis=selectMitarbeiter.executeQuery();`
  </code></pre>
  
  <p>with:</p>
  
  <pre><code>public String StringModulierung(String str){
  boolean erster=true;
  
      if(isNameSuche()){ str=str.concat("Name LIKE ? ");
          erster=false;
      }
      if(isVornameSuche()){ if(erster){str=str.concat("Vorname LIKE ? ");erster=false;
      }else{str=str.concat("AND Vorname LIKE ? ");}
  }
      if(isPersonalnummerSuche()){ if(erster){str=str.concat("Personalnummer LIKE ? ");erster=false;
      }else{str=str.concat("AND Personalnummer LIKE ? ");}
  }
      if(isPC_NameSuche()){ if(erster){str=str.concat("PC_Name LIKE ? ");erster=false;
      }else{str=str.concat("AND PC_Name LIKE ? ");}
  }
      if(isIPSuche()){ if(erster){str=str.concat("IP LIKE ? ");erster=false;
                          }else{str=str.concat("AND IP LIKE ? ");}
      }
      if(isTelefonSuche()){ if(erster){str=str.concat("Telefon LIKE ? ");erster=false;
      }else{str=str.concat("AND Telefon LIKE ? ");}
  }
      if(isGeburtstagSuche()){ if(erster){str=str.concat("Geburtstag LIKE ? ");erster=false;
                              }else{str=str.concat("AND Geburtstag LIKE ? ");}
  }
      if(isGruppeSuche()){ if(erster){str=str.concat("Gruppe LIKE ? ");erster=false;
                          }else{str=str.concat("AND Gruppe LIKE ? ");}
  }
      if(isMobilSuche()){ if(erster){str=str.concat("Mobil LIKE ? ");erster=false;
      }else{str=str.concat("AND Mobil LIKE ? ");}
  }
  
      return str;
  }
  </code></pre>
  
  <p>Here's what i've done with jpa:</p>
  
  <pre><code>CriteriaBuilder cb = EM.getCriteriaBuilder();
          CriteriaQuery&lt;Mitarbeiter2&gt; q = cb.createQuery(Mitarbeiter2.class);
          Root&lt;Mitarbeiter2&gt; mit= q.from(Mitarbeiter2.class);
  
  
  
          ParameterExpression&lt;String&gt; pc_name = cb.parameter(String.class,"PC_Name_p");
          ParameterExpression&lt;String&gt; ip = cb.parameter(String.class,"IP_p");
          ParameterExpression&lt;String&gt; pers_num = cb.parameter(String.class,"Personalnummer_p");
          ParameterExpression&lt;String&gt; name = cb.parameter(String.class,"Name_p");
          ParameterExpression&lt;String&gt; vorname = cb.parameter(String.class,"Vorname_p");
          ParameterExpression&lt;String&gt; telefon = cb.parameter(String.class,"Telefon_p");
          ParameterExpression&lt;String&gt; geburtstag = cb.parameter(String.class,"Geburtstag_p");
          ParameterExpression&lt;String&gt; gruppe = cb.parameter(String.class,"Gruppe_p");
          ParameterExpression&lt;String&gt; mobil = cb.parameter(String.class,"Mobil_p");
  
  
          Predicate p = cb.conjunction();
  
          if(isPC_NameSuche()){
              p = cb.and(p, cb.like(pc_name, this.getWc()+_PC_Name+this.getWc() ));
  
  }
          if(isIPSuche()) {
              p = cb.and(p, cb.like(ip,this.getWc()+_IP+this.getWc()));
              }
          if(isPersonalnummerSuche()) {
              p = cb.and(p, cb.like(pers_num, this.getWc()+_Personalnummer+this.getWc() ));}
  
          if(isNameSuche()){
              p = cb.and(p, cb.like(name, this.getWc()+_Name+this.getWc()));
              }
          if(isVornameSuche()) {
              p = cb.and(p, cb.like(vorname, this.getWc()+_Vorname+this.getWc()));}
          if(isTelefonSuche()) {
              p = cb.and(p, cb.like(telefon, this.getWc()+_Telefon+this.getWc() ));}
          if(isGeburtstagSuche()){
              p = cb.and(p, cb.like(geburtstag, this.getWc()+_Geburtstag+this.getWc()));}
          if(isGruppeSuche()) {
              p = cb.and(p, cb.like(gruppe, this.getWc()+_Gruppe+this.getWc()));}
          if(isMobilSuche()) {
              p = cb.and(p, cb.like(mobil, this.getWc()+_Mobil+this.getWc()));}
  
          q.where(p);
          q.select(mit);
          TypedQuery&lt;Mitarbeiter2&gt; tq = EM.createQuery(q);
  
          tq.setParameter("PC_Name_p", "PC_Name"); // searches this.getWc()+_Mobil+this.getWc() in  PC_Name !
          tq.setParameter("IP_p", "IP");
          tq.setParameter("Personalnummer_p", "Personalnummer");
          tq.setParameter( name/**entered by user*/, "Name"/**should be the field */);
          tq.setParameter("Vorname_p", "Vorname");
          tq.setParameter("Telefon_p", "Telefon");
          tq.setParameter("Geburtstag_p","Geburtstag" );
          tq.setParameter("Gruppe_p", "Gruppe");
          tq.setParameter("Mobil_p" ,"Mobil");
  
  
  
  
  
          List&lt;Mitarbeiter2&gt; ergebnis= tq.getResultList();
  </code></pre>
  
  <p>I've incorporated the two following solutions:</p>
  
  <blockquote>
    <p>In order to add dynamically where conditions, you can either use a
    List where each Predicate is defined like this:</p>
  </blockquote>
  
  <pre><code>&gt; Predicate p = cb.like(...);
  </code></pre>
  
  <blockquote>
    <p>or you can modify dynamically a single Predicate like this:</p>
  </blockquote>
  
  <pre><code>&gt;  Predicate p = cb.conjunction(); for ( filter : filters) {
      &gt;     p = cb.and(p, cb.like(...)); }
  </code></pre>
  
  <p>and</p>
  
  <blockquote>
    <p>When you do q.where you set the WHERE expression.</p>
    
    <p>It does not append, meaning, the last q.where you invoked is the one
    set for the query.</p>
    
    <p>What you need to do is build a Boolean Expression (I think ANDs is
    what you want).</p>
    
    <p>Then at the and user q.where to set the expression.</p>
  </blockquote>
  
  <p>Thanks for your help ;) .</p>
  
  <p>Harald</p>