dboper.java

仓库管理系统毕业设计论文最好的参考资料！

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;
	}
}