你还在用PreparedStatement吗？

    我先列举PreparedStatement的几大罪状吧。

 

1.难以调试 。这几乎是PreparedStatement最为人诟病之处了。在Debug的时候，你是无法读到完整的SQL的。

2.过于死板，难以扩展。

罪证：

有一个SQL模板

 

select * from table where name in (?)

 

 

 

我希望把一个有可能变动的array植入这个模板，例如["Tim","Mary","Joe"], 期望可以得到

 

select * from table where name in ('Tim','Mary','Joe')

 

 

虽然PreparedStatement有setArray(java.sql.Array)这样的方法，先别说java.sql.Array这个 几乎没几个人听说过的类是什么来着，但是令人崩溃的很多驱动（Driver）都没有去实现这个方法，其中包括MySql的官方驱动。遇着这种情况只能去拼 接SQL了。

 

 

3.有时候阅读起来非常费劲

罪证：

 

 

insert String sql="into table (ffews,fggws,fu756,grgr,jg,f498s,luy,qff,gre,T4y6,n_,ngn,erw) values(?,?,?,?,?,?,?,?,?,?,?,?,?)";

PreparedStatement ps=...;
ps.setString(1,"abc");
ps.setString(2,"hr");
ps.setString(3,"gege");
ps.setString(4,"nfgdn");
ps.setString(5,"nt");
ps.setString(6,"o78");
ps.setString(7,"kykt");
ps.setString(8,"h4f");
ps.setString(9,"kuyk");
ps.setString(10,"nhnd");
ps.setString(11,"ng");
ps.setString(12,"32r32");
ps.setString(13,"bfg");

 

 

 你知道值”h4f “是对应哪个column吗？你不会是在数吧？

 

 

 

     读到这里，很多人都庆幸自己早已经摒弃PreparedStatement，用上ORM的框架了。但是本文并不是探讨用ORM的替代方案。我相信现在还是 有很多的遗留系统在使用PreparedStatement或者直接拼接SQL，我就经手过不少这样的项目。因此我在这里想探讨的是保留SQL的前提下， 寻找比PrepareStatement更好的解决方案，而不用对系统造成太大的改动。

 

     我之前有写过使用DSL的方式来改善SQL的编写，请看这里 。

 

     现在它变成了一个开源的小工具，名字叫LikeSql （http://code.google.com/p/likesql/）。虽然它还没有正式的release版本（我还在努力），但是我想先把这个想法提出来，和大家讨论，希望我不是在重复发明*，或者是创造了另外一个Evil。

 

     LikeSql设计的主要目的就是让程序员更好地使用Java编写SQL，同时可以针对PreparedStatement的缺点做出改善。我把设计目的总结为以下的几点。

 

1. 容易调试

2. 容易编写和扩展

3. 容易理解

 

 

    说了那么多，终于可以上代码了，我们通过一些代码的例子来看几个LikeSql的特点吧。

(1)更简单的Insert语句

 

//测试Insert语句
public void testSimpleSql(){
		String eSql="INSERT INTO users (no,id,name,gender,height) VALUES(1,15666662656565,'johny',null,195.36)";
		String eTpl="INSERT INTO users (no,id,name,gender,height) VALUES(?,?,?,?,?)";
		
		LikeSql iSql=DML.insertInto("users")
		    .value("no", 1)
		    .value("id", new BigInteger("15666662656565"))
		    .value("name","johny")
		    .value("gender",null)
		    .value("height", 195.36);
		
		assertEquals(eSql,iSql.toString());
		assertEquals(eTpl,iSql.toTemplate());
	}

    虽然这里稍微修改了insert语句的语法结构，但是value和column的对应关系一目了然。

 

(2)模板匹配和静态工厂方法

 

//测试update语句
public void testSimpleSql(){
		String eSql="UPDATE user SET gender='M', name='Tom' WHERE id=123";
		String eTpl="UPDATE user SET gender=?, name=? WHERE id=?";
		
		LikeSql uSql=DML.update("user")
		                .set(QuestExp.qt("gender=?, name=?")
                   		     .set(1, 'M')
                		     .set(2, "Tom"))
		                .where(QuestExp.qt("id=?").set(1,123));
		
		assertEquals(eSql,uSql.toString());
		assertEquals(eTpl,uSql.toTemplate());
	}

 

public void testAtStyleExpression(){
		String eSql="UPDATE user SET gender='M', name='Tom' WHERE id=123";
		String eTpl="UPDATE user SET gender=?, name=? WHERE id=?";
		
		LikeSql uSql=DML.update("user")
		                .set(AtExp.at("[e-mail], [e-mail]")
                   		     .set("gender", 'M')
                		     .set("name", "Tom"))
		                .where(QuestExp.qt("id=?").set(1,123));
		
		assertEquals(eSql,uSql.toString());
		assertEquals(eTpl,uSql.toTemplate());
	}

    这里语法应该还是比较好懂的，QuestExp.qt(String pattern)方法参数是一个SQL的模板，类似PreparedStatement的语法，使用set方法来替换模板内?字符为所需要的值。AtExp则是另外一种模板，允许通过匹配以@开头的参数。

     LikeSql在设计的时候大量使用了静态工厂来创建实例，例如QuestExp.qt方法返回QuestExp实例，AtExp.at()也是一样。这样在使用JDK5以后的版本可以在语法上显得简洁点。

    以下是JDK5或以后版本的写法

import static com.googlecode.likesql.dml.DML;
import static com.googlecode.likesql.expression.QuestExp;

LikeSql uSql=update("user").set(qt("gender=?, name=?")
                   		     .set(1, 'M')
                		     .set(2, "Tom"))
		           .where(qt("id=?")
                                     .set(1,123));

 

    (3)自定义类型和转换器

 

//测试select语句
public void testLikeCriteria(){
		LikeSql sSql=DML.selectAll()
		    .from("user")
		    .where(QuestExp.qt("name like ?").set(1, LikeExp.like("T%d")));
		
		String eSql="SELECT * FROM user WHERE name like 'T%d'";
		String eTpl="SELECT * FROM user WHERE name like ?";
		
		assertEquals(eSql,sSql.toString());
		assertEquals(eTpl,sSql.toTemplate());
	}

 

public void testInExpression(){
		LikeSql sSql=DML.select("id,name")
		    .from("user")
		    .where(QuestExp.qt("id in (?)").set(1,ArrayExp.array(new int[]{1,3,6})));
		
		String eSql="SELECT id,name FROM user WHERE id in (1,3,6)";
		String eTpl="SELECT id,name FROM user WHERE id in (?,?,?)";
		
		assertEquals(eSql,sSql.toString());
		assertEquals(eTpl,sSql.toTemplate());
	}

     LikeSql允许你set更多的类型。

     LikeSql允许你往Sql注入自定义的类型，你只需要实现一个相应的转换器（com.googlecode.converter.Converter）来转换这个类型的实例为SQL字符串，而且只需很简单的配置就可以实现类型与转换器的匹配。

    以下是一个我写的String类型的转换器

public class StringConverter implements Converter {

    public String toTemplate(Object obj) {
		return String.valueOf(SqlConstant.CHAR_FOR_REPLACE);//return '?'
	}
	
	public String toSql(Object obj){
		return StringUtils.replaceFirst("'?'", SqlConstant.CHAR_FOR_REPLACE, StringUtils.filterSql(obj.toString()));//return a filtered sql string
	}
}

 

 

    其实要实现容易调试的目的，很简单，只需要规定包含SQL的某个类使用toString()方法返回完整的SQL便可。

 

    以下是LikeSql接口代码：

 

/**
 * <p>LikeSql represents a SQL expression containing a executable SQL 
 * string and its SQL template without sensitive data.</p>
 * A default conversion is to implement {@link Object#toString()} to return a SQL string expression.
 * 
 * @author Johny Huang
 * @see     java.lang.Object#toString()
 */
public interface LikeSql {
	
	/**
	 * Return the SQL template without sensitive data.
	 * @return SQL template without sensitive data.
	 */
	String toTemplate();
}

 

   （4）更友好，更简单的调试

   在Debug的时候，把断点设到继承LikeSql的变量，就可以很简单看到完整的SQL。而toTemplate()方法则是允许你返回不含敏感数据的SQL模板，例如"select * from table where name=?"，方便做Logging。

 

/**
 * <p>LikeSql represents a SQL expression containing a executable SQL 
 * string and its SQL template without sensitive data.</p>
 * A default conversion is to implement {@link Object#toString()} to return a SQL string expression.
 * 
 * @author Johny Huang
 * @see     java.lang.Object#toString()
 */
public interface LikeSql {
	
	/**
	 * Return the SQL template without sensitive data.
	 * @return SQL template without sensitive data.
	 */
	String toTemplate();
}

 

以下是eclipse在debug时候的截图：

很感谢你可以一直看到这里，不知道你有没有被LikeSql的一些特性吸引呢？或者是你看到了LikeSql的缺陷？任何质疑和建议都欢迎。