在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>我调用的时候会出现java.sql.SQLException: Column 'gp_id' not found.错误,麻烦老大帮我看看我的存储过程有问题么?
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>我调用的时候会出现java.sql.SQLException: Column 'gp_id' not found.错误,麻烦老大帮我看看我的存储过程有问题么?
存储过程里面没有发现任何gp_id的字段,却有好多gb_id。
在你的配置文件里面把gp_id改成gb_id吧。
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就可以了。
我的错误信息如下: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)
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)
at org.apache.tomcat.dbcp.dbcp.DelegatingResultSet.getInt(DelegatingResultSet.java:237)
这句中VALUES中的数据从哪里来?
用INSERT SELECT形式试试
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_business_plan中的主键gp_id我定义的是auto_increment,用INSERT SELECT形式试试是怎样?能不能说的详细些?
insert into jz_business_plan(g_id,gb_id,account_id,bs_id)
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(gid,gbid,accountid,bsid)?
如是多记录,用INSERT SELECT形式,要用VALUES,要打开游标,循环、赋值、插入,
用NSERT SELECT形式简单一些。
insert into jz_business_plan(g_id,gb_id,account_id,bs_id)
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');
错误信息为:2008-11-13 09:38:07,882 WARN [org.hibernate.util.JDBCExceptionReporter] - SQL Error: 0, SQLState: S0022
2008-11-13 09:38:07,882 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)
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)
at org.apache.tomcat.dbcp.dbcp.DelegatingResultSet.getInt(DelegatingResultSet.java:237)
at org.hibernate.type.IntegerType.get(IntegerType.java:28)
at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:113)
at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:102)
at org.hibernate.loader.Loader.getKeyFromResultSet(Loader.java:1088)
at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:554)
at org.hibernate.loader.Loader.doQuery(Loader.java:689)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
at org.hibernate.loader.Loader.doList(Loader.java:2145)
的问题
还是
INSERT SELECT问题?
分开执行一下
jz_business_plan
<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>gp_id 这个列明显的在表jz_business_plan不存在!
<class name="cn.qdrk.strive.model.JzBusinessPlan" table="jz_business_plan" lazy="false">
<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="accountTable" column="account_id"/>
<return-property name="jzBusiness" column="bs_id" />
<return-property name="jzGuestbook" column="gb_id" />
<return-property name="jzGbmes" column="g_id" />
<return-property name="plandate" column="plandate" />
<return-property name="re" column="re" />
</return>
{call undobusiness()}
</sql-query>
</hibernate-mapping>还是出现上述问题。
我单独调用call undobusiness();
获取了数据如下:
发现没有向里面插入进数据,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') ;,这个会列出表中的数据,但是jz_business_plan中列出的数据gp_id 为4,但是account_id,bs_id,gb_id都是null,是不是这方面的原因?