Small knowledge, big challenge! This article is participating in the creation activity of “Essential Tips for Programmers”.

MyBatis uses the SQL statement builder

The problem

• One of the most painful things to do with code is embed SQL statements in Java code. This is usually done to dynamically generate SQL that could otherwise be placed in an external file or stored procedure. As we have already seen, MyBatis has a powerful SQL generation scheme in its XML mapping feature, but sometimes it is necessary for Java code not to create SQL statements. Another feature of MyBatis that helps us at this point is to reduce the typical add-in, quote, newline, formatting issues AND embedding conditions before handling redundant commas or AND connectors. Our use of Java to concatenate SQL code is actually very poor design, such as:

String sql = "SELECT P.ID, P.USERNAME, P.PASSWORD, P.FULL_NAME, "
"P.LAST_NAME,P.CREATED_ON, P.UPDATED_ON " +
"FROM PERSON P, ACCOUNT A " +
"INNER JOIN DEPARTMENT D on D.ID = P.DEPARTMENT_ID " +
"INNER JOIN COMPANY C on D.COMPANY_ID = C.ID " +
"WHERE (P.ID = A.ID AND P.FIRST_NAME like ?) " +
"OR (P.LAST_NAME like ?) " +
"GROUP BY P.ID " +
"HAVING (P.LAST_NAME like ?) " +
"OR (P.FIRST_NAME like ?) " +
"ORDER BY P.ID, P.FULL_NAME";
Copy the code

The solution

• MyBatis 3 provides a handy basis class to help solve this problem. Using SQL classes, simply create an instance to call methods to generate SQL statements. The problem in the above example is rewritten using SQL classes

private String selectPersonSql(a) {
   return new SQL() {{
    SELECT("P.ID, P.USERNAME, P.PASSWORD, P.FULL_NAME");
    SELECT("P.LAST_NAME, P.CREATED_ON, P.UPDATED_ON");
    FROM("PERSON P");
    FROM("ACCOUNT A");
    INNER_JOIN("DEPARTMENT D on D.ID = P.DEPARTMENT_ID");
    INNER_JOIN("COMPANY C on D.COMPANY_ID = C.ID");
    WHERE("P.ID = A.ID");
    WHERE("P.FIRST_NAME like ?");
    OR();
    WHERE("P.LAST_NAME like ?");
    GROUP_BY("P.ID");
    HAVING("P.LAST_NAME like ?");
    OR();
    HAVING("P.FIRST_NAME like ?");
    ORDER_BY("P.ID");
    ORDER_BY("P.FULL_NAME");
  }}.toString();
}
Copy the code

• The advantage of this is that we don’t have to worry about the occasional “AND” keyword, or the choice between “WHERE” AND” AND” or nothing at all. It has worked out perfectly for us.

SQL class

Here are some examples,

//Provider
public String insertSql(a) {
    return new SQL()
            .INSERT_INTO("blog")
            .INTO_COLUMNS("id"."name"."title"."content")
            .INTO_VALUES("#{id}"."#{name}"."#{title}"."#{content}")
            .toString();
}

public String updateSql(a) {
    return new SQL()
            .UPDATE("blog")
            .SET("name = #{name}")
            .SET("title = #{title}")
            .SET("content = #{content}")
            .WHERE("id = #{id}")
            .toString();
}

public String findAllSql(a) {
    return new SQL() {{
        SELECT("name, title, content");
        FROM("blog");
    }}.toString();

}

public String findBlogLikeSql(@Param("name") String name, @Param("title") String title,
                                @Param("content") String content) {
    return new SQL() {{
        SELECT("name, title, content");
        FROM("blog");
        if(name ! =null) {
            WHERE("name like #{name}");
        }
        if(title ! =null) {
            WHERE("title like #{title}");
        }
        if(content ! =null) {
            WHERE("content like #{content}");
        }
    }}.toString();

}


//Mapper
public interface BlogMapper {

    @SelectProvider(type = BlogProvider.class, method = "findAllSql")
    @Results(id = "resultMap", value = { @Result(id = true, column = "id", property = "id"), @Result(column = "author_id", property = "authorId"), @Result(column = "name", property = "name"), @Result(column = "title", property = "title"), @Result(column = "content", property = "content"), })
    List<Blog> finAll(a);

    @SelectProvider(type = BlogProvider.class, method = "findBlogLikeSql")
    @ResultMap(value = {"resultMap"})
    List<Blog> findBlogLike(@Param("name") String name, @Param("title") String title,
                            @Param("content") String content);

    @InsertProvider(type = BlogProvider.class, method = "insertSql")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    void insert(Blog dto);

    @UpdateProvider(type = BlogProvider.class, method = "updateSql")
    void update(Blog dto);
}
Copy the code

The resources

• mybatis.com