This is the 31st day of my participation in the August Text Challenge.More challenges in August

Mybatis + XML configuration file (CURD) can be implemented using Java annotations instead of XML files

I. Mybatis annotation development

As for setting up the project environment, please check the blog post if you have any questions:

1. Basic configuration

Database for testing

CREATE TABLE `money` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL DEFAULT ' ' COMMENT 'Username',
  `money` int(26) NOT NULL DEFAULT '0' COMMENT 'money',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0',
  `create_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation time',
  `update_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Update Time'.PRIMARY KEY (`id`),
  KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=551 DEFAULT CHARSET=utf8mb4;
Copy the code

The corresponding database entity class MoneyPO

@Data
@NoArgsConstructor
@AllArgsConstructor
public class MoneyPo {
    private Integer id;

    private String name;

    private Long money;

    private Integer isDeleted;

    private Timestamp createAt;

    private Timestamp updateAt;
}
Copy the code

Mapper interface class

@Mapper
public interface MoneyMapper {}Copy the code

Notice the difference from the previous section, where we don’t have an XML file

2. Insert data

Add data directly to the methods defined in the interface using the @insert annotation

/** * save the data and save the primary key id **@param po
 * @return int
 */
@Options(useGeneratedKeys = true, keyProperty = "po.id", keyColumn = "id")
@Insert("insert into money (name, money, is_deleted) values (#{po.name}, #{po.money}, #{po.isDeleted})")
int save(@Param("po") MoneyPo po);
Copy the code

The @insert annotation is the same as the tags in the XML. The @options annotation is used to specify some configuration information, such as the case above, which is used to configure the id to be saved to the ID field in the MoneyPo parameter

3. Query data

Queries use the @SELECT annotation


    /** * primary key query **@param id id
     * @return {@link MoneyPo}
     */
    @Select("select * from money where id = #{id}")
    @Results(id = "moneyResultMap", value = { @Result(property = "id", column = "id", id = true, jdbcType = JdbcType.INTEGER), @Result(property = "name", column = "name", jdbcType = JdbcType.VARCHAR), @Result(property = "money", column = "money", jdbcType = JdbcType.INTEGER), @Result(property = "isDeleted", column = "is_deleted", jdbcType = JdbcType.TINYINT), @Result(property = "createAt", column = "create_at", jdbcType = JdbcType.TIMESTAMP), @Result(property = "updateAt", column = "update_at", jdbcType = JdbcType.TIMESTAMP)})
    MoneyPo getById(@Param("id") int id);
Copy the code

There’s nothing special about the select annotation, but what about the @Results annotation

This annotation, like the

tag in XML, uses @result to specify the mapping between the database table structure and the Java entity class. The outermost ID is mainly used for reuse

@Select("select * from money where `name` = #{name}")
@ResultMap(value = "moneyResultMap")
MoneyPo getByName(@Param("name") String name);
Copy the code

4. Data update

Give the corresponding case directly


/** * update **@param id    id
 * @paramMoney money *@return int
 */
@Update("update money set `money`=#{money} where id = #{id}")
int addMoney(@Param("id") int id, @Param("money") long money);
Copy the code

5. Delete data

/** * delete data **@param id id
 * @return int
 */
@Delete("delete from money where id = #{id}")
int delete(@Param("id") int id);
Copy the code

6. Summary

The use of annotations is basically the same as the use of XML files. Of course, from the above example, it seems to be simpler to use annotations. After all, the SQL statement is placed directly on the method, and there is no need to switch back and forth between the two files as before

Note, however, that there is a reason why annotations are not widely used. These are just a few simple cases, and when SQL statements are more complex, annotations are not as straightforward to write

Such as in the query

/** * foreach query **@param ids
 * @return* /
@Select("")
List<MoneyPo> getByIds(@Param("ids") List<Integer> ids);
Copy the code

Too much content in annotations, especially the above string concatenation, will greatly affect the reading experience; Of course, there is text block support in jdK14’s new features, similar to pyton’s use of three double quotes to mark a large text block, but how many projects are actually upgrading to JDK14?

III. Can’t miss the source code and related knowledge points

0. Project

  • Project: github.com/liuyueyi/sp…
  • Source: github.com/liuyueyi/sp…

Series of blog posts

  • 【DB series 】Mybatis series of CURD basic use posture

1. Wechat official account: Yash Blog

As far as the letter is not as good, the above content is purely one’s opinion, due to the limited personal ability, it is inevitable that there are omissions and mistakes, if you find bugs or have better suggestions, welcome criticism and correction, don’t hesitate to appreciate

Below a gray personal blog, record all the study and work of the blog, welcome everyone to go to stroll

  • A grey Blog Personal Blog blog.hhui.top
  • A Grey Blog-Spring feature Blog Spring.hhui.top