Received: by 10.66.74.39 with SMTP id q7mr4226824pav.29.1349781229299;
Tue, 09 Oct 2012 04:13:49 -0700 (PDT)
X-BeenThere: mybatis-user@googlegroups.com
Received: by 10.68.237.161 with SMTP id vd1ls25773859pbc.3.gmail; Tue, 09 Oct
2012 04:13:46 -0700 (PDT)
Received: by 10.68.229.231 with SMTP id st7mr5061198pbc.2.1349781226977;
Tue, 09 Oct 2012 04:13:46 -0700 (PDT)
Date: Tue, 9 Oct 2012 04:13:46 -0700 (PDT)
From: grimmel97 <grimme...@gmail.com>
To: mybatis-user@googlegroups.com
Message-Id: <c6cd12b2-e968-45e5-87eb-672743f8f03d@googlegroups.com>
In-Reply-To: <CABtDKm4HE-AQ4LMYfeqN-2d-j=ww01nQ4xW9V86_0xpcsv4U2w@mail.gmail.com>
References: <93781836-d11d-41aa-9424-2fb98131268a@googlegroups.com>
<fe5eed85-aaa9-40bd-857d-87ea00c6fba6@googlegroups.com> <d36d76b5-5885-47f4-9bf8-8ca5d9e43d6d@googlegroups.com>
<f6890ec5-06e3-4ce9-b828-297ee796312c@googlegroups.com> <f1d91136-5e8b-40f8-9e2f-5a491b65397e@googlegroups.com>
<ff2565ba-2e0e-4ffa-abcd-0c872bf5d4dd@googlegroups.com> <11c64dd1-05c6-470d-a729-391622fb0f38@googlegroups.com>
<05755119-8dfe-4aa8-9952-eb01c91686dd@googlegroups.com>
<CABtDKm4HE-AQ4LMYfeqN-2d-j=ww01nQ4xW9V86_0xpcsv4U2w@mail.gmail.com>
Subject: Re: How to Pass Java List of POJO to Oracle Stored Procedure using
MyBatis?
MIME-Version: 1.0
Content-Type: multipart/mixed;
boundary="----=_Part_1643_20405630.1349781226329"
------=_Part_1643_20405630.1349781226329
Content-Type: multipart/alternative;
boundary="----=_Part_1644_31137983.1349781226329"
------=_Part_1644_31137983.1349781226329
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
Hi,
Yes my TypeHandler currently directly implements the interface. Thank you=
=20
for your advice. I will look to take that on board once I can get a simple=
=20
round-trip to the database working.
I have since gotten rid of the OracleConnection casting error, and the=20
statement is getting executed against the database, however I am now=20
getting an Oracle error with either the wrong type or number of parameters.=
=20
Although I suspect it is the first case given my test procedure only has=
=20
one parameter and I am passing one!
On Tuesday, 9 October 2012 18:03:58 UTC+11, Dridi Boukelmoune wrote:
>
> Hi,=20
>
> Does your type handler directly implement the interface ?=20
> If you want to handle nulls you should rather extend the default type=20
> handler.=20
>
> Dridi=20
>
> On Tue, Oct 9, 2012 at 7:59 AM, grimmel97 <grim...@gmail.com <javascript:=
>>=20
> wrote:=20
> > I have progressed this a bit, there was an issue with my parameter=20
> passing=20
> > getting the NullPointerException, but now I have got it to pass to the=
=20
> > TypeHandler I am now getting an:=20
> >=20
> > Error updating database. Cause: java.lang.ClassCastException:=20
> > org.apache.commons.dbcp.PoolableConnection cannot be cast to=20
> > oracle.jdbc.OracleConnection=20
> >=20
> >=20
> > On Tuesday, 9 October 2012 14:59:20 UTC+11, grimmel97 wrote:=20
> >>=20
> >> I've tried to get my TypeHandler to work, but it just doesn't seem to=
=20
> be=20
> >> happy. So it is obvious that I am not not understanding this=20
> correctly. I=20
> >> have just written a single test case with a simple insert Oracle store=
d=20
> >> procedure that just takes a single collection as an IN parameter to tr=
y=20
> and=20
> >> get a working piece of code.=20
> >>=20
> >> The code gets to the setParameter mehod in my TypeHandler then fails.=
=20
> >>=20
> >> To be honest, I am not sure if I am understanding this correctly, but =
I=20
> >> thought the process was:=20
> >>=20
> >> 1. The Object parameter for setParameter would be the List<Pojo> objec=
t=20
> >> that I have already created elsewhere in my code.=20
> >> 2. I define a Map object as well as a List of these Map objects.=20
> >> 3. I assign the values of each Pojo in my List<Pojo> to a Map object=
=20
> and=20
> >> then add this Map object to the List of Map objects=20
> >> 4. I then create the Oracle array types as per your previous examples=
=20
> and=20
> >> write my List of Map objects to them.=20
> >>=20
> >> However it is not working and I have pasted my generic SQLMap,=20
> >> setParameter method of the customer TypeHandler and the trace log.=20
> Much=20
> >> appreciated if you could tell me what I am doing wrong.=20
> >>=20
> >> Thanks in advance!=20
> >>=20
> >> MyBatis SQLMap:=20
> >> <update id=3D"test" statementType=3D"CALLABLE">=20
> >> {CALL test(#{pojoList, mode=3DIN,=20
> >> typeHandler=3Dpackage.MyTypeHandler})=20
> >> </update>=20
> >>=20
> >> setParameter method of my TypeHandler:=20
> >> public void setParameter(PreparedStatement ps, int argNum, Object=20
> >> parameter, JdbcType jdbcType) throws SQLException {=20
> >>=20
> >> Connection conn;=20
> >> // list of maps=20
> >> LinkedHashMap<String,Object> pojoMap;=20
> >> List<LinkedHashMap<String,Object>> listOfPojoMaps;=20
> >>=20
> >> pojoMap =3D new LinkedHashMap<>();=20
> >> listOfPojoMaps =3D new ArrayList<>();=20
> >>=20
> >> // keys corresponding to the properties of the=20
> ORACLE_COLLECTION_T=20
> >>=20
> >> if(parameter !=3D null){=20
> >>=20
> >> for(Pojo e : (List<Pojo>)parameter){=20
> >> pojoMap.put("PROPERTY_1", e.getParameter1());=20
> >> pojoMap.put("PROPERTY_2", e.getParameter2());=20
> >> pojoMap.put("PROPERTY_3", e.getParameter3());=20
> >>=20
> >> listOfPojoMaps.add(pojoMap);=20
> >> }=20
> >> }=20
> >>=20
> >> STRUCT[] structArray =3D new STRUCT[listOfPojoMaps.size()];=20
> >> conn =3D ps.getConnection();=20
> >>=20
> >> StructDescriptor structDesc =3D=20
> >> StructDescriptor.createDescriptor("ORACLE_COLLECTION_RT", conn);=20
> >> ArrayDescriptor arrayDesc =3D=20
> >> ArrayDescriptor.createDescriptor("ORACLE_COLLECTION_T", conn);=20
> >>=20
> >> int valueCounter =3D 0;=20
> >>=20
> >> for (Map<String, Object> properties : listOfPojoMaps) {=20
> >>=20
> >> structArray[valueCounter] =3D new STRUCT(structDesc, conn, properties)=
;=20
> >> valueCounter++;=20
> >> }=20
> >>=20
> >> ARRAY arrayOfType =3D new ARRAY(arrayDesc, conn, structArray);=20
> >> ps.setArray(argNum, arrayOfType);=20
> >> }=20
> >>=20
> >> Log:=20
> >> SEVERE: org.mybatis.spring.MyBatisSystemException: nested exception is=
=20
> >> org.apache.ibatis.exceptions.PersistenceException:=20
> >> ### Error updating database. Cause: java.lang.NullPointerException=20
> >> ### The error may involve defaultParameterMap=20
> >> ### The error occurred while setting parameters=20
> >> ### SQL: {CALL test(?)=20
> >> ### Cause: java.lang.NullPointerException=20
> >> javax.faces.el.EvaluationException:=20
> >> org.mybatis.spring.MyBatisSystemException: nested exception is=20
> >> org.apache.ibatis.exceptions.PersistenceException:=20
> >> ### Error updating database. Cause: java.lang.NullPointerException=20
> >> ### The error may involve defaultParameterMap=20
> >> ### The error occurred while setting parameters=20
> >> ### SQL: {CALL test(?)=20
> >> ### Cause: java.lang.NullPointerException=20
> >>=20
> >>=20
> >> On Tuesday, 9 October 2012 02:03:48 UTC+11, AntPort wrote:=20
> >>>=20
> >>> I made a mistake: Line=20
> >>>=20
> #{returnValue,mode=3DOUT,jdbcTypeName=3DNUMERIC,javaType=3Djava.lang.Inte=
ger}=20
> must=20
> >>> be converted into=20
> >>> #{returnValue,mode=3DOUT,jdbcType=3DNUMERIC,javaType=3Djava.lang.Inte=
ger}=20
> >>>=20
> >>> Dana subota, 6. listopada 2012. 23:35:44 UTC+2, korisnik AntPort=20
> napisao=20
> >>> je:=20
> >>>>=20
> >>>> 1.Type handler must be defined for every parameter. Here is one=20
> example:=20
> >>>> if we have wrapperObject having properties listOfMaps1,2,3 (mentione=
d=20
> in my=20
> >>>> post above) and returnValue map snippet would look like this:=20
> >>>>=20
> >>>> <select id=3D"callMyProcedure" statementType=3D"CALLABLE"=20
> >>>> parameterType=3D"wrapperObject">=20
> >>>> call=20
> >>>> my_proc(#{listOfMaps1,mode=3DIN,typeHandler=3Dpackage.Param1TypeHand=
ler},=20
> >>>>=20
> >>>> #{listOfMaps2,mode=3DIN,typeHandler=3Dpackage.Param2TypeHandler},=20
> >>>>=20
> >>>> #{listOfMaps3,mode=3DIN,typeHandler=3Dpackage.Param3TypeHandler},=20
> >>>>=20
> >>>>=20
> #{returnValue,mode=3DOUT,jdbcTypeName=3DNUMERIC,javaType=3Djava.lang.Inte=
ger})=20
> >>>> </select>=20
> >>>> If you have multiple parameters, you have to specify type handler fo=
r=20
> >>>> every one of them.=20
> >>>>=20
> >>>> 2. No. Only if you use same type handler for IN and OUT parameters.=
=20
> >>>> Integer doesn't need specific type handler, just define OUT paramete=
r=20
> like=20
> >>>> in the example above.=20
> >>>>=20
> >>>> Dana petak, 5. listopada 2012. 16:30:47 UTC+2, korisnik grimmel97=20
> >>>> napisao je:=20
> >>>>>=20
> >>>>> Hi,=20
> >>>>>=20
> >>>>> Thank you again for your time in trying to explain this to me. I=
=20
> will=20
> >>>>> try to digest this and write the TypeHandler tomorrow. However for=
=20
> now I=20
> >>>>> have a few more questions.=20
> >>>>>=20
> >>>>> 1. If I have to create a TypeHandler for each of my parameters, the=
n=20
> in=20
> >>>>> my original example I would have 3 type handlers, say=20
> >>>>>=20
> >>>>> Param1TypeHandler=20
> >>>>>=20
> >>>>> Param2TypeHandler=20
> >>>>>=20
> >>>>> Param3TypeHandler=20
> >>>>>=20
> >>>>>=20
> >>>>> Can I specify more than one TypeHandler for the MyBatis SQLMap?=20
> >>>>>=20
> >>>>> <update id=3D"callOracleSP" statementType=3D"CALLABLE"=20
> >>>>> typeHandler=3D"Param1TypeHandler,Param2TypeHandler,Param3TypeHAndle=
r">=20
> >>>>> {CALL my_proc( #{param1, mode=3DIN},=20
> >>>>> #{param2, mode=3DIN},=20
> >>>>> #{param3, mode=3DIN}=20
> >>>>>=20
> >>>>> )=20
> >>>>> }=20
> >>>>> </update>=20
> >>>>>=20
> >>>>> 2. I am actually returning a single Integer value from an OUT=20
> parameter=20
> >>>>> in my procedure; Do I have to write an implementation for the=20
> getResult in=20
> >>>>> this case?=20
> >>>>>=20
> >>>>> 3. Yes, I am using the older Oracle jdbc driver, so will upgrade it=
=20
> in=20
> >>>>> my project and see how it goes.=20
> >>>>>=20
> >>>>> Thank you so much for your time. I will try and write a single typ=
e=20
> >>>>> handler tomorrow and try to get it to compile.=20
> >>>>>=20
> >>>>>=20
> >>>>> On Friday, 5 October 2012 16:09:11 UTC+10, AntPort wrote:=20
> >>>>>>=20
> >>>>>> 1. Yes, you have to implement TypeHandler<T> interface.=20
> >>>>>>=20
> >>>>>> public interface TypeHandler<T> {=20
> >>>>>>=20
> >>>>>> public void setParameter(PreparedStatement ps, int i, T=20
> parameter,=20
> >>>>>> JdbcType jdbcType)=20
> >>>>>> throws SQLException;=20
> >>>>>>=20
> >>>>>> public T getResult(ResultSet rs, String columnName)=20
> >>>>>> throws SQLException;=20
> >>>>>>=20
> >>>>>> public T getResult(CallableStatement cs, int columnIndex)=20
> >>>>>> throws SQLException;=20
> >>>>>>=20
> >>>>>> }=20
> >>>>>>=20
> >>>>>> If you are not returning anything from your procedure, you can=20
> leave=20
> >>>>>> two getResult methods empty.=20
> >>>>>>=20
> >>>>>> 2. No, you have to do something like this:=20
> >>>>>> If these are the POJO properties:=20
> >>>>>>=20
> >>>>>> IdReservation=20
> >>>>>> IdBranch=20
> >>>>>> IAgn=20
> >>>>>> NapAgn=20
> >>>>>>=20
> >>>>>> ...then you have to do something like this:=20
> >>>>>>=20
> >>>>>> List<Map<String,Object>> listOfPojoMaps =3D new=20
> >>>>>> ArrayList<Map<String,Object>>();=20
> >>>>>> Map<String,Object> pojoMap =3D new LinkedHashMap<String,Object>();=
=20
> >>>>>>=20
> >>>>>> // keys correspond to the properties of the I_RESERVATION_TYPE=20
> >>>>>>=20
> >>>>>> pojoMap.put("ID_RESERVATION",pojo.getIdReservation);=20
> >>>>>> pojoMap.put("ID_BRANCH",pojo.getIdReservation);=20
> >>>>>> pojoMap.put("ID_AGN",pojo.getIdReservation);=20
> >>>>>> pojoMap.put("NAP_AGN",pojo.getIdReservation)=20
> >>>>>>=20
> >>>>>> listOfPojoMaps.add(pojoMap);=20
> >>>>>>=20
> >>>>>> The listOfPojoMaps is the property you set as an input parameter i=
n=20
> >>>>>> your stored procedure call.=20
> >>>>>>=20
> >>>>>> 3. Every map from the listOfPojoMaps creates one STRUCT object.=20
> Since=20
> >>>>>> we have I_REZERVATION_ARRAY as an array of objects in the=20
> database,we have=20
> >>>>>> to create STRUCT[] array which we pass as a parameter to the=20
> >>>>>> prepairedStatement. If we didn't have that, we could send only one=
=20
> >>>>>> parameter/pojo to the database.=20
> >>>>>> Remember, you are not sending pojos to typehandler, just it's=20
> values=20
> >>>>>> wrapped in a list of maps.=20
> >>>>>>=20
> >>>>>> 4. In essence, yes, you will have to create typeHandler for your=
=20
> every=20
> >>>>>> type in the database, although you can do something like this:=20
> >>>>>>=20
> >>>>>> public class StoredProcedurePojo {=20
> >>>>>>=20
> >>>>>> private ArrayList<LinkedHashMap<String, Object>> listOfPojoMaps;=
=20
> >>>>>> private String typeName;=20
> >>>>>> private String arrayName;=20
> >>>>>>=20
> >>>>>> //getters / setters=20
> >>>>>>=20
> >>>>>> }=20
> >>>>>>=20
> >>>>>> You can send this wrapper as an input parameter to your typeHandle=
r=20
> >>>>>> but you have to modify the code snippet mentioned in my first post=
=20
> to:=20
> >>>>>>=20
> >>>>>> StoredProcedurePojo wrapperPojo =3D=20
> >>>>>> (StoredProcedurePojo) passedValue;=20
> >>>>>>=20
> >>>>>> List<LinkedHashMap<String,Object>> inParams =3D=20
> >>>>>> wrapperPojo.getListOfPojoMaps();=20
> >>>>>>=20
> >>>>>> STRUCT[] structArray =3D new STRUCT[inParams.size()];=20
> >>>>>> conn =3D ps.getConnection();=20
> >>>>>> StructDescriptor structDesc =3D StructDescriptor.createDescriptor(=
=20
> >>>>>> wrapperPojo.getTypeName(), conn);=20
> >>>>>> ArrayDescriptor arrayDesc =3D ArrayDescriptor.createDescriptor(=20
> >>>>>> wrapperPojo.getArrayName(), conn);=20
> >>>>>>=20
> >>>>>> 5. Regarding the compiler errors....Are you using=20
> >>>>>> oracle.sql.STRUCT.STRUCT(StructDescriptor arg0, Connection arg1,=
=20
> Map arg2) ?=20
> >>>>>>=20
> >>>>>> P.S.=20
> >>>>>>=20
> >>>>>> It is important that you use ojdbc6 oracle driver, ojdbc14 doesn't=
=20
> >>>>>> seem to work...=20
> >>>>>>=20
> >>>>>>=20
> >>>>>> I' m not sure if this way is the simplest or the best approach, bu=
t=20
> it=20
> >>>>>> works in our environment...=20
> >>>>>>=20
> >>>>>> Dana petak, 5. listopada 2012. 03:16:00 UTC+2, korisnik grimmel97=
=20
> >>>>>> napisao je:=20
> >>>>>>>=20
> >>>>>>> Thank you AntPort for taking the time to reply. Apologies for th=
e=20
> >>>>>>> additional questions, however if you haven't already guessed, I a=
m=20
> new to=20
> >>>>>>> this, so are you able to clarify for me:=20
> >>>>>>>=20
> >>>>>>> 1. When I create the TypeHandler, do I just implement the MyBatis=
=20
> >>>>>>> TypeHandler?=20
> >>>>>>>=20
> >>>>>>> 2. I can't get my head around the code-listing you have in the=20
> >>>>>>> TypeHandler. So say I have a pojo IReservation that matches your=
=20
> >>>>>>> I_RESERVATION_TYPE with the properties:=20
> >>>>>>>=20
> >>>>>>> IdReservation=20
> >>>>>>> IdBranch=20
> >>>>>>> IAgn=20
> >>>>>>> NapAgn=20
> >>>>>>>=20
> >>>>>>> and my application creates and populates a list of IReservation:=
=20
> >>>>>>> List<IReservation> and I want to pass the entire list as an=20
> Oracle, how do I=20
> >>>>>>> incorporate this into the TypeHandler code example you have=20
> provided?=20
> >>>>>>>=20
> >>>>>>> 3. Could you explain why the STRUCT[] array is required? Is that=
=20
> >>>>>>> because that is the type of array that Oracle supports?=20
> >>>>>>>=20
> >>>>>>> 4. Are you saying that I would have to create a TypeHandler for=
=20
> each=20
> >>>>>>> parameter in my example?=20
> >>>>>>>=20
> >>>>>>> Many thanks.=20
> >>>>>>>=20
> >>>>>>>=20
> >>>>>>>=20
> >>>>>>> On Wednesday, 3 October 2012 11:55:06 UTC+10, grimmel97 wrote:=20
> >>>>>>>>=20
> >>>>>>>> Hi,=20
> >>>>>>>>=20
> >>>>>>>> I have been googling this for a while and cannot seem to find an=
y=20
> >>>>>>>> real answers.=20
> >>>>>>>>=20
> >>>>>>>> I have an Oracle stored procedure that has a number of in=20
> parameters=20
> >>>>>>>> that have a type that is table of the table rowtype. So for=20
> example:=20
> >>>>>>>>=20
> >>>>>>>> Declared in the pacakge:=20
> >>>>>>>> TYPE param1_type_t IS TABLE OF table1%ROWTYPE;=20
> >>>>>>>> TYPE param2_type_t IS TABLE OF table2%ROWTYPE;=20
> >>>>>>>> TYPE param3_type_t IS TABLE OF table3%ROWTYPE;=20
> >>>>>>>>=20
> >>>>>>>> Procedure:=20
> >>>>>>>> PROCEDURE my_proc=20
> >>>>>>>> (=20
> >>>>>>>> parameter1 IN param1_type_t,=20
> >>>>>>>> parameter2 IN param2_type_t,=20
> >>>>>>>> parameter3 IN param3_type_t=20
> >>>>>>>> )=20
> >>>>>>>>=20
> >>>>>>>> On the java side, I have 3 corresponding POJO objects=20
> representing=20
> >>>>>>>> each of the parameters and the code creates a java List of each=
=20
> of the=20
> >>>>>>>> POJOs. Is it possible to call the Oracle procedure using MyBati=
s=20
> in this=20
> >>>>>>>> scenario?=20
> >>>>>>>>=20
> >>>>>>>> If so, how would the SQLMap be written? Or do I need to write=
=20
> more=20
> >>>>>>>> objects to cater for this scenario?=20
> >>>>>>>>=20
> >>>>>>>> Thanks in advance.=20
>
>
>
> --=20
> Dridi Boukelmoune=20
> D=C3=A9veloppeur/Formateur=20
>
> GSM : +33 (0)6 17 91 14 23=20
>
------=_Part_1644_31137983.1349781226329
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: quoted-printable
Hi,<div><br></div><div>Yes my TypeHandler currently directly implements the=
interface. Thank you for your advice. I will look to take that=
on board once I can get a simple round-trip to the database working.</div>=
<div><br></div><div>I have since gotten rid of the OracleConnection casting=
error, and the statement is getting executed against the database, however=
I am now getting an Oracle error with either the wrong type or number of p=
arameters. Although I suspect it is the first case given my test proc=
edure only has one parameter and I am passing one!</div><div><br><br>On Tue=
sday, 9 October 2012 18:03:58 UTC+11, Dridi Boukelmoune wrote:<blockquote =
class=3D"gmail_quote" style=3D"margin: 0;margin-left: 0.8ex;border-left: 1p=
x #ccc solid;padding-left: 1ex;">Hi,
<br>
<br>Does your type handler directly implement the interface ?
<br>If you want to handle nulls you should rather extend the default type h=
andler.
<br>
<br>Dridi
<br>
<br>On Tue, Oct 9, 2012 at 7:59 AM, grimmel97 <<a href=3D"javascript:" t=
arget=3D"_blank" gdf-obfuscated-mailto=3D"OFGZ7B_JnaEJ">grim...@gmail.com</=
a>> wrote:
<br>> I have progressed this a bit, there was an issue with my parameter=
passing
<br>> getting the NullPointerException, but now I have got it to pass to=
the
<br>> TypeHandler I am now getting an:
<br>>
<br>> Error updating database. Cause: java.lang.ClassCastException=
:
<br>> org.apache.commons.dbcp.<wbr>PoolableConnection cannot be cast to
<br>> oracle.jdbc.OracleConnection
<br>>
<br>>
<br>> On Tuesday, 9 October 2012 14:59:20 UTC+11, grimmel97 wrote:
<br>>>
<br>>> I've tried to get my TypeHandler to work, but it just doesn't =
seem to be
<br>>> happy. So it is obvious that I am not not understanding =
this correctly. I
<br>>> have just written a single test case with a simple insert Orac=
le stored
<br>>> procedure that just takes a single collection as an IN paramet=
er to try and
<br>>> get a working piece of code.
<br>>>
<br>>> The code gets to the setParameter mehod in my TypeHandler then=
fails.
<br>>>
<br>>> To be honest, I am not sure if I am understanding this correct=
ly, but I
<br>>> thought the process was:
<br>>>
<br>>> 1. The Object parameter for setParameter would be the List<=
Pojo> object
<br>>> that I have already created elsewhere in my code.
<br>>> 2. I define a Map object as well as a List of these Map object=
s.
<br>>> 3. I assign the values of each Pojo in my List<Pojo> to =
a Map object and
<br>>> then add this Map object to the List of Map objects
<br>>> 4. I then create the Oracle array types as per your previous e=
xamples and
<br>>> write my List of Map objects to them.
<br>>>
<br>>> However it is not working and I have pasted my generic SQLMap,
<br>>> setParameter method of the customer TypeHandler and the trace =
log. Much
<br>>> appreciated if you could tell me what I am doing wrong.
<br>>>
<br>>> Thanks in advance!
<br>>>
<br>>> MyBatis SQLMap:
<br>>> <update id=3D"test" statementType=3D"CALLABLE=
">
<br>>> {CALL test(#{pojoList, mode=3DIN,
<br>>> typeHandler=3Dpackage.<wbr>MyTypeHandler})
<br>>> </update>
<br>>>
<br>>> setParameter method of my TypeHandler:
<br>>> public void setParameter(PreparedStatement ps, int argNum, Obj=
ect
<br>>> parameter, JdbcType jdbcType) throws SQLException {
<br>>>
<br>>> Connection conn;
<br>>> // list of maps
<br>>> LinkedHashMap<String,Object>=
pojoMap;
<br>>> List<LinkedHashMap<String,<w=
br>Object>> listOfPojoMaps;
<br>>>
<br>>> pojoMap =3D new LinkedHashMap<&=
gt;();
<br>>> listOfPojoMaps =3D new ArrayList&l=
t;>();
<br>>>
<br>>> // keys corresponding to the prope=
rties of the ORACLE_COLLECTION_T
<br>>>
<br>>> if(parameter !=3D null){
<br>>>
<br>>> for(Pojo e : (List&l=
t;Pojo>)parameter){
<br>>> pojoMa=
p.put("PROPERTY_1", e.getParameter1());
<br>>> pojoMa=
p.put("PROPERTY_2", e.getParameter2());
<br>>> pojoMa=
p.put("PROPERTY_3", e.getParameter3());
<br>>>
<br>>> listOf=
PojoMaps.add(pojoMap);
<br>>> }
<br>>> }
<br>>>
<br>>> STRUCT[] structArray =3D new STRUCT[listOfPojoMaps.size()];
<br>>> conn =3D ps.getConnection();
<br>>>
<br>>> StructDescriptor structDesc =3D
<br>>> StructDescriptor.<wbr>createDescriptor("ORACLE_<wbr>COLLECTION=
_RT", conn);
<br>>> ArrayDescriptor arrayDesc =3D
<br>>> ArrayDescriptor.<wbr>createDescriptor("ORACLE_<wbr>COLLECTION_=
T", conn);
<br>>>
<br>>> int valueCounter =3D 0;
<br>>>
<br>>> for (Map<String, Object> properties : listOfPojoMaps) {
<br>>>
<br>>> structArray[valueCounter] =3D new STRUCT(structDesc, conn, pro=
perties);
<br>>> valueCounter++;
<br>>> }
<br>>>
<br>>> ARRAY arrayOfType =3D new ARRAY(arrayDesc, conn, structArray);
<br>>> ps.setArray(argNum, arrayOfType);
<br>>> }
<br>>>
<br>>> Log:
<br>>> SEVERE: org.mybatis.spring.<wbr>MyBatisSystemException: nested=
exception is
<br>>> org.apache.ibatis.exceptions.<wbr>PersistenceException:
<br>>> ### Error updating database. Cause: java.lang.NullPointe=
rException
<br>>> ### The error may involve defaultParameterMap
<br>>> ### The error occurred while setting parameters
<br>>> ### SQL: {CALL test(?)
<br>>> ### Cause: java.lang.NullPointerException
<br>>> javax.faces.el.<wbr>EvaluationException:
<br>>> org.mybatis.spring.<wbr>MyBatisSystemException: nested excepti=
on is
<br>>> org.apache.ibatis.exceptions.<wbr>PersistenceException:
<br>>> ### Error updating database. Cause: java.lang.NullPointe=
rException
<br>>> ### The error may involve defaultParameterMap
<br>>> ### The error occurred while setting parameters
<br>>> ### SQL: {CALL test(?)
<br>>> ### Cause: java.lang.NullPointerException
<br>>>
<br>>>
<br>>> On Tuesday, 9 October 2012 02:03:48 UTC+11, AntPort wrote:
<br>>>>
<br>>>> I made a mistake: Line
<br>>>> #{returnValue,mode=3DOUT,<wbr>jdbcTypeName=3DNUMERIC,javaT=
ype=3D<wbr>java.lang.Integer} must
<br>>>> be converted into
<br>>>> #{returnValue,mode=3DOUT,<wbr>jdbcType=3DNUMERIC,javaType=
=3D<wbr>java.lang.Integer}
<br>>>>
<br>>>> Dana subota, 6. listopada 2012. 23:35:44 UTC+2, korisnik A=
ntPort napisao
<br>>>> je:
<br>>>>>
<br>>>>> 1.Type handler must be defined for every parameter. He=
re is one example:
<br>>>>> if we have wrapperObject having properties listOfMaps1=
,2,3 (mentioned in my
<br>>>>> post above) and returnValue map snippet would lo=
ok like this:
<br>>>>>
<br>>>>> <select id=3D"callMyProcedure" statementType=3D"CAL=
LABLE"
<br>>>>> parameterType=3D"wrapperObject">
<br>>>>> call
<br>>>>> my_proc(#{listOfMaps1,mode=3DIN,<wbr>typeHandler=3Dpac=
kage.<wbr>Param1TypeHandler},
<br>>>>>
<br>>>>> #{listOfMaps2,mode=3DIN,<wbr>typeHandler=3Dpackage.<wb=
r>Param2TypeHandler},
<br>>>>>
<br>>>>> #{listOfMaps3,mode=3DIN,<wbr>typeHandler=3Dpackage.<wb=
r>Param3TypeHandler},
<br>>>>>
<br>>>>> #{returnValue,mode=3DOUT,<wbr>jdbcTypeName=3DNUMERIC,j=
avaType=3D<wbr>java.lang.Integer})
<br>>>>> </select>
<br>>>>> If you have multiple parameters, you have to specify t=
ype handler for
<br>>>>> every one of them.
<br>>>>>
<br>>>>> 2. No. Only if you use same type handler for IN and OU=
T parameters.
<br>>>>> Integer doesn't need specific type handler, just defin=
e OUT parameter like
<br>>>>> in the example above.
<br>>>>>
<br>>>>> Dana petak, 5. listopada 2012. 16:30:47 UTC+2, korisni=
k grimmel97
<br>>>>> napisao je:
<br>>>>>>
<br>>>>>> Hi,
<br>>>>>>
<br>>>>>> Thank you again for your time in trying to explain=
this to me. I will
<br>>>>>> try to digest this and write the TypeHandler tomor=
row. However for now I
<br>>>>>> have a few more questions.
<br>>>>>>
<br>>>>>> 1. If I have to create a TypeHandler for each of m=
y parameters, then in
<br>>>>>> my original example I would have 3 type handlers, =
say
<br>>>>>>
<br>>>>>> Param1TypeHandler
<br>>>>>>
<br>>>>>> Param2TypeHandler
<br>>>>>>
<br>>>>>> Param3TypeHandler
<br>>>>>>
<br>>>>>>
<br>>>>>> Can I specify more than one TypeHandler for the My=
Batis SQLMap?
<br>>>>>>
<br>>>>>> <update id=3D"callOracleSP" statementType=3D"CA=
LLABLE"
<br>>>>>> typeHandler=3D"<wbr>Param1TypeHandler,<wbr>Param2T=
ypeHandler,<wbr>Param3TypeHAndler">
<br>>>>>> {CALL my_proc( #{param1, mode=3DIN},
<br>>>>>> &=
nbsp; #{param2, mode=3DIN},
<br>>>>>> &=
nbsp; #{param3, mode=3DIN}
<br>>>>>>
<br>>>>>> &=
nbsp; )
<br>>>>>> }
<br>>>>>> </update>
<br>>>>>>
<br>>>>>> 2. I am actually returning a single Integer value =
from an OUT parameter
<br>>>>>> in my procedure; Do I have to write an implementat=
ion for the getResult in
<br>>>>>> this case?
<br>>>>>>
<br>>>>>> 3. Yes, I am using the older Oracle jdbc driver, s=
o will upgrade it in
<br>>>>>> my project and see how it goes.
<br>>>>>>
<br>>>>>> Thank you so much for your time. I will try =
and write a single type
<br>>>>>> handler tomorrow and try to get it to compile.
<br>>>>>>
<br>>>>>>
<br>>>>>> On Friday, 5 October 2012 16:09:11 UTC+10, AntPort=
wrote:
<br>>>>>>>
<br>>>>>>> 1. Yes, you have to implement TypeHandler<T=
> interface.
<br>>>>>>>
<br>>>>>>> public interface TypeHandler<T> {
<br>>>>>>>
<br>>>>>>> public void setParameter(PreparedStatem=
ent ps, int i, T parameter,
<br>>>>>>> JdbcType jdbcType)
<br>>>>>>> throws SQLException;
<br>>>>>>>
<br>>>>>>> public T getResult(ResultSet rs, String=
columnName)
<br>>>>>>> throws SQLException;
<br>>>>>>>
<br>>>>>>> public T getResult(CallableStatement cs=
, int columnIndex)
<br>>>>>>> throws SQLException;
<br>>>>>>>
<br>>>>>>> }
<br>>>>>>>
<br>>>>>>> If you are not returning anything from your pr=
ocedure, you can leave
<br>>>>>>> two getResult methods empty.
<br>>>>>>>
<br>>>>>>> 2. No, you have to do something like this:
<br>>>>>>> If these are the POJO properties:
<br>>>>>>>
<br>>>>>>> IdReservation
<br>>>>>>> IdBranch
<br>>>>>>> IAgn
<br>>>>>>> NapAgn
<br>>>>>>>
<br>>>>>>> ...then you have to do something like this:
<br>>>>>>>
<br>>>>>>> List<Map<String,Object>> listOfPoj=
oMaps =3D new
<br>>>>>>> ArrayList<Map<String,Object>>(<wbr=
>);
<br>>>>>>> Map<String,Object> pojoMap =3D new Linke=
dHashMap<String,Object>()<wbr>;
<br>>>>>>>
<br>>>>>>> // keys correspond to the properties of the I_=
RESERVATION_TYPE
<br>>>>>>>
<br>>>>>>> pojoMap.put("ID_RESERVATION",<wbr>pojo.getIdRe=
servation);
<br>>>>>>> pojoMap.put("ID_BRANCH",pojo.<wbr>getIdReserva=
tion);
<br>>>>>>> pojoMap.put("ID_AGN",pojo.<wbr>getIdReservatio=
n);
<br>>>>>>> pojoMap.put("NAP_AGN",pojo.<wbr>getIdReservati=
on)
<br>>>>>>>
<br>>>>>>> listOfPojoMaps.add(pojoMap);
<br>>>>>>>
<br>>>>>>> The listOfPojoMaps is the property you set as =
an input parameter in
<br>>>>>>> your stored procedure call.
<br>>>>>>>
<br>>>>>>> 3. Every map from the listOfPojoMaps creates o=
ne STRUCT object. Since
<br>>>>>>> we have I_REZERVATION_ARRAY as an array of obj=
ects in the database,we have
<br>>>>>>> to create STRUCT[] array which we pass as a pa=
rameter to the
<br>>>>>>> prepairedStatement. If we didn't have that, we=
could send only one
<br>>>>>>> parameter/pojo to the database.
<br>>>>>>> Remember, you are not sending pojos to typehan=
dler, just it's values
<br>>>>>>> wrapped in a list of maps.
<br>>>>>>>
<br>>>>>>> 4. In essence, yes, you will have to create ty=
peHandler for your every
<br>>>>>>> type in the database, although you can do some=
thing like this:
<br>>>>>>>
<br>>>>>>> public class StoredProcedurePojo {
<br>>>>>>>
<br>>>>>>> private ArrayList<LinkedHashMap<<wbr>Str=
ing, Object>> listOfPojoMaps;
<br>>>>>>> private String typeName;
<br>>>>>>> private String arrayName;
<br>>>>>>>
<br>>>>>>> //getters / setter=
s
<br>>>>>>>
<br>>>>>>> }
<br>>>>>>>
<br>>>>>>> You can send this wrapper as an input paramete=
r to your typeHandler
<br>>>>>>> but you have to modify the code snippet mentio=
ned in my first post to:
<br>>>>>>>
<br>>>>>>> &nbs=
p; StoredProcedurePojo wrapperPojo =3D
<br>>>>>>> (StoredProcedurePojo) passedValue;
<br>>>>>>>
<br>>>>>>> List<LinkedHashMap<String,<wbr>Object>=
;> inParams =3D
<br>>>>>>> wrapperPojo.getListOfPojoMaps(<wbr>);
<br>>>>>>>
<br>>>>>>> STRUCT[] structArray =3D new STRUCT[inParams.s=
ize()];
<br>>>>>>> conn =3D ps.getConnection();
<br>>>>>>> StructDescriptor structDesc =3D StructDescript=
or.<wbr>createDescriptor(
<br>>>>>>> wrapperPojo.getTypeName(), conn);
<br>>>>>>> ArrayDescriptor arrayDesc =3D ArrayDescriptor.=
<wbr>createDescriptor(
<br>>>>>>> wrapperPojo.getArrayName(), conn);
<br>>>>>>>
<br>>>>>>> 5. Regarding the compiler errors....Are you us=
ing
<br>>>>>>> oracle.sql.STRUCT.STRUCT(<wbr>StructDescriptor=
arg0, Connection arg1, Map arg2) ?
<br>>>>>>>
<br>>>>>>> P.S.
<br>>>>>>>
<br>>>>>>> It is important that you use ojdbc6 oracle dri=
ver, ojdbc14 doesn't
<br>>>>>>> seem to work...
<br>>>>>>>
<br>>>>>>>
<br>>>>>>> I' m not sure if this way is the simplest or t=
he best approach, but it
<br>>>>>>> works in our environment...
<br>>>>>>>
<br>>>>>>> Dana petak, 5. listopada 2012. 03:16:00 UTC+2,=
korisnik grimmel97
<br>>>>>>> napisao je:
<br>>>>>>>>
<br>>>>>>>> Thank you AntPort for taking the time to r=
eply. Apologies for the
<br>>>>>>>> additional questions, however if you haven=
't already guessed, I am new to
<br>>>>>>>> this, so are you able to clarify for me:
<br>>>>>>>>
<br>>>>>>>> 1. When I create the TypeHandler, do I jus=
t implement the MyBatis
<br>>>>>>>> TypeHandler?
<br>>>>>>>>
<br>>>>>>>> 2. I can't get my head around the code-lis=
ting you have in the
<br>>>>>>>> TypeHandler. So say I have a pojo IR=
eservation that matches your
<br>>>>>>>> I_RESERVATION_TYPE with the properties:
<br>>>>>>>>
<br>>>>>>>> IdReservation
<br>>>>>>>> IdBranch
<br>>>>>>>> IAgn
<br>>>>>>>> NapAgn
<br>>>>>>>>
<br>>>>>>>> and my application creates and populates a=
list of IReservation:
<br>>>>>>>> List<IReservation> and I want to pas=
s the entire list as an Oracle, how do I
<br>>>>>>>> incorporate this into the TypeHandler code=
example you have provided?
<br>>>>>>>>
<br>>>>>>>> 3. Could you explain why the STRUCT[] arra=
y is required? Is that
<br>>>>>>>> because that is the type of array that Ora=
cle supports?
<br>>>>>>>>
<br>>>>>>>> 4. Are you saying that I would have to cre=
ate a TypeHandler for each
<br>>>>>>>> parameter in my example?
<br>>>>>>>>
<br>>>>>>>> Many thanks.
<br>>>>>>>>
<br>>>>>>>>
<br>>>>>>>>
<br>>>>>>>> On Wednesday, 3 October 2012 11:55:06 UTC+=
10, grimmel97 wrote:
<br>>>>>>>>>
<br>>>>>>>>> Hi,
<br>>>>>>>>>
<br>>>>>>>>> I have been googling this for a while =
and cannot seem to find any
<br>>>>>>>>> real answers.
<br>>>>>>>>>
<br>>>>>>>>> I have an Oracle stored procedure that=
has a number of in parameters
<br>>>>>>>>> that have a type that is table of the =
table rowtype. So for example:
<br>>>>>>>>>
<br>>>>>>>>> Declared in the pacakge:
<br>>>>>>>>> TYPE param1_type_t IS TABLE OF table1%=
ROWTYPE;
<br>>>>>>>>> TYPE param2_type_t IS TABLE OF table2%=
ROWTYPE;
<br>>>>>>>>> TYPE param3_type_t IS TABLE OF table3%=
ROWTYPE;
<br>>>>>>>>>
<br>>>>>>>>> Procedure:
<br>>>>>>>>> PROCEDURE my_proc
<br>>>>>>>>> (
<br>>>>>>>>> parameter1 I=
N param1_type_t,
<br>>>>>>>>> parameter2 I=
N param2_type_t,
<br>>>>>>>>> parameter3 I=
N param3_type_t
<br>>>>>>>>> )
<br>>>>>>>>>
<br>>>>>>>>> On the java side, I have 3 correspondi=
ng POJO objects representing
<br>>>>>>>>> each of the parameters and the code cr=
eates a java List of each of the
<br>>>>>>>>> POJOs. Is it possible to call th=
e Oracle procedure using MyBatis in this
<br>>>>>>>>> scenario?
<br>>>>>>>>>
<br>>>>>>>>> If so, how would the SQLMap be written=
? Or do I need to write more
<br>>>>>>>>> objects to cater for this scenario?
<br>>>>>>>>>
<br>>>>>>>>> Thanks in advance.
<br>
<br>
<br>
<br>--=20
<br>Dridi Boukelmoune
<br>D=C3=A9veloppeur/Formateur
<br>
<br>GSM : +33 (0)6 17 91 14 23
<br></blockquote></div>
------=_Part_1644_31137983.1349781226329--
------=_Part_1643_20405630.1349781226329--
Message from discussion How to Pass Java List of POJO to Oracle Stored Procedure using MyBatis?
| Create a group - Google Groups - Google Home - Terms of Service - Privacy Policy |
| ©2013 Google |