Cody_Yu 通用的DAO查询，常见方法集合,基于Oracle&Hibernate&Spring，集合了多种常用方法，方便调用。

本文介绍了一个基于Hibernate的通用DAO层实现，通过该实现可以简化常见的数据库操作，如增删改查等。此外，还提供了分页查询、执行SQL、获取记录总数等功能。
importjava.io.*;
002import java.sql.SQLException;

003import java.util.ArrayList;

004import java.util.List;

005 

006import org.hibernate.Hibernate;

007import org.hibernate.HibernateException;

008import org.hibernate.Query;

009import org.hibernate.SQLQuery;

010import org.hibernate.Session;

011import org.hibernate.Transaction;

012import org.hibernate.type.Type;

013import org.springframework.orm.hibernate3.HibernateCallback;

014import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

015 

016public class
GenericDao extends 
HibernateDaoSupport{

017    private
ThreadLocal<Session> sessions = new
ThreadLocal<Session>();

018    private
ThreadLocal<Transaction> transactions = new
ThreadLocal<Transaction>();

019    public
synchronized Session getMySession(){

020        Session session = sessions.get();

021        if(session==null){

022            session = getSession();

023            transactions.set(session.beginTransaction());

024            sessions.set(session);

025        }else

026        if(!session.isOpen()){

027            session = getSession();

028            transactions.set(session.beginTransaction());

029            sessions.set(session);

030        }

031        return
session;

032    }

033    public
synchronized void 
commitMySession(){

034        Session session = sessions.get();

035        if(session!=null&&session.isOpen()){

036            transactions.get().commit();

037            session.close();

038        }

039        transactions.remove();

040        sessions.remove();

041    }

042    public
synchronized void 
rollbackMySession(){

043        Session session = sessions.get();

044        if(session!=null&&session.isOpen()){

045            transactions.get().rollback();

046            session.close();

047        }

048        transactions.remove();

049        sessions.remove();

050    }

051    public
<T> T get(Class<T> clazz, Serializable id){

052        return
(T)getHibernateTemplate().get(clazz, id);

053    }

054    public
<T> T load(Class<T> clazz, Serializable id){

055        return
(T)getHibernateTemplate().load(clazz, id);

056    }

057    public
<T> void 
save(T entity){

058//        System.out.println("---->gdao.save("+entity.getClass().getName()+")----");

059        getHibernateTemplate().save(entity);

060    }

061    public
<T> void 
update(T entity){

062        getHibernateTemplate().update(entity);

063    }

064    public
<T> void 
delete(T entity){

065        getHibernateTemplate().delete(entity);

066    }

067    

068    public
int execUpdateSQL(String sql, Serializable...values){

069        Session sess = getMySession();

070        SQLQuery query = sess.createSQLQuery(sql);

071        if(values!=null&&values.length>0){

072            for(int
i=0; i<values.length; i++){

073                query.setParameter(i, values[i]);

074            }

075        }

076        return
query.executeUpdate();

077    }

078    public
Long getFirstLong(String sql, final
Serializable... values) throws
Exception{

079        List<String> params =
new ArrayList<String>(1);

080        List<Type> types =
new ArrayList<Type>(1);

081        params.add("c"); types.add(Hibernate.INTEGER);

082        Object obj = findUnique(sql, params, types, values);

083        if(obj==null){

084            return
-1L;

085        }

086        if(obj
instanceof Object[]){

087            obj = ((Object[])obj)[0];

088        }

089        if(obj
instanceof Number){

090            return
((Number)obj).longValue();

091        }

092        return
-1L;

093    }

094//    public Long getFirstLong(String sql,  List<String>params, List<Type>types) throws Exception{

095//        Object[] obj = findUnique(sql, params, types);

096//        if(obj==null||obj.length==0){

097//            return -1L;

098//        }

099//        Object object = obj[0];

100//        if(object instanceof Number){

101//            return ((Number)object).longValue();

102//        }

103//        return -1L;

104//    }

105    public
List<?> find(String hql){

106        return
getHibernateTemplate().find(hql);

107    }

108//    public List<?> find(String hql, final Object...values){

109//        logger.info(hql);

110//        return getHibernateTemplate().find(hql, values);

111//    }

112    

113    public
<T> List<T> findByPage(Class<T> clazz, final
int start, final
int limit, final
String where, final 
Serializable... values){

114        String hql =
"from " + clazz.getName() +
" as e";

115        if(where!=null&&where.length()>0){

116            hql +=
" where " + where;

117        }

118        final
String fHql = hql;

119        return
getHibernateTemplate().executeFind(new
HibernateCallback(){

120 

121            public
Object doInHibernate(Session session)

122                    throws
HibernateException, SQLException {

123                Query q = session.createQuery(fHql);

124                if(values!=null&&values.length>0){

125                    for(int
i=0; i<values.length; i++){

126                        q.setParameter(i, values[i]);

127                    }

128                }

129                q.setFirstResult(start).setMaxResults(limit);

130                return
q.list();

131            }

132            

133        });

134    }

135    public
<T> List<T> find(Class<T> clazz, final
String where, final 
Serializable... values){

136        String hql =
"from " + clazz.getSimpleName()+" as e";

137        if(where!=null&&where.length()>0){

138            hql +=
" where " + where;

139        }

140        return
getHibernateTemplate().find(hql, values);

141    }

142    public
long getTotalCountByHql(Class<?> clazz,
final String where, 
final Serializable... values) 
throws Exception {

143        String hql =
"select count(*) from " + clazz.getSimpleName()+" as e";

144        if(where!=null&&where.length()>0){

145            hql +=
" where " + where;

146        }

147        List<Number> cs = getHibernateTemplate().find(hql, values);

148        if(cs!=null&&cs.size()>0){

149            Number n = cs.get(0);

150            return
n.longValue();

151        }

152        return
0;

153    }

154    public
long getTotalCount(Class<?> clazz,
final String where, 
final Serializable... values) 
throws Exception {

155        String sql =
"select count(1) as c from " 
+ clazz.getSimpleName() + " e";

156        if(where!=null&&where.length()>0){

157            sql +=
" where " + where;

158        }

159        return
getFirstLong(sql, values);

160    }

161    

162    public
long getTotalCount(String sql, 
final Serializable... values) 
throws Exception {

163        return
getFirstLong(sql, values).longValue();

164    }

165    public
Object[] findUnique(final
String sql,  List<String>params, List<Type>types, final
Serializable...values) {

166        Session sess = getMySession();

167        logger.debug("------findUnique.getSession()! sess="+sess.hashCode());

168        SQLQuery query = sess.createSQLQuery(sql);

169        for(int
j=0; j<params.size(); j++){

170            query.addScalar(params.get(j), types.get(j));

171        }

172        if(values!=null&&values.length>0){

173            for(int
i=0; i<values.length; i++){

174                query.setParameter(i, values[i]);

175            }

176        }

177        query.setMaxResults(1);

178        List<?> list = query.list();

179        if(list==null||list.size()==0){

180            return
null;

181        }

182        Object obj = list.get(0);

183        if(obj==null){

184            return
null;

185        }

186        logger.debug("obj.type="+obj.getClass().getName());

187        if(!obj.getClass().isArray()){//如果返回值不是数组，则要进行相关的处理

188            if(obj
instanceof Number){

189                if(obj
instanceof Long){

190                    return
new Long[]{(Long)(obj)};

191                }

192                if(obj
instanceof Integer){

193                    return
new Long[]{new
Long((Integer)obj)};

194                }

195                return
new Number[]{(Number)obj};

196            }

197            return
new Object[]{obj};

198        }

199        return
(Object[])obj;

200    }

201    public
List<Object[]> find(final
String sql, List<String>params, List<Type>types, final
Serializable... values) {

202        Session sess = getMySession();

203        logger.debug("------find.getSession()! sess="+sess.hashCode());

204        SQLQuery query = sess.createSQLQuery(sql);

205        

206        for(int
j=0; j<params.size(); j++){

207            query.addScalar(params.get(j), types.get(j));

208        }

209        if(values!=null&&values.length>0){

210            for(int
i=0; i<values.length; i++){

211                query.setParameter(i, values[i]);

212            }

213        }

214        return
query.list();

215    }

216    public
long getNextId(String sequence) 
throws Exception{

217        String sql =
"select "+sequence+".nextval as nextid from dual";

218 

219        Session sess = getMySession();

220        logger.debug("------generateId.getSession()! sess="+sess.hashCode());

221        SQLQuery query = sess.createSQLQuery(sql);

222        query.addScalar("nextid", Hibernate.LONG);

223        List<?> list = query.list();

224        if(list==null||list.size()==0){

225            return
-1L;

226        }

227        Object obj = list.get(0);

228        logger.debug("obj.type="+obj.getClass().getName());

229        if(obj
instanceof Number){

230            return
((Number)obj).longValue();

231        }

232        return
-1L;

233    }

234    public
boolean exists(String sql, 
final Object...values) throws
Exception{

235        Session sess = getMySession();

236        logger.debug("------exists.getSession()! sess="+sess.hashCode());

237        SQLQuery query = sess.createSQLQuery(sql);

238        for(int
i=0; i<values.length; i++){

239            query.setParameter(i, values[i]);

240        }

241        List<?> list = query.list();

242        if(list==null||list.size()==0){

243            return
false;

244        }

245        return
true;

246    }

247 

248}