CODEWITHSUNDEEP
javaspring-boot
Richard Tingle
Richard Tingle

Reputation: 17226

How to create a completely custom query in spring boot

Request

Within spring boot I'd like to run a completely custom query, for example running a query to get the firstname and lastname columns on the table person. BUT where the existence of the table person and the fact that it may have columns firstname and lastname was not known at compile time.

Why I want to do this

I want to do this as within our application because we have a concept of custom fields and custom entities. These have views automatically built over the top of them. At run time I will know what views are available and what columns they have but I will not know that when the application starts (and they may change while the application is running

What I don't want

A query annotation on a crudRepository because that still needs to target a particular object and so can't have dynamic fields or an arbitrary object (unless someone knows how to make a crudRepository do that)

Upvotes: 1

Views: 397

Answers (1)

Emmanuel Price
Emmanuel Price

Reputation: 130

If you have knowledge of the entity and the fields you need at run time, you can use SpringJDBC Templates to construct SQL queries. Below is an example of fetching a Todo item. You can do something similar but passing in the entity name and a collection of fields you would want. Here is a very basic example: 

@Autowired 
private DataSource dataSource; // Configure this in a class annotated with @Configuration

public Todo fetchWithToDoId(long id) {
    Todo record = new JdbcTemplate(dataSource).queryForObject("SELECT * FROM PUBLIC.TODO WHERE todo_id = ?", new Object[]{id}, getRowMapper());
    return record;
}

private RowMapper<Todo> getRowMapper() {
    return (resultSet, i) -> {
        Todo d = new Todo();
        d.setUserId(resultSet.getInt("todo_user_id"));
        d.setId(resultSet.getInt("todo_id"));
        d.setTitle(resultSet.getString("todo_title"));
        d.setCompleted(resultSet.getBoolean("todo_completed"));
        d.setCreated(resultSet.getTimestamp("todo_created"));
        return d;
    };
}

If the tables and the columns do not exist then an exception will be thrown and it's up to you to handle them on the server side and present the appropriate view to the client. You would probably expand this to take in as argument an entity and a KeyValuePair data structure to map field to value. When you construct you query then it will present all the fields and their target values.

Upvotes: 1

Related Questions