Using JooqTemplate implement UserService Demo

No need annotation,No need check null, No need inherit,No need scan,Based on JOOQ

1.Quer User Paramater

public class UserParam {
    String name;
    LocalDate beginBirthday;
    LocalDate endBirthday;
    int offset;
    int limit;
    ...
}

2. User Bean

public class User {
    private Integer id;
    private String name;
    private LocalDate birthday;
    private String nickName;
    private Gender gender;
    private String avatarAddress;
    ...
}

3.UserService

@Service
public class UserService {
    @Autowired
    private JooqTemplate jt;
    public int insertUser(User user) {
        //Bean to camel map
        Map values=JooqMaps.toCamelCase(user);
        //Add additional data
        values.put("create_time", LocalDateTime.now());
        // Insert record
        return jt.insertReturningv("user_table",values,"id").get("id", Integer.class);
    }
    public void updateUser(User user) {
        Map values=JooqMaps.toSnakeCase(user);
        //Regardless of whether it is null or not, update in Map. Update statement does not include in Map
        values.remove("id");
        values.remove("name");
        //jt.updatev("some_table",values,"column1",param1,"column2",param2...);
        //Variable parameter condition update UPDATE user_table SET ... WHERE id=?
        jt.updatev("user_table",values,"id",user.getId());
    }
    public void deleteUser(int id) {
        //jt.deletev("some_table","column1",param1,"column2",param2...);
        //DELETE FROM user_table WHERE id=?
        jt.deletev("user_table","id",id);
    }
    public User loadUser(int id) {
        //1 Variable parameter condition loading SELECT * FROM user_table WHERE id=? LIMIT 1
        return jt.loadv("user_table",User.class,"id",id);
    }
    public List<User> selectUser(UserParam param) {
        //Automatically ignore null parameters
        //SELECT * FROM user_table WHERE name LIKE '%?%' AND birthday BETWEEN ? AND ? ORDER BY name ASC,birthday desc;
        return jt.queryv("user_table",User.class,"name%",param.getName(),"birthday:between",param.getBeginBirthday(),param.getEndBirthday(),"name:asc","birthday:desc");
    }
    // Paging single table query
    public LimitResult<User> pagingUser(UserParam param) {
        //Automatically ignore null parameters
        //SELECT * FROM user_table WHERE (name = 'name' OR nick_name LIKE '%name%') AND birthday>=beginBirthday AND birthday<=endBirthday ORDER BY birthday DESC LIMIT limit OFFSET offset; 
        List<Condition> conditions=jt.conditions(jt.orOne("name","nick_name%",param.getName()),"birthday>=",param.getBeginBirthday(),"birthday<=",param.getEndBirthday());
        return jt.query("user_table",User.class,conditions,Arrays.asList(F("birthday").desc()),LimitRange.of(param.getLimit(), param.getOffset()));
    }
    // Left join pagination query
    public LimitResult<User> pagingUserLeftJoinDept(UserParam param) {
        //Automatically ignore null parameters
        //SELECT u.name,d.name dept_name FROM user_table u LEFT JOIN dept d on u.dept_id=d.id
        //    WHERE (u.name = 'name' OR u.nick_name LIKE '%name%') AND u.birthday>=beginBirthday AND u.birthday<=endBirthday 
        //    ORDER BY birthday DESC LIMIT limit OFFSET offset;         
        List<Condition> conditions=jt.conditions(jt.orOne("u.name","u.nick_name%",param.getName()),
            "u.birthday>=",param.getBeginBirthday(),"u.birthday<=",param.getEndBirthday());
        return jt.query(
            T("user_table").as("u").leftJoin(T("dept_table").as("d")).on(F("u","dept_id").eq(F("d","id"))),
            User.class,
            SL("u.name","d.name dept_name"),
            conditions,
            jt.orders("birthday:desc"),
            LimitRange.of(param.getLimit(), param.getOffset())
        );
    }
}