leaflet hello world

Jpa @Query 复杂查询（联表，动态条件，分页）

Jpa 这里不做介绍了，直接上干货了。

两张表的结构

用户表和书籍表，用户创建书籍。

DDL

create table user_info
(
    id          int(10) auto_increment
        primary key,
    name        varchar(32)                         not null comment '用户名称',
    pwd         varchar(32)                         not null comment '用户密码',
    create_time timestamp default CURRENT_TIMESTAMP not null comment '创建时间'
);

create table book_info
(
    id          int(10) auto_increment primary key,
    name        varchar(32)                         not null comment '书籍名称',
    create_user int(10)                             not null comment '创建人ID',
    create_time timestamp default CURRENT_TIMESTAMP not null comment '创建时间'
)
    comment '书籍表' engine = InnoDB;

实体

@Entity
@Table(name = "user_info")
public class UserInfo {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(columnDefinition = "int(10) COMMENT ''")
    private int id;

    @Column(name = "name", columnDefinition = "varchar(32) not null comment '用户名称'")
    private String name;

    @Column(name = "pwd", columnDefinition = "varchar(32) not null comment '用户密码'")
    private String pwd;

    @Column(name = "create_time", columnDefinition = "timestamp default CURRENT_TIMESTAMP not null COMMENT '创建时间'", insertable = false, updatable = false)
    @Temporal(TemporalType.TIMESTAMP)
    private Date createTime;

}

@Entity
@Table(name = "book_info")
public class BookInfo {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(columnDefinition = "int(10) COMMENT ''")
    private int id;

    @Column(name = "name", columnDefinition = "varchar(32) not null comment '书籍名称'")
    private String name;

    @Column(name = "create_user", columnDefinition = "int(10) not null COMMENT '创建人ID'")
    private int createUser;

    @Column(name = "create_time", columnDefinition = "timestamp default CURRENT_TIMESTAMP not null COMMENT '创建时间'", insertable = false, updatable = false)
    @Temporal(TemporalType.TIMESTAMP)
    private Date createTime;

    @Transient
    private UserInfo userInfo;

    //默认构造方法
    public BookInfo() {
    }

    //联表查询用到的构造方法
    public BookInfo(BookInfo bookInfo, UserInfo userInfo) {
        this.id = bookInfo.id;
        this.name = bookInfo.name;
        this.createUser = bookInfo.createUser;
        this.createTime = bookInfo.createTime;
        this.userInfo = userInfo;

    }

}

其中 BookInfo(BookInfo bookInfo, UserInfo userInfo) 是我们联表查询需要的构造方法。

JPQL

JPQL 即一种面向对象的 SQL 语法结构，熟悉 JPA 的应该也对 JPQL 很熟悉，这里不做解释，直接上干货了。

@Repository
public interface UserInfoDao extends JpaRepository<UserInfo, Long> {

    UserInfo findByNameAndPwd(String name, String pwd);

    @Query(value = "SELECT new BookInfo (b,u) FROM BookInfo b"
            + " left join UserInfo u on b.createUser = u.id"
            + " WHERE 1=1"
            + " and (u.name like CONCAT('%',?1,'%') OR ?1 IS NULL)"
            + " and (b.name like CONCAT('%',?2,'%')  OR ?2 IS NULL)",
            countQuery = "SELECT COUNT(b.id) FROM BookInfo b"
                    + " left join UserInfo u on b.createUser = u.id"
                    + " WHERE 1=1"
                    + " and (u.name like CONCAT('%',?1,'%') OR ?1 IS NULL)"
                    + " and (b.name like CONCAT('%',?2,'%')  OR ?2 IS NULL)")
    Page<BookInfo> queryBookPage(String userName, String bookName, Pageable pageable);
}

• OR ?1 IS NULL 语法作为动态查询支持，前提是你要把查询条件处理一下，比如 空串 转为 NULL 等。
• new BookInfo(b,u) 自然就是将联表查询结果保存啦
• pageable 参数，JPA 会自动识别 pageable 为 sql 加上分页查询。

结束

到此为止就结束了，是不是很“简单”。

---

转载请注明来源，欢迎对文章中的引用来源进行考证，欢迎指出任何有错误或不够清晰的表达。可以在下面评论区评论，也可以邮件至 rockeycui@163.com