发现一种超简单的将SQL包装成PrepraredStatement的方法

冒个泡，jSQLBox项目已启动，欢迎有兴趣的加入:https://github.com/drinkjava2/jSQLBox
jSQLBox项目要完成的基本功能有: CRUD方法、 JDBC包装、一级缓存、脏检查、纯JAVA方式配置，配置可动态生成修改。

在jSQLBox编写过程中，刚完成对JDBC的包装，突然发现一种超简单的将SQL包装成PrepraredStatement的方法，利用ThreadLocal将参数暂存，从而将字符串连接的SQL自动包装为PreparedStatement从而实现防SQL注入，提高性能，且不损害SQL的可读性。 这个比较好， 如果软件怎么写也可以申请专利的话，而且以前也没人干过，我得去申请专利了:

public class Tester {
	public void tx_insertDemo() {
		Dao.dao.execute("delete from user");
		Dao.dao.execute("insert user (username,age) values(" + W("user1") + "," + W(10) + ")");
		Dao.dao.execute("insert user (username,age) values(" + W("user2", 20) + ")");
		Dao.dao.execute("insert user (username,age) values(?,?)" + K("user3") + K(30));
		Dao.dao.execute("insert user (username,age) values(?,?)" + K("user4", 40));
		Dao.dao.execute(
				"insert " + User.Table + " (" + User.UserName + "," + User.Age + ") values(" + W("user5", 50) + ")");
		Dao.dao.execute("update user set username=?,address=? " + K("Sam", "BeiJing") + " where age=" + W(50));
		User user = new User();
		user.setUsername("user3");
		user.setAge(40);
		user.dao().save(); //TODO
	}

	public void tx_batchInsertDemo() {
		for (int i = 6; i < 100000; i++)
			Dao.dao.cacheSQL("insert user (username,age) values(?,?)" + K("user" + i, 60));
		Dao.dao.executeCatchedSQLs();
	}

	public static void main(String[] args) {
		Tester tester = BeanBox.getBean(Tester.class);//获取代理实例
		tester.tx_insertDemo();//包装在Spring的声明式事务中
		tester.tx_batchInsertDemo();
	}
}

以上代码实测通过,已上传。 简单说明一下上面代码，User表中只有id, username, address, age四个字段，上例分别用几种不同的SQL写法进行插入和更新，batchInsertDemo方法是包装了JDBC的批量插入，插入10万行数据大约需时5秒。上面代码中W表示将参数暂存在ThreadLocal中并返回问号，K表示返回为空,都是静态引入的方法。如果你还在苦恼于使用JDBC的繁琐，可以借签一下上面的做法。 目前常见的做法是将参数全部放在方法的最后一个参数传入，可读性极差，把set和where的参数写在一起传进去，参数多时很难维护。 上面的做法避免了这一缺点，可以非常灵活地将参数织入到SQL中。
2017.5.17更新一个小问题：为了防止Threadlocad变量互相干拢（有点类似于内存泄漏），可用一个小技巧来解决：在方法的第一个参数注入时用一个特殊的方法，名称任意，如K0, W0,Empty0之类，在压参数入Threadlocad之前先清空Threadlocad缓存内容，这样用起来就比较放心了。

在以上基础上，再支持逗号和分行支持，不怕Eclipse自动排版了，字段名和赋值一一对应，方便维护。

		Dao.dao.execute("update user set username=?,address=? ", K("John", "Shanghai"), " where age=", W(30));
		Dao.dao.execute("update user set", //
				" username=?", K("Peter"), //
				",address=? ", K("Nanjing"), //
				" where age=?", K(40));
		Dao.dao.execute("update user set", //
				" username=", W("Jeffery"), //
				",address=", W("Tianjing"), //
				" where age=", W(50));
		Dao.dao.execute("insert user ", //
				" (username", K("Andy"), //
				", address", K("Guanzhou"), //
				", age)", K("60"), //
				" values(?,?,?)");

没看懂，这样如何防止SQL注入。
PrepraredStatement本身就是可以防止sql注入

实现的源码在https://github.com/drinkjava2/jSQLBox/tree/master/jsqlbox/src/main/java/com/github/drinkjava2/jsqlbox下，看一下Dao.java和SQLHeelper两个类的源码就知道了。目前比较简单，只支持可自动转化为字符串的类型，其它类型如Blob等要添加方法来实现，但基本思路在这里了。

对比用DbUtils的QueryRunner, 写法是

qry.update(conn, "update user set username = ?, age = ?, address = ? where id = ?", "Bill", 23, "Tianjing", 5); 

和JdbcTemplate，写法是

jdbc.update("update user set username = ?, age = ?, address = ? where id = ?", "Bill", 23, "Tianjing", 5);

jSQLBox写法是

Dao.dao.execute("update user set", //  
        " username=", W("Bill"), //  
        ",age=", W("23"), //  
        ",address=", W("Tianjing"), //  
        " where id=", W(5));  

其优点在于：
1)要被赋值的字段和实际参数写在同一行上，便于维护。如果字段很多的话(>10)，就能看出好处了，直接删除添加一行就好了，不用担心删除添加错位置，问号和参数不配对。
2)没有重新发明SQL语法，不降低SQL的可读性，实参的代入甚至提高了代码的可读性。
3)可以在SQL中任何地方插入方法，只要返回为空或问号，这就有可能加入一些其它功能，如在select语句中加入字段显示别名等额外内容。顺便说一下，insert语句插入的字段多时，values(...)中的问号可以用一个方法来自动生成。

再摘一段JDBC多条件查询的单元测试实例（源码可在jSQLBox项目下找到)，大家知道在条件不确定的情况下，执行动态拼接生成的SQL，即要保证SQL安全性，防止SQL注入，又要保证编码的简洁性，这一直是一个头痛的问题。 jSQLBox利用ThreadLocal解决了这个问题:

public class ConditionQueryTest {
	@Before
	public void setup() {
		TestPrepare.dropAndRecreateTables();
		User u = new User(); // use default SqlBoxContext
		u.setUserName("User1");
		u.setAddress("Address1");
		u.setAge(10);
		u.insert();
	}

	@After
	public void cleanUp() {
		TestPrepare.closeDefaultContexts();
	}

	public int conditionQuery(int condition, Object parameter) {
		User u = SqlBox.createBean(User.class);
		String sql = "Select count(*) from " + u.table() + " where ";
		if (condition == 1 || condition == 3)
			sql = sql + u.userName() + "=" + q(parameter) + " and " + u.address() + "=" + q("Address1");

		if (condition == 2)
			sql = sql + u.userName() + "=" + q(parameter);

		if (condition == 3)
			sql = sql + " or " + u.age() + "=" + q(parameter);

		return SqlBox.queryForInteger(sql);
	}

	@Test
	public void doJdbcConditionQuery() {
		Assert.assertEquals(1, conditionQuery(1, "User1"));
		Assert.assertEquals(0, conditionQuery(2, "User does not exist"));
		Assert.assertEquals(1, conditionQuery(3, 10));
		Assert.assertEquals(0, conditionQuery(3, 20));
	}
}

再顺便说一下u.userName()也可以直接写成"username"字串，写成u.userName()是jSQLBox项目鼓励的一种方式，当User类字段名重构或字段名不变但是数据库列名更改时（配置文件变化)，所有JDBC SQL语句自动适应更改，不需要一个个手工检查和改正SQL，保证了SQL语句的建壮性。另外u.UserName()这种写法在查询中还会利用到，因为项目还在开发中，就不多说了，总之个人认为这是一种比较好的编程风格，在不改变SQL语法的前提下实现了SQL支持重构。

再上一个复杂一点的例子，也算是对jSqlBox功能的一个简单介绍吧：

public class ORMDemo {

	@Before
	public void setup() {
		PrepareTestContext.prepareDatasource_setDefaultSqlBoxConetxt_recreateTables();
		Dao.execute("insert into users values('u1','user1')");
		Dao.execute("insert into users values('u2','user2')");
		Dao.execute("insert into users values('u3','user3')");
		Dao.execute("insert into users values('u4','user4')");
		Dao.execute("insert into users values('u5','user5')");

		Dao.execute("insert into address values('a1','address1','u1')");
		Dao.execute("insert into address values('a2','address2','u2')");
		Dao.execute("insert into address values('a3','address3','u3')");
		Dao.execute("insert into address values('a4','address4','u4')");
		Dao.execute("insert into address values('a5','address5','u5')");

		Dao.execute("insert into email values('e1','email1','u1')");
		Dao.execute("insert into email values('e2','email2','u1')");
		Dao.execute("insert into email values('e3','email3','u2')");
		Dao.execute("insert into email values('e4','email4','u2')");
		Dao.execute("insert into email values('e5','email5','u3')");

		Dao.execute("insert into roles values('r1','role1')");
		Dao.execute("insert into roles values('r2','role2')");
		Dao.execute("insert into roles values('r3','role3')");
		Dao.execute("insert into roles values('r4','role4')");
		Dao.execute("insert into roles values('r5','role5')");

		Dao.execute("insert into privilegetb values('p1','privilege1')");
		Dao.execute("insert into privilegetb values('p2','privilege2')");
		Dao.execute("insert into privilegetb values('p3','privilege3')");
		Dao.execute("insert into privilegetb values('p4','privilege4')");
		Dao.execute("insert into privilegetb values('p5','privilege5')");

		Dao.execute("insert into userroletb values('u1','r1')");
		Dao.execute("insert into userroletb values('u2','r1')");
		Dao.execute("insert into userroletb values('u2','r2')");
		Dao.execute("insert into userroletb values('u2','r3')");
		Dao.execute("insert into userroletb values('u3','r4')");
		Dao.execute("insert into userroletb values('u4','r1')");

		Dao.execute("insert into roleprivilege values('r1','p1')");
		Dao.execute("insert into roleprivilege values('r2','p1')");
		Dao.execute("insert into roleprivilege values('r2','p2')");
		Dao.execute("insert into roleprivilege values('r2','p3')");
		Dao.execute("insert into roleprivilege values('r3','p3')");
		Dao.execute("insert into roleprivilege values('r4','p1')");
		Dao.refreshMetaData(); 
	}

	/**
	 * 1 user has 1 address, 1 address has 1 user
	 */
	@Test
	public void oneToOneNoBind() {
		System.out.println("============oneToOneNoBind=========");
		User u = new User();
		Address a = new Address();
		List<User> users = Dao.queryForEntityList(User.class, select(), u.all(), ",", a.all(), from(), u.table(), ",",
				a.table(), " where ", oneToOne(), u.ID(), "=", a.UID(), bind());
		for (User user : users) {
			System.out.println(user.getUserName());
			Address address = user.getChildNode(Address.class);
			System.out.println("\t" + address.getAddressName());
			User user2 = address.getParentNode(User.class);
			Assert.assertTrue(user == user2);
		}
	}
  
	/**
	 * 1 user has many many Emails
	 */
	@Test
	public void oneToManyWithBind() {
		System.out.println("============oneToManyWithBind=========");
		User u = new User();
		Email e = new Email();
		List<User> users = Dao.queryForEntityList(User.class, select(), u.all(), ",", e.all(), from(), u.table(), ",",
				e.table(), " where ", oneToMany(), u.ID(), "=", e.UID(), bind(u.EMAILS(), e.USER()));
		for (User user : users) {
			System.out.println(user.getUserName());
			Set<Email> emails = user.getEmails();
			for (Email email : emails) {
				System.out.println("\t" + email.getEmailName());
				Assert.assertTrue(email.getUser() == user);
			}
		}
	}

	/**
	 * Use 2 oneToMany() to simulate 1 manyToMany <br/> 
	 */
	@Test
	public void manyToManyTest() {
		System.out.println("============manyToManyTest=========");
		User u = new User();
		Role r = new Role();
		Privilege p = new Privilege();
		UserRole ur = new UserRole();
		RolePrivilege rp = new RolePrivilege();
		Dao.getDefaultContext().setShowSql(true);
		List<User> users = Dao.queryForEntityList(User.class, select(), Dao.pagination(1, 10), u.all(), ",", ur.all(),
				",", r.all(), ",", rp.all(), ",", p.all(), from(), u.table(), //
				" left join ", ur.table(), " on ", oneToMany(), u.ID(), "=", ur.UID(), bind(), //
				" left join ", r.table(), " on ", oneToMany(), r.ID(), "=", ur.RID(), bind(), //
				" left join ", rp.table(), " on ", oneToMany(), r.ID(), "=", rp.RID(), bind(), //
				" left join ", p.table(), " on ", oneToMany(), p.ID(), "=", rp.PID(), bind(), //
				Dao.orderBy(u.ID(), ",", r.ID(), ",", p.ID()));
		for (User user : users) {
			System.out.println(user.getUserName());
			Set<Role> roles = user.getUniqueNodeList(Role.class);
			for (Role role : roles)
				System.out.println("\t" + role.getRoleName());
			Set<Privilege> privs = user.getUniqueNodeList(Privilege.class);
			for (Privilege priv : privs)
				System.out.println("\t" + priv.getPrivilegeName());
		}
	}

}

以上代码的特点介绍：
1)跨数据库，这段代码在H2、MySQL、Oracle、MSSQLServer上实测通过,一些实体类如User、Address等源码未列出，详见https://github.com/drinkjava2/jSQLBox/tree/master/jsqlbox/src/test/java/test/examples/orm目录。
2)没有重新发明新的SQL语言，对SQL进行了装饰，但是最后创建的还是原生SQL。好比JSP和HTML的关系，JSP在运行期最终还是被翻译成HTML。
3)SQL支持重构，这个前面已经介绍过了，数据库字段或实体类字段更名，可直接利用IDE的重构功能来实现,all()方法表示返回所有列名。
4)以上代码实现了ORM，目前只支持对象树的构建，不支持对象树写回到数据库的更新。目前有一对一，一对多两种关联映射，多对多关联可以用两个一对多来模拟实现。实现关联映射的目是是将SQL查询获得的平面的、有冗余信息的一行行数据装配成互相关联的对象树结构，以方便程序做进一步处理。这个例子中的对象关系图如下:

其中User与Address是一对一关系，User与Email是一对多关系，User与Role是多对多关系，Role与Privilege是多对多关系，UserRole和RolePrivilege是两个中间表，用来连接多对多关系。

5)没有用到XML或Annotation注解，ORM配置直接写在SQL里，并暂存在ThreadLocal中。
6)jSQLBox支持两种方式来访问父对象或子对象,一种方式是(与Hibernate类似)在实体类中定义相应的属性，例如在User类中定义一个固定的Emails集合属性，用user.getEmails()来访问,配置通过在SQL中用bind()方法来绑定字段。另一种方式是在User类中不添加属性，直接用user.getChildNodeList(Email.class)来动态获取与此user关联的所有Emails集合。后一种方式灵活性好但速度不如第一种方式，因为每次都要在内存中的对象树中遍历一遍。
7)两个方法Dao.pagination()和Dao.orderBy()结合起来用，实现了通用的跨数据库的分页。
8)getUniqueNodeList(target.class)方法是一个通用的获取与当前实体关联的所有子对象或父对象列表的方法，可以在内存对象图中跨级别搜索到所有关联的目标对象。有了这个方法，可以轻易地实现对象图中无限层级关系的一对多、多对多关系查找，但使用这个方法的限制是路径和目标类必须在整个对象图中是唯一的，否则必须手工给出查找路径。
本示例的输出结果如下：

============oneToOneNoBind=========
user1
	address1
user2
	address2
user3
	address3
user4
	address4
user5
	address5
 
============oneToManyWithBind=========
user1
	email1
	email2
user2
	email3
	email4
user3
	email5
============manyToManyTest=========
user1
	role1
	privilege1
user2
	role1
	role2
	role3
	privilege1
	privilege2
	privilege3
user3
	role4
	privilege1
user4
	role1
	privilege1
user5

从BeetlSql项目将SQL全放在Beetl模板里得到启发，又想到一个比较偏门的用法。以下代码实测通过，详见jSqlBox的test\examples\multipleLineSQL\SqlTemplateDemo.java源程序，此示例特殊点在于：发布时必须将此源程序拷贝一份在项目的类根目录，(或Maven管理的Resource目录下),并将后缀".java"改为".sql".

public class SqlTemplateDemo extends TestBase {  
  
    //@formatter:off      
    public static class InsertUser extends SqlTemplate {  
        public InsertUser(Object name, Object address, Object age){   
        /* insert into  
           users  
           (username, address, age) */ empty(name,address,age);  
        /* values  (?,?,?)*/   
        }  
    }  
      
    public static class FindUsers extends SqlTemplate  {  
        public FindUsers(Object name, Object age){   
        /* select count(*)  
           from 
           users 
          where */  
        /* username=? */empty(name);   
        /* and age>? */empty(age);  
        /* order by username */    
        }  
    }  
  
    public static class GetUserCount extends SqlTemplate  {      
        /* select count(*)  
           from users  */      
    }     
      
    public static class SqlTemplateEndTag{}  
      
    @Test  
    public void doTest() {   
        Dao.getDefaultContext().setShowSql(true);  
        Dao.executeInsert(new InsertUser("Tom","BeiJing",10).toString());
        Dao.executeInsert(new InsertUser("Sam","ShangHai",20).toString());
        Assert.assertEquals((Integer) 1,  Dao.queryForInteger(new FindUsers("Sam",15).toString()));
        Assert.assertEquals((Integer) 2,  Dao.queryForInteger(new GetUserCount().toString()));
    }     
} 

以上代码是利用Java源程序作为模板文件来统一放置SQL，以便于DBA管理。其优点在于：
1)没有引入第三方模板，直接用Java作为模板文件
2)支持多行字符串, 且没有引入IDE插件或Maven编译插件等,通用性好。
3)支持Java方法和模板混用，模板负责存储SQL,Java负责赋值(利用ThreadLocal)，互不干拢。
4)public static class的类名即为SQL ID,便于重命名重构和SQL的快速定位（Ctrl+鼠标左键即可），这是文本方式模板无法做到的，后者必须用文本搜索功能才能定位SQL。
缺点是：
1)每次模板更改后，还必须手工拷贝一份同样的文件放在类根目录作为资源文件
2)因为SQL写在注释中， 所以必须利用标记 //@formatter:off 来关闭IDE(我用Eclipse)的格式化功能，防止IDE误格式化。
3)从安全角度出发，模板文件中要注意不要放入除SQL以外的内容。

这样还不如直接上代码生成器哦。

代码生成器仅用于成熟且变动不大的架构，我现在的目标恰恰是改进底层架构，所以暂时不适用代码生成器。
另外关于Java注释模板方式再说一下，拷贝到类根目录是必须的，但是在开发阶段也可以改进一下SqlTemplate，把java源码目录放进去，则可以直接读取Java源码，不必每次改动Java后再拷贝一遍到类根目录。仅仅在发布时再拷贝一遍，改成从资源文件读。我在另一个小工具项目里已经这么做了，感觉很方便。
另外一个常见的需求是仅当变量为非空值时才插入SQL片段并赋值, 初步构想是可以通过写以下Java方法来实现：ifHasValue(name, "username=?");
具体原理是：当ifHasValue方法运行时先在Threadlocal中存入一个对应"username=?"键的name值,在toString()方法中检查对应"username=?"键是否有值，如果有值的话才将username=?字串插入SQL。键必须是唯一的，如果有多个相同键字符串可以通过添加空格来区分。
关于多行字符串，详见https://my.oschina.net/drinkjava2/blog/892309一文，有一个通用的TextSupport类用于处理多行字符串，做到开发阶段从源码读取，布署阶段从资源文件读取(布署时改一下基类的getJavaSrcFolder()方法使其返回空值并拷贝Java模板到类根目类，并改为.txt后缀)