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