MyBatis, Select Provider and SQLBuilder

Question

this is more than a simple question and my English is not as good as I want... I'll try my best.

I use java 8, with Mybatis 3.4.6 over Postgres 9.6 and I need to do a custom dynamic query.

In my mapper.java class I've created a method to use with myBatis SQL Builder class

@SelectProvider(type = PreIngestManager.class, method = "selectPreIngestsSQLBuilder")
@Results({ @Result(property = "id", column = "id"), @Result(property = "inputPath", column = "input_path"),
        @Result(property = "idCategoriaDocumentale", column = "id_categoria_documentale"), @Result(property = "idCliente", column = "id_cliente"),
        @Result(property = "outputSipPath", column = "output_sip_path"), @Result(property = "esito", column = "esito"),
        @Result(property = "stato", column = "stato"), @Result(property = "pathRdp", column = "path_rdp"),
        @Result(property = "dataInizio", column = "data_inizio"), @Result(property = "dataFine", column = "data_fine") })
List<PreIngest> selectPreIngestsByFilters(@Param("idCatDoc") Long idCatDoc, @Param("nomePacchetto") String nomePacchetto,
        @Param("dataInizioInferiore") Date dataInizioInferiore, @Param("dataInizioSuperiore") Date dataInizioSuperiore,
        @Param("statiPreIngest") String statiPreIngest);

I have specified the @SelectProvider annotation, class and method to point at, which, in the example is PreIngestManager.class and selectPreIngestsSQLBuilder method.

This is the method

public String selectPreIngestsSQLBuilder(Map<String, Object> params) {
    return new SQL() {
        {
            SELECT("*");
            FROM("pre_ingest");
            WHERE("id_categoria_documentale = #{idCatDoc}");
            if (params.get("nomePacchetto") != null)
                WHERE("input_path like '%' || #{nomePacchetto}");
            if (params.get("dataInizioInferiore") != null) {
                if (params.get("dataInizioSuperiore") != null) {
                    WHERE("data_inizio between #{dataInizioInferiore} and #{dataInizioSuperiore}");
                } else {
                    WHERE("data_inizio >= #{dataInizioInferiore}");
                }
            } else {
                if (params.get("dataInizioSuperiore") != null) {
                    WHERE("data_inizio <= #{dataInizioSuperiore}");
                }
            }
            if (params.get("statiPreIngest") != null)
                WHERE("stato in (#{statiPreIngest})");
            ORDER_BY("id ASC");
        }
    }.toString();
}

and these are my questions:

have I to specify @Results annotation and every @Result , or can I use a java model class ? I have tried with @ResultMap(value = { "mycompany.model.PreIngest" }) but it did not work.

Most of all, as stated on documentation, with SQL builder you can access method parameters having them as final objects

// With conditionals (note the final parameters, required for the anonymous inner class to access them)
public String selectPersonLike(final String id, final String firstName, 
final String lastName) {
return new SQL() {{
   SELECT("P.ID, P.USERNAME, P.PASSWORD, P.FIRST_NAME, P.LAST_NAME");
    FROM("PERSON P");
    if (id != null) {
      WHERE("P.ID like #{id}");
    }
    if (firstName != null) {
      WHERE("P.FIRST_NAME like #{firstName}");
    }
    if (lastName != null) {
      WHERE("P.LAST_NAME like #{lastName}");
    }
    ORDER_BY("P.LAST_NAME");
    }}.toString();
}

But if I put those final in my method I can't access them. Do I need to delete the @Param from the method declaration? Do SQLBuilder need to be called without @SelectProvider ? Am I mixing solutions ?

As far as I have researched, for now I see 3 methods to do a dynamic query, or a custom where condition.

1. To use MyBatisGenerator library and combine where condition as search criteria to use with SelectByExample method. (I use this when the query is simple)
2. To Write an SQL query directly, modifying XML mapper files using if, choose, statements and others as descripted here
3. To use SQL Builder class with @SelectProvider annotation.

Do you know when prefer the 2° method over the 3° one ? Why in the 3° method documentation I can't find how to use it ? There is written how to create custom queries but not how to launch them.

Thank a lot for your time and your suggestions.

Answer 1

I don't know whether you already found the answer, I just want to share my experience. Btw please forgive my english if it wasn't good.

Note: I use MyBatis 3.4.6 and Spring Framework.

have I to specify @Results annotation and every @Result , or can I use a java model class ?

Actually you can do either one.

if you want to use @Results and @ResultMap, you just need to specify @Results annotation just once in one mapper file. The trick is you need to specify id for the Results to be used in other functions.

Using truncated version of your classes, eg:

    @Results(id="myResult", value= {
        @Result(property = "id", column = "id"), 
        @Result(property = "inputPath", column = "input_path"),
        @Result(property = "idCategoriaDocumentale", ... })
    List<PreIngest> selectPreIngestsByFilters(@Param("idCatDoc") Long idCatDoc, @Param("nomePacchetto") String nomePacchetto, ...);

Then in another function you can use @ResultMap with the value refer to id from @Results mentioned before.

    @ResultMap("myResult")
    List<PreIngest> selectPreIngestsBySomethingElse(....);

..., or can I use a java model class ?

You can use java model class as result without using @Results and @ResultMap, but you have to make sure your java model class has the same properties/fields as the result of your query. Database tables usually have fields with snake_case. Since java is using camelCase, you have to add settings to your mybatis-config.xml file.

This is what I usually add to the mybatis-config.xml

    <?xml version="1.0" encoding="UTF-8" ?>
    <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
    <configuration>
        <settings>
            <!-- changes from the defaults -->
            <setting name="lazyLoadingEnabled" value="false" />
            <setting name="mapUnderscoreToCamelCase" value="true"/>
            <setting name="jdbcTypeForNull" value="NULL"/>
        </settings>
    </configuration>

The important one is mapUnderscoreToCamelCase, set this to true than you can use your java model class without the hassle of @Results and @ResultMap. You can find all the explanation of the settings in MyBatis 3 Configuration.

This is the example using your classes,

The class:

public class PreIngest {
    private Long idCategoriaDocumentale;
    private Long idCliente;
    ........ other fields
    ........ setter, getter, etc
}

The mapper file:

List<PreIngest> selectPreIngestsByFilters(@Param("idCatDoc") Long idCatDoc, @Param("nomePacchetto") String nomePacchetto, ...);

Now onwards to SqlBuilder.

But if I put those final in my method I can't access them. Do I need to delete the @Param from the method declaration? Do SQLBuilder need to be called without @SelectProvider ?

I can't answer about those final in your method since I never made SqlBuilder class with final parameters.

For SqlBuilder you must use @SelectProvider, @InsertProvider, @UpdateProvider or @DeleteProvider and it depends on the query you use.

In my experience with SQLBuilder, @Param is necessary if you need more than one parameters and use Map params to access it from the SqlBuilder class. If you don't want to use @Param in the mapper file, then you need to make sure there is only one parameter in the said mapper function. You can use java model class as the parameter though if you just specify one parameter.

If using your class for example, you can have one class

    public class PersonFilter {
        private Long id;
        private String firstName;
        private String lastName;

        ...... setter, getter, etc
    }

the mapper function

    @SelectProvider(type=PersonSqlBuilder.class, method="selectPersonLike")
    List<Person> selectPersonLike(PersonFilter filter);

the SqlBuilder class

    public class PersonSqlBuilder {

        public String selectPersonLike(PersonFilter filter) {
            return new SQL() {{
                SELECT("P.ID, P.USERNAME, P.PASSWORD, P.FIRST_NAME, P.LAST_NAME");
                FROM("PERSON P");
                if (filter.getId() != null) {
                    WHERE("P.ID like #{id}");
                }
                if (filter.getFirstName() != null) {
                    WHERE("P.FIRST_NAME like #{firstName}");
                }
                if (filter.getLastName() != null) {
                    WHERE("P.LAST_NAME like #{lastName}");
                }
                ORDER_BY("P.LAST_NAME");
            }}.toString();
        }
    }

That's it. Hopefully my experience can help.

Answer 2

I don't know how to do this with the sql builder but I do have an idea how to do this with an xml mapper file:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="path.to.class.PreIngestMapper">

    <resultMap id="preIngestManager" type="path.to.class.PreIngestManager">
        <id property="id" column="id" />
        <result property="id" column="id" />
        <result property="inputPath" column="input_path" />
        <result property="idCategoriaDocumentale" column="id_categoria_documentale" />
        ...
    </resultMap>

    <select id="selectPreIngests" parameterType="Map" resultMap="preIngestManager">
        SELECT *
        FROM pre_ingest
        WHERE id_categoria_documentale = #{idCatDoc}
        <if test = "nomePacchetto != null">
          and input_path like '%' || #{nomePacchetto}

        </if> 
        ...
        ;
    </select>


</mapper>