org.apache.commons.dbcp.DelegatingPreparedStatement cannot be cast to com.mysql.jdbc.PreparedStatement

Question

I am trying to query the database using prepared statement however i am getting an error: org.apache.commons.dbcp.DelegatingPreparedStatement cannot be cast to com.mysql.jdbc.PreparedStatement.

Imports

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;


import com.mysql.jdbc.PreparedStatement;
import com.mysql.jdbc.Statement;

JDBC

public List<Crime> getCrimeList(Crime crime) {
        String where_clause = "";

        final List<Object> args = new ArrayList<Object>();
        final StringBuilder whereClause = new StringBuilder();


        if (crime.getCrimeDate()!= null){
             whereClause.append(" and crime.crimeDate = ?");
             args.add(crime.getCrimeDate());

        }

        if(crime.getCrimeDetails() != null){
            whereClause.append(" and crime.crimeDetails = ?");
             args.add(crime.getCrimeDetails());

        }

        ..............

        logger.debug("In getCrimeList()");
        final String sql = "select crime.*, "+
                     "criminalSocialSecurityNumber,criminal.fName as criminalFName,criminal.lName as criminalLName,"+
                     "criminal.photo as criminalPhoto,criminal.dob as criminalDob,victimSocialSecurityNumber,"+
                     "victim.fName as victimFName,victim.lName as victimLName,victim.photo as victimPhoto, victim.dob as victimDob "+ 
                     "from tblcrimes crime "+
                     "left join tblcriminalcrime on crime.crimeRecNo = tblcriminalcrime.crimeRecNo "+
                     "left join tblvictimcrime on crime.crimeRecNo = tblvictimcrime.crimeRecNo "+
                     "inner join tblcitizens criminal on criminal.socialSecurityNumber = tblcriminalcrime.criminalSocialSecurityNumber "+
                     "inner join tblcitizens victim on victim.socialSecurityNumber = tblvictimcrime.victimSocialSecurityNumber ";

        logger.debug("Executing getCrimeList String "+sql);



        List<Crime> crimeList = getJdbcTemplate().query(new PreparedStatementCreator(){

        public java.sql.PreparedStatement createPreparedStatement(
                java.sql.Connection connection) throws SQLException {

            PreparedStatement ps =(PreparedStatement) connection.prepareStatement(sql + whereClause);
            int i = 1;
            for (Object arg : args) {
                ps.setObject(i, arg);
                i++;
            }

            return ps;
        }
        },  new CrimeMapper());      



        return crimeList;   

    }

Error

HTTP Status 500 - Request processing failed; nested exception is java.lang.ClassCastException: org.apache.commons.dbcp.DelegatingPreparedStatement cannot be cast to com.mysql.jdbc.PreparedStatement

org.springframework.web.util.NestedServletException: Request processing failed; nested exception is java.lang.ClassCastException: org.apache.commons.dbcp.DelegatingPreparedStatement cannot be cast to com.mysql.jdbc.PreparedStatement

org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:894)
    org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:789)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:647)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:728)

root cause

java.lang.ClassCastException: org.apache.commons.dbcp.DelegatingPreparedStatement cannot be cast to com.mysql.jdbc.PreparedStatement
    com.crimetrack.jdbc.JdbcCrimeDAO$2.createPreparedStatement(JdbcCrimeDAO.java:372)
    org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:581)
    org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:637)
    org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:662)
    com.crimetrack.jdbc.JdbcCrimeDAO.getCrimeList(JdbcCrimeDAO.java:367)
    com.crimetrack.service.CrimeManager.getCrimesList(CrimeManager.java:24)
    com.crimetrack.web.CrimeRegistrationController.handelCrimeList(CrimeRegistrationController.java:229)
    sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    java.lang.reflect.Method.invoke(Unknown Source)
    org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:213)
    org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:126)
    org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:96)
    org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:617)
    org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:578)
    org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:80)
    org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:923)
    org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:852)
    org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:882)
    org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:789)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:647)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:728)

Answer 1

On line :

PreparedStatement ps =(PreparedStatement) connection.prepareStatement(sql + whereClause);

Are you sure PreparedStatement is java.sql.PreparedStatement ?

Not sure why you need to cast the object returned from connection.prepareStatement(). This method returns an object of type java.sql.PreparedStatement. Please check your import statement if you are correctly importing the classes.