introduce

TgDao is a compilation time SQL generator based on Mybatis, which uses annotations to express SQL and generates mapper.xml files based on your method signature. It can reduce the amount of simple SQL you need to write in your daily development. Since it only generates mapper.xml files, you can also write your own SQL for complex query scenarios that tools cannot generate.

@Table(name = "T_User")
public class User {
    @Id("id")
    private int id;
    private String username;
    private int age;
}

Copy the code

The above model defines the relationship between the model and the database table. Looking at the signature of these methods, you should be smart enough to guess the SQL for each method. That’s what this library does.

@DaoGen(model = User.class)
public interface UserDao {
    @Select
    @OrderBy("id desc")
    List<User> queryUser(@Condition(criterion = Criterions.EQUAL, column = "username") String name,
                         @Condition(criterion = Criterions.GREATER, attach = Attach.OR) int age,
                         @Limit int limit, @OffSet int offset);

    @Select
    List<User> queryUser2(@Condition(criterion = Criterions.GREATER, column = "age") int min,
                          @Condition(criterion = Criterions.LESS, column = "age") int max);

    @Select
    List<User> queryUser3(@Condition(criterion = Criterions.EQUAL, column = "username") String name,
                          @Condition(attach = Attach.OR, column = "id", criterion = Criterions.IN) String[] ids);

    @Insert(useGeneratedKeys = true, keyProperty = "id")
    int insert(User user);

    @BatchInsert(columns = "username,age")
    int batchInsert(List<User> users);

    @Update
    @ModelConditions({
            @ModelCondition(field = "id")
    })
    int update(User user);

    @Delete
    int delete(@Condition(criterion = Criterions.GREATER, column = "age") int min,
               @Condition(criterion = Criterions.LESS, column = "age") int max);
}
Copy the code

Project address github.com/twogoods/Tg…

The document

Introduce the following dependencies:

< the dependency > < groupId > com. Making. Twogoods < / groupId > < artifactId > tgdao - core < / artifactId > < version > 0.1.3 < / version > </dependency>Copy the code

Table is associated with Model

@ingore: @ingore: @ingore: @ingore: @ingore: @ingore: @ingore: @ingore: @ingore: @ingore: @ingore: @ingore: @ingore: @ingore: @ingore: @ingore: @ingore: @ingore: @ingore: @ingore: @ingore: @ingore: @ingore: @ingore: @ingore: @ingore: @ingore: @ingore: @ingore

@Table(name = "T_User")
public class User {
    @Id("id")
    private int id;

    private String username;
    private String password;
    private int age;

    @Column("old_address")
    private String oldAddress;
    @Column("now_address")
    private String nowAddress;

    private int state;

    @Column("created_at")
    private Timestamp createdAt;
    @Column("updated_at")
    private Timestamp updatedAt;

    @Ignore
    private String remrk;
Copy the code

The query

@Select
@OrderBy("id desc")
List<User> queryUser(@Condition(criterion = Criterions.EQUAL, column = "username") String name,
                    @Condition(criterion = Criterions.GREATER, attach = Attach.OR) int age,
                    @Condition(column = "id", criterion = Criterions.IN) String[] ids,
                    @Limit int limit, @OffSet int offset);
Copy the code
@Select
  • columns: the defaultselect *You can configure thecolumns("username,age")Select some fields;
  • SqlModeSELECTIVE: Two options are available: SQLmode. SELECTIVE and SQLmode.mon. The difference is that SELECTIVE checks whether the field in the query condition is NULL to implement dynamic query<if test="name ! = null">username = #{name}</if>
@Condition
  • criterion: Query conditions,=.<.>.inEtc., see for detailsCriterions
  • column: Corresponds to a table field. This parameter is optional if the field name is the same as the field name
  • attach: connectionand.orThe default isand
  • test: Selective judgment expression, namely<if test="username ! = null">The test property in

@limit, @offset are paging fields. The parameters of a method are used as query conditions without any annotations, as in the following two functions:

@Select()
List<User> queryUser(Integer age);

@Select()
List<User> queryUser(@Condition(criterion = Criterions.EQUAL, column = "age") Integer age);
Copy the code

The query Model

We can encapsulate the query conditions into a class and annotate the conditions with @modelconditions. Note that @Modelconditions can only have one parameter.

@Select
@Page
@ModelConditions({
       @ModelCondition(field = "username", criterion = Criterions.EQUAL),
       @ModelCondition(field = "minAge", column = "age", criterion = Criterions.GREATER),
       @ModelCondition(field = "maxAge", column = "age", criterion = Criterions.LESS),
       @ModelCondition(field = "ids", column = "id", criterion = Criterions.IN),
       @ModelCondition(field = "idArr", column = "id", criterion = Criterions.IN, paramType = InType.ARRAY)
})
List<User> queryUser5(UserSearch userSearch);
Copy the code
@ModelCondition
  • field: Specifies the attribute of the class in the query condition
  • column: Indicates the corresponding table field
  • paramType: in requires configuration only when the array isarrayThe List forcollectiontype
  • test: Selective judgment expression, namely<if test="username ! = null">The test property in

@page can only be used for queries under ModelConditions, and the class of method parameters should have offset and limit attributes.

Note:

@Select(columns = "username,age")
List<User> queryUser(Integer age);

@Select(columns = "username,age")
List<User> queryUser2param(Integer age, String username);

<select id="queryUser" resultMap="XXX">select username,age from T_User
    <where>
      <if test="age ! = null">AND age = #{age}</if>
    </where>
</select>

<select id="queryUser2param" resultMap="XXX">select username,age from T_User
    <where>
      <if test="age ! = null">AND age = #{age}</if>
      <if test="username ! = null">AND username = #{username}</if>
    </where>
</select>
Copy the code

The SQL generated by the two functions is as follows: the @select attribute SqlMode is Selective by default, so both of them have conditional judgment, but Mybatis does not support the SQL of the first function, and the execution will report an error. Similar to no Age Getter in java.lang.Interger, Mybatis will treat this single parameter as an object to fetch the value inside. Solution: impose @param () annotation in the function signature, or use sqlMode = sqlmode.mon in the @select function to remove the if judgment in the generated SQL. This problem can only occur if the method has only one argument and the SQL generated by the second function is OK.

paging

The paging in @limit, @offset, or @page in the query model is primitive. TgDao is just an SQL generator, so you can use various plug-ins or integrate with other frameworks. For paging, you can integrate seamlessly with PageHelper.

@Select
List<User> queryUser2(@Condition(criterion = Criterions.GREATER, column = "age") int min,
                @Condition(criterion = Criterions.LESS, column = "age") int max);


@Test
public void testQueryUser2() throws Exception {
   PageHelper.offsetPage(1, 10);
   List<User> users = mapper.queryUser2(12, 30);
   PageInfo page = new PageInfo<>(users);
   System.out.println(page.getTotal());
   Assert.assertTrue(page.getList().size() > 0);
}
Copy the code

insert

@Insert(useGeneratedKeys = true, keyProperty = "id"Int insert(User User); @BatchInsert(columns ="username,age"Int batchInsert(List<User> users);Copy the code

Columns are strongly recommended by BatchInsert because the generated statement does not filter null columns and inserts null into the database are prone to errors.

update

@Update(columns = "username,age"ModelCondition({@modelCondition (field =))// Select update some columns @modelCondition ({@modelCondition (field =)"id")
})
int update(User user);
Copy the code

delete

@Delete
int delete(@Condition(criterion = Criterions.GREATER, column = "age") int min,
          @Condition(criterion = Criterions.LESS, column = "age") int max);

@Delete
@ModelConditions({
       @ModelCondition(attach = Attach.AND, field = "minAge", column = "age", criterion = Criterions.GREATER),
       @ModelCondition(attach = Attach.AND, field = "maxAge", column = "age", criterion = Criterions.LESS)
})
int delete2(UserSearch userSearch);
Copy the code

selective

@select, @count, @update, and @delete all have the selective property, which has two values: SqlMode.COMMON and SQLmode.selective. The difference is clearly shown in the XML generated below, sqlmode. SELECTIVE introduces Mybatis dynamic SQL capabilities.

<! -- SELECTIVE --> <select id="queryUser" resultMap="BaseResultMap">select username,age from T_User 
    <where>
      <if test="name! =null and name! = "">AND username = #{name}</if>
      <if test="age ! = null">OR age = #{age}</if>
    </where> </select> <! -- COMMON --> <select id="queryUser" resultMap="BaseResultMap">select username,age from T_User 
    <where>
      AND username = #{name} OR age = #{age}
    </where>
  </select>
Copy the code

The default selective attribute of @select, @count is SQLmode. selective, so that the query statement can make full use of Mybatis dynamic SQL capability. In selective mode, @update and @delete default to SQLmode.mon. This is because: In selective mode, if all parameters are null, there is no condition in the WHERE statement, resulting in the Update and deletion of the entire table, which is extremely dangerous. Therefore, @update and @delete should use the SQLmode. SELECTIVE mode with caution.

@Params

The @param annotation defines a name for the parameter in the method, so that you can use this name to obtain the corresponding value of the parameter in XML SQL. As follows:

    List<User> queryUser(@Param("name") String name);
    
    <select id="queryUser">select * from T_User where username=#{name} </select>
Copy the code

Why annotate @param with a name when the argument is name? This is because after Java is compiled, the parameter name will be discarded, so that mybatis will not know what the parameter name is, so it needs to annotate a name. Parameter ‘XXX’ not found. Available parameters are… Parameter ‘XXX’ not found. That’s the problem with not having this annotation. However, in Java8 we can keep the parameter name by adding the -parameters parameter to javac so that Mybatis can use this information without the @param annotation. Maven can be set in the following ways:

  <plugin>
      <groupId>org.apache.maven.plugins</groupId>
      <artifactId>maven-compiler-plugin</artifactId>
      <version>3.1</version>
      <configuration>
          <compilerArgs>
              <arg>-parameters</arg>
          </compilerArgs>
      </configuration>
  </plugin>
Copy the code

List

queryUser4(List ids); Mybatis does not recognize ids when the parameter is collection or array. And @params annotation is Mybatis own annotation @param and -parameters another solution. @Params can be annotated on classes and methods, which automatically annotate all method parameters at compile time with @Param. It takes a leaf out of Lombok’s book by modifying the abstract syntax tree at compile time to alter the bytecode files generated by compilation.

    @Select(columns = "username,age") @Params List<User> queryUser(Integer age, String username); List<User> queryUser(@param ("age") Integer var1, @Param("username") String var2);
Copy the code

See examples for more

instructions

  • The compiled XML file is in the same package as the Mapper interface
  • Only Java8 and MySql are supported
  • Incremental compilation is caused by changing the method definition in the source code or the mapping between the model and the data table and finding that the compiled XML does not change. Generating AN XML requires both the Model and mapper Interface parts. If you modify only one of the two parts of the code, the compiler does not process the unmodified part of the code, and this compilation will not get all the information, so TgDao will not generate the latest version of XML. The solution is every timemvn clean compileClean up the compile directory first, a better solution is looking for…