Update old SQL WHERE clause builder to Spring Data solution?
1
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.
java spring-boot spring-data-jpa spring-data querydsl
asked Nov 14 '18 at 19:59
deviciferdevicifer
64
add a comment |
1
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.
java spring-boot spring-data-jpa spring-data querydsl
asked Nov 14 '18 at 19:59
deviciferdevicifer
64
Does your solution have to be Spring Data based only or is a third party library, such as jooq.org an option? I'm asking because 1) You seem to be using some dynamic SQL, which is one of jOOQ's strengths and 2) Both Spring Boot Spring Data JDBC encourage third party library usage, see e.g. docs.spring.io/spring-boot/docs/2.1.0.RELEASE/reference/html/…
– Lukas Eder
Nov 22 '18 at 15:16
add a comment |
1
1
1
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.
java spring-boot spring-data-jpa spring-data querydsl
asked Nov 14 '18 at 19:59
deviciferdevicifer
64
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.
java spring-boot spring-data-jpa spring-data querydsl
java spring-boot spring-data-jpa spring-data querydsl
asked Nov 14 '18 at 19:59
deviciferdevicifer
64
asked Nov 14 '18 at 19:59
deviciferdevicifer
64
asked Nov 14 '18 at 19:59
deviciferdevicifer
64
asked Nov 14 '18 at 19:59
deviciferdevicifer
64
asked Nov 14 '18 at 19:59
deviciferdevicifer
64
64
Does your solution have to be Spring Data based only or is a third party library, such as jooq.org an option? I'm asking because 1) You seem to be using some dynamic SQL, which is one of jOOQ's strengths and 2) Both Spring Boot Spring Data JDBC encourage third party library usage, see e.g. docs.spring.io/spring-boot/docs/2.1.0.RELEASE/reference/html/…
– Lukas Eder
Nov 22 '18 at 15:16
add a comment |
Does your solution have to be Spring Data based only or is a third party library, such as jooq.org an option? I'm asking because 1) You seem to be using some dynamic SQL, which is one of jOOQ's strengths and 2) Both Spring Boot Spring Data JDBC encourage third party library usage, see e.g. docs.spring.io/spring-boot/docs/2.1.0.RELEASE/reference/html/…
– Lukas Eder
Nov 22 '18 at 15:16
Does your solution have to be Spring Data based only or is a third party library, such as jooq.org an option? I'm asking because 1) You seem to be using some dynamic SQL, which is one of jOOQ's strengths and 2) Both Spring Boot Spring Data JDBC encourage third party library usage, see e.g. docs.spring.io/spring-boot/docs/2.1.0.RELEASE/reference/html/…
– Lukas Eder
Nov 22 '18 at 15:16
Does your solution have to be Spring Data based only or is a third party library, such as jooq.org an option? I'm asking because 1) You seem to be using some dynamic SQL, which is one of jOOQ's strengths and 2) Both Spring Boot Spring Data JDBC encourage third party library usage, see e.g. docs.spring.io/spring-boot/docs/2.1.0.RELEASE/reference/html/…
– Lukas Eder
Nov 22 '18 at 15:16
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53307913%2fupdate-old-sql-where-clause-builder-to-spring-data-solution%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53307913%2fupdate-old-sql-where-clause-builder-to-spring-data-solution%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Does your solution have to be Spring Data based only or is a third party library, such as jooq.org an option? I'm asking because 1) You seem to be using some dynamic SQL, which is one of jOOQ's strengths and 2) Both Spring Boot Spring Data JDBC encourage third party library usage, see e.g. docs.spring.io/spring-boot/docs/2.1.0.RELEASE/reference/html/…
– Lukas Eder
Nov 22 '18 at 15:16