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'

7.Boolean类型要注意xml传数据时是否用引号' '，用了的话，要将Boolean改为String；并且model层中的get\set方法要注意