This is the 11th day of my participation in the August More Text Challenge. For details, see:August is more challenging

Above introduced Mybatis using XML to add, delete, modify and check. In Mybatis, although the use of XML is more common, but in fact, Mybatis also supports the use of annotations to add, delete, change and check, this article will introduce the Mybatis based on annotations to achieve the method of add, delete, change and check.

Annotations are supported by Mybatis org. Apache. Ibatis. The annotations in the package, this article only choose some commonly used annotations to demonstrate its use.

To prepare

// Commodity classification POJO
public class Category {
    /** * ID */
    private Integer id;
    /** * Category name */
    private String name;
    // Omit methods such as getters and setters
}
POJO / / commodities
public class Purchase {

    /** * ID */
    private Integer id;
    /** * Name of commodity */
    private String name;
    /** * Commodity price */
    private Integer price;
    /** * Product classification */
    private Integer category;
    
    public Purchase(a) {}public Purchase(Integer id, String name) {
        this.id = id;
        this.name = name;
    }
    
    // Omit methods such as getters and setters
}
Copy the code

increase

Method in Mapper

// The Insert annotation passes in an array of strings, so the SQL statement can be broken up into an array of strings or pieced together into a string
// @Insert("insert into purchase (id, name, price, category) values(#{id},#{name},#{price},#{category})")
@Insert({"insert", "into purchase", " (id, name, price, category)", " values(#{id},#{name},#{price},#{category})"})
// The Options annotation is used to return the autoincrement ID of the data after insertion
@Options(useGeneratedKeys = true, keyColumn = "id", keyProperty = "id")
int insertAnnoPojo(Purchase purchase);
Copy the code

Testing the code and results, you can see that the ID of the data you just inserted has been returned.

@Test
public void insert(a) {
    PurchaseMapper mapper = sqlSession.getMapper(PurchaseMapper.class);
    // Assembly parameters
    Purchase purchase = new Purchase();
    purchase.setName("Watermelon");
    purchase.setPrice(12);
    purchase.setCategory(3);
    mapper.insertAnnoPojo(purchase);
    System.out.println(purchase);
}
Copy the code

DEBUG [main] - ==> Preparing: insert into purchase (id, name, price, category) values(? ,? ,? ,?) DEBUG [main] - ==> Parameters: null, String, 12(Integer), 3(Integer) 1 Purchase{id=12, name=' Purchase ', price=12, category=3}Copy the code

The query

Method in the Mapper interface

Commonly used annotations

// Use ConstructorArgs and Arg to assign a value to a POJO using ConstructorArgs
@Select("select id,name from purchase where id = #{id}")
@ConstructorArgs({ @Arg(column = "id", javaType = Integer.class, id = true), @Arg(column = "name", javaType = String.class) })
Purchase findAnnoPojo(Purchase purchase);

// Use the Results and Result annotations to assign a value to the POJO by mapping the Result set. This method is usually used when the column name is not consistent with the attribute name.
The Results annotation id gives the result set a name that other queries can use to use the result set
@Select("select id,name from purchase where id = #{id}")
@Results(id = "purchaseMap", value = { @Result(property = "id", column = "id", javaType = Integer.class, jdbcType = JdbcType.INTEGER, id = true), @Result(property = "name", column = "name", javaType = String.class, jdbcType = JdbcType.VARCHAR) })
Purchase findAnnoPojo(Purchase purchase);

// Use the ResultMap annotation to reuse the result set
@Select("select id,name from purchase where id = #{id}")
@ResultMap("purchaseMap")
Purchase findAnnoById(Integer id);
Copy the code

ResultType Usage of annotations

Results / / custom processor, org. Apache. Ibatis. Session. ResultHandler interface
public class PurchaseResultHandler implements ResultHandler {

    List<Purchase> purchases;

    public PurchaseResultHandler(a) {
        super(a);this.purchases = new ArrayList<>();
    }

    // Implement the methods in the interface and process the data returned by the query here
    @Override
    public void handleResult(ResultContext resultContext) {
        Purchase purchase = (Purchase) resultContext.getResultObject();
        purchases.add(purchase);
    }

    // Obtain the result set using the get method
    public List<Purchase> getPurchases(a) {
        returnpurchases; }}// How to write a method in Mapper
@Select("select id,name from purchase where id = #{id}")
@ResultType(Purchase.class)
void findAnnoById(PurchaseResultHandler resultHandler, @Param("id") Integer id);

// Test the code
@Test
public void query(a) {
    PurchaseResultHandler resultHandler = new PurchaseResultHandler();
    mapper.findAnnoById(resultHandler, 12);
    System.out.println(resultHandler.getPurchases());
}
Copy the code

One-to-one query

• POJO class

public class PurchaseVO {

    /** * ID */
    private Integer id;
    /** * Name */
    private String name;
    /** * Price */
    private Integer price;
    /** * category */
    private Category category;
    // Omit methods such as getters and setters
}
Copy the code

• Mapper interface method, using@OneAnnotations to realize

@Select("select * from purchase where id = #{id}")
@Results(id = "purchaseVoMapper", value = { @Result(property = "category", column = "category", one = @One(select = "org.apache.ibatis.z_run.mapper.PurchaseMapper.findCategoryById")) })
PurchaseVO findPurchaseById(Integer id);

@Select("select * from category where id = #{id}")
Category findCategoryById(Integer id);
Copy the code

• Test the code and query results

@Test
public void query(a) {
    PurchaseMapper mapper = sqlSession.getMapper(PurchaseMapper.class);
    System.out.println(mapper.findPurchaseById(11));
}
Copy the code

DEBUG [main] - ==> Preparing: select * from purchase where id = ? DEBUG [main] - ==> Parameters: 11(Integer) DEBUG [main] - ====> Preparing: select * from category where id = ? DEBUG [main] - ====> Parameters: 1(Integer) DEBUG [main] - <==== Total: 1 DEBUG [main] - <== Total: 1 Purchase{id=11, name=' Purchase ', price=3, category= category {id=1, name=' Purchase '}}Copy the code

One to many query

• POJO class

public class CategoryVO {
    private Integer id;
    private String name;
    private List<Purchase> purchases;
    // Omit methods such as getters and setters
}
Copy the code

• Method in the Mapper interface

@Select("select * from category where id = #{id}")
// The id mapping must be written, otherwise the ID value is null
@Results(id = "categoryVoMapper", value = { @Result(property = "id", column = "id"), @Result(property = "purchases", column = "id", many = @Many(select = "org.apache.ibatis.z_run.mapper.PurchaseMapper.findPurchaseByCategory")) })
CategoryVO findCategoryByID(Integer id);

@Select("select * from purchase where category = #{category}")
Purchase findPurchaseByCategory(Integer category);
Copy the code

• Test the code and query results

@Test
public void query(a) {
    PurchaseMapper mapper = sqlSession.getMapper(PurchaseMapper.class);
    System.out.println(mapper.findCategoryByID(1));
}
Copy the code

DEBUG [main] - ==> Preparing: select * from category where id = ? DEBUG [main] - ==> Parameters: 1(Integer) DEBUG [main] - ====> Preparing: select * from purchase where category = ? DEBUG [main] - ====> Parameters: 1(Integer) DEBUG [main] - <==== Total: 5 DEBUG [main] - <== Total: Purchase{id=8, category=1, Purchase{id=8, category=1, Purchase{id=8, category=1, Purchase{id=8, category=1, Purchase{id=8, category=1, Purchase{id=8, category=1, Purchase =8, Purchase = 9 Purchase{id=10, category=1}, Purchase{id=10, category=1}, Purchase{id=10, category=1}, Purchase{id=10, category=1}, Purchase{id=10, category=1}, Purchase{id=10, category=1}, Purchase{id=10, category=1}, Purchase{id=10, category=1}, Purchase{id=11, name=' Purchase ', price=3, category=1}]}Copy the code

Modify the

// In general, use the Update annotation
@Update("update purchase set price = #{price} where id = #{id}")
int updateAnnoById(@Param("id") Integer id, @Param("price") Integer price);

// Use the UpdateProvider annotation to use dynamically built SQL
@UpdateProvider(type = SqlProvider.class, method = "provideUpdate")
int updateAnnoById(@Param("id") Integer id, @Param("price") Integer price);

class SqlProvider {
    public String provideUpdate(@Param("id") Integer id, @Param("price") Integer price) {
        StringBuilder sql = new StringBuilder();
        if(id ! =null) {
            sql.append("update purchase ");
            if(price ! =null) {
                sql.append("set price = #{price}");
            }
            sql.append(" where id = #{id}");
        }
        returnsql.toString(); }}Copy the code

delete

@Delete("delete from purchase where id = #{id}")
int deleteAnnoById(Integer id);
Copy the code

The above is the use of Mybatis annotation to complete the method of adding, deleting, modifying and checking. Since writing SQL on Mapper interface methods is less readable (especially complex SQL), you can understand it this way, and if you come across someone writing code this way, you can understand it.