Storing SQL queries in Java-CodePudding

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  "'";

CodePudding user response：

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

CodePudding user response：

You should avoid "global" variables as much as you can in your code. What @greg-449 mentioned is the best way to go. Use prepared statement.

If you have to use Strings for your SQL statements I would use something like this:

public static final String cmd = "SELECT * FROM Users WHERE NAME={0}"

And you would use it like this:

var sqlCommand = MessageData.format(cmd, currentUserName)

See https://docs.oracle.com/javase/7/docs/api/java/text/MessageFormat.html