0

I have got three fields 

 String stateID = "";
 String  districtID = "";
 String    talukaID = "";

These three fields can be empty Or can have value

The description of the table is

desc tbl_dealer

contactName
phone1
stateID
districtID
talukMandalID

Based on the values recivied , i have to write an SQL Query dynamically

Based on the values recivied , i have to write an SQL Query dynamically

For example

if all three are empty 

select contactName , phone1 from tbl_dealer

If stateID is empty then (removing state from the query)

select contactName , phone1 from tbl_dealer where districtID = "'+districtID+'" AND talukaID = "'+talukaID+'"

And similarly for all the cases

Could you please let me know how can to wrie this efficiently ,

Improve this question

2 Answers 2

Reset to default
2

I would suggest, first create a method :

private void appendFilter(StringBuilder sb, String fieldName, String fieldValue) {
    if(fieldValue != null && !fieldValue.trim().equalsIgnoreCase("")) {
        sb.append("AND "+fieldName+"='"+fieldValue+"'");
    }
}

Then use this method:

StringBuilder sb = new StringBuilder();
sb.append("select contactName , phone1 from tbl_dealer where 1=1 ");
appendFilter(sb, "districtID", districtID);
appendFilter(sb, "stateID", stateID);
appendFilter(sb, "talukaID", talukaID);
final String query=sb.toString();
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

I'd suggest to use StringBuilder instead of StringBuffer here. StringBuffer is synchronized and thus less performant.
Oh! yeah, please use StringBuilder.
Whereas you can use this method for building the query you should absolutely not push in the fields or you're opening yourself to an SQL Injection attack. You should make use of placeholders and a PreparedStatement
True, we can try similar approach to build prepared statement. Looking at the desired output, I did it
In addition and also, as by @MrWiggles mentioned, to avoid SQL injection, you could have a look on jOOQ, hibernate or any other ORM. Handling jdbc stuff yourself is a bit tedious, especially connection close etc.
1

You should use a PreparedStatement with bind variables to eliminate the chance of SQL injection (and to enable the DB to cache the query):

PreparedStatement ps = null;
try {
    List<String> bindVariables = new ArrayList<>();
    StringBuilder query = new StringBuilder(
        "select contactName, phone1 from tbl_dealer WHERE 1=1");

    if (stateID.length > 0) {
        query.append(" AND stateID = ?");
        bindVariables.add(stateID);
    }
    if (districtID.length > 0) {
        query.append(" AND districtID = ?");
        bindVariables.add(districtID);
    }
    if (talukaID.length > 0) {
        query.append(" AND talukaID = ?");
        bindVariables.add(talukaID);
    }
    ps = myConnection.prepareStatement(query.toString());

    for (int i = 0; i < bindVariables.size(); i++) {
        // variables are indexed from 1 in JDBC
        ps.setString(i+1, bindVariables.get(i));
    }
    ResultSet rs = ps.executeQuery();

    // iterate over the result set here

    rs.close();
} finally {
    if (ps != null) {
        ps.close();
    }
}

There's some duplication in the code that could be removed by introducing a helper method, if you were to use more columns in the WHERE condition.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.