1、表名必须大写;
2、数据库用到Date数据类型的，model层也应为date；同时在查询时若只需展示到日期，就用to_char()函数：以下为JPA实例
如：private Date planFinishTime;//计划结束时间
private String planFinishTimeStr;//用于接收to_char()函数转换的planFinishTime
@DateTimeFormat(pattern="yyyy-MM-dd")
@Temporal(TemporalType.TIMESTAMP)
@Column(name = "PLAN_FINISH_TIME", length = 19)
public Date getPlanFinishTime() {
return planFinishTime;
}
public void setPlanFinishTime(Date planFinishTime) {
this.planFinishTime = planFinishTime;
}
@Transient
public String getPlanFinishTimeStr() {
return planFinishTimeStr;
}
public void setPlanFinishTimeStr(String planFinishTimeStr) {
this.planFinishTimeStr = planFinishTimeStr;
}
以上为model层注释
oracle查询：
TO_CHAR(t1.PLAN_FINISH_TIME,'yyyy-mm-dd') as planFinishTimeStr
MySQL查询
DATE_FORMAT(t1.PLAN_FINISH_TIME,"%Y-%m-%d") as planFinishTimeStr
3、cancat函数用法需修改
MySQL：CONCAT('%' , #{planName}, '%')
oracle：CONCAT('%' , CONCAT(#{planName}, '%'))
4、MySQL中的now()和oracle的sysdate相同，都是获取系统当前时间
5、分页语句
a.MySQL中直接使用limit;
select * from 表名 where ****** //查询预计
ORDER BY t1.PLAN_START_TIME DESC,t1.UPDATER_TIME DESC,t1.CREATOR_TIME DESC //排序
limit #{start}， #{end} //分页
b.oracle中用ROWNUM 关键字
select * from ( SELECT A.*,ROWNUM as NUM FROM (
select * from 表名 where ****** //查询预计
ORDER BY t1.PLAN_START_TIME DESC,t1.UPDATER_TIME DESC,t1.CREATOR_TIME DESC //排序
)A where ROWNUM <= #{end}+#{start} )where NUM >#{start}
6、当需要查询数据库中某一个表的字段和相关属性时有不同
a.mysql中使用：INFORMATION_SCHEMA.Columns
select t.TABLE_NAME,t.DATA_TYPE,t.COLUMN_NAME,t.COLUMN_COMMENT from INFORMATION_SCHEMA.Columns t
where t.TABLE_SCHEMA=#{dataSchema} and t.table_name='resmgr_asset'
b.oracle中使用：ALL_TAB_COLUMNS 与USER_COL_COMMENTS 链接查询
select t1.TABLE_NAME,t1.DATA_TYPE,t1.COLUMN_NAME,t2.COMMENTS
from ALL_TAB_COLUMNS t1
inner join USER_COL_COMMENTS t2 on t1.table_name= t2.table_name and t1.COLUMN_NAME=t2.COLUMN_NAME
where t1.OWNER=#{dataSchema} and t1.table_name='RESMGR_ASSET'
