Menu
During insertion in hibernate query i'm passing some fields as table class objects which i've mapped to corresponding tables, The query works fine but the query is becoming too large because each of these mapped objects are getting updated individually to their corresponding tables.
Can anyone please tell me whether this is the correct way of insertion and also why i'm getting those update queries.
Hibernate: insert into ortms.tool_modified_his_tbl (tool_desc, old_tool_desc, connec1, old_connec1, connec2, old_connec2, landed_cost, old_landed_cost, acqui_date, old_acqui_date, manuf_date, old_manuf_date, price_ref, old_price_ref, op_rate_cost, old_op_rate_cost, stb_rate_cost, old_stb_rate_cost, day_rate1_cost, old_day_rate1_cost, day_rate2_cost, old_day_rate2_cost, packermilling_cost, old_packermilling_cost, sale_value, old_sale_value, status, created_date, modified_date, tool_id, tool_modi_reas_id, tool_modi_usr_id, supplier_id, old_supplier_id, tc_status_id, old_tc_status_id, pricing_type_id, old_pricing_type_id, old_ownership_id, ownership_id, unit_id, old_unit_id, branch_id, old_branch_id, reta_tool_choice_id, old_reta_tool_choice_id, non_ser_tol_cho_id, old_non_ser_tol_cho_id, charge_by_id, old_charge_by_id, size_range_id, old_size_range_id, rack_id, old_rack_id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Hibernate: update ortms.master2_tool_master set tool_id=?, tool_desc=?, connec1=?, connec2=?, landed_cost=?, acqui_date=?, manuf_date=?, price_ref=?, op_rate_cost=?, stb_rate_cost=?, day_rate1_cost=?, day_rate2_cost=?, packermilling_cost=?, sale_value=?, uploaded_filename=?, uploaded_file=?, status=?, created_date=?, modified_date=?, supplier_id=?, tc_status_id=?, pricing_type_id=?, unit_id=?, reta_tool_choice_id=?, non_ser_tol_cho_id=?, branch_id=?, charge_by_id=?, size_range_id=?, rack_id=?, ownership_id=? where id=?
Hibernate: update ortms.table_users set user_code=?, username=?, password=?, first_name=?, last_name=?, status=?, created_date=?, modified_date=?, dept_id=?, branch_id=? where id=?
Hibernate: update ortms.table_choice_select set choice_name=?, status=?, created_date=?, modified_date=? where id=?
Hibernate: update ortms.master2_toolmast_chargeby set chargeby=?, status=?, created_date=?, modified_date=? where id=?
Hibernate: update ortms.master2_sizerange set size_code=?, size_range=?, status=?, created_date=?, modified_date=?, grp_lev3_id=? where id=?
Hibernate: update ortms.master2_group_level3 set grp_lev3_name=?, grp_lev3_desc=?, created_date=?, modified_date=?, status=?, grp_lev2_id=? where id=?
Hibernate: update ortms.master2_group_level2 set grp_lev2_name=?, grp_lev2_desc=?, created_date=?, modified_date=?, status=?, grp_lev1_id=? where id=?
After calling action: master2.toolmaster.ToolMaster Time taken: 1234 ms
UPDATION 2
ToolModifiedHisTbl.hbm.xml
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!-- Generated Mar 2, 2013 9:29:05 PM by Hibernate Tools 3.2.1.GA -->
<hibernate-mapping>
<class name="mappingfiles.ToolModifiedHisTbl" table="tool_modified_his_tbl" catalog="ortms">
<id name="id" type="java.lang.Integer">
<column name="id" />
<generator class="identity" />
</id>
<property name="toolDesc" type="string">
<column name="tool_desc" length="65535" />
</property>
<property name="oldToolDesc" type="string">
<column name="old_tool_desc" length="65535" />
</property>
<property name="connec1" type="string">
<column name="connec1" length="60" />
</property>
<property name="oldConnec1" type="string">
<column name="old_connec1" length="60" />
</property>
<property name="connec2" type="string">
<column name="connec2" length="60" />
</property>
<property name="oldConnec2" type="string">
<column name="old_connec2" length="60" />
</property>
<property name="landedCost" type="string">
<column name="landed_cost" length="20" />
</property>
<property name="oldLandedCost" type="string">
<column name="old_landed_cost" length="20" />
</property>
<property name="acquiDate" type="date">
<column name="acqui_date" length="10" />
</property>
<property name="oldAcquiDate" type="date">
<column name="old_acqui_date" length="10" />
</property>
<property name="manufDate" type="date">
<column name="manuf_date" length="10" />
</property>
<property name="oldManufDate" type="date">
<column name="old_manuf_date" length="10" />
</property>
<property name="priceRef" type="string">
<column name="price_ref" length="20" />
</property>
<property name="oldPriceRef" type="string">
<column name="old_price_ref" length="20" />
</property>
<property name="opRateCost" type="string">
<column name="op_rate_cost" length="20" />
</property>
<property name="oldOpRateCost" type="string">
<column name="old_op_rate_cost" length="20" />
</property>
<property name="stbRateCost" type="string">
<column name="stb_rate_cost" length="20" />
</property>
<property name="oldStbRateCost" type="string">
<column name="old_stb_rate_cost" length="20" />
</property>
<property name="dayRate1Cost" type="string">
<column name="day_rate1_cost" length="20" />
</property>
<property name="oldDayRate1Cost" type="string">
<column name="old_day_rate1_cost" length="20" />
</property>
<property name="dayRate2Cost" type="string">
<column name="day_rate2_cost" length="20" />
</property>
<property name="oldDayRate2Cost" type="string">
<column name="old_day_rate2_cost" length="20" />
</property>
<property name="packermillingCost" type="string">
<column name="packermilling_cost" length="20" />
</property>
<property name="oldPackermillingCost" type="string">
<column name="old_packermilling_cost" length="20" />
</property>
<property name="saleValue" type="string">
<column name="sale_value" length="20" />
</property>
<property name="oldSaleValue" type="string">
<column name="old_sale_value" length="20" />
</property>
<property name="status" type="string">
<column name="status" length="20" />
</property>
<property name="createdDate" type="timestamp">
<column name="created_date" length="19" />
</property>
<property name="modifiedDate" type="timestamp">
<column name="modified_date" length="19" />
</property>
<many-to-one class="mappingfiles.Master2ToolMaster" unique="true" name="toolId" column="tool_id" cascade="all" />
<many-to-one class="mappingfiles.TableModifiedReason" unique="true" name="toolModiReasId" column="tool_modi_reas_id" cascade="all" />
<many-to-one class="mappingfiles.TableUsers" unique="true" name="toolModiUsrId" column="tool_modi_usr_id" cascade="all" />
<many-to-one class="mappingfiles.TableSupplier" unique="true" name="supplierId" column="supplier_id" cascade="all" />
<many-to-one class="mappingfiles.TableSupplier" unique="true" name="oldSupplierId" column="old_supplier_id" cascade="all" />
<many-to-one class="mappingfiles.TableToolContractStatus" unique="true" name="tcStatusId" column="tc_status_id" cascade="all" />
<many-to-one class="mappingfiles.TableToolContractStatus" unique="true" name="oldTcStatusId" column="old_tc_status_id" cascade="all" />
<many-to-one class="mappingfiles.Master2PriceType" unique="true" name="pricingTypeId" column="pricing_type_id" cascade="all" />
<many-to-one class="mappingfiles.Master2PriceType" unique="true" name="oldPricingTypeId" column="old_pricing_type_id" cascade="all" />
<many-to-one class="mappingfiles.TableOwnership" unique="true" name="oldOwnershipId" column="old_ownership_id" cascade="all" />
<many-to-one class="mappingfiles.TableOwnership" unique="true" name="ownershipId" column="ownership_id" cascade="all" />
<many-to-one class="mappingfiles.Master2UnitMaster" unique="true" name="unitId" column="unit_id" cascade="all" />
<many-to-one class="mappingfiles.Master2UnitMaster" unique="true" name="oldUnitId" column="old_unit_id" cascade="all" />
<many-to-one class="mappingfiles.TableBranchesCompany" unique="true" name="branchId" column="branch_id" cascade="all" />
<many-to-one class="mappingfiles.TableBranchesCompany" unique="true" name="oldBranchId" column="old_branch_id" cascade="all" />
<many-to-one class="mappingfiles.TableChoiceSelect" unique="true" name="retaToolChoiceId" column="reta_tool_choice_id" cascade="all" />
<many-to-one class="mappingfiles.TableChoiceSelect" unique="true" name="oldRetaToolChoiceId" column="old_reta_tool_choice_id" cascade="all" />
<many-to-one class="mappingfiles.TableChoiceSelect" unique="true" name="nonSerTolChoId" column="non_ser_tol_cho_id" cascade="all" />
<many-to-one class="mappingfiles.TableChoiceSelect" unique="true" name="oldNonSerTolChoId" column="old_non_ser_tol_cho_id" cascade="all" />
<many-to-one class="mappingfiles.Master2ToolmastChargeby" unique="true" name="chargeById" column="charge_by_id" cascade="all" />
<many-to-one class="mappingfiles.Master2ToolmastChargeby" unique="true" name="oldChargeById" column="old_charge_by_id" cascade="all" />
<many-to-one class="mappingfiles.Master2Sizerange" unique="true" name="sizeRangeId" column="size_range_id" cascade="all" />
<many-to-one class="mappingfiles.Master2Sizerange" unique="true" name="oldSizeRangeId" column="old_size_range_id" cascade="all" />
<many-to-one class="mappingfiles.Master2Racks" unique="true" name="rackId" column="rack_id" cascade="all" />
<many-to-one class="mappingfiles.Master2Racks" unique="true" name="oldRackId" column="old_rack_id" cascade="all" />
</class>
</hibernate-mapping>
TableUsers.hbm.xml
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!-- Generated Jan 13, 2013 4:26:04 PM by Hibernate Tools 3.2.1.GA -->
<hibernate-mapping>
<class name="mappingfiles.TableUsers" table="table_users" catalog="ortms">
<id name="id" type="java.lang.Integer">
<column name="id" />
<generator class="identity" />
</id>
<property name="userCode" type="string">
<column name="user_code" length="60" />
</property>
<property name="username" type="string">
<column name="username" length="50" />
</property>
<property name="password" type="string">
<column name="password" length="50" />
</property>
<property name="firstName" type="string">
<column name="first_name" length="60" />
</property>
<property name="lastName" type="string">
<column name="last_name" length="60" />
</property>
<property name="status" type="string">
<column name="status" length="20" />
</property>
<property name="createdDate" type="timestamp">
<column name="created_date" length="19" />
</property>
<property name="modifiedDate" type="timestamp">
<column name="modified_date" length="19" />
</property>
<many-to-one class="mappingfiles.TableDepartments" unique="true" name="deptId" column="dept_id" cascade="all" />
<many-to-one class="mappingfiles.TableBranchesCompany" unique="true" name="branchId" column="branch_id" cascade="all" />
</class>
</hibernate-mapping>
UPDATION 3
i've removed cascade="all" attribute from all mappings....now i'm getting one update rest all clear
Hibernate: insert into ortms.tool_modified_his_tbl (tool_desc, old_tool_desc, connec1, old_connec1, connec2, old_connec2, landed_cost, old_landed_cost, acqui_date, old_acqui_date, manuf_date, old_manuf_date, price_ref, old_price_ref, op_rate_cost, old_op_rate_cost, stb_rate_cost, old_stb_rate_cost, day_rate1_cost, old_day_rate1_cost, day_rate2_cost, old_day_rate2_cost, packermilling_cost, old_packermilling_cost, sale_value, old_sale_value, status, created_date, modified_date, tool_id, tool_modi_reas_id, tool_modi_usr_id, supplier_id, old_supplier_id, tc_status_id, old_tc_status_id, pricing_type_id, old_pricing_type_id, old_ownership_id, ownership_id, unit_id, old_unit_id, branch_id, old_branch_id, reta_tool_choice_id, old_reta_tool_choice_id, non_ser_tol_cho_id, old_non_ser_tol_cho_id, charge_by_id, old_charge_by_id, size_range_id, old_size_range_id, rack_id, old_rack_id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Hibernate: update ortms.master2_tool_master set tool_id=?, tool_desc=?, connec1=?, connec2=?, landed_cost=?, acqui_date=?, manuf_date=?, price_ref=?, op_rate_cost=?, stb_rate_cost=?, day_rate1_cost=?, day_rate2_cost=?, packermilling_cost=?, sale_value=?, uploaded_filename=?, uploaded_file=?, status=?, created_date=?, modified_date=?, supplier_id=?, tc_status_id=?, pricing_type_id=?, unit_id=?, reta_tool_choice_id=?, non_ser_tol_cho_id=?, branch_id=?, charge_by_id=?, size_range_id=?, rack_id=?, ownership_id=? where id=?
649
Summary: save() method saves records into database by INSERT SQL query, Generates a new identifier and return the Serializable identifier back. On the other hand saveOrUpdate() method either INSERT or UPDATE based upon existence of object in database.
Using Session#persist() and Session#save() The Session interface provides many methods to perform CRUD operations on an Entity. Its persist() method will save the entity into the database. Session session = sessionFactory.
Hibernate Query Language (HQL) is an object-oriented query language, similar to SQL, but instead of operating on tables and columns, HQL works with persistent objects and their properties. HQL queries are translated by Hibernate into conventional SQL queries, which in turns perform an action on a database.
The unwanted updates come from the "cascade all" settings. You may want to configure it properly according to your needs. For example, have a look here: http://www.mkyong.com/hibernate/hibernate-cascade-example-save-update-delete-and-delete-orphan/
160
Use dynamic-insert=true and dynamic-update=true at class level
After this your query will be short. It will insert and update only the specified not null values.
35
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
