如何编写Spring数据JPA存储库&实体连接两个表和具有多个条件的where子句

Question

我能够使用Spring Data JPA中的@OneToOne使用主键关系连接两个实体Employee和Department并实现了这个结果，但是我不知道如何将where子句应用到我的需求中。下面是查询

select * from 
  Employee e1,
  Department d1
Where e1.emp_id = d1.dep_id
  And d1.crtn_time between to_date('08-NOV-2017 00:00', 'DD-MON-YYYY hh24:mi') 
  And to_date('08-NOV-2017 20:00', 'DD-MON-YYYY hh24:mi')
  And e1.EMP_CODE='10' and e1.Desg='Clerk'

@Transactional(readOnly = true, transactionManager = "empDataTxnManager")
public interface EmpRepository extends JpaRepository<Employee, Long> {
}

员工：实体

@Entity
@Table(name = "Employee")
public class Employee {

    @Id
    @Column(name = "emp_id")
    private Integer id;
    @Column(name = "Emp_Code")
    private String empCode;
    @Column(name = "Desg")
    private String desg;

    @OneToOne(fetch=FetchType.LAZY,
            cascade=CascadeType.ALL, 
            mappedBy="emp")
    private Department department;

    ---- getters & setters omitted

部门实体

@Entity
@Table(name = "Department")
public class Department {
    @Id
    @Column(name = "dep_id")
    private Integer id;
    @Column(name = "crtn_time")
    private String crtnTime;

    @OneToOne(fetch=FetchType.LAZY)
    @JoinColumn(name = "emp_id")
    private Employee emp;

    ----getters & setters omitted

我的问题是如何在Repository类中的where子句之后应用其余的'AND'条件。类似于在存储库或服务类中编写的任何QueryByMethod(or)条件。

请建议是否有任何现有的例子可以参考以达到此要求

谢谢

Answer 1

您可以在存储库上执行以下操作：

public class YourRepo extends Serializable{
...
   public List<Entity> findEntities( Type parameter ) {
        List<Entity> resultList = new ArrayList<Entity>();
        Entity e;
        String sqlQuery = buildDynamicQuery(); // Build your complex where
        TypedQuery<Object[]> q = getEntityManager().createNamedQuery( sqlQuery , Object[].class ); //Extract custom info from both tables.
        q.setParameter( "parameterName", parameter );

        for ( Object[] element : q.getResultList() ) {
            e= new Entity();
            e.setAtribute1( elemento[0].toString() );
            e.setAtribute2( elemento[1].toString() );
            e.setAtribute3( elemento[3].toString() );
            resultList .add(e);
        }
        return resultList;
    }
}

private String buildDynamicQuery( HistoricoGIDFiltro filtroBusqueda ) {
        String sqlQuery = "SELECT filed1, field2, field3 ... FROM Employee e1,
  Department d1 WHERE e1.emp_id = d1.dep_id ";
        if ( look if it has an employ code  ) {
            sqlQuery += " AND e1.EMP_CODE='10' ";
        }
        return sqlQuery;
    }

使用上面的代码，您可以创建自定义查询，并从不同的表中提取自定义信息。