Hi All, I was writing an xml for using the formula attribute in the property tag. I want to query a table and set the returned value for a property. The XML that i wrote is given below. Can anyone tell me why the SQL query is not being executed at all. Is there any other settings to be done.
The state used in the where clause of formula is a field in the LogDTO


<class name="LogDTO" table="ACTIVATION_LOG" >
<composite-id>
<key-property name="vcNo" column="ALOG_VC"/>
<key-property name="externalId" column="ALOG_EXT_ID"/>
</composite-id>
<property name="stb" column="ALOG_STB" />
<property name="requestDate" column="ALOG_REQ_DATE_TIME" />
<property name="accountStatus" column="ALOG_ACC_STATUS" />
<property name="actionStatus" column="ALOG_ACT_STATUS" />
<property name="orderId" column="ALOG_ORDER_ID" />
<property name="pinCode" column="ALOG_PIN" />
<property name="statePK" column="ALOG_ST_PK" formula="(select st_pk from states where st_name = state)" />
<property name="districtPK" column="ALOG_DT_PK" />
<property name="costCenterPK" column="ALOG_CC_PK" />
<property name="source" column="ALOG_SRC" />
<property name="recievedDate" column="ALOG_REC_DATE_TIME" />
</class>

You can't have both a column and a formula attribute.

Okay so how can I retrive a value from a table based on a condition and use the same value to insert in another table. How should the formula attribute be used in such a situation.

Are you trying to calculate a default value?

No. Consider there is a table which contains string and corresponding int values. I need to retrive the int value for the a string parameter that i pass and the value which is retrived is to be inserted in another table. How can i achieve this. And how can i pass a dynamic parameter for the where condition of the sql query ?

So you need a State POJO with their mapping file.

And then you have to transform property statePK to a many-to-one.


An get rid of statePK property of LogDTO, and put instead a State state; property.

Right?

Okay so you are trying to say that I need to define one more DTO which describes the States table (which contains the string and int values) and use it in my LogDTO. Then how can i pass a dynamic parameter ie string to this POJO mapping. And is there any other way of solving the problem, coz i don't want to write one more DTO or mapping xml file. Because i need to retrive values from three such tables in the same query...

Sorry, but I don't understand it. Could you explain it a bit. Maybe with some code or some example.


SLOTH

Okay. See I have three tables all of them contain string and corresponding int values and some more fields the table names are states, disctricts and costcenter. Now there is a log table in which i need to insert logging related values. Before doing that i have to retrieve the int values corresponding to the strings parameters that i have from the above three tables.

private String vcNo = null;
private String externalId = null;
private String stb = null;
private String requestDate = null;
private String accountStatus = null;
private String actionStatus = null;
private String orderId = null;
private int pinCode;
private int statePK;
private int districtPK;
private int costCenterPK;
private String source = null;
private String recievedDate = null;
private String state = null;
private String temp = null;

These are the fields in my LogDTO and the three field statePK, districtPK and CostCenterPK values have to be set after querying the three tables. How should I write the mapping XML file to achieve this.

Usually it is the other way, you have the int and need the String.

Where do the Strings come from?

I get these strings from another program. And will set them in the DTO fields like the state field i described before. Now i need to access this value in the where clause and get the int value from the table. I'll just give you the code snippet.

ActivationLogDTO activationLogDTO = new ActivationLogDTO();
activationLogDTO.setVcNo("12999");
activationLogDTO.setExternalId("12999");
activationLogDTO.setStb("12349");
activationLogDTO.setRequestDate("10/07/2006");
activationLogDTO.setActionStatus("Y");
activationLogDTO.setAccountStatus("Y");
activationLogDTO.setOrderId("34737");
activationLogDTO.setPinCode(4564546);
//activationLogDTO.setStatePK(10);
activationLogDTO.setState("Karnataka");
activationLogDTO.setDistrictPK(10);
activationLogDTO.setCostCenterPK(12);
activationLogDTO.setSource("main");
activationLogDTO.setRecievedDate("10/07/2006");

System.out.println("Starting Hibernate for Activation Log..............");
HibernateUtil.updateDatabase(activationLogDTO);
System.out.println("Finishing Hibernate Activation Log.............");

The HibernateUtil is a class with a static method which implements the hibernate logic for insert the DTO values into the log table. In the above code see i've set the state field to 'Karnataka' now i have to pass this string as a parameter to the sql query and get back the int value. Which i'll have to insert in the logging table column ALOG_ST_PK. How can I achieve this....

I would made a

method in a StateDAO class.

Then it would be:


Do you know the DAO pattern?

Also, why do you name your business objects with the DTO suffix?

Thank you for that. I also thought about the same solution but was trying to find whether the same can be achieved in the mapping xml itself using the formula attribute. I didn't want to call a dao for that. And i've suffixed my class with DTO coz they act as Data Transfer Objects