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

来源：互联网发布：mysql教程 linux 编辑：程序博客网时间：2024/06/05 03:07

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);        }    }

0 0