package com.zxwl.base.dao;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import javax.sql.DataSource;
import org.hibernate.HibernateException;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.HibernateTemplate;
import org.springframework.orm.hibernate3.SessionFactoryUtils;
public class ProcedureSqlServer<T>
implements IProcedure<T>
{
protected EntityHelper<T> entityHelper;
private HibernateTemplate hibernateTemplate;
public ProcedureSqlServer(EntityHelper<T> entityHelper)
{
this.entityHelper = entityHelper;
}
public String formatFieldName(String fieldName)
{
String[] names = fieldName.split("_");
StringBuilder nameBuffer = new StringBuilder();
nameBuffer.append(names[0]);
for (int i = 1; i < names.length; i++)
{
nameBuffer.append(names[i].substring(0, 1).toUpperCase());
nameBuffer.append(names[i].substring(1));
}
return nameBuffer.toString();
}
public void setHibernateTemplate(HibernateTemplate hibernateTemplate)
{
this.hibernateTemplate = hibernateTemplate;
}
public HibernateTemplate getHibernateTemplate()
{
return this.hibernateTemplate;
}
public int getDatabaseType(Class<?> type)
{
if (type == String.class)
{
return 12;
}
if (type == Integer.class)
{
return 4;
}
if (type == Short.class)
{
return 4;
}
if (type == Date.class)
{
return 91;
}
return 12;
}
public void registerParameter(CallableStatement cs, QueryParameter queryParameter)
throws SQLException
{
Set<String> paraNames = queryParameter.findParameterNames();
for (String paraName : paraNames)
{
Parameter parameter = queryParameter.findParameter(paraName);
if (parameter != null)
{
int paraType = getDatabaseType(parameter.findType());
Object value = parameter.getValue();
if (parameter.getDirction().equals("IN"))
{
cs.setObject(paraName, value, paraType);
}
else
{
cs.registerOutParameter(paraName, paraType);
if (value != null)
{
cs.setObject(paraName, value, paraType);
}
}
}
}
}
public List<T> queryList(final String procedureName, final QueryParameter queryParameter)
{
return (List)getHibernateTemplate().execute(new HibernateCallback()
{
public Object doInHibernate(Session session)
throws HibernateException, SQLException
{
Connection con = SessionFactoryUtils.getDataSource(session.getSessionFactory()).getConnection();
String queryString = "";
String queryParams = "";
int paramCount = 0;
paramCount = queryParameter.size().intValue();
for (int i = 0; i < paramCount; i++)
{
queryParams = queryParams + "?,";
}
if (!"".equals(queryParams))
{
queryParams = queryParams.substring(0, queryParams.length() - 1);
}
queryString = "{call " + procedureName + "(" + queryParams + ")}";
CallableStatement cs = con.prepareCall(queryString);
Set<String> paraNames = queryParameter.findParameterNames();
List resultList = null;
ResultSet rs = null;
try
{
ProcedureSqlServer.this.registerParameter(cs, queryParameter);
rs = cs.executeQuery();
if (ProcedureSqlServer.this.entityHelper == null)
{
throw new Exception("entityHelper异常");
}
resultList = ProcedureSqlServer.this.entityHelper.convert(rs);
for (String paraName : paraNames)
{
Parameter parameter = queryParameter.findParameter(paraName);
if (parameter != null)
{
if (parameter.getDirction().equals("OUT"))
{
parameter.setValue(cs.getObject(paraName));
}
}
}
}
catch (SQLException e)
{
e.printStackTrace();
}
catch (Exception e)
{
e.printStackTrace();
}
finally
{
if (rs != null)
{
rs.close();
}
if (cs != null)
{
cs.close();
}
if (con != null)
{
con.close();
}
}
return resultList;
}
});
}
public void execute(final String procedureName, final QueryParameter queryParameter)
{
getHibernateTemplate().execute(new HibernateCallback()
{
public Object doInHibernate(Session session)
throws HibernateException, SQLException
{
Connection con = SessionFactoryUtils.getDataSource(session.getSessionFactory()).getConnection();
String queryString = "";
String queryParams = "";
int paramCount = 0;
paramCount = queryParameter.size().intValue();
for (int i = 0; i < paramCount; i++)
{
queryParams = queryParams + "?,";
}
if (!"".equals(queryParams))
{
queryParams = queryParams.substring(0, queryParams.length() - 1);
}
queryString = "{call " + procedureName + "(" + queryParams + ")}";
CallableStatement cs = con.prepareCall(queryString);
Set<String> paraNames = queryParameter.findParameterNames();
try
{
ProcedureSqlServer.this.registerParameter(cs, queryParameter);
cs.execute();
for (String paraName : paraNames)
{
Parameter parameter = queryParameter.findParameter(paraName);
if (parameter != null)
{
if (parameter.getDirction().equals("OUT"))
{
parameter.setValue(cs.getObject(paraName));
}
}
}
}
catch (Exception e)
{
e.printStackTrace();
}
finally
{
if (cs != null)
{
cs.close();
}
if (con != null)
{
con.close();
}
}
return null;
}
});
}
public List<Map<String, String>> queryMapList(final String procedureName, final QueryParameter queryParameter)
{
return (List)getHibernateTemplate().execute(new HibernateCallback()
{
public Object doInHibernate(Session session)
throws HibernateException, SQLException
{
Connection con = SessionFactoryUtils.getDataSource(session.getSessionFactory()).getConnection();
String queryString = "";
String queryParams = "";
int paramCount = 0;
paramCount = queryParameter.size().intValue();
for (int i = 0; i < paramCount; i++)
{
queryParams = queryParams + "?,";
}
if (!"".equals(queryParams))
{
queryParams = queryParams.substring(0, queryParams.length() - 1);
}
queryString = "{call " + procedureName + "(" + queryParams + ")}";
CallableStatement cs = con.prepareCall(queryString);
Set<String> paraNames = queryParameter.findParameterNames();
List resultList = null;
ResultSet rs = null;
try
{
ProcedureSqlServer.this.registerParameter(cs, queryParameter);
rs = cs.executeQuery();
int colCount = rs.getMetaData().getColumnCount();
String[] colNameList = new String[colCount];
for (int i = 0; i < colCount; i++)
{
String fieldName = rs.getMetaData().getColumnName(i + 1);
colNameList[i] = ProcedureSqlServer.this.formatFieldName(fieldName.toLowerCase());
}
resultList = new ArrayList();
int i;
while (rs.next())
{
Map rowMap = new HashMap();
for (i = 0; i < colCount; i++)
{
Object val = rs.getObject(i + 1);
if (val != null)
{
if ((val instanceof Date))
{
val = rs.getTimestamp(i + 1);
rowMap.put(colNameList[i], val.toString());
}
else
{
rowMap.put(colNameList[i], val.toString());
}
}
else
{
rowMap.put(colNameList[i], null);
}
}
resultList.add(rowMap);
}
for (String paraName : paraNames)
{
Parameter parameter = queryParameter.findParameter(paraName);
if (parameter != null)
{
if (parameter.getDirction().equals("OUT"))
{
parameter.setValue(cs.getObject(paraName));
}
}
}
}
catch (Exception e)
{
e.printStackTrace();
}
finally
{
if (rs != null)
{
rs.close();
}
if (cs != null)
{
cs.close();
}
if (con != null)
{
con.close();
}
}
return resultList;
}
});
}
}
ProcedureSqlServer
最新推荐文章于 2024-03-09 17:06:13 发布