public void testMapAndObjectHolder() {
// 清晰的sql语句,/~ ~/为一个语法块
String sql =
"select * from user where 1=1 "
+ "/~ and title = {title} ~/"
+ "/~ and sex = {sex} ~/"
+ "/~ and salary = {salary} ~/"
+ "/~ and age = [age] ~/"
+ "/~ and mapKey = [mapKey] ~/";
// filters为参数
BlogInfo info = new BlogInfo();
info.setTitle("java");
Map hashMap = new HashMap();
hashMap.put("mapKey", "2009_mapKey");
XsqlFilterResult result = new XsqlBuilder().generateHql(sql, hashMap, info);
assertEquals(
"select * from user where 1=1 and title = :title and sex = :sex and age = 0 and mapKey = 2009_mapKey ",
result.getXsql());
assertTrue(result.getAcceptedFilters().containsKey("sex"));
assertTrue(result.getAcceptedFilters().containsKey("title"));
assertFalse(result.getAcceptedFilters().containsKey("age"));
}
private Page search1(HttpServletRequest request,int pageNo,int pageSize) throws Exception
{
Map searchMap=getParameterMap(request);
String sql="select * from Hr_family where 1=1 /~ and id={id} ~/ ";
XsqlFilterResult xsql = new XsqlBuilder().generateSql(sql,searchMap);
Page page=dataBaseControl.getPageResultSet(xsql.getXsql(), xsql.getAcceptedFilters().values().toArray(),pageNo,pageSize);
return page;
}
public void testDataModifierWithEmptyValue() {
Map filters = new HashMap();
String sql = "select * from user " + "/~age={age?long}~/";
filters.put("age", "");
XsqlFilterResult result = builder.applyFilters(sql, filters);
assertEquals("select * from user ", result.getXsql());
}
private Page detailsearch(Map searchMap,int pageNo,int pageSize) throws Exception
{
String sql="select decode(o.ispay, '1', '支付中', '2', '已付', '未付') fk, t.ration_apply_id,o.num,o.note,o.price,o.money, b.name brname,m.name,o.type,o.odate,(select hb.name from hr_base_info hb where hb.id = o.operson)pname,s.name gongys from gm_purchase t, gm_purchase_item r, materiel m, ma_brand b,gm_materiel_inout o,supplier s where t.id = r.purchase_id and r.id = o.purchase_item_id and r.materiel_id = m.id and r.brand_id = b.id and t.spzt = '5' and o.type='入库' and s.id(+) = r.gongys "
+ "/~ and t.ration_apply_id like '%[ration_apply_id]%' ~/ "
+ "/~ and r.materiel_id={materiel_id} ~/ "
+ "/~ and o.ispay={ispay} ~/ "
+ "/~ and s.name like '%[gong]%' ~/ "
+ " order by t.ration_apply_id desc,o.id desc ";
XsqlFilterResult xsql = new XsqlBuilder().generateSql(sql,searchMap);
Page page=dataBaseControl.getPageResultSet(xsql.getXsql(), xsql.getAcceptedFilters().values().toArray(),pageNo,pageSize);
return page;
}
public void testApplyFiltersWithMultiKeys() {
String xsql = "select * from user /~order by {order} {orderDirection}~/";
Map filters = new HashMap();
XsqlFilterResult result = builder.applyFilters(xsql, filters);
assertEquals("select * from user ", result.getXsql());
filters.put("order", "username");
result = builder.applyFilters(xsql, filters);
assertEquals("select * from user ", result.getXsql());
filters.put("orderDirection", "DESC");
result = builder.applyFilters(xsql, filters);
assertEquals("select * from user order by {order} {orderDirection}", result.getXsql());
}
public void testApplyFiltersReplace() {
String xsql = "select * from user /~order by [order] [orderDirection]~/";
Map filters = new HashMap();
XsqlFilterResult result = builder.applyFilters(xsql, filters);
assertEquals("select * from user ", result.getXsql());
filters.put("order", "username");
result = builder.applyFilters(xsql, filters);
assertEquals("select * from user ", result.getXsql());
filters.put("orderDirection", "DESC");
result = builder.applyFilters(xsql, filters);
assertEquals("select * from user order by username DESC", result.getXsql());
}
public void total(HttpServletRequest request,HttpServletResponse response) throws Exception {
String sql="select sum(o.price) sump,sum(o.num) num,sum(o.money) sum,count(*) count from gm_purchase t, gm_purchase_item r, materiel m, ma_brand b,gm_materiel_inout o,supplier s where t.id = r.purchase_id and r.id = o.purchase_item_id and r.materiel_id = m.id and r.brand_id = b.id and t.spzt = '5' and o.type='入库' and s.id(+) = r.gongys "
+ "/~ and t.ration_apply_id like '%[ration_apply_id]%' ~/ "
+ "/~ and r.materiel_id={materiel_id} ~/ "
+ "/~ and o.ispay={ispay} ~/ "
+ "/~ and s.name like '%[gong]%' ~/ "
+ " order by t.ration_apply_id desc";
XsqlFilterResult xsql = new XsqlBuilder().generateSql(sql,getParameterMap(request));
Page page=dataBaseControl.getPageResultSet(xsql.getXsql(), xsql.getAcceptedFilters().values().toArray(),1,1);
Map map = (Map) ((ArrayList)page.getThisPageElements()).get(0);
String jsonStr = JSON.toJSONString(map);
setAjaxInfo(response,jsonStr);
}
private Page search1(HttpServletRequest request,int pageNo,int pageSize) throws Exception
{
Map searchMap=getParameterMap(request);
String sql="select t.*,m.branchname,b.name,(select p.name from pr_project p where p.id=t.pro_id)pname from fi_payfor t,mrbranch m,hr_base_info b where 1 = 1 and t.p_id = b.id and t.dept_id = m.id "
+ "/~ and t.money={money} ~/ "
+ "/~ and t.id={id} ~/ "
+ "/~ and t.p_id={p_id} ~/ "
+ "/~ and t.payforstate={payforstate} ~/ "
+ "/~ and t.odate>=to_date({begin_date},'yyyy-MM-dd') ~/ "
+ "/~ and t.odate<=to_date({end_date},'yyyy-MM-dd') ~/ "
+ " order by t.id desc ";
XsqlFilterResult xsql = new XsqlBuilder().generateSql(sql,searchMap);
Page page=dataBaseControl.getPageResultSet(xsql.getXsql(), xsql.getAcceptedFilters().values().toArray(),pageNo,pageSize);
return page;
}
public void testDemo() {
// 清晰的sql语句,/~ ~/为一个语法块
String sql =
"select * from user where 1=1 "
+ "/~ and username = {username} ~/"
+ "/~ and password = {password} ~/";
// filters为参数
Map filters = new HashMap();
filters.put("username", "badqiu");
filters.put("sex", "F");
XsqlFilterResult result = new XsqlBuilder().generateHql(sql, filters);
assertTrue(result.getAcceptedFilters().containsKey("username"));
assertFalse(result.getAcceptedFilters().containsKey("sex"));
assertEquals("select * from user where 1=1 and username = :username ", result.getXsql());
}
public void testDataModifier() {
Map filters = new HashMap();
String sql =
"select * from user "
+ "/~age={age?long}~/"
+ "/~birthDate={birthDate?timestamp}~/"
+ "/~num=[num?string]~/";
filters.put("age", "20");
filters.put("birthDate", "1990-10-10 10:10:10.111");
filters.put("num", new Long(10));
XsqlFilterResult result = builder.applyFilters(sql, filters);
Map acceptedFilters = result.getAcceptedFilters();
assertEquals(new Long(20), acceptedFilters.get("age"));
assertTrue(acceptedFilters.get("birthDate") instanceof Timestamp);
assertNull(acceptedFilters.get("num"));
assertEquals("select * from user age={age}birthDate={birthDate}num=10", result.getXsql());
}
private Page search(HttpServletRequest request,int pageNo,int pageSize) throws Exception
{
Map searchMap=getParameterMap(request);
String sql="select io.id ioid,t.ration_apply_id,(r.rksl - r.yfsl) kzf," +
"r.id,r.purchase_id,r.brand_id,r.materiel_id,r.sqsl,r.cksl,r.yaoqdhrq,r.price,r.gongys,r.customer_id,r.rksl,r.yfsl,r.fujmc,r.ds_mf_status,r.czy,r.czrq,r.note,r.ration_item_id,r.confirmsql,r.state,r.reason," +
"s.name gys,m.name maname,io.price ioprice,io.money iomoney," +
"io.num ionum,p.name pname,t.prj_id " +
"from gm_purchase t, gm_purchase_item r, gm_materiel_inout io,supplier s,materiel m,pr_project p " +
"where t.id = r.purchase_id and r.id = io.purchase_item_id and io.type = '入库' and io.ispay = '0' and s.id = r.gongys and m.id = r.materiel_id and p.id = t.prj_id "
+ "/~ and p.name like '%[proname]%' ~/ "
+ "/~ and m.name like '%[maname]%' ~/ "
+ "/~ and s.name like '%[gong]%' ~/ "
+ "/~ and t.ration_apply_id like '%[ration_apply_id]%' ~/ "
+ "/~ and r.materiel_id={materiel_id} ~/ ";
XsqlFilterResult xsql = new XsqlBuilder().generateSql(sql,searchMap);
Page page=dataBaseControl.getPageResultSet(xsql.getXsql(), xsql.getAcceptedFilters().values().toArray(),pageNo,pageSize);
return page;
}
public void testBean() {
// 清晰的sql语句,/~ ~/为一个语法块
String sql =
"select * from user where 1=1 "
+ "/~ and title = {title} ~/"
+ "/~ and sex = {sex} ~/"
+ "/~ and salary = {salary} ~/"
+ "/~ and age = [age] ~/";
// filters为参数
BlogInfo info = new BlogInfo();
info.setTitle("java");
XsqlFilterResult result = new XsqlBuilder().generateHql(sql, info);
assertEquals(
"select * from user where 1=1 and title = :title and sex = :sex and age = 0 ",
result.getXsql());
assertTrue(result.getAcceptedFilters().containsKey("sex"));
assertTrue(result.getAcceptedFilters().containsKey("title"));
assertFalse(result.getAcceptedFilters().containsKey("age"));
}
public void testSafeSqlFilter() {
Map filters = new HashMap();
String sql = "select * from user " + "/~where name='[name]'~/";
XsqlBuilder builder1 = new XsqlBuilder(true);
filters.put("name", "bad'qiu");
XsqlFilterResult result1 = builder1.applyFilters(sql, filters);
assertEquals("select * from user where name='bad'qiu'", result1.getXsql());
XsqlBuilder builder2 = new XsqlBuilder(true, new EscapeSingleQuotesSafeSqlProcesser());
filters.put("name", "bad'qiu");
XsqlFilterResult result2 = builder2.applyFilters(sql, filters);
assertEquals("select * from user where name='bad''qiu'", result2.getXsql());
XsqlBuilder builder3 =
new XsqlBuilder(true, new EscapeBackslashAndSingleQuotesSafeSqlProcesser());
filters.put("name", "bad'\\qiu");
XsqlFilterResult result3 = builder3.applyFilters(sql, filters);
assertEquals("select * from user where name='bad''\\\\qiu'", result3.getXsql());
}
public void testApplyFilters() {
Map filters = new HashMap();
String sql =
"select * from user where 1=1"
+ "/~ and username = '******'~/"
+ "/~ and pwd = '{password}'~/"
+ "/~ and age = '{age}'~/";
XsqlFilterResult result = builder.applyFilters(sql, filters);
assertEquals("select * from user where 1=1", result.getXsql());
assertEquals(0, result.getAcceptedFilters().size());
filters.put("username", "badqiu");
filters.put("age", "age");
result = builder.applyFilters(sql, filters);
assertEquals(
"select * from user where 1=1 and username = '******' and age = '{age}'",
result.getXsql());
assertEquals(2, result.getAcceptedFilters().size());
assertTrue(result.getAcceptedFilters().keySet().contains("username"));
assertTrue(result.getAcceptedFilters().keySet().contains("age"));
}