Dynamic Query in Spring Boot

These are the steps for creating a dynamic query in spring boot

Step 1 => Create a simple spring boot project with name "DynamicQuery".

2. Add maven dependencies for database connection and rest API creation.

                <dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jpa</artifactId>
		</dependency>

		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<scope>runtime</scope>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>
		<dependency>
			<groupId>org.hibernate</groupId>
			<artifactId>hibernate-validator</artifactId>
			<version>5.2.4.Final</version>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-jdbc</artifactId>
		</dependency>

3. Add database related configuration in the application.properties file.

#----------------------------Database Connection Configuration-------------------------#

spring.datasource.url=jdbc:mysql://localhost:3306/temp?autoReconnect=true&useSSL=false
spring.datasource.username=root
spring.datasource.password=root
spring.jpa.hibernate.ddl-auto=update
spring.jpa.show-sql=true
spring.datasource.name=temp
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.jpa.database-platform = org.hibernate.dialect.MySQL5Dialect
#--------------------------------------------------------------------------------------#
server.port=8080

4. Create a class with named "DynamicQueryConfig" for creating bean of HibernateJpaSessionFactoryBean.

package com.dynamicquery;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.orm.jpa.vendor.HibernateJpaSessionFactoryBean;


@Configuration
public class DynamicQueryConfig {

	public static Logger logger = LoggerFactory.getLogger(DynamicQueryConfig.class);


	/**
	 * Bean for getting the handle of SessionFactory for
	 * Manually Creating Hibernate Session if needed.
	 * @return
	 */
	@Bean
	public HibernateJpaSessionFactoryBean sessionFactory() {
		logger.debug("Creating hibernate Session factoy");

		HibernateJpaSessionFactoryBean hibernateJpaSessionFactoryBean = null;
		try{
			hibernateJpaSessionFactoryBean = new HibernateJpaSessionFactoryBean();
		}catch(Exception e){
			logger.debug("Exception occure while create HibernateJpaSessionFactoryBean : "+e);
		}
		logger.debug("HibernateJpaSessionFactoryBean created succesfully.");
		return hibernateJpaSessionFactoryBean;
	}

}

5. Create new entity with named "Employee".

package com.dynamicquery.domain;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;

@Entity
public class Employee {

	@Id
	@GeneratedValue(strategy=GenerationType.AUTO)
	private Integer employeeId;
	
	private String email;
	
	private String firstName;
	
	private String lastName;
	
	private Integer age;
	
	private Integer salary;
	
	private Boolean isActive;
	
	public Employee(){}

	public Integer getEmployeeId() {
		return employeeId;
	}

	public void setEmployeeId(Integer employeeId) {
		this.employeeId = employeeId;
	}

	public String getEmail() {
		return email;
	}

	public void setEmail(String email) {
		this.email = email;
	}

	public String getFirstName() {
		return firstName;
	}

	public void setFirstName(String firstName) {
		this.firstName = firstName;
	}

	public String getLastName() {
		return lastName;
	}

	public void setLastName(String lastName) {
		this.lastName = lastName;
	}

	public Integer getAge() {
		return age;
	}

	public void setAge(Integer age) {
		this.age = age;
	}

	public Integer getSalary() {
		return salary;
	}

	public void setSalary(Integer salary) {
		this.salary = salary;
	}

	public Boolean getIsActive() {
		return isActive;
	}

	public void setIsActive(Boolean isActive) {
		this.isActive = isActive;
	}
	
}

6. Create new Service class

package com.dynamicquery.service;

import java.lang.reflect.Field;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Query;

import org.apache.catalina.User;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.orm.jpa.vendor.HibernateJpaSessionFactoryBean;
import org.springframework.stereotype.Service;

import com.dynamicquery.domain.Employee;
import com.dynamicquery.domain.Req_GetEmployee;

@Service
public class EmployeeService {


	public static Logger log = LoggerFactory.getLogger(EmployeeService.class);


	@Autowired
	private HibernateJpaSessionFactoryBean sessionFactory;



	public String getValueByFieldType(Object value,Class fieldType){

		if(fieldType.equals(String.class)){
			return "\'"+value+"\'";
		}
		return value.toString();
		
	}

	public String createDynamicQuery(Object obj){
		
		Class classObj = obj.getClass();
		StringBuffer query=new StringBuffer("from "+classObj.getSimpleName());

		Field[] fields = classObj.getDeclaredFields();
		boolean isWhereExist=false;

		for(Field field : fields){
			field.setAccessible(true);
			try{
				Object value = field.get(obj);
				if(value != null){
					if(!isWhereExist){
						query.append(" where "+field.getName()+" = "+getValueByFieldType(value,field.getType()));
						isWhereExist = true;
					}else{
						query.append(" and "+field.getName()+" = "+getValueByFieldType(value,field.getType()));
					}
				}
			}catch (Exception e) {
				log.error("Exception ocuur while fetch field value : ",e);
			}
		}
		log.info("Query is => "+query);
		return query.toString();
	}

	public Map<String,Object> getEmployee(Employee employee){

		EntityManagerFactory emf = sessionFactory.getEntityManagerFactory();
		EntityManager en = emf.createEntityManager();
		
		Query query = en.createQuery(createDynamicQuery(employee));
		List<Employee>  employeeList = query.getResultList();
		Map<String,Object> response = new HashMap<String,Object>();
		response.put("employees",employeeList );
		return response;
		
	}

}

7. Create new Controller.

package com.dynamicquery.controller;

import java.util.HashMap;
import java.util.Map;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.MediaType;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.ModelAttribute;
import org.springframework.web.bind.annotation.RestController;

import com.dynamicquery.domain.Employee;
import com.dynamicquery.service.EmployeeService;

@RestController
public class EmployeeController {

	@Autowired
	private EmployeeService employeeService;
	
	public static Logger log = LoggerFactory.getLogger(EmployeeController.class);
	
	@GetMapping(value="/employee",produces=MediaType.APPLICATION_JSON_UTF8_VALUE)
	public Map<String,Object> getEmployee(@ModelAttribute Employee employee){
		
		log.info("Inside getEmployee API");
		Map<String,Object> response = new HashMap<String,Object>();
		response.put("hi","ddddddd");
		try{
		response = employeeService.getEmployee(employee);
		response.put("isError", false);
		response.put("message","Employees fetched successfully");
		}catch (Exception e) {
			log.error("Excption occur while fetch employee : ",e);
			response.put("isError", true);
			response.put("message","Employees not fetched");
		}
		return response;
	}
	
}