简介
sharp-database 是一个持久化操作工具的java实现，主要做查询功能。底层依赖 JdbcTemplate 。可以结合 MyBatis 的动态SQL一起使用。sharp-database 作为数据库操作工具的补充，不是用来了替换 MyBatis 或 JPA。目前支持 Mysql 和 Oracle 两种数据库，默认支持 Mysql。
pom.xml 添加依赖
<dependency>
<groupId>com.rick.db</groupId>
<artifactId>sharp-database</artifactId>
<version>1.0-SNAPSHOT</version>
</dependency>
application.xml
sharp:
database:
type: oracle
方法测试
SharpService
获取Map集合
List<Map<String, Object>> query(String sql, Map<String, ?> params)
测试代码
@Test
public void testListAsMap() {
// language=SQL
String sql = "SELECT id, title, description, create_time, is_delete, create_by\n" +
"FROM t_project\n" +
"WHERE id IN (:id)\n" +
" AND title LIKE :title\n" +
" AND create_time > :createTime\n" +
" AND is_delete = :deleted" +
" AND create_by = ${createBy}";
Map<String, Object> params = new HashMap<>();
// params.put("id", "1341369230607347714,1341299740150394882,1341734037772668930,1341313565406904322,1341368363682439169");
// params.put("id", Arrays.asList(1341369230607347714L,
// 1341299740150394882L,1341734037772668930L,1341313565406904322L,1341368363682439169L));
// params.put("id", Sets.newHashSet(1341369230607347714L,
// 1341299740150394882L, 1341734037772668930L, 1341313565406904322L, 1341368363682439169L));
params.put("id", new Long[] { 1341369230607347714L,
1341299740150394882L, 1341734037772668930L, 1341313565406904322L, 1341368363682439169L});
params.put("title", "haha");
params.put("createTime", "2020-10-22 17:27:41");
params.put("createBy", "156629745675451");
List<Map<String, Object>> list = gridService.query(sql, params);
list.forEach(System.out::println);
}
SQL打印
SELECT id, title, description, create_time, is_delete
FROM t_project
WHERE id IN (:id0, :id1, :id2, :id3, :id4)
AND UPPER(title) LIKE CONCAT('%', UPPER(:title), '%') ESCAPE '\\'
AND create_time > :createTime
args:=> [{id0=1341734037772668930, createTime=2020-10-22 17:27:41, id2=1341313565406904322, id1=1341368363682439169, id4=1341369230607347714, id3=1341299740150394882, title=haha, create_by=156629745675451}]
代码中SQL有5个变量：id、title、createTime、deleted、create_by。但是params中只提供了id、title、createTime、create_by4个参数。在执行查询的时候会忽略没有提供参数的变量。类似于 Mybatis 中的动态SQL。只是这里不需要通过if去手动判断。
常见的变量形式：
- id = :id
- id in (:id) 值可以是
ListSet等实现了Iterable接口的对象；可以是数组，如new Long[] {1, 2};或者是以逗号分割的字符串，如“1341369230607347714,1341299740150394882” - title like :title 不区分大小写，%:title%
- create_time > :createTime
- id = ${id} 这个是替换操作，会有sql注入的风险
获取对象集合
public <T> List<T> query(String sql, Map<String, ?> params, Class<T> clazz)
测试代码
@Test
public void testListAsClass() {
// language=SQL
String sql = "SELECT id, title, description, create_time, is_delete, create_by\n" +
"FROM t_project\n" +
"WHERE id IN (:id)\n" +
" AND title LIKE :title\n" +
" AND create_time > :createTime\n" +
" AND is_delete = :deleted" +
" AND create_by = ${createBy}";
Map<String, Object> params = new HashMap<>();
params.put("id", new Long[] { 1341369230607347714L,1341299740150394882L});
List<Project> list = gridService.query(sql, params, Project.class);
list.forEach(System.out::println);
}
获取自定义的集合
public <T> List<T> query(String sql, Map<String, ?> params, JdbcTemplateCallback<T> jdbcTemplateCallback)
测试代码
@Test
public void testListAsCustom() {
// language=SQL
String sql = "SELECT id, title, description, create_time, is_delete, create_by\n" +
"FROM t_project\n" +
"WHERE id IN (:id)\n" +
" AND title LIKE :title\n" +
" AND create_time > :createTime\n" +
" AND is_delete = :deleted" +
" AND create_by = ${createBy}";
Map<String, Object> params = new HashMap<>();
params.put("title", "title");
List<String> list = gridService.query(sql, params, new SharpService.JdbcTemplateCallback<String>() {
@Override
public List<String> query(NamedParameterJdbcTemplate jdbcTemplate, String sql, Map<String, ?> paramMap) {
return jdbcTemplate.query(sql, paramMap, new RowMapper<String>() {
public String mapRow(ResultSet rs, int var2) throws SQLException {
return rs.getString(1) + "-" + rs.getString(2);
}
});
}
});
list.forEach(System.out::println);
}
输出的结果是一个String集合，String是id和title通过“-”连接起来的。打印结果如下：
1341734037772668930-title1
1341736555407835137-title2
查询两列，第一列key，第二列value
public <T> List<T> query(String sql, Map<String, ?> params, JdbcTemplateCallback<T> jdbcTemplateCallback)
测试代码
@Test
public void testKeyValue() {
// language=SQL
String sql = "SELECT id, title, description, create_time, is_delete, create_by\n" +
"FROM t_project\n" +
"WHERE id IN (:id)\n" +
" AND title LIKE :title\n" +
" AND create_time > :createTime\n" +
" AND is_delete = :deleted" +
" AND create_by = ${createBy}";
Map<String, Object> params = new HashMap<>();
params.put("id", new Long[] { 1341369230607347714L,1341299740150394882L});
Map<Object, Object> map = gridService.queryForKeyValue(sql, params);
System.out.println(map);
}
打印结果如下：
{1341299740150394882=this is project title, 1341369230607347714=hello world}
查询单个对象Map,用Optional包装
public Optional<Map<String, Object>> queryForObject(String sql, Map<String, ?> params)
测试代码
@Test
public void testQueryObject() {
// language=SQL
String sql = "SELECT id, title, description, create_time, is_delete, create_by\n" +
"FROM t_project\n" +
"WHERE id IN (:id)\n" +
" AND title LIKE :title\n" +
" AND create_time > :createTime\n" +
" AND is_delete = :deleted" +
" AND create_by = ${createBy}";
Map<String, Object> params = new HashMap<>();
params.put("id", new Long[] { 1341369230607347714L});
Optional<Map<String, Object>> optionalMap = gridService.queryForObject(sql, params);
System.out.println(optionalMap.isPresent());
if (optionalMap.isPresent()) {
System.out.println(optionalMap.get());
}
}
打印结果如下：
{id=1341369230607347714, title=hello world, description=hello你税的朋友, create_time=2020-12-22 21:05:21.0, is_delete=false, create_by=156629745675451}
NOTE： 如果查询结果超过1条记录，抛出异常。
查询单个Bean对象,用Optional包装
public Optional<Map<String, Object>> queryForObject(String sql, Map<String, ?> params)
测试代码
@Test
public void testQueryBean() {
// language=SQL
String sql = "SELECT id, title, description, create_time, is_delete, create_by\n" +
"FROM t_project\n" +
"WHERE id IN (:id)\n" +
" AND title LIKE :title\n" +
" AND create_time > :createTime\n" +
" AND is_delete = :deleted" +
" AND create_by = ${createBy}";
Map<String, Object> params = new HashMap<>();
params.put("id", new Long[] { 1341369230607347714L});
Optional<Project> optionalMap = gridService.queryForObject(sql, params, Project.class);
System.out.println(optionalMap.isPresent());
if (optionalMap.isPresent()) {
System.out.println(optionalMap.get());
}
}
打印结果如下：
Project(id=1341369230607347714, title=hello world, description=hello你税的朋友, coverUrl=null, ownerId=null, deleted=null, createdAt=null, createdBy=null, updatedAt=null, updatedBy=null)
重载方法，添加了一个class参数。
NOTE： 如果查询结果超过1条记录，抛出异常。
更新操作
public int update(String sql, Map<String, ?> params)
测试代码
@Test
@Transactional(rollbackFor = Exception.class)
public void testUpdate() {
// language=SQL
String sql = "UPDATE t_project SET title = :title, description = :description, is_delete = :deleted\n" +
"WHERE id IN (:id)\n" +
" AND create_time > :createTime\n" +
"AND create_by = ${createBy}";
Map<String, Object> params = new HashMap<>();
params.put("id", new Long[] { 1341369230607347714L, 2L});
params.put("createTime", "2019-10-22 17:27:41");
// params.put("title", "new title xx");
params.put("description", "new description3");
params.put("deleted", true);
int count = gridService.update(sql, params);
System.out.println(count);
}
MappedSharpService
结合 Mybatis 动态SQL的特点，将SQL写在xml文件中。
在 pom.xml 添加依赖
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.6</version>
<scope>provided</scope>
</dependency>
获取自定义的集合
public <T> T handle(String selectId, Map<String, Object> params, SharpServiceHandler<T> sharpServiceHandler)
public <T> T handle(SharpService sharpService, String sql, Map<String, Object> params)
测试代码
@Autowired
private MappedSharpService mappedSharpService;
@Test
public void testMappedSharpService() {
// findById标签必须是<select，不能是<sql>
Map<String, Object> params = Maps.newHashMapWithExpectedSize(2);
params.put("title", "");
params.put("id", 5);
Optional<Project> optionalProject = mappedSharpService.handle("com.yodean.component.project.modules.project.dao.mapper.ProjectMapper.findById",
params,
(sharpService, sql, params1) -> sharpService.queryForObject(sql, params1, Project.class)
);
List<Project> projectList = mappedSharpService.handle("com.yodean.component.project.modules.project.dao.mapper.ProjectMapper.findById",
params,
(sharpService, sql, params1) -> sharpService.query(sql, params1, Project.class)
);
List<Map<String, Object>> projectMapList = mappedSharpService.handle("com.yodean.component.project.modules.project.dao.mapper.ProjectMapper.findById",
params,
(sharpService, sql, params1) -> sharpService.query(sql, params1)
);
log.info("project is {}", optionalProject.orElse(null));
log.info("project list as object is {}", projectList);
log.info("project list as as Map is {}", projectMapList);
}
mapper.xml
<mapper namespace="com.yodean.component.project.modules.project.dao.mapper.ProjectMapper">
<select id="findById">
<!-- gridService不能用这种变量形式 id = #{id} 或 id = ?，只能用id = :id -->
select
<include refid="column" />
from t_project where
id=:id
<if test="title != null and title !=''">
and title like ${title}
</if>
<if test="description != null and description !=''">
and description like :description
</if>
</select>
<sql id="column">
id, title, description, 'haha' as plain
</sql>
</mapper>
真正执行查询操作的还是 SharpService，只是利用了 Mybatis 动态获取了SQL。
xml中SQL写法有两点需要注意：
- 标签必须是
select，不能是sql - gridService不能用这种变量形式 id = #{id} 或 id = ?，只能用id = :id
GridService／GridUtils
GridService 继承自 SharpService，增加了分页相关的操作。GridUtils 又依赖了 GridService 提供了更方便的操作。所以我们分页操作只需要使用 GridUtils就可以了。
统计结果是数字的单行记录
public static final List<BigDecimal> numericObject(String sql, Map<String, ?> params)
比如：
- 合计summary: select sum(score), sum(money) from t_xx
- 平均值avg: select avg(score), avg(money) from t_xx
测试代码
/**
* 列求和
*/
@Test
public void testSummary() {
List<BigDecimal> list = GridUtils.numericObject("SELECT SUM(work_time) FROM t_demo", null);
list.forEach(System.out::println);
}
注意：
- 记录数只能是一行
- 数字列，可以列可以映射成
BigDecimal
查询table结果集
public static final Grid<Map<String, Object>> list(String sql, Map<String, ?> params, String countSQL, String... sortableColumns)
比如：
- countSQL：可以单独指定count的SQL，可以为null
- sortableColumns：可以允许排序的列，查询字段必须包含id列，不然mysql排序会有问题
测试代码
@Test
public void testList() {
Map params = new HashMap<>();
// 通用参数
params.put("page", 1);
params.put("size", 2);
params.put("sidx", "title");
params.put("sord", "desc");
// 业务参数
params.put("title", "version");
// Grid list = GridUtils.list("SELECT id, title FROM t_demo WHERE title like :title", params, null, new String[]{"title"});
Grid list = GridUtils.list("SELECT id, title FROM t_demo WHERE title like :title", params, null, "title");
list.getRows().forEach(System.out::println);
}
SQL打印
SELECT *
FROM (SELECT id, title FROM t_demo WHERE UPPER(title) LIKE CONCAT('%', UPPER(:title), '%') ESCAPE '\\') temp_
ORDER BY temp_.title DESC, temp_.id DESC
LIMIT 0,2
结果集 Grid 数据结构
public class Grid<T> implements Serializable {
/**
* 当前页面索引
*/
private int page;
/**
* 一页显示记录条数
*/
private int pageSize;
/***
* 总纪录数
*/
private long records;
/***
* 总页数
*/
private long totalPages;
/***
* 数据项
*/
private List<T> rows;
public static <T> Grid<T> emptyInstance(int pageSize) {
Grid<T> grid = Grid.builder().totalPages(0)
.page(1)
.rows(Collections.EMPTY_LIST)
.pageSize(pageSize)
.records(0)
.build();
return grid;
}
}
SQLUtils处理一些SQL层面的操作
查询根据多个id删除记录
public static int deleteByIn(String tableName, String deleteColumn, Collection<?> deleteValues)
测试代码
@Test
public void testDeleteByIn() {
int deletedCount = SQLUtils.deleteByIn("t_demo", "id", Arrays.asList(1358619329104297985L, 1358619736027283457L));
System.out.println(deletedCount);
}
查询根据多个id删除记录
public static int deleteByNotIn(String tableName, String deleteColumn, Collection<?> deleteValues)
测试代码和上面类似，略。
查询根据参数个数，生成in的参数？
public static String formatInSQLPlaceHolder(int paramSize)
if paramSize = 4 format as (?, ?, ?, ?)
导出Excel
在 pom.xml 添加依赖
<dependency>
<groupId>com.rick.office</groupId>
<artifactId>sharp-excel</artifactId>
<version>1.0-SNAPSHOT</version>
<scope>compile</scope>
</dependency>
ExportUtils 依赖了 GridService 提供了方便的导出操作。只需要根据查询SQL，直接导出excel文件。
public static final void export(String sql, Map<String, ?> params, OutputStream outputStream, List<MapTableColumn> columnList)
测试代码
/**
* 查询sql导出
*/
@Test
public void testSqlExport() throws IOException {
Map params = new HashMap<>();
// 业务参数
params.put("title", "version");
ExportUtils.export("SELECT id, title FROM t_demo WHERE title like :title", params,
new FileOutputStream("/Users/rick/Downloads/export/t_demo.xlsx"),
Lists.newArrayList(
new MapTableColumn("title", "标题") // 导出文件中第一列为标题，且仅导出标题，忽略id
// new MapTableColumn("id", "ID")
));
}
Controller
编写一个通用的Controller，统一获取Table结果集 Grid ，需要结合之前介绍用到的 MappedSharpService 和 GridUtils。
QueryController.java
@RestController
@RequestMapping("query")
@RequiredArgsConstructor
public class QueryController {
private final CommonTableGridBySelectIdService selectIdService;
private static final Map<String, String> selectIdMap = new HashMap<>();
static {
// 在此注册selectId
selectIdMap.put("product-list", "com.yodean.component.project.modules.project.dao.mapper.ProjectMapper.findById");
}
@GetMapping("{selectKey}")
public Grid request(@PathVariable String selectKey, HttpServletRequest request) {
return selectIdService.list(selectIdMap.get(selectKey), request);
}
}
mapper.xml中的查询SQL，之前已经贴过了。
CommonTableGridBySelectIdService.java
@Service
@RequiredArgsConstructor
public class CommonTableGridBySelectIdService {
private final MappedSharpService mappedSharpService;
public Grid<Map<String, Object>> list(String selectId, HttpServletRequest request) {
Assert.notNull(selectId, "selectId cannot be null");
Map requestParams = HttpServletRequestUtils.getParameterMap(request);
Object sidx = requestParams.get(PageModel.PARAM_SIDX);
return mappedSharpService.handle(selectId, requestParams,
(SharpServiceHandler<Grid<Map<String, Object>>>) (gridService, sql, params) -> GridUtils.list(sql, params, null
, Objects.isNull(sidx) ? null : sidx.toString()));
}
}
测试：
GET /query/product-list?title='hello world'& description=hello& sidx=title HTTP/1.1
Host: localhost:8912
cache-control: no-cache
Postman-Token: c3707206-0c4f-492e-aee2-586ac3750411
结果集JSON
{
"success": true,
"code": 200,
"msg": "成功",
"data": {
"page": 1,
"pageSize": 15,
"records": 1,
"totalPages": 1,
"rows": [
{
"id": 1341368363682439169,
"title": "hello world",
"description": "hello",
"plain": "haha"
}
]
}
}
