JAVA编码规范中关于sql语句规范要求

来源：互联网发布：itunes软件下载安装编辑：程序博客网时间：2024/04/30 15:29

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"

例四:

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"