0

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)

1 Answer 1

2

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.

Sign up to request clarification or add additional context in comments.

2 Comments

i included the imports in the question
ok my error i changed it to java.sql.PreparedStatement ps = connection.prepareStatement(sql + whereClause); and it works. Thanks alot for your help

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.