Ibatis/MyBatis select dynamically without need to create any Pojo / Mapper

Question

Is there any way to select/update/delete dynamically using Ibatis/MyBatis?

When I say "dynamically" it means I don't want to create any POJO/DataMapper at all.

Any URL example would be welcomed.

Bustard

Answer 1

Yes, just set the resultType attribute to map and the table data will be placed into a HashMap of column names to values. If the query returns more than 1 row, the mapped rows will be put into a List. If you want to select a single column, you can get just that value (as String, int, etc) or as a list.

<select id="test1" resultType="map">select * from user</select>
<select id="test2" resultType="map" parameterType="int">
  select * from user where id=#{value}</select>
<select id="test3" resultType="string">select name from user</select>
...
// returns a list of maps
List test = sqlSession.selectList("test1");

// returns a single map
Object map = sqlSession.selectOne("test2", 0);

// returns a list of strings
List names = sqlSession.selectList("test3");

This applies to MyBatis 3; I think you can do something similar in iBatis 2.

Sapowith

The difference is between select * and select name, assuming name is VARCHAR. It doesn't have to be *, but as long as you need more than 1 column, the resultType should be map (or a Pojo). If you are only selecting 1 column, set the resultType to whatever the column type is. – Sapowith

Answer 2

The following approach can be useful. Say, you have some generic select interface, like:

public interface IAutoRepository {
    /**
     * The automatically generated insertPKs sql statements.
     * Parts of the query can be set manually in the sql (insert-select query).
     *
     * @param items     the {@link WhereStmt} statements
     * @return the inserted rows count
     */
    @Transactional
    <T extends WhereStmt> Integer insertPKs(@Param("items") List<T> items);

    /**
     * Returns the value based on the {@link Parameter} class
     *
     * @param param     the {@link Parameter} instance
     * @return the searched value in a {@link java.util.Map} form
     */
    @MapKey("VAL")
    <T extends Parameter> Map<String, Map<String, ?>> getDistinctValues(@Param("param") T param);
}

According to some external type (say, single column or date range or range whatsoever) you can define the following query in the template Common.xml:

<sql id="includeDistinctValues">
SELECT
    <choose>
        <when test='param.type.name() == "set"'>
            DISTINCT ${param.column} AS val
        </when>
        <when test='param.type.name() == "date" or param.type.name() == "range"'>
            <some uid>              AS val,
            MIN(${param.minColumn}) AS min,
            MAX(${param.maxColumn}) AS max
        </when>
    </choose>
FROM ${entityTable}
</sql>

What you receive from mybatis is the java.util.Map. Then you can you use it some kind like:

public enum StmtType {
    set((valMap) -> {
        final Set<String> distinctValues = valMap
                .values()
                .stream()
                .map(val -> (String) val.get("VAL"))
                //use in date/range case
                //final Date minDate = (Date) val.get("MIN");
                //final Date maxDate = (Date) val.get("MAX");
                .collect(Collectors.toSet());
        return distinctValues;
    },
            (values, params) -> {
                final SetParameter parameter = (SetParameter) params.getParams();
                return new WhereSetStmt<>(parameter.getColumn(), values, params.getIncludeEmptyValues());
            });
    @Getter
    private Function<Map<String, Map<String, ?>>, ? extends Iterable> toValue;
    @Getter
    private BiFunction<Collection, DataParam, ? extends WhereStmt> toWhereStmt;

    StmtType(
            Function<Map<String, Map<String, ?>>, ? extends Iterable> toValue,
            BiFunction<Collection, DataParam, ? extends WhereStmt> toWhereStmt
    ) {
        this.toValue = toValue;
        this.toWhereStmt = toWhereStmt;
    }
}

where the SetParameter can be represented as the following:

@Getter
public class SetParameter extends Parameter {
    /**
     * Column in sql query,
     */
    private final String column;
    public SetParameter(String column) {
        super(StmtType.set);
        this.column = column;
    }
}

Moreover, you can define some WhereStmt like:

public abstract class WhereStmt {
    /**
     * Type of the statement
     */
    private final StmtType type;
    /**
     * Shall empty values be included.
     */
    private final boolean includeEmptyValues;
}

@Getter
public class WhereSetStmt<T> extends WhereStmt {
    /**
     * The column for `column` IN (...) statement
     */
    private String column;
    /**
     * Values for `column` IN (...) statement
     */
    private Collection<T> values;

    public WhereSetStmt(String column, Collection<T> values, boolean includeEmptyValues) {
        super(StmtType.set, includeEmptyValues);
        this.column = column;
        this.values = values;
    }
}

@Getter
@AllArgsConstructor
public final class DataParam<P extends Parameter> {
    /**
     * Whether to include nullable values.
     */
    private final Boolean includeEmptyValues;
    /**
     * Represents database required information for later processing and sql statements generation.
     */
    private final P params;
}

Finally, in mybatis generic Common.xml you can use it like:

<sql id="includeInsertPkSelect">
    SELECT DISTINCT(${id})
    FROM ${entityTable}
</sql>

<sql id="includeInsertPkWhere">
    <if test="items != null and items.size() > 0">
        AND
        <foreach collection="items" item="item" index="i" separator="AND">
            <choose>
                <when test='item.type.name() == "set" and ( item.values != null and item.values.size() > 0 or item.includeEmptyValues )'>
                    (
                    <if test="item.values != null and item.values.size() > 0">
                        ${item.column} IN
                        <foreach item="value" collection="item.values" separator="," open="("
                                 close=")">
                            #{value}
                        </foreach>
                        <if test="item.includeEmptyValues">
                            OR
                        </if>
                    </if>
                    <if test="item.includeEmptyValues">
                        ${item.column} IS null
                    </if>
                    )
                </when>

                <when test='item.type.name() == "date"'>
                    (
                    COALESCE(${item.column}, SYSDATE + 1000000)
                    BETWEEN #{item.from} AND #{item.to}
                    <if test="item.includeEmptyValues">
                        OR ${item.column} IS NULL
                    </if>
                    )
                </when>
                <when test='item.type.name() == "range"'>
                    (
                    COALESCE(${item.column}, 1000000000)
                    BETWEEN #{item.from} AND #{item.to}
                    <if test="item.includeEmptyValues">
                        OR ${item.column} IS NULL
                    </if>
                    )
                </when>
            </choose>
        </foreach>
    </if>
</sql>

And combine sql statements as the template, say:

<insert id='insertPKs'>
    INSERT INTO requiredTable
    <include refid="Common.includeInsertPkSelect">
        <property name="id" value="entityId"/>
        <property name="entityTable" value="entityTable"/>
    </include>
    <include refid="Common.includeInsertPkWhere">
        <property name="id" value="entityId"/>
        <property name="entityTable" value="entityTable"/>
    </include>
</insert>

Halsy

Answer 3

Yes, it should be possible to build the mapping in runtime through an API, and to use Maps instead of entity classes.

Tumefaction

Answer 4

Yes, you can do select/update/delete without using POJO. In myBatis you can write your query in Mapper XML and set the resultType that will be returned from SQL statement and store that in objects.

For Example,

    <mapper namespace = "data">
      <select id = "fetch" resultType = "java.util.HashMap">
        select * from table_name
      </select>
    </mapper>

You can use List of Maps to store those results.

  List<Map<String,Object>> results = session.selectList("data.fetch");

Jehius