databaseservices.java

基于Struts的通讯录系统

		int    gender = contact.getGender();
		String   birthday = contact.getBirthday();
		String personalWebsite = contact.getPersonalWebsite();
		String   anniversary = contact.getAnniversary();
		String notes = contact.getNotes();
		HomeInfo homeInfo = contact.getHomeInfo();
		BusinessInfo businessInfo = contact.getBusinessInfo();
		ImInfo imInfo = contact.getImInfo();
		EmailInfo emailInfo = contact.getEmail();
		
		// add contact
		
		String s_birthday ="";
		
		String s_anniversary ="";
		
		if(birthday.equals(""))
			
			s_birthday="null";
		else
			s_birthday="'"+birthday+"'";
		
		if(anniversary.equals(""))
			
			s_anniversary="null";
		else
			s_anniversary="'"+anniversary+"'";
		
		String sql_contact = "insert into contacts " +
				"(uid,firstname,lastname,middlename,name,nickname,mobile,gender,birthday,personalwebsite,anniversary,notes) " +
				" values("+uid+",'"+firstName+"','"+lastName+"','"+middleName+"','"+name+"','"+nickName+"','"+mobile+"',"+gender+","+s_birthday+
				" ,'"+personalWebsite+"',"+s_anniversary+",'"+notes+"')"; 
		
		System.out.println("sql_contact--:"+sql_contact);
		
		try{
			
			db.insert(sql_contact);
			
		}catch(SQLException e){
			
			e.printStackTrace();
		}
		
		//System.out.println("---------");
		
		String cid = this.getCid(uid,contact);
		
		String sql_home = "insert into homeInfo values ("+cid+",'"+homeInfo.getPhone()+"','"+homeInfo.getStreet()+"','"+homeInfo.getCity()+"'" +
		  ",'"+homeInfo.getZipcode()+"','"+homeInfo.getProvince()+"','"+homeInfo.getCountry()+"')";


		String sql_bus = "insert into businessInfo "+
		" values ("+cid+",'"+businessInfo.getCompanyName()+"','"+businessInfo.getJobTitle()+"','"+businessInfo.getDepartment()+"'" +
		 ",'"+businessInfo.getOffice()+"','"+businessInfo.getPhone()+"','"+businessInfo.getFax()+"'"+
		 ",'"+businessInfo.getPager()+"','"+businessInfo.getIpphone()+"','"+businessInfo.getBussinessWebsite()+"','"+businessInfo.getStreet()+"'" +
		 ",'"+businessInfo.getCity()+"','"+businessInfo.getZipcode()+"','"+businessInfo.getProvince()+"','"+businessInfo.getCountry()+"')";
		
		
		String sql_im = "insert into imInfo "+
			" values ("+cid+",'"+imInfo.getQq()+"','"+imInfo.getMsn()+"','"+imInfo.getGoogle()+"','"+imInfo.getIcq()+"','"+imInfo.getIrc()+"','"+imInfo.getSkype()+"','"+imInfo.getOther()+"')";
		
		String sql_insert_email = " insert into emailInfo values ("+cid+",'"+emailInfo.getEmail()+"',true)";
		
		try{
			
			db.insert(sql_home);
			
			System.out.println(sql_home);
			
			db.insert(sql_bus);
			
			System.out.println(sql_bus);
			
			db.insert(sql_im);
			
			db.insert(sql_insert_email);
			
		}catch(Exception e){
			
			e.printStackTrace();
		}
		
		
		/*
		 * update email
		 */
		
		return cid;
    }
	
	
	public String getCid (String uid,Contact contact){
    	
    	String s_birthday ="";
		
		String s_anniversary ="";
		
		if(contact.getBirthday().equals(""))
			
			s_birthday="birthday is null";
		else
			s_birthday="birthday ='"+contact.getBirthday()+"'";
		
		if(contact.getAnniversary().equals(""))
			
			s_anniversary="anniversary is null";
		else
			s_anniversary="anniversary='"+contact.getAnniversary()+"'";
    	
    	String sql = "select cid from contacts " +
    			"where uid="+uid+" and firstname='"+contact.getFirstName()+"' and lastname='"+contact.getLastName()+"' and middlename='"+contact.getMiddleName()+
    			"' and nickname='"+contact.getNickName()+"' and name='"+contact.getName()+"' and mobile='"+contact.getMobile()+"' and gender="+contact.getGender()+
    			" and "+s_birthday+" and personalwebsite='"+contact.getPersonalWebsite()+"' and "+s_anniversary+
    			" and notes='"+contact.getNotes()+"'";
    	
    	//System.out.println(sql);
    	
    	String cid="";
    	
		try {
			cid = db.selectSingle(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
    	
    	if("".equals(cid)){
    		
    		System.out.println("can't get cid");
    	}
    	
    	return cid;
    	
    }
	
	 public String getGid(String uid, String groupName){
	    	
	    	String sql = "select gid from groupInfo where uid="+uid+" and groupname='"+groupName+"'";
	    	
	    	String gid="";
	    	
	    	try{
	    		
	    		gid = db.selectSingle(sql);
	    		
	    	}catch(Exception e){
	    		
	    		e.printStackTrace();
	    	}
	    	return gid;	
	    }
	 
	 public String getGid(String cid){
	    	
	    	String sql = "select distinct gid from contactGroupInfo where cid="+cid;
	    	
	    	
	    	String gid="";
	    	
	    	try{
	    		
	    		gid = db.selectSingle(sql);
	    		
	    	}catch(Exception e){
	    		
	    		e.printStackTrace();
	    	}
	    	return gid;	
	    }

	public Vector getContact(String cid) {
		
		Vector result = new Vector();
		
		
		/*
		 * get contact infomation
		 */
		
		String sql_getContactInfo = "select a.cid,a.uid,a.firstname, a.lastname, a.middlename,a.name,a.nickname,a.mobile,a.gender,"+
			" a.birthday,a.personalwebsite,a.anniversary,a.notes " +
			" from contacts a where a.cid="+cid;
		
			
		Vector contactV = new Vector();
		
		try {
			contactV = db.select(sql_getContactInfo);
			
		} catch (SQLException e1) {
			
			e1.printStackTrace();
		} 
		
		Hashtable hashtable = (Hashtable) contactV.get(0);

		cid = hashtable.get("cid").toString();
		
		Contact contact = new Contact();
		 
		contact.setCid(cid);
			
		contact.setFirstName(hashtable.get("firstname").toString());
			
		contact.setLastName(hashtable.get("lastname").toString());
			
		contact.setMiddleName(hashtable.get("middlename").toString());
			
		contact.setName(hashtable.get("name").toString());
			
		contact.setNickName(hashtable.get("nickname").toString());
			
		contact.setMobile(hashtable.get("mobile").toString());
			
		contact.setGender(Integer.parseInt(hashtable.get("gender").toString()));
			
		contact.setPersonalWebsite(hashtable.get("personalwebsite").toString());
		
		contact.setBirthday(hashtable.get("birthday").toString());
			
		contact.setAnniversary(hashtable.get("anniversary").toString());
			
		contact.setNotes(hashtable.get("notes").toString());
			
			
		/*
		 * home info
		 */
				
		String sql_getDetailInfo = "select phone,street,city,zipcode,province,country " +
						" from homeInfo where cid="+cid;
		
		Vector tempV = new Vector();
		
		try {
			tempV = db.select(sql_getDetailInfo);
		} catch (SQLException e) {
			
			e.printStackTrace();
		}
		
		Hashtable tempH = new Hashtable();
			
		HomeInfo homeInfo = new HomeInfo();
			
		if(tempV.size()>0){
				
			tempH = (Hashtable)tempV.get(0);
				
			homeInfo.setPhone(tempH.get("phone").toString());
				
			homeInfo.setStreet(tempH.get("street").toString());
			
			homeInfo.setCity(tempH.get("city").toString());
			
			homeInfo.setZipcode(tempH.get("zipcode").toString());
			
			homeInfo.setProvince(tempH.get("province").toString());
			
			homeInfo.setCountry(tempH.get("country").toString());
		}
			
			
		
		/*
		 * business info
		 */
			
		sql_getDetailInfo = "select companyname,jobtitle,department,office,phone,fax," +
						" pager,ipphone,businesswebsite,street,city,zipcode,province,country " +
						" from businessInfo where cid="+cid;
			
		try {
			
			tempV = db.select(sql_getDetailInfo);
			
		} catch (SQLException e) {
			
			e.printStackTrace();
		}
		
	    BusinessInfo businessInfo = new BusinessInfo();	
	    
	    if(tempV.size()>0){
	    	
	    	tempH = (Hashtable)tempV.get(0);
	    	
	    	businessInfo.setCompanyName(tempH.get("companyname").toString());
	    	
	    	businessInfo.setJobTitle(tempH.get("jobtitle").toString());
	    	
	    	businessInfo.setDepartment(tempH.get("department").toString());
	    	
	    	businessInfo.setOffice(tempH.get("office").toString());
	    	
	    	businessInfo.setPhone(tempH.get("phone").toString());
	    	
	    	businessInfo.setFax(tempH.get("fax").toString());
	    	
	    	businessInfo.setPager(tempH.get("pager").toString());
	    	
	    	businessInfo.setIpphone(tempH.get("ipphone").toString());
	    	
	    	businessInfo.setBussinessWebsite(tempH.get("businesswebsite").toString());
			
	    	businessInfo.setStreet(tempH.get("street").toString());
			
	    	businessInfo.setCity(tempH.get("city").toString());
			
	    	businessInfo.setZipcode(tempH.get("zipcode").toString());
			
	    	businessInfo.setProvince(tempH.get("province").toString());
			
	    	businessInfo.setCountry(tempH.get("country").toString());
	    }
		
	    
	    /*
	     * im info
	     */
			
		sql_getDetailInfo = "select qq,msn,google,icq,irc,skype,other " +
						" from imInfo where cid="+cid;
			
		try {
			tempV = db.select(sql_getDetailInfo);
		} catch (SQLException e) {
			e.printStackTrace();
		}
			
		ImInfo imInfo = new ImInfo();
		
		 if(tempV.size()>0){
		    	
		    	tempH = (Hashtable)tempV.get(0);
		    	
		    	imInfo.setQq(tempH.get("qq").toString());
		    	
		    	imInfo.setMsn(tempH.get("msn").toString());
		    	
		    	imInfo.setGoogle(tempH.get("google").toString());
		    	
		    	imInfo.setIcq(tempH.get("icq").toString());
		    	
		    	imInfo.setIrc(tempH.get("irc").toString());
		    	
		    	imInfo.setSkype(tempH.get("skype").toString());
		    	
		    	imInfo.setOther(tempH.get("other").toString());
		   }
		
		 /*
		  * email info
		  */
		sql_getDetailInfo = "select value,type " +
			" from emailInfo where cid="+cid+" and type=1";

		try {
			
			tempV = db.select(sql_getDetailInfo);
		
		} catch (SQLException e) {
			
			e.printStackTrace();
		}
		
		EmailInfo email = new EmailInfo();
		
		if(tempV.size()>0){
			
			Hashtable ht_email = (Hashtable) tempV.get(0);
				
			email.setEmail(ht_email.get("value").toString());
				
			String type = ht_email.get("type").toString();
				
			email.setDefault((type.equals("true"))?true:false);
		}
		
		/*
		 * store in contacts
		 */
		contact.setHomeInfo(homeInfo);
		
		contact.setBusinessInfo(businessInfo);
		
		contact.setImInfo(imInfo);
		
		contact.setEmail(email);
		
		Hashtable ht = new Hashtable();
		
		ht.put("contact",contact);
		
		result.add(ht);
		
		return result;
	}
	
	public void updateContact(String cid, Contact contact){
		
		String firstName = contact.getFirstName();
		String lastName = contact.getLastName();
		String middleName = contact.getMiddleName();
		String name = contact.getName();
		String nickName = contact.getNickName();
		String mobile = contact.getMobile();
		int    gender = contact.getGender();
		String   birthday = contact.getBirthday();
		String personalWebsite = contact.getPersonalWebsite();
		String   anniversary = contact.getAnniversary();
		String notes = contact.getNotes();
		HomeInfo homeInfo = contact.getHomeInfo();
		BusinessInfo businessInfo = contact.getBusinessInfo();
		ImInfo imInfo = contact.getImInfo();
		EmailInfo emailInfo = contact.getEmail();
		
		// add contact
		
		String s_birthday ="";
		
		String s_anniversary ="";
		
		if(birthday.equals(""))
			
			s_birthday="null";
		else
			s_birthday="'"+birthday+"'";
		
		if(anniversary.equals(""))
			
			s_anniversary="null";
		else
			s_anniversary="'"+anniversary+"'";
		
		String sql_contact = "update contacts " +
				" set firstname='"+firstName+"',lastname='"+lastName+"',middlename='"+middleName+
				"',name='"+name+"',nickname='"+nickName+"',mobile='"+mobile+"',gender="+gender+
				",birthday="+s_birthday+",personalwebsite='"+personalWebsite+"',anniversary="+s_anniversary+
				",notes='"+notes+"' where cid="+cid;
		
		//System.out.println("sql_contact--:"+sql_contact);
		
		String sql_home = "update homeInfo set phone='"+homeInfo.getPhone()+"', street='"+homeInfo.getStreet()+
						  "', city='"+homeInfo.getCity()+"',province='"+homeInfo.getProvince()+"', country='"+homeInfo.getCountry()+
						  "',zipcode='"+homeInfo.getZipcode()+"' where cid="+cid;


		String sql_bus = "update businessInfo " +
				"set companyname='"+businessInfo.getCompanyName()+"',jobtitle='"+businessInfo.getJobTitle()+
				"', department='"+businessInfo.getDepartment()+"',office='"+businessInfo.getOffice()+"',phone='"+businessInfo.getPhone()+
				"',fax='"+businessInfo.getFax()+"',pager='"+businessInfo.getPager()+"',ipphone='"+businessInfo.getIpphone()+
				"',businesswebsite='"+businessInfo.getBussinessWebsite()+"',street='"+businessInfo.getStreet()+
				"',city='"+businessInfo.getCity()+"',province='"+businessInfo.getProvince()+"',country='"+businessInfo.getCountry()+
				"',zipcode='"+businessInfo.getZipcode()+"' where cid="+cid;
		
		
		
		String sql_im = "update imInfo set msn='"+imInfo.getMsn()+"',qq='"+imInfo.getQq()+"',icq='"+imInfo.getIcq()+"',irc='"+imInfo.getIrc()+
				"',google='"+imInfo.getGoogle()+"',skype='"+imInfo.getSkype()+"',other='"+imInfo.getOther()+"' where cid="+cid;
		
		String sql_email = "update emailInfo set value='"+emailInfo.getEmail()+"' where cid="+cid;


		try{
			
			System.out.println(sql_contact);
			
			db.update(sql_contact);
			
			System.out.println(sql_home);
			
			db.update(sql_home);
			
			System.out.println(sql_bus);
			
			db.update(sql_bus);
			
			System.out.println(sql_im);
			
			db.update(sql_im);
			
			System.out.println(sql_email);