将系统的数据库从MySQL 5.5迁移到PostgreSQL 9.1(二)
group_id),
10
CONSTRAINT FK_recipient_recipientgroup_recipient FOREIGN KEY (recipient_id) REFERENCES recipient (id),
11
CONSTRAINT FK_recipient_recipientgroup_recipient_group FOREIGN KEY (recipient_group_id) REFERENCES recipient_group (id)
12
);
代码修改
----------
* 将SpringSide 3.3.4中提供的IdEntity类修改如下
01
/**
02
* 参考SpringSide3，统一定义id的entity基类.
03
*
04
* 基类统一定义id的属性名称、数据类型、列名映射及生成策略.
05
* 子类可重载getId()函数重定义id的列名映射和生成策略.
06
*/ www.2cto.com
07
//JPA 基类的标识
08
@MappedSuperclass
09
public abstract class IdEntity {
10
11
protected Long id;
12
13
@Id
14
// @GeneratedValue(strategy = GenerationType.AUTO)
15
@GeneratedValue(strategy = GenerationType.IDENTITY)
16
@Column(unique = true, nullable = false)
17
public Long getId() {
18
return this.id;
19
}
20
21
public void setId(Long id) {
22
this.id = id;
23 www.2cto.com
}
24
25
}
* 代码中的SQL/HQL 更改
01
public List findExceptLeakageDetailList(String ids) {
02
String queryString = "SELECT * FROM leakage_detail "
03
+ "WHERE " // -- DATE_FORMAT(find_date, '%Y%m')<(DATE_FORMAT(NOW(), '%Y%m')-1) AND
04
+ "CONCAT(find_date, find_process) IN ( "
05
+ "SELECT CONCAT(find_date, find_process) AS xx "
06
+ "FROM leakage_detail WHERE id IN(" + ids + ")"
07
+ "GROUP BY find_date, find_process "
// + "HAVING COUNT(xx)>5)"; // 这种写法MySQL支持，PostgreSQL不支持！
09
+ "HAVING COUNT(CONCAT(find_date, find_process))>5) AND id IN(" + ids + ") ORDER BY find_date, find_process";
10
logger.info("Leakage模块数据导入时发送漏液异常邮件的查询sql->"+queryString);
11
Query query = getSession().createSQLQuery(queryString).addEntity(LeakageDetail.class);
12
return query.list();
13 www.2cto.com
}
01
public List getStatisticalAnalysisList() {
02
// String hql = "select workshop as name, count(id) as num from DataModel where date_format(create_at, '%Y-%m')=date_format(now(), '%Y-%m') group by workshop";
03
String sql = "select workshop as name, count(id) as num "
04
+ "from data_models "
05
// + "where date_format(create_at, '%Y-%m')=date_format(now(), '%Y-%m') " // date_format函数是MySQL专用的
06
+ "where to_char(create_at, 'yyyy-MM')=to_char(now(), 'yyyy-MM') " // PostgreSQL中的日期格式化函数是to_char
07
+ "group by workshop";
08
// Query query = getSession().createSQLQuery(hql);
09
Query query = getSession().createSQLQuery(sql).addScalar("name", Hibernate.STRING).addScalar("num", Hibernate.LONG);
10
query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
11
.setResultTransformer(Transformers.aliasToBean(StatisticalAnalysisVo.class));
12
return query.list();
13 www.2cto.com
}
* 存储过程更改
MySQL
01
DROP PROCEDURE IF EXISTS `calcUlclp`;
02
DELIMITER //
03
CREATE DEFINER=`root`@`localhost` PROCEDURE `calcUlclp`()
04
COMMENT '计算Hipot不良率的上下限的存储过程'
05
BEGIN
06
07
SELECT (@rownum := @rownum + 1) AS `id`, DATE_FORMAT(lot_no_to_date, '%Y%m') AS year_and_month,
08
`model_no`, gr