springdata-jpa之jdbcTemplate

一、为什么要写这篇文章

springdata-jpa提供了非常多的默认抽象，当我们选择用hibernate进行实现后，绝大多数db操作都能自动完成了，唯一头疼的问题是连表查询，当然如果按hibernate的映射方式设计数据结构是可以用jpa默认实现搞定的。但是目前绝大多数数据库设计都是定义一个字段表示外键关系，而不是用表外键约束。

比如 需要表示user下用户的权限 account 那么在 account 中有一个字段对应用户的userId  但是不会把这个字段与user表建立实际的外键关系。

那么我们在查询的时候 就需要手写sql语句了。

二、实际例子，总共分为4个环节

1、注入jdbcTemplate

@Autowired JdbcTemplate jdbcTemplate;

2、编写sql，包括动态查询sql

public PageResult<UserBo> queryPageTrade(String userNo, String userName, Integer status, Integer sort, Integer pageIndex,Integer pageSize) {
    Integer start = 0;
    if (pageSize <= 0) {
        pageSize = 20;
    }
    if (pageIndex > 1) {
        pageIndex = pageIndex - 1;
        //计算起始页 start
        start = pageIndex * pageSize;
    }
    String sqlData = " u.user_name as userName ,u.user_avatar as userAvatar, u.create_at as createAt,u.update_at as updateAt, a.name as name,a.role as role ";

    String sql = "SELECT count(1) FROM user u INNER JOIN account a on u.user_no=a.user_no where 1=1 ";

    if (!StringUtils.isEmpty(userNo)) {
        sql += " and u.user_no =" + userNo;
    }
    if (!StringUtils.isEmpty(userName)) {
        sql += " and u.user_name LIKE '%" + userName + "%'";
    }
    if (status != null && status > 0) {
        sql += " and u.status=" + status;
    }
    log.debug("totalCountSql:{}", sql);
    //获得总条数
    int totalCount = jdbcTemplate.queryForObject(sql, Integer.class);
    if (totalCount <= 0) {
        return PageUtils.convert(new ArrayList<UserBo>(), totalCount, page);
    }
    sql = sql.replace("count(1)", sqlData);
    sql += " ORDER BY ";

    if (Objects.equals(sort, 1)) {
        sql += " u.create_at ASC";
    } else {
        sql += " u.create_at DESC";
    }
    sql += " LIMIT " + start + " , " + pageSize;
    log.debug("sql:{}", sql);
    //这里需要加入用来接收jdbc数据的对象
    List<UserDTO> list = jdbcTemplate.query(sql, new UserDTO());
    List<UserBo> dlist = new ArrayList<>();
    list.forEach(dto -> {
        //这里需要将dto对象转换为可输出的对象
        dlist.add(userMapper.toUserBo(dto));
    });
    return convert(dlist, totalCount, page);
}

3、创建sql返回接受对象

/**
 * 用户查询db返回结果，注意这里实现了RowMapper，详情参阅springdata-jdbc文档
 */
@AllArgsConstructor
@NoArgsConstructor
@Data
public class UserDTO implements RowMapper<UserDTO>, Serializable {

    String userNo;
    String userAvatar;
    String name;
    String role;
    Date createAt;
    Date updateAt;

    @Override
    public UserDTO mapRow(ResultSet rs, int rowNum) {
        UserDTO dto = new UserDTO();
        //反射赋值，不嫌烦的可以一个个set
        Field[] fields = dto.getClass().getDeclaredFields();
        for (Field field : fields) {
            try {
                field.setAccessible(true);
                field.set(dto, rs.getObject(field.getName()));
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return dto;
    }
}

4、创建方法数据返回对象转换

public static <T> PageResult<T> convert(Collection<T> rows, int total, PageRequest page) {
    PageResult<T> res = new PageResult<>();
    res.setResult(rows);

    PageResult.PageInfo info = new PageResult.PageInfo();
    info.setPageIndex(page.getPageIndex());
    info.setPageSize(page.getPageSize());
    info.setPageTotal((int) Math.ceil(1.0D * total / page.getPageSize()));
    info.setTotal(total);
    info.setLastPage((total == 0 || CollectionUtils.isEmpty(rows)) ? true : info.getPageIndex() == info.getPageTotal());
    res.setPageInfo(info);
    return res;
}

欢迎关注我的个人公众号