0

I'm introducing with Criteria API and I have a problem with making queries, I'm trying to do this for 2 days so I need your help. I have to get US zip codes from database, but as United States have cities with the same name in many states and counties, I have passed state, county and city to the Facade (String is formatted like "state - county - city"), and now I have to make query by using Criteria API. So, the logic is to take zip code where state = "state_name", county = "county_name" and city = "city_name" but my effort don't give any results. I debugged code, and it breaks down on the line with "cq.where(...)". By tutorials, I thought this will work: (e.g. I passed parameter "cities" formatted like this: HI - HAWAII - HONUMU so I need zip code for HONUMU)

    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery cq = cb.createQuery();

    for (String c : cities) {
        String[] b = c.split("-");
        String state= b[0].trim();
        String county= b[1].trim();
        String city = b[2].trim();


        Root<ZipCodes> zip = cq.from(ZipCodes.class);
        cq.select(zip);
        cq.where(cb.and(cb.equal(zip.<String>get("state"), state), 
                cb.equal(zip.<String>get("county"), county)), 
                cb.equal(zip.<String>get("city"), city));

        return em.createQuery(cq).getResultList();
jmlv21104
  • 89
  • 12
javaNoob
  • 1

1 Answers1

0

You should try to create an Expression from the comparision strings; this way:

    String state= b[0].trim();
    String county= b[1].trim();
    String city = b[2].trim();

    Expression<String> state_e = cb.literal(state);
    Expression<String> county_e = cb.literal(county);
    Expression<String> city_e = cb.literal(city);

    Root<ZipCodes> zip = cq.from(ZipCodes.class);
    cq.select(zip);
    cq.where(cb.and(cb.equal(zip.<String>get("state"), state_e), 
            cb.equal(zip.<String>get("county"), county_e)), 
            cb.equal(zip.<String>get("city"), city_e));

Notice the "new" Expressions with underscore e(_e) as in Expressions. Hope it Helps!

jmlv21104
  • 89
  • 12