CODEWITHSUNDEEP
springjdbctemplate
Luca Sepe
Luca Sepe

Reputation: 805

java Spring JDBCTemplate - where clause

In my JDBC training, I have a question on the use of the where clause.

Suppose that i have a table in my db that i want manage with a spring application using a jdbc template, let's assume "Logbase", with this column: host, user, clientip. Suppose now that i want allow query db based on a single column for all column, that is:

Select * from Logbase where host = x

and

Select * from Logbase where user = y

and

Select * from Logbase where clientip = z

I suppose I must write a separated java method for every of this query, something like this:

 public Logbase getLogbaseFromHost(String id) 
       {
          String SQL = "select * from Logbase where host = ?";
          Logbase logbase = (Logbase) jdbcTemplate.queryForObject(SQL,  new Object[]{id}, 
                  (rs, rowNum) -> new Logbase(rs.getString("host"), rs.getString("user"),
                  rs.getInt("clientip")));

          return logbase;
       }



public Logbase getLogbaseFromUser(String id) 
       {
          String SQL = "select * from Logbase where user = ?";
          Logbase logbase = (Logbase) jdbcTemplate.queryForObject(SQL,  new Object[]{id}, 
                  (rs, rowNum) -> new Logbase(rs.getString("host"), rs.getString("user"),
                  rs.getInt("clientip")));

          return logbase;
       }




public Logbase getLogbaseFromClientIP(String id) 
           {
              String SQL = "select * from Logbase where clientip = ?";
              Logbase logbase = (Logbase) jdbcTemplate.queryForObject(SQL,  new Object[]{id}, 
                      (rs, rowNum) -> new Logbase(rs.getString("host"), rs.getString("user"),
                      rs.getInt("clientip")));

              return logbase;
           }

Now, if i want allow query db based on 2 parameters, i suppose i must write a method for the 3 possible pair of parameters (one for clientip-user, another for clientip-host and the last for user-host).

Finally, if i want allow query db selecting all the parameters, i must write another method with the where clause in the query that ask for all variables.

If I did not say heresies and everything is correct, i have 7 method. But, i the number of parameters and combinations grow, this may be a problem. There is a way to get around it?

Note: for work reasons, i cant use Hibernate or other ORM framework. I MUST use jdbc.

Tnx to all for patience and response.

Upvotes: 2

Views: 5896

Answers (2)

xmx2048m
xmx2048m

Reputation: 21

So, you also can use org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate

** Select *
   from Logbase where
        (:host is null or host = :host)
        AND (:user is null or user = :user)
        AND (:clientip is null or clientip = :clientip)**

And java code:

        MapSqlParameterSource params = new MapSqlParameterSource();
        params.addValue("host", host);
        params.addValue("user", user);
        params.addValue("clientip", clientip);
        namedParameterJdbcTemplate.queryForObject(sqlQuer, params);

Upvotes: 2

StanislavL
StanislavL

Reputation: 57381

The solution could be based on SQL

Select * 
from Logbase 
where 
   (? is null or host = ?)
   AND (? is null or user = ?)
   AND (? is null or clientip = ?)

jdbcTemplate.queryForObject(SQL, new Object[]{host, host, user, user, clienttip, clienttip}

So e.g. if user is not specified (user is null - true) all the records are included

Upvotes: 3

Related Questions