SQL query with variable structure in Spring

Question

I want to query the DB based on user input. For instance, if this input is "long red wire", the query must be:

SELECT * FROM foo WHERE tag ILIKE 'long' OR tag ILIKE 'red' OR tag ILIKE 'wire';
                        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

As you can see, the structure of the query changes with the input, it is not just the data.

Does Spring offer any functionality to cope with queries with variable structures? Or should I construct the string by myself?

This is somewhat what I have in mind.

SELECT * FROM foo WHERE {{tag = '?'} OR};

Lucas Oliveira · Accepted Answer · 2013-11-18 19:13:24Z

2

seems to me that you are interested in select statement "in", with spring you can do like this ...

   NamedParameterJdbcTemplate namedJdbcTemplate = new NamedParameterJdbcTemplate( jdbcTemplate.getDataSource() );

    Set<String> maps = new HashSet<String>();
    maps.add("long")
    maps.add("red")
    maps.add("wire")
    MapSqlParameterSource params = new MapSqlParameterSource();
    params.addValue("tags", maps  );                
    namedJdbcTemplate.query( "SELECT ... tags  in (:tags)" , params , new RowMapper() {

        @Override
        public Object mapRow(ResultSet rs, int arg1) throws SQLException {
             ....
        }
    });

answered Nov 18, 2013 at 19:13

Lucas Oliveira

8931 gold badge10 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

eversor Over a year ago

I had omitted it since I though it was not relevant for the question. Comparsion is made by "ILIKE" instead of "=" (I have already edited it). However, I could make a workaround that will let me use "=", therefore allowing me to use your answear (+1). I will wait a couple of days to see if someone knows a method to achieve exactly what I want, if not, I will mark your answear as valid.

alexkasko · Accepted Answer · 2013-11-18 19:28:53Z

1

First answer is correct one for this particular case where it can be represented as in expression.

If you want to build your query string in runtime - spring-jdbc doesn't support this out of the box. There are a lot of third-party libraries to do it, I also wrote one - query-string-builder, you may find links to others in the project readme.

answered Nov 18, 2013 at 19:28

alexkasko

4,9451 gold badge29 silver badges31 bronze badges

Collectives™ on Stack Overflow

SQL query with variable structure in Spring

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related