ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Vadim <deadmo...@gmail.com>
Subject Re: result element order matters?
Date Fri, 07 Nov 2008 12:36:18 GMT
I don't really know how you manage to get an IntegrityConstraintsException
when performing selects. Updates and inserts, on the other hand, are
sensitive to parameter placement.
You might be referring to the exception being thrown on update when you feed
newly received object (using the faulty select) to the update or insert
procedure. If this is the case - do you perform an insert, and if you do -
do you generate new primary key?
I have never had problems with the ordering of columns of the resultset. Did
you try using implicit resultMap?

On Wed, Nov 5, 2008 at 7:23 PM, I L <isster@hotmail.com> wrote:

>  This took several days to figure out and is not intuitive. We assume it is
> a bug.
>
> We have a resultMap that has about 40 mappings. When we added a new column
> to the result map, we started getting an error where a "select *" would not
> include another unrelated column in the result set. To fix this, we moved
> that unrelated column mapping further down the resultMap and everything
> worked again.
>
> This is somewhat very difficult to explain so i'll try to do it via an
> example:
> Lets assume my result map is initially:
>
> <resultMap>
>       <result column="a"/>
>       <result column="b"/>
>       <result column="c"/>
> <resultMap>
>
> <select id="find" parameterClass="long" resultMap="result">
>         select * from example where id = #id:BIGINT#
> </select>
>
> if I run the select "find", I expect my result set to contain columns a,b
> and c.
>
> Then I added a new column x...
>
> <resultMap>
>       <result column="a"/>
>       <result column="x"/>
>       <result column="b"/>
>       <result column="c"/>
> <resultMap>
>
> When I run the select "find", it would return a result set of only columns
> a, x, c. It would ommit column b.
>
>
> But if I move column b to lets say after column c:
>
> <resultMap>
>       <result column="a"/>
>       <result column="x"/>
>       <result column="c"/>
>       <result column="b"/>
> <resultMap>
>
> Then the select "find" would return ALL the columns in the result set
> (a,b,c,x).
>
> I hope that made sense.
>
> Here is the actual files I am using. I had to move
> "constructionImprovementCostsAmount" to be after "pud" for the select find
> to work. When "constructionImprovementCostsAmount" used to be after
> "acquiredDate", "constructionImprovementCostsAmount" column was ommitted
> from the find result set and I would get a
>
> --- Cause:
> com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException: Column
> 'construction_improvement_costs_amount' cannot be null
>
> error because constructionImprovementCostsAmount was never sent back in the
> result set.
>
> ----------------------------
> xml
> ----------------------------
> <?xml version="1.0" encoding="UTF-8"?>
> <!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN" "
> http://ibatis.apache.org/dtd/sql-map-2.dtd">
> <sqlMap namespace="SubjectProperty">
>   <typeAlias alias="SubjectPropertyAlias"
> type="com.taggartsoftware.lura.business.model.SubjectProperty"/>
>
>   <resultMap class="SubjectPropertyAlias" id="SubjectPropertyResult">
>     <result column="subject_property.pid" jdbcType="BIGINT"
> property="pid"/>
>     <result column="subject_property.version" jdbcType="INTEGER"
> property="version"/>
>     <result column="subject_property.acquired_date" jdbcType="DATE"
> property="acquiredDate"/>
>     <result column="subject_property.financed_units_count"
> jdbcType="INTEGER" property="financedUnitsCount"/>
>     <result column="subject_property.land_estimated_value_amount"
> jdbcType="DECIMAL" property="landEstimatedValueAmount"/>
>     <result column="subject_property.land_original_cost_amount"
> jdbcType="DECIMAL" property="landOriginalCostAmount"/>
>     <result column="subject_property.leasehold_expiration_date"
> jdbcType="DATE" property="leaseholdExpirationDate"/>
>     <result column="subject_property.legal_description" jdbcType="VARCHAR"
> property="legalDescription"/>
>     <result column="subject_property.management_agent_name"
> jdbcType="VARCHAR" property="managementAgentName"/>
>     <result column="subject_property.monthly_gross_rental_amount"
> jdbcType="DECIMAL" property="monthlyGrossRentalAmount"/>
>     <result column="subject_property.monthly_net_rental_amount"
> jdbcType="DECIMAL" property="monthlyNetRentalAmount"/>
>     <result column="subject_property.net_rental_percent" jdbcType="DECIMAL"
> property="netRentalPercent"/>
>     <result column="subject_property.original_cost_amount"
> jdbcType="DECIMAL" property="originalCostAmount"/>
>     <result column="subject_property.ownership_percent" jdbcType="DECIMAL"
> property="ownershipPercent"/>
>     <result column="subject_property.place_pid" jdbcType="BIGINT"
> property="placePid"/>
>     <result column="subject_property.county_pid" jdbcType="BIGINT"
> property="countyPid"/>
>     <result column="subject_property.project_classification_type"
> jdbcType="VARCHAR" property="projectClassificationType"/>
>     <result column="subject_property.property_category_type"
> jdbcType="VARCHAR" property="propertyCategoryType"/>
>     <result column="subject_property.property_rights_type"
> jdbcType="VARCHAR" property="propertyRightsType"/>
>     <result column="subject_property.pud" jdbcType="BIT" property="pud"/>
>     <result column="subject_property.construction_improvement_costs_amount"
> jdbcType="DECIMAL" property="constructionImprovementCostsAmount"/>
>     <result column="subject_property.refinance_improvement_costs_amount"
> jdbcType="DECIMAL" property="refinanceImprovementCostsAmount"/>
>     <result column="subject_property.refinance_improvements_type"
> jdbcType="VARCHAR" property="refinanceImprovementsType"/>
>     <result
> column="subject_property.refinance_proposed_improvements_description"
> jdbcType="VARCHAR" property="refinanceProposedImprovementsDescription"/>
>     <result column="subject_property.structure_built_year"
> jdbcType="INTEGER" property="structureBuiltYear"/>
>     <result column="subject_property.title_manner_held_description"
> jdbcType="VARCHAR" property="titleMannerHeldDescription"/>
>     <result column="subject_property.deal_pid" jdbcType="BIGINT"
> property="dealPid"/>
>     <result column="subject_property.building_status_type"
> jdbcType="VARCHAR" property="buildingStatusType" />
>     <result column="subject_property.native_american_lands_type"
> jdbcType="VARCHAR" property="nativeAmericanLandsType" />
>     <result column="subject_property.community_land_trust" jdbcType="BIT"
> property="communityLandTrust" />
>     <result column="subject_property.inclusionary_zoning" jdbcType="BIT"
> property="inclusionaryZoning" />
>     <result column="subject_property.ownership_type" jdbcType="VARCHAR"
> property="ownershipType" />
>     <result column="subject_property.unique_dwelling_type"
> jdbcType="VARCHAR" property="uniqueDwellingType" />
>     <result column="subject_property.living_unit_count" jdbcType="INTEGER"
> property="livingUnitCount" />
>     <result column="subject_property.project_design_type"
> jdbcType="VARCHAR" property="projectDesignType" />
>     <result column="subject_property.new_mode" jdbcType="BIT"
> property="newMode"/>
>   </resultMap>
>
>     <resultMap class="SubjectPropertyAlias"
> id="SubjectPropertyWithTransients" extends="SubjectPropertyResult"
> groupBy="pid">
>         <result property="transientPlace" resultMap="Place.PlaceResult"/>
>     </resultMap>
>
>     <select id="find" parameterClass="long"
> resultMap="SubjectPropertyResult">
>         select *
>         from subject_property
>         where pid = #pid:BIGINT#
>     </select>
>
>     <select id="version" parameterClass="long" resultClass="int">
>         SELECT version FROM subject_property WHERE pid = #pid:BIGINT#
>     </select>
>
>     <delete id="delete" parameterClass="SubjectPropertyAlias">
>         delete from subject_property
>         where pid = #pid:BIGINT#
>     </delete>
>
>     <insert id="insert" parameterClass="SubjectPropertyAlias">
>         INSERT INTO subject_property
>         (
>             version,
>             acquired_date,
>             construction_improvement_costs_amount,
>             financed_units_count,
>             land_estimated_value_amount,
>             land_original_cost_amount,
>             leasehold_expiration_date,
>             legal_description,
>             management_agent_name,
>             monthly_gross_rental_amount,
>             monthly_net_rental_amount,
>             net_rental_percent,
>             original_cost_amount,
>             ownership_percent,
>             place_pid,
>             county_pid,
>             project_classification_type,
>             property_category_type,
>             property_rights_type,
>             pud,
>             refinance_improvement_costs_amount,
>             refinance_improvements_type,
>             refinance_proposed_improvements_description,
>             structure_built_year,
>             title_manner_held_description,
>             deal_pid,
>             building_status_type,
>             native_american_lands_type,
>             community_land_trust,
>             inclusionary_zoning,
>             ownership_type,
>             unique_dwelling_type,
>             living_unit_count,
>             project_design_type,
>             new_mode
>         )
>         VALUES
>         (
>             #version:INTEGER#,
>             #acquiredDate:DATE#,
>             #constructionImprovementCostsAmount:DECIMAL#,
>             #financedUnitsCount:INTEGER#,
>             #landEstimatedValueAmount:DECIMAL#,
>             #landOriginalCostAmount:DECIMAL#,
>             #leaseholdExpirationDate:DATE#,
>             #legalDescription:VARCHAR#,
>             #managementAgentName:VARCHAR#,
>             #monthlyGrossRentalAmount:DECIMAL#,
>             #monthlyNetRentalAmount:DECIMAL#,
>             #netRentalPercent:DECIMAL#,
>             #originalCostAmount:DECIMAL#,
>             #ownershipPercent:DECIMAL#,
>             #placePid:BIGINT#,
>             #countyPid:BIGINT#,
>             #projectClassificationType:VARCHAR#,
>             #propertyCategoryType:VARCHAR#,
>             #propertyRightsType:VARCHAR#,
>             #pud:BIT#,
>             #refinanceImprovementCostsAmount:DECIMAL#,
>             #refinanceImprovementsType:VARCHAR#,
>             #refinanceProposedImprovementsDescription:VARCHAR#,
>             #structureBuiltYear:INTEGER#,
>             #titleMannerHeldDescription:VARCHAR#,
>             #dealPid:BIGINT#,
>             #buildingStatusType:VARCHAR#,
>             #nativeAmericanLandsType:VARCHAR#,
>             #communityLandTrust:BIT#,
>             #inclusionaryZoning:BIT#,
>             #ownershipType:VARCHAR#,
>             #uniqueDwellingType:VARCHAR#,
>             #livingUnitCount:INTEGER#,
>             #projectDesignType:VARCHAR#,
>             #newMode:BIT#
>         )
>         <selectKey keyProperty="pid" resultClass="java.lang.Long"
> type="post">
>             SELECT LAST_INSERT_ID()
>         </selectKey>
>     </insert>
>
>     <update id="update" parameterClass="SubjectPropertyAlias">
>         UPDATE subject_property SET
>             version = #version:INTEGER#,
>             acquired_date = #acquiredDate:DATE#,
>             construction_improvement_costs_amount =
> #constructionImprovementCostsAmount:DECIMAL#,
>             financed_units_count = #financedUnitsCount:INTEGER#,
>             land_estimated_value_amount =
> #landEstimatedValueAmount:DECIMAL#,
>             land_original_cost_amount = #landOriginalCostAmount:DECIMAL#,
>             leasehold_expiration_date = #leaseholdExpirationDate:DATE#,
>             legal_description = #legalDescription:VARCHAR#,
>             management_agent_name = #managementAgentName:VARCHAR#,
>             monthly_gross_rental_amount =
> #monthlyGrossRentalAmount:DECIMAL#,
>             monthly_net_rental_amount = #monthlyNetRentalAmount:DECIMAL#,
>             net_rental_percent = #netRentalPercent:DECIMAL#,
>             original_cost_amount = #originalCostAmount:DECIMAL#,
>             ownership_percent = #ownershipPercent:DECIMAL#,
>             place_pid = #placePid:BIGINT#,
>             county_pid = #countyPid:BIGINT#,
>             project_classification_type =
> #projectClassificationType:VARCHAR#,
>             property_category_type = #propertyCategoryType:VARCHAR#,
>             property_rights_type = #propertyRightsType:VARCHAR#,
>             pud = #pud:BIT#,
>             refinance_improvement_costs_amount =
> #refinanceImprovementCostsAmount:DECIMAL#,
>             refinance_improvements_type =
> #refinanceImprovementsType:VARCHAR#,
>             refinance_proposed_improvements_description =
> #refinanceProposedImprovementsDescription:VARCHAR#,
>             structure_built_year = #structureBuiltYear:INTEGER#,
>             title_manner_held_description =
> #titleMannerHeldDescription:VARCHAR#,
>             deal_pid = #dealPid:BIGINT#,
>             building_status_type = #buildingStatusType:VARCHAR#,
>             native_american_lands_type = #nativeAmericanLandsType:VARCHAR#,
>
>             community_land_trust = #communityLandTrust:BIT#,
>             inclusionary_zoning = #inclusionaryZoning:BIT#,
>             ownership_type = #ownershipType:VARCHAR#,
>             unique_dwelling_type = #uniqueDwellingType:VARCHAR#,
>             living_unit_count = #livingUnitCount:INTEGER#,
>             project_design_type = #projectDesignType:VARCHAR#,
>             new_mode = #newMode:BIT#
>         WHERE
>             pid = #pid:BIGINT#
>     </update>
>
>     <select id="findByDeal" parameterClass="long"
> resultMap="SubjectPropertyResult">
>         SELECT *
>         FROM subject_property
>         WHERE deal_pid = #dealPid:BIGINT#
>     </select>
>
>     <select id="findByDealWithTransients" parameterClass="long"
> resultMap="SubjectPropertyWithTransients">
>         SELECT *
>         FROM
>             subject_property,
>             place
>         WHERE
>             deal_pid = #dealPid:BIGINT#
>             AND place_pid = place.pid
>     </select>
>
>     <select id="findByPlace" parameterClass="long"
> resultMap="SubjectPropertyResult">
>         SELECT * FROM subject_property WHERE place_pid = #placePid:BIGINT#
>     </select>
>
>     <select id="findByApplication" parameterClass="long"
> resultMap="SubjectPropertyResult">
>         SELECT *
>         FROM subject_property, application
>         WHERE application.pid = #applicationPid:BIGINT# AND
> application.deal_pid=subject_property.deal_pid
>     </select>
> </sqlMap>
>
>
>
>
>
> ----------------------------
> sql
> ----------------------------
>
>     create table subject_property (
>         pid bigint not null auto_increment,
>         version integer not null,
>         acquired_date date,
>         construction_improvement_costs_amount numeric(15,0) not null,
>         financed_units_count integer,
>         land_estimated_value_amount numeric(15,0) not null,
>         land_original_cost_amount numeric(15,0) not null,
>         leasehold_expiration_date date,
>         legal_description varchar(1024),
>         management_agent_name varchar(128),
>         monthly_gross_rental_amount numeric(15,2) not null,
>         monthly_net_rental_amount numeric(15,2) not null,
>         net_rental_percent numeric(11,9) not null,
>         original_cost_amount numeric(15,0) not null,
>         ownership_percent numeric(11,9) not null,
>         place_pid bigint not null,
>         county_pid bigint,
>         project_classification_type varchar(32),
>         property_category_type varchar(32),
>         property_rights_type varchar(32),
>         pud bit,
>         refinance_improvement_costs_amount numeric(15,0) not null,
>         refinance_improvements_type varchar(32),
>         refinance_proposed_improvements_description varchar(32),
>         structure_built_year integer,
>         title_manner_held_description varchar(256),
>         deal_pid bigint not null,
>         building_status_type varchar(32) not null,
>         native_american_lands_type varchar(32) not null,
>         community_land_trust bit,
>         inclusionary_zoning bit,
>         ownership_type varchar(32) not null,
>         unique_dwelling_type varchar(32) not null,
>         living_unit_count integer,
>         project_design_type varchar(32) not null,
>         new_mode bit,
>         primary key (pid)
>     ) ENGINE=InnoDB;
>
>
>
> ------------------------------
> Stay up to date on your PC, the Web, and your mobile phone with Windows
> Live Click here <http://clk.atdmt.com/MRT/go/119462413/direct/01/>
>

Mime
View raw message