[FIXED] Storing SQL queries in Java

Issue

I am having issues with how we are currently storing our SQL queries, we currently have a java class that stores the SQL as a public static final, some of these SQL queries require a global string object to be injected into the where clause such as a system name or a user name, however because we store the queries as a static final, when another test runs and the system name or user name has the value reassigned the SQL query still holds the value from the previous test, I need to find a more efficient and stable way to store the SQL queries. can anyone help?

Example of how we store our SQL queries:

public static final String GET_USER_ACCESS_VALUES =
"SELECT \n" +
                        "[Company].[dbo].[TUsers].[PINExpiryDate]\n" +
                        ",[Company].[dbo].[TUsers].[PINForceChange]\n" +
                        ",[Company].[dbo].[TUsers].[Active]\n" +
                        ",[Company].[dbo].[TUsers].[ContractExpiry]\n" +
                        ",[Company].[dbo].[TUserGroups].[Name]\n" +
                        ",[Company].[dbo].[TUsers].[ActiveDate]\n" +
                        ",[Company].[dbo].[TUsers].[ExpiryDate]\n" +
                        ",[Company].[dbo].[TUsers].[AllowanceAcrossSystemsOverride]\n" +
                        "FROM [Company].[dbo].[TUsers] \n" +
                        "INNER JOIN [Company].[dbo].[TUserGroups] \n" +
                        "ON [Company].[dbo].[TUsers].[UserGroupID] = [Company].[dbo].[TUserGroups].[UserGroupID]\n" +
                        "WHERE [Company].[dbo].[TUsers].[DisplayName] = '" + userNameValue +"'";

Solution

Don't concatenate strings like that. Use PreparedStatement with the setString method to set the user name.

PreparedStatement prep = connection.prepareStatement(GET_USER_ACCESS_VALUES);

prep.setString(1, userNameValue);

GET_USER_ACCESS_VALUES now just has a ? for the name:

public static final String GET_USER_ACCESS_VALUES = """
      SELECT
          [Company].[dbo].[TUsers].[PINExpiryDate]
          ,[Company].[dbo].[TUsers].[PINForceChange]
          ,[Company].[dbo].[TUsers].[Active]
          ,[Company].[dbo].[TUsers].[ContractExpiry]
          ,[Company].[dbo].[TUserGroups].[Name]
          ,[Company].[dbo].[TUsers].[ActiveDate]
          ,[Company].[dbo].[TUsers].[ExpiryDate]
          ,[Company].[dbo].[TUsers].[AllowanceAcrossSystemsOverride]
          FROM [Company].[dbo].[TUsers]
          INNER JOIN [Company].[dbo].[TUserGroups]
          ON [Company].[dbo].[TUsers].[UserGroupID] = [Company].[dbo].[TUserGroups].[UserGroupID]
          WHERE [Company].[dbo].[TUsers].[DisplayName] = ?
      """;

I have used a Java 15 text block here to simplify the string

Answered By - greg-449
Answer Checked By - Terry (JavaFixing Volunteer)