dboperate.java

一个专家资料的管理系统

				FileOutputStream fileOut = new FileOutputStream(ls_path + "\\result.xls");
				wb.write(fileOut);
				fileOut.close();
			}catch(IOException e){e.printStackTrace();}	
				
			
			//显示搜索结果的页面数据
			rs = sm.executeQuery("select * from result limit " + start + "," + qi.pageSize);
			List list = new ArrayList(qi.pageSize);
			while(rs.next()){
				Member member = new Member();
				member.setName(rs.getString("name"));
				member.setSex(rs.getBoolean("sex"));
				member.setBirthday(rs.getDate("birthday"));
				member.setProvince(rs.getString("province"));
				member.setPeople(rs.getString("people"));
				member.setParty(rs.getString("party"));
				member.setProfessionalTitle(rs.getString("professional_title"));
				member.setDegree(rs.getString("degree"));
				member.setEducation(rs.getString("education"));
				member.setDuty(rs.getString("duty"));
				member.setAdress(rs.getString("adress"));
				member.setZip(rs.getString("zip"));
				member.setWorkPlace(rs.getString("work_place"));
				member.setWorkPhone(rs.getString("work_phone"));
				member.setHomePhome(rs.getString("home_phone"));
				member.setEmail(rs.getString("email"));
				member.setMobilePhone(rs.getString("mobile_phone"));
				member.setLearnSpecialty(rs.getString("learn_specialty"));
				member.setWorkSpecialty(rs.getString("work_specialty"));
				member.setSchool(rs.getString("school"));
				member.setWorkStart(rs.getDate("work_start"));
				member.setIsZkyAcademician(rs.getBoolean("is_zky_academician"));
				member.setIsGcyAcademician(rs.getBoolean("is_gcy_academician"));
				member.setIsAllowance(rs.getBoolean("is_allowance"));
				member.setIsContribute(rs.getBoolean("is_contribute"));
				member.setCertificate(rs.getString("certificate"));
				member.setAcademyDuty(rs.getString("academy_duty"));
				member.setCommittee(rs.getString("committee"));
				member.setMagnumOpus(rs.getString("magnum_opus"));
				member.setProjects(rs.getString("projects"));
				member.setEncouragement(rs.getString("encouragement"));
				member.setBeCompetentFor(rs.getString("be_competent_for"));
				member.setWorkAttitude(rs.getString("work_attitude"));
				member.setGljAttitude(rs.getString("glj_attitude"));
				member.setWorkTime(rs.getString("work_time"));
				reportResult(member,rowNum,sheet);
				list.add(member);
			}
			return list;
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			close(rs);
			close(sm);
			close(con);
		}
		return Collections.emptyList();
	}
	
	/**
	 * 删除用户的方法
	 */
	//TODO:这里只是按名字删除,如有重名会一起删除,这个问题有待解决
	public boolean removeMember(Member member){
		Connection con = null;
		Statement sm = null;
		ResultSet rs = null;
		boolean result = true;
		try{
			con = ConnectManager.getConnection();
			con.setAutoCommit(false);//禁止自动提交事务
			sm = con.createStatement();
			sm.addBatch("delete from mmsmember where name='" + member.getName() + "'");
			sm.executeBatch();
			con.commit();//提交
		}catch(SQLException e){
			result = false;
			e.printStackTrace();
			//如出现异常则回滚
			try{con.rollback();}catch(Exception e2){e2.printStackTrace();}
		}finally{
			close(rs);
			close(sm);
			close(con);
		}
		return result;
	}
	
	/**
	 * 新增用户的方法
	 */
	public boolean insertMember(Member member){
		Connection con = null;
		Statement sm = null;
		ResultSet rs = null;
		try{
			con = ConnectManager.getConnection();
			
			if(getMember(member.getName())!=null){
				MessageDialog.openError(null,null,"资料库中已经有同名的人,请输入后缀");
				return false;
			}
				
			String sql = "insert into mmsmember(name,sex,birthday,province,people,party," +
					"professional_title,degree,education,duty,adress,zip,work_place," +
					"work_phone,home_phone,email,mobile_phone,learn_specialty,work_specialty," +
					"school,work_start,is_zky_academician,is_gcy_academician,is_allowance," +
					"is_contribute,certificate,academy_duty,committee,magnum_opus,projects," +
					"encouragement,be_competent_for,work_attitude,glj_attitude,work_time)" +
					" VALUES (" + "'" + member.getName().toString() + "'," + 
					member.getSex() + "," +
					"'" + member.getBirthday() + "'," +
					"'" + member.getProvince() + "'," +
					"'" + member.getPeople() + "'," +
					"'" + member.getParty() + "'," +
					"'" + member.getProfessionalTitle() + "'," +
					"'" + member.getDegree() + "'," +
					"'" + member.getEducation() + "'," +
					"'" + member.getDuty() + "'," +
					"'" + member.getAdress() + "'," +
					"'" + member.getZip() + "'," +
					"'" + member.getWorkPlace() + "'," +
					"'" + member.getWorkPhone() + "'," +
					"'" + member.getHomePhone() + "'," +
					"'" + member.getEmail() + "'," +
					"'" + member.getMobilePhone() + "'," +
					"'" + member.getLearnSpecialty() + "'," +
					"'" + member.getWorkSpecialty() + "'," +
					"'" + member.getSchool() + "'," +
					"'" + member.getWorkStart() + "'," +
					member.getIsZkyAcademician() + "," +
					member.getIsGcyAcademician() + "," +
					member.getIsAllowance() + "," +
					member.getIsContribute() + "," +
					"'" + member.getCertificate() + "'," +
					"'" + member.getAcademyDuty() + "'," +
					"'" + member.getCommittee() + "'," +
					"'" + member.getMagnumOpus() + "'," +
					"'" + member.getProjects() + "'," +
					"'" + member.getEncouragement() + "'," +
					"'" + member.getBeCompetentFor() + "'," +
					"'" + member.getWorkAttitude() + "'," +
					"'" + member.getGljAttitude() + "'," +
					"'" + member.getWorkTime() + "');";
			sm = con.createStatement();
			sm.addBatch(sql);
			sm.executeBatch();
			return true;
		}catch(SQLException e){
			e.printStackTrace();
			try{con.rollback();}catch(Exception e2){e2.printStackTrace();}
		}finally{
			close(rs);
			close(sm);
			close(con);
		}
		return false;
	}
	
	/**
	 * 修改用户方法
	 */
	public boolean modifyMember(Member member){
		Connection con = null;
		Statement sm = null;
		ResultSet rs = null;
		try{
			con = ConnectManager.getConnection();
			con.setAutoCommit(false);//禁止自动提交事务
			String sql = "update mmsmember set "+
			"name=" + "'" + member.getName().toString() + "'," +
			"sex=" + member.getSex() + "," +
			"birthday=" + "'" + member.getBirthday() + "'," +
			"province=" + "'" + member.getProvince() + "'," +
			"people=" + "'" + member.getPeople() + "'," +
			"party=" + "'" + member.getParty() + "'," +
			"professional_title=" + "'" + member.getProfessionalTitle() + "'," +
			"degree=" + "'" + member.getDegree() + "'," +
			"education=" + "'" + member.getEducation() + "'," +
			"duty=" + "'" + member.getDuty() + "'," +
			"adress=" + "'" + member.getAdress() + "'," +
			"zip=" + "'" + member.getZip() + "'," +
			"work_place=" + "'" + member.getWorkPlace() + "'," +
			"work_phone=" + "'" + member.getWorkPhone() + "'," +
			"home_phone=" + "'" + member.getHomePhone() + "'," +
			"email=" + "'" + member.getEmail() + "'," +
			"mobile_phone=" + "'" + member.getMobilePhone() + "'," +
			"learn_specialty=" + "'" + member.getLearnSpecialty() + "'," +
			"work_specialty=" + "'" + member.getWorkSpecialty() + "'," +
			"school=" + "'" + member.getSchool() + "'," +
			"work_start=" + "'" + member.getWorkStart() + "'," +
			"is_zky_academician=" + member.getIsZkyAcademician() + "," +
			"is_gcy_academician=" + member.getIsGcyAcademician() + "," +
			"is_allowance=" + member.getIsAllowance() + "," +
			"is_contribute=" + member.getIsContribute() + "," +
			"certificate=" + "'" + member.getCertificate() + "'," +
			"academy_duty=" + "'" + member.getAcademyDuty() + "'," +
			"committee=" + "'" + member.getCommittee() + "'," +
			"magnum_opus=" + "'" + member.getMagnumOpus() + "'," +
			"projects=" + "'" + member.getProjects() + "'," +
			"encouragement=" + "'" + member.getEncouragement() + "'," +
			"be_competent_for=" + "'" + member.getBeCompetentFor() + "'," +
			"work_attitude=" + "'" + member.getWorkAttitude() + "'," +
			"glj_attitude=" + "'" + member.getGljAttitude() + "'," +
			"work_time=" + "'" + member.getWorkTime() + "'" +
			" where name='" + member.getName() + "'";
			System.out.println(sql);
			sm = con.createStatement();
			sm.addBatch(sql);
			sm.executeBatch();
			con.commit();
			return true;
		}catch(SQLException e){
			e.printStackTrace();
			try{con.rollback();}catch(Exception e2){e2.printStackTrace();}
		}finally{
			close(rs);
			close(sm);
			close(con);
		}
		return false;
	}

	/**
	 * 关闭ResultSet对象
	 */
	void close(ResultSet rs){
		if(rs != null){
			try{rs.close();}catch(SQLException e){e.printStackTrace();}
			rs = null;
		}
	}
	/**
	 * 关闭Statement对象
	 */
	void close(Statement sm){
		if(sm != null){
			try{sm.close();}catch(SQLException e){e.printStackTrace();}
			sm = null;
		}
	}
	
	/**
	 * 关闭Connection对象
	 * @param con
	 */
	void close(Connection con){}
	
	
//	STRING到日期 
	public static java.sql.Date stringToDate(String dateStr) 
	{ 
		return java.sql.Date.valueOf(dateStr); 
	} 
	
//	日期到STRING 
	public static String dateToString(java.sql.Date datee) 
	{ 
		return datee.toString(); 
	}
	
	//输出搜索结果的方法
	public void reportResult(Member member,int rowNum,HSSFSheet sheet){
		HSSFRow row = sheet.createRow((short)rowNum);
		HSSFCell cell = row.createCell((short)0);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue(rowNum);
		cell = row.createCell((short)1);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue(member.getName());
		cell = row.createCell((short)2);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue(member.getWorkPlace());
		cell = row.createCell((short)3);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue(member.getEducation() + "/" + member.getDegree());
		cell = row.createCell((short)4);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue(member.getProfessionalTitle());
		cell = row.createCell((short)5);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue(member.getDuty());
		cell = row.createCell((short)6);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue(dateToString(member.getBirthday()));
		cell = row.createCell((short)7);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue(member.getWorkTime());
		cell = row.createCell((short)8);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue(member.getWorkSpecialty());
	}
}