小弟我发贴求得如下原生SQL语句,小弟不才,不知道如何该如何写JPA的JPQL查询语句,或者是Hibernate的HQL查询语句,所有请教各位朋友:原贴地址:http://topic.csdn.net/u/20101220/11/45f2c723-0050-485f-b196-5a9e0f1dd730.html?39918原贴内容:小弟新手,现在遇到分组问题,求,JPA或者Hibernate的查询语句,谢谢
现有如下表:CREATE TABLE `mailtemplate` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`mailContent` text COMMENT '模板内容',
`mailType` int(11) DEFAULT NULL COMMENT '邮件内容',
`lastEditTime` datetime DEFAULT NULL COMMENT '最后一次编辑日期',
`orderNumber` int(11) DEFAULT NULL COMMENT '排列序号',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8要求:
以mailType字段分组,并且提取每个分组orderNumber字段为最小值的所有记录,求的原生sql语句一:
select mt.* from mailtemplate as mt
inner join (
select min(orderNumber) min_num,mailType from mailtemplate group by mailType
) t
on mt.orderNumber=t.min_num and mt.mailType = t.mailType;原生sql语句二:select * from mailtemplate where (orderNumber , mailType) in (select min(orderNumber),mailType from mailtemplate group by mailType)原生sql语句三:select a.*
from mailtemplate a,
(select min(m.orderNumber) as orderN, m.mailType as mailT
from mailtemplate m
group by m.mailType) b
where a.orderNumber = b.orderN
and a.mailType = b.mailT注以上原生SQL都经过MYSQL5.5测试,并能得到正确的结果,现不知,如何转化为JPA或者是Hibernate使用的查询语句,只要其中任意一条sql语句即可,谢谢,再次感谢提供以上三条原生SQL的仁兄!!~~!!!以下是我写的JAVA BntityBean @Entity
@Table(name = "mailtemplate")
public class MailTemplate implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Basic(optional = false)
@Column(name = "id")
private Integer id;
@Lob
@Column(name = "mailContent")
private String mailContent;
@Column(name = "mailType")
private Integer mailType;
@Column(name = "lastEditTime")
@Temporal(TemporalType.TIMESTAMP)
private Date lastEditTime;
@Column(name = "orderNumber")
private Integer orderNumber; public SystemMailTemplate() {
} public SystemMailTemplate(Integer id) {
this.id = id;
} public Integer getId() {
return id;
} public void setId(Integer id) {
this.id = id;
} public String getMailContent() {
return mailContent;
} public void setMailContent(String mailContent) {
this.mailContent = mailContent;
} public Integer getMailType() {
return mailType;
} public void setMailType(Integer mailType) {
this.mailType = mailType;
} public Date getLastEditTime() {
return lastEditTime;
} public void setLastEditTime(Date lastEditTime) {
this.lastEditTime = lastEditTime;
} public Integer getOrderNumber() {
return orderNumber;
} public void setOrderNumber(Integer orderNumber) {
this.orderNumber = orderNumber;
} @Override
public int hashCode() {
int hash = 0;
hash += (id != null ? id.hashCode() : 0);
return hash;
} @Override
public boolean equals(Object object) {
// TODO: Warning - this method won't work in the case the id fields are not set
if (!(object instanceof SystemMailTemplate)) {
return false;
}
SystemMailTemplate other = (SystemMailTemplate) object;
if ((this.id == null && other.id != null) || (this.id != null && !this.id.equals(other.id))) {
return false;
}
return true;
} @Override
public String toString() {
return "soago.com.model.SystemMailTemplate[id=" + id + "]";
}}
以下是我写转换为的JPQL语句,但全部是错误的,请朋友们指点:// String sql = "SELECT s FROM SystemMailTemplate s WHERE (s.orderNumber,s.mailType) in (select min(m.orderNumber),m.mailType FROM SystemMailTemplate m GROUP BY m.mailType)";
// String sql = "SELECT s FROM SystemMailTemplate s WHERE (s.orderNumber,s.mailType) IN (SELECT MIN(m.orderNumber),m.mailType FROM SystemMailTemplate m GROUP BY m.mailType)";
// String sql = "SELECT s FROM SystemMailTemplate s WHERE (s.orderNumber,s.mailType) IN (SELECT min(m.orderNumber) as orderNumber, m.mailType as mailType FROM SystemMailTemplate m group by m.mailType)";注其中: IN (SELECT min(m.orderNumber) as orderNumber, m.mailType as mailType FROM SystemMailTemplate m group by m.mailType)这部分JPQL查询语句能得到分组后正确结果,但整体语句有语法错误,望指点,谢谢求正确的JPA或者Hibernate查询语句,再次感谢!~~!
现有如下表:CREATE TABLE `mailtemplate` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`mailContent` text COMMENT '模板内容',
`mailType` int(11) DEFAULT NULL COMMENT '邮件内容',
`lastEditTime` datetime DEFAULT NULL COMMENT '最后一次编辑日期',
`orderNumber` int(11) DEFAULT NULL COMMENT '排列序号',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8要求:
以mailType字段分组,并且提取每个分组orderNumber字段为最小值的所有记录,求的原生sql语句一:
select mt.* from mailtemplate as mt
inner join (
select min(orderNumber) min_num,mailType from mailtemplate group by mailType
) t
on mt.orderNumber=t.min_num and mt.mailType = t.mailType;原生sql语句二:select * from mailtemplate where (orderNumber , mailType) in (select min(orderNumber),mailType from mailtemplate group by mailType)原生sql语句三:select a.*
from mailtemplate a,
(select min(m.orderNumber) as orderN, m.mailType as mailT
from mailtemplate m
group by m.mailType) b
where a.orderNumber = b.orderN
and a.mailType = b.mailT注以上原生SQL都经过MYSQL5.5测试,并能得到正确的结果,现不知,如何转化为JPA或者是Hibernate使用的查询语句,只要其中任意一条sql语句即可,谢谢,再次感谢提供以上三条原生SQL的仁兄!!~~!!!以下是我写的JAVA BntityBean @Entity
@Table(name = "mailtemplate")
public class MailTemplate implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Basic(optional = false)
@Column(name = "id")
private Integer id;
@Lob
@Column(name = "mailContent")
private String mailContent;
@Column(name = "mailType")
private Integer mailType;
@Column(name = "lastEditTime")
@Temporal(TemporalType.TIMESTAMP)
private Date lastEditTime;
@Column(name = "orderNumber")
private Integer orderNumber; public SystemMailTemplate() {
} public SystemMailTemplate(Integer id) {
this.id = id;
} public Integer getId() {
return id;
} public void setId(Integer id) {
this.id = id;
} public String getMailContent() {
return mailContent;
} public void setMailContent(String mailContent) {
this.mailContent = mailContent;
} public Integer getMailType() {
return mailType;
} public void setMailType(Integer mailType) {
this.mailType = mailType;
} public Date getLastEditTime() {
return lastEditTime;
} public void setLastEditTime(Date lastEditTime) {
this.lastEditTime = lastEditTime;
} public Integer getOrderNumber() {
return orderNumber;
} public void setOrderNumber(Integer orderNumber) {
this.orderNumber = orderNumber;
} @Override
public int hashCode() {
int hash = 0;
hash += (id != null ? id.hashCode() : 0);
return hash;
} @Override
public boolean equals(Object object) {
// TODO: Warning - this method won't work in the case the id fields are not set
if (!(object instanceof SystemMailTemplate)) {
return false;
}
SystemMailTemplate other = (SystemMailTemplate) object;
if ((this.id == null && other.id != null) || (this.id != null && !this.id.equals(other.id))) {
return false;
}
return true;
} @Override
public String toString() {
return "soago.com.model.SystemMailTemplate[id=" + id + "]";
}}
以下是我写转换为的JPQL语句,但全部是错误的,请朋友们指点:// String sql = "SELECT s FROM SystemMailTemplate s WHERE (s.orderNumber,s.mailType) in (select min(m.orderNumber),m.mailType FROM SystemMailTemplate m GROUP BY m.mailType)";
// String sql = "SELECT s FROM SystemMailTemplate s WHERE (s.orderNumber,s.mailType) IN (SELECT MIN(m.orderNumber),m.mailType FROM SystemMailTemplate m GROUP BY m.mailType)";
// String sql = "SELECT s FROM SystemMailTemplate s WHERE (s.orderNumber,s.mailType) IN (SELECT min(m.orderNumber) as orderNumber, m.mailType as mailType FROM SystemMailTemplate m group by m.mailType)";注其中: IN (SELECT min(m.orderNumber) as orderNumber, m.mailType as mailType FROM SystemMailTemplate m group by m.mailType)这部分JPQL查询语句能得到分组后正确结果,但整体语句有语法错误,望指点,谢谢求正确的JPA或者Hibernate查询语句,再次感谢!~~!
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货