hj_springdata_jpa

官方文档 -> springdata-jpa 

借鉴:  springdata-jpa学习笔记

JPA: Java持久化API,sun官方在JDK5.0后提出的java持久化规范<JSR 338 所在包 javax.persistence>;

ORM: 即Object-Relational Mapping,关系型数据库和对象之间的一个映射;

ORM映射元数据: 支持XML和注解这两种形式,元数据描述对象和表之间的映射关系;

API: 操作实体对象来对数据库执行CRUD操作;

查询语言: 面向对象而非面向数据库的查询语言(JPQL)来查询数据,避免程序的SQL语句耦合,这样更换数据库类型不用重写代码;

JPA , Hibernate , Springdata-jpa 三者之间关系:

 JPA是ORM规范,Hibernate是JPA规范的具体实现,开发者可以面向JPA规范进行持久层的开发,底层的实现是可以切换的,如不用

 Hibernate而使用TopLink等其他框架. Springdata-jpa 则在JPA之上又添加了一层抽象(Repository),持久层的开发更简化.底层实现还是

 使用的Hibernate框架来实现对数据库的CRUD操作;

以下基于Springboot项目:

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>

yml配置:

spring:
  datasource:
    type: com.zaxxer.hikari.HikariDataSource
    url: jdbc:mysql://127.0.0.1:3306/hj?useUnicode=true&characterEncoding=utf8&useSSL=false&useLegacyDatetimeCode=false&serverTimezone=Asia/Shanghai
    username: root
    password: root
  jpa:
    database-platform: org.hibernate.dialect.MySQL5InnoDBDialect
    database: MYSQL
    show-sql: true
    properties:
      hibernate.id.new_generator_mappings: true
      hibernate.connection.provider_disables_autocommit: true
      hibernate.cache.use_second_level_cache: true
      hibernate.cache.use_query_cache: false
      hibernate.generate_statistics: false
      hibernate.enable_lazy_load_no_trans: true
    hibernate:
      ddl-auto: update
# ddl-auto: 
#  create: 每次运行程序,都会重新建表,数据丢失;
#  create-drop: 每次运行程序时先建表结构,结束程序时又清空表;
#  upodate: 每次运行程序,没有表则建表,对象发生改变会对应更新表结构,原数据不会清空,会对应更新(推荐)
#  validate: 运行程序会校验数据与数据库的字段类型是否匹配,不匹配会报错
#  none: 禁用DDL处理

 springdata-jpa UML类图:

 实体类注解:

@Entity: 标注实体类

@Table: 映射的表 name="tb_hj"

@Column: 与数据库字段的绑定,同名或者基于下划线的可以不加会自动映射

@Id: 主键,每个实体类都必须有,否则报错

@GenericGenerator: Hibernate提供的主键生成策略注解

@GeneratedValue: 策略值(JPA注解)

示例: 

package com.hj.domain.entity;

import lombok.Data;
import org.hibernate.annotations.GenericGenerator;

import javax.persistence.*;

@Entity
@Table(name = "tb_hj")
@Data
public class Hj{

    @Id
    @GenericGenerator(name = "idGenerator", strategy = "uuid")
    @GeneratedValue(generator = "idGenerator")
    private String id;

    private String userName;

    private String password;

    @Column(name = "mobile", length = 11)
    private String phone;

}
/*
JPA自带的主键生成策略:
 1 TABLE: 使用一个特定的数据库表格来保存主键;
 2 SEQUENCE: 根据底层数据库的序列来生成主键,需数据库支持序列,如:Oracle.需要与generator配合使用,generator指定生成主键使用的生成器;
 3 IDENTITY: 主键由数据库自动生成(主要是支持主键自增,如Mysql);
 4 AUTO: 主键由程序控制,GenerationType的默认值.     建议主键使用雪花算法生成.
*/

JPA查询

 声明一个接口继承自 Repository或者其子接口,通常是JpaRepository

@Repository
public interface HjRepository extends JpaRepository<Hj, Long>, JpaSpecificationExecutor<Hj> {

    int countByPhone(String phone);

    List<HJ> findByUserName(String userName);

}
/*
Repository方法的Null值处理:
可以使用 Optional接口作为方法返回值来表明可能存在缺省值,典型示例 CruduRepository  --findById方法
也可以使用Spring提供的注解来处理Null值
@NonNullApi: 在包级别使用来声明参数和返回值不能为null
@NonNull: 在参数或返回值上使用,当它们不能为Null时(包级别有使用@NonNullApi,这就没必要再加)
@Nullable: 在参数或返回值上加,当它们可以为null时.
/*

/*
自定义一个BaseRepository接口继承Repository接口,从CurdRepository中copy需要暴露的curd方法.
@NoRepositoryBean:  The intermediate repository interface is annotated with @NoRepositoryBean. 
   Make sure you add that annotation to all repository interfaces for which Spring Data should not create instances at runtime.
*/

package com.hj.repository.repository;

import org.springframework.data.repository.NoRepositoryBean;
import org.springframework.data.repository.Repository;

import java.util.Optional;

/**
 * 自定义Repository，选择性暴露CRUD方法
 */
@NoRepositoryBean
public interface HjBaseRepository<T, ID> extends Repository<T, ID> {

    Optional<T> findById(ID id);

    <S extends T> S save(S entity);

}

/*
使用 @RepositoryDefinition 注解,并从CrudRepository中copy需要暴露的CRUD方法也可...
*/
package com.hj.repository;

import com.hj.domain.entity.Hj;
import org.springframework.data.repository.RepositoryDefinition;

import java.util.Optional;

@RepositoryDefinition(domainClass = HJ.class, idClass = String.class)
public interface HjRepository3 {

    Optional<HJ> findById(String id);

    HJ save(Hj hj);

}

 spingdata-jpa可通过解析方法名创建查询,框架在进行方法名解析时,会先把方法名的前缀:find...By,query...By,count...By等截取掉,

 然后对其他部分进行解析,多条件用And或者Or连接,从而创建大量基本的查询

关键字: And , Or , Is,Equase , Between , LessThan , LessThanEqual, GreaterThan , GreaterThanEqual , After ,

            Before , IsNull , IsNotNull,NotNull , Like , NotLike , StartingWith , EndingWith , Containing , OrderBy ,

           Not , In , NotIn , True , False , IgnoreCase ...

Spring Data Jpa 方法名解析规则参看官方文档:   官方文档4.4.3. Property Expressions

支持使用 first , top , Distinct 关键字来限制查询结果:

Hj findFirstByUserNameOrderByUserNameAsc(String userName);

List<Hj> findTop10ByUserName(String userName, Sort sort);
    
List<Hj> findTop10ByUserName(String userName, Pageable pageable);

自定义查询 @Query  在方法上标注@Query注解,自定义一个 JPQL 查询语句即可

@Query("select hj from Hj hj where hj.userName like %?1%")   //位置绑定传参
List<Hj> getByUserNameLike(String userName);

使用命名参数传参,更具可读性

@Query("select hj from Hj hj where hj.userName = :userName or hj.phone = :phone")
Hj getByUserNameOrPhone(@Param("userName") String userName, @Param("phone") String phone);

还可以使用表达式 SpEL Expressions

/*
 entityName的解析方式: 如果实体类在@Entity注解上设置了name属性,则用设置的属性值,否则,是使用实体类的简单类名.
为避免在@Query注解使用实际的实体类名,可以使用 #{#entityName} 进行代替
*/
@Query("select hj from #{#entityName} hj where hj.phone= ?1")
User getByPhone(String phone);

原生Native Query    nativeQuery=true 即可.然后写原生查询语句.也是支持位置绑定或命名传参.

不支持对原生查询进行动态排序,可以通过自己指定计数查询countQuery来使用原生查询进行分页,排序..

@Query(value = "select * from tb_hj hj where hj.mobile = ?1", nativeQuery = true)
Hj queryByPhone(String phone);

@Query(value = "select * from tb_hj hj where hj.mobile = :phone", nativeQuery = true)
Hj queryByPhone(@Param("phone") String phone);

@Query(value = "select * from tb_hj hj where hj.user_name like %?1%",
            countQuery = "select count(1) from tb_hj hj where hj.user_name = %?1%",
            nativeQuery = true)
Page<Hj> queryByUserNameLike(String userName, Pageable pageable);

修改,删除 需要 再加上 @Modifying 注解.

@Transactional()
@Modifying
@Query("update Hj hj set hj.phone= ?2 where hj.userName = ?1")
int updatePhoneByUserName(String userName, String phone);

@Transactional()
@Modifying
@Query("delete from Hj where userName = ?1")
void deleteByUserName(String userName);

//注意：Modifying queries can only use void or int/Integer as return type!

分页查询及排序,在方法中直接传入 Pageable ,Sort ,通常是方法的最后一个参数.

@Query("select hj from Hj hj where hj.userName like %?1%")
Page<Hj> findByUserNameLike(String userName, Pageable pageable);

@Query("select hj from Hj hj where hj.userName like %?1%")
List<Hj> findByUserNameAndSort(String userName, Sort sort);

/*
*使用 PageRequest的of方法可以创建Pageable对象
PageRequest.of(0, 5);
PageRequest.of(0, 5, Sort.Direction.ASC, "userName");
PageRequest.of(0, 5, Sort.by(Sort.Direction.ASC, "userName"));
*/

多表查询.这个对jpa来说个人觉得并不友好,会产生重复数据而且不好按需只取特定的少量字段.

大概记录下 基本的注解 @ManyToMany @OneToMany @ManyToOne 

// 多对多 A表里面调用的 多对多的 B表
 @ManyToMany(targetEntity =B.class, cascade = {CascadeType.PERSIST, CascadeType.MERGE}, fetch = FetchType.LAZY)
    @JoinTable(name = "tb_A_B", joinColumns = {@JoinColumn(name = "A_id", referencedColumnName = "id")},
            inverseJoinColumns = {@JoinColumn(name = "B_id", referencedColumnName = "id")})
private Set<B> bSet;

//一对多 部门对应员工 部门表
@OneToMany(mappedBy = "department", cascade = CascadeType.ALL, fetch = FetchType.LAZY)
private Set<Employee> employees;
// 多对一 员工对应部门 员工表
@ManyToOne(targetEntity = Department.class, cascade = CascadeType.ALL, fetch = FetchType.LAZY)
@JoinColumn(name = "dept_id")
private Department department;

//这查询 很容易报错.循环依赖的问题. LomBok 的@Data 导致的.

//Lombok的@Data注解相当于@Getter、@Setter、@RequiredArgsConstructor、@ToString、@EqualsAndHashCode这几个注解,
// 反编译看Lombok生成的toString()方法

// Employee
public String toString() {
  return "Employee(id=" + getId() + ", empName=" + getEmpName() + ", empJob=" + getEmpJob() + ",
  deptId=" + getDeptId() + ", department=" + getDepartment() + ")";
}
// Department
public String toString() {
  return "Department(id=" + getId() + ", deptName=" + getDeptName() + ", employees=" + getEmployees() + ")";
}

// toString()方法覆盖了整个类的所有属性.导致循环依赖问题.
//解决: 去掉@Data注解,替换成 @Setter @Getter @EquaseAndHashCode ,重写 toString方法

// Department
@Override
public String toString() {
    return "Department{" +
            "id='" + id + '\'' +
            ", deptName='" + deptName + '\'' +
            '}';
}
// Employee
@Override
public String toString() {
    return "Employee{" +
            "id='" + id + '\'' +
            ", empName='" + empName + '\'' +
            ", empJob='" + empJob + '\'' +
            ", deptId='" + deptId + '\'' +
            ", department=" + department +
            '}';
}

Hibernate懒加载异常,无法处理代理类,No Session;

org.hibernate.LazyInitializationException: failed to lazily initialize a collection of 
b: com.hj.domain.entity.Hj.bSet, could not initialize proxy - no Session

/*
原因:LSpring Boot整合JPA后Hibernate的Session就交付给Spring管理,每次数据库操作后,会关闭Session,当我们想要用懒加载方式去获得数据的时候,
原来的Session已经关闭,不能获取数据,所以会抛出这样的异常.
解决方法:
在application.yml中做如下配置:
spring:
  jpa:
    open-in-view: true
    properties:
      hibernate:
        enable_lazy_load_no_trans: true
*/

 

审计Auditing      官方文档5.9Auditing

 数据库表在设计时若添加了4个审计字段.Springdata提供 @CreateBy @LastModifiedBy @CreateDate @LastModifiedDate4个注解

 来记录表中记录的创建及修改信息.  示例:

// 实体类
package com.hj.domain.entity;

import lombok.Data;
import org.hibernate.annotations.GenericGenerator;
import org.springframework.data.annotation.CreatedBy;
import org.springframework.data.annotation.CreatedDate;
import org.springframework.data.annotation.LastModifiedBy;
import org.springframework.data.annotation.LastModifiedDate;
import org.springframework.data.jpa.domain.support.AuditingEntityListener;

import javax.persistence.*;
import java.util.Date;
import java.util.Set;

@Entity
@EntityListeners(AuditingEntityListener.class)
@Table(name = "tb_hj")
@Data
public class Hj{

    @Id
    @GenericGenerator(name = "idGenerator", strategy = "uuid")
    @GeneratedValue(generator = "idGenerator")
    private String id;

    private String userName;

    @Column(name = "mobile", unique = true, length = 11)
    private String phone;

    @CreatedDate
    @Column(name = "created_date", updatable = false)
    private Date createdDate;

    @CreatedBy
    @Column(name = "created_by", updatable = false, length = 64)
    private String createdBy;

    @LastModifiedDate
    @Column(name = "updated_date")
    private Date updatedDate;

    @LastModifiedBy
    @Column(name = "updated_by", length = 64)
    private String updatedBy;

}

//实现AuditorAware接口
package com.hj.handle;

import org.springframework.data.domain.AuditorAware;
import org.springframework.stereotype.Component;

import java.util.Optional;

@Component
public class AuditorAwareImpl implements AuditorAware<String> {

    @Override
    public Optional<String> getCurrentAuditor() {
      //这简单返回一个admin代表当前用户名
        return Optional.of("admin");
    }

}

//启用Jpa审计功能 ,在springboot启动类上添加@EnableJpaAuditing注解
package com.hj;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.data.jpa.repository.config.EnableJpaAuditing;

@SpringBootApplication
@EnableJpaAuditing
public class HjApplication {

    public static void main(String[] args) {
        SpringApplication.run(HjApplication.class, args);
    }
}
或者写个配置文件
@Configuration
@EnableJpaRepositories("com.hj.repository")
@EnableJpaAuditing(auditorAwareRef = "springSecurityAuditorAware")
@EnableTransactionManagement
public class HibernateJpaConfiguration {
    
}

 

Native 动态 SQL 拼接,本地查询 实战代码:

  createNativeQuery()方法 需要注意: sql的字段别名需与entity的属性名相同.

/**
     * 根据条件获取分页 MemberQueryModel 数据
     *
     * @param req        参数
     * @param pageable   分页
     * @param queryCount 列表--查询总条数, 导出-不查总条数
     * @return Page--Member
     */
    public Page<MemberQueryModel> listMemberQueryModel(ReqMemberVo req, Pageable pageable, Boolean queryCount) {
        Map<String, String> map = this.getConditionMemberQuerySql(req);
        String dataSql = map.get("dataSql");
        if (queryCount) {
            String countSql = map.get("countSql");
            Query countQuery = entityManager.createNativeQuery(countSql);
            BigInteger count = (BigInteger) countQuery.getSingleResult();
            long c = Long.parseLong(count.toString());
            if (c > 0) {
                Query dataQuery = entityManager.createNativeQuery(
                    dataSql, MemberQueryModel.class).setFirstResult(pageable.getPageNumber() * pageable.getPageSize()).setMaxResults(pageable.getPageSize());
                List<MemberQueryModel> models = dataQuery.getResultList();
                return new PageImpl<>(models, pageable, c);
            } else {
                return Page.empty();
            }
        } else {
            Query dataQuery = entityManager.createNativeQuery(
                dataSql, MemberQueryModel.class).setFirstResult(pageable.getPageNumber() * pageable.getPageSize()).setMaxResults(pageable.getPageSize());
            List<MemberQueryModel> models = dataQuery.getResultList();
            return new PageImpl<>(models, pageable, models.size());
        }

    }

 

 /**
     * 带条件查询-member表sql封装
     *
     * @return sql-map集合
     */
    private Map<String, String> getConditionMemberQuerySql(ReqMemberVo req) {
        Map<String, String> map = new HashMap<>(2);
        StringBuilder countSql = new StringBuilder("select count(DISTINCT m.id) from member as m\n");
        StringBuilder dataSql = new StringBuilder("SELECT distinct\n" +
            "m.id id,\n" +
            "m.work_wechat_external_user_id ,\n" +
            "m.nick_name ,\n" +
            "m.member_id ,\n" +
            "m.member_name ,\n" +
            "m.member_flow_no ,\n" +
            "m.month_active ,\n" +
            "m.create_date \n" +
            "FROM member m\n"
        );
        // 连接 member_tag表
        if (StringUtils.isNotBlank(req.getTagId())) {
            countSql.append("join member_tag mt on m.work_wechat_external_user_id=mt.work_wechat_external_user_id\n");
            dataSql.append("join member_tag mt on m.work_wechat_external_user_id=mt.work_wechat_external_user_id\n");
        }
        // 左接 user_member_bind 表
        if ((null != req.getMonthActiveType() && null != req.getMonthActive()) ||
            Objects.nonNull(req.getUnconcactDays()) || StringUtils.isNotBlank(req.getUserId())) {
            countSql.append(" join user_member_bind umb on m.work_wechat_external_user_id=umb.work_wechat_external_user_id\n");
            dataSql.append(" join user_member_bind umb on m.work_wechat_external_user_id=umb.work_wechat_external_user_id\n");
        }
        // 查询条件
        countSql.append(" where 1 = 1\n");
        dataSql.append(" where 1 = 1\n");
        // 月活跃
        if (null != req.getMonthActiveType() && null != req.getMonthActive()) {
            if (req.getPerson()) {
                if (1 == req.getMonthActiveType()) {
                    // 大于
                    countSql.append(" and umb.month_active>").append(req.getMonthActive());
                    dataSql.append(" and umb.month_active>").append(req.getMonthActive());
                } else {
                    countSql.append(" and umb.month_active<").append(req.getMonthActive());
                    dataSql.append(" and umb.month_active<").append(req.getMonthActive());
                }
            } else {
                if (1 == req.getMonthActiveType()) {
                    // 大于
                    countSql.append(" and m.month_active>").append(req.getMonthActive());
                    dataSql.append(" and m.month_active>").append(req.getMonthActive());
                } else {
                    countSql.append(" and m.month_active<").append(req.getMonthActive());
                    dataSql.append(" and m.month_active<").append(req.getMonthActive());
                }
            }

        }

        if (Objects.nonNull(req.getUnconcactDays())) {
            Instant instant = Instant.now().minus(Duration.ofDays(req.getUnconcactDays()));
            Date date = Date.from(instant);
            SimpleDateFormat dateFormat = new SimpleDateFormat(ApplicationConstant.DATE_FORMATTER_YMDHMS);
            String userLastConcactTime = dateFormat.format(date);
            countSql.append(" and umb.user_last_concact_time < '").append(userLastConcactTime).append("'");
            dataSql.append(" and umb.user_last_concact_time < '").append(userLastConcactTime).append("'");
        }
        if (StringUtils.isNotBlank(req.getUserId())) {
            countSql.append(" and umb.user_id = '").append(req.getUserId()).append("'");
            dataSql.append(" and umb.user_id = '").append(req.getUserId()).append("'");
        }

        if (StringUtils.isNotBlank(req.getTagId())) {
            countSql.append(" and mt.tag_id = '").append(req.getTagId()).append("'");
            dataSql.append(" and mt.tag_id = '").append(req.getTagId()).append("'");
        }
        if (StringUtils.isNotBlank(req.getName())) {
            countSql.append(" and m.member_name = '").append(req.getName()).append("'");
            dataSql.append(" and m.member_name = '").append(req.getName()).append("'");
        }
        if (StringUtils.isNotBlank(req.getPhone())) {
            countSql.append(" and m.member_phone = '").append(req.getPhone()).append("'");
            dataSql.append(" and m.member_phone = '").append(req.getPhone()).append("'");
        }
        if (StringUtils.isNotBlank(req.getWxNickName())) {
            countSql.append(" and m.nick_name = '").append(req.getWxNickName()).append("'");
            dataSql.append(" and m.nick_name = '").append(req.getWxNickName()).append("'");
        }
        if (StringUtils.isNotBlank(req.getFlowNo())) {
            countSql.append(" and m.member_flow_no = '").append(req.getFlowNo()).append("'");
            dataSql.append(" and m.member_flow_no = '").append(req.getFlowNo()).append("'");
        }
        if (StringUtils.isNotBlank(req.getBeginJoinDay())) {
            countSql.append(" and TO_DAYS(m.create_date) >= TO_DAYS('").append(req.getBeginJoinDay()).append("')");
            dataSql.append(" and TO_DAYS(m.create_date) >= TO_DAYS('").append(req.getBeginJoinDay()).append("')");
        }
        if (StringUtils.isNotBlank(req.getEndJoinDay())) {
            countSql.append(" and TO_DAYS(m.create_date) <= TO_DAYS('").append(req.getEndJoinDay()).append("')");
            dataSql.append(" and TO_DAYS(m.create_date) <= TO_DAYS('").append(req.getEndJoinDay()).append("')");
        }
        map.put("countSql", countSql.toString());
        map.put("dataSql", dataSql.toString());
        return map;
    }

  就先到这吧~反正觉得不好用~~~

 

   private String getQuerySql(ReqMemberVo req) {
        //如果req.getPerson为true 是查询我的学员
        //查询 sql 拼接
        StringBuilder dataSql = new StringBuilder("SELECT\n" +
            "m.id id,\n" +
            "m.member_id ,\n" +
            "m.nick_name ,\n" +
            "m.member_name ,\n" +
            "m.member_flow_no ,\n" +
            "m.create_date ,\n" +
            "mt.tag_name ,\n" +
            "umb1.user_name ,\n" +
            "umb2.month_active ,\n" +
            "umb2.user_last_concact_time \n" +
            "FROM member m\n"
        );
        // 左连 member_tag 表
        dataSql.append("left join \n");
        //带有标签
        if (StringUtils.isNotBlank(req.getTagId())) {
            dataSql.append(
                " (SELECT tag_id,work_wechat_external_user_id, GROUP_CONCAT(tag_name  separator ',') tag_name \n" +
                    "     from member_tag  GROUP BY work_wechat_external_user_id \n" +
                    "HAVING tag_id='");
            dataSql.append(req.getTagId());
            dataSql.append(
                "' ) mt\n" +
                    "on m.work_wechat_external_user_id=mt.work_wechat_external_user_id\n"
            );
        } else {
            dataSql.append(
                " (SELECT work_wechat_external_user_id, GROUP_CONCAT(tag_name  separator ',') tag_name \n" +
                    "     from member_tag  GROUP BY work_wechat_external_user_id \n" +
                    ") mt\n" +
                    "on m.work_wechat_external_user_id=mt.work_wechat_external_user_id\n"
            );
        }
        if (req.getPerson()) {
            dataSql.append("join \n");
        } else {
            dataSql.append("left join \n");
        }
        dataSql.append(
            " (SELECT work_wechat_external_user_id, GROUP_CONCAT(user_name  separator ',') user_name \n" +
                "                from user_member_bind  GROUP BY work_wechat_external_user_id \n" +
                ") umb1\n" +
                "on m.work_wechat_external_user_id=umb1.work_wechat_external_user_id \n");
        if (req.getPerson()) {
            dataSql.append("join \n");
        } else {
            dataSql.append("left join \n");
        }
        // 未联系时间
        if (null != req.getUnconcactDays()) {
            Instant instant = Instant.now().minus(Duration.ofDays(req.getUnconcactDays()));
            Date date = Date.from(instant);
            SimpleDateFormat dateFormat = new SimpleDateFormat(ApplicationConstant.DATE_FORMATTER_YMDHMS);
            String userLastConcactTime = dateFormat.format(date);
            dataSql.append(
                " (SELECT user_id,work_wechat_external_user_id, month_active,max(user_last_concact_time) user_last_concact_time \n" +
                    "  from user_member_bind  GROUP BY work_wechat_external_user_id \n" +
                    "HAVING UNIX_TIMESTAMP(user_last_concact_time) < UNIX_TIMESTAMP('");
            dataSql.append(userLastConcactTime);
            dataSql.append(
                "')" +
                    " ) umb2 \n" +
                    "on m.work_wechat_external_user_id=umb2.work_wechat_external_user_id \n" +
                    "where 1=1 "
            );
        } else {
            dataSql.append(
                " (SELECT user_id,work_wechat_external_user_id, month_active,max(user_last_concact_time) user_last_concact_time \n" +
                    "  from user_member_bind  GROUP BY work_wechat_external_user_id \n" +
                    ") umb2 \n" +
                    "on m.work_wechat_external_user_id=umb2.work_wechat_external_user_id \n" +
                    "where 1=1 "
            );
        }
        if (StringUtils.isNotBlank(req.getUserId())) {
            dataSql.append(" and umb2.user_id = '").append(req.getUserId()).append("'");
        }
        if (req.getPerson()) {
            if (null != req.getMonthActiveType() && null != req.getMonthActive()) {
                if (1 == req.getMonthActiveType()) {
                    // 大于
                    dataSql.append(" and umb2.month_active>").append(req.getMonthActive());
                } else {
                    dataSql.append(" and umb2.month_active<").append(req.getMonthActive());
                }
            }
        } else {
            if (null != req.getMonthActiveType() && null != req.getMonthActive()) {
                if (1 == req.getMonthActiveType()) {
                    // 大于
                    dataSql.append(" and m.month_active>").append(req.getMonthActive());
                } else {
                    dataSql.append(" and m.month_active<").append(req.getMonthActive());
                }
            }
        }

        if (StringUtils.isNotBlank(req.getName())) {
            dataSql.append(" and m.member_name = '").append(req.getName()).append("'");
        }
        if (StringUtils.isNotBlank(req.getPhone())) {
            dataSql.append(" and m.member_phone = '").append(req.getPhone()).append("'");
        }
        if (StringUtils.isNotBlank(req.getWxNickName())) {
            dataSql.append(" and m.nick_name = '").append(req.getWxNickName()).append("'");
        }
        if (StringUtils.isNotBlank(req.getFlowNo())) {
            dataSql.append(" and m.member_flow_no = '").append(req.getFlowNo()).append("'");
        }
        log.info("查询的sql:\n" + dataSql + "\n");
        return dataSql.toString();
    }

复杂的sql语句....执行效率很低....
-- explain
SELECT
m.nick_name nickName,
m.member_name name,
m.member_flow_no flowNo,
m.create_date joinTime,
mt.tag_name tags,
umb1.user_name inviteUserNames,
umb2.month_active monthActive,
umb2.user_last_concact_time userLastConcactTime
FROM member m
left join 
(
 SELECT work_wechat_external_user_id, GROUP_CONCAT(tag_name  separator ',') tag_name 
                from member_tag  GROUP BY work_wechat_external_user_id 
) mt
on m.work_wechat_external_user_id=mt.work_wechat_external_user_id
left JOIN
(
 SELECT work_wechat_external_user_id, GROUP_CONCAT(user_name  separator ',') user_name 
                from user_member_bind  GROUP BY work_wechat_external_user_id 
) umb1
on m.work_wechat_external_user_id=umb1.work_wechat_external_user_id

left JOIN
(
 SELECT work_wechat_external_user_id, month_active,max(user_last_concact_time) user_last_concact_time 
                from user_member_bind  GROUP BY work_wechat_external_user_id 
) umb2
on m.work_wechat_external_user_id=umb2.work_wechat_external_user_id