After pouring through pages and forums and what not, I just could not figure out why a simple stored procedure was not working in MySql MySQL Connector/J 5.1.9 (MySQL server 5.4). Assuming, I won't find a solution either, like many others, I am posting a solution that is a work around for this configuration.
Code:
DELIMITER $$
CREATE PROCEDURE `myDepartName`()
NOT DETERMINISTIC
SQL SECURITY DEFINER
COMMENT ''
BEGIN
SELECT min(department_id) as department_id, min(department_name) as department_name FROM company_department;
END$$
DELIMITER ;
Common Hibernate configuration:
<property name="hibernate.connection.driver_class">
com.mysql.jdbc.Driver
</property>
<property name="hibernate.dialect">
org.hibernate.dialect.MySQL5Dialect
</property>
CONFIGURATION THAT FAILS: This example configuration is discussed and used in almost in every other place. Although it might work for others, it does not in my case, as it throws the column not found exception.
Department mapping file:
<sql-query name="departmentName_SP" callable="true">
<return alias="dept" class="database.Department">
<return-property name="department_id" column="department_id"/>
<return-property name="department_name" column="department_name"/>
</return>
{ call myDepartName() }
</sql-query>
access the stored procedure as
session.getNamedQuery("myDepartName").list();
throws an exception stack trace:
Hibernate:
/* named native SQL query departmentName_SP */ { call myDepartName() }
org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:90)WARN - JDBCExceptionReporter.logExceptions(100) | SQL Error: 0, SQLState: S0022
ERROR - JDBCExceptionReporter.logExceptions(101) | Column 'record3_5_0_' not found.
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
at org.hibernate.loader.Loader.doList(Loader.java:2235)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2129)
at org.hibernate.loader.Loader.list(Loader.java:2124)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:312)
at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1723)
at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:165)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:175)
at webservices.util.WebServicesUtility.main(WebServicesUtility.java:419)
Caused by: java.sql.SQLException: Column 'record3_5_0_' not found.
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:926)
at com.mysql.jdbc.ResultSetImpl.findColumn(ResultSetImpl.java:1144)
at com.mysql.jdbc.ResultSetImpl.getString(ResultSetImpl.java:5549)
at com.mchange.v2.c3p0.impl.NewProxyResultSet.getString(NewProxyResultSet.java:3342)
at org.hibernate.type.StringType.get(StringType.java:41)
at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:184)
at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:173)
at org.hibernate.type.AbstractType.hydrate(AbstractType.java:105)
at org.hibernate.persister.entity.AbstractEntityPersister.hydrate(AbstractEntityPersister.java:2114)
at org.hibernate.loader.Loader.loadFromResultSet(Loader.java:1404)
at org.hibernate.loader.Loader.instanceNotYetLoaded(Loader.java:1332)
at org.hibernate.loader.Loader.getRow(Loader.java:1230)
at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:603)
at org.hibernate.loader.Loader.doQuery(Loader.java:724)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259)
at org.hibernate.loader.Loader.doList(Loader.java:2232)
... 7 more
WORKING ALTERNATIVE:However, changing the code to the following works:
<sql-query name="departmentName_SP" callable="true">
<return-scalar column="department_id" type="java.lang.Long"/>
<return-scalar column="department_name" type="java.lang.String"/>
{ call myDepartName() }
</sql-query>
and using a resultTransformer works fine as,
Query qry = session.getNamedQuery("departmentName_SP").
setResultTransformer(Transformers.aliasToBean(Department.class));
List<Department> list = qry.list();
for(Department department : list)
{
System.out.println(department.getDepartment_name());
}
I tested this for a both single row and multiple rows returning as resultset in my stored procedure. This also worked for parametrized stored procedures.
This alternative is preferred when you can store stored procedures belonging to an entity in the same hbm file itself. If your stored procedures return calculated values when you are not mapping the result set to the existing bean properties, i.e., not part of the entity bean or table columns, adding these calculated fields/attributes that are not part of the entity bean adds confusion and breaks convention. A resultsettransformer or a custom transformer can always map the resultset to your custom bean rather than an entity bean.
Login
FAQ
Search
