How to make a dynamic query for MySQL

Question

I used Spring MVC, how can I made a dynamic query for Mysql? I have in my controller something like that: public String getNewsByDateAndAuthor(Model model, HttpServletRequest request) {}

How can I used all my parameters from URL with request.getParameterMap(), and my map are <String, List<String>> to create a dynamic query with that?

For example, if I have 3 parameters I want to generate one query like that:

SELECT * FROM news WHERE parameter = IN(val1, val2...) AND parameter2 = IN(val1, val2...) AND parameter3 = IN(val1, val2...)

or something like that is I have just one parameter;

SELECT * FROM news WHERE parameter = IN(val1, val2...)

If anyone know please show me how I can do that.

I want some code like that:

public String getQuery(Map<String,List<String>> parameters){
        List<String> author = new ArrayList<>();
        List<String> startDate = new ArrayList<>();
        List<String> endDate = new ArrayList<>();
        List<String> categories = new ArrayList<>();
        List<String> about = new ArrayList<>();
        List<String> soureID = new ArrayList<>();

        if (!parameters.get("author").isEmpty())
            for (int i = 0; i < parameters.get("author").size(); i++) {
                author.add(parameters.get("author").get(i));
            }

        if (!parameters.get("startDate").isEmpty())
            for (int i = 0; i < parameters.get("startDate").size(); i++) {
                startDate.add(parameters.get("startDate").get(i));
            }

        if (!parameters.get("endDate").isEmpty())
            for (int i = 0; i < parameters.get("endDate").size(); i++) {
                endDate.add(parameters.get("endDate").get(i));
            }

        if (!parameters.get("categories").isEmpty())
            for (int i = 0; i < parameters.get("categories").size(); i++) {
                categories.add(parameters.get("categories").get(i));
            }

        if (!parameters.get("about").isEmpty())
            for (int i = 0; i < parameters.get("about").size(); i++) {
                about.add(parameters.get("about").get(i));
            }

        if (!parameters.get("soureID").isEmpty())
            for (int i = 0; i < parameters.get("soureID").size(); i++) {
                soureID.add(parameters.get("soureID").get(i));
            }

        StringBuilder queryBuilder = new StringBuilder();
        queryBuilder.append("SELECT * FROM news WHERE");

        if (!author.isEmpty()) {
            String authors = author.toString();
            authors.replace(" ", " , ");
            queryBuilder.append(" author = IN ( " + authors + " ) AND");
        }
        if (!startDate.isEmpty()) {
            String startDates = startDate.toString();
            startDates.replace(" ", " , ");
            queryBuilder.append(" author = IN ( " + startDates + " ) AND");
        }
        if (!endDate.isEmpty()) {
            String endDates = endDate.toString();
            endDates.replace(" ", " , ");
            queryBuilder.append(" author = IN ( " + endDates + " ) AND");
        }
        if (!categories.isEmpty()) {
            String categoriesR = categories.toString();
            categoriesR.replace(" ", " , ");
            queryBuilder.append(" author = IN ( " + categoriesR + " ) AND");
        }
        if (!about.isEmpty()) {
            String abouts = about.toString();
            abouts.replace(" ", " , ");
            queryBuilder.append(" author = IN ( " + abouts + " ) AND");
        }
        if (!soureID.isEmpty()) {
            String sourceIDs = soureID.toString();
            sourceIDs.replace(" ", " , ");
            queryBuilder.append(" author = IN ( " + sourceIDs + " ) AND");
        }

        queryBuilder.delete(queryBuilder.length() - 3, queryBuilder.length());

        String query = queryBuilder.toString();

        return query;
}

No you don't want a method like that, you don't want to use String concat to create a dynamic query. Use the `Criteria` API instead. — M. Deinum, Jul 14 '15 at 12:10

score 0 · Accepted Answer · answered Jul 14 '15 at 12:21

You don't want a method like that, you don't want to use String concats to create a dynamic query. You want to use the Criteria API for that.

Something like the following

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<News> cq = cb.createQuery(News.class);
Root<News> news = cq.from(News.class);

if (!parameters.get("author").isEmpty()) {
    cq.where(cb.in(news.get("author"), parameters.get("author"));
}

TypedQuery<News> query = em.createQuery(cq);
return query.getResultList();

As you have multiple results you probably first want to create a list of Predicates and then apply them using cq.where(cb.and(predicates.toArray(new Predicate[predicates.size()]);.

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<News> cq = cb.createQuery(News.class);
Root<News> news = cq.from(News.class);
List<Predicate> predicates = new ArrayList<Predicate>();

if (!parameters.get("author").isEmpty()) {
    predicates.add(cb.in(news.get("author"), parameters.get("author")));
}

// Other params here.

cq.where(cb.and(predicates.toArray(new Predicate[predicates.size()]));
TypedQuery<News> query = em.createQuery(cq);
return query.getResultList();

Ehrm no as they all reside in the `java.persistence` package or one of its sub packages. Just include the JPA dependencies and let your IDE figure it out. — M. Deinum, Jul 14 '15 at 13:34
That is the `EntityManager` I assumed that you where using JPA (if not this approach isn't going to work). If you are using plain SQL instead JPA (HQL) this approach won't work. But that wasn't clear from your question. — M. Deinum, Jul 14 '15 at 13:35
and how I can define this? EntityManager em; CriteriaBuilder cb = em.getCriteriaBuilder();? — Dumitru Vlad, Jul 14 '15 at 13:38

How to make a dynamic query for MySQL

1 Answers1