businesssqldaoimpl.java

开发的一个java web的任务管理系统。

package unicom.dao;

import java.util.*;
import java.sql.*;

import unicom.bean.*;

import unicom.util.*;
/**
 * @author xiaogang
 * 日期：Apr 17, 2008
 * 功能：实现接口Business,封装数据库具体操作
 * 优点：
 * 缺点：
 * 建议：
 */
public class BusinessSqlDaoImpl implements IBusinessDao {
   //取得数据库中Business表的记录,并将记录以Business对象存储在List中
	public List getBusinessList() {
	  List businessList = new ArrayList();
	  String sql = "select id,name from TBusiness order by id";
	  Connection conn = DBUtil.getConn();
	  Statement stmt = DBUtil.getStatement(conn);
	  ResultSet rs = DBUtil.getResultSet(stmt, sql);
	  try {
		while(rs.next())
		  {
			  Business business = new Business();
			  business.setId(rs.getString(1));
			  business.setName(rs.getString(2));
			  businessList.add(business);
		  }
	} catch (SQLException e) {
		e.printStackTrace();
	}
	  return businessList;
	}

	//从数据库的TBusinessRule表中取出记录并以对象形式存储在List中
	public List getBusinessRuleList() {
	  {
		  List businessRuleList = new ArrayList();
		  String sql = "select id,code from TBusinessRule";
		  Connection conn = DBUtil.getConn();
		  Statement stmt = DBUtil.getStatement(conn);
		  ResultSet rs = DBUtil.getResultSet(stmt, sql);
		  try {
			while(rs.next())
			  {
				  BusinessRule businessRule = new BusinessRule();
				  businessRule.setId(rs.getString(1));
				  businessRule.setCode(rs.getString(2));
				  businessRuleList.add(businessRule);
			  }
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			//释放数据库资源
			DBUtil.close(rs);
			DBUtil.close(stmt);
			DBUtil.close(conn);
		}
		  return businessRuleList;
	  }
	}

	public List getBusinessRuleListById(String id) {
		//由id取得TBusinessRule表中的纪律,并且一BusinessRule对象存储在List中
		
			List businessRuleByIdList = new ArrayList();
			String sql = "select id,code from TBusinessRule where id = '" + id + "'";
			Connection conn = DBUtil.getConn();
			Statement stmt = DBUtil.getStatement(conn);
			ResultSet rs = DBUtil.getResultSet(stmt, sql);
			try {
				while(rs.next())
				{
					BusinessRule businessRule = new BusinessRule();
					businessRule.setId(rs.getString(1));
					businessRule.setCode(rs.getString(2));
					businessRuleByIdList.add(businessRule);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}finally{
				//释放数据库资源
				DBUtil.close(rs);
				DBUtil.close(stmt);
				DBUtil.close(conn);
			}
			return businessRuleByIdList;
		
	}

	//根据含业务规则的List,更新在数据库中更改业务规则
	public boolean updateBusinessRule(List businessRuleList) {
		boolean flag = false;
		String sqlDelete = "delete from TBusinessRule";
		Connection conn = DBUtil.getConn();
		Statement stmt = null;
		try {
			boolean autoCommit = conn.getAutoCommit();
			conn.setAutoCommit(false);
			stmt = conn.createStatement();
			
			//如果集合中无元素，则只执行删除操作
			if(businessRuleList.isEmpty())
			{
				stmt.execute(sqlDelete);
			}
			//否则，先执行删除操作，后插入操作
			else{
			stmt.execute(sqlDelete);
			for(int i=0;i<businessRuleList.size();i++)
			{
				String sqlInsert = "insert into TBusinessRule(id,code) values('O','" + businessRuleList.get(i).toString() + "')";
				stmt.addBatch(sqlInsert);
			}
			stmt.executeBatch();
			}
			
			conn.commit();
			conn.setAutoCommit(autoCommit);
			flag = true;
		} catch (SQLException e) {
			e.printStackTrace();
			try {
				//当事务出现异常，则回滚
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		}finally{
			DBUtil.close(conn);
			DBUtil.close(stmt);
		}
		
		return flag;
	}

}