java.sql.SQLException: Column 'gp_id' not found.错误

Question

在hibernate中调用存储过程，其中我的存储过程编写如下：DELIMITER $$DROP PROCEDURE IF EXISTS `qdrk`.`undobusiness`$$CREATE DEFINER=`root`@`localhost` PROCEDURE `undobusiness`()
BEGIN
   DECLARE business_date timestamp default current_timestamp;
   DECLARE  mon int;
   SELECT business_date;
   SELECT max(plandate) into business_date from jz_business_plan;
   SELECT business_date;
   set mon = DATE_FORMAT(business_date,'%m');
   WHILE (mon < DATE_FORMAT(Now(),'%m')) do
     select g_id ,gb_id,account_id,bs_id from jz_gbmes where ((stday < date_format(Now(),'%d') and enday > date_format(Now(),'%d') and remindtype= '2');
     insert into jz_business_plan(g_id,gb_id,account_id,bs_id) values(g_id,gb_id,account_id,bs_id); 
     set mon = mon+1;
   end WHILE; 
   SELECT * from jz_business_plan WHERE DATE_FORMAT(jz_business_plan.plandate,'%m') = DATE_FORMAT(now(),'%m');
END$$DELIMITER ;hibernate的配置文件如下：<hibernate-mapping>
    <class name="cn.qdrk.strive.model.JzBusinessPlan" table="jz_business_plan" catalog="qdrk">
        <id name="gpId" type="java.lang.Integer">
            <column name="gp_id" />
            <generator class="native"></generator>
        </id>
        <many-to-one name="accountTable" class="cn.qdrk.strive.model.AccountTable" fetch="select">
            <column name="account_id" />
        </many-to-one>
        <many-to-one name="jzBusiness" class="cn.qdrk.strive.model.JzBusiness" fetch="select">
            <column name="bs_id" />
        </many-to-one>
        <many-to-one name="jzGuestbook" class="cn.qdrk.strive.model.JzGuestbook" fetch="select">
            <column name="gb_id" />
        </many-to-one>
        <many-to-one name="jzGbmes" class="cn.qdrk.strive.model.JzGbmes" fetch="select">
            <column name="g_id" />
        </many-to-one>
        <property name="plandate" type="java.util.Date">
            <column name="plandate" length="19" not-null="true" />
        </property>
        <property name="re" type="java.lang.String">
            <column name="re" />
        </property>
    </class>
    
    <sql-query name="undobusiness" callable="true">
  <return alias="JzBusinessPlan" class="cn.qdrk.strive.model.JzBusinessPlan">
  <return-property name="gpId" column="gp_id"/>
   <return-property name="jzGbmes" column="g_id" />
   <return-property name="accountTable" column="account_id"/>
   <return-property name="jzBusiness" column="bs_id" />
   <return-property name="plandate" column="plandate" />
   <return-property name="re" column="re" />
   <return-property name="jzGuestbook" column="gb_id" />
  </return>
  {call undobusiness()}
 </sql-query>
</hibernate-mapping>dao接口：public List undoBusiness() {
List list = this.getSession().getNamedQuery("undobusiness").list();我调用的时候会出现java.sql.SQLException: Column 'gp_id' not found.错误，用这种方式调用其他的存储过程时没有错误，郁闷！难道我的存储过程有问题么？

Answer 1

Column 'gp_id' not found.错字段没有，可能是1 数据库连接错误
2 字段写错了，或者表格你看错了

Answer 2

我的表结构如下：CREATE TABLE jz_business_plan(
   gp_id int(10) unsigned NOT NULL auto_increment,
   g_id int(11) unsigned default NULL,
   account_id int(11) unsigned default NULL,
   bs_id int unsigned default null,
   plandate timestamp NOT NULL default CURRENT_TIMESTAMP,
   re varchar(255) default NULL,
   gb_id int unsigned default null,
   index (gb_id),
   FOREIGN KEY (gb_id) REFERENCES jz_guestbook (gb_id),
   index (account_id),
   FOREIGN KEY (account_id) REFERENCES account_table(account_id) ,
   index (g_id),
   FOREIGN KEY (g_id) REFERENCES jz_gbmes(g_id),
   index (bs_id),
   FOREIGN KEY (bs_id) REFERENCES jz_business(bs_id) ,
  PRIMARY KEY  (gp_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;映射文件中也有gp_id啊。数据库应该没有连接错，因为我还能查到其他表的数据。

Answer 3

这是错误信息：2008-11-13 08:33:13,361 INFO [org.hibernate.type.IntegerType] - could not read column value from result set: gp_id; Column 'gp_id' not found.
2008-11-13 08:33:13,382 WARN [org.hibernate.util.JDBCExceptionReporter] - SQL Error: 0, SQLState: S0022
2008-11-13 08:33:13,382 ERROR [org.hibernate.util.JDBCExceptionReporter] - Column 'gp_id' not found.
org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:2148)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2029)
at org.hibernate.loader.Loader.list(Loader.java:2024)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:111)
at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1655)
at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:142)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:164)
at cn.qdrk.strive.dao.hibernate.CRMDaoHibernate.undoBusiness(CRMDaoHibernate.java:43)
at cn.qdrk.strive.dao.hibernate.CRMDaoHibernate$$FastClassByCGLIB$$afe1117e.invoke(<generated>)
at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:149)
at org.springframework.aop.framework.Cglib2AopProxy$CglibMethodInvocation.invokeJoinpoint(Cglib2AopProxy.java:694)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:106)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:629)
at cn.qdrk.strive.dao.hibernate.CRMDaoHibernate$$EnhancerByCGLIB$$cffb0ac1.undoBusiness(<generated>)
at cn.qdrk.strive.struts.action.crm.UndoBusinessAction.selectall(UndoBusinessAction.java:24)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
Caused by: java.sql.SQLException: Column 'gp_id' 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:1093)
at com.mysql.jdbc.ResultSetImpl.getInt(ResultSetImpl.java:2734)

Answer 4

gp_id  这个命名好像不太规范啊，
      你把数据库字段命名都规范化一点，有时候很有可能就是这样原因引起的。

Answer 5

贴下这个cn.qdrk.strive.model.JzBusinessPlan类

Answer 6

cn.qdrk.strive.model.JzBusinessPlan类如下：public abstract class AbstractJzBusinessPlan implements java.io.Serializable { private Integer gpId;
private AccountTable accountTable;
private JzBusiness jzBusiness;
private JzGuestbook jzGuestbook;
private JzGbmes jzGbmes;
private Date plandate;
private String re; // Constructors /** default constructor */
public AbstractJzBusinessPlan() {
} /** minimal constructor */
public AbstractJzBusinessPlan(Date plandate) {
this.plandate = plandate;
} /** full constructor */
public AbstractJzBusinessPlan(AccountTable accountTable,
JzBusiness jzBusiness, JzGuestbook jzGuestbook, JzGbmes jzGbmes,
Date plandate, String re) {
this.accountTable = accountTable;
this.jzBusiness = jzBusiness;
this.jzGuestbook = jzGuestbook;
this.jzGbmes = jzGbmes;
this.plandate = plandate;
this.re = re;
} // Property accessors public Integer getGpId() {
return this.gpId;
} public void setGpId(Integer gpId) {
this.gpId = gpId;
} public AccountTable getAccountTable() {
return this.accountTable;
} public void setAccountTable(AccountTable accountTable) {
this.accountTable = accountTable;
} public JzBusiness getJzBusiness() {
return this.jzBusiness;
} public void setJzBusiness(JzBusiness jzBusiness) {
this.jzBusiness = jzBusiness;
} public JzGuestbook getJzGuestbook() {
return this.jzGuestbook;
} public void setJzGuestbook(JzGuestbook jzGuestbook) {
this.jzGuestbook = jzGuestbook;
} public JzGbmes getJzGbmes() {
return this.jzGbmes;
} public void setJzGbmes(JzGbmes jzGbmes) {
this.jzGbmes = jzGbmes;
} public Date getPlandate() {
return this.plandate;
} public void setPlandate(Date plandate) {
this.plandate = plandate;
} public String getRe() {
return this.re;
} public void setRe(String re) {
this.re = re;
}
}
public class JzBusinessPlan extends AbstractJzBusinessPlan implements
java.io.Serializable {
private static final long serialVersionUID = -9103441798531953281L;
public JzBusinessPlan() {
}
public JzBusinessPlan(Date plandate) {
super(plandate);
} public JzBusinessPlan(AccountTable accountTable, JzBusiness jzBusiness,
JzGuestbook jzGuestbook, JzGbmes jzGbmes, Date plandate,
String re) {
super(accountTable, jzBusiness, jzGuestbook, jzGbmes, plandate, re);
}
}

Answer 7

select g_id ,gb_id,account_id,bs_id from jz_gbmes where ((stday < date_format(Now(),'%d') and enday > date_format(Now(),'%d') and remindtype= '2');
     insert into jz_business_plan(g_id,gb_id,account_id,bs_id) values(g_id,gb_id,account_id,bs_id);<column name="gp_id" />??你是不是写错了 字段名啊？
 

Answer 8

你确定 jz_business_plan 表 有 gp_id 列？sql提示很清楚了  找不到这列

Answer 9

这不是一张表：
select g_id ,gb_id,account_id,bs_id from jz_gbmes where ((stday < date_format(Now(),'%d') and enday > date_format(Now(),'%d') and remindtype= '2'); 
    insert into jz_business_plan(g_id,gb_id,account_id,bs_id) values(g_id,gb_id,account_id,bs_id); 这是表jz_gbmes;<column name="gp_id" /> 这是表jz_business_plan的hibernate映射文件中的内容。

Answer 10

我describe jz_business_plan时，有gp_id这一列，因为把他设为主键，所以在插入数据的时候，就没有insert into jz_business_plan(g_id,gb_id,account_id,bs_id) values(g_id,gb_id,account_id,bs_id); 写gp_id.

Answer 11

你把 存储过程 独立拿到数据库去执行一下 看看返回的字段吧对比一下：
<sql-query name="undobusiness" callable="true">
  <return alias="JzBusinessPlan" class="cn.qdrk.strive.model.JzBusinessPlan">
  <return-property name="gpId" column="gp_id"/>
   <return-property name="jzGbmes" column="g_id" />
   <return-property name="accountTable" column="account_id"/>
   <return-property name="jzBusiness" column="bs_id" />
   <return-property name="plandate" column="plandate" />
   <return-property name="re" column="re" />
   <return-property name="jzGuestbook" column="gb_id" />
  </return>
  {call undobusiness()}
 </sql-query>

Answer 12

在写insert语句时，别的字段可以不插入，但是主键一定要有的，楼主兄弟，修改一下试试看还有没有错

Answer 13

不是，我单独调用undobusiness();的时候列出的信息gp_id为4，plandate为当前时间，其余的列值为null，好像问题出在这个地方。