Convert from using 3 identical SQL params to 1

I'm using the Java spring framework for security. My pre-existing table layout differs from spring's expected, but I'm allowed to specify a custom authorities-by-username query on the jdbc-user-service. The problem is that this query expects only a single parameter (?) in the SQL statement. Basically, if the user exists in the users table at all, they should get 'ROLE_USER'. If they开发者_JAVA百科 exist in the auth table as 'S', they should get 'ROLE_USER', 'ROLE_LICENSEE' and 'ROLE_SYSADMIN'. If they exist in the auth table as 'L', they should get both 'ROLE_LICENSEE', and 'ROLE_USER'.

SELECT U.email AS 'username', 'ROLE_USER' AS 'authority' FROM users U WHERE U.email=**?**
UNION
SELECT U.email AS 'username', 'ROLE_LICENSEE' AS 'authority' 
  FROM users U, auth_sys A 
 WHERE U.user_id=A.user_id AND A.auth_type IN ('L', 'S') AND U.email=**?**
UNION
SELECT U.email AS 'username', 'ROLE_ADMIN' AS 'authority' 
  FROM users U, auth_sys A 
 WHERE U.user_id=A.user_id AND A.auth_type='S' AND U.email=**?**;

My question is how can I reduce this from 3 (?)'s down to 1?

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.

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

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