hibernate 系列之ＢaseDao --基础创建sql，hql，分页查询

HQL查询

1.创建,HQL查询

public Query createQuery （String hql，Object... params）{

    Query query = this.getSessionFactory().getCurrentSession().createQuery(hql);
    for(int i=0;i<params.length;i++){
        query.setParameter(i,params[i]);
    }
    return query;
}

2.不分页，HQL查询

public List<T> query(String hql, Object... params) {
        return this.createQuery(hql, params).list();
    }

3.分页，HQL查询

/**
     * 创建HQL分页语句
     * @param hql
     * @param offset
     * @param pagesize
     * @param params
     * @return
     */
private Query createPageQuery(String hql, int offset, int pagesize,
            Object... params) {
        Query query = this.getSessionFactory().getCurrentSession()
                .createQuery(hql);
        for (int i = 0; i < params.length; i++) {
            query.setParameter(i, params[i]);
        }
        query.setFirstResult((offset-1)*pagesize).setMaxResults(pagesize);
        return query;
    }

/**
     * 分页查询
     * 
     * @Title: pageQuery
     * @Description:
     * @param hql
     * @param offset
     * @param pagesize
     * @param params
     * @return
     */
public List<T> pageQuery(String hql, int offset, int pagesize,
            Object... params) {
        return this.createPageQuery(hql, offset, pagesize, params).list();
    }

SQL查询

1.查询不分页，原生sql查询

/**
     * 查询不分页，原生sql查询
     * @Title: queryBySql
     * @Description:
     * @param sql
     * @param params
     * @return
     */
    public List queryBySql(String sql, Object... params) {
        SQLQuery sqlQuery = this.getSessionFactory().getCurrentSession()
                .createSQLQuery(sql);
        for (int i = 0; i < params.length; i++) {
            sqlQuery.setParameter(i, params[i]);
        }
        return sqlQuery.list();
    }

2.查询分页，原生sql查询

    /**
     * 查询分页，原生sql查询
     * @param sql
     * @param offset
     * @param pagesize
     * @param total
     * @param params
     * @return
     */
    public Map<String, Object> pageQueryBySql(String sql, Integer offset, Integer pagesize, Integer total, Object...params) {

        if (offset == null || offset < 0) {
            offset = 0;
        }
        if (pagesize == null || pagesize == 0) {
            pagesize = 20;
        }
        if (total == null || total == 0) {
            String countSQL = "select count(*) from (" + sql +") t";
            List result = this.queryBySql(countSQL, params);
            total = Integer.parseInt(result.get(0).toString());
        }

        List<Map<String, String>> listResult = this.pageQueryMapBySql(sql, offset, pagesize, params);

        Map<String, Object> retMap = new HashMap<String, Object>();
        retMap.put("total", total);
        retMap.put("page_size", pagesize);
        retMap.put("page", offset);
        retMap.put("data", listResult);

        return retMap;
    }

3.分页 -sql转为Map

    /***
     *分页  -sql转为Map
     * @param sql
     * @param offset
     * @param pagesize
     * @param params
     * @return
     */
    public List<Map<String, String>> pageQueryMapBySql(String sql, int offset, int pagesize,
            Object... params) {
        SQLQuery sqlQuery = this.getSessionFactory().getCurrentSession().createSQLQuery(sql);
        for (int i = 0; i < params.length; i++) {
            sqlQuery.setParameter(i, params[i]);
        }
        sqlQuery.setFirstResult((offset-1)*pagesize).setMaxResults(pagesize);

        List<Map<String, String>> retList = resultAsMap(sqlQuery,sql);

        return retList;
    }

4.不分页 -sql转为Map

/**
     * 不分页  -sql转为Map
     * @param sql
     * @param params
     * @return
     */
    public List<Map<String, String>> queryMapBySql(String sql, Object... params) {
        SQLQuery sqlQuery = this.getSessionFactory().getCurrentSession()
                .createSQLQuery(sql);
        for (int i = 0; i < params.length; i++) {
            sqlQuery.setParameter(i, params[i]);
        }

        List<Map<String, String>> retList = resultAsMap(sqlQuery, sql);

        return retList;
    }

5.list 转 Map

 /**
     * list<Object> 转 Map
     * @param sqlQuery
     * @param sql
     * @return
     */
    private List<Map<String, String>> resultAsMap(SQLQuery sqlQuery, String sql) {

        List<Map<String, String>> retList = new ArrayList<Map<String, String>>();

        List<Object[]> result = sqlQuery.list();

        String[] columns = this.getQueryColumns(sql);
        if (columns == null || columns.length == 0) {
            throw new RuntimeException("不支持的查询方式");
        }

        for (Object[] objs : result) {
            Map<String, String> rowData = new HashMap<String, String>();
//          for (int i = 0; i < objs.length; i++) {//修改分页objs为columns author:xyy ;modifytime:20170323 
            for (int i = 0; i < columns.length; i++) {
                if (objs[i] != null) {
                    rowData.put(columns[i], objs[i].toString());
                } else {
                    rowData.put(columns[i], "");
                }
            }
            retList.add(rowData);
        }
        return retList;
    }

6.获得sql语句中的列的集合

/**
     * 获得sql语句中的列的集合
     * @param sql
     * @return
     */
    private String[] getQueryColumns(String sql) {
        if (sql != null) {
            String[] keywords = sql.split(" |,");
            List<String> columns = new ArrayList<String>();
            boolean begin = false;
            for (int i= 0; i < keywords.length; i++) {
                String keyword = keywords[i];
                if ("select".equals(keyword.toLowerCase().trim())) {
                    begin = true;
                    continue; 
                }
                if ("as".equals(keyword.trim().toLowerCase())) {
//                  i++;
                    columns.remove(columns.size() - 1);
                    continue;
                }
                if ("".equals(keyword.trim()) || "*".equals(keyword.trim())) {
                    continue;
                }

                if ("from".equals(keyword.toLowerCase().trim())) {
                    begin = false;
                    break;
                }

                if (begin) {
                    keyword = keyword.replaceAll("[.]", "_").replaceAll("`", "");
                    columns.add(keyword);
                }
            }
            return columns.toArray(new String[0]);
        }
        throw new RuntimeException("SQL不能为空");
    }


    //测试
    public static void main(String args[]) {
        BaseDAO baseDao = new BaseDAO();
        String sql = "select a, b, c from x";
        String[] columns = baseDao.getQueryColumns(sql);
        for (String column : columns) {
            System.out.println(column);
        }
    }