Re: How to Pass Java List of POJO to Oracle Stored Procedure using MyBatis?

[AntPort]

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; 

         -------------------------------------------------------------------------------------------------------------------------------------------------------

         -------------------------------------------------------------------------------------------------------------------------------------------------------

         TypeHandler:

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;

STRUCT[] structArray = new STRUCT[inParams.size()];

conn = ps.getConnection();

StructDescriptor structDesc = StructDescriptor.createDescriptor(

"I_REZERVATION_TYPE", conn);

ArrayDescriptor arrayDesc = ArrayDescriptor.createDescriptor(

"I_REZERVATION_ARRAY", conn);

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:

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?

Thanks in advance.

[grimmel97]

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.

[grimmel97]

Oh one other thing, the 

structArray[valueCounter] = new STRUCT(structDesc, conn, properties); is causing me a compilation error as it says there is no suitable constructor that matches this.

[AntPort]

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

pojoMap.put("ID_RESERVATION",pojo.getIdReservation);

pojoMap.put("ID_BRANCH",pojo.getIdReservation);

pojoMap.put("ID_AGN",pojo.getIdReservation);

pojoMap.put("NAP_AGN",pojo.getIdReservation)

listOfPojoMaps.add(pojoMap);

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:

public class StoredProcedurePojo {

private ArrayList<LinkedHashMap<String, Object>> listOfPojoMaps;

private String typeName;

private String arrayName;

        //getters / setters

}

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:

                StoredProcedurePojo wrapperPojo = (StoredProcedurePojo) passedValue; 

List<LinkedHashMap<String,Object>> inParams = wrapperPojo.getListOfPojoMaps();

STRUCT[] structArray = new STRUCT[inParams.size()];

conn = ps.getConnection();

StructDescriptor structDesc = StructDescriptor.createDescriptor(

wrapperPojo.getTypeName(), conn);

ArrayDescriptor arrayDesc = ArrayDescriptor.createDescriptor(

wrapperPojo.getArrayName(), conn);

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...

[grimmel97]

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? 

<update id="callOracleSP" statementType="CALLABLE" typeHandler="Param1TypeHandler,Param2TypeHandler,Param3TypeHAndler">
    {CALL my_proc( #{param1, mode=IN},
                   #{param2, mode=IN},
                   #{param3, mode=IN}

                 )
    }
</update>

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.

[AntPort]

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.

[AntPort]

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}

[grimmel97]

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.

Thanks in advance!

MyBatis SQLMap:

    <update id="test" statementType="CALLABLE">

        {CALL test(#{pojoList, mode=IN, typeHandler=package.MyTypeHandler})

    </update>

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();

        

StructDescriptor structDesc = StructDescriptor.createDescriptor("ORACLE_COLLECTION_RT", conn);

ArrayDescriptor arrayDesc = ArrayDescriptor.createDescriptor("ORACLE_COLLECTION_T", conn);

int valueCounter = 0;

                

for (Map<String, Object> properties : listOfPojoMaps) {

structArray[valueCounter] = new STRUCT(structDesc, conn, properties);

valueCounter++;

}

ARRAY arrayOfType = new ARRAY(arrayDesc, conn, structArray);

ps.setArray(argNum, arrayOfType);

       }

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

[grimmel97]

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

[Dridi Boukelmoune]

Hi,

Does your type handler directly implement the interface ?
If you want to handle nulls you should rather extend the default type handler.

Dridi

--
Dridi Boukelmoune
Développeur/Formateur

GSM : +33 (0)6 17 91 14 23

[grimmel97]

Hi,

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!

[grimmel97]

Hi AntPort,

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.

You have been a life saver!!

Kind regards.

On Tuesday, 9 October 2012 02:03:48 UTC+11, AntPort wrote:

[grimmel97]

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:

#{returnValue,mode=OUT,jdbcType=NUMERIC,javaType=java.lang.Integer} 

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.

I have defined the mapper as such:

    <update id="mgTest" statementType="CALLABLE">

        {CALL oracleSP(#{parameter1, mode=IN, typeHandler=package.PojoTypeHandler},

                       #{parameter2, mode=OUT, jdbcType=NUMERIC, javaType=java.lang.Integer})

    </update>

Method in the mapper object as follows:

    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?

Thanks in advance.

On Tuesday, 9 October 2012 02:03:48 UTC+11, AntPort wrote:

[Jeff Butler]

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

[grimmel97]

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!

All good now.

[bidya parhy]

Hi Grimmel,

I am trying to do the same exact thing as you were doing. I am using Spring with myBatis. I have a Java Object which looks like below

Class request {
String x;
int y;
String z;
}

And I need to pass an Array of that class.

I am trying to implement as above. So my question is in the Dao impl can I just call

getSqlSessionWithStats().update("callManageResources", manageResourceRequest);  and will it work? or I need to make any other change ?