一个用java实现的快速查询组件,支持三种数据库
- mysql
- oralce
- sqlserver
解决在java jdbc 开发中拼接sql语句的情景，通常sql会和java代码混在一起可读性差，像古代妇女的裹脚布一样，又长又臭。
- 分页
- 排序
- 动态传参
- 动态组合where条件
- 支持freemarker表达式（在sql里写ftl指令）
让Java和sql语句分离
| 关键字 | 说明 |
|---|---|
| logic | and or 运算符 |
| operator | 对应下方表格 where 操作符 |
| table | sql表别名 |
| field | 字段名称或者括号( ) |
| value | 值 |
| value2 | 日期字段下的第2个值 |
| type | 字段类型：TEXT,NUMBER,DATE,STRING,SELECT,DATETIME,BOOLEAN |
| 类型 | jfastQuery | 英文 |
|---|---|---|
| 等于 | eq | equals |
| 不等于 | neq | not equals |
| 大于 | gt | greater than |
| 小于 | lt | less than |
| 大于等于 | gte | Greater than or equal to |
| 小于等于 | lte | Less than equal |
| 介于 | bw | between |
| 包含 | c | Contain |
| 左包含 | sw | start with |
| 右包含 | ew | end with |
| 集合 | ... | in |
| 空 | nl | is null |
| 非空 | nnl | is not null |
#jfastquery默认数据源实现类
app.default.datasource=com.kingschan.fastquery.test.DruidDatasourceConfigure
#对应pageindex关键字
app.keyword.page.index=offset
#对应pagesize关键字
app.keyword.page.size=limit
#对应where 条件组合json字符串关键字
app.keyword.filter=filter
# 对应排序字段
app.keyword.sort=sort
# 对应排序方式关键字
app.keyword.order=order
实现类：com.kingschan.fastquery.conf.Configure
public class DruidDatasourceConfigure implements Configure{
@Override
public DataSource getDataSource() throws Exception {
Map<String, String> properties = new HashMap<String, String>();
properties.put("url", "jdbc:mysql:https://ip:端口/数据库名");
properties.put("username", "用户名");
properties.put("password", "密码");
properties.put("filters", "stat");
properties.put("maxActive", "20");
properties.put("initialSize", "1");
properties.put("maxWait", "60000");
properties.put("minIdle", "1");
properties.put("timeBetweenEvictionRunsMillis", "60000");
properties.put("minEvictableIdleTimeMillis", "300000");
properties.put("testWhileIdle", "true");
properties.put("testOnBorrow", "false");
properties.put("testOnReturn", "false");
properties.put("poolPreparedStatements", "true");
properties.put("maxOpenPreparedStatements", "20");
return DruidDataSourceFactory.createDataSource(properties);
}
}
public class QueryDispacherTest {
private static Logger log = LoggerFactory.getLogger(QueryDispacherTest.class);
public static void main(String[] args) {
List<SqlCondition> conditions=new ArrayList<SqlCondition>();
conditions.add(new SqlCondition("and","a.req_datetime","DATE","bw","2017-01-01","2017-02-01"));
String filter=JSONArray.fromObject(conditions).toString();
//查询方案所有传入参数
Map<String,Object> map = new HashMap<String, Object>();
map.put("filter",filter);//查询条件
map.put("sort","req_datetime");//排序字段
map.put("order","desc");//排序的方式
map.put("limit","10");//显示条数
map.put("offset","1");//第几页
map.put("blog","kingschan");//传入变量blog
SqlCommand cmd = new SqlCommand();
cmd.setSql("select req_ip,count(1) total from blog_request_log a where a.req_blog='${blog}' group by req_ip");
cmd.setDBtype(DbType.MYSQL);
DataTransfer dt= new LogicHandleDispacher().handleDispacher(map,cmd,new Class[]{
BuildConditionLogicHandle.class,
BuildVariableLogicHandle.class,
BuildPagingLogicHandle.class,
ExecuteTotalLogicHandle.class,
ExecuteMapQueryLogicHandle.class
});
log.info("{}", JSONObject.fromObject(dt));
}
}
public class ServletTest extends HttpServlet{
private static final long serialVersionUID = 1L;
//https://localhost/jfastquery/test?offset=1&limit=30&sort=req_datetime&order=desc
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException,
IOException {
//查询方案所有传入参数
Map<String,Object> map =ServletUtil.getParameterMap(req);
List<SqlCondition> conditions= new ArrayList<SqlCondition>();
conditions.add( new SqlCondition("and","a.req_datetime","DATE","bw","2017-01-01","2017-02-01"));
conditions.add( new SqlCondition("and","a.req_blog","STRING","eq","kingschan",null));
JSONArray jons=JSONArray.fromObject(conditions);
map.put("filter",jons);//查询条件
map.put("sort","id");//排序字段
map.put("order","desc");//排序的方式
map.put("limit","10");//显示条数
map.put("offset","1");//第几页
WebLogicHandleDispacher dispa = new WebLogicHandleDispacher(req, resp);
SqlCommand cmd = new SqlCommand();
cmd.setSql("select * from blog_request_log a limit 10");
cmd.setDBtype(DbType.MYSQL);
dispa.handleDispacher(map,cmd, new StandardOutPut(),
new Class[]{
BuildConditionLogicHandle.class,
BuildVariableLogicHandle.class,
BuildPagingLogicHandle.class,
ExecuteTotalLogicHandle.class,
ExecuteMapQueryLogicHandle.class
});
}
}