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

Solution 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;
}

Solution 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);
....

Comments

[user2083529]

tied it before, gives me error ie can not resolve method "query(java.lang.string)

[Victor Gubin]

Java 9 and Spring 4 ?

[user2083529]

java 8 spring boot 2

[Victor Gubin]

Then simply check your app config I think Spring aspect missing or something similar.

[Victor Gubin]

BTW, if you simply need a UUID you can use Java UUID class to generate.

[user2083529]

tried it already , got org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [SELECT UUID()]; nested exception is java.sql.SQLSyntaxErrorException: unexpected end of statement

[kakabali]

I tried with same code that I have shared above, it works perfect for me

[kakabali]

might be some issue with your code, it works for me "select UUID()" also "SELECT UUID()"

[user2083529]

i have updated the code, are u using mysql or hsqldb? i think select uuid() works in both cases

[kakabali]

I am using h2 db

[kakabali]

which mysql version?

[user2083529]

i m using hsql and also mysql the lastest one

[kakabali]

we are missing out something very simple I guess on your side, because I have tried myself. And May be you can try by cleaning the build folder and trying again

[user2083529]

maybe the dialect ?

[kakabali]

it has a different dialect

[kakabali]

Refer here in the answer. stackoverflow.com/questions/44460662/…

[user2083529]

still getting the same problem :( , i have to look deeper

[kakabali]

:-( shouldn't be like this. but could be some minor stuff only

[user2083529]

well the problem was with hsqldb. somehow SELECT UUID() is not a valid function in hsqldb but it works fine with MYSQL. I will accept your answer.

[kakabali]

thats great news