companydaoimpl.java

一个公司管理系统,用简单的htmlt要servlet做的,有增.查.改功能.适合同学学习交流.

package com.tarena.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.tarena.util.JdbcUtils;
import com.tarena.vo.Company;
/**
 * 
 * @author dong xuyuan
 *
 */
public class CompanyDaoImpl implements CompanyDao {

	public boolean addCompany(Company company) {
		
		boolean finished = false;
		Connection conn = null;
		PreparedStatement pstmt = null;
		StringBuffer sql = 
			new StringBuffer("insert into company" +
					"(name,city,address,scale,requiretech,kind,email,description) values(?,?,?,?,?,?,?,?)");
		try {
			conn = JdbcUtils.getConnection();
			pstmt = conn.prepareStatement(sql.toString());
			pstmt.setString(1, company.getName());
			pstmt.setString(2, company.getCity());
			pstmt.setString(3, company.getAddress());
			pstmt.setString(4, company.getScale());
			pstmt.setString(5, company.getRequiretech());
			pstmt.setString(6, company.getKind());
			pstmt.setString(7, company.getEmail());
			pstmt.setString(8, company.getDescription());
			
			pstmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
			return finished;
		} finally {
			JdbcUtils.close(pstmt);
			JdbcUtils.close(conn);
		}
		finished = true;
		return finished;
	}

	public boolean deleteCompanyById(int id) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		StringBuffer sql = new StringBuffer("delete from company where id=?");
		try {
			conn = JdbcUtils.getConnection();
			pstmt = conn.prepareStatement(sql.toString());
			pstmt.setInt(1, id);
			
			pstmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			JdbcUtils.close(pstmt);
			JdbcUtils.close(conn);
		}
		return true;
	}

	public List<Company> listCompanies() {
		List<Company> result = new ArrayList<Company>();
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "select * from company";
		try {
			conn = JdbcUtils.getConnection();
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while(rs.next()) {
				Company c = new Company();
				c.setId(rs.getInt("id"));
				c.setName(rs.getString("name"));
				c.setCity(rs.getString("city"));
				c.setAddress(rs.getString("address"));
				c.setScale(rs.getString("scale"));
				c.setKind(rs.getString("kind"));
				c.setRequiretech(rs.getString("requiretech"));
				c.setEmail(rs.getString("email"));
				c.setDescription(rs.getString("description"));
				
				result.add(c);
			}
		} catch(Exception e) {
			e.printStackTrace();
			return new ArrayList<Company>();
		} finally {
			JdbcUtils.close(rs);
			JdbcUtils.close(pstmt);
			JdbcUtils.close(conn);
		}
		
		return result;
	}
	
	public Company queryCompanyById(int id) {
		Company comp = new Company();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		StringBuffer sql = new StringBuffer("select * from company where id=?");
		
		try {
			conn = JdbcUtils.getConnection();
			pstmt = conn.prepareStatement(sql.toString());
			pstmt.setInt(1, id);
			rs = pstmt.executeQuery();
			
			if(rs.next()) {
				comp.setId(id);
				
				comp.setAddress(rs.getString("address"));
				comp.setCity(rs.getString("city"));
				comp.setDescription(rs.getString("description"));
				comp.setKind(rs.getString("kind"));
				comp.setName(rs.getString("name"));
				comp.setRequiretech(rs.getString("requiretech"));
				comp.setScale(rs.getString("requiretech"));
				comp.setEmail(rs.getString("email"));
				comp.setScale(rs.getString("scale"));
			}
		} catch(Exception e) {
			e.printStackTrace();
			return null;
		} finally {
			JdbcUtils.close(rs);
			JdbcUtils.close(pstmt);
			JdbcUtils.close(conn);
		}
		
		return comp;
	}

	public List<Company> queryCompany(Company company) {
		
		List<Company> result = new ArrayList<Company>();
		
		if(company == null) {
			return result;
		}
		
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		StringBuffer sql = new StringBuffer("select * from company where 1=1 ");
		//构造条件
		if(!isEmpty(company.getName())) {
			sql.append(" and name='" + company.getName() + "'");
		} else if(!isEmpty(company.getAddress())) {
			sql.append(" and address='" + company.getAddress() + "'");
		} else if(!isEmpty(company.getCity())) {
			sql.append(" and city='" + company.getCity() + "'");
		} else if(!isEmpty(company.getEmail())) {
			sql.append(" and email='" + company.getEmail() + "'");
		} else if(!isEmpty(company.getRequiretech())) {
			sql.append(" and requiretech='" + company.getRequiretech() + "'");
		} else if(!isEmpty(company.getKind())) {
			sql.append(" and kind ='" + company.getKind() + "'");
		} else if(!isEmpty(company.getScale())) {
			sql.append(" and scale='" + company.getScale() + "'");
		} else if(company.getId() > 0) {
			sql.append(" and id=" + company.getId());
		} else if(!isEmpty(company.getDescription())) {
			sql.append(" and description='" + company.getDescription() + "'");
		}
		
		try {
			conn = JdbcUtils.getConnection();
			//与preparedstatement不同
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql.toString());
			
			while(rs.next()) {
				Company comp = new Company();
				comp.setAddress(rs.getString("address"));
				comp.setId(rs.getInt("id"));
				comp.setCity(rs.getString("city"));
				comp.setDescription(rs.getString("description"));
				comp.setEmail(rs.getString("email"));
				comp.setKind(rs.getString("kind"));
				comp.setRequiretech(rs.getString("requiretech"));
				comp.setName(rs.getString("name"));
				comp.setScale(rs.getString("scale"));
				
				result.add(comp);
			}
		} catch(Exception e) {
			e.printStackTrace();
			return new ArrayList<Company>();
		} finally {
			JdbcUtils.close(rs);
			JdbcUtils.close(stmt);
			JdbcUtils.close(conn);
		}		
		return result;
	}

	public boolean updateCompanyById(Company company) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "update company set name=?, address=?, kind=?, city=?, requiretech=?, email=?, description=?, scale=? where id=?";
		try {
			conn = JdbcUtils.getConnection();
			pstmt = conn.prepareStatement(sql.toString());
			
			pstmt.setString(1, company.getName());
			pstmt.setString(2, company.getAddress());
			pstmt.setString(3, company.getKind());
			pstmt.setString(4, company.getCity());
			pstmt.setString(5, company.getRequiretech());
			pstmt.setString(6, company.getEmail());
			pstmt.setString(7, company.getDescription());
			pstmt.setString(8, company.getScale());
			pstmt.setInt(9, company.getId());
			
			pstmt.executeUpdate();
			
		} catch(Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			JdbcUtils.close(pstmt);
			JdbcUtils.close(conn);
		}
		return true;
	}
	
	
	private boolean isEmpty(String str) {
		return str== null || str.trim().equals("");
	}

}