mybatis处理postgresql问题

lilei0303 2014-02-10 07:04:11
xml的配置文件:
<update id="updateAltitude" parameterType="Map" >
CREATE OR REPLACE FUNCTION test() RETURNS void AS
$BODY$

BEGIN
IF #{am.ID} NOT IN (SELECT id FROM zh_cn."Altitude") THEN
INSERT INTO zh_cn."Altitude" VALUES (#{am.ID}, #{am.Altitude});
ELSIF #{am.ID} IN (SELECT id FROM zh_cn."Altitude") THEN
UPDATE zh_cn."Altitude" SET id = #{am.ID} altitude = #{am.Altitude};
ELSIF EXISTS(SELECT * FROM zh_cn."Altitude" WHERE id NOT IN
<foreach collection="idlist" index="index" item="item" open="(" separator="," close=")" >
#{item}
</foreach>
) THEN
DELETE * FROM zh_cn."Altitude" WHERE id NOT IN
<foreach collection="idlist" index="index" item="item" open="(" separator="," close=")" >
#{item}
</foreach>;
END IF;

END
$BODY$
LANGUAGE 'plpgsql' ;
SELECT test();

</update>


映射接口里是这样的:
public void updateAltitude(@Param("am")AltitudeM am,
@Param("idlist")List<Long> list);

运行时报错:
Exception in thread "main" org.apache.ibatis.exceptions.PersistenceException:
### Error updating database. Cause: org.postgresql.util.PSQLException: 栏位索引超过许可范围:1,栏位数:0。
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: CREATE OR REPLACE FUNCTION test() RETURNS void AS $BODY$ BEGIN IF ? NOT IN (SELECT id FROM zh_cn."Altitude") THEN INSERT INTO zh_cn."Altitude" VALUES (?, ?); ELSIF ? IN (SELECT id FROM zh_cn."Altitude") THEN UPDATE zh_cn."Altitude" SET id = ? altitude = ?; ELSIF EXISTS(SELECT * FROM zh_cn."Altitude" WHERE id NOT IN ( ? , ? , ? , ? , ? , ? , ? ) ) THEN DELETE * FROM zh_cn."Altitude" WHERE id NOT IN ( ? , ? , ? , ? , ? , ? , ? ) ; END IF; END $BODY$ LANGUAGE 'plpgsql' ; SELECT test();
### Cause: org.postgresql.util.PSQLException: 栏位索引超过许可范围:1,栏位数:0。
at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:23)
at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:150)
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:49)
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:43)
at com.sun.proxy.$Proxy0.updateAltitude(Unknown Source)
at Service.InsertService.updateMethod(InsertService.java:39)
at Test.main(Test.java:13)
Caused by: org.postgresql.util.PSQLException: 栏位索引超过许可范围:1,栏位数:0。
at org.postgresql.core.v3.CompositeParameterList.findSubParam(CompositeParameterList.java:37)
at org.postgresql.core.v3.CompositeParameterList.setLiteralParameter(CompositeParameterList.java:78)
at org.postgresql.jdbc2.AbstractJdbc2Statement.bindLiteral(AbstractJdbc2Statement.java:2176)
at org.postgresql.jdbc2.AbstractJdbc2Statement.setLong(AbstractJdbc2Statement.java:1228)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:63)
at com.sun.proxy.$Proxy6.setLong(Unknown Source)
at org.apache.ibatis.type.LongTypeHandler.setNonNullParameter(LongTypeHandler.java:28)
at org.apache.ibatis.type.LongTypeHandler.setNonNullParameter(LongTypeHandler.java:23)
at org.apache.ibatis.type.BaseTypeHandler.setParameter(BaseTypeHandler.java:46)
at org.apache.ibatis.type.UnknownTypeHandler.setNonNullParameter(UnknownTypeHandler.java:42)
at org.apache.ibatis.type.BaseTypeHandler.setParameter(BaseTypeHandler.java:46)
at org.apache.ibatis.scripting.defaults.DefaultParameterHandler.setParameters(DefaultParameterHandler.java:77)
at org.apache.ibatis.executor.statement.PreparedStatementHandler.parameterize(PreparedStatementHandler.java:77)
at org.apache.ibatis.executor.statement.RoutingStatementHandler.parameterize(RoutingStatementHandler.java:58)
at org.apache.ibatis.executor.SimpleExecutor.prepareStatement(SimpleExecutor.java:71)
at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:44)
at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:100)
at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:75)
at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:148)
... 5 more


求行家指教
...全文
666 1 打赏 收藏 转发到动态 举报
写回复
用AI写文章
1 条回复
切换为时间正序
请发表友善的回复…
发表回复
lilei0303 2014-02-11
  • 打赏
  • 举报
回复
人呢?求来个人解释下

951

社区成员

发帖
与我相关
我的任务
社区描述
PostgreSQL相关内容讨论
sql数据库数据库架构 技术论坛(原bbs)
社区管理员
  • PostgreSQL社区
  • yang_z_1
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧