How to change value(null to empty string) from query when using mybatis?

Question

I am doing a project which uses Spring 3.1.1 and MyBatis3.0.

I am trying to change iBatis to MyBatis. However, I am struggling with resultmap.

When using iBatis, I can handle values from query like below with 'nullValue'.

<resultMap class="java.util.HashMap" id="ChannelData">
        <result property="id"       javaType="java.lang.String"         column="CHANNEL_ID" nullValue=""/>
        <result property="code"         column="SELECTSCOPE"        nullValue="Television"/>
</resultMap>

The problem is there no 'nullValue' in MyBatis. In addition, if the column is 'null' then mybatis never fills that element. for Example. if 'SELECTSCOPE' is null, it brings {id=aaa}. I need some data like this --> {id=aaa, code=''}. Is there anyway to handle this?

P.S.

I queries more than 20 columns. Some of them need "" when value is null, others has own default some string value.(If I use iBatis, 'nullValue' is magic keyword) I found some links which recommend to use customized type handler, but making handler more than 20 can be cause of future confusion to repair or maintaining. I need some simple way.

Thanks a lot:D

======================================================================================

I found the way to bring some null values. It needs some configuration.

• make config.xml, which should contain some information about MyBatis Config DTD and Settings like below

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD SQL MAP Config 3.1//EN" 
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
  <settings>
      <setting name="callSettersOnNulls" value="true"/>
  </settings>
</configuration>

now, I can get {id="aaa", code = null}. Here is additional question. How can I set default values for query? for example. if value of 'code' is null, then I want to put default String 'default'. So result should change form {id="aaa", code=null} to {id="aaa",code="default"}. Is it possible?

Thanks~

Answer 1

I think I could suggest an answer for myself. But somehow it feels not efficient. I made a TypeHandlerClass which implements the interface 'org.apache.ibatis.type.TypeHandler'. Source code is below.

public class EmptyStringIfNull implements TypeHandler<String> {

    @Override
    public String getResult(ResultSet rs, String columnName) throws SQLException {
        return (rs.getString(columnName) == null) ? "" : rs.getString(columnName); 
    }

    @Override
    public String getResult(ResultSet rs, int columnIndex) throws SQLException {
        return (rs.getString(columnIndex) == null) ? "" : rs.getString(columnIndex);
    }

    @Override
    public String getResult(CallableStatement cs, int columnIndex)
            throws SQLException {
        return (cs.getString(columnIndex) == null) ? "" : cs.getString(columnIndex);
    }

    @Override
    public void setParameter(PreparedStatement ps, int arg1, String str,
            JdbcType jdbcType) throws SQLException {
    }
}; 

So I linked this in resultMap element 'typehandler' which looks like :

    <resultMap type="map" id="channel">
        <result property="id"   column="CHANNEL_ID" typeHandler="StringHandler"/>
        <result property="code" column="SELECTSCOPE"    typeHandler="StringHandler"/>
</resultMap>

But I still got additional question. I see I can put some defaultValue in this .java code. But resultMap has many results. If every result has their own specific default value, How can handle this?

using 'if else' in java code fills inefficient, because some of them does not need to check value, they just only need to check null or not. Suggest your clever solutions :D Thanx

Answer 2

Indeed, the nullValue property of result map was very convenient.

If you map results to a custom type instead of a HashMap, the callSettersOnNulls setting that is false by defaults will help if concerned properties have default value:

public class ChannelData {
    private String id;
    private String code = "default";
}

Although it could be very tedious, you can also edit the SQL queries to handle default values using functions such as NVL / COALESCE:

SELECT channel_Id, NVL(selectScope, 'default') AS selectScope

The typeHandler you suggested has the drawback to apply to every column of the handled type and does not allow specifying different default values meeting the needs.

It can be achieved by implementing result handlers (but very verbose solution) which would consist in if-else on concerned properties (do not forget the custom result handler have to feed the result list if it is required).