sharp-database使用指北

简介

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) 值可以是List Set 等实现了 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'&amp; description=hello&amp; 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"
            }
        ]
    }
}