总结一下Java中SQL的九种写法

因为在做持久层工具开发，现总结一下各种SQL的写法，也算是清晰一下自已的思路：

第一种写法
public void someMethod(){
try
   Connection conn=...;
   conn.setAutoCommit(false);
   PreparedStatement pst=...;
   ResultSet rs= executeSomeSql("some sql...");
   conn.commit();
} catch (SQLException ex) {
   doSomething();
} finally {
   close(rs);
   close(pst);
   close(conn)
}
最基础的JDBC用法，从connectin的获取，事务的开启、提交，rs、st、connection的关闭全部照顾到
优点：100%掌控所有细节。
缺点：过分繁琐，不推荐业务开发中使用
支持第一种写法的工具类：JDK自带。

第二种写法
public void someMethod(Connection conn) throws SQLException {
   queryRunner.executeSQL(conn, "some sql...",参数1, 参数2...); 
}
将connection作为参数传递， 业务方法不再负责connection的关闭。
优点：业务方法可以简化到一条语句，极大简化了编程。
缺点：必须另外有一个总的方法进行导常处理和关闭connection，connection作为业务方法的参数传入是对业务方法的入侵。
支持第二种写法的工具类：DbUtils。

第三种写法
QueryRunner queryRunner=new QueryRunner(dataSource);
public void someMethod() throws SQLException {
   queryRunner.executeSQL("some sql...",参数1, 参数2...); 
}
不再将connection作为参数传递, 工具类构造时将DataSource实例注入，SQL方法完成后Connection自动关闭。
优点：业务方法可以简化成一条语句，没有connection参数入侵业务方法。
缺点：如没有第三方服务支持，事务不太好控制，SQLException异常的抛出也是一种入侵，还是需要另一个方法来捕获。
代表作：DbUtils。

第四种写法
DbPro dbPro=new DbPro(dataSource);
public void someMethod(){
   dbPro.executeSQL("some sql...",参数1, 参数2...); 
}
业务方法没有Connection参数传入，也不会抛出SQLException异常，工具类将SqlException转化为运行时异常抛出。
优点：业务方法没有受到入侵(严格来说dbPro变量的存在也是对业务方法的一种极轻度的入侵，影响业务方法的可移植性)，无须关心异常，运行时异常通常不必处理，由事务服务捕获并回滚。
缺点：无明显缺点，但是要引入IOC/AOP工具如Spring等，以捕获切面的方式实现对异常处理、事务处理及Connection的关闭的支持。
代表作：Spring-JDBC、DbUtils-Pro

以上是从对connection关闭、异常处理和事务处理的角度来看的，从以下写法将开始研究对SQL本身写法的优化：

第五种写法
DbPro dbPro=new DbPro(dataSource);
public void someMethod(){
   dbPro.executeSQL("sql piece1",param0(参数1),"sql piece2", param(参数2),"sql piece3",param(参数3)...); 
}
将参数利用ThreadLocal暂存，从而可以将参数织入到SQL的任意位置，详见"一种将SQL包装成PrepraredStatement的方法"(http://www.iteye.com/topic/1145415)，
优点：1）如果字段名用Java方法或常量代替，则可以实现让普通的SQL支持重构。
    2）参数多的SQL，可以保证占位符与实际参数相邻，利于维护。例如下面这种写法，比起传统的JdbcTemplate将所有参数放在方法未尾传递的方式，在可维护性上要好很多：
  DbPro.execute("update user set", //   
        " username=?", param0("Bill"), //   
        ",age=?", param("23"), //   
        ",address=", question("Tianjing"), //   
        " where id=", question(5));   
缺点：重复的参数也必须重复注入; ThreadLocal变量有可能互相干拢，例如不能在一个SQL方法中嵌套另一个SQL调用;
代表作：DbUtils-Pro (或jSqlBox, 其内核基于DbUtils-Pro)

第六种写法
public void someMethod(){
   put0("name","张三");  
   user.setAddress("BeiJing");
   put("user",user);
   dbPro.executeSQL("update users set name=#{name}, address=#{user.address}"); 
或 dbPro.executeSQL(findSQL("someSqlID")); 
}
利用模板来统一存放SQL，模板的形式可以为XML、文本或Java字符串、Java Annoation、JVM语言如Groovy甚至Java注释(见https://my.oschina.net/drinkjava2/blog/892309))，另外使用模板也可用第5种方法介绍过的利用ThreadLocal赋值。
优点：便于统一管理SQL，尤其是一些长SQL
缺点：模板不支持重构，参数的赋值和实际的SQL有时存放在两个文件里，而IDE又不支持导航定位，维护不方便。占位符的存在和赋值要多打几个字。
代表作：MyBatis, BeetlSql, DbUtils-Pro, NamedParameterJdbcTemplate

以下写法开始，引入了ORM概念，从SQL角度来看，ORM也可看成一种写SQL的写法。ORM主要分两大部分：1）Java Bean与数据库表的映射  2)Bean之间关联关系与数据库表关联关系的映射
ORM框架林林总总:
从功能来区分，有些简单到只有Bean到数据表的映射如Memory；有些只支持单向的关联映射如MyBatis/BeetlSql/jSqlBox, 有些是全功能的支持复杂的对象-数据库双向关联关系映射如Hibernate，
从是否支持配置来区分，有些是零配置的(如Memory/jFinal-Dao/DbUtils)，映射关系靠命名约定来保证, 有些是固定配置的,一旦配置好就不能再变动(MyBatis/Hibernate/JPA/BeetlSQL)。有些支持运行期动态调整配置(如jSqlBox)。
从配置方式来区分，有写在XML中(MyBatis)，有写在注解中(Hibernate/JPA/BeetlSQL)，有写在Java方法里(jSqlBox)

第七种写法
public void someMethod(){ 
   Session session = getSessionFromSomeWhere();
   User user = new user();
   user.setName("张三");
   session.save(user);
}
优点：CRUD操作非常方便
缺点：对复杂SQL无能为力；session变量的存在对业务方法是一种轻度入侵，尤其当session的功能设计的很复杂，方法很多时，就变成了重度入侵，业务方法的移植将变得越来越不可能，也就是说业务方法被绑定在了特定工具上(Hibernate/JPA等)。
代表作：Hibernate, MyBatis, BeetlSql

第八种写法
public void someMethod(){ 
   User user = new user();
   user.setName("张三");
   user.save();
}
又被称为ActiveRecord模式，业务方法中没有session变量出现，
优点：CRUD操作非常方便, 业务方法更简炼
缺点: 对复杂SQL无能为力；Java8以下要实现这种写法，必须让实体类继承于一个基类，占用了唯一的单继承，这也是一种(大多数情况下无关紧要的)入侵。Java8情况要好一些，只需要声明实现接口即可。
代表作：jFinal-Dao, jSqlBox 以及所有基于ActiveRecord模式的持久层工具

第九类写法
严格来说，这不是一种写法，而是一大类五花八门的写法，它往往是专有的，仅适用于各自的框架，例如Hibernate的HQL语言，Hibernate以及Nutz的用Java方法来代替SQL语法，Spring Boots用方法名代替SQL，各种模板对SQL语法的扩充，jSqlBox的利用ThreadLocal暂存映射配置等，如果你看到什么新奇的SQL写法，都归到这一大类吧，第九类写法并不都优于前面8种写法，只是不太好归类而已，甚至个人认为有些是反模式，如HQL和用Java方法代替SQL语法等。
具体在开发中个人优先推荐使用第5、6、8种写法。

另外文中提到的DbUtils-Pro还处于开发阶段，它是一个继承于DbUtils的小项目，欢迎有兴趣者加入。

以下是DbUtils-Pro https://github.com/drinkjava2/dbutils-pro的一个单元测试，演示了传统模式、In-Line模式、模板模式三种方式在同一个方法中混用， 实测通过。 三种模式通过方法名区分，方法名不加前缀为传统模式，加"i"的为In-Line模式，加"t"的为模板模式。SQLException全部转为RuntimeException，无事务配置时工作在自动提交模式，与JDBCTemplate相似。 因为DbUtils-Pro依赖于DbUtils, DbPro是QueryRunner的子类， 所以这样一来，上文所述的前6种方式实际上DbUtils-Pro都支持。 

	public void executeTest() {
		DbPro dbPro = new DbPro((DataSource) BeanBox.getBean(DataSourceBox.class));
		dbPro.setAllowShowSQL(true);
		User user = new User();
		user.setName("Tom");
		user.setAddress("China");

		// ==========normal old style==========
		dbPro.execute("insert into users (name,address) values(?,?)", user.getName(), user.getAddress());
		dbPro.execute("update users set name=?, address=?", "Sam", "Canada");
		Assert.assertEquals(1L, dbPro.queryForObject("select count(*) from users where name=?", "Sam"));
		dbPro.execute("delete from users where name=? and address=?", "Sam", "Canada");

		// ==========In-line style==========
		dbPro.iExecute("insert into users (", //
				" name ,", param0(user.getName()), //
				" address ", param(user.getAddress()), //
				") ", valuesQuesions());

		param0("Sam","Canada");  
		dbPro.iExecute("update users set name=?,address=?");

		Assert.assertEquals(1L, dbPro.iQueryForObject("select count(*) from users where name=" + question0("Sam")));
		dbPro.iExecute("delete from users where name=", question0("Sam"), " and address=", question("Canada"));

		// ==========Template style==========
		put0("user", user);
		dbPro.tExecute("insert into users (name, address) values(#{user.name},#{user.address})");

		put0("name", "Sam");
		put("addr", "Canada");
		dbPro.tExecute("update users set name=#{name}, address=#{addr}");

		Assert.assertEquals(1L, dbPro.tQueryForObject(
				"select count(*) from users where ${col}=#{name}" + put0("name", "Sam") + replace("col", "name")));
		dbPro.tExecute("delete from users where name=#{nm} and address=#{addr}", put0("nm", "Sam"),
				put("addr", "Canada"));
	}

6666666