I have been googling this for a while and cannot seem to find any real answers.
I have an Oracle stored procedure that has a number of in parameters that have a type that is table of the table rowtype. So for example:
*Declared in the pacakge:* TYPE param1_type_t IS TABLE OF table1%ROWTYPE; TYPE param2_type_t IS TABLE OF table2%ROWTYPE; TYPE param3_type_t IS TABLE OF table3%ROWTYPE;
*Procedure:* PROCEDURE my_proc ( parameter1 IN param1_type_t, parameter2 IN param2_type_t, parameter3 IN param3_type_t )
On the java side, I have 3 corresponding POJO objects representing each of the parameters and the code creates a java List of each of the POJOs. Is it possible to call the Oracle procedure using MyBatis in this scenario?
If so, how would the SQLMap be written? Or do I need to write more objects to cater for this scenario?
I don't know about table of records, but you can send array of types as an input parameter, but you must create specific typeHandler. That goes something like this (for Oracle):
create or replace TYPE "I_REZERVATION_TYPE" AS OBJECT ( ID_REZERVATION NUMBER, ID_BRANCH NUMBER, I_AGN VARCHAR2 (1 CHAR), NAP_AGN VARCHAR2 (100 CHAR), CONSTRUCTOR FUNCTION I_REZERVATION_TYPE RETURN SELF AS RESULT);
create or replace TYPE "I_REZERVATION_ARRAY" AS TABLE OF I_REZERVATION_TYPE;
public void setParameter(PreparedStatement ps, int argNum, Object passedValue, JdbcType jdbcType) throws SQLException {
Connection conn;
// list of maps // every map contains type field names as a key and field values as a value List<LinkedHashMap<String,Object>> inParams = (List<LinkedHashMap<String,Object>>) passedValue;
int valueCounter = 0; for (Map<String, Object> properties : inCollection) {
structArray[valueCounter] = new STRUCT(structDesc, conn, properties); valueCounter++;
}
ARRAY arrayOfType = new ARRAY(arrayDesc, conn, structArray); ps.setArray(argNum, arrayOfType); }
Map input parameter:procedure_call(#{listOfMaps,mode=IN,typeHandler=yourTypeHandler}) The downside of this approach is that you have to map all your properties from pojos to the keys in map. Key values must correspond to database type properties. Also you will have to create special typehandler for every type from the database (or you can wrap the List<Map>, arrayName, typeName in another pojo and use one generic typehandler for all calls).
Dana srijeda, 3. listopada 2012. 03:55:06 UTC+2, korisnik grimmel97 napisao je:
> I have been googling this for a while and cannot seem to find any real > answers.
> I have an Oracle stored procedure that has a number of in parameters that > have a type that is table of the table rowtype. So for example:
> *Declared in the pacakge:* > TYPE param1_type_t IS TABLE OF table1%ROWTYPE; > TYPE param2_type_t IS TABLE OF table2%ROWTYPE; > TYPE param3_type_t IS TABLE OF table3%ROWTYPE;
> *Procedure:* > PROCEDURE my_proc > ( > parameter1 IN param1_type_t, > parameter2 IN param2_type_t, > parameter3 IN param3_type_t > )
> On the java side, I have 3 corresponding POJO objects representing each of > the parameters and the code creates a java List of each of the POJOs. Is > it possible to call the Oracle procedure using MyBatis in this scenario?
> If so, how would the SQLMap be written? Or do I need to write more > objects to cater for this scenario?
Thank you AntPort for taking the time to reply. Apologies for the additional questions, however if you haven't already guessed, I am new to this, so are you able to clarify for me:
1. When I create the TypeHandler, do I just implement the MyBatis TypeHandler?
2. I can't get my head around the code-listing you have in the TypeHandler. So say I have a pojo IReservation that matches your I_RESERVATION_TYPE with the properties:
IdReservation IdBranch IAgn NapAgn
and my application creates and populates a list of IReservation: List<IReservation> and I want to pass the entire list as an Oracle, how do I incorporate this into the TypeHandler code example you have provided?
3. Could you explain why the STRUCT[] array is required? Is that because that is the type of array that Oracle supports?
4. Are you saying that I would have to create a TypeHandler for each parameter in my example?
On Wednesday, 3 October 2012 11:55:06 UTC+10, grimmel97 wrote:
> Hi,
> I have been googling this for a while and cannot seem to find any real > answers.
> I have an Oracle stored procedure that has a number of in parameters that > have a type that is table of the table rowtype. So for example:
> *Declared in the pacakge:* > TYPE param1_type_t IS TABLE OF table1%ROWTYPE; > TYPE param2_type_t IS TABLE OF table2%ROWTYPE; > TYPE param3_type_t IS TABLE OF table3%ROWTYPE;
> *Procedure:* > PROCEDURE my_proc > ( > parameter1 IN param1_type_t, > parameter2 IN param2_type_t, > parameter3 IN param3_type_t > )
> On the java side, I have 3 corresponding POJO objects representing each of > the parameters and the code creates a java List of each of the POJOs. Is > it possible to call the Oracle procedure using MyBatis in this scenario?
> If so, how would the SQLMap be written? Or do I need to write more > objects to cater for this scenario?
structArray[valueCounter] = new STRUCT(structDesc, conn, properties); is causing me a compilation error as it says there is no suitable constructor that matches this.
On Friday, 5 October 2012 11:16:00 UTC+10, grimmel97 wrote:
> Thank you AntPort for taking the time to reply. Apologies for the > additional questions, however if you haven't already guessed, I am new to > this, so are you able to clarify for me:
> 1. When I create the TypeHandler, do I just implement the MyBatis > TypeHandler?
> 2. I can't get my head around the code-listing you have in the > TypeHandler. So say I have a pojo IReservation that matches your > I_RESERVATION_TYPE with the properties:
> IdReservation > IdBranch > IAgn > NapAgn
> and my application creates and populates a list of IReservation: > List<IReservation> and I want to pass the entire list as an Oracle, how do > I incorporate this into the TypeHandler code example you have provided?
> 3. Could you explain why the STRUCT[] array is required? Is that because > that is the type of array that Oracle supports?
> 4. Are you saying that I would have to create a TypeHandler for each > parameter in my example?
> Many thanks.
> On Wednesday, 3 October 2012 11:55:06 UTC+10, grimmel97 wrote:
>> Hi,
>> I have been googling this for a while and cannot seem to find any real >> answers.
>> I have an Oracle stored procedure that has a number of in parameters that >> have a type that is table of the table rowtype. So for example:
>> *Declared in the pacakge:* >> TYPE param1_type_t IS TABLE OF table1%ROWTYPE; >> TYPE param2_type_t IS TABLE OF table2%ROWTYPE; >> TYPE param3_type_t IS TABLE OF table3%ROWTYPE;
>> *Procedure:* >> PROCEDURE my_proc >> ( >> parameter1 IN param1_type_t, >> parameter2 IN param2_type_t, >> parameter3 IN param3_type_t >> )
>> On the java side, I have 3 corresponding POJO objects representing each >> of the parameters and the code creates a java List of each of the POJOs. >> Is it possible to call the Oracle procedure using MyBatis in this scenario?
>> If so, how would the SQLMap be written? Or do I need to write more >> objects to cater for this scenario?
The listOfPojoMaps is the property you set as an input parameter in your stored procedure call.
3. Every map from the listOfPojoMaps creates one STRUCT object. Since we have I_REZERVATION_ARRAY as an array of objects in the database,we have to create STRUCT[] array which we pass as a parameter to the prepairedStatement. If we didn't have that, we could send only one parameter/pojo to the database. Remember, you are not sending pojos to typehandler, just it's values wrapped in a list of maps.
4. In essence, yes, you will have to create typeHandler for your every type in the database, although you can do something like this:
5. Regarding the compiler errors....Are you using oracle.sql.STRUCT<eclipse-javadoc:%E2%98%82=java-essential/%5C/home%5C/pron %5C/.m2%5C/repository%5C/com%5C/oracle%5C/ojdbc6%5C/11.1.0.7.0%5C/ojdbc6-11 .1.0.7.0.jar%3Coracle.sql(STRUCT.class%E2%98%83STRUCT> .STRUCT(StructDescriptor<eclipse-javadoc:%E2%98%82=java-essential/%5C/home% 5C/pron%5C/.m2%5C/repository%5C/com%5C/oracle%5C/ojdbc6%5C/11.1.0.7.0%5C/oj dbc6-11.1.0.7.0.jar%3Coracle.sql(STRUCT.class%E2%98%83STRUCT~STRUCT~Loracle .sql.StructDescriptor;~Ljava.sql.Connection;~Ljava.util.Map;%E2%98%82Struct Descriptor> arg0, Connection<eclipse-javadoc:%E2%98%82=java-essential/%5C/home%5C/pron%5C/.m2 %5C/repository%5C/com%5C/oracle%5C/ojdbc6%5C/11.1.0.7.0%5C/ojdbc6-11.1.0.7. 0.jar%3Coracle.sql(STRUCT.class%E2%98%83STRUCT~STRUCT~Loracle.sql.StructDes criptor;~Ljava.sql.Connection;~Ljava.util.Map;%E2%98%82Connection> arg1, Map<eclipse-javadoc:%E2%98%82=java-essential/%5C/home%5C/pron%5C/.m2%5C/rep ository%5C/com%5C/oracle%5C/ojdbc6%5C/11.1.0.7.0%5C/ojdbc6-11.1.0.7.0.jar%3 Coracle.sql(STRUCT.class%E2%98%83STRUCT~STRUCT~Loracle.sql.StructDescriptor ;~Ljava.sql.Connection;~Ljava.util.Map;%E2%98%82Map> arg2) ?
P.S.
*It is important that you use ojdbc6 oracle driver, ojdbc14 doesn't seem to work...* * * * * I' m not sure if this way is the simplest or the best approach, but it works in our environment...* *
Dana petak, 5. listopada 2012. 03:16:00 UTC+2, korisnik grimmel97 napisao je:
> Thank you AntPort for taking the time to reply. Apologies for the > additional questions, however if you haven't already guessed, I am new to > this, so are you able to clarify for me:
> 1. When I create the TypeHandler, do I just implement the MyBatis > TypeHandler?
> 2. I can't get my head around the code-listing you have in the > TypeHandler. So say I have a pojo IReservation that matches your > I_RESERVATION_TYPE with the properties:
> IdReservation > IdBranch > IAgn > NapAgn
> and my application creates and populates a list of IReservation: > List<IReservation> and I want to pass the entire list as an Oracle, how do > I incorporate this into the TypeHandler code example you have provided?
> 3. Could you explain why the STRUCT[] array is required? Is that because > that is the type of array that Oracle supports?
> 4. Are you saying that I would have to create a TypeHandler for each > parameter in my example?
> Many thanks.
> On Wednesday, 3 October 2012 11:55:06 UTC+10, grimmel97 wrote:
>> Hi,
>> I have been googling this for a while and cannot seem to find any real >> answers.
>> I have an Oracle stored procedure that has a number of in parameters that >> have a type that is table of the table rowtype. So for example:
>> *Declared in the pacakge:* >> TYPE param1_type_t IS TABLE OF table1%ROWTYPE; >> TYPE param2_type_t IS TABLE OF table2%ROWTYPE; >> TYPE param3_type_t IS TABLE OF table3%ROWTYPE;
>> *Procedure:* >> PROCEDURE my_proc >> ( >> parameter1 IN param1_type_t, >> parameter2 IN param2_type_t, >> parameter3 IN param3_type_t >> )
>> On the java side, I have 3 corresponding POJO objects representing each >> of the parameters and the code creates a java List of each of the POJOs. >> Is it possible to call the Oracle procedure using MyBatis in this scenario?
>> If so, how would the SQLMap be written? Or do I need to write more >> objects to cater for this scenario?
Thank you again for your time in trying to explain this to me. I will try to digest this and write the TypeHandler tomorrow. However for now I have a few more questions.
1. If I have to create a TypeHandler for each of my parameters, then in my original example I would have 3 type handlers, say
Param1TypeHandler
Param2TypeHandler
Param3TypeHandler
Can I specify more than one TypeHandler for the MyBatis SQLMap?
2. I am actually returning a single Integer value from an OUT parameter in my procedure; Do I have to write an implementation for the getResult in this case?
3. Yes, I am using the older Oracle jdbc driver, so will upgrade it in my project and see how it goes.
Thank you so much for your time. I will try and write a single type handler tomorrow and try to get it to compile.
> The listOfPojoMaps is the property you set as an input parameter in your > stored procedure call.
> 3. Every map from the listOfPojoMaps creates one STRUCT object. Since we > have I_REZERVATION_ARRAY as an array of objects in the database,we have > to create STRUCT[] array which we pass as a parameter to the > prepairedStatement. If we didn't have that, we could send only one > parameter/pojo to the database. > Remember, you are not sending pojos to typehandler, just it's values > wrapped in a list of maps.
> 4. In essence, yes, you will have to create typeHandler for your every > type in the database, although you can do something like this:
> 5. Regarding the compiler errors....Are you using oracle.sql.STRUCT > .STRUCT(StructDescriptor arg0, Connection arg1, Map arg2) ?
> P.S.
> *It is important that you use ojdbc6 oracle driver, ojdbc14 doesn't seem > to work...* > * > * > * > * > I' m not sure if this way is the simplest or the best approach, but it > works in our environment...* > *
> Dana petak, 5. listopada 2012. 03:16:00 UTC+2, korisnik grimmel97 napisao > je:
>> Thank you AntPort for taking the time to reply. Apologies for the >> additional questions, however if you haven't already guessed, I am new to >> this, so are you able to clarify for me:
>> 1. When I create the TypeHandler, do I just implement the MyBatis >> TypeHandler?
>> 2. I can't get my head around the code-listing you have in the >> TypeHandler. So say I have a pojo IReservation that matches your >> I_RESERVATION_TYPE with the properties:
>> IdReservation >> IdBranch >> IAgn >> NapAgn
>> and my application creates and populates a list of IReservation: >> List<IReservation> and I want to pass the entire list as an Oracle, how do >> I incorporate this into the TypeHandler code example you have provided?
>> 3. Could you explain why the STRUCT[] array is required? Is that because >> that is the type of array that Oracle supports?
>> 4. Are you saying that I would have to create a TypeHandler for each >> parameter in my example?
>> Many thanks.
>> On Wednesday, 3 October 2012 11:55:06 UTC+10, grimmel97 wrote:
>>> Hi,
>>> I have been googling this for a while and cannot seem to find any real >>> answers.
>>> I have an Oracle stored procedure that has a number of in parameters >>> that have a type that is table of the table rowtype. So for example:
>>> *Declared in the pacakge:* >>> TYPE param1_type_t IS TABLE OF table1%ROWTYPE; >>> TYPE param2_type_t IS TABLE OF table2%ROWTYPE; >>> TYPE param3_type_t IS TABLE OF table3%ROWTYPE;
>>> *Procedure:* >>> PROCEDURE my_proc >>> ( >>> parameter1 IN param1_type_t, >>> parameter2 IN param2_type_t, >>> parameter3 IN param3_type_t >>> )
>>> On the java side, I have 3 corresponding POJO objects representing each >>> of the parameters and the code creates a java List of each of the POJOs. >>> Is it possible to call the Oracle procedure using MyBatis in this scenario?
>>> If so, how would the SQLMap be written? Or do I need to write more >>> objects to cater for this scenario?
1.Type handler must be defined for every parameter. Here is one example: if we have wrapperObject having properties listOfMaps1,2,3 (mentioned in my post above) and returnValue map snippet would look like this:
<select id="callMyProcedure" statementType="CALLABLE" parameterType="wrapperObject"> call my_proc(#{listOfMaps1,mode=IN,typeHandler=package.Param1TypeHandler}, #{listOfMaps2,mode=IN,typeHandler=package. Param2TypeHandler}, #{listOfMaps3,mode=IN,typeHandler=package. Param3TypeHandler}, # {returnValue,mode=OUT,jdbcTypeName=NUMERIC,javaType=java.lang.Integer}) </select> If you have multiple parameters, you have to specify type handler for every one of them.
2. No. Only if you use same type handler for IN and OUT parameters. Integer doesn't need specific type handler, just define OUT parameter like in the example above.
Dana petak, 5. listopada 2012. 16:30:47 UTC+2, korisnik grimmel97 napisao je:
> Thank you again for your time in trying to explain this to me. I will try > to digest this and write the TypeHandler tomorrow. However for now I have > a few more questions.
> 1. If I have to create a TypeHandler for each of my parameters, then in my > original example I would have 3 type handlers, say
> Param1TypeHandler
> Param2TypeHandler
> Param3TypeHandler
> Can I specify more than one TypeHandler for the MyBatis SQLMap?
> 2. I am actually returning a single Integer value from an OUT parameter in > my procedure; Do I have to write an implementation for the getResult in > this case?
> 3. Yes, I am using the older Oracle jdbc driver, so will upgrade it in my > project and see how it goes.
> Thank you so much for your time. I will try and write a single type > handler tomorrow and try to get it to compile.
> On Friday, 5 October 2012 16:09:11 UTC+10, AntPort wrote:
>> 1. Yes, you have to implement TypeHandler<T> interface.
>> public interface TypeHandler<T> {
>> public void setParameter(PreparedStatement ps, int i, T parameter, >> JdbcType jdbcType) >> throws SQLException;
>> public T getResult(ResultSet rs, String columnName) >> throws SQLException;
>> public T getResult(CallableStatement cs, int columnIndex) >> throws SQLException;
>> }
>> If you are not returning anything from your procedure, you can leave two >> getResult methods empty.
>> 2. No, you have to do something like this: >> If these are the POJO properties:
>> IdReservation >> IdBranch >> IAgn >> NapAgn
>> ...then you have to do something like this:
>> List<Map<String,Object>> listOfPojoMaps = new >> ArrayList<Map<String,Object>>(); >> Map<String,Object> pojoMap = new LinkedHashMap<String,Object>();
>> // keys correspond to the properties of the I_RESERVATION_TYPE
>> The listOfPojoMaps is the property you set as an input parameter in your >> stored procedure call.
>> 3. Every map from the listOfPojoMaps creates one STRUCT object. Since we >> have I_REZERVATION_ARRAY as an array of objects in the database,we have >> to create STRUCT[] array which we pass as a parameter to the >> prepairedStatement. If we didn't have that, we could send only one >> parameter/pojo to the database. >> Remember, you are not sending pojos to typehandler, just it's values >> wrapped in a list of maps.
>> 4. In essence, yes, you will have to create typeHandler for your every >> type in the database, although you can do something like this:
>> 5. Regarding the compiler errors....Are you using oracle.sql.STRUCT >> .STRUCT(StructDescriptor arg0, Connection arg1, Map arg2) ?
>> P.S.
>> *It is important that you use ojdbc6 oracle driver, ojdbc14 doesn't seem >> to work...* >> * >> * >> * >> * >> I' m not sure if this way is the simplest or the best approach, but it >> works in our environment...* >> *
>> Dana petak, 5. listopada 2012. 03:16:00 UTC+2, korisnik grimmel97 napisao >> je:
>>> Thank you AntPort for taking the time to reply. Apologies for the >>> additional questions, however if you haven't already guessed, I am new to >>> this, so are you able to clarify for me:
>>> 1. When I create the TypeHandler, do I just implement the MyBatis >>> TypeHandler?
>>> 2. I can't get my head around the code-listing you have in the >>> TypeHandler. So say I have a pojo IReservation that matches your >>> I_RESERVATION_TYPE with the properties:
>>> and my application creates and populates a list of IReservation: >>> List<IReservation> and I want to pass the entire list as an Oracle, how do >>> I incorporate this into the TypeHandler code example you have provided?
>>> 3. Could you explain why the STRUCT[] array is required? Is that >>> because that is the type of array that Oracle supports?
>>> 4. Are you saying that I would have to create a TypeHandler for each >>> parameter in my example?
>>> Many thanks.
>>> On Wednesday, 3 October 2012 11:55:06 UTC+10, grimmel97 wrote:
>>>> Hi,
>>>> I have been googling this for a while and cannot seem to find any real >>>> answers.
>>>> I have an Oracle stored procedure that has a number of in parameters >>>> that have a type that is table of the table rowtype. So for example:
>>>> *Declared in the pacakge:* >>>> TYPE param1_type_t IS TABLE OF table1%ROWTYPE; >>>> TYPE param2_type_t IS TABLE OF table2%ROWTYPE; >>>> TYPE param3_type_t IS TABLE OF table3%ROWTYPE;
>>>> *Procedure:* >>>> PROCEDURE my_proc >>>> ( >>>> parameter1 IN param1_type_t, >>>> parameter2 IN param2_type_t, >>>> parameter3 IN param3_type_t >>>> )
>>>> On the java side, I have 3 corresponding POJO objects representing each >>>> of the parameters and the code creates a java List of each of the POJOs. >>>> Is it possible to call the Oracle procedure using MyBatis in this scenario?
>>>> If so, how would the SQLMap be written? Or do I need to write more >>>> objects to cater for this scenario?
I made a mistake: Line #{returnValue,mode=OUT,jdbcTypeName=NUMERIC,javaType=java.lang.Integer} must be converted into #{returnValue,mode=OUT,jdbcType =NUMERIC,javaType=java.lang.Integer}
Dana subota, 6. listopada 2012. 23:35:44 UTC+2, korisnik AntPort napisao je:
> 1.Type handler must be defined for every parameter. Here is one example: > if we have wrapperObject having properties listOfMaps1,2,3 (mentioned in > my post above) and returnValue map snippet would look like this:
> <select id="callMyProcedure" statementType="CALLABLE" > parameterType="wrapperObject"> > call > my_proc(#{listOfMaps1,mode=IN,typeHandler=package.Param1TypeHandler}, > #{listOfMaps2,mode=IN,typeHandler=package. > Param2TypeHandler}, > #{listOfMaps3,mode=IN,typeHandler=package. > Param3TypeHandler}, > # > {returnValue,mode=OUT,jdbcTypeName=NUMERIC,javaType=java.lang.Integer}) > </select> > If you have multiple parameters, you have to specify type handler for > every one of them.
> 2. No. Only if you use same type handler for IN and OUT parameters. > Integer doesn't need specific type handler, just define OUT parameter like > in the example above.
> Dana petak, 5. listopada 2012. 16:30:47 UTC+2, korisnik grimmel97 napisao > je:
>> Hi,
>> Thank you again for your time in trying to explain this to me. I will >> try to digest this and write the TypeHandler tomorrow. However for now I >> have a few more questions.
>> 1. If I have to create a TypeHandler for each of my parameters, then in >> my original example I would have 3 type handlers, say
>> Param1TypeHandler
>> Param2TypeHandler
>> Param3TypeHandler
>> Can I specify more than one TypeHandler for the MyBatis SQLMap?
>> 2. I am actually returning a single Integer value from an OUT parameter >> in my procedure; Do I have to write an implementation for the getResult in >> this case?
>> 3. Yes, I am using the older Oracle jdbc driver, so will upgrade it in my >> project and see how it goes.
>> Thank you so much for your time. I will try and write a single type >> handler tomorrow and try to get it to compile.
>> On Friday, 5 October 2012 16:09:11 UTC+10, AntPort wrote:
>>> 1. Yes, you have to implement TypeHandler<T> interface.
>>> public interface TypeHandler<T> {
>>> public void setParameter(PreparedStatement ps, int i, T parameter, >>> JdbcType jdbcType) >>> throws SQLException;
>>> public T getResult(ResultSet rs, String columnName) >>> throws SQLException;
>>> public T getResult(CallableStatement cs, int columnIndex) >>> throws SQLException;
>>> }
>>> If you are not returning anything from your procedure, you can leave two >>> getResult methods empty.
>>> 2. No, you have to do something like this: >>> If these are the POJO properties:
>>> The listOfPojoMaps is the property you set as an input parameter in >>> your stored procedure call.
>>> 3. Every map from the listOfPojoMaps creates one STRUCT object. Since we >>> have I_REZERVATION_ARRAY as an array of objects in the database,we have >>> to create STRUCT[] array which we pass as a parameter to the >>> prepairedStatement. If we didn't have that, we could send only one >>> parameter/pojo to the database. >>> Remember, you are not sending pojos to typehandler, just it's values >>> wrapped in a list of maps.
>>> 4. In essence, yes, you will have to create typeHandler for your every >>> type in the database, although you can do something like this:
>>> 5. Regarding the compiler errors....Are you using oracle.sql.STRUCT >>> .STRUCT(StructDescriptor arg0, Connection arg1, Map arg2) ?
>>> P.S.
>>> *It is important that you use ojdbc6 oracle driver, ojdbc14 doesn't >>> seem to work...* >>> * >>> * >>> * >>> * >>> I' m not sure if this way is the simplest or the best approach, but it >>> works in our environment...* >>> *
>>> Dana petak, 5. listopada 2012. 03:16:00 UTC+2, korisnik grimmel97 >>> napisao je:
>>>> Thank you AntPort for taking the time to reply. Apologies for the >>>> additional questions, however if you haven't already guessed, I am new to >>>> this, so are you able to clarify for me:
>>>> 1. When I create the TypeHandler, do I just implement the MyBatis >>>> TypeHandler?
>>>> 2. I can't get my head around the code-listing you have in the >>>> TypeHandler. So say I have a pojo IReservation that matches your >>>> I_RESERVATION_TYPE with the properties:
>>>> and my application creates and populates a list of IReservation: >>>> List<IReservation> and I want to pass the entire list as an Oracle, how do >>>> I incorporate this into the TypeHandler code example you have provided?
>>>> 3. Could you explain why the STRUCT[] array is required? Is that >>>> because that is the type of array that Oracle supports?
>>>> 4. Are you saying that I would have to create a TypeHandler for each >>>> parameter in my example?
>>>> Many thanks.
>>>> On Wednesday, 3 October 2012 11:55:06 UTC+10, grimmel97 wrote:
>>>>> Hi,
>>>>> I have been googling this for a while and cannot seem to find any real >>>>> answers.
>>>>> I have an Oracle stored procedure that has a number of in parameters >>>>> that have a type that is table of the table rowtype. So for example:
>>>>> *Declared in the pacakge:* >>>>> TYPE param1_type_t IS TABLE OF table1%ROWTYPE; >>>>> TYPE param2_type_t IS TABLE OF table2%ROWTYPE; >>>>> TYPE param3_type_t IS TABLE OF table3%ROWTYPE;
>>>>> *Procedure:* >>>>> PROCEDURE my_proc >>>>> ( >>>>> parameter1 IN param1_type_t, >>>>> parameter2 IN param2_type_t, >>>>> parameter3 IN param3_type_t >>>>> )
>>>>> On the java side, I have 3 corresponding POJO objects representing >>>>> each of the parameters and the code creates a java List of each of the >>>>> POJOs. Is it possible to call the Oracle procedure using MyBatis in this >>>>> scenario?
>>>>> If so, how would the SQLMap be written? Or do I need to write more >>>>> objects to cater for this scenario?
I've tried to get my TypeHandler to work, but it just doesn't seem to be happy. So it is obvious that I am not not understanding this correctly. I have just written a single test case with a simple insert Oracle stored procedure that just takes a single collection as an IN parameter to try and get a working piece of code.
The code gets to the setParameter mehod in my TypeHandler then fails.
To be honest, I am not sure if I am understanding this correctly, but I thought the process was:
1. The Object parameter for setParameter would be the List<Pojo> object that I have already created elsewhere in my code. 2. I define a Map object as well as a List of these Map objects. 3. I assign the values of each Pojo in my List<Pojo> to a Map object and then add this Map object to the List of Map objects 4. I then create the Oracle array types as per your previous examples and write my List of Map objects to them.
However it is not working and I have pasted my generic SQLMap, setParameter method of the customer TypeHandler and the trace log. Much appreciated if you could tell me what I am doing wrong.
*setParameter method of my TypeHandler:* public void setParameter(PreparedStatement ps, int argNum, Object parameter, JdbcType jdbcType) throws SQLException {
Connection conn; // list of maps LinkedHashMap<String,Object> pojoMap; List<LinkedHashMap<String,Object>> listOfPojoMaps;
pojoMap = new LinkedHashMap<>(); listOfPojoMaps = new ArrayList<>();
// keys corresponding to the properties of the ORACLE_COLLECTION_T
if(parameter != null){
for(Pojo e : (List<Pojo>)parameter){ pojoMap.put("PROPERTY_1", e.getParameter1()); pojoMap.put("PROPERTY_2", e.getParameter2()); pojoMap.put("PROPERTY_3", e.getParameter3());
listOfPojoMaps.add(pojoMap); } }
STRUCT[] structArray = new STRUCT[listOfPojoMaps.size()]; conn = ps.getConnection();
On Tuesday, 9 October 2012 02:03:48 UTC+11, AntPort wrote:
> I made a mistake: Line #{returnValue,mode=OUT,jdbcTypeName=NUMERIC,javaType=java.lang.Integer} > must be converted into #{returnValue,mode=OUT,jdbcType > =NUMERIC,javaType=java.lang.Integer}
> Dana subota, 6. listopada 2012. 23:35:44 UTC+2, korisnik AntPort napisao > je:
>> 1.Type handler must be defined for every parameter. Here is one example: >> if we have wrapperObject having properties listOfMaps1,2,3 (mentioned in >> my post above) and returnValue map snippet would look like this:
>> <select id="callMyProcedure" statementType="CALLABLE" >> parameterType="wrapperObject"> >> call >> my_proc(#{listOfMaps1,mode=IN,typeHandler=package.Param1TypeHandler}, >> #{listOfMaps2,mode=IN,typeHandler=package. >> Param2TypeHandler}, >> #{listOfMaps3,mode=IN,typeHandler=package. >> Param3TypeHandler}, >> # >> {returnValue,mode=OUT,jdbcTypeName=NUMERIC,javaType=java.lang.Integer}) >> </select> >> If you have multiple parameters, you have to specify type handler for >> every one of them.
>> 2. No. Only if you use same type handler for IN and OUT parameters. >> Integer doesn't need specific type handler, just define OUT parameter like >> in the example above.
>> Dana petak, 5. listopada 2012. 16:30:47 UTC+2, korisnik grimmel97 napisao >> je:
>>> Hi,
>>> Thank you again for your time in trying to explain this to me. I will >>> try to digest this and write the TypeHandler tomorrow. However for now I >>> have a few more questions.
>>> 1. If I have to create a TypeHandler for each of my parameters, then in >>> my original example I would have 3 type handlers, say
>>> Param1TypeHandler
>>> Param2TypeHandler
>>> Param3TypeHandler
>>> Can I specify more than one TypeHandler for the MyBatis SQLMap?
>>> 2. I am actually returning a single Integer value from an OUT parameter >>> in my procedure; Do I have to write an implementation for the getResult in >>> this case?
>>> 3. Yes, I am using the older Oracle jdbc driver, so will upgrade it in >>> my project and see how it goes.
>>> Thank you so much for your time. I will try and write a single type >>> handler tomorrow and try to get it to compile.
>>> On Friday, 5 October 2012 16:09:11 UTC+10, AntPort wrote:
>>>> 1. Yes, you have to implement TypeHandler<T> interface.
>>>> public interface TypeHandler<T> {
>>>> public void setParameter(PreparedStatement ps, int i, T parameter, >>>> JdbcType jdbcType) >>>> throws SQLException;
>>>> public T getResult(ResultSet rs, String columnName) >>>> throws SQLException;
>>>> public T getResult(CallableStatement cs, int columnIndex) >>>> throws SQLException;
>>>> }
>>>> If you are not returning anything from your procedure, you can leave >>>> two getResult methods empty.
>>>> 2. No, you have to do something like this: >>>> If these are the POJO properties:
>>>> The listOfPojoMaps is the property you set as an input parameter in >>>> your stored procedure call.
>>>> 3. Every map from the listOfPojoMaps creates one STRUCT object. Since >>>> we have I_REZERVATION_ARRAY as an array of objects in the database,we >>>> have to create STRUCT[] array which we pass as a parameter to the >>>> prepairedStatement. If we didn't have that, we could send only one >>>> parameter/pojo to the database. >>>> Remember, you are not sending pojos to typehandler, just it's values >>>> wrapped in a list of maps.
>>>> 4. In essence, yes, you will have to create typeHandler for your every >>>> type in the database, although you can do something like this:
>>>> 5. Regarding the compiler errors....Are you using oracle.sql.STRUCT >>>> .STRUCT(StructDescriptor arg0, Connection arg1, Map arg2) ?
>>>> P.S.
>>>> *It is important that you use ojdbc6 oracle driver, ojdbc14 doesn't >>>> seem to work...* >>>> * >>>> * >>>> * >>>> * >>>> I' m not sure if this way is the simplest or the best approach, but it >>>> works in our environment...* >>>> *
>>>> Dana petak, 5. listopada 2012. 03:16:00 UTC+2, korisnik grimmel97 >>>> napisao je:
>>>>> Thank you AntPort for taking the time to reply. Apologies for the >>>>> additional questions, however if you haven't already guessed, I am new to >>>>> this, so are you able to clarify for me:
>>>>> 1. When I create the TypeHandler, do I just implement the MyBatis >>>>> TypeHandler?
>>>>> 2. I can't get my head around the code-listing you have in the >>>>> TypeHandler. So say I have a pojo IReservation that matches your
I have progressed this a bit, there was an issue with my parameter passing getting the NullPointerException, but now I have got it to pass to the TypeHandler I am now getting an:
Error updating database. Cause: java.lang.ClassCastException: org.apache.commons.dbcp.PoolableConnection cannot be cast to oracle.jdbc.OracleConnection
On Tuesday, 9 October 2012 14:59:20 UTC+11, grimmel97 wrote:
> I've tried to get my TypeHandler to work, but it just doesn't seem to be > happy. So it is obvious that I am not not understanding this correctly. I > have just written a single test case with a simple insert Oracle stored > procedure that just takes a single collection as an IN parameter to try and > get a working piece of code.
> The code gets to the setParameter mehod in my TypeHandler then fails.
> To be honest, I am not sure if I am understanding this correctly, but I > thought the process was:
> 1. The Object parameter for setParameter would be the List<Pojo> object > that I have already created elsewhere in my code. > 2. I define a Map object as well as a List of these Map objects. > 3. I assign the values of each Pojo in my List<Pojo> to a Map object and > then add this Map object to the List of Map objects > 4. I then create the Oracle array types as per your previous examples and > write my List of Map objects to them.
> However it is not working and I have pasted my generic SQLMap, > setParameter method of the customer TypeHandler and the trace log. Much > appreciated if you could tell me what I am doing wrong.
> *Log:* > SEVERE: org.mybatis.spring.MyBatisSystemException: nested exception is > org.apache.ibatis.exceptions.PersistenceException: > ### Error updating database. Cause: java.lang.NullPointerException > ### The error may involve defaultParameterMap > ### The error occurred while setting parameters > ### SQL: {CALL test(?) > ### Cause: java.lang.NullPointerException > javax.faces.el.EvaluationException: > org.mybatis.spring.MyBatisSystemException: nested exception is > org.apache.ibatis.exceptions.PersistenceException: > ### Error updating database. Cause: java.lang.NullPointerException > ### The error may involve defaultParameterMap > ### The error occurred while setting parameters > ### SQL: {CALL test(?) > ### Cause: java.lang.NullPointerException
> On Tuesday, 9 October 2012 02:03:48 UTC+11, AntPort wrote:
>> I made a mistake: Line #{returnValue,mode=OUT,jdbcTypeName=NUMERIC,javaType=java.lang.Integer} >> must be converted into #{returnValue,mode=OUT,jdbcType >> =NUMERIC,javaType=java.lang.Integer}
>> Dana subota, 6. listopada 2012. 23:35:44 UTC+2, korisnik AntPort napisao >> je:
>>> 1.Type handler must be defined for every parameter. Here is one example: >>> if we have wrapperObject having properties listOfMaps1,2,3 (mentioned >>> in my post above) and returnValue map snippet would look like this:
>>> <select id="callMyProcedure" statementType="CALLABLE" >>> parameterType="wrapperObject"> >>> call >>> my_proc(#{listOfMaps1,mode=IN,typeHandler=package.Param1TypeHandler}, >>> #{listOfMaps2,mode=IN,typeHandler=package. >>> Param2TypeHandler}, >>> #{listOfMaps3,mode=IN,typeHandler=package. >>> Param3TypeHandler}, >>> # >>> {returnValue,mode=OUT,jdbcTypeName=NUMERIC,javaType=java.lang.Integer}) >>> </select> >>> If you have multiple parameters, you have to specify type handler for >>> every one of them.
>>> 2. No. Only if you use same type handler for IN and OUT parameters. >>> Integer doesn't need specific type handler, just define OUT parameter like >>> in the example above.
>>> Dana petak, 5. listopada 2012. 16:30:47 UTC+2, korisnik grimmel97 >>> napisao je:
>>>> Hi,
>>>> Thank you again for your time in trying to explain this to me. I will >>>> try to digest this and write the TypeHandler tomorrow. However for now I >>>> have a few more questions.
>>>> 1. If I have to create a TypeHandler for each of my parameters, then in >>>> my original example I would have 3 type handlers, say
>>>> Param1TypeHandler
>>>> Param2TypeHandler
>>>> Param3TypeHandler
>>>> Can I specify more than one TypeHandler for the MyBatis SQLMap?
>>>> 2. I am actually returning a single Integer value from an OUT parameter >>>> in my procedure; Do I have to write an implementation for the getResult in >>>> this case?
>>>> 3. Yes, I am using the older Oracle jdbc driver, so will upgrade it in >>>> my project and see how it goes.
>>>> Thank you so much for your time. I will try and write a single type >>>> handler tomorrow and try to get it to compile.
>>>> On Friday, 5 October 2012 16:09:11 UTC+10, AntPort wrote:
>>>>> 1. Yes, you have to implement TypeHandler<T> interface.
>>>>> public interface TypeHandler<T> {
>>>>> public void setParameter(PreparedStatement ps, int i, T parameter, >>>>> JdbcType jdbcType) >>>>> throws SQLException;
>>>>> public T getResult(ResultSet rs, String columnName) >>>>> throws SQLException;
>>>>> public T getResult(CallableStatement cs, int columnIndex) >>>>> throws SQLException;
>>>>> }
>>>>> If you are not returning anything from your procedure, you can leave >>>>> two getResult methods empty.
>>>>> 2. No, you have to do something like this: >>>>> If these are the POJO properties:
>>>>> The listOfPojoMaps is the property you set as an input parameter in >>>>> your stored procedure call.
>>>>> 3. Every map from the listOfPojoMaps creates one STRUCT object. Since >>>>> we have I_REZERVATION_ARRAY as an array of objects in the database,we >>>>> have to create STRUCT[] array which we pass as a parameter to the >>>>> prepairedStatement. If we didn't have that, we could send only one >>>>> parameter/pojo to the database. >>>>> Remember, you are not sending pojos to typehandler, just it's values >>>>> wrapped in a list of maps.
>>>>> 4. In essence, yes, you will have to create typeHandler for your every >>>>> type in the database, although you can do something like this:
On Tue, Oct 9, 2012 at 7:59 AM, grimmel97 <grimme...@gmail.com> wrote:
> I have progressed this a bit, there was an issue with my parameter passing
> getting the NullPointerException, but now I have got it to pass to the
> TypeHandler I am now getting an:
> Error updating database. Cause: java.lang.ClassCastException:
> org.apache.commons.dbcp.PoolableConnection cannot be cast to
> oracle.jdbc.OracleConnection
> On Tuesday, 9 October 2012 14:59:20 UTC+11, grimmel97 wrote:
>> I've tried to get my TypeHandler to work, but it just doesn't seem to be
>> happy. So it is obvious that I am not not understanding this correctly. I
>> have just written a single test case with a simple insert Oracle stored
>> procedure that just takes a single collection as an IN parameter to try and
>> get a working piece of code.
>> The code gets to the setParameter mehod in my TypeHandler then fails.
>> To be honest, I am not sure if I am understanding this correctly, but I
>> thought the process was:
>> 1. The Object parameter for setParameter would be the List<Pojo> object
>> that I have already created elsewhere in my code.
>> 2. I define a Map object as well as a List of these Map objects.
>> 3. I assign the values of each Pojo in my List<Pojo> to a Map object and
>> then add this Map object to the List of Map objects
>> 4. I then create the Oracle array types as per your previous examples and
>> write my List of Map objects to them.
>> However it is not working and I have pasted my generic SQLMap,
>> setParameter method of the customer TypeHandler and the trace log. Much
>> appreciated if you could tell me what I am doing wrong.
>> Log:
>> SEVERE: org.mybatis.spring.MyBatisSystemException: nested exception is
>> org.apache.ibatis.exceptions.PersistenceException:
>> ### Error updating database. Cause: java.lang.NullPointerException
>> ### The error may involve defaultParameterMap
>> ### The error occurred while setting parameters
>> ### SQL: {CALL test(?)
>> ### Cause: java.lang.NullPointerException
>> javax.faces.el.EvaluationException:
>> org.mybatis.spring.MyBatisSystemException: nested exception is
>> org.apache.ibatis.exceptions.PersistenceException:
>> ### Error updating database. Cause: java.lang.NullPointerException
>> ### The error may involve defaultParameterMap
>> ### The error occurred while setting parameters
>> ### SQL: {CALL test(?)
>> ### Cause: java.lang.NullPointerException
>> On Tuesday, 9 October 2012 02:03:48 UTC+11, AntPort wrote:
>>> I made a mistake: Line
>>> #{returnValue,mode=OUT,jdbcTypeName=NUMERIC,javaType=java.lang.Integer} must
>>> be converted into
>>> #{returnValue,mode=OUT,jdbcType=NUMERIC,javaType=java.lang.Integer}
>>> Dana subota, 6. listopada 2012. 23:35:44 UTC+2, korisnik AntPort napisao
>>> je:
>>>> 1.Type handler must be defined for every parameter. Here is one example:
>>>> if we have wrapperObject having properties listOfMaps1,2,3 (mentioned in my
>>>> post above) and returnValue map snippet would look like this:
>>>> #{returnValue,mode=OUT,jdbcTypeName=NUMERIC,javaType=java.lang.Integer})
>>>> </select>
>>>> If you have multiple parameters, you have to specify type handler for
>>>> every one of them.
>>>> 2. No. Only if you use same type handler for IN and OUT parameters.
>>>> Integer doesn't need specific type handler, just define OUT parameter like
>>>> in the example above.
>>>> Dana petak, 5. listopada 2012. 16:30:47 UTC+2, korisnik grimmel97
>>>> napisao je:
>>>>> Hi,
>>>>> Thank you again for your time in trying to explain this to me. I will
>>>>> try to digest this and write the TypeHandler tomorrow. However for now I
>>>>> have a few more questions.
>>>>> 1. If I have to create a TypeHandler for each of my parameters, then in
>>>>> my original example I would have 3 type handlers, say
>>>>> Param1TypeHandler
>>>>> Param2TypeHandler
>>>>> Param3TypeHandler
>>>>> Can I specify more than one TypeHandler for the MyBatis SQLMap?
>>>>> 2. I am actually returning a single Integer value from an OUT parameter
>>>>> in my procedure; Do I have to write an implementation for the getResult in
>>>>> this case?
>>>>> 3. Yes, I am using the older Oracle jdbc driver, so will upgrade it in
>>>>> my project and see how it goes.
>>>>> Thank you so much for your time. I will try and write a single type
>>>>> handler tomorrow and try to get it to compile.
>>>>> On Friday, 5 October 2012 16:09:11 UTC+10, AntPort wrote:
>>>>>> 1. Yes, you have to implement TypeHandler<T> interface.
>>>>>> public interface TypeHandler<T> {
>>>>>> public void setParameter(PreparedStatement ps, int i, T parameter,
>>>>>> JdbcType jdbcType)
>>>>>> throws SQLException;
>>>>>> public T getResult(ResultSet rs, String columnName)
>>>>>> throws SQLException;
>>>>>> public T getResult(CallableStatement cs, int columnIndex)
>>>>>> throws SQLException;
>>>>>> }
>>>>>> If you are not returning anything from your procedure, you can leave
>>>>>> two getResult methods empty.
>>>>>> 2. No, you have to do something like this:
>>>>>> If these are the POJO properties:
>>>>>> The listOfPojoMaps is the property you set as an input parameter in
>>>>>> your stored procedure call.
>>>>>> 3. Every map from the listOfPojoMaps creates one STRUCT object. Since
>>>>>> we have I_REZERVATION_ARRAY as an array of objects in the database,we have
>>>>>> to create STRUCT[] array which we pass as a parameter to the
>>>>>> prepairedStatement. If we didn't have that, we could send only one
>>>>>> parameter/pojo to the database.
>>>>>> Remember, you are not sending pojos to typehandler, just it's values
>>>>>> wrapped in a list of maps.
>>>>>> 4. In essence, yes, you will have to create typeHandler for your every
>>>>>> type in the database, although you can do something like this:
>>>>>> You can send this wrapper as an input parameter to your typeHandler
>>>>>> but you have to modify the code snippet mentioned in my first post to:
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.
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 parameters. Although I suspect it is the first case given my test procedure only has one parameter and I am passing one!
On Tuesday, 9 October 2012 18:03:58 UTC+11, Dridi Boukelmoune wrote:
> Hi,
> Does your type handler directly implement the interface ? > If you want to handle nulls you should rather extend the default type > handler.
> Dridi
> On Tue, Oct 9, 2012 at 7:59 AM, grimmel97 <grim...@gmail.com <javascript:>> > wrote: > > I have progressed this a bit, there was an issue with my parameter > passing > > getting the NullPointerException, but now I have got it to pass to the > > TypeHandler I am now getting an:
> > Error updating database. Cause: java.lang.ClassCastException: > > org.apache.commons.dbcp.PoolableConnection cannot be cast to > > oracle.jdbc.OracleConnection
> > On Tuesday, 9 October 2012 14:59:20 UTC+11, grimmel97 wrote:
> >> I've tried to get my TypeHandler to work, but it just doesn't seem to > be > >> happy. So it is obvious that I am not not understanding this > correctly. I > >> have just written a single test case with a simple insert Oracle stored > >> procedure that just takes a single collection as an IN parameter to try > and > >> get a working piece of code.
> >> The code gets to the setParameter mehod in my TypeHandler then fails.
> >> To be honest, I am not sure if I am understanding this correctly, but I > >> thought the process was:
> >> 1. The Object parameter for setParameter would be the List<Pojo> object > >> that I have already created elsewhere in my code. > >> 2. I define a Map object as well as a List of these Map objects. > >> 3. I assign the values of each Pojo in my List<Pojo> to a Map object > and > >> then add this Map object to the List of Map objects > >> 4. I then create the Oracle array types as per your previous examples > and > >> write my List of Map objects to them.
> >> However it is not working and I have pasted my generic SQLMap, > >> setParameter method of the customer TypeHandler and the trace log. > Much > >> appreciated if you could tell me what I am doing wrong.
> >> Log: > >> SEVERE: org.mybatis.spring.MyBatisSystemException: nested exception is > >> org.apache.ibatis.exceptions.PersistenceException: > >> ### Error updating database. Cause: java.lang.NullPointerException > >> ### The error may involve defaultParameterMap > >> ### The error occurred while setting parameters > >> ### SQL: {CALL test(?) > >> ### Cause: java.lang.NullPointerException > >> javax.faces.el.EvaluationException: > >> org.mybatis.spring.MyBatisSystemException: nested exception is > >> org.apache.ibatis.exceptions.PersistenceException: > >> ### Error updating database. Cause: java.lang.NullPointerException > >> ### The error may involve defaultParameterMap > >> ### The error occurred while setting parameters > >> ### SQL: {CALL test(?) > >> ### Cause: java.lang.NullPointerException
> >> On Tuesday, 9 October 2012 02:03:48 UTC+11, AntPort wrote:
> >>> I made a mistake: Line
> #{returnValue,mode=OUT,jdbcTypeName=NUMERIC,javaType=java.lang.Integer} > must > >>> be converted into > >>> #{returnValue,mode=OUT,jdbcType=NUMERIC,javaType=java.lang.Integer}
> >>> Dana subota, 6. listopada 2012. 23:35:44 UTC+2, korisnik AntPort > napisao > >>> je:
> >>>> 1.Type handler must be defined for every parameter. Here is one > example: > >>>> if we have wrapperObject having properties listOfMaps1,2,3 (mentioned > in my > >>>> post above) and returnValue map snippet would look like this:
> #{returnValue,mode=OUT,jdbcTypeName=NUMERIC,javaType=java.lang.Integer}) > >>>> </select> > >>>> If you have multiple parameters, you have to specify type handler for > >>>> every one of them.
> >>>> 2. No. Only if you use same type handler for IN and OUT parameters. > >>>> Integer doesn't need specific type handler, just define OUT parameter > like > >>>> in the example above.
> >>>> Dana petak, 5. listopada 2012. 16:30:47 UTC+2, korisnik grimmel97 > >>>> napisao je:
> >>>>> Hi,
> >>>>> Thank you again for your time in trying to explain this to me. I > will > >>>>> try to digest this and write the TypeHandler tomorrow. However for > now I > >>>>> have a few more questions.
> >>>>> 1. If I have to create a TypeHandler for each of my parameters, then > in > >>>>> my original example I would have 3 type handlers, say
> >>>>> Param1TypeHandler
> >>>>> Param2TypeHandler
> >>>>> Param3TypeHandler
> >>>>> Can I specify more than one TypeHandler for the MyBatis SQLMap?
> >>>>> 2. I am actually returning a single Integer value from an OUT > parameter > >>>>> in my procedure; Do I have to write an implementation for the > getResult in > >>>>> this case?
> >>>>> 3. Yes, I am using the older Oracle jdbc driver, so will upgrade it > in > >>>>> my project and see how it goes.
> >>>>> Thank you so much for your time. I will try and write a single type > >>>>> handler tomorrow and try to get it to compile.
> >>>>> On Friday, 5 October 2012 16:09:11 UTC+10, AntPort wrote:
> >>>>>> 1. Yes, you have to implement TypeHandler<T> interface.
> >>>>>> public interface TypeHandler<T> {
> >>>>>> public void setParameter(PreparedStatement ps, int i, T > parameter, > >>>>>> JdbcType jdbcType) > >>>>>> throws SQLException;
> >>>>>> public T getResult(ResultSet rs, String columnName) > >>>>>> throws SQLException;
> >>>>>> public T getResult(CallableStatement cs, int columnIndex) > >>>>>> throws SQLException;
> >>>>>> }
> >>>>>> If you are not returning anything from your procedure, you can > leave > >>>>>> two getResult methods empty.
> >>>>>> 2. No, you have to do something like this: > >>>>>> If these are the POJO properties:
Thank you so much for your time and help. I have finally managed to get my parameter correctly passed to my Oracle stored procedure!! I can now look at writing the other TypeHandlers and improving the code with a wrapper. If you haven't already guessed, I have much less experience with Java than with Oracle PL/SQL ... but I guess we all have to start somewhere.
On Tuesday, 9 October 2012 02:03:48 UTC+11, AntPort wrote:
> I made a mistake: Line #{returnValue,mode=OUT,jdbcTypeName=NUMERIC,javaType=java.lang.Integer} > must be converted into #{returnValue,mode=OUT,jdbcType > =NUMERIC,javaType=java.lang.Integer}
> Dana subota, 6. listopada 2012. 23:35:44 UTC+2, korisnik AntPort napisao > je:
>> 1.Type handler must be defined for every parameter. Here is one example: >> if we have wrapperObject having properties listOfMaps1,2,3 (mentioned in >> my post above) and returnValue map snippet would look like this:
>> <select id="callMyProcedure" statementType="CALLABLE" >> parameterType="wrapperObject"> >> call >> my_proc(#{listOfMaps1,mode=IN,typeHandler=package.Param1TypeHandler}, >> #{listOfMaps2,mode=IN,typeHandler=package. >> Param2TypeHandler}, >> #{listOfMaps3,mode=IN,typeHandler=package. >> Param3TypeHandler}, >> # >> {returnValue,mode=OUT,jdbcTypeName=NUMERIC,javaType=java.lang.Integer}) >> </select> >> If you have multiple parameters, you have to specify type handler for >> every one of them.
>> 2. No. Only if you use same type handler for IN and OUT parameters. >> Integer doesn't need specific type handler, just define OUT parameter like >> in the example above.
>> Dana petak, 5. listopada 2012. 16:30:47 UTC+2, korisnik grimmel97 napisao >> je:
>>> Hi,
>>> Thank you again for your time in trying to explain this to me. I will >>> try to digest this and write the TypeHandler tomorrow. However for now I >>> have a few more questions.
>>> 1. If I have to create a TypeHandler for each of my parameters, then in >>> my original example I would have 3 type handlers, say
>>> Param1TypeHandler
>>> Param2TypeHandler
>>> Param3TypeHandler
>>> Can I specify more than one TypeHandler for the MyBatis SQLMap?
>>> 2. I am actually returning a single Integer value from an OUT parameter >>> in my procedure; Do I have to write an implementation for the getResult in >>> this case?
>>> 3. Yes, I am using the older Oracle jdbc driver, so will upgrade it in >>> my project and see how it goes.
>>> Thank you so much for your time. I will try and write a single type >>> handler tomorrow and try to get it to compile.
>>> On Friday, 5 October 2012 16:09:11 UTC+10, AntPort wrote:
>>>> 1. Yes, you have to implement TypeHandler<T> interface.
>>>> public interface TypeHandler<T> {
>>>> public void setParameter(PreparedStatement ps, int i, T parameter, >>>> JdbcType jdbcType) >>>> throws SQLException;
>>>> public T getResult(ResultSet rs, String columnName) >>>> throws SQLException;
>>>> public T getResult(CallableStatement cs, int columnIndex) >>>> throws SQLException;
>>>> }
>>>> If you are not returning anything from your procedure, you can leave >>>> two getResult methods empty.
>>>> 2. No, you have to do something like this: >>>> If these are the POJO properties:
>>>> The listOfPojoMaps is the property you set as an input parameter in >>>> your stored procedure call.
>>>> 3. Every map from the listOfPojoMaps creates one STRUCT object. Since >>>> we have I_REZERVATION_ARRAY as an array of objects in the database,we >>>> have to create STRUCT[] array which we pass as a parameter to the >>>> prepairedStatement. If we didn't have that, we could send only one >>>> parameter/pojo to the database. >>>> Remember, you are not sending pojos to typehandler, just it's values >>>> wrapped in a list of maps.
>>>> 4. In essence, yes, you will have to create typeHandler for your every >>>> type in the database, although you can do something like this:
>>>> 5. Regarding the compiler errors....Are you using oracle.sql.STRUCT >>>> .STRUCT(StructDescriptor arg0, Connection arg1, Map arg2) ?
>>>> P.S.
>>>> *It is important that you use ojdbc6 oracle driver, ojdbc14 doesn't >>>> seem to work...* >>>> * >>>> * >>>> * >>>> * >>>> I' m not sure if this way is the simplest or the best approach, but it >>>> works in our environment...* >>>> *
>>>> Dana petak, 5. listopada 2012. 03:16:00 UTC+2, korisnik grimmel97 >>>> napisao je:
>>>>> Thank you AntPort for taking the time to reply. Apologies for the >>>>> additional questions, however if you haven't already guessed, I am new to >>>>> this, so are you able to clarify for me:
>>>>> 1. When I create the TypeHandler, do I just implement the MyBatis >>>>> TypeHandler?
>>>>> 2. I can't get my head around the code-listing you have in the >>>>> TypeHandler. So say I have a pojo IReservation that matches your >>>>> I_RESERVATION_TYPE with the properties:
>>>>> and my application creates and populates a list of IReservation: >>>>> List<IReservation> and I want to pass the entire list as an Oracle, how do >>>>> I incorporate this into the TypeHandler code example you have provided?
>>>>> 3. Could you explain why the STRUCT[] array is required? Is that >>>>> because that is the type of array that Oracle supports?
>>>>> 4. Are you saying that I would have to create a TypeHandler for each >>>>> parameter in my example?
>>>>> Many thanks.
>>>>> On Wednesday, 3 October 2012 11:55:06 UTC+10, grimmel97 wrote:
>>>>>> Hi,
>>>>>> I have been googling this for a while and cannot seem to find any >>>>>> real answers.
>>>>>> I have an Oracle stored procedure that has a number of in parameters >>>>>> that have a type that is table of the table rowtype. So for example:
>>>>>> *Declared in the pacakge:* >>>>>> TYPE param1_type_t IS TABLE OF table1%ROWTYPE; >>>>>> TYPE param2_type_t IS TABLE OF table2%ROWTYPE; >>>>>> TYPE param3_type_t IS TABLE OF table3%ROWTYPE;
>>>>>> *Procedure:* >>>>>> PROCEDURE my_proc >>>>>> ( >>>>>> parameter1 IN param1_type_t, >>>>>> parameter2 IN param2_type_t, >>>>>> parameter3 IN param3_type_t >>>>>> )
>>>>>> On the java side, I have 3 corresponding POJO objects representing >>>>>> each of the parameters and the code creates a java List of each of the >>>>>> POJOs. Is it possible to call the Oracle procedure using MyBatis in this >>>>>> scenario?
>>>>>> If so, how would the SQLMap be written? Or do I need to write more >>>>>> objects to cater for this scenario?
I spoke too soon. Now I have tested my procedure call is working, however it is the returning of the single OUT parameter, the Integer, which I thought would be the simplest part of the problem is now causing the issue. I have defined the out parameters as per your details below:
However, no data ever comes back. I have checked that the Integer value is correctly set in the Oracle stored procedure, however nothing comes back to the Java application.
public void maintainStatements(@Param("parameter1") List<Pojo> parameter1, @Param("parameter2") Integer parameter2 );
The mapper is then referenced via a DAO interface and a DAO implementation. However the value of parameter2 remains null and is never set by the procedure call.
Is there something further that needs to be done here? Or have I made an error somewhere?
On Tuesday, 9 October 2012 02:03:48 UTC+11, AntPort wrote:
> I made a mistake: Line #{returnValue,mode=OUT,jdbcTypeName=NUMERIC,javaType=java.lang.Integer} > must be converted into #{returnValue,mode=OUT,jdbcType > =NUMERIC,javaType=java.lang.Integer}
> Dana subota, 6. listopada 2012. 23:35:44 UTC+2, korisnik AntPort napisao > je:
>> 1.Type handler must be defined for every parameter. Here is one example: >> if we have wrapperObject having properties listOfMaps1,2,3 (mentioned in >> my post above) and returnValue map snippet would look like this:
>> <select id="callMyProcedure" statementType="CALLABLE" >> parameterType="wrapperObject"> >> call >> my_proc(#{listOfMaps1,mode=IN,typeHandler=package.Param1TypeHandler}, >> #{listOfMaps2,mode=IN,typeHandler=package. >> Param2TypeHandler}, >> #{listOfMaps3,mode=IN,typeHandler=package. >> Param3TypeHandler}, >> # >> {returnValue,mode=OUT,jdbcTypeName=NUMERIC,javaType=java.lang.Integer}) >> </select> >> If you have multiple parameters, you have to specify type handler for >> every one of them.
>> 2. No. Only if you use same type handler for IN and OUT parameters. >> Integer doesn't need specific type handler, just define OUT parameter like >> in the example above.
>> Dana petak, 5. listopada 2012. 16:30:47 UTC+2, korisnik grimmel97 napisao >> je:
>>> Hi,
>>> Thank you again for your time in trying to explain this to me. I will >>> try to digest this and write the TypeHandler tomorrow. However for now I >>> have a few more questions.
>>> 1. If I have to create a TypeHandler for each of my parameters, then in >>> my original example I would have 3 type handlers, say
>>> Param1TypeHandler
>>> Param2TypeHandler
>>> Param3TypeHandler
>>> Can I specify more than one TypeHandler for the MyBatis SQLMap?
>>> 2. I am actually returning a single Integer value from an OUT parameter >>> in my procedure; Do I have to write an implementation for the getResult in >>> this case?
>>> 3. Yes, I am using the older Oracle jdbc driver, so will upgrade it in >>> my project and see how it goes.
>>> Thank you so much for your time. I will try and write a single type >>> handler tomorrow and try to get it to compile.
>>> On Friday, 5 October 2012 16:09:11 UTC+10, AntPort wrote:
>>>> 1. Yes, you have to implement TypeHandler<T> interface.
>>>> public interface TypeHandler<T> {
>>>> public void setParameter(PreparedStatement ps, int i, T parameter, >>>> JdbcType jdbcType) >>>> throws SQLException;
>>>> public T getResult(ResultSet rs, String columnName) >>>> throws SQLException;
>>>> public T getResult(CallableStatement cs, int columnIndex) >>>> throws SQLException;
>>>> }
>>>> If you are not returning anything from your procedure, you can leave >>>> two getResult methods empty.
>>>> 2. No, you have to do something like this: >>>> If these are the POJO properties:
>>>> The listOfPojoMaps is the property you set as an input parameter in >>>> your stored procedure call.
>>>> 3. Every map from the listOfPojoMaps creates one STRUCT object. Since >>>> we have I_REZERVATION_ARRAY as an array of objects in the database,we >>>> have to create STRUCT[] array which we pass as a parameter to the >>>> prepairedStatement. If we didn't have that, we could send only one >>>> parameter/pojo to the database. >>>> Remember, you are not sending pojos to typehandler, just it's values >>>> wrapped in a list of maps.
>>>> 4. In essence, yes, you will have to create typeHandler for your every >>>> type in the database, although you can do something like this:
>>>> 5. Regarding the compiler errors....Are you using oracle.sql.STRUCT >>>> .STRUCT(StructDescriptor arg0, Connection arg1, Map arg2) ?
>>>> P.S.
>>>> *It is important that you use ojdbc6 oracle driver, ojdbc14 doesn't >>>> seem to work...* >>>> * >>>> * >>>> * >>>> * >>>> I' m not sure if this way is the simplest or the best approach, but it >>>> works in our environment...* >>>> *
>>>> Dana petak, 5. listopada 2012. 03:16:00 UTC+2, korisnik grimmel97 >>>> napisao je:
>>>>> Thank you AntPort for taking the time to reply. Apologies for the >>>>> additional questions, however if you haven't already guessed, I am new to >>>>> this, so are you able to clarify for me:
>>>>> 1. When I create the TypeHandler, do I just implement the MyBatis >>>>> TypeHandler?
>>>>> 2. I can't get my head around the code-listing you have in the >>>>> TypeHandler. So say I have a pojo IReservation that matches your >>>>> I_RESERVATION_TYPE with the properties:
>>>>> and my application creates and populates a list of IReservation: >>>>> List<IReservation> and I want to pass the entire list as an Oracle, how do >>>>> I incorporate this into the TypeHandler code example you have provided?
>>>>> 3. Could you explain why the STRUCT[] array is required? Is that >>>>> because that is the type of array that Oracle supports?
>>>>> 4. Are you saying that I would have to create a TypeHandler for each >>>>> parameter in my example?
>>>>> Many thanks.
>>>>> On Wednesday, 3 October 2012 11:55:06 UTC+10, grimmel97 wrote:
>>>>>> Hi,
>>>>>> I have been googling this for a while and cannot seem to find any >>>>>> real answers.
>>>>>> I have an Oracle stored procedure that has a number of in parameters >>>>>> that have a type that is table of the table rowtype. So for example:
>>>>>> *Declared in the pacakge:* >>>>>> TYPE param1_type_t IS TABLE OF table1%ROWTYPE; >>>>>> TYPE param2_type_t IS TABLE OF table2%ROWTYPE; >>>>>> TYPE param3_type_t IS TABLE OF table3%ROWTYPE;
>>>>>> *Procedure:* >>>>>> PROCEDURE my_proc >>>>>> ( >>>>>> parameter1 IN param1_type_t, >>>>>> parameter2 IN param2_type_t, >>>>>> parameter3 IN param3_type_t >>>>>> )
>>>>>> On the java side, I have 3 corresponding POJO objects representing >>>>>> each of the parameters and the code creates a java List of each of the >>>>>> POJOs. Is it possible to call the Oracle procedure using MyBatis in this >>>>>> scenario?
>>>>>> If so, how would the SQLMap be written? Or do I need to write more >>>>>> objects to cater for this scenario?
With OUT parameters you will need to make your own map to pass parameters
and not use the @Param annotation. After the call, the out value will be in
the map you pass.
On Friday, October 12, 2012, grimmel97 wrote:
> Hi AntPort,
> I spoke too soon. Now I have tested my procedure call is working, however
> it is the returning of the single OUT parameter, the Integer, which I
> thought would be the simplest part of the problem is now causing the issue.
> I have defined the out parameters as per your details below:
> However, no data ever comes back. I have checked that the Integer value
> is correctly set in the Oracle stored procedure, however nothing comes back
> to the Java application.
> The mapper is then referenced via a DAO interface and a DAO
> implementation. However the value of parameter2 remains null and is never
> set by the procedure call.
> Is there something further that needs to be done here? Or have I made an
> error somewhere?
> Thanks in advance.
> On Tuesday, 9 October 2012 02:03:48 UTC+11, AntPort wrote:
> I made a mistake: Line #{returnValue,mode=OUT,jd**bcTypeName
> =NUMERIC,javaType=**java.lang.Integer} must be converted into #
> {returnValue,mode=OUT,jd**bcType=NUMERIC,javaType=java.**lang.Integer}
> Dana subota, 6. listopada 2012. 23:35:44 UTC+2, korisnik AntPort napisao
> je:
> 1.Type handler must be defined for every parameter. Here is one example:
> if we have wrapperObject having properties listOfMaps1,2,3 (**mentioned
> in my post above) and returnValue map snippet would look like this:
> <select id="callMyProcedure" statementType="CALLABLE"
> parameterType="wrapperObject">
> call
> my_proc(#{listOfMaps1,mode=IN,**typeHandler=package.Param1Type**Handler},
> #{listOfMaps2,mode=IN,**typeHandler=package.
> Param2Type**Handler},
> #{listOfMaps3,mode=IN,**typeHandler=package.
> Param3Type**Handler},
> #{returnValue,mode=OUT,**
> jdbcTypeName=NUMERIC,javaType=**java.lang.Integer})
> </select>
> If you have multiple parameters, you have to specify type handler for
> every one of them.
> 2. No. Only if you use same type handler for IN and OUT parameters.
> Integer doesn't need specific type handler, just define OUT parameter like
> in the example above.
> Dana petak, 5. listopada 2012. 16:30:47 UTC+2, korisnik grimmel97 napisao
> je:
> Hi,
> Thank you again for your time in trying to explain this to me. I will try
> to digest this and write the TypeHandler tomorrow. However for now I have
> a few more questions.
> 1. If I have to create a TypeHandler for each of my parameters, then in my
> original example I would have 3 type handlers, say
> Param1TypeHandler
> Param2TypeHandler
> Param3TypeHandler
> Can I specify more than one TypeHandler for the MyBatis SQLM
Thanks Jeff. I had read several posts regarding using a map for the parameters, however for some reason (perhaps coming from an Oracle background) I was just waiting for the OUT parameter to magically populate itself back through the map rather than getting it myself!
On Friday, 12 October 2012 22:43:25 UTC+11, Jeff Butler wrote:
> With OUT parameters you will need to make your own map to pass parameters > and not use the @Param annotation. After the call, the out value will be in > the map you pass.
> Jeff Butler
> On Friday, October 12, 2012, grimmel97 wrote:
>> Hi AntPort,
>> I spoke too soon. Now I have tested my procedure call is working, >> however it is the returning of the single OUT parameter, the Integer, which >> I thought would be the simplest part of the problem is now causing the >> issue. I have defined the out parameters as per your details below:
>> However, no data ever comes back. I have checked that the Integer value >> is correctly set in the Oracle stored procedure, however nothing comes back >> to the Java application.
>> The mapper is then referenced via a DAO interface and a DAO >> implementation. However the value of parameter2 remains null and is never >> set by the procedure call.
>> Is there something further that needs to be done here? Or have I made an >> error somewhere?
>> Thanks in advance.
>> On Tuesday, 9 October 2012 02:03:48 UTC+11, AntPort wrote:
>> I made a mistake: Line #{returnValue,mode=OUT,jd**bcTypeName >> =NUMERIC,javaType=**java.lang.Integer} must be converted into # >> {returnValue,mode=OUT,jd**bcType=NUMERIC,javaType=java.**lang.Integer}
>> Dana subota, 6. listopada 2012. 23:35:44 UTC+2, korisnik AntPort napisao >> je:
>> 1.Type handler must be defined for every parameter. Here is one example: >> if we have wrapperObject having properties listOfMaps1,2,3 (**mentioned >> in my post above) and returnValue map snippet would look like this:
>> <select id="callMyProcedure" statementType="CALLABLE" >> parameterType="wrapperObject"> >> call >> my_proc(#{listOfMaps1,mode=IN,**typeHandler=package.Param1Type**Handler}, >> #{listOfMaps2,mode=IN,**typeHandler=package. >> Param2Type**Handler}, >> #{listOfMaps3,mode=IN,**typeHandler=package. >> Param3Type**Handler}, >> #{returnValue,mode=OUT,** >> jdbcTypeName=NUMERIC,javaType=**java.lang.Integer}) >> </select> >> If you have multiple parameters, you have to specify type handler for >> every one of them.
>> 2. No. Only if you use same type handler for IN and OUT parameters. >> Integer doesn't need specific type handler, just define OUT parameter like >> in the example above.
>> Dana petak, 5. listopada 2012. 16:30:47 UTC+2, korisnik grimmel97 napisao >> je:
>> Hi,
>> Thank you again for your time in trying to explain this to me. I will >> try to digest this and write the TypeHandler tomorrow. However for now I >> have a few more questions.
>> 1. If I have to create a TypeHandler for each of my parameters, then in >> my original example I would have 3 type handlers, say
>> Param1TypeHandler
>> Param2TypeHandler
>> Param3TypeHandler
>> Can I specify more than one TypeHandler for the MyBatis SQLM
