cym1102-sqlHelper像 MongoDB 一样使用 SQL 数据库-技术圈

本项目是基于 spring-data-jdbc 的 orm，支持 sqlite、mysql、postgresql 三种数据库，主要特点是像 mongodb 一样使用 sql 数据库。

sqlHelper 为 mongoHelper 的兄弟项目，旨在为关系型数据库提供近似 mongodb 的使用体验。即开发过程中完全不用关心数据库结构，在任意一个空白或是有结构的数据库中，在项目启动的瞬间都可以立刻构建出与 pojo 类对应的数据库结构，可以立即开始进行业务开发。除了查询 sql 语句的执行效果，已经完全不必打开数据库客户端对数据库结构进行管理了。

软件架构

本项目只适用于 springBoot 项目，项目也依赖 springBoot 相关库，springMVC 项目无法使用。另外项目依赖了 hutool 提供的诸多 Util 工具，让代码更简洁。

演示应用项目：https://gitee.com/cym1102/nginxWebUI

使用说明

1. 基本操作

本orm会在容器中注入一个对象SqlHelper,这个对象拥有诸多单表查询功能,如下

按id删除：deleteById(String, Class<?>)
按条件删除：deleteByQuery(CriteriaAndWrapper, Class<?>)
查询所有：findAll(Class)
查询数量：findCount(Class<?>)
根据id查询：findById(String, Class)
根据条件查询：findListByQuery(CriteriaAndWrapper, Class<?>)
根据条件查询并分页：findPage(CriteriaAndWrapper, Page, Class<?>)
插入：insert(Object)
插入或更新：insertOrUpdate(Object)
根据id更新：updateById(Object)
根据id更新全部字段：updateAllColumnById(Object)
累加某一个字段的数量, 原子操作：addCountById(String id, String property, Long count, Class<?> clazz)

这个SqlHelper能够完成所有查询任务,插入和更新操作能够自动判断pojo的类型操作对应表,查询操作根据传入的Class进行对应表操作,本orm所有数据库操作都基于SqlHelper的功能,不用像mybatis一样,每个表都要建立一套Mapper,xml,Service,model,大大减少数据层的代码量。可以将SqlHelper直接注入到controller层,简单的操作直接调用SqlHelper进行操作,不需要调用service层。

而复杂的操作及事务处理需要service层,将SqlHelper注入service,并使用service层的@Transactional注解就能使用springBoot管理的事务功能。

2. 复杂查询功能

本orm的查询功能都在SqlHelper的findByQuery,findPage方法中.使用CriteriaAndWrapper和CriteriaOrWrapper对象作为sql的拼接对象

// 根据输入条件进行查询
public List<User> search(String word, Integer type) {
	CriteriaAndWrapper criteriaAndWrapper = new CriteriaAndWrapper();

	if (StrUtil.isNotEmpty(word)) {
		criteriaAndWrapper.and(new CriteriaOrWrapper().like("name", word).like("phone", word));
	}
	if (type != null) {
		criteriaAndWrapper.eq("type", type);
	}
		
	List<User> userList = SqlHelper.findListByQuery(criteriaAndWrapper, User.class);

	return userList ;
}

以上代码组装了类似于select * from user where (name like '%xxx%' or phone like '%xxx%') and type = xxx的查询语句。

本项目不支持使用left join rigth join等连接查询,关系型数据库的连表查询能解决很多问题,但在大公司中已不再推荐使用,因为很难做数据库优化,数据量庞大时查询时间很慢而且很难进行优化。需要连表查询时,先查出对方id集,再使用in进行包含查询,可以很方便的走索引,而且分库的时候很容易修改。这样使用的话,实际是将关系型数据库用成了近似文档型数据库,表之间不再产生关联。

基于以上理念,本orm还提供了一些小功能用于完善这种多次连接查询,在mongoHelper中有以下方法

只查出表的id作为List返回：findIdsByQuery(CriteriaAndWrapper criteriaAndWrapper, Class<?> clazz)
只查出表的某个字段作为List返回：findPropertiesByQuery(CriteriaAndWrapper criteriaAndWrapper, Class<?> documentClass, String property, Class propertyClass)

用法示例：

// 查出订单下的所有商品（OrderProduct.class为订单商品对照表）
public List<Product> getProductList(String orderId) {
	List<String> productIds = mongoHelper.findPropertiesByQuery(new CriteriaAndWrapper().eq("orderId", orderId), OrderProduct.class,  "productId", String.class);
	return mongoHelper.findListByQuery(new CriteriaAndWrapper().in("id", productIds), Product.class);
}


// 根据产品名查出所有订单
public Page search(Page page, String keywords) {
	CriteriaOrWrapper criteriaOrWrapper = new CriteriaOrWrapper();
		
	if (StrUtil.isNotEmpty(keywords)) {
			
	    List<String> productIds = mongoHelper.findIdsByQuery(new CriteriaAndWrapper().like("name", keywords), Product.class);
	    List<String> orderIds = mongoHelper.findPropertiesByQuery(new CriteriaAndWrapper().in("productId", productIds), OrderProduct.class,  "orderId", String.class);
	
	    criteriaOrWrapper.in("id", orderIds);
	}

	page = mongoHelper.findPage(criteriaOrWrapper, page, Order.class);
	return page;
}

3. 分页查询,

本orm提供一个Page类,包含count总记录数,limit每页记录数,curr起始页（从1开始）,records结果列表四个属性,只要将包含curr和limit数据的Page对象传入findPage,即可查询出records,count的数据并自动返回到Page对象中。这里三个属性参考了layui的分页参数,可直接无缝对接layui的分页控件。

public Page search(Page page, String word, Integer type) {
    CriteriaAndWrapper criteriaAndWrapper = new CriteriaAndWrapper();

	if (StrUtil.isNotEmpty(word)) {
		criteriaAndWrapper.and(new CriteriaOrWrapper().like("name", word).like("phone", word));
	}
	if (type != null) {
		criteriaAndWrapper.eq("type", type);
	}
	Sort sort = Sort.by(Direction.DESC, "creatTime");	
	page = SqlHelper.findPage(criteriaAndWrapper, sort, page, User.class);

	return page;
}