Convert SQL LIKE clause to JAVA PrepareStatement

I have a problem to convert existing code to PrepareStatement as code below.
In this case I'm using LIKE clause. My database is Oracle 19c.

public int getCountReprintHistory(String request_date, String statement_type, String option_type, String cust_no) {
    	String sql = "";

   	if ( request_date.equals("") && statement_type.equals("") && option_type.equals("") && cust_no.equals("") ) {
    		sql += "SELECT COUNT(REPRINTLOG_DATE) FROM TBL_REPRINT_HISTORY2 ";
    		sql += "ORDER BY REPRINTLOG_JOBID DESC ";
    	} else {    	 	        
	    	sql += "SELECT COUNT(REPRINTLOG_DATE) FROM TBL_REPRINT_HISTORY2 ";
			sql += "WHERE TO_CHAR(REPRINTLOG_DATE,'dd/mm/yyyy') LIKE '%" + request_date +"%' ";
			sql += "AND REPRINTLOG_STATEMENT_TYPE LIKE '%" + statement_type +"%' ";
			sql += "AND REPRINTLOG_OPTION_TYPE LIKE '%" + option_type +"%' ";
			sql += "AND REPRINTLOG_CUSTOMER_NO LIKE '%" + cust_no +"%' ";
			sql += "ORDER BY REPRINTLOG_JOBID DESC ";
    	}    	
    	return jdbcTemplate.queryForObject(sql, Integer.class);
    }

I tried this query after that it was logout and returns to the main page.

		public int getCountReprintHistory(String request_date, String statement_type, String option_type, String cust_no) {
    	String sql = "";
    	
    	if ( request_date.equals("") && statement_type.equals("") && option_type.equals("") && cust_no.equals("") ) {
    		sql += "SELECT COUNT(REPRINTLOG_DATE) FROM TBL_REPRINT_HISTORY2 ";
    		sql += "ORDER BY REPRINTLOG_JOBID DESC ";
    	} else {    	 	        
	    	sql += "SELECT COUNT(REPRINTLOG_DATE) FROM TBL_REPRINT_HISTORY2 ";
			sql += "WHERE TO_CHAR(REPRINTLOG_DATE,'dd/mm/yyyy') LIKE '%' || ? || '%' ";
			sql += "AND REPRINTLOG_STATEMENT_TYPE LIKE '%' || ? || '%' ";
			sql += "AND REPRINTLOG_OPTION_TYPE LIKE '%' || ? || '%' ";
			sql += "AND REPRINTLOG_CUSTOMER_NO LIKE '%' || ? || '%' ";
			sql += "ORDER BY REPRINTLOG_JOBID DESC ";
    	}    	
    	return jdbcTemplate.queryForObject(sql, new Object[]{request_date,statement_type,option_type,cust_no}, Integer.class);
    }

Try something like this:

public int getCountReprintHistory(String request_date, String statement_type, String option_type, String cust_no) throws SQLException {
try (PreparedStatement stmt = cnt.prepareStatement(
"SELECT COUNT(REPRINTLOG_DATE) FROM TBL_REPRINT_HISTORY2 "
+ "WHERE TO_CHAR(REPRINTLOG_DATE,'dd/mm/yyyy') LIKE '%' || ? || '%' "
+ "AND REPRINTLOG_STATEMENT_TYPE LIKE '%' || ? || '%' "
+ "AND REPRINTLOG_OPTION_TYPE LIKE '%' || ? || '%' "
+ "AND REPRINTLOG_CUSTOMER_NO LIKE '%' || ? || '%' "
+ "ORDER BY REPRINTLOG_JOBID DESC ")) {
stmt.setString(1, request_date);
stmt.setString(2, statement_type);
stmt.setString(3, option_type);
stmt.setString(4, cust_no);
try (ResultSet rs = stmt.executeQuery()) {
rs.next();
return rs.getInt(1);
}
}
}

If you want to optimise the unconstrained case:

public int getCountReprintHistory(String request_date, String statement_type, String option_type, String cust_no) throws SQLException {
PreparedStatement stmt;
boolean unconstrained = request_date.equals("") && statement_type.equals("") && option_type.equals("") && cust_no.equals("");
if ( unconstrained ) {
stmt = cnt.prepareStatement(
"SELECT COUNT(REPRINTLOG_DATE) FROM TBL_REPRINT_HISTORY2 "
+ "ORDER BY REPRINTLOG_JOBID DESC ");
} else {                    
stmt = cnt.prepareStatement(
"SELECT COUNT(REPRINTLOG_DATE) FROM TBL_REPRINT_HISTORY2 "
+ "WHERE TO_CHAR(REPRINTLOG_DATE,'dd/mm/yyyy') LIKE '%' || ? || '%' "
+ "AND REPRINTLOG_STATEMENT_TYPE LIKE '%' || ? || '%' "
+ "AND REPRINTLOG_OPTION_TYPE LIKE '%' || ? || '%' "
+ "AND REPRINTLOG_CUSTOMER_NO LIKE '%' || ? || '%' "
+ "ORDER BY REPRINTLOG_JOBID DESC ");
}
try {
if (!unconstrained) {
stmt.setString(1, request_date);
stmt.setString(2, statement_type);
stmt.setString(3, option_type);
stmt.setString(4, cust_no);
}
try (ResultSet rs = stmt.executeQuery()) {
rs.next();
return rs.getInt(1);
}
} finally {
stmt.close();
}
}

To optimise a little more:

public int getCountReprintHistory(String request_date, String statement_type, String option_type, String cust_no) throws SQLException {
List<String> conds = new ArrayList<>();
List<String> parms = new ArrayList<>();
if (!request_date.equals("")) {
conds.add("TO_CHAR(REPRINTLOG_DATE,'dd/mm/yyyy') LIKE '%' || ? || '%'");
parms.add(request_date);
}
if (!request_date.equals("")) {
conds.add("REPRINTLOG_STATEMENT_TYPE LIKE '%' || ? || '%'");
parms.add(statement_type);
}
if (!request_date.equals("")) {
conds.add("REPRINTLOG_OPTION_TYPE LIKE '%' || ? || '%'");
parms.add(option_type);
}
if (!request_date.equals("")) {
conds.add("REPRINTLOG_CUSTOMER_NO LIKE '%' || ? || '%'");
parms.add(cust_no);
}
StringBuilder buf = new StringBuilder(
"SELECT COUNT(REPRINTLOG_DATE) FROM TBL_REPRINT_HISTORY2 ");
if (!conds.isEmpty()) {
buf.append("WHERE ");
buf.append(conds.get(0));
for (int i = 1; i < conds.size(); ++i) {
buf.append(" AND ");
buf.append(conds.get(i));
}
}
try (PreparedStatement stmt = cnt.prepareStatement(buf.toString())) {
for (int i = 0; i < parms.size(); ++i) {
stmt.setString(i+1, parms.get(i));
}
try (ResultSet rs = stmt.executeQuery()) {
rs.next();
return rs.getInt(1);
}
}
}

This is working for me.

public int getCountReprintHistory(String request_date, String statement_type, String option_type, String cust_no) {
String sql = "";
Integer count=0;
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try{
conn = jdbcTemplate.getDataSource().getConnection();
if ( request_date.equals("") && statement_type.equals("") && option_type.equals("") && cust_no.equals("") ) {
sql += "SELECT COUNT(REPRINTLOG_DATE) FROM TBL_REPRINT_HISTORY2 ";
sql += "ORDER BY REPRINTLOG_JOBID DESC ";
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
while(rs.next()){
count = rs.getInt(1);	 					
}
} else {    	 	        
sql += "SELECT COUNT(REPRINTLOG_DATE) FROM TBL_REPRINT_HISTORY2 ";
sql += "WHERE TO_CHAR(REPRINTLOG_DATE,'dd/mm/yyyy') LIKE ? ";
sql += "AND REPRINTLOG_STATEMENT_TYPE LIKE ? ";
sql += "AND REPRINTLOG_OPTION_TYPE LIKE ? ";
sql += "AND REPRINTLOG_CUSTOMER_NO LIKE ? ";
sql += "ORDER BY REPRINTLOG_JOBID DESC ";
pstmt = conn.prepareStatement(sql);
pstmt.setString(1,"%"+request_date+ "%");
pstmt.setString(2,"%"+ statement_type+ "%");
pstmt.setString(3,"%"+ option_type+ "%");
pstmt.setString(4,"%"+ cust_no+ "%");
rs = pstmt.executeQuery();
while(rs.next()){
count = rs.getInt(1);	 					
}
}  
}catch(Exception e){
System.out.println("[reprintHistory count] Exception :"+e.getMessage());
e.printStackTrace();  
} finally {
if (rs != null) {
try {
rs.close();
rs = null;
} catch (SQLException e) {
e.printStackTrace();;
}
}        
if (pstmt != null) {
try {
pstmt.close();
pstmt = null;
} catch (SQLException e) {
e.printStackTrace();
}
}
try {
if (conn!= null && !conn.isClosed()){
conn.close();
conn= null;
}
} catch (SQLException e) {
e.printStackTrace();
}
}
return count;
}