动态SQL拼接工具类

动态SQL拼接处理工具类,为了传动态参数sql语句拼接的灵活使用。具体源代码见:

https://github.com/ticktack/kungfu/blob/master/src/main/java/org/kungfu/sql/DynamicSQL.java


下面简单介绍下使用Demo:

public static void main(String[] args) throws IOException {
	DynamicSQL dsql = new DynamicSQL();
	dsql.append("SELECT s.dept_name, s.id, s.name, t.year, t.semester, t.course_id, t.grade  ");
	dsql.append("  FROM student s   ");
	dsql.append("  LEFT JOIN takes t ON s.id = t.id ");
	dsql.append("  where s.id = '1'      ");
	dsql.isNotEmpty(" and t.year = #    ", "2015");
	dsql.isNotEmpty(" and t.month = #    ", "");
	dsql.isEqual(" and t.semester = $  ", 2, 2);

	System.out.println(dsql.getSql());
	System.out.println(dsql.getParams());
	System.out.println(dsql);
	System.out.println("+++++++++++++++++++++++++++++++++++++++++++");
	dsql.clear();

	dsql.append("SELECT s.dept_name, s.id, s.name, t.year, t.semester, t.course_id, t.grade  ");
	dsql.append("  FROM student s   ");
	dsql.append("  LEFT JOIN takes t ON s.id = t.id ");
	dsql.append("  where s.id = '1'      ");
	dsql.isNotEmpty(" and t.year = #    ", "2015");
	dsql.isNotEmpty(" and t.month = $    ", "");
	dsql.isEqual(" and t.semester = $  ", new Integer(3), new Integer(3));
	dsql.isEqual(" and t.semester2 = $  ", 5, 5);

	System.out.println(dsql.getSql());
	System.out.println(dsql.getParams());
	System.out.println(dsql);
	System.out.println("+++++++++++++++++++++++++++++++++++++++++++");
	dsql.clear();


	String ids = "1, 2, 3, 4, 5";
	//dsql.append("delete from sdt_bdi_std_type ");
	//dsql.isNotEmpty("  where id in ($) ", ids);
	dsql.isNotEmpty(String.format("delete from %s where id in ($) ", "sdt_bdi_std_type"), ids);
	System.out.println(dsql.getSql());
	System.out.println(dsql.getParams());
	System.out.println(dsql);
}

运行的结果如下:

SELECT s.dept_name, s.id, s.name, t.year, t.semester, t.course_id, t.grade FROM student s LEFT JOIN takes t ON s.id = t.id where s.id = '1' and t.year = ? and t.semester = ? 

[2015, 2]

SELECT s.dept_name, s.id, s.name, t.year, t.semester, t.course_id, t.grade FROM student s LEFT JOIN takes t ON s.id = t.id where s.id = '1' and t.year = '2015' and t.semester =  2  

+++++++++++++++++++++++++++++++++++++++++++

SELECT s.dept_name, s.id, s.name, t.year, t.semester, t.course_id, t.grade FROM student s LEFT JOIN takes t ON s.id = t.id where s.id = '1' and t.year = ? and t.semester2 = ? 

[2015, 5]

SELECT s.dept_name, s.id, s.name, t.year, t.semester, t.course_id, t.grade FROM student s LEFT JOIN takes t ON s.id = t.id where s.id = '1' and t.year = '2015' and t.semester2 =  5  

+++++++++++++++++++++++++++++++++++++++++++

delete from sdt_bdi_std_type where id in (?) 

[1, 2, 3, 4, 5]

delete from sdt_bdi_std_type where id in ( 1, 2, 3, 4, 5 ) 


评论区

提提买卖

2016-06-18 18:31

有启发,对于列表页,最多10个参数,最少0个参数,动态拼接sql好忧伤

古刹飞鹰

2016-06-22 16:22

StringBuffer sb = new StringBuffer();
sb.append("FROM pur_plan");
model.removeNullValueAttrs();
String[] keys = model.getAttrNames();
Page page = null;
sb.append(" where 1=1");
if(keys.length>0){
for (String key : keys) {
sb.append(" and ").append(key);
if(model.get(key) instanceof String){
sb.append(" like ?");
String v = model.get(key);
v =v .replace("'","");
v = "%"+v+"%";
model.put(key,v);
}else{
sb.append(" = ?");
}
}
}
这是我现在用的拼接方法

happyboy

2016-06-24 09:19

将sql代码在java文件里拼接,在我看来是一种灾难,尤其是sql很长的时候,后期维护相当不方便。我现在采用的是类似ibatis那样的配置文件方式,唯一不足的是对于动态sql还不能实现预处理。

小飞象

2016-06-24 10:19

这一块波总会放大招的。O(∩_∩)O~

紫电清霜

2016-06-27 14:27

@小飞象 期待

yoqu

2016-07-14 09:28

我不太明白的你的sql拼接这样拼的好处是什么?我也开发了一个sql拼接工具(https://github.com/yoqu/SqlHandle/tree/master/SqlHandle),你看看给我提点意见?看看怎样做比较好?