how to execute sql statements inside spring boot controller?

Question

I want to execute sql statement inside my spring boot controller class with out defining any method in the jpa repository. The statement i want to use is

SELECT UUID();

This statement is database related and is not associated with a particular entity.

It would be nice if any one can provide solution for the execution of the above statement via

1. spring controller class
2. jpa repository (if recommended)

update

controller:

@Autowired
JdbcTemplate jdbcTemplate;

@RequestMapping(value = "/UUID", method = RequestMethod.GET)
public ResponseEntity<String> getUUID() {
    String uuid = getUUID();
    return buildGuestResponse(uuid);
}

public String getUUID(){
    UUID uuid = (UUID)jdbcTemplate.queryForObject("select UUID()", UUID.class);
    return uuid.toString();
}

Answer 1

You can use JdbcTemplate in your code.

The bean you will need in your configuration class is:-

@Bean
public JdbcTemplate jdbcTemplate(DataSource dataSource)
{
    return new JdbcTemplate(dataSource);
}

And the code to run the query is:-

@Autowired
private JdbcTemplate JdbcTemplate;

public String getUUID(){
    UUID uuid = (UUID)jdbcTemplate.queryForObject("select UUID()", UUID.class);
    return uuid.toString();
}

or may be like this:-

public UUID getUUID(){
    UUID uuid = (UUID)jdbcTemplate.queryForObject("select UUID()", UUID.class);
    return uuid;
}

Answer 2

This is generally architecturally bad design to execute any SQL (do any persistence) on presentation layer (controller or view) in JEE applications.

The best option is make controller to use service layer, when service layer calling the persistence layer for: obtaining, saving or updating data.

In any case, you can use Spring Data JDBC. Something like:

import org.springframework.jdbc.core.JdbcTemplate;

....
 UUID uuid = (UUID)jdbcTemplate.query("SELECT UUID()", UUID.class);
....