Skip to content

Latest commit

 

History

History
170 lines (145 loc) · 5.47 KB

README.md

File metadata and controls

170 lines (145 loc) · 5.47 KB
Raw

jfastQuery

一个用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

where 操作符

类型 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

配置文件fastquery.properties

#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://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);
	}

}

java运行demo参考

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));
    }
}

servlet 运行demo参考类

public class ServletTest extends HttpServlet{

    private static final long serialVersionUID = 1L;

    //http://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
            });
    }
}