CODEWITHSUNDEEP
javahibernatedata-bindingtype-conversionjava-stored-procedures
Dima R.
Dima R.

Reputation: 997

Mapping hibernate datatypes to output from stored procedure of mixed types

Have a problem mapping datatypes from stored procedure output to a java datatype

Using a SQLServer, I am given the following 2 tables initially - can't change their definitions.

CREATE TABLE [dbo].[tblDetail](
    ...
[dCdeType] [dbo].[Tcde] NOT NULL,
    [dCdeState] [dbo].[Tvch] NOT NULL,
    ...
)

CREATE TABLE [dbo].[tblAP](
    ...
[apChState] [char](2) NOT NULL, 
[apChTaxType] [char](2) NOT NULL,
    ...
)

I have created a 3rd table - whose definition I can change if I have to.

CREATE TABLE [dbo].[tblTempAP](
    ...
[tpChState] [char](2) NOT NULL, 
[tpChTaxType] [char](2) NOT NULL,
    ...
)

I have a stored procedure "sp_MyProcedure", that is called multiple times from Java application using Hibernate (version 3.1.3).
Stored procedure makes a select from tblDetails and inserts records into tblTempAP. And then it does the same from tblAP

select <list of fiields> into #tempD from tblDetails
...some logic...
INSERT INTO [dbo].[tblTempAP]
       (...
       ,[tpChType]
       ,[tpChState]
        ...
  )
select <list of fields> from #TempD
............some logic .....

select <list of fiields> into #tempA from tblAP
...
    INSERT INTO [dbo].[tblTempAP]
       (...
       ,[tpChType]
       ,[tpChState]
        ...
  )
select <list of fields> from #TempA

There can be "Updates" as well along "some logic" to the table tblTempAP based on some criterias. But the 2 fields I am questioning - datatypes are the same. "sp_MyProcedure" us called in a loop from Java with different parameters every time, so it is guaranteed that data inserted into tblTempAP is different every time.

At the end of the stored procedure, i do 

select <some fileds, including the 2 mentioned> 
from tblTempAP
where <some criterias>

Output is taken in Java and processed, accumulated based on business logic.

Java code

String queryString = "{ call sp_MyProcedure(?) }";
Query q;
    List resultList;
    try {
       q = session.createSQLQuery(queryString);
       q.setInteger(0, port);
               resultList = q.list();
    } 
    catch (HibernateException hex) {
        throw new TaxPartnersSystemException("My Exception");
    }

Here is the problem, I need help with !!! At the end of the application run, I have populated tblTempAP, and it looks fine when looked at in SQL server management studio. However, the data in resultList on the java side has a problem - I see it with debugger, inspecting elements - The state that came has ONLY one character - so whatever in the tblTempAP is "NY" would be "N", "CA" - would be "C". Same for type field. Those which were single character were correct. However, something like "03" - would be "0", "34" would come out as "3".

I think it's a mapping inside hibernate API

Please, help !! Where is problem? And how to figure it out? Remember, I can't change the 2 initial tables - it's a given.

I do not use Cast or Convert in stored procedure. If I should, please tell me where ? when selecting from first 2 tables? or when insert/updating the 3rd ?

Thanks in advance

Upvotes: 1

Views: 1517

Answers (1)

Faresis
Faresis

Reputation: 103

I have resolved such a problem using Hibernate Result Transformer functionality. You just need to extend SQL query in the next way:

select RES1 as PROP1, RES2 as PROP2 from Table
        (select sp_MyProcedure(?) from dual)

And then create result POJO:

class MyProcRes {
    public String getPROP1() {}
    public void setPROP1(String prop1) {}

    // same for PROP2 + constructors default and one with props
}

And finally you can apply result transformation to the results of the query:

List<MyProcRes> res = session.createSQLQuery(query).setResultTransformer(
                          Transformers.aliasToBean(MyProcRes.class)
                      ).list();

And you will get all fields initialized in MyProcRes objects.

Upvotes: 1

Related Questions