Prepare to add a feat for @Sql Annotation

[Lambert-Rao]

Feature request I want to work on this to provide an @Sql Annotation

I have used Mybatis for a well, but I find it's not convenient that there is not an @Sql annotation for <sql> just like @Insert for <insert>, so I want to add @Sql and @Include for a reuse of sql statement.

I have post this on Google group

[harawata]

Hello @Lambert-Rao ,

I don't understand how @Sql and @Include works. Please show us some example usages before start coding.

p.s. Please use ` when writing annotations to avoid notifying random users.

[Lambert-Rao]

Thank you for your reply, @harawata,

here's the example in Mapper file:

@Sql(id="userColumns",value="${alias}.id,${alias}.username,${alias}.password") 
Integer sqlStatement();

@Select("select <include refid="userColumns"> from user_table where uid=#{userId}") 
List<User> getUserInfo(@Param("alias") User userEntity, String userId);

@Select("select <include refid="userColumns"> from user_table oder by id desc") 
List<User> getAllUserInfoDesc(@Param("alias") User userEntity);

I put some sql statements in @Sql Annotation, and use them in @Select Annotation with node. Then I can reuse the @Sqlstatement in other method. I'm sorry that I mistakenly said @Include before, but I'll use <include> in @Select, @Update etc.

[harawata]

Okay. At this point, I'm not sure if this is worth the extra code to maintain [1]. Let's keep this issue open and see if it gets up-votes from other users.

[1] As you may be aware, it is possible to define <sql> in the XML mapper and reference it from @Select or other annotations. I was wrong about this. You cannot use <include> in annotations.

[Lambert-Rao]

Thanks, My idea is that, I found it not convenient when I want to reuse some sql statement , for example, the tablename. if I changed the tablename, I have to fix every @Select annotation. And I think it's more convenient to use a JAVA annotation then <sql > in XML mapper.

[HuaJFrame]

Hi,@Lambert-Rao I think you can use public static constants in the Mapper file to achieve your needs:

String BASE_COLUMN_LIST = "id, username, password";

@Select("select" + BASE_COLUMN_LIST + " from user_table where id=#{userId}")
List<User> getUserInfo(String userId);

[Lambert-Rao]

@HuaJFrame Please look at this eample, this is how I define string:

    static String TABLE_SQL = "user_info";

    static String INSERT_SQL = "insert into " + TABLE_SQL;

    static String SELECT_SQL = "select * from " + TABLE_SQL + " where ";

    static String UPDATE_SQL = "update " + TABLE_SQL + "set ";

And I use them like this:

@ResultMap("userMap")
    @Select({SELECT_SQL,
            "ui_id = #{uiId}"
    })
    public UserInfoEntity checkUserByUserId(UserInfo userInfoEntity);

But it's not convenient when the number of static String grows.

Btw, in the future, we hope that we can use Text Block instead of static String when the logic is complex , like this:

@select{"""
select <include refid='id'/> where id = 1
"""
}

that's one of the reason why we need @Sql annotation.

p.s. I have already work on this feature.

[HuaJFrame]

@Lambert-Rao

But it's not convenient when the number of static String grows.

Sorry, I don't quite understand here, if we use the @Sql annotation, do we need to define the following sql?

    static String TABLE_SQL = "user_info";

    static String INSERT_SQL = "insert into " + TABLE_SQL;

    static String SELECT_SQL = "select * from " + TABLE_SQL + " where ";

    static String UPDATE_SQL = "update " + TABLE_SQL + "set ";

Do we need to declare it like this for every SQL statement? If so, won’t the number also increase?

@Sql(id="userColumns",value="${alias}.id,${alias}.username,${alias}.password") 
Integer sqlStatement();

Can you give more detailed examples to let everyone know how to use @Sql?

Also, when using Text Block, we can also use string splicing. Although it is not beautiful, using static constants is efficient, and the compiler will help us optimize their splicing.

We need to consider whether it is worth spending more time to process @Sql annotations and whether the advantages outweigh the disadvantages.

These are some of my personal thoughts. Because I cannot fully appreciate its advantages, I have too many questions, so please forgive me. If it can simplify development, I'm looking forward to it and happy to use it, and I will actively recommend it to others.