soho-mybatis是封装mybatis底层动态sql的产物,语法风格类似mongodb,使用本框架95%情况下无需编写自定义查询代码和sql,而且可提升开发效率2-3倍,数据库操作数据bug率降低90%;下面提供文档示例:
附上项目地址: 点击下载
附上项目CRUD代码生成器平台地址: https://www.cartoonai.com/
示例1。获取指定单个或者字段数据,用户账号为zhangsan并且年龄大于18
Cnd sql = new SQLCnd().eq("username", "zhangsan").gt("age", 18);
// 获取多条记录
List<RbacUser> rbacUsers = rbacUserService.findByCnd(sql);
// 获取单条记录
RbacUser rbacUser = rbacUserService.findOneByCnd(sql);
Preparing: select id, username, password, uid, utype, areacode, mobile, sex, age, headimg, nickname, realname, idno, email, wxcat, alipay, password2, level, ctime, utime, state from rbac_user WHERE username = ? AND age > ?
Parameters: zhangsan(String), 18(Integer)
示例2。获取一个时间段内新增的数据,昨天与今天新增用户并且邮箱地址不能为空
Cnd sql = new SQLCnd().between("ctime", 1531843200000l, 1531929600000l).notnull("email");
List<RbacUser> rbacUsers = rbacUserService.findByCnd(sql);
Preparing: select id, username, password, uid, utype, areacode, mobile, sex, age, headimg, nickname, realname, idno, email, wxcat, alipay, password2, level, ctime, utime, state from rbac_user WHERE ( ctime BETWEEN ? AND ? ) AND email IS NOT NULL
Parameters: 1531843200000(Long), 1531929600000(Long)
示例3。示例1与示例2条件进行or条件组合,并且数据状态处于1,2,3
Cnd sql1 = new SQLCnd().eq("username", "zhangsan").gt("age", 18);
Cnd sql2 = new SQLCnd().between("ctime", 1531843200000l, 1531929600000l).notnull("email");
Cnd sql3 = new SQLCnd().or(sql1, sql2).in("state", 1, 2, 3);
List<RbacUser> rbacUsers = rbacUserService.findByCnd(sql3);
Preparing: select id, username, password, uid, utype, areacode, mobile, sex, age, headimg, nickname, realname, idno, email, wxcat, alipay, password2, level, ctime, utime, state from rbac_user WHERE ( ( username = ? AND age > ? ) OR ( ( ctime BETWEEN ? AND ? ) AND email IS NOT NULL ) ) AND state IN ( ? , ? , ? )
Parameters: zhangsan(String), 18(Integer), 1531843200000(Long), 1531929600000(Long), 1(Integer), 2(Integer), 3(Integer)
示例4。示例2分页查询
Cnd sql = new SQLCnd().between("ctime", 1531843200000l, 1531929600000l).notnull("email").limit(1,10);
List<RbacUser> rbacUsers = rbacUserService.findByCnd(sql);
Preparing: select count(1) from (select id, username, password, uid, utype, areacode, mobile, sex, age, headimg, nickname, realname, idno, email, wxcat, alipay, password2, level, ctime, utime, state from rbac_user WHERE ( ctime BETWEEN ? AND ? ) AND email IS NOT NULL) as x
Parameters: 1531843200000(Long), 1531929600000(Long)
Preparing: select id, username, password, uid, utype, areacode, mobile, sex, age, headimg, nickname, realname, idno, email, wxcat, alipay, password2, level, ctime, utime, state from rbac_user WHERE ( ctime BETWEEN ? AND ? ) AND email IS NOT NULL limit 0,10
Parameters: 1531843200000(Long), 1531929600000(Long)
示例5。连表查询,通过用户id获取拥有的角色数据
Cnd sql = new SQLCnd().fields("a.id as id", "a.name as name", "a.code as code").from("rbac_role a").join(new Join(MODE.LEFT, "rbac_user_role b").on("a.id", "b.roleId")).join(new Join(MODE.LEFT, "rbac_user c").on("b.userId", "c.id")).eq("c.id", 1l);
List<RbacRole> rbacRoles = rbacUserService.findMapByCnd(sql, RbacRole.class);
Preparing: select a.id , a.name , a.code from rbac_role a LEFT JOIN rbac_user_role b ON a.id = b.roleId LEFT JOIN rbac_user c ON b.userId = c.id WHERE c.id = ?
Parameters: 1(Long)
示例6。保存/更新用户数据
// 新增数据
RbacUser rbacUser = new RbacUser();
rbacUser.setUsername("zhangsan");
rbacUser.setPassword("123456");
rbacUser.setAge(20);
rbacUser.setCtime(System.currentTimeMillis());
rbacUserService.insert(rbacUser);
// 更新数据
rbacUser.setNickname("张三");
rbacUser.setUtime(System.currentTimeMillis());
rbacUserService.update(rbacUser);
示例7。更新指定用户数据
Cnd sql = new SQLCnd().addUpdateKeyValue(new String[]{"age", "email"}, 15, "124010356@qq.com").between("ctime", 1531843200000l, 1531929600000l).notnull("email");
rbacUserService.update(sql);
示例8。聚合函数统计数据,age>18的用户id合计是多少
Cnd sql = new SQLCnd().fields("sum(id) id").gt("age", 18);
RbacUser rbacUser = rbacUserService.findOneByCnd(sql);
System.out.println(rbacUser.getId());
示例9。按字段分组数据
Cnd sql = new SQLCnd().fields("id").gt("age", 18).groupby("id");
List<RbacUser> rbacUsers = rbacUserService.findByCnd(sql);
示例10。查询排序数据
Cnd sql = new SQLCnd().fields("id").gt("age", 18).groupby("id").orderby("id", SortBy.D);
List<RbacUser> rbacUsers = rbacUserService.findByCnd(sql);
核心接口代码:
// =
public Cnd eq(String key, Object value);
// <>
public Cnd noteq(String key, Object value);
// <
public Cnd lt(String key, Object value);
// <=
public Cnd lte(String key, Object value);
// >
public Cnd gt(String key, Object value);
// >=
public Cnd gte(String key, Object value);
// is null
public Cnd isnull(String key);
// is not null
public Cnd notnull(String key);
// between x and y
public Cnd between(String key, Object value1, Object value2);
// not between x and y
public Cnd notbetween(String key, Object value1, Object value2);
// in
public Cnd in(String key, Object... values);
// in
public Cnd in(String key, Object values);
// not in
public Cnd notin(String key, Object... values);
// not in
public Cnd notin(String key, Object values);
// like
public Cnd like(String key, Object value);
// not like
public Cnd notlike(String key, Object value);
// or
public Cnd or(Cnd... cnds);
// 分页方法(根据数据库方言)
public Cnd limit(Integer pageNo, Integer pageSize);
// 分页方法(根据数据库方言)
public Cnd limit(Integer pageNo, Integer pageSize, boolean spilled);
// 分页方法(根据数据库方言)
public Cnd limit(Integer pageSize);
// distinct
public Cnd distinct(String... keys);
// group by
public Cnd groupby(String... keys);
// order by
public Cnd orderby(String key, SortBy sortBy);
// 获取指定字段
public Cnd fields(String... keys);
// 指定更新字段
public Cnd addUpdateKeyValue(String[] keys, Object... values);
// 指定更新对象(自定义sql时使用)
public Cnd addUpdateObj(Object obj);
// 传递额外参数(自定义sql时使用)
public Cnd addOther(String key, Object value);
// from table1 a(连表查询使用)
public Cnd from(String table);
// left(right,inner) join table1 b(连表查询使用)
public Cnd join(Join join);
// 复制条件
public Cnd copy(Cnd cnd);
/**
* 保存实体信息
*
* @param entity
* @return int
* @throws BizErrorEx
*/
public int insert(E entity) throws BizErrorEx;
/**
* 保存实体集合信息
*
* @param entities
* @return int
* @throws BizErrorEx
*/
public int insert(List<E> entities) throws BizErrorEx;
/**
* 更新实体信息
*
* @param entity
* @return int
* @throws BizErrorEx
*/
public int update(E entity) throws BizErrorEx;
/**
* 更新实体信息
*
* @param cnd
* @return int
* @throws BizErrorEx
*/
public int update(Cnd cnd) throws BizErrorEx;
/**
* 通过对象删除实体信息
*
* @param entities
* @return int
* @throws BizErrorEx
*/
public int delete(E... entities) throws BizErrorEx;
/**
* 通过对象删除实体信息
*
* @param entity
* @return int
* @throws BizErrorEx
*/
public int delete(E entity) throws BizErrorEx;
/**
* 通过PK删除实体信息
*
* @param ids
* @return int
* @throws BizErrorEx
*/
public int delete(ID... ids) throws BizErrorEx;
/**
* 通过PK删除实体信息
*
* @param id
* @return int
* @throws BizErrorEx
*/
public int delete(ID id) throws BizErrorEx;
/**
* 通过Cnd删除实体信息
*
* @param cnd
* @return int
* @throws BizErrorEx
*/
public int delete(Cnd cnd) throws BizErrorEx;
/**
* 通过PK查询实体信息
*
* @param id
* @return E
* @throws BizErrorEx
*/
public E findById(ID id) throws BizErrorEx;
/**
* 通过Cnd条件对象查询实体数目
*
* @param cnd
* @return int
* @throws BizErrorEx
*/
public int countByCnd(Cnd cnd) throws BizErrorEx;
/**
* 查询实体数目
*
* @return int
* @throws BizErrorEx
*/
public int countAll() throws BizErrorEx;
/**
* 通过Cnd条件对象查询实体集合
*
* @param cnd
* @return List<E>
* @throws BizErrorEx
*/
public List<E> findByCnd(Cnd cnd) throws BizErrorEx;
/**
* 查询实体集合
*
* @return List<E>
* @throws BizErrorEx
*/
public List<E> findAll() throws BizErrorEx;
/**
* 通过Cnd条件对象查询实体
*
* @param cnd
* @return E
* @throws BizErrorEx
*/
public E findOneByCnd(Cnd cnd) throws BizErrorEx;
/**
* 通过Cnd条件对象分页查询实体
*
* @param cnd
* @return PagingResult<E>
* @throws BizErrorEx
*/
public Pagination<E> pagingByCnd(Cnd cnd) throws BizErrorEx;
/**
* 获取多个MAP数据
*
* @param cnd
* @return List<Map<String, Object>>
* @throws BizErrorEx
*/
public List<Map<String, Object>> findMapByCnd(Cnd cnd) throws BizErrorEx;
/**
* 获取多个MAP数据
*
* @param cnd
* @return List<T>
* @throws BizErrorEx
*/
public <T> List<T> findMapByCnd(Cnd cnd, Class<T> clazz) throws BizErrorEx;
/**
* 获取单个MAP数据
*
* @param cnd
* @return Map<String, Object>
* @throws BizErrorEx
*/
public Map<String, Object> findMapOneByCnd(Cnd cnd) throws BizErrorEx;
/**
* 获取单个MAP数据
*
* @param cnd
* @return T
* @throws BizErrorEx
*/
public <T> T findMapOneByCnd(Cnd cnd, Class<T> clazz) throws BizErrorEx;
/**
* 获取单独的字段单条集合
*
* @param cnd
* @param clazz
* @return T
* @throws BizErrorEx
*/
public <T> T findFieldOneByCnd(Cnd cnd, Class<T> clazz) throws BizErrorEx;
/**
* 获取单独的字段集合
*
* @param cnd
* @param clazz
* @return List<T>
* @throws BizErrorEx
*/
public <T> List<T> findFieldByCnd(Cnd cnd, Class<T> clazz) throws BizErrorEx;
由于该框架使用方式灵活多变,所以示例代码不能全部举例说明。下面我提供项目示例源码下载地址,部署项目前请先阅读readme.txt,如在使用过程中产生疑问或有改进提议可联系我,感谢您的支持。