昨天晚上遇到一个需求，每天早上要生成一份报告给各个部门的Leader。实现方式基本上确定为HTML格式的电子邮件。但是数据方面犯了难。原因在于数据库中存储的数据是跨表的，而且还要做count统计，这样得到的结果就不是原生的MySQL表，我用的又是JPA技术。我们知道，使用JPA第一步就是映射实体，每一张表就至少对应一个实体（力求严谨，因为联合主键时一张表会对应两个对象）。可是对于灵活的查询尤其是连接查询，并不存在一个真正的表与其对应，怎么样才能解决呢？来，我们来举个“栗子”
假设我们有两张表，一张学院表，一张学生表。学院表里存着学院ID和学院名称，学生表里存着学生的基本信息，包括学号、学院ID和学生姓名（其它较复杂的属性我们不看了），正如下面的建表语句所示：
-- ----------------------------
-- Records of depts
-- ----------------------------
INSERT INTO `depts` VALUES ('1', '哲学院');
INSERT INTO `depts` VALUES ('2', '经济学院');
INSERT INTO `depts` VALUES ('3', '法学院');
INSERT INTO `depts` VALUES ('4', '教育学院');
INSERT INTO `depts` VALUES ('5', '文学院');
INSERT INTO `depts` VALUES ('6', '历史学院');
INSERT INTO `depts` VALUES ('7', '理学院');
INSERT INTO `depts` VALUES ('8', '工学院');
INSERT INTO `depts` VALUES ('9', '农学院');
INSERT INTO `depts` VALUES ('10', '医学院');
INSERT INTO `depts` VALUES ('11', '军事学院');
INSERT INTO `depts` VALUES ('12', '管理学院');
INSERT INTO `depts` VALUES ('13', '艺术学院');
-- ----------------------------
-- Records of students
-- ----------------------------
INSERT INTO `students` VALUES ('1000', '13', '鸟叔');
INSERT INTO `students` VALUES ('1001', '7', '乔布斯');
INSERT INTO `students` VALUES ('1002', '3', '阿汤哥');
INSERT INTO `students` VALUES ('1003', '3', '施瓦辛格');
INSERT INTO `students` VALUES ('1004', '2', '贝克汉姆');
INSERT INTO `students` VALUES ('1005', '3', '让雷诺');
使用Group By和不使用Group By：
我们按照往常编码那样，从一个主要的实体操作服务中暴露出EntityManager来：
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import org.springframework.stereotype.Service;
@Service
public class ObjectDaoServiceImpl implements ObjectDaoService {
@PersistenceContext
private EntityManager entityManager;
@Override
public EntityManager getEntityManager(){
return this.entityManager;
}
}
然后我们还需要和以前一样构造两个表的实体类：
学院表的实体类：
import java.io.Serializable;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;
@Entity
@Table(name="depts")
public class Depts implements Serializable {
/**
*
*/
private static final long serialVersionUID = 3602227759878736655L;
@Id
@GeneratedValue(strategy= GenerationType.AUTO)
@Column(name= "deptId")
private Integer deptId;
@Column(name= "deptName", length= 50, nullable= false)
private String deptName;
//getters and setters...
}
import java.io.Serializable;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.Table;
@Entity
@Table(name= "students")
public class Students implements Serializable {
/**
*
*/
private static final long serialVersionUID = -5942212163629824609L;
@Id
@GeneratedValue(strategy= GenerationType.AUTO)
@Column(name= "stuNo")
private Long stuNo;
@ManyToOne
@JoinColumn(name= "deptId", nullable= false)<SPAN style="WHITE-SPACE: pre"> </SPAN>
private Depts depts;
@Column(name= "stuName", length= 50, nullable= false)
private String stuName;
//getters and setters...
}
import java.io.Serializable;
public class Report implements Serializable {
/**
*
*/
private static final long serialVersionUID = 4497500574990765498L;
private Integer deptId;
private String deptName;
private Integer totalCount;
public Report(){};
public Report(Integer deptId, String deptName, Integer totalCount) {
this.deptId = deptId;
this.deptName = deptName;
this.totalCount = totalCount;
}
//getters and setters...
}
import java.util.List;
import javax.annotation.Resource;
import javax.persistence.EntityManager;
import javax.persistence.TypedQuery;
import org.springframework.stereotype.Service;
import net.csdn.blog.chaijunkun.pojo.Depts;
import net.csdn.blog.chaijunkun.pojo.Report;
import net.csdn.blog.chaijunkun.pojo.Students;
@Service
public class ReportServiceImpl implements ReportService {
@Resource
private ObjectDaoService objectDaoService;
@Override
public List<Report> getReport() {
String jpql= String.format("select new %3$s(a.deptId, a.deptName, (select count(*) from %2$s b where b.deptId= a.deptId) as totalCount) from %1$s a",
Depts.class.getName(),
Students.class.getName(),
Report.class.getName());
EntityManager entityManager= objectDaoService.getEntityManager();
//建立有类型的查询
TypedQuery<Report> reportTypedQuery= entityManager.createQuery(jpql, Report.class);
//另外有详细查询条件的在jpql中留出参数位置来(?1 ?2 ?3....)，然后在这设置
//reportTypedQuery.setParameter(1, params);
List<Report> reports= reportTypedQuery.getResultList();
return reports;
}
}
另外，向大家推荐一本书——Apress出版社出版的《Pro JPA 2 Mastering the Java trade Persistence API》，这本书详细介绍了JPA的相关技术，非常实用。
