select from several where criteria jpa

Question

I'm developing software which allows a user to select dynamically one or several users from a database using jpa/Criteria.

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.

When an user inputs d as PC_Name, d as Name and c as Vorname, the verbose eclipselink shows me this:

[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 => [PC_Name, %d%, Name, %b%, Vorname, %c%]

This confirms what i'm saying, when i input n as name, i get the entire database because n always belongs to name..

End of edits/updates

Here is the sql version which works:

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();`

with:

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;
}

Here's what i've done with jpa:

CriteriaBuilder cb = EM.getCriteriaBuilder();
        CriteriaQuery<Mitarbeiter2> q = cb.createQuery(Mitarbeiter2.class);
        Root<Mitarbeiter2> mit= q.from(Mitarbeiter2.class);



        ParameterExpression<String> pc_name = cb.parameter(String.class,"PC_Name_p");
        ParameterExpression<String> ip = cb.parameter(String.class,"IP_p");
        ParameterExpression<String> pers_num = cb.parameter(String.class,"Personalnummer_p");
        ParameterExpression<String> name = cb.parameter(String.class,"Name_p");
        ParameterExpression<String> vorname = cb.parameter(String.class,"Vorname_p");
        ParameterExpression<String> telefon = cb.parameter(String.class,"Telefon_p");
        ParameterExpression<String> geburtstag = cb.parameter(String.class,"Geburtstag_p");
        ParameterExpression<String> gruppe = cb.parameter(String.class,"Gruppe_p");
        ParameterExpression<String> 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<Mitarbeiter2> 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<Mitarbeiter2> ergebnis= tq.getResultList();

I've incorporated the two following solutions:

In order to add dynamically where conditions, you can either use a List where each Predicate is defined like this:

> Predicate p = cb.like(...);

or you can modify dynamically a single Predicate like this:

>  Predicate p = cb.conjunction(); for ( filter : filters) {
    >     p = cb.and(p, cb.like(...)); }

and

When you do q.where you set the WHERE expression.

It does not append, meaning, the last q.where you invoked is the one set for the query.

What you need to do is build a Boolean Expression (I think ANDs is what you want).

Then at the and user q.where to set the expression.

Thanks for your help ;) .

Harald

Answer 1

The two problems i had were these: 1) How to build dynamically a query with Criteria , in order to get a query like (in SQL) this(with many different predicates)?

SELECT * FROM mitarbeitertabelle where (1) like (2) and (3) like (4) ...

This means that at runtime, the user can select one or several criteria he'll use to search an mitarbeiter(german for employee).

2)How to set a link to the attributes of my object?

1) In order to do this, you have to build a predicate, using a predicate which is modified at each adding of a new predicate if the criterion is selected.

p= criteriabuilder.and(p, criteriabuilder.like(path object linking to the right attribute, String s used for the search));

2) The right path enables us to have a link to the right attribute. I use it above. Here, the thing is that without pathes, it is not possible to have a link to your attributes so far i know ;) .

After that, you simply have to achieve your sql command by adding the where clause which takes the predicate as a parameter and the select clause which takes the root(where we'll search in the database) as a parameter:

    q.where(p);
    q.select(mit);

Then , get your results:

List<Mitarbeiter2> ergebnis= EM.createQuery(q).getResultList();

Theere is no need to fill and use parameterexpression objects, because i am not using parameterexpression objects. The string used to search is set directly in the like clause at runtime execution.

The entire code(with my variables,names,..) is:

            CriteriaBuilder cb = EM.getCriteriaBuilder();
        CriteriaQuery<Mitarbeiter2> q = cb.createQuery(Mitarbeiter2.class);
        Root<Mitarbeiter2> mit= q.from(Mitarbeiter2.class);

            Path<String> pc_name2 = mit.get("PC_Name");
        Path<String> ip2 = mit.get("IP");
        Path<String> pers_num2 = mit.get("Personalnummer");
        Path<String> name2 = mit.get("Name");
        Path<String> vorname2 = mit.get("Vorname");
        Path<String> telefon2 = mit.get("Telefon");
        Path<String> geburtstag2 = mit.get("Geburtstag");
        Path<String> gruppe2 = mit.get("Gruppe");
        Path<String> mobil2 = mit.get("Mobil");

            Predicate p = cb.conjunction();

        if(isPC_NameSuche()){
            p = cb.and(p, cb.like(pc_name2, this.getWc()+_PC_Name+this.getWc() ));

}
        if(isIPSuche()) {
            p = cb.and(p, cb.like(ip2,this.getWc()+_IP+this.getWc()));
            }
        if(isPersonalnummerSuche()) {
            p = cb.and(p, cb.like(pers_num2, this.getWc()+_Personalnummer+this.getWc() ));}

        if(isNameSuche()){
            p = cb.and(p, cb.like(name2, this.getWc()+_Name+this.getWc()));
            }
        if(isVornameSuche()) {
            p = cb.and(p, cb.like(vorname2, this.getWc()+_Vorname+this.getWc()));}
        if(isTelefonSuche()) {
            p = cb.and(p, cb.like(telefon2, this.getWc()+_Telefon+this.getWc() ));}
        if(isGeburtstagSuche()){
            p = cb.and(p, cb.like(geburtstag2, this.getWc()+_Geburtstag+this.getWc()));}
        if(isGruppeSuche()) {
            p = cb.and(p, cb.like(gruppe2, this.getWc()+_Gruppe+this.getWc()));}
        if(isMobilSuche()) {
            p = cb.and(p, cb.like(mobil2, this.getWc()+_Mobil+this.getWc()));}

        q.where(p);
        q.select(mit);
            List<Mitarbeiter2> ergebnis= EM.createQuery(q).getResultList();

Thank you very much for your help @perissf and @aviram-segal ! Don't hesitate to comment if you think something isn't easy to understand in my answer.

Answer 2

In your equal clauses, you are using this expression:

ParameterExpression<String> pc_name = cb.parameter(String.class,"PC_Name");

Try instead this:

Path<Mitarbeiter2> pc_name = mit.get("PC_Name");

Or, using Metamodel generated classes,

Path<Mitarbeiter2> pc_name = mit.get(Mitarbeiter2_.pc_name);

I cannot test the Path expressions right now, but I am sure that the following works:

q.where(cb.like(mit.get("PC_Name"), pc_name));

or:

q.where(cb.like(mit.get(Mitarbeiter2_.pc_name), pc_name));

In order to add dynamically where conditions, you can either use a List<Predicate> where each Predicate is defined like this:

Predicate p = cb.like(...);

or you can modify dynamically a single Predicate like this:

Predicate p = cb.conjunction();
for ( filter : filters) {
    p = cb.and(p, cb.like(...));
}

Finally, if you are comparing strings, don't forget to transform everything to the same case, before doing the comparison.

Answer 3

When you do q.where you set the WHERE expression.

It does not append, meaning, the last q.where you invoked is the one set for the query.

What you need to do is build a Boolean Expression (I think ANDs is what you want).

Then at the and user q.where to set the expression.