Convert SQL LIKE clause to JAVA PrepareStatement

Question

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);
    }

e.g. https://stackoverflow.com/questions/8247970/using-like-wildcard-in-prepared-statement — Shakirov Ramil, Mar 09 '23 at 12:32

Maurice Perry · Answer 1 · 2023-03-09T13:01:55.007

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 solution made a lot of change on my program. So, I tried another way. — iChinJO, Mar 14 '23 at 07:55

score 0 · Accepted Answer · answered May 11 '23 at 11:53

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;
}

Convert SQL LIKE clause to JAVA PrepareStatement

2 Answers2