packagecom.boxun.crm.util;
importjava.sql.CallableStatement;
importjava.sql.ResultSet;
importjava.sql.ResultSetMetaData;
importjava.sql.SQLException;
importjava.sql.Statement;
importjava.util.ArrayList;
importjava.util.HashMap;
importjava.util.List;
importjava.util.Map;
importorg.hibernate.HibernateException;
importorg.hibernate.Query;
importorg.hibernate.Session;
importorg.hibernate.SessionFactory;
importorg.hibernate.Transaction;
importorg.hibernate.cfg.Configuration;
publicclassDAOUtil {
privatestaticDAOUtil instance =null;
privatestaticString CONFIG_FILE_LOCATION ="/hibernate.cfg.xml";
privatestaticfinalThreadLocal threadLocal =newThreadLocal();
privatestaticfinalConfiguration cfg =newConfiguration();
privatestaticSessionFactory sessionFactory =null;
publicsynchronizedstaticDAOUtil getInstance()
{
if(instance ==null) {
instance =newDAOUtil();
try{
cfg.configure(CONFIG_FILE_LOCATION);
sessionFactory = cfg.buildSessionFactory();
}catch(HibernateException e) {
e.printStackTrace();
}
}
returninstance;
}
publicSession getSession() {
Session session = (Session) threadLocal.get();
try{
if((session ==null) || (!session.isOpen())) {
session = sessionFactory.openSession();
threadLocal.set(session);
}
}catch(HibernateException e) {
e.printStackTrace();
}
returnsession;
}
publicvoidcloseSession() {
Session session = (Session) threadLocal.get();
threadLocal.set(null);
if((session !=null) && (session.isOpen())) {
session.flush();
session.close();
}
}
privateString getSqlStr(String fname,intsize) {
StringBuffer sql =newStringBuffer();
//String call="{? call pro(?,?,?)}";
sql.append("{ ? = call ");
sql.append(fname);
sql.append("(");
for(inti =0; i
sql.append("?");
if(i
sql.append(",");
}
}
sql.append(") }");
returnsql.toString();
}
privateString formatSqlStr(String sql, ArrayList params) {
if((params ==null) || (params.size() ==0))
returnsql;
for(inti =0; i
sql = sql.replaceFirst("\\?", String.valueOf(params.get(i)));
}
returnsql;
}
@SuppressWarnings("deprecation")
publicintexecuteByFun(Session session, String fname, List params)
throwsHibernateException, SQLException {
String sql = getSqlStr(fname, params.size());
intrval = -100;
try{
if(session.isConnected()) {
CallableStatement call = session.connection().prepareCall(sql);
call.registerOutParameter(1,4);
inti =0;
for(intj =2; i
call.setObject(j, params.get(i));
i++;
}
if(!call.execute()) {
rval = call.getInt(1);
}
call.close();
}
}catch(Exception ex) {
ex.printStackTrace();
}
returnrval;
}
publicintexecuteByFun(String fname, List params)
throwsHibernateException, SQLException {
String sql = getSqlStr(fname, params.size());
intrval = -100;
try{
Session session = DAOUtil.getInstance().getSession();
if(session.isConnected()) {
CallableStatement call = session.connection().prepareCall(sql);
call.registerOutParameter(1,4);
inti =0;
for(intj =2; i
call.setObject(j, params.get(i));
i++;
}
if(!call.execute()) {
rval = call.getInt(1);
}
call.close();
}
}finally{
closeSession();
}
returnrval;
}
publicdoubleexecuteByFunDouble(String fname, ArrayList params)
throwsHibernateException, SQLException {
String sql = getSqlStr(fname, params.size());
doublerval = -100.0D;
try{
Session session = DAOUtil.getInstance().getSession();
if(session.isConnected()) {
CallableStatement call = session.connection().prepareCall(sql);
call.registerOutParameter(1,8);
inti =0;
for(intj =2; i
call.setObject(j, params.get(i));
i++;
}
if(!call.execute()) {
rval = call.getDouble(1);
}
call.close();
}
}finally{
closeSession();
}
returnrval;
}
publicintexecuteBySql(String sql, ArrayList params)
throwsHibernateException, SQLException {
Transaction tran =null;
try{
Session session = DAOUtil.getInstance().getSession();
if(session.isConnected()) {
Query query = session.createSQLQuery(sql);
for(inti =0; i
query.setParameter(i, params.get(i));
tran = session.beginTransaction();
intrval = query.executeUpdate();
tran.commit();
returnrval >0?0: -100;
}
}finally{
closeSession();
}
return-100;
}
publicdoubleexecuteBySqlDouble(String sql, ArrayList params)
throwsHibernateException, SQLException {
Transaction tran =null;
doublerval =0.0D;
List list =null;
try{
Session session = DAOUtil.getInstance().getSession();
if(session.isConnected()) {
Query query = session.createSQLQuery(sql);
for(inti =0; i
query.setParameter(i, params.get(i));
tran = session.beginTransaction();
list = query.list();
if(list !=null&& list.size() >0) {
rval = Double.parseDouble(list.get(0)==null?0.0+""
: list.get(0) +"");
}
tran.commit();
}
}finally{
closeSession();
}
returnrval;
}
publicList getResultByFun(String fname, ArrayList params)
throwsHibernateException, SQLException {
String sql = getSqlStr(fname, params.size());
List result =null;
try{
Session session = DAOUtil.getInstance().getSession();
if(session.isConnected()) {
System.out.println(session.connection().prepareCall(sql));
CallableStatement call = session.connection().prepareCall(sql);
call.registerOutParameter(1, -10);
for(inti =0, j =2; i
call.setObject(j, params.get(i));
i++;
}
if(!call.execute()) {
ResultSet rs = (ResultSet) call.getObject(1);
ResultSetMetaData rsmd = rs.getMetaData();
String[] colname =newString[rsmd.getColumnCount()];
for(intj =1, i =0; i
colname[i] = rsmd.getColumnName(j);
i++;
}
result =newArrayList();
while(rs.next()) {
Map row =newHashMap();
for(inti =0; i
row.put(colname[i], rs.getObject(colname[i]));
result.add(row);
}
rs.close();
}
call.close();
}
}finally{
closeSession();
}
returnresult;
}
publicString getResultByFunString(String fname, ArrayList params)
throwsHibernateException, SQLException {
String sql = getSqlStr(fname, params.size());
String str ="";
try{
Session session = DAOUtil.getInstance().getSession();
if(session.isConnected()) {
CallableStatement call = session.connection().prepareCall(sql);
call.registerOutParameter(1,12);
for(inti =0, j =2; i
call.setObject(j, params.get(i));
i++;
}
if(!call.execute()) {
str = String.valueOf(call.getObject(1));
}
call.close();
}
}finally{
closeSession();
}
returnstr;
}
publicList getResultBySql(String sql, ArrayList params)
throwsHibernateException, SQLException {
List result =null;
sql = formatSqlStr(sql, params);
try{
Session session = DAOUtil.getInstance().getSession();
if(session.isConnected()) {
Statement stat = session.connection().createStatement();
ResultSet rs = stat.executeQuery(sql);
ResultSetMetaData rsmd = rs.getMetaData();
String[] colname =newString[rsmd.getColumnCount()];
for(intj =1, i =0; i
colname[i] = rsmd.getColumnName(j);
i++;
}
result =newArrayList();
while(rs.next()) {
Map row =newHashMap();
for(inti =0; i
row.put(colname[i], rs.getObject(colname[i]));
result.add(row);
}
rs.close();
stat.close();
}
}finally{
closeSession();
}
returnresult;
}
}