I have a function
public void executeMyQuery(Connection con) {
PreparedStatement ps = con.prepareStatement("SELECT * FROM STUDENT WHERE ID = ?");
ps.setInt(1, 7);
ps.executeQuery();
}
if i will run this it will work fine. But I want to do like this.
Or is there is any way to dynamically remove or modify the string after WHERE Clause.
Other answers have shown how to achieve dynamic SQL using string based JDBC usage. There are many disadvantages to building SQL strings dynamically using string concatenation, including:
Also, when you're using plain JDBC (which only supports indexed bind variables), rather than some utility such as Spring JdbcTemplate, MyBatis, jOOQ, etc. you will have to manually match ? placeholders with their corresponding indexes, which is another subtle source of errors.
At some point, when you implement dynamic SQL queries more often, query builders will definitely help. The most popular ones are:
There are many other options that are more or less well maintained. For very trivial cases (like the one in your question), you can also build your own simple predicate builder.
Disclaimer: I work for the company behind jOOQ.
You have to build your query dynamically, at the beginning of the method check whether id is null or equal 0. To make it easier you can use trick in where clause with 1=1 so where clause can be included all the time in the query.
public void executeMyQuery( Connection con, Integer id) {
String query = "SELECT *FROM STUDENT WHERE 1=1";
if(id != null){
query += "AND ID = ?";
}
PreparedStatement ps = con.prepareStatement(query);
if(id != null){
ps.setInt(1, id);
}
ps.executeQuery();}
ifNull/Coalesce work nicely for this, if you pass a null, it will select where the field equals itself.
SELECT *
FROM STUDENT
WHERE 1 = 1
and ID = ifNull(:ID, ID)
I'd also suggest something other than using ? for your variables, fine when you a couple but as you get a ton of them, difficult to keep track or modify. I've found https://github.com/dejlek/jlib/blob/master/src/main/java/com/areen/jlib/sql/NamedParameterStatement.java pretty easy, made a few modifications to fit my particular needs but SQL is much easier to read and doing substitutions in intellij db console are much easier when developing the SQL.
You can have two PreparedStatements defined in your program - one without the WHERE ID = ? clause, and another one with it.
Moreover, you are supposed to keep your PreparedStatements and re-use, so you better store them as a field, etc.
And then, when needing to get the data - call either the first prepared statement, or the second one.
String but one PreparedStatement, there is no need to create two if one will not be used ;)Michael Dz is close to the solution in his answer, but there is a problem in the code : he calls setInt on a non existing preparedStatement.
Try something like this :
public void executeMyQuery( Connection con, int Id) {
StringBuffer sql = new StringBuffer();
sql.append("Select * FROM STUDENT");
if(Id > -1) {
sql.append(" Where ID = ?");
}
preparedStatement ps = con.prepareStatement(sql.toString());
if(ID > -1) {
ps.setInt(1, Id);
}
ps.executeQuery(); // You might want to catch the result of the query as well
}
Hope this helps !
PreparedStatements with every call.
mysqlorsql-server? Pick (the right) one...setInt()or not. You should have 2 mehods, one which requires anintparameter and one that doesn't.