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();
}
}
}
jdbc
2021-10-25 02:26:36