ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From I L <iss...@hotmail.com>
Subject RE: result element order matters?
Date Sat, 08 Nov 2008 01:05:27 GMT

Hi Vadim,



You are correct on the insert. I didn't tell the entire story on how I received the IntegrityConstraintsException.



The db column constructionImprovementCostsAmount has a not null constraint.



Lets say it currently has a value of 33.



When I do a select, I get a null value instead.

The problem came to my attention when I attempt to re-save the column. That's how I managed
to get the IntegrityConstraintsException. So the error doesn't happen on a SELECT. Sorry to
mislead you.



I am going to create a small program that attempts to replicate this
problem. Are you interested  at running this program when complete?
Where should I send it?



thx.

Date: Fri, 7 Nov 2008 14:36:18 +0200
From: deadmoro3@gmail.com
To: user-java@ibatis.apache.org
Subject: Re: result element order matters?

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



_________________________________________________________________
Windows Live Hotmail now works up to 70% faster.
http://windowslive.com/Explore/Hotmail?ocid=TXT_TAGLM_WL_hotmail_acq_faster_112008
Mime
View raw message