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