Mybatis with stored procedure to get association object has issues
1,270 views
Skip to first unread message
Parag Dhikale
Mar 1, 2014, 4:01:13 PM3/1/14
to mybati...@googlegroups.com
Hi,
I am using mybatis 3.2.3. I was trying to use stored procedure to return multiple relation in one DB hit.
////////////////////////////////////////////////////////// SUMMERY///////////////////////////////////////////////////////////////////////////////////////////////////////////
I managed to get collection relation using procedure call, but not able to get 'association' relational object.
e.g.: I have following resultMap :
<resultMap id="contactResult" type="Contact">
<id property="id" column="CONTACT_ID"/>
<result property="deptId" column="C_DEPT_ID"/>
<result property="name" column="CONTACT_NAME"/>
<result property="phone" column="CONTACT_PHONE"/>
<association property="dpt" select="getDpt"
column="C_DEPT_ID" resultSet="dept" foreignColumn="DEPT_ID" javaType="Dept" />
<collection property="emailList" resultSet="emails" select="getEmailList"
column="CONTACT_ID" foreignColumn="E_CONTACT_ID" ofType="Email" javaType="java.util.ArrayList" >
</collection>
</resultMap>
Following call returns me the correct list of emails which is a collection relation (multiple result rows):
<select id="findAllContacts2" resultSets="emails" parameterType="Contact" statementType="CALLABLE" resultMap="contactResult">
{call get_all_contacts(#{id, mode=INOUT, jdbcType=INTEGER}, #{name, mode=OUT, jdbcType=VARCHAR},
#{phone, jdbcType=VARCHAR, mode=OUT},
#{emailList, jdbcType=CURSOR, javaType=java.sql.ResultSet,resultMap=com.sigma.samp.stmSample.EmailInterFace.emailResult, mode=OUT})}
</select>
with Stored proc:
CREATE OR REPLACE PROCEDURE get_all_contacts (V_CONTACT_ID IN OUT NUMBER, V_CONTACT_NAME OUT VARCHAR2,
V_CONTACT_PHONE OUT VARCHAR2, EMAIL_LIST out sys_refcursor)
IS
BEGIN
SELECT CONTACT_ID, CONTACT_NAME, CONTACT_PHONE
INTO V_CONTACT_ID, V_CONTACT_NAME, V_CONTACT_PHONE
FROM CONTACT
WHERE CONTACT_ID = V_CONTACT_ID;
OPEN EMAIL_LIST FOR
SELECT *
from EMAIL
WHERE E_CONTACT_ID = V_CONTACT_ID;
END;
/
////////////////////////////////////////////////////////// PROBLEM ///////////////////////////////////////////////////////////////////////////////////////////////////////////
But when I added an association of 'dept' to 'contact' I couldn't find which JDBC type to be applied for single row return:
<select id="findAllContacts4" resultSets="emails, dept" parameterType="Contact" statementType="CALLABLE" resultMap="contactResult">
{call get_all_contacts3(#{id, mode=INOUT, jdbcType=INTEGER}, #{deptId, mode=INOUT, jdbcType=INTEGER}, #{name, mode=OUT, jdbcType=VARCHAR},
#{phone, jdbcType=VARCHAR, mode=OUT},
#{emailList, jdbcType=CURSOR, javaType=java.sql.ResultSet,resultMap=com.sigma.samp.stmSample.EmailInterFace.emailResult, mode=OUT},
#{dpt, jdbcType=ROWTYPE, javaType=Dept, resultMap=com.sigma.samp.stmSample.DeptInterFace.deptResult, mode=OUT})}
</select>
Stored Proc:
CREATE OR REPLACE PROCEDURE get_all_contacts3 (V_CONTACT_ID IN OUT NUMBER, V_DEPT_ID IN OUT NUMBER, V_CONTACT_NAME OUT VARCHAR2,
V_CONTACT_PHONE OUT VARCHAR2, EMAIL_LIST out sys_refcursor,
V_DEPT OUT DEPT%rowtype)
IS
BEGIN
SELECT CONTACT_ID, CONTACT_NAME, CONTACT_PHONE
INTO V_CONTACT_ID, V_CONTACT_NAME, V_CONTACT_PHONE
FROM CONTACT
WHERE CONTACT_ID = V_CONTACT_ID;
OPEN EMAIL_LIST FOR
SELECT *
from EMAIL
WHERE E_CONTACT_ID = V_CONTACT_ID;
SELECT * into V_DEPT
FROM DEPT
WHERE DEPT_ID = V_DEPT_ID;
END;
/
I am getting following error no matter what jdbcType I specify:
Caused by: java.lang.IllegalStateException: Type handler was null on parameter mapping for property 'dpt'. It was either not specified and/or could not be found for the javaType / jdbcType combination specified.
at org.apache.ibatis.mapping.ParameterMapping$Builder.validate(ParameterMapping.java:114)
at org.apache.ibatis.mapping.ParameterMapping$Builder.build(ParameterMapping.java:101)
Can anybody please help me with this?
Thanks & Regards,
Parag Dhikale
I am using mybatis 3.2.3. I was trying to use stored procedure to return multiple relation in one DB hit.
////////////////////////////////////////////////////////// SUMMERY///////////////////////////////////////////////////////////////////////////////////////////////////////////
I managed to get collection relation using procedure call, but not able to get 'association' relational object.
e.g.: I have following resultMap :
<resultMap id="contactResult" type="Contact">
<id property="id" column="CONTACT_ID"/>
<result property="deptId" column="C_DEPT_ID"/>
<result property="name" column="CONTACT_NAME"/>
<result property="phone" column="CONTACT_PHONE"/>
<association property="dpt" select="getDpt"
column="C_DEPT_ID" resultSet="dept" foreignColumn="DEPT_ID" javaType="Dept" />
<collection property="emailList" resultSet="emails" select="getEmailList"
column="CONTACT_ID" foreignColumn="E_CONTACT_ID" ofType="Email" javaType="java.util.ArrayList" >
</collection>
</resultMap>
Following call returns me the correct list of emails which is a collection relation (multiple result rows):
<select id="findAllContacts2" resultSets="emails" parameterType="Contact" statementType="CALLABLE" resultMap="contactResult">
{call get_all_contacts(#{id, mode=INOUT, jdbcType=INTEGER}, #{name, mode=OUT, jdbcType=VARCHAR},
#{phone, jdbcType=VARCHAR, mode=OUT},
#{emailList, jdbcType=CURSOR, javaType=java.sql.ResultSet,resultMap=com.sigma.samp.stmSample.EmailInterFace.emailResult, mode=OUT})}
</select>
with Stored proc:
CREATE OR REPLACE PROCEDURE get_all_contacts (V_CONTACT_ID IN OUT NUMBER, V_CONTACT_NAME OUT VARCHAR2,
V_CONTACT_PHONE OUT VARCHAR2, EMAIL_LIST out sys_refcursor)
IS
BEGIN
SELECT CONTACT_ID, CONTACT_NAME, CONTACT_PHONE
INTO V_CONTACT_ID, V_CONTACT_NAME, V_CONTACT_PHONE
FROM CONTACT
WHERE CONTACT_ID = V_CONTACT_ID;
OPEN EMAIL_LIST FOR
SELECT *
from EMAIL
WHERE E_CONTACT_ID = V_CONTACT_ID;
END;
/
////////////////////////////////////////////////////////// PROBLEM ///////////////////////////////////////////////////////////////////////////////////////////////////////////
But when I added an association of 'dept' to 'contact' I couldn't find which JDBC type to be applied for single row return:
<select id="findAllContacts4" resultSets="emails, dept" parameterType="Contact" statementType="CALLABLE" resultMap="contactResult">
{call get_all_contacts3(#{id, mode=INOUT, jdbcType=INTEGER}, #{deptId, mode=INOUT, jdbcType=INTEGER}, #{name, mode=OUT, jdbcType=VARCHAR},
#{phone, jdbcType=VARCHAR, mode=OUT},
#{emailList, jdbcType=CURSOR, javaType=java.sql.ResultSet,resultMap=com.sigma.samp.stmSample.EmailInterFace.emailResult, mode=OUT},
#{dpt, jdbcType=ROWTYPE, javaType=Dept, resultMap=com.sigma.samp.stmSample.DeptInterFace.deptResult, mode=OUT})}
</select>
Stored Proc:
CREATE OR REPLACE PROCEDURE get_all_contacts3 (V_CONTACT_ID IN OUT NUMBER, V_DEPT_ID IN OUT NUMBER, V_CONTACT_NAME OUT VARCHAR2,
V_CONTACT_PHONE OUT VARCHAR2, EMAIL_LIST out sys_refcursor,
V_DEPT OUT DEPT%rowtype)
IS
BEGIN
SELECT CONTACT_ID, CONTACT_NAME, CONTACT_PHONE
INTO V_CONTACT_ID, V_CONTACT_NAME, V_CONTACT_PHONE
FROM CONTACT
WHERE CONTACT_ID = V_CONTACT_ID;
OPEN EMAIL_LIST FOR
SELECT *
from EMAIL
WHERE E_CONTACT_ID = V_CONTACT_ID;
SELECT * into V_DEPT
FROM DEPT
WHERE DEPT_ID = V_DEPT_ID;
END;
/
I am getting following error no matter what jdbcType I specify:
Caused by: java.lang.IllegalStateException: Type handler was null on parameter mapping for property 'dpt'. It was either not specified and/or could not be found for the javaType / jdbcType combination specified.
at org.apache.ibatis.mapping.ParameterMapping$Builder.validate(ParameterMapping.java:114)
at org.apache.ibatis.mapping.ParameterMapping$Builder.build(ParameterMapping.java:101)
Can anybody please help me with this?
Thanks & Regards,
Parag Dhikale
Guy Rouillier
Mar 2, 2014, 2:25:34 AM3/2/14
to mybati...@googlegroups.com
See the MyBatis User's Guide. Section
4.1.4 discusses returning a STRUCT parameter type, and section
4.1.5.9 shows how to handle multiple resultsets for associations.
--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mybatis-user...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
-- Guy Rouillier
|
This email is free from viruses and malware because avast! Antivirus protection is active. |
Parag Dhikale
Mar 2, 2014, 4:41:15 AM3/2/14
to mybati...@googlegroups.com
Thanks for the reply Guy. But I went through that already. There is only one line about struct. It doesn't really give much idea how to use it. And when I used it the way given there, it still fails with same error.
If we can get set of rows from procedure into collection object so easily using jdbctype 'cursor', why isn't there simillar simple construct that I can simply use as JDBC type which will automatically map to my Object type for value population?
I have got my multiple resultset return syntax correct, the only problem is in assigning single row return type to my association object 'Dept'. I have debugged into mybatis 3.2.3 code and it is failing in only assignment of this association object because of no matching jdbcType to my java object type.
It will really be helpful, if you can explain in bit detail.
Thanks & Regards,
Parag
If we can get set of rows from procedure into collection object so easily using jdbctype 'cursor', why isn't there simillar simple construct that I can simply use as JDBC type which will automatically map to my Object type for value population?
I have got my multiple resultset return syntax correct, the only problem is in assigning single row return type to my association object 'Dept'. I have debugged into mybatis 3.2.3 code and it is failing in only assignment of this association object because of no matching jdbcType to my java object type.
It will really be helpful, if you can explain in bit detail.
Thanks & Regards,
Parag
Parag Dhikale
Mar 2, 2014, 6:17:14 AM3/2/14
to mybati...@googlegroups.com
More update on this, Just to confirm whether my multiple resultset return works correctly, I converted 'dpt' association object relation in 'Contact' to 'list' type, i.e. collection. and used following syntax, which worked perfectly:
MAPPER:
MAPPER:
<resultMap id="contactResult" type="Contact">
<id property="id" column="CONTACT_ID"/>
<result property="deptId" column="C_DEPT_ID"/>
<result property="name" column="CONTACT_NAME"/>
<result property="phone" column="CONTACT_PHONE"/>
<id property="id" column="CONTACT_ID"/>
<result property="deptId" column="C_DEPT_ID"/>
<result property="name" column="CONTACT_NAME"/>
<result property="phone" column="CONTACT_PHONE"/>
<collection property="dpt" select="getDpt"
column="C_DEPT_ID" resultSet="dept" foreignColumn="DEPT_ID" ofType="Dept" javaType="java.util.ArrayList"/>
column="C_DEPT_ID" resultSet="dept" foreignColumn="DEPT_ID" ofType="Dept" javaType="java.util.ArrayList"/>
<collection property="emailList" resultSet="emails" select="getEmailList"
column="CONTACT_ID" foreignColumn="E_CONTACT_ID" ofType="Email" javaType="java.util.ArrayList" >
</collection>
</resultMap>
Query:
<select id="findAllContacts4" resultSets="emails, dept" parameterType="Contact" statementType="CALLABLE" resultMap="contactResult">
{call get_all_contacts3(#{id, mode=INOUT, jdbcType=INTEGER}, #{deptId, mode=INOUT, jdbcType=INTEGER}, #{name, mode=OUT, jdbcType=VARCHAR},
#{phone, jdbcType=VARCHAR, mode=OUT},
#{emailList, jdbcType=CURSOR, javaType=java.sql.ResultSet,resultMap=com.sigma.samp.stmSample.EmailInterFace.emailResult, mode=OUT},
{call get_all_contacts3(#{id, mode=INOUT, jdbcType=INTEGER}, #{deptId, mode=INOUT, jdbcType=INTEGER}, #{name, mode=OUT, jdbcType=VARCHAR},
#{phone, jdbcType=VARCHAR, mode=OUT},
#{emailList, jdbcType=CURSOR, javaType=java.sql.ResultSet,resultMap=com.sigma.samp.stmSample.EmailInterFace.emailResult, mode=OUT},
#{dpt, jdbcType=CURSOR, javaType=java.sql.ResultSet, resultMap=com.sigma.samp.stmSample.DeptInterFace.deptResult, mode=OUT})}
</select>
Procedure:
</select>
Procedure:
CREATE OR REPLACE PROCEDURE get_all_contacts3 (V_CONTACT_ID IN OUT NUMBER, V_DEPT_ID IN OUT NUMBER, V_CONTACT_NAME OUT VARCHAR2,
V_CONTACT_PHONE OUT VARCHAR2, EMAIL_LIST out sys_refcursor,
V_DEPT OUT sys_refcursor)
IS
BEGIN
SELECT CONTACT_ID, CONTACT_NAME, CONTACT_PHONE
INTO V_CONTACT_ID, V_CONTACT_NAME, V_CONTACT_PHONE
FROM CONTACT
WHERE CONTACT_ID = V_CONTACT_ID;
OPEN EMAIL_LIST FOR
SELECT *
from EMAIL
WHERE E_CONTACT_ID = V_CONTACT_ID;
-- SELECT * into V_DEPT
-- FROM DEPT
-- WHERE DEPT_ID = V_DEPT_ID;
OPEN V_DEPT FOR
SELECT *
-- FROM DEPT
-- WHERE DEPT_ID = V_DEPT_ID;
OPEN V_DEPT FOR
SELECT *
FROM DEPT
WHERE DEPT_ID = V_DEPT_ID;
END;
/
================================================================================
But I don't want to change my association type to collection just for this.
I want this to work with :
Mapper:
But I don't want to change my association type to collection just for this.
I want this to work with :
Mapper:
<resultMap id="contactResult" type="Contact">
<id property="id" column="CONTACT_ID"/>
<result property="deptId" column="C_DEPT_ID"/>
<result property="name" column="CONTACT_NAME"/>
<result property="phone" column="CONTACT_PHONE"/>
<association property="dpt" select="getDpt"
column="C_DEPT_ID" resultSet="dept" foreignColumn="DEPT_ID" javaType="Dept" />
<collection property="emailList" resultSet="emails" select="getEmailList"
column="CONTACT_ID" foreignColumn="E_CONTACT_ID" ofType="Email" javaType="java.util.ArrayList" >
</collection>
</resultMap>
<id property="id" column="CONTACT_ID"/>
<result property="deptId" column="C_DEPT_ID"/>
<result property="name" column="CONTACT_NAME"/>
<result property="phone" column="CONTACT_PHONE"/>
<association property="dpt" select="getDpt"
column="C_DEPT_ID" resultSet="dept" foreignColumn="DEPT_ID" javaType="Dept" />
<collection property="emailList" resultSet="emails" select="getEmailList"
column="CONTACT_ID" foreignColumn="E_CONTACT_ID" ofType="Email" javaType="java.util.ArrayList" >
</collection>
</resultMap>
Procedure:
CREATE OR REPLACE PROCEDURE get_all_contacts3 (V_CONTACT_ID IN OUT NUMBER, V_DEPT_ID IN OUT NUMBER, V_CONTACT_NAME OUT VARCHAR2,
V_CONTACT_PHONE OUT VARCHAR2, EMAIL_LIST out sys_refcursor,
V_DEPT OUT DEPT%rowtype)
IS
BEGIN
SELECT CONTACT_ID, CONTACT_NAME, CONTACT_PHONE
INTO V_CONTACT_ID, V_CONTACT_NAME, V_CONTACT_PHONE
FROM CONTACT
WHERE CONTACT_ID = V_CONTACT_ID;
OPEN EMAIL_LIST FOR
SELECT *
from EMAIL
WHERE E_CONTACT_ID = V_CONTACT_ID;
SELECT * into V_DEPT
FROM DEPT
WHERE DEPT_ID = V_DEPT_ID;
END;
/
V_CONTACT_PHONE OUT VARCHAR2, EMAIL_LIST out sys_refcursor,
V_DEPT OUT DEPT%rowtype)
IS
BEGIN
SELECT CONTACT_ID, CONTACT_NAME, CONTACT_PHONE
INTO V_CONTACT_ID, V_CONTACT_NAME, V_CONTACT_PHONE
FROM CONTACT
WHERE CONTACT_ID = V_CONTACT_ID;
OPEN EMAIL_LIST FOR
SELECT *
from EMAIL
WHERE E_CONTACT_ID = V_CONTACT_ID;
SELECT * into V_DEPT
FROM DEPT
WHERE DEPT_ID = V_DEPT_ID;
END;
/
Query:
<select id="findAllContacts4" resultSets="emails, dept" parameterType="Contact" statementType="CALLABLE" resultMap="contactResult">
{call get_all_contacts3(#{id, mode=INOUT, jdbcType=INTEGER}, #{deptId, mode=INOUT, jdbcType=INTEGER}, #{name, mode=OUT, jdbcType=VARCHAR},
#{phone, jdbcType=VARCHAR, mode=OUT},
#{emailList, jdbcType=CURSOR, javaType=java.sql.ResultSet,resultMap=com.sigma.samp.stmSample.EmailInterFace.emailResult, mode=OUT},
{call get_all_contacts3(#{id, mode=INOUT, jdbcType=INTEGER}, #{deptId, mode=INOUT, jdbcType=INTEGER}, #{name, mode=OUT, jdbcType=VARCHAR},
#{phone, jdbcType=VARCHAR, mode=OUT},
#{emailList, jdbcType=CURSOR, javaType=java.sql.ResultSet,resultMap=com.sigma.samp.stmSample.EmailInterFace.emailResult, mode=OUT},
#{dpt, jdbcType=<?>, javaType=com.sigma.samp.stmSample.Dept, resultMap=com.sigma.samp.stmSample.DeptInterFace.deptResult, mode=OUT})}
</select>
</select>
On Sunday, 2 March 2014 02:31:13 UTC+5:30, Parag Dhikale wrote:
Guy Rouillier
Mar 2, 2014, 2:53:25 PM3/2/14
to mybati...@googlegroups.com
I can't do any more without some working code. If you'd like further
suggestions, then please package up a working sample. Include the
necessary DDL to create the tables and stored procedures, and insert
some dummy data into the tables. You can attach a zip file in email
sent to this mailing list.
On 3/2/2014 6:17 AM, Parag Dhikale wrote:
> More update on this, Just to confirm whether my multiple resultset
> return works correctly, I converted 'dpt' association object relation in
> 'Contact' to 'list' type, i.e. collection. and used following syntax,
> which worked perfectly:
>
> *MAPPER:
>
> * <resultMap id="contactResult" type="Contact">
> * <select id="findAllContacts4" resultSets="emails, dept"
> resultMap=com.sigma.samp.stmSample.DeptInterFace.deptResult, mode=OUT})}
> </select>
>
> *Procedure:
> *
>
> * <resultMap id="contactResult" type="Contact">
>
> Procedure:
>
> *CREATE OR REPLACE PROCEDURE get_all_contacts3 (V_CONTACT_ID IN OUT
>
> Query:
>
> * <select id="findAllContacts4" resultSets="emails, dept"
> javaType*=com.sigma.samp.stmSample.Dept*,
> resultMap=com.sigma.samp.stmSample.DeptInterFace.deptResult, mode=OUT})}
> </select>*
> #{dpt, jdbcType=*ROWTYPE*, javaType=Dept,
---
suggestions, then please package up a working sample. Include the
necessary DDL to create the tables and stored procedures, and insert
some dummy data into the tables. You can attach a zip file in email
sent to this mailing list.
On 3/2/2014 6:17 AM, Parag Dhikale wrote:
> More update on this, Just to confirm whether my multiple resultset
> return works correctly, I converted 'dpt' association object relation in
> 'Contact' to 'list' type, i.e. collection. and used following syntax,
> which worked perfectly:
>
>
> * <resultMap id="contactResult" type="Contact">
> <id property="id" column="CONTACT_ID"/>
> <result property="deptId" column="C_DEPT_ID"/>
> <result property="name" column="CONTACT_NAME"/>
> <result property="phone" column="CONTACT_PHONE"/>
> <*collection property="dpt" select="getDpt"
> <result property="deptId" column="C_DEPT_ID"/>
> <result property="name" column="CONTACT_NAME"/>
> <result property="phone" column="CONTACT_PHONE"/>
> column="C_DEPT_ID" resultSet="dept" foreignColumn="DEPT_ID"
> ofType="Dept" javaType="java.util.ArrayList"/*>
> <collection property="emailList" resultSet="emails"
> select="getEmailList"
> column="CONTACT_ID" foreignColumn="E_CONTACT_ID"
> ofType="Email" javaType="java.util.ArrayList" >
> </collection>
> </resultMap>
>
> *Query:
> select="getEmailList"
> column="CONTACT_ID" foreignColumn="E_CONTACT_ID"
> ofType="Email" javaType="java.util.ArrayList" >
> </collection>
> </resultMap>
>
> * <select id="findAllContacts4" resultSets="emails, dept"
> parameterType="Contact" statementType="CALLABLE" resultMap="contactResult">
> {call get_all_contacts3(#{id, mode=INOUT, jdbcType=INTEGER},
> #{deptId, mode=INOUT, jdbcType=INTEGER}, #{name, mode=OUT,
> jdbcType=VARCHAR},
> #{phone, jdbcType=VARCHAR, mode=OUT},
> #{emailList, jdbcType=CURSOR,
> javaType=java.sql.ResultSet,resultMap=com.sigma.samp.stmSample.EmailInterFace.emailResult,
> mode=OUT},
> #{dpt, *jdbcType=CURSOR, javaType=java.sql.ResultSet*,
> {call get_all_contacts3(#{id, mode=INOUT, jdbcType=INTEGER},
> #{deptId, mode=INOUT, jdbcType=INTEGER}, #{name, mode=OUT,
> jdbcType=VARCHAR},
> #{phone, jdbcType=VARCHAR, mode=OUT},
> #{emailList, jdbcType=CURSOR,
> javaType=java.sql.ResultSet,resultMap=com.sigma.samp.stmSample.EmailInterFace.emailResult,
> mode=OUT},
> resultMap=com.sigma.samp.stmSample.DeptInterFace.deptResult, mode=OUT})}
> </select>
>
> *Procedure:
> *
> CREATE OR REPLACE PROCEDURE get_all_contacts3 (V_CONTACT_ID IN OUT
> NUMBER, V_DEPT_ID IN OUT NUMBER, V_CONTACT_NAME OUT VARCHAR2,
> V_CONTACT_PHONE OUT
> VARCHAR2, EMAIL_LIST out sys_refcursor,
> V_DEPT OUT *sys_refcursor*)
> NUMBER, V_DEPT_ID IN OUT NUMBER, V_CONTACT_NAME OUT VARCHAR2,
> V_CONTACT_PHONE OUT
> VARCHAR2, EMAIL_LIST out sys_refcursor,
> IS
> BEGIN
>
> SELECT CONTACT_ID, CONTACT_NAME, CONTACT_PHONE
> INTO V_CONTACT_ID, V_CONTACT_NAME, V_CONTACT_PHONE
> FROM CONTACT
> WHERE CONTACT_ID = V_CONTACT_ID;
>
> OPEN EMAIL_LIST FOR
> SELECT *
> from EMAIL
> WHERE E_CONTACT_ID = V_CONTACT_ID;
>
> -- SELECT * into V_DEPT
> -- FROM DEPT
> -- WHERE DEPT_ID = V_DEPT_ID;
>
> OPEN *V_DEPT *FOR
> BEGIN
>
> SELECT CONTACT_ID, CONTACT_NAME, CONTACT_PHONE
> INTO V_CONTACT_ID, V_CONTACT_NAME, V_CONTACT_PHONE
> FROM CONTACT
> WHERE CONTACT_ID = V_CONTACT_ID;
>
> OPEN EMAIL_LIST FOR
> SELECT *
> from EMAIL
> WHERE E_CONTACT_ID = V_CONTACT_ID;
>
> -- SELECT * into V_DEPT
> -- FROM DEPT
> -- WHERE DEPT_ID = V_DEPT_ID;
>
> SELECT *
> FROM DEPT
> WHERE DEPT_ID = V_DEPT_ID;
>
>
> END;
> /
>
> ================================================================================
>
> But I don't want to change my association type to collection just for this.
>
> I want this to work with :
>
> *Mapper:
> FROM DEPT
> WHERE DEPT_ID = V_DEPT_ID;
>
>
> END;
> /
>
> ================================================================================
>
> But I don't want to change my association type to collection just for this.
>
> I want this to work with :
>
>
> * <resultMap id="contactResult" type="Contact">
> <id property="id" column="CONTACT_ID"/>
> <result property="deptId" column="C_DEPT_ID"/>
> <result property="name" column="CONTACT_NAME"/>
> <result property="phone" column="CONTACT_PHONE"/>
> * <association property="dpt" select="getDpt"
> <result property="deptId" column="C_DEPT_ID"/>
> <result property="name" column="CONTACT_NAME"/>
> <result property="phone" column="CONTACT_PHONE"/>
> column="C_DEPT_ID" resultSet="dept" foreignColumn="DEPT_ID"
> javaType="Dept" />*
> <collection property="emailList" resultSet="emails"
> select="getEmailList"
> column="CONTACT_ID" foreignColumn="E_CONTACT_ID"
> ofType="Email" javaType="java.util.ArrayList" >
> </collection>
> </resultMap>*
> select="getEmailList"
> column="CONTACT_ID" foreignColumn="E_CONTACT_ID"
> ofType="Email" javaType="java.util.ArrayList" >
> </collection>
>
> Procedure:
>
> *CREATE OR REPLACE PROCEDURE get_all_contacts3 (V_CONTACT_ID IN OUT
> NUMBER, V_DEPT_ID IN OUT NUMBER, V_CONTACT_NAME OUT VARCHAR2,
> V_CONTACT_PHONE OUT
> VARCHAR2, EMAIL_LIST out sys_refcursor,
> V_DEPT OUT *DEPT%rowtype*)
> V_CONTACT_PHONE OUT
> VARCHAR2, EMAIL_LIST out sys_refcursor,
> IS
> BEGIN
>
> SELECT CONTACT_ID, CONTACT_NAME, CONTACT_PHONE
> INTO V_CONTACT_ID, V_CONTACT_NAME, V_CONTACT_PHONE
> FROM CONTACT
> WHERE CONTACT_ID = V_CONTACT_ID;
>
> OPEN EMAIL_LIST FOR
> SELECT *
> from EMAIL
> WHERE E_CONTACT_ID = V_CONTACT_ID;
>
> SELECT * into *V_DEPT*
> BEGIN
>
> SELECT CONTACT_ID, CONTACT_NAME, CONTACT_PHONE
> INTO V_CONTACT_ID, V_CONTACT_NAME, V_CONTACT_PHONE
> FROM CONTACT
> WHERE CONTACT_ID = V_CONTACT_ID;
>
> OPEN EMAIL_LIST FOR
> SELECT *
> from EMAIL
> WHERE E_CONTACT_ID = V_CONTACT_ID;
>
> FROM DEPT
> WHERE DEPT_ID = V_DEPT_ID;
>
>
> END;
> /*
> WHERE DEPT_ID = V_DEPT_ID;
>
>
> END;
>
> Query:
>
> * <select id="findAllContacts4" resultSets="emails, dept"
> parameterType="Contact" statementType="CALLABLE" resultMap="contactResult">
> {call get_all_contacts3(#{id, mode=INOUT, jdbcType=INTEGER},
> #{deptId, mode=INOUT, jdbcType=INTEGER}, #{name, mode=OUT,
> jdbcType=VARCHAR},
> #{phone, jdbcType=VARCHAR, mode=OUT},
> #{emailList, jdbcType=CURSOR,
> javaType=java.sql.ResultSet,resultMap=com.sigma.samp.stmSample.EmailInterFace.emailResult,
> mode=OUT},
> #{dpt, jdbcType=*<?>*,
> {call get_all_contacts3(#{id, mode=INOUT, jdbcType=INTEGER},
> #{deptId, mode=INOUT, jdbcType=INTEGER}, #{name, mode=OUT,
> jdbcType=VARCHAR},
> #{phone, jdbcType=VARCHAR, mode=OUT},
> #{emailList, jdbcType=CURSOR,
> javaType=java.sql.ResultSet,resultMap=com.sigma.samp.stmSample.EmailInterFace.emailResult,
> mode=OUT},
> javaType*=com.sigma.samp.stmSample.Dept*,
> resultMap=com.sigma.samp.stmSample.DeptInterFace.deptResult, mode=OUT})}
> </select>*
> --
> You received this message because you are subscribed to the Google
> Groups "mybatis-user" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to mybatis-user...@googlegroups.com
> <mailto:mybatis-user...@googlegroups.com>.
> You received this message because you are subscribed to the Google
> Groups "mybatis-user" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to mybatis-user...@googlegroups.com
---
This email is free from viruses and malware because avast! Antivirus protection is active.
http://www.avast.com
Parag Dhikale
Mar 3, 2014, 2:41:22 AM3/3/14
to mybati...@googlegroups.com
Thanks for your support Guy, but it is tedious and will take me lot of time to make workable package to be sent to you and my time frame doesn't permit me, sorry for that.
Let me give another shot to explain my problem, Basically I want to know what 'jdbcType' can be given in 'out' parameter definition that can be mapped to single row returned from procedure (NOTE: not collection of rows, so no 'CURSOR' jdbc type is not allowed) and which will automatically assign to my association type of relational object (i.e. Single object, not list of objects).
Let me give another shot to explain my problem, Basically I want to know what 'jdbcType' can be given in 'out' parameter definition that can be mapped to single row returned from procedure (NOTE: not collection of rows, so no 'CURSOR' jdbc type is not allowed) and which will automatically assign to my association type of relational object (i.e. Single object, not list of objects).
<select id="findAllContacts4" resultSets="emails, dept" parameterType="Contact" statementType="CALLABLE" resultMap="contactResult">
{call get_all_contacts3(#{id, mode=INOUT, jdbcType=INTEGER}, #{deptId, mode=INOUT, jdbcType=INTEGER}, #{name, mode=OUT, jdbcType=VARCHAR},
#{phone, jdbcType=VARCHAR, mode=OUT},
#{emailList, jdbcType=CURSOR, javaType=java.sql.ResultSet,resultMap=com.sigma.samp.stmSample.EmailInterFace.emailResult, mode=OUT},
#{dpt, jdbcType=<?>, javaType=com.sigma.samp.stmSample.Dept, resultMap=com.sigma.samp.stmSample.DeptInterFace.deptResult, mode=OUT})}
</select>
</select>
Please let me know if you don't understand some points.
Thanks & Regards,
Parag Dhikale
On Sunday, 2 March 2014 02:31:13 UTC+5:30, Parag Dhikale wrote:
Parag Dhikale
Mar 3, 2014, 4:49:43 AM3/3/14
to mybati...@googlegroups.com
Just another update:
I don't know how appropriate it is in terms of some other test scenarios which I might have not considered, but this worked for me.
I have fixed the 'BeanWrapper.java' in MyBatis to add another method called 'extractObjectFromList' which is exact copy of same method from 'ResultExtractor' class. This 'extractObjectFromList' method is called from 'setBeanProperty' method and will convert the 'value' object as per the method requirenment before calling 'method.invoke'.
Following part in brown colour is added extra which did the trick:
private void setBeanProperty(PropertyTokenizer prop, Object object, Object value) {
try {
Invoker method = metaClass.getSetInvoker(prop.getName());
if (value.getClass().equals(ArrayList.class)){
value = extractObjectFromList((ArrayList)value, method.getType());
}
Object[] params = {value};
try {
method.invoke(object, params);
} catch (Throwable t) {
throw ExceptionUtil.unwrapThrowable(t);
}
} catch (Throwable t) {
throw new ReflectionException("Could not set property '" + prop.getName() + "' of '" + object.getClass() + "' with value '" + value + "' Cause: " + t.toString(), t);
}
}
public Object extractObjectFromList(List<Object> list, Class<?> targetType) {
Object value = null;
if (targetType != null && targetType.isAssignableFrom(list.getClass())) {
value = list;
} else if (targetType != null && targetType.isArray()) {
Object[] array = (Object[]) Array.newInstance(targetType.getComponentType(), list.size());
value = list.toArray(array);
} else {
if (list != null && list.size() > 1) {
throw new ExecutorException("Statement returned more than one row, where no more than one was expected.");
} else if (list != null && list.size() == 1) {
value = list.get(0);
}
}
return value;
}
I don't know how appropriate it is in terms of some other test scenarios which I might have not considered, but this worked for me.
I have fixed the 'BeanWrapper.java' in MyBatis to add another method called 'extractObjectFromList' which is exact copy of same method from 'ResultExtractor' class. This 'extractObjectFromList' method is called from 'setBeanProperty' method and will convert the 'value' object as per the method requirenment before calling 'method.invoke'.
Following part in brown colour is added extra which did the trick:
private void setBeanProperty(PropertyTokenizer prop, Object object, Object value) {
try {
Invoker method = metaClass.getSetInvoker(prop.getName());
if (value.getClass().equals(ArrayList.class)){
value = extractObjectFromList((ArrayList)value, method.getType());
}
Object[] params = {value};
try {
method.invoke(object, params);
} catch (Throwable t) {
throw ExceptionUtil.unwrapThrowable(t);
}
} catch (Throwable t) {
throw new ReflectionException("Could not set property '" + prop.getName() + "' of '" + object.getClass() + "' with value '" + value + "' Cause: " + t.toString(), t);
}
}
public Object extractObjectFromList(List<Object> list, Class<?> targetType) {
Object value = null;
if (targetType != null && targetType.isAssignableFrom(list.getClass())) {
value = list;
} else if (targetType != null && targetType.isArray()) {
Object[] array = (Object[]) Array.newInstance(targetType.getComponentType(), list.size());
value = list.toArray(array);
} else {
if (list != null && list.size() > 1) {
throw new ExecutorException("Statement returned more than one row, where no more than one was expected.");
} else if (list != null && list.size() == 1) {
value = list.get(0);
}
}
return value;
}
Thanks & Regards,
Parag Dhikale
On Sunday, 2 March 2014 02:31:13 UTC+5:30, Parag Dhikale wrote:
Reply all
Reply to author
Forward
0 new messages