201120:searchable排序顺序+jdbc分页查询-分

一. searchable排序顺序

实例代码

searchable.addSort(Sort.Direction.ASC,"lostState");
searchable.addSort(Sort.Direction.DESC, "createTime");

这样放先根据createTime排再是lostState,放在上面的后排序

二. searchable+jdbc分页查询

paging()方法

Page<MessageVo> paging = PersistentFactory.getJdbcDao().paging(sql, searchable, MessageVo.class, new Object[]{userId});
List<MessageVo> messageVos = paging.getContent();

三. 分页拿到的实体类需要转成vo对象

PageImpl<ErticleVo>(voList, searchCondition.getPage(), page.getTotalElements());

Page<Erticle> page = this.paging(searchCondition);
if(page.getContent().size()>0){
    voList = getVoList(page,request);//转换成VO对象
}
return new PageImpl<ErticleVo>(voList, searchCondition.getPage(), page.getTotalElements());

四. 未读消息查询

两张表 消息表和签阅表，用户读消息后往签阅表增加消息id和用户id

需求：查出未读消息

查出消息表的消息id不在签阅表的数据即可 not in

SELECT
        tm. ID,
        tm.MESSAGE_TYPE,
        tm.DATA_ID,
        tm.MESSAGE_TITLE,
        tm.CREATE_TIME,
        tm.LOST_TIME,
        tm.LOST_STATE,
        tm.CREATE_USER_ID
    FROM
        T_MESSAGE tm
    WHERE
        TM. ID NOT IN (
            SELECT
                T .MESSAGE_ID
            FROM
                T_MESSAGE_READ T
        )
    AND tm.CREATE_USER_ID != '8a748586723b0b0f017254fba99d0162'
    AND tm.DELETED = '0'
    AND tm.LOST_STATE = '1'
    ORDER BY
        tm.CREATE_TIME DESC

五. nginx配置

前端的包放在nginx/html下，配置location与服务器IP对应即可

nginx.conf文件中

server {
        listen       9999;
        server_name  localhost;

        #charset koi8-r;

        #access_log  logs/host.access.log  main;

        location / {
            root   html;
            index  index.html index.htm;
        }

    location /xmzygl/ {
            proxy_pass http://20.20.30.152:8088;
        }

六. java 构造树

树节点实体类TreeNode

public class TreeNode {
    private String cid;
    private String cname;
    private String pid;
    private List nodes = new ArrayList();
}

传入根节点id即可

sql1是获取根节点数据

sql2是第二级节点数据，用递归去赋值即可

public class TreeExtendUtils {

    public static TreeNode recursiveTree(String cid) {
        String sql1 = "SELECT CODE as cid,FULL_NAME as cname,UP_GOV_CODE pid FROM T_ORG_INFO where code ='"+cid+"'";
        List<TreeNode> treeNodeList = PersistentFactory.getJdbcDao().queryForList(sql1,TreeNode.class);
        //根据cid获取节点对象(SELECT * FROM tb_tree t WHERE t.cid=?)
        TreeNode node = treeNodeList.get(0);
        String sql2 = "SELECT CODE as cid,FULL_NAME as cname,UP_GOV_CODE pid FROM T_ORG_INFO where UP_GOV_CODE ='"+cid+"'";
        List<TreeNode> treeNodes= PersistentFactory.getJdbcDao().queryForList(sql2,TreeNode.class);
        //查询cid下的所有子节点(SELECT * FROM tb_tree t WHERE t.pid=?)
        List<TreeNode> childTreeNodes = treeNodes;
        for (TreeNode child : childTreeNodes) {
            TreeNode n = recursiveTree(child.getCid()); //递归
            node.getNodes().add(n);
        }
        return node;
    }
}