dboperate.java

一个专家资料的管理系统

package cn.com.likai.mms.db;


import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.eclipse.jface.dialogs.MessageDialog;


public class DbOperate {
	//根据用户名得到用户对象,如返回null则表示此用户不存在
	public Member getMember(String name){
		Connection con = null;
		Statement sm = null;
		ResultSet rs = null;
		try{
			con = ConnectManager.getConnection();
			sm = con.createStatement();
			rs = sm.executeQuery("select * from mmsmember where name='" + name +"'");
			if(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"));
				return member;
			}
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			close(rs);
			close(sm);
			close(con);
		}
		return null;
	}
	
	public List getMembers(QueryInfo qi){
		Connection con = null;
		Statement sm = null;
		ResultSet rs = null;
		try{
			con = ConnectManager.getConnection();
			sm = con.createStatement();
			//得到总记录数
			rs = sm.executeQuery("select count(name) from mmsmember");
			rs.next();
			qi.rsCount = rs.getInt(1);
			if(qi.rsCount == 0)
				return Collections.emptyList();
			//算出总页数
			if(qi.rsCount%qi.pageSize == 0)
				qi.pageCount = qi.rsCount/qi.pageSize;
			else
				qi.pageCount = qi.rsCount/qi.pageSize + 1;
			//算出起始位置 = (当前页号-1)*每页记录数
			int start = (qi.currentPage - 1) * qi.pageSize;
			rs = sm.executeQuery("select * from mmsmember 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"));
				list.add(member);
			}
			return list;
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			close(rs);
			close(sm);
			close(con);
		}
		return Collections.emptyList();
	}
	
	/**
	 * 查找返回用户的方法
	 */
	public void createView(SearchMember searchMember){
		Connection con = null;
		Statement sm = null;
		ResultSet rs = null;
		try{
			con = ConnectManager.getConnection();
			sm = con.createStatement();
			con.setAutoCommit(false);//禁止自动提交事务
			//创建一个视图,得到所有的搜索结果
			String drop = "DROP TABLE IF EXISTS result";
			sm.addBatch(drop);
			sm.executeBatch();
			String sql = "CREATE TABLE result AS SELECT * FROM mmsmember WHERE 1=1";
			if(searchMember.getName()!="")
				sql = sql + " and name='" + searchMember.getName() + "'";
			if(searchMember.getSex()==0)
				sql = sql + " and sex=true";
			if(searchMember.getSex()==1)
				sql = sql + " and sex=false";
			if(searchMember.getLearnSpecialty()!="")
				sql = sql + " and learn_specialty='" + searchMember.getLearnSpecialty() + "'";
			if(searchMember.getWorkSpecialty()!="")
				sql = sql + " and work_specialty='" + searchMember.getWorkSpecialty() + "'";
			String temp[]=new String[5]; 
			temp = searchMember.getCommittee();
			for(int i=0;i<5;i++){
				if(temp[i]!=""){
					sql = sql + " and committee like '%" + temp[i] + "%'";
				}
			}
			if(!searchMember.getFromBirthday().toString().equals("0001-01-01")){
				sql = sql + " and '" + searchMember.getFromBirthday() + "'<=birthday";
				sql = sql + " and birthday<='" + searchMember.getToBirthday() + "'";
			}
			sm.addBatch(sql);
			sm.executeBatch();
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			close(rs);
			close(sm);
			close(con);
		}
	}
	public List getSearchResult(QueryInfo qi){
		Connection con = null;
		Statement sm = null;
		ResultSet rs = null;
		try{
			con = ConnectManager.getConnection();
			sm = con.createStatement();
			con.setAutoCommit(false);//禁止自动提交事务
			//得到总记录数
			rs = sm.executeQuery("select count(name) from result");
			rs.next();
			qi.rsCount = rs.getInt(1);
			if(qi.rsCount == 0)
				return Collections.emptyList();
			//算出总页数
			if(qi.rsCount%qi.pageSize == 0)
				qi.pageCount = qi.rsCount/qi.pageSize;
			else
				qi.pageCount = qi.rsCount/qi.pageSize + 1;
			//算出起始位置 = (当前页号-1)*每页记录数
			int start = (qi.currentPage - 1) * qi.pageSize;
			//导出一个EXCEL表
			rs = sm.executeQuery("select * from result");
			int rowNum=1;
			//创建一个Excel文件
			HSSFWorkbook wb = new HSSFWorkbook();
			HSSFSheet sheet = wb.createSheet("new sheet");
			HSSFRow row = sheet.createRow((short)0);
			HSSFCell cell = row.createCell((short)0);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue("序号");
			cell = row.createCell((short)1);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue("姓名");
			cell = row.createCell((short)2);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue("单位");
			cell = row.createCell((short)3);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue("学历/学位");
			cell = row.createCell((short)4);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue("技术职务职称");
			cell = row.createCell((short)5);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue("行政职务");
			cell = row.createCell((short)6);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue("出生时间");
			cell = row.createCell((short)7);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue("从事专业时间");
			cell = row.createCell((short)8);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue("从事专业");
			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);
				rowNum++;
			}
			//生成EXCEL
			try{
				String ls_path = System.getProperty("user.dir");