作者热门文章
- html - 出于某种原因,IE8 对我的 Sass 文件中继承的 html5 CSS 不友好?
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景?
我正在使用 Java 1.7, IBatis and postgreSQL
我有一个 StudentVO
import java.util.List;
public class StudentVO {
private Long studentId;
private String studentName;
private List<Long> studentFriendNums;
//getters and setters
}
我的 Function
在 postgreSQL 中是 get_party_details(VARIADIC bigint[])
CREATE OR REPLACE FUNCTION get_party_details(VARIADIC bigint[])
RETURNS TABLE(studentid bigint,studentName character varying, amtPaid numeric) AS
$BODY$
DECLARE
SQL VARCHAR;
BEGIN
RETURN QUERY
SELECT
STU.student_id as "Student ID"
,STU.student_name as "Student Name"
,STU.amt_paid as "Amount Paid"
FROM STUDENT STU
WHERE STU.STUDENT_ID IN ( SELECT $1[I] FROM GENERATE_SUBSCRIPTS($1, 1) G(I));
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100
ROWS 1000;
我的函数返回以下结果:
select * from get_party_details(101,102,103,104);
StudentId Student Name Amt Paid
101 JOHN 428000.00
102 SMITH 275405.00
103 JACKSON 109250.00
104 LOVELESS 63200.00
我的 queries.xml
<select id="get_party_details" parameterType="StudentVO"
statementType="PREPARED" resultMap="partyMap">
select * from get_party_details(#{studentFriendNums})
</select>
<resultMap type="StudentVO" id="partyMap">
<result property="studentId" column="studentid" />
<result property="studentName" column="studentname" />
<result property="amtPaid" column="amtpaid" />
</resultMap>
如果我使用上面的 Select 语句,我会得到以下异常:
org.apache.ibatis.exceptions.PersistenceException:
### Error querying database. Cause: java.lang.IllegalStateException: Type handler was null on parameter mapping for property 'studentFriendNums'. It was either not specified and/or could not be found for the javaType / jdbcType combination specified.
### Cause: java.lang.IllegalStateException: Type handler was null on parameter mapping for property 'studentFriendNums'. It was either not specified and/or could not be found for the javaType / jdbcType combination specified.
有什么帮助/建议吗?
编辑 1:我也尝试了一个 foreach 循环,但未能解决问题。
<select id="get_party_details" parameterType="StudentVO" statementType="PREPARED" resultMap="partyMap">
select * from get_party_details(
<foreach item="friends" index="index" collection="studentFriendNums"
open="(" separator="," close=")">
#{friends}
</foreach>
)
</select>
异常 <foreach/>
:
org.apache.ibatis.exceptions.PersistenceException:
### Error querying database. Cause: org.postgresql.util.PSQLException: ERROR: function get_party_details(record) does not exist
Hint: No function matches the given name and argument types. You might need to add explicit type casts.
Position: 15
### The error may exist in com/myblog/queries.xml
### The error may involve com.myblog.StudentDAO.get_party_details-Inline
### The error occurred while setting parameters
### SQL: select * from epc.get_party_details( ( ? , ? , ? , ? ) )
### Cause: org.postgresql.util.PSQLException: ERROR: function get_party_details(record) does not exist
Hint: No function matches the given name and argument types. You might need to add explicit type casts.
Position: 15
编辑 2: 我的 Ibatis 版本是 3.2.3
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.myblog.StudentDTO">
<select id="get_party_details" parameterType="StudentVO"
statementType="PREPARED" resultMap="partyMap">
select * from get_party_details
<iterate property="studentFriendNums" conjunction="," open = "(" close = ")">
#studentFriendNums[]#
</iterate>
</select>
<resultMap type="StudentVO" id="partyMap">
<result property="studentId" column="studentid" />
<result property="studentName" column="studentname" />
<result property="amtPaid" column="amtpaid" />
</resultMap>
</mapper>
最佳答案
您可以使用 foreach :
<select id="get_party_details" parameterType="StudentVO"
statementType="PREPARED" resultMap="partyMap">
select * from get_party_details
<foreach item="item" index="index" collection="studentFriendNums"
open="(" separator="," close=")">
#{item}
</foreach>
</select>
刚刚看到您的第一次编辑,您不需要在 foreach 前后打开和关闭括号。参数 open 和 close 为您完成。
关于java - 如何在 IBatis 的选择查询中发送一个列表?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/30397326/
我是一名优秀的程序员,十分优秀!