CODEWITHSUNDEEP
javaspringspring-bootspring-data
Harry
Harry

Reputation: 505

how to implement a spring boot controller to return the result of a query as json

I am trying to a write a spring boot controller which can return the result of a native query as json. I will be passing the query as input parameter and the return must be result of the query. Is there a way to do this? I know the http rpc help on this. The query can be anything and the system must accept it and must respond with the result as json.

For example if I pass the request as select * from employee it must respond with result of query as json.

Upvotes: 0

Views: 3453

Answers (3)

Jonathan
Jonathan

Reputation: 883

Simply make every function returning:

Map<String, Object>

It will automatically map the object property and value. That means a json object is an instance of Map. If you are managing an array of it, enclose it with a List:

List<Map<String, Object>>

and finally the ResponseEntity becomes:

ResponseEntity<List<Map<String, Object>>>

Upvotes: 1

Govind Sharma
Govind Sharma

Reputation: 137

SpringBoot

//Controller Class

@RestController
@RequestMapping("/employee")
public class EmployeeController {
    @Autowired
    private EmployeeService employeeService;

     @GetMapping("/all")
    public List<Employee> getAllEmplpyee() {
        logger.info("get All Employeee");
        return employeeService.getAllEmployeeService();
    }
}

//ServiceImpl

@Service
public class EmployeeService {
    private static final Logger logger = LoggerFactory.getLogger(EmployeeService.class);

    @Autowired
    private EmployeeRepository employeeRepository;


    public List<Employee> getAllEmployeeService() {
        logger.info(getClass().getName(),"  invked getAllEmployee");
        List<Employee>    empBo = employeeRepository.findAll();
        return copyPropertiesValues(empBo);

    }
}

//DAO 

@Component
public interface EmployeeRepository extends JpaRepository<Employee, String>{    
}

//Model

@Entity
@Table(name = "employees")
public class Employee {

    @Id
    @Column(name = "employeeNumber",nullable=false)
    private String employeeNumber;
    @Column(nullable=false)
    private String lastName;
    @Column(nullable=false)
    private String firstName;
    @Column(nullable=false)
    private String extension;
    @Column(nullable=false)
    private String email;
    @Column( nullable=false)
    private String officeCode;
    @Column(nullable=false)
    private String reportsTo;
    @Column(nullable=false)
    private String jobTitle;
  //GETTER SETTER
}

//application.properties

spring.jpa.hibernate.ddl-auto=update
spring.jpa.open-in-view=true
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5InnoDBDialect
logging.level.org.hibernate.SQL=debug
logging.level.org.hibernate.type.descriptor.sql=trace
spring.jpa.hibernate.naming.implicit-strategy=org.hibernate.boot.model.naming.ImplicitNamingStrategyLegacyJpaImpl
spring.jpa.hibernate.naming.physical-strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl
spring.datasource.url=jdbc:mysql://localhost:3306/test
spring.datasource.username=root
spring.datasource.password=****

Upvotes: 0

Avi
Avi

Reputation: 1528

You could actually use Spring JDBC for that,

Repo

@Repository
public class FooRepo {

  @Autowire
  private JdbcTemplate jdbcTemplate;

  public Object returnDataForQuery(String sql) {
    return jdbcTemplate.queryForObject(sql, Object.class); // You could define a proper class if you know the return Type else returning plain object is more then enough
    // return jdbcTemplate.queryForList(sql, Object.class) Incase Multiple Data
  }
}

Model

public class FooDto {

  private String query;

  // Getter, Setter & No Args Constructor (or) Lombok

}

Controller


@Autowire 
private FooRepo fooRepo;

@PostMapping(value = "/postData", produces = MediaType.APPLICATION_JSON_VALUE)
public ResponseEntity postData(@RequestBody FooDto foo) {
    return ResponseEntity.ok(fooRepo.returnDataForQuery(foo.getQuery);
}

This is just a overview, you could bend it.As for your result output concern you ResponseEntity will take care of it

Upvotes: 0

Related Questions