操作数据库总结2

 package com.qhit.db;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.apache.commons.beanutils.BeanUtils;
import com.qhit.UseException;

/**
 * 执行数据库操作的类
 */
public class DbOper {
 private java.sql.Connection conn;
 private java.sql.Statement st;
 private java.sql.PreparedStatement pst;
 private java.sql.ResultSet rs;
 
 /**
  * 构造方法得到数据库的连接、statement
  *
  */
 public DbOper() throws UseException{
  conn = DbConn.getDbConn().getConn();
  if(conn == null)throw new UseException("得到数据库连接出错！");
  try {
   st = conn.createStatement();
  } catch (SQLException e) {
   throw new UseException(e,"得到statement对象出错！");
  }
 }
 
 /**
  * 关闭数据库连接
  *
  */
 public void closeConn() throws UseException{
  if(conn != null){
   try {
    conn.close();
    conn = null;
   } catch (SQLException e) {
    throw new UseException(e,"关闭数据库连接时出错！");
   }
  }
 }
 
 /**
  * 关闭Statement对象
  */
 public void closeSt() throws UseException{
  if(st != null){
   try {
    st.close();
    st = null;
   } catch (SQLException e) {
    // TODO Auto-generated catch block
    throw new UseException(e,"关闭数据库statement时出错！");
   }
  }
 }
 
 /**
  * 关闭PreparedStatement对象
  */
 public void closePst() throws UseException{
  if(pst != null){
   try {
    pst.close();
    pst = null;
   } catch (SQLException e) {
    // TODO Auto-generated catch block
    throw new UseException(e,"关闭数据库PreparedStatement时出错！");
   }
  }
 }
 
 /**
  *
  * 关闭结果集对象
  */
 public void closeRs() throws UseException{
  if(rs != null){
   try {
    rs.close();
    rs = null;
   } catch (SQLException e) {
    // TODO Auto-generated catch block
    throw new UseException(e,"关闭数据库ResultSet时出错！");
   }
  }
 }
 
 /**
  *
  * 关闭数据库的全部对象
  */
 public void closeAll() throws UseException{
  try{
   if(rs != null){
    rs.close();
    rs = null;
   }
   if(st != null){
    st.close();
    st = null;
   }
   
   if(pst != null){
    pst.close();
    pst = null;
   }
   
   if(conn != null){
    conn.close();
    conn = null;
   }
  }catch(SQLException e){
   throw new UseException(e,"关闭数据库的所有对象时出错！");
  }
 }
 
 /**
  * 开始一个事务
  */
 public void beginTraction() throws UseException{
  try{
   conn.setAutoCommit(false);
  }catch(SQLException e){
   e.printStackTrace();
   throw new UseException(e,"开始事务时出错!");
  }
 }
 
 /**
  * 提交一个事务
  */
 public void commitTraction()throws UseException{
  try{
   conn.commit();
  }catch(SQLException e){
   e.printStackTrace();
   throw new UseException(e,"提交事务时出错!");
  }
 }
 
 /**
  * 回滚一个事务
  */
 public void rollbackTraction()throws UseException{
  try{
   conn.rollback();
  }catch(SQLException e){
   e.printStackTrace();
   throw new UseException(e,"回滚事务时出错!");
  }
 }
 
 /**
  *
  *执行不带占位符的SQL增、删、修命令
  */
 public int exeSql(String sql) throws UseException{
  int ret = 0;
  try {
   ret = st.executeUpdate(sql);
  } catch (SQLException e) {
   throw new UseException(e,"执行：" + sql + "时出错！");
  }
  return ret;
 }
 
 /**
  *
  *执行带占位符的SQL增、删、修命令
  */
 public int exeSql(String sql,Object obj[]) throws UseException{
  int ret = 0;
  try {
   pst = conn.prepareStatement(sql);
   if(obj != null){
    for(int i=0;i<obj.length;i++){
     pst.setObject(i+1, obj[i]);
    }
   }
   ret = pst.executeUpdate();
  } catch (SQLException e) {
   throw new UseException(e,"执行：" + sql + "时出错！请检查物料编号是否已经存在");
  }
  return ret;
 }
 
 /**
  * 得到数据库表的一条记录返回为Object数组的形式
  * @throws UseException
  */
 public Object[] getARecord(String sql) throws UseException{
  Object reobj[] = null;
  try {
   rs = st.executeQuery(sql);
   int col = rs.getMetaData().getColumnCount();
   reobj = new Object[col];
   if(rs.next()){
    for(int i=1;i<=col;i++){
     if("image".equals(rs.getMetaData().getColumnTypeName(i))){
      reobj[i-1] = rs.getBytes(i);
     }else{
      reobj[i-1] = rs.getObject(i);
     }
    }
   }
  } catch (SQLException e) {
   throw new UseException(e,"执行SQL：" + sql + "转化为数组时出错！");
  }
  return reobj;
 }
 
 /**
  * 得到数据库表的一条记录(不带参数)返回为JAVABEAN对象的形式
  * @throws UseException
  */
 public Object getARecord(String sql,Class cla) throws UseException{
  Object reobj = null;
  try {
   rs = st.executeQuery(sql);
   int col = rs.getMetaData().getColumnCount();
   //得到列的属性(也为javaBean的属性)
   String colName[] = new String[col];
   for(int i=0;i<col;i++){
    colName[i] = rs.getMetaData().getColumnName(i+1);
   }
   //得到javaBean对象
   reobj = cla.newInstance();
   if(rs.next()){
    for(int i=1;i<=col;i++){
     if(cla.getDeclaredField(colName[i-1]).getType().toString().equals("class java.sql.Date")){
      if(rs.getDate(i) != null)
      BeanUtils.setProperty(reobj, colName[i-1], rs.getDate(i));
     }else{
      BeanUtils.setProperty(reobj, colName[i-1], rs.getObject(i));
     }
    }
   }
  } catch (Exception e) {
   throw new UseException(e,"执行SQL：" + sql + "转化为JAVABEAN时出错！");
  }
  return reobj;
 }
 
 /**
  * 得到数据库表的一条记录(带参数)返回为JAVABEAN对象的形式
  * @throws UseException
  */
 public Object getARecord(String sql,Object obj[],Class cla) throws UseException{
  Object reobj = null;
  try {
   pst = conn.prepareStatement(sql);
   if(obj != null){
    for(int i=0;i<obj.length;i++){
     pst.setObject(i+1, obj[i]);
    }
   }
   rs = pst.executeQuery();
   int col = rs.getMetaData().getColumnCount();
   //得到列的属性(也为javaBean的属性)
   String colName[] = new String[col];
   for(int i=0;i<col;i++){
    colName[i] = rs.getMetaData().getColumnName(i+1);
   }
   //得到javaBean对象
   reobj = cla.newInstance();
   if(rs.next()){
    for(int i=1;i<=col;i++){
     if(cla.getDeclaredField(colName[i-1]).getType().toString().equals("class java.sql.Date")){
      if(rs.getDate(i) != null)
      BeanUtils.setProperty(reobj, colName[i-1], rs.getDate(i));
     }else{
      BeanUtils.setProperty(reobj, colName[i-1], rs.getObject(i));
     }
    }
   }
  } catch (Exception e) {
   throw new UseException(e,"执行SQL：" + sql + "转化为JAVABEAN时出错！");
  }
  return reobj;
 }
 
 /**
  * 查询得到数据库的结果集，转化为Object对象数组存入List对象返回
  * @throws UseException
  */
 public java.util.List getQuery(String sql) throws UseException{
  List list = new ArrayList();
  try{
   ResultSet rs = st.executeQuery(sql);
   int col = rs.getMetaData().getColumnCount();
   while(rs.next()){
    Object obj[] = new Object[col];
    for(int i=1;i<=col;i++){
     obj[i-1] = rs.getObject(i);
    }
    list.add(obj);
   }
  }catch(SQLException e){
   throw new UseException(e,"执行" + sql + "查询时出错！");
  }
  return list;
 }
 
 /**
  * 查询得到数据库的结果集，转化为JAVABEAN对象存入List对象返回
  * cla是要转化为javabean的类型
  * @throws IllegalAccessException
  * @throws InstantiationException
  */
 public java.util.List getQuery(String sql,Class cla) throws UseException{
  List list = new ArrayList();
  try{
   
   ResultSet rs = st.executeQuery(sql);
   int col = rs.getMetaData().getColumnCount();
   
   //得到查询结果集里的字段属性
   String prop[] = new String[col];
   for(int i = 0;i<col;i++){
    prop[i] = rs.getMetaData().getColumnName(i+1);
   }
   while(rs.next()){
    //得到javabean的实例
    Object obj = cla.newInstance();
    
    //设置JAVABEAN里的属性值
    for(int i=1;i<=col;i++){
     if(cla.getDeclaredField(prop[i-1]).getType().toString().equals("class java.sql.Date")){
      if(rs.getDate(i)!= null)
      BeanUtils.setProperty(obj, prop[i-1], rs.getDate(i));
     }else{
      BeanUtils.setProperty(obj, prop[i-1], rs.getObject(i));
     }
     
    }
    list.add(obj);
   }
  }catch(Exception e){
   e.printStackTrace();
   throw new UseException(e,"执行" + sql + "查询时出错！");
  }
  return list;
 }
 
 /**
  * 查询得到数据库的结果集(带占位符参数)，转化为JAVABEAN对象存入List对象返回
  * cla是要转化为javabean的类型
  * @throws IllegalAccessException
  * @throws InstantiationException
  */
 public java.util.List getQuery(String sql,Object o[],Class cla) throws UseException{
  List list = new ArrayList();
  try{
   pst = conn.prepareStatement(sql);
   if(o != null){
    for(int i=0;i<o.length;i++){
     pst.setObject(i+1, o[i]);
    }
   }
   ResultSet rs = pst.executeQuery();
   int col = rs.getMetaData().getColumnCount();
   
   //得到查询结果集里的字段属性
   String prop[] = new String[col];
   for(int i = 0;i<col;i++){
    prop[i] = rs.getMetaData().getColumnName(i+1);
   }
   while(rs.next()){
    //得到javabean的实例
    Object obj = cla.newInstance();
    
    //设置JAVABEAN里的属性值
    for(int i=1;i<=col;i++){
     if(cla.getDeclaredField(prop[i-1]).getType().toString().equals("class java.sql.Date")){
      BeanUtils.setProperty(obj, prop[i-1], rs.getDate(i));
     }else{
      BeanUtils.setProperty(obj, prop[i-1], rs.getObject(i));
     }
     
    }
    list.add(obj);
   }
  }catch(Exception e){
   e.printStackTrace();
   throw new UseException(e,"执行" + sql + "查询时出错！");
  }
  return list;
 }
 public int getSum(String sql) throws UseException{
  int sum = 0;
  try {
   rs = st.executeQuery(sql);
   rs.next();
   sum = rs.getInt(1);
  } catch (SQLException e) {
   throw new UseException(e,"执行：" + sql + "时出错！");
  }  
  return sum;
 }
 public List getIoSum(String sql) throws UseException{
  List list = new java.util.ArrayList();
  try {
   rs = st.executeQuery(sql);
   int col = rs.getMetaData().getColumnCount();
   while(rs.next()){
    Object o[] = new Object[col];
    for(int i =1;i<=col;i++){
     o[i-1] = rs.getObject(i);
    }
    list.add(o);
   } 
  } catch (SQLException e) {
   throw new UseException(e,"执行：" + sql + "时出错！");
  }  
  return list;
 }
}