I'm trying to call a stored procedure using Hibernate, but I'm getting the following exception all the time:
Code:
org.postgresql.util.PSQLException: The column name plan_id was not found in this ResultSet.
The stored procedure is added as an auxiliary database object in one hbm.xml (actually this is the only XML mapping file I have, everything else is done with annotations):
Code:
<hibernate-mapping>
<database-object>
<create>
CREATE OR REPLACE FUNCTION copy_plan(p_plan_id bigint, p_plan_for_year varchar, p_copy_pp boolean)
RETURNS REFCURSOR
AS $$
DECLARE
result REFCURSOR;
// some more declarations
BEGIN
// a lot of logic which works fine
OPEN result FOR
SELECT plans.plan_id as plan_id,
plans.plan_for_year as plan_for_year,
plans.plan_plan_id as plan_plan_id,
plans.plan_f_type as plan_f_type,
plans.plan_enabled as plan_enabled
FROM rgt_plans plans WHERE plans.plan_id = v_plan_id;
RETURN result;
END;
$$ LANGUAGE PLPGSQL
</create>
<drop>DROP FUNCTION copy_plan</drop>
</database-object>
</hibernate-mapping>
The entity class:
Code:
@Entity
@Table(name = "rgt_plans")
@NamedNativeQueries({ @NamedNativeQuery(name = "copyPlan", query = "select copy_plan(:planId, :newYear, :copy)", resultClass=Plan.class) })
public class Plan implements Serializable {
private static final long serialVersionUID = -8448579977833577641L;
@Id
@GeneratedValue(generator = "IdGenerator", strategy = GenerationType.TABLE)
@TableGenerator(name = "IdGenerator", pkColumnValue = "rgt_plans", table = "Sequence_Table", allocationSize = 1)
@Column(name = "plan_id", columnDefinition = "int4")
private Long id;
@Column(name = "plan_for_year")
@NotNull
private String forYear;
@Column(name = "plan_plan_id")
@Basic
private Long plan_id;
@Column(name = "plan_f_type", length = 1)
@Basic
@NotNull
private String type;
@Column(name = "plan_enabled")
@Basic
@NotNull
private Boolean enabled;
// getters, setters, equals, hashcode
}
And thats how I call it from my DAO:
Code:
public List<Plan> copyPlan(Long currentPlanId, String newYear, Boolean copy) {
return this.getHibernateTemplate()
.findByNamedQueryAndNamedParam("copyPlan",
new String[] { "planId", "newYear", "copy" },
new Object[] { currentPlanId, newYear, copy });
}
As you can see I'm using PostgreSQL (8 if that's relevant). Any ideas to why it's not working? I mean I'm returning everything from that table even with labels assigned... Not sure whether it's a hibernate or postgres related problem but hopefully someone will be able to help me. Posted this also on StackOverflow but with no luck.