JAVA编码规范中关于sql语句规范要求
1、关键字单独占一行。(SELECT 、UPDATE、DELETE FROM、INSERT INTO、VALUES、SET、FROM、WHERE、GROUP BY 、ORDER BY 、JOIN)
2、每行显式加回车换行符'\r\n'
3、每个查询字段、条件字段、分组字段、排序字段单独占一行
4、多表连接查询字段、表名都要加别名,且一条语句只要一种连接方式。
5、续行的开始位置为第7个字符,具体缩进格式参见范本sample_sql.txt
6、变量用绑定变量或占位符!
7、为使代码清晰,“+ '\r\n'”右对齐。
---------------------------------------------------------------------------------------------------
注意缩进格式说明:
select 的第一个字段前面空一个“select”的长度,即空6个空格,下面的所有字段、表名的开始位置上下保持对齐。
也就是说,
select的字段、
from 的表名、
where 的条件字段、
group by 的分组字段、
order by 的排序字段
的开始位置都是从第7个字符开始的。delete from 、insert into 、update语句的字段的开始位置,与此相同。
","前空4个空格,"and"前空2个空格。
left join on 在同一行,每个连接字段单独占一行。
-----------------------------------------------------------------------------------------------------
建议:
1、续行的“"”与上面的对齐,例如:
String sql = " insert into " + "\r\n"
+ " ACC_BUDYEARINIT " + "\r\n"
2、在insert 语句的values子句、其他sql的where子句中,建议用绑定变量,即用"?" 或 :var_name
不要用 + var_name 写成常数。
3、与NULL判断时,用IS NULL、IS NOT NULL
4、同一条语句中只用一种连接语法,用join或者用from a,b,c where a.xx=b.xx and a.xx=c.xx ,不要混合使用。
--------------------------------------------------------------------------------------------------------
sql代码规范样式:
例一:
String sql = "select" + "\r\n"
+ " a" + "\r\n"
+ " , b" + "\r\n"
+ " , c" + "\r\n"
+ "from" + "\r\n"
+ " tab1" + "\r\n"
+ "where" + "\r\n"
+ " a=?" + "\r\n"
+ " and b=?" + "\r\n"
+ "order by" + "\r\n"
+ " c" + "\r\n"
+ " , d" + "\r\n"
例二:
String sql = "select" + "\r\n"
+ " t1.field1 a" + "\r\n"
+ " , t1.field2 b" + "\r\n"
+ " , t2.field3 c" + "\r\n"
+ "from" + "\r\n"
+ " tab1 t1" + "\r\n"
+ " , tab2 t2" + "\r\n"
+ "where" + "\r\n"
+ " t1.field1=t2.field2" + "\r\n"
+ " and t1.field1=?" + "\r\n"
+ " and t1.field2=?" + "\r\n"
+ "order by" + "\r\n"
+ " t1.field2" + "\r\n"
+ " , t2.field3" + "\r\n"
String sql = "select" + "\r\n"
+ " t1.field1 a" + "\r\n"
+ " , t1.field2 b" + "\r\n"
+ " , t2.field3 c" + "\r\n"
+ "from" + "\r\n"
+ " tab1 t1" + "\r\n"
+ " join tab2 t2 on" + "\r\n"
+ " t1.field1=t2.field2" + "\r\n"
+ "where" + "\r\n"
+ " t1.field1=?" + "\r\n"
+ " and t1.field2=?" + "\r\n"
+ "order by" + "\r\n"
+ " t1.field2" + "\r\n"
+ " , t2.field3" + "\r\n"
嵌套表样式:
注意层次。
String sql = "select" + "\r\n"
+ " a.BUDITEM_ID as BUDITEM_ID" + "\r\n"
+ " , a.BUDITEM_CODE as BUDITEM_CODE" + "\r\n"
+ " , d.ACCTITLE_DIRECT as ACCTITLE_DIRECT" + "\r\n"
+ " , d.LEAFNODEFLAG as LEAFNODEFLAG" + "\r\n"
+ "from" + "\r\n"
+ " BUD_BUDITEM as a" + "\r\n"
+ "left join " + "\r\n"
+ " ( select + "\r\n"
+ " c.ACCSET_ID" + "\r\n"
+ " , c.DATASETVER_ID" + "\r\n"
+ " , b.BUDITEM_ID" + "\r\n"
+ " , b.INITDEBBALAMT" + "\r\n"
+ " , b.INITCRDBALAMT" + "\r\n"
+ " , c.ACCTITLE_CODE" + "\r\n"
+ " from" + "\r\n"
+ " ACC_BUDYEARINIT as b" + "\r\n"
+ " , ACC_ACCTITLE as c" + "\r\n"
+ " where" + "\r\n"
+ " c.ACCSET_ID = b.ACCSET_ID" + "\r\n"
+ " and c.DATASETVER_ID = b.DATASETVER_ID" + "\r\n"
+ " and c.ACCTITLE_ID = b.ACCTITLE_ID" + "\r\n"
+ " and b.ACCSET_ID = ?" + "\r\n"
+ " and b.DATASETVER_ID = ?" + "\r\n"
+ " ) " + "\r\n"
+ " as d on " + "\r\n"
+ " ( " + "\r\n"
+ " a.ACCSET_ID = d. ACCSET_ID" + "\r\n"
+ " and a.DATASETVER_ID = d.DATASETVER_ID" + "\r\n"
+ " and a.BUDITEM_ID = d.BUDITEM_ID" + "\r\n"
+ " ) " + "\r\n"
+ "where" + "\r\n"
+ " a.ACCSET_ID = ?" + "\r\n"
+ " and a.DATASETVER_ID = ?" + "\r\n"
+ "order by" + "\r\n"
+ " a.BUDITEM_ID" + "\r\n"
例三:
String sql = "insert into" + "\r\n"
+ " tab1(" + "\r\n"
+ " a" + "\r\n"
+ " , b" + "\r\n"
+ " , c" + "\r\n"
+ " , d" + "\r\n"
+ ")" + "\r\n"
+ "values(" + "\r\n"
+ " ?" + "\r\n"
+ " , ?" + "\r\n"
+ " , ?" + "\r\n"
+ " , ?" + "\r\n"
+ ")" + "\r\n"
例四:
String sql = "update" + "\r\n"
+ " tab1" + "\r\n"
+ "set" + "\r\n"
+ " a=?" + "\r\n"
+ " , b=?" + "\r\n"
+ " , c=?" + "\r\n"
+ "where" + "\r\n"
+ " a=?" + "\r\n"
+ " and b=?" + "\r\n"
例五:
String sql = "delete from" + "\r\n"
+ " tab1" + "\r\n"
+ "where" + "\r\n"
+ " a=?" + "\r\n"
+ " and b=?" + "\r\n"
posted on 2007-01-05 19:44
★yesjoy★ 阅读(766)
评论(0) 编辑 收藏