Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

There is case that a TypeHandler is incorrectly selected for a nested select query parameter #1551

Closed
dezhiShen opened this issue May 30, 2019 · 7 comments · Fixed by #1693
Closed
Assignees
Labels
bug
Milestone

Comments

@dezhiShen
Copy link

@dezhiShen dezhiShen commented May 30, 2019

there are two table with the same column name 'ID' and without the same type

    <resultMap id="BaseResultMap" type="com.geco.domain.MdProduct">
        <id column="ID" property="id" jdbcType="VARCHAR" />
        ...
     </resultMap>
    <resultMap id="InfoResultMap" extends="BaseResultMap"  type="com.geco.service.product.dto.ProductResp">
        <result column="INFO_ID" property="additionalInfo.id" jdbcType="DECIMAL"/>
      ....
       <collection property="skus"
                    select="selectSkuByPk1" column="ID"/>
    </resultMap>
<select id="select resultMap="InfoResultMap">

   select p.id ,i.ID as INFO_ID from md_product,md_product_info i
where ....
</select>

there are error info

org.apache.ibatis.executor.result.ResultMapException: Error attempting to get column 'ID' from result set.  Cause: java.sql.SQLException: Invalid value for getLong() - '82620ef6659811e9809900163e02c6cf'

I debug it and try fix it

class:  org.apache.ibatis.type.UnknownTypeHandler
method:  private TypeHandler<?> resolveTypeHandler(ResultSet rs, String column)

old code:

private TypeHandler<?> resolveTypeHandler(ResultSet rs, String column) {
        try {
            Map<String, Integer> columnIndexLookup = new HashMap();
            ResultSetMetaData rsmd = rs.getMetaData();
            int count = rsmd.getColumnCount();

            for (int i = 1; i <= count; ++i) {
                String name = rsmd.getColumnName(i);
                columnIndexLookup.put(name, i);
            }

            Integer columnIndex = (Integer) columnIndexLookup.get(column);
            TypeHandler<?> handler = null;
            if (columnIndex != null) {
                handler = this.resolveTypeHandler(rsmd, columnIndex);
            }

            if (handler == null || handler instanceof UnknownTypeHandler) {
                handler = OBJECT_TYPE_HANDLER;
            }

            return (TypeHandler) handler;
        } catch (SQLException var8) {
            throw new TypeException("Error determining JDBC type for column " + column + ".  Cause: " + var8, var8);
        }
    }

this is my code:

private TypeHandler<?> resolveTypeHandler(ResultSet rs, String column) {
        try {
            Map<String, Integer> columnIndexLookup = new HashMap();
            ResultSetMetaData rsmd = rs.getMetaData();
            int count = rsmd.getColumnCount();

            for (int i = 1; i <= count; ++i) {
                String name = rsmd.getColumnLabel(i);
                columnIndexLookup.put(name, i);
            }

            Integer columnIndex = (Integer) columnIndexLookup.get(column);
            TypeHandler<?> handler = null;
            if (columnIndex != null) {
                handler = this.resolveTypeHandler(rsmd, columnIndex);
            }

            if (handler == null || handler instanceof UnknownTypeHandler) {
                handler = OBJECT_TYPE_HANDLER;
            }

            return (TypeHandler) handler;
        } catch (SQLException var8) {
            throw new TypeException("Error determining JDBC type for column " + column + ".  Cause: " + var8, var8);
        }
    }

getColumnName = > getColumnLabel

it works but i don't know there are any other bugs after i fixed this bug

@harawata

This comment has been minimized.

Copy link
Member

@harawata harawata commented May 31, 2019

Hi @dezhiShen ,

Could you provide a demo project with a minimum set of tables/columns?
Thanks in advance!

@dezhiShen

This comment has been minimized.

Copy link
Author

@dezhiShen dezhiShen commented Jun 22, 2019

@harawata
Table

create table product
(
id varchar(32) not null,
code varchar(80) not null,
name varchar(240) not null
);
create table product_sku
(
id varchar(32) not null,
product_id varchar(32) not null,
code varchar(80) not null,
color varchar(40),
size varchar(40)
);
create table product_info
(
id int(11)  not null,
product_id varchar(32) not null,
other_info varchar(240) 
);

ProductSku.class

public class ProductSku {
    private String id;
    private String color;
    private String size;
    private String productId;

    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }

    public String getColor() {
        return color;
    }

    public void setColor(String color) {
        this.color = color;
    }

    public String getSize() {
        return size;
    }

    public void setSize(String size) {
        this.size = size;
    }

    public String getProductId() {
        return productId;
    }

    public void setProductId(String productId) {
        this.productId = productId;
    }
}

ProductInfo.class

public class ProductInfo {
    private Long id;
    private String productId;
    private String otherInfo;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getProductId() {
        return productId;
    }

    public void setProductId(String productId) {
        this.productId = productId;
    }

    public String getOtherInfo() {
        return otherInfo;
    }

    public void setOtherInfo(String otherInfo) {
        this.otherInfo = otherInfo;
    }
}

ProductResp.class

public class ProductResp {
    private String id;
    private String code;
    private String name;
    private List<ProductSku> skus;
    private ProductInfo productInfo;

    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }

    public String getCode() {
        return code;
    }

    public void setCode(String code) {
        this.code = code;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public List<ProductSku> getSkus() {
        return skus;
    }

    public void setSkus(List<ProductSku> skus) {
        this.skus = skus;
    }

    public ProductInfo getProductInfo() {
        return productInfo;
    }

    public void setProductInfo(ProductInfo productInfo) {
        this.productInfo = productInfo;
    }
}

ProductMapper.xml

<mapper namespace="ProductMapper">
   <resultMap id="AllInfoResultMap" type="ProductResp">
        <id column="ID" property="id" jdbcType="VARCHAR"/>
        <result column="NAME" property="name" jdbcType="VARCHAR"/>
        <result column="CODE" property="code" jdbcType="VARCHAR"/>
        <result column="INFO_ID" property="productInfo.id" jdbcType="DECIMAL"/>
        <result column="INFO_OTHER_INFO" property="productInfo.otherInfo" jdbcType="VARCHAR"/>
        <collection property="skus"
                    select="selectSkuByPk1" column="ID"/>
    </resultMap>
    <select id="selectAllInfo" resultMap="AllInfoResultMap">
         select p.id,
                   p.code,
                   p.name,
                   i.id as INFO_ID,
                   i.other_info as INFO_OTHER_INFO
          from product p left join product_info i
              on p.id = i.product_id
         where p.code = #{code,jdbcType=VARCHAR}
     </select>
    <resultMap id="SkuResultMap" type="ProductSku">
        <id column="ID" property="id" jdbcType="VARCHAR"/>
        <result column="PRODUCT_ID" property="productId" jdbcType="VARCHAR"/>
        <result column="COLOR" property="color" jdbcType="VARCHAR"/>
        <result column="SIZE" property="size" jdbcType="VARCHAR"/>
    </resultMap>
     <select id="selectSkuByPk1" resultMap="SkuResultMap">
          select * from product_sku
           where product_id = #{id,jdbcType=VARCHAR}
     </select>
</mapper>

ProductMapper.class

public interface ProductMapper {
    List<ProductResp> selectAllInfo(@Param("code") String code);
}  

Call function selectAllInfo

 <collection property="skus"
                    select="selectSkuByPk1" column="ID"/>

This code will use ID when there are two column that their names are ID without same type.

@harawata

This comment has been minimized.

Copy link
Member

@harawata harawata commented Jun 22, 2019

Thanks for the update, @dezhiShen !
I think I understood the problem.

Actually, MyBatis should check the value of useColumnLabel setting.

  • when useColumnLabel=true (default), getColumnLabel() should be used.
  • when useColumnLabel=false, getColumnName() should be used.

I am not sure how to fix this cleanly yet.

@harawata harawata added bug and removed waiting for feedback labels Jun 22, 2019
@huangdx0726

This comment has been minimized.

Copy link
Contributor

@huangdx0726 huangdx0726 commented Jul 3, 2019

@dezhiShen @harawata mysql驱动包5.1及以上会产生这个问题。按你的修改可以解决这个问题,如果怕引发其他bug可以改用5.0的驱动包试试。

@dezhiShen

This comment has been minimized.

Copy link
Author

@dezhiShen dezhiShen commented Jul 10, 2019

@dezhiShen @harawata mysql驱动包5.1及以上会产生这个问题。按你的修改可以解决这个问题,如果怕引发其他bug可以改用5.0的驱动包试试。

通过另外一种 <collection> 使用方式和sql关联,一次查出所有数据的方式,可以避免上述情况发生,也更好
http://www.mybatis.org/mybatis-3/zh/sqlmap-xml.html#Result_Maps
在resultMap中使用

<resultMap id="detailedBlogResultMap" type="Blog">
 ...
  <collection property="posts" ofType="Post">
    <id property="id" column="post_id"/>
    <result property="subject" column="post_subject"/>
     ...
  </collection>
    ...
</resultMap>

There are another way to use the tag <collection>.
We can query all columns only once call sql.
Like this:
http://www.mybatis.org/mybatis-3/sqlmap-xml.html#Result_Maps

resultMap:

<resultMap id="detailedBlogResultMap" type="Blog">
 ...
  <collection property="posts" ofType="Post">
    <id property="id" column="post_id"/>
    <result property="subject" column="post_subject"/>
     ...
  </collection>
    ...
</resultMap>
@kazuki43zoo

This comment has been minimized.

Copy link
Member

@kazuki43zoo kazuki43zoo commented Jul 10, 2019

@huangdx0726 @dezhiShen , Please use English as much as possible.

@kazuki43zoo kazuki43zoo added this to the 3.5.3 milestone Jul 14, 2019
@harawata harawata modified the milestones: 3.5.3, 3.5.4 Oct 20, 2019
kazuki43zoo added a commit to kazuki43zoo/mybatis-3 that referenced this issue Oct 24, 2019
kazuki43zoo added a commit to kazuki43zoo/mybatis-3 that referenced this issue Oct 26, 2019
@kazuki43zoo kazuki43zoo self-assigned this Oct 26, 2019
kazuki43zoo added a commit that referenced this issue Oct 26, 2019
Change to refers useColumnLabel at UnknownTypeHandler
@kazuki43zoo kazuki43zoo changed the title A bug of TypeHandler version:3.5.1 There is case that a TypeHandler is incorrectly selected for a nested select query parameter Oct 26, 2019
@kazuki43zoo

This comment has been minimized.

Copy link
Member

@kazuki43zoo kazuki43zoo commented Oct 26, 2019

@dezhiShen Sorry for late fixing.

We fixed this issue at now and will release on next maintenance version(3.5.4). If you have a time, I would be happy that confirm this issue can be resolved using 3.5.4-SNAPSHOT.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

4 participants
You can’t perform that action at this time.