jdbc

public class DataBaseManage {
Connection conn=null;
PreparedStatement ps=null;
ResultSet rs=null;
List list=null;
private Log log = LogFactory.getLog(getClass());


public  Connection  getConnection()
{
try
{
Class.forName("oracle.jdbc.driver.OracleDriver");
conn=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl","qhit","123");
//conn.setAutoCommit(false);/*设定事务不自动提交*/
}
catch(Exception ex)
{
ex.printStackTrace();
}
return  conn;
    }
public static void main(String args[]){
DataBaseManage obj=new DataBaseManage();
System.out.println("conn="+obj.getConnection());
}

//**********************************利用类反射机制查询数据对象***************************************************                                    批量处理


public List getDataListByReflectForOracle(StringBuffer buffer, PageInfoVo pagination, Class voclass)
throws SQLException {


List valueList = new ArrayList();
conn=getConnection();
try {
StringBuffer SQLBuffer = new StringBuffer("");
if (pagination == null) // 全部取出记录
{  
ps=conn.prepareStatement(buffer.toString());
rs = ps.executeQuery();
ResultSetMetaData rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount();
HashMap hmList = new HashMap();
while (rs.next()) {
for (int j = 1; j <= columnCount; j++) {
hmList.put(rsmd.getColumnName(j).toLowerCase(), rs.getString(j));
}
Object vo = null;
if (voclass != null) {
vo = voclass.newInstance();
BeanUtils.populate(vo, hmList);
valueList.add(vo);
} else {
valueList.add(hmList);
}
}
return valueList;
} else {
int pageNo = pagination.getPage();
int max = pageNo * pagination.getPageSize() + 1;
int min = (pageNo - 1) * pagination.getPageSize();
SQLBuffer.append(" select  * from (select my_table.*, rownum as my_rownum from (  ");
SQLBuffer.append(buffer);
SQLBuffer.append("  ) my_table where rownum <  ").append(max).append(") where my_rownum> ").append(min);
log.info("查询SQL：＝" + SQLBuffer.toString());


ps=conn.prepareStatement(SQLBuffer.toString());
rs = ps.executeQuery();
ResultSetMetaData rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount();
HashMap hmList = new HashMap();
while (rs.next()) {
for (int j = 1; j <= columnCount; j++) {
hmList.put(rsmd.getColumnName(j).toLowerCase(), rs.getString(j));
}
Object vo = null;
if (voclass != null) {
vo = voclass.newInstance();
BeanUtils.populate(vo, hmList);
valueList.add(vo);
} else {
valueList.add(hmList);
}
}


StringBuffer buffercount = new StringBuffer("  select  count(1)  as  b   from  (").append(
buffer.toString()).append(")");
log.info("buffercount=" + buffercount.toString());
int m = getRecordCount(buffercount.toString());
log.info("查询所得的记录数为：＝" + m);
pagination.setTotalCount(m);
return valueList;
}
} catch (Exception e) {
e.printStackTrace();
throw new SQLException(e.getMessage());
}
finally{
rs.close();
ps.close();
conn.close();
}


}
 
/*利用类反射机制自动给数据对象填充值：用于查询一条数据情况如：修改前提取数据*/


public Object getDataVoByReflect(StringBuffer SQLBuffer, Class voclass) throws SQLException {
try {
conn=getConnection();
ps=conn.prepareStatement(SQLBuffer.toString());
System.out.println("==getDataVoByReflect==sql:"+SQLBuffer.toString());
rs = ps.executeQuery();
ResultSetMetaData rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount();
   Object vo = null;
   HashMap hmList = new HashMap();
while (rs.next()) {
vo = voclass.newInstance();
/*利用第三方包所做的类反射完成数据填充*/
    for (int j = 1; j <= columnCount; j++) {
hmList.put(rsmd.getColumnName(j).toLowerCase(), rs.getString(j)); /*列的名称和列的值*/
   }
   if (voclass != null) {
vo = voclass.newInstance();
BeanUtils.populate(vo, hmList); /*调用第三方包对数据对象进行填充*/
         }
}
return vo;
} catch (Exception e) {
e.printStackTrace();
throw new SQLException(e.getMessage());
}finally{
rs.close();
ps.close();
conn.close();
}
}


/*获取总的记录数*/
private int getRecordCount(String strSql) throws SQLException {
int a = 0;
try {
conn=getConnection();
ps=conn.prepareStatement(strSql);
rs = ps.executeQuery();
rs.next();
a = rs.getInt(1);
return a;
} catch (Exception ex) {
ex.printStackTrace();
log.info("getRecordCount()" + ex.getMessage());
throw new SQLException("get getRecordCount:  falure");
}finally{
rs.close();
ps.close();
conn.close();
}
}
}