SSH框架中通用的原生SQL查询基类支持，方便进行双orm操作

2015/11/22 47319

清华大佬耗费三个月吐血整理的几百G的资源，免费分享！....>>>

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
package com.common;
  
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
  
import org.hibernate.Hibernate;
import org.hibernate.Query;
import org.hibernate.Session;
  
  
/**
 * 通用的SQL查询基类
 */
public class BaseGeneralQuery extends AbstractHibernateDAOImpl {
      
    /**
     * 通用的SQL查询方法(返回多条记录)
     *
     *     SQL：SQL语句,切记必须指定查询字段，不可使用通配 *
     * sqlType：SQL(原生SQL)、HQL(HQL语句)
     *    
     *  List对应每一条记录，Map的put中对应中每一个字段、值。SQL语句，应规范SQL语句，HQL最好兼容SQL的写法.
     *  解析Map的时候，需要通过查询字段对应的下标获取，从0开始。如：maps.get(0).toString();
     */
    public List<Map> executeQuery(String sqlType,String sql){
        List<Map> list=null;
        try {
            list = new LinkedList<Map>();
            Session session = this.getSession();
            Query query = null;
              
            if("SQL".equals(sqlType)){
                query = session.createSQLQuery(sql);
            }
            if("HQL".equals(sqlType)){
                query = session.createQuery(sql);
            }
              
            //String hqlstr = sql.toString().substring(6, sql.toString().indexOf("from")).trim();
            //String[] codenames = hqlstr.split(",");
              
            Map maps = null;
            Iterator it  = query.list().iterator();
            while(it.hasNext()){
                maps = new HashMap();
                Object[] obj = (Object[])it.next();
                //由于SQL或者是HQL语句的查询字段的复杂性和规范性，无法进行有效的截取判断。只能进行数据条字段下标进行通用封装。
                for(int i=0;i<obj.length;i++){
                    maps.put(i, obj[i]==null?"":obj[i].toString());
                }  
                list.add(maps);
            }
              
        } catch (Exception e) {
            System.out.println("#Error BaseGeneralQuery.executeQuery(String sqlType,String sql) ["+e.getMessage()+"] ");
        }
  
        return list;
    }
      
    /**
     * 通用的SQL查询方法(返回一条数据集合)
     *     SQL：SQL语句(select aa,bb from table   
     *     返回根据语句查询到的记录对象Map映射
     */
    public Map getObject(String sql){      
        Map map = null;
        try {
            List<Object[]> object = super.getSession(true).createSQLQuery(sql).list();
          
            if(object != null && object.size() >0 ){
                map = new HashMap();
                for(int j=0;j<object.get(0).length;j++){
                    map.put(j, object.get(0)[j]);
                }
            }
               
            super.getSession(true).close();
        } catch (Exception e) {
            System.out.println("#Error BaseGeneralQuery.getObject(String sql) ["+e.getMessage()+"] ");
        }
          
        return map;
    }
      
    /**
     * 通用的SQL查询方法(返回单个字段数据)
     *   field: 单个查询语句的字段aa
     *     SQL：SQL语句(select aa from table  
     *     返回根据语句查询到的记录对象Map映射
     */
    public String getString(String field,String sql){
        String val  =  "";
        try {
            val = (String)super.getSession(true).createSQLQuery(sql).addScalar(field, Hibernate.STRING).uniqueResult();
              
            super.getSession(true).close();
        } catch (Exception e) {
            System.out.println("#Error BaseGeneralQuery.getString(String field,String sql) ["+e.getMessage()+"] ");
        }
        return val;
    }
      
    /**
     * 通用的SQL查询方法(返回记录条数)
     *
     *     SQL：SQL语句,(select count(*) counts from table )
     * sqlType：SQL(原生SQL)、HQL(HQL语句)
     *
     *     返回根据语句查询到的记录条数
     */
    public int executeQueryCount(String sqlType,String sql){
        int count = 0;
        try {
            Session session = this.getSession();
              
            if("SQL".equals(sqlType)){
                count = Integer.parseInt((String) session.createSQLQuery(sql).addScalar("counts",Hibernate.STRING).uniqueResult());
            }
            if("HQL".equals(sqlType)){
                count = session.createQuery(sql).list().size();
            }
              
        } catch (Exception e) {
            System.out.println("#Error BaseGeneralQuery.executeQueryCount(String sqlType,String sql) ["+e.getMessage()+"] ");
        }
  
        return count;
    }  
      
    /**
     * 通用的SQL添加方法
     *
     *     SQL：SQL语句,(select count(*) counts from table )
     * sqlType：SQL(原生SQL)、HQL(HQL语句)
     *
     * 返回SQL语句受影响的行数
     */
    public int executeSave(String sqlType,String sql){
        int count = 0;
        try {
            Session session = this.getSession();
              
            if("SQL".equals(sqlType)){
                count = session.createSQLQuery(sql).executeUpdate();
            }
            if("HQL".equals(sqlType)){
                //count = session.createQuery(sql).list().size();
            }
              
        } catch (Exception e) {
            System.out.println("#Error BaseGeneralQuery.executeSave(String sqlType,String sql) ["+e.getMessage()+"] ");
        }
          
        return count;
    }  
      
}