Convert from using 3 identical SQL params to 1.

Answer 1

As far as I know is not possible. You have to use three query with union.

Answer 2

It sounds like you won't be able to do that; since it sounds like you want a separate row for each user if they're in multiple roles.

If you can get by with only one row returned per user; and can assume that any Admin is also a licensee (evident by your where clause), you should be able to use a case statement and a left join.

Something like:

SELECT u.Email,
CASE WHEN a.user_id is null then 'Role_User'
WHEN a.auth_type = 'S' then 'Role_Admin'
WHEN a.auth_type = 'L' then 'Role_Licensee' end as 'authority'
FROM users u LEFT JOIN auth_sys a on u.user_id = a.user_id
WHERE u.email = **?**

Granted; i'm not completely familiar with the table structure, so it might require a little tweaking; but that should be enough to get you started

Answer 3

Since you are already using Spring Framework, you can use the Spring Framework to make your JDBC calls as well. Spring Framework has a NamedParameterJdbcTemplate that allows you to name your JDBC parameters. So, you can create a named parameter called :emailAddress for example, and use the same parameter three times in the SQL, but pass it in only once into the template.