import java.beans.PropertyDescriptor;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import org.apache.commons.beanutils.BeanUtils;
public class DBComm<T>
{
/**
* 数据库查询 返回结果集
* @param SQL 查询语句
* @param t java Bean
* @return 返回结果集Model
*/
public List<T> GetModelList(String SQL,Class<T> t)
{
List<T> list = new ArrayList<T>();
Statement ment = null;
ResultSet set = null ;
Connection conn = DBConnectionPool.GetConnection();
try
{
ment = conn.createStatement();
set = ment.executeQuery(SQL);
List<String> Columns = GetRealColumnName(set,t);
while(set.next())
{
T model = GetModelList(set,t,Columns);
// T model = GetModel(set,t);
list.add(model);
}
}
catch(Exception e)
{
System.out.println("GetData:"+e.getMessage());
}
finally
{
try{
if(set!=null)set.close();
if(ment!=null)ment.close();
}catch(Exception e){}
DBConnectionPool.CloseConnection(conn);
}
return list;
}
/**
* 获取单个实例
* @param SQL 查询语句
* @param t java Bean
* @return 实例化后的Model
*/
public T GetModel(String SQL,Class<T> t)
{
T model = null;
Statement ment = null;
ResultSet set = null ;
Connection conn = DBConnectionPool.GetConnection();
try
{
ment = conn.createStatement();
set = ment.executeQuery(SQL);
if(set.next())
{
model = GetModel(set,t);
}
}
catch(Exception e)
{
System.out.println("GetData:"+e.getMessage());
}
finally
{
try{
if(set!=null)set.close();
if(ment!=null)ment.close();
}catch(Exception e){}
DBConnectionPool.CloseConnection(conn);
}
return model;
}
/**
* 查询单条数据使用 低效
* @param set
* @param t
* @param list
* @return
* @throws Exception
*/
private T GetModel(ResultSet set,Class<T> t)
{
PropertyDescriptor[] pd = org.apache.commons.beanutils.PropertyUtils.getPropertyDescriptors(t);
T model = null;
try {
model = t.newInstance();
} catch (Exception e1) {e1.printStackTrace();}
for(int i = 0;i<pd.length-1;i++)
{
try
{
String value = set.getString(pd[i].getName());
BeanUtils.setProperty(model, pd[i].getName(), value);
}
catch(Exception e){System.out.println("GetModel:"+e.getMessage()+i);continue;}
}
return model;
}
/**
* 查询多条数据建议采用 高效
* @param set
* @param t
* @param list
* @return
* @throws Exception
*/
private T GetModelList(ResultSet set,Class<T> t,List<String> list) throws Exception
{
T model = null;
model = t.newInstance();//创建JavaBean实例
for(int i = 0;i<list.size();i++)
{
try
{
String value = set.getString(list.get(i));
BeanUtils.setProperty(model,list.get(i), value);
}
catch(Exception e){throw e;}
}
return model;
}
/**
* 获取读取数据库的列数
* @param set
* @return
* @throws Exception
*/
private List<String> getColumnName(ResultSet set) throws Exception
{
List<String> list = new ArrayList<String>();
ResultSetMetaData data = set.getMetaData();
for(int i = 1;i<data.getColumnCount();i++)
{
String ColumnNames = data.getColumnName(i);
list.add(ColumnNames);
}
return list;
}
/**
* 获取JavaBean中的参数
* @param t
* @return
* @throws Exception
*/
private List<String> getColumnName(Class<T> t) throws Exception
{
List<String> list = new ArrayList<String>();
PropertyDescriptor[] pd = org.apache.commons.beanutils.PropertyUtils.getPropertyDescriptors(t);
for(int i = 0;i<pd.length-1;i++)
{
String ColumnNames = pd[i].getName();
list.add(ColumnNames);
}
return list;
}
/**
* 获取 所需的字段
* @param set 数据库读取对象
* @param t javaBean对象额Class
* @return 返回的字段集合
* @throws Exception
*/
private List<String> GetRealColumnName(ResultSet set,Class<T> t) throws Exception
{
List<String> list = new ArrayList<String>();
List<String> OracleColumn = getColumnName(set);
List<String> ModelColumn = getColumnName(t);
//最多最少的一列进行对比 优化对比效率
if(ModelColumn.size()>OracleColumn.size())
{
for(java.util.Iterator<String> ite=OracleColumn.iterator();ite.hasNext();)
{
String value = ite.next().toString().trim();
if(ModelColumn.contains(value)||ModelColumn.contains(value.toLowerCase())||ModelColumn.contains(value.toUpperCase()))
{
list.add(value);
}
}
}
else
{
for(java.util.Iterator<String> ite=ModelColumn.iterator();ite.hasNext();)
{
String value = ite.next().toString().trim();
if(OracleColumn.contains(value)||OracleColumn.contains(value.toLowerCase())||OracleColumn.contains(value.toUpperCase()))
{
list.add(value);
}
}
}
return list;
}
}
/* 使用方法 */
/*-----------------------------------------------------------------------------------------------------------------------------------------------------------------*/
try{
Calendar time = Calendar.getInstance();
long start = time.getTimeInMillis();
//UserInfo为数据库在程序中的映射类(Model)
DBComm<UserInfo> db = new DBComm<UserInfo>();
String SQL = "select a.* from (select UserInfo.username,UserInfo.USERPASS,rownum as trow from UserInfo where rownum<=1000) a where a.trow > 50";
List<UserInfo> list = db.GetModelList(SQL, UserInfo.class);
Calendar time1 = Calendar.getInstance();
long end = time1.getTimeInMillis();
System.out.println("共耗时:"+(end-start)+" 毫秒");
}catch(Exception e){}