Eclipselink problem with MySQL Stored Procedures

[Marc Marais]

Hi guys.

I am trying to set up Eclipselink to work with my MySQL database, in particular the stored procedures. Below is my code:

User.java

package za.co.strutstest.model;

import java.io.Serializable;
import javax.persistence.*;

import org.eclipse.persistence.annotations.NamedStoredProcedureQuery;
import org.eclipse.persistence.annotations.StoredProcedureParameter;


/**
 * The persistent class for the user database table.
 * 
 */

@NamedStoredProcedureQuery(
	    name="GetUserDetails",
	    procedureName="competition.userDetails",
	    parameters={
	        @StoredProcedureParameter(queryParameter = "UserName"),
	        @StoredProcedureParameter(queryParameter = "Password")
	    },
	    resultClass=User.class
	)
@Entity
public class User implements Serializable {
	private static final long serialVersionUID = 1L;

	@Id
	@GeneratedValue(strategy=GenerationType.IDENTITY)
	private String userId;

	private java.math.BigInteger companyId;

	private String userCelNumber;

	private String userFaxNumber;

	private String userFirstName;

	private String userIdNumber;

	private String userName;

	private String userPassword;

	private String userSurname;

	private String userTelNumber;

    public User() {
    }

	public String getUserId() {
		return this.userId;
	}

	public void setUserId(String userId) {
		this.userId = userId;
	}

	public java.math.BigInteger getCompanyId() {
		return this.companyId;
	}

	public void setCompanyId(java.math.BigInteger companyId) {
		this.companyId = companyId;
	}

	public String getUserCelNumber() {
		return this.userCelNumber;
	}

	public void setUserCelNumber(String userCelNumber) {
		this.userCelNumber = userCelNumber;
	}

	public String getUserFaxNumber() {
		return this.userFaxNumber;
	}

	public void setUserFaxNumber(String userFaxNumber) {
		this.userFaxNumber = userFaxNumber;
	}

	public String getUserFirstName() {
		return this.userFirstName;
	}

	public void setUserFirstName(String userFirstName) {
		this.userFirstName = userFirstName;
	}

	public String getUserIdNumber() {
		return this.userIdNumber;
	}

	public void setUserIdNumber(String userIdNumber) {
		this.userIdNumber = userIdNumber;
	}

	public String getUserName() {
		return this.userName;
	}

	public void setUserName(String userName) {
		this.userName = userName;
	}

	public String getUserPassword() {
		return this.userPassword;
	}

	public void setUserPassword(String userPassword) {
		this.userPassword = userPassword;
	}

	public String getUserSurname() {
		return this.userSurname;
	}

	public void setUserSurname(String userSurname) {
		this.userSurname = userSurname;
	}

	public String getUserTelNumber() {
		return this.userTelNumber;
	}

	public void setUserTelNumber(String userTelNumber) {
		this.userTelNumber = userTelNumber;
	}

}

LoginAction.java

package za.co.strutstest;

import java.util.List;

import javax.persistence.Query;

import za.co.strutstest.config.data.Configuration;
import za.co.strutstest.model.User;

import com.opensymphony.xwork2.ActionSupport;

public class LoginAction extends ActionSupport{
    private String username;
    private String password;
    private String firstName;
    private String surname;
 
    public String execute() {
    	Query query = Configuration.getJpaEntityManager().createNamedQuery("GetUserDetails");
    	query.setParameter("UserName", this.username);
    	query.setParameter("Password", this.password);   	
    	try{
    		List<User> result = query.getResultList();
        	firstName = result .get(0).getUserFirstName();
        	surname = result .get(0).getUserSurname();
            return "success";
        } catch(Exception e) {
        	addActionError("Incorrect username or password");
            return "error";
        }
    }
 
    public String getUsername() {
        return username;
    }
 
    public void setUsername(String username) {
        this.username = username;
    }
 
    public String getPassword() {
        return password;
    }
 
    public void setPassword(String password) {
        this.password = password;
    }

	public String getFirstName() {
		return firstName;
	}

	public void setFirstName(String firstName) {
		this.firstName = firstName;
	}

	public String getSurname() {
		return surname;
	}

	public void setSurname(String surname) {
		this.surname = surname;
	}
    
}

Stored Procedure
DELIMITER $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `userDetails`(UserName varchar(16), Password varchar(16)) BEGIN SELECT userId from `competition`.`user` WHERE UserName = UserName AND Password = Password; END

When I attempt to get the result list I get the following error:

[EL Warning]: 2011-12-28 08:35:22.601--UnitOfWork(20862036)--java.lang.ClassCastException: com.mysql.jdbc.JDBC4PreparedStatement cannot be cast to java.sql.CallableStatement

Does anyone know how I can cast the statement or if there is a problem with my code or a workaround?

[Vijitha Kumara]

Can you post the full stack trace?

[Marc Marais]

Thanks for your response. That is the only line of error I have in my console.

[Marc Marais]

Here is the full stacktrace:

Exception in thread "main" java.lang.ClassCastException: com.mysql.jdbc.JDBC4PreparedStatement cannot be cast to java.sql.CallableStatement
	at org.eclipse.persistence.internal.databaseaccess.DatabasePlatform.executeStoredProcedure(DatabasePlatform.java:2118)
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:602)
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:535)
	at org.eclipse.persistence.internal.sessions.AbstractSession.basicExecuteCall(AbstractSession.java:1717)
	at org.eclipse.persistence.sessions.server.ServerSession.executeCall(ServerSession.java:566)
	at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:207)
	at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:193)
	at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeSelectCall(DatasourceCallQueryMechanism.java:264)
	at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.selectAllRows(DatasourceCallQueryMechanism.java:646)
	at org.eclipse.persistence.queries.ReadAllQuery.executeObjectLevelReadQuery(ReadAllQuery.java:420)
	at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeDatabaseQuery(ObjectLevelReadQuery.java:1081)
	at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:844)
	at org.eclipse.persistence.queries.ObjectLevelReadQuery.execute(ObjectLevelReadQuery.java:1040)
	at org.eclipse.persistence.queries.ReadAllQuery.execute(ReadAllQuery.java:392)
	at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeInUnitOfWork(ObjectLevelReadQuery.java:1128)
	at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2871)
	at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1516)
	at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1498)
	at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1463)
	at org.eclipse.persistence.internal.jpa.EJBQueryImpl.executeReadQuery(EJBQueryImpl.java:485)
	at org.eclipse.persistence.internal.jpa.EJBQueryImpl.getSingleResult(EJBQueryImpl.java:773)
	at za.co.strutstest.LoginAction.main(LoginAction.java:28)

[Vijitha Kumara]

Try using executeUpdate() method instead getResultList() method.

[Marc Marais]

I thought executeUpdate() was used to run update and delete statements. It returns an integer and I need a result class.

[James Sutherland]

The issue is that your procedure returns a result set, but by default @NamedStoredProcedureQuery assumes output parameters are used or no result.

You need to set returnsResultSet=true in your @NamedStoredProcedureQuery.

But please log a bug, this exception should not occur, it should always be using a CallableStatement for @NamedStoredProcedureQuery, (but you should get an empty result set without returnsResultSet=true).