Pfthb

Consider the following example of an old SQL query in Java:

(I had to sanitize this, so it may not actually compile.)

Connection con = null;
PreparedStatement stmt = null;
ResultSet result = null;
String whereClause = null;
whereClause = getWhereClause(clientSearch);
List l = new ArrayList();
int nextParam = 1;
boolean isPreClient = false;
String sql = "SELECT c." + CL_CLIENT_USER_ID + ",u." + US_FIRST_NM +
 ",u." + US_LAST_NM + ",u." + US_SSN + ",c." + CL_APP_SITE_CD +
 ",c." + CL_SYS_USER_ID + ",c." + CL_APP_USER_TYP_CD +
 ",c." + CL_PREREG_CMPL_CD +
 ",c." + CL_SPC_CAT_CD +
 ",c." + CL_REENLISTED_CD +
 ",u." + US_EMAIL_ADDR_TX +
 ",c." + CL_SPECIAL_PROGRAM_CD +
 " FROM " + DBT_APP_USERS + " u," + DBT_APP_CLIENT + " c " +
 whereClause +
 " ORDER BY " + US_LAST_NM + "," + US_FIRST_NM;


try {
 con = DbHelper.getConnection();
 stmt = con.prepareStatement(sql);
 if (clientSearch.isActive()) 
 stmt.setInt(nextParam, ConstantsDB.ACTIVE);
 nextParam++;
 stmt.setInt(nextParam, ConstantsDB.MODIFIED);
 nextParam++;
 else 
 stmt.setInt(nextParam, ConstantsDB.INACTIVE);
 nextParam++;
 
...

result = stmt.executeQuery();
 while (result.next()) 
 isPreClient = getIsPreClient(result.getInt(1), result);
 l.add(new ClientListVO(result.getInt(1), forJava(result.getString(2)),
 forJava(result.getString(3)), forJava(result.getString(4)), result.getInt(5),
 result.getInt(6), result.getInt(7), result.getDate(8),
 result.getDate(9), forJava(result.getString(10)), result.getDate(11), null,
 result.getInt(12));
 
...

private String getWhereClause(ClientSearchVO clientSearch) 
 String whereClause;

 if (clientSearch.isActive()) 
 whereClause = " WHERE u." + US_SYS_USER_ID + "=c." + CL_SYS_USER_ID
 + " AND c." + CL_ELMT_STATUS_CD + " IN (?,?)";
 else 
 whereClause = " WHERE u." + US_SYS_USER_ID + "=c." + CL_SYS_USER_ID
 + " AND c." + CL_ELMT_STATUS_CD + "=?";
 

 if (clientSearch.getSsnFull() != null) 
 whereClause = whereClause + " AND u." + US_SSN + "=?";
 
 if (clientSearch.isSearchPrereg()) 
 whereClause = whereClause
 + " AND LENGTH(U." + US_SSN + ")=?";
 

 if (clientSearch.getSsnLastFour() != null) 
 whereClause = whereClause + " AND (substr(u." + US_SSN + ",6,4)=? OR u."
 + US_SSN + "=?)";
 
 if (clientSearch.getLastName() != null) 
 whereClause = whereClause + " AND UPPER(u." + US_LAST_NM + ") like ?";
 

 if (clientSearch.getClientId() > 0) 
 whereClause = whereClause + " AND c." + CL_CLIENT_USER_ID + "=?";
 
 return whereClause;

I need to convert this kind of code for a newer application. I'm using BOM version 1.5x of Spring Data JPA, Spring Boot, etc. I can't upgrade to Spring Boot 2, because I don't currently have access to the newer Servlet versions.

The above code is for a search screen that is searching and pulling data from database tables APP_CLIENT and APP_USERS. I'm not sure what to do with it, due to the WHERE clause being built as such.

I was thinking about doing something like this:

public class ClientSearchResult 
 protected AppClient client;
 protected AppUsers user;

 public ClientSearchResult(AppClient client, AppUsers user) 
 this.client = client;
 this.user = user;
 

 ...deligate methods


public interface AppClientRepo extends JpaRepository<AppClient, Long> 

 @Query("SELECT new ClientSearchResult(au, ac) FROM AppUsers au, AppClient ac "
 + "WHERE "
 + "ORDER BY au.lastNm, au.firstNm ")
 List<ClientSearchResult> findBySearchCriteria(@Param("someParam01") long someParam01, @Param("someParam02") String someParam02);

I'm not sure what to do about the WHERE clause, however. If someone knows a good way to convert such code into a more modern approach, I would love to hear about it. A simple method would be preferred, but not required.