databaseservices.java

基于Struts的通讯录系统

			db.update(sql_email);
			
		}catch(Exception e){
			
			e.printStackTrace();
		}
	}
	
	public void deleteContact(String cid){
		
		/*
		 *delete contact that belong to user 
		 */
		String sql_del_contacts = "delete from contacts where cid="+cid;
		
		/*
		 * delete homeinfo that belong to the contact
		 */
		String sql_del_homeInfo = "delete from homeInfo where cid="+cid;
		
		/*
		 * delete bussiness that belong to the contact
		 */
		String sql_del_businessInfo = "delete from businessInfo where cid="+cid;
		
		/*
		 * delete iminfo  that belong to the contact
		 */
		String sql_del_imfo = "delete from imInfo where cid="+cid;
		
		/*
		 * delete emailInfo that belong to the contact
		 */
		String sql_del_emailInfo = "delete from emailInfo where cid="+cid;
		
		/*
		 * delete contactGroupInfo that belong to the contact
		 */
		String sql_del_contactGroupInfo = "delete from contactGroupInfo where cid="+cid;
		
		try{
			
			System.out.println(sql_del_contacts);
			db.delete(sql_del_contacts);
			System.out.println(sql_del_homeInfo);
			db.delete(sql_del_homeInfo);
			System.out.println(sql_del_businessInfo);
			db.delete(sql_del_businessInfo);
			System.out.println(sql_del_imfo);
			db.delete(sql_del_imfo);
			System.out.println(sql_del_emailInfo);
			db.delete(sql_del_emailInfo);
			System.out.println(sql_del_contactGroupInfo);
			db.delete(sql_del_contactGroupInfo);
			
		}catch(Exception e){
			
			e.printStackTrace();
		}
		
	}

	public String getContactName(String cid) {
		
		String sql = "select name from contacts where cid="+cid;
		
		String name="";
		
		try {
			
			name = db.selectSingle(sql);
			
		} catch (SQLException e) {
			
			e.printStackTrace();
		}
		
		return name;
	}
	
	public Vector getContactsByGid(String gid){
		
		
		String sql = "select a.cid, a.name, a.firstname,a.lastname,a.nickname from contacts a, contactGroupInfo b where a.cid=b.cid and b.gid="+gid;
		
		Vector vector = new Vector();
		
		try {
			
			vector = db.select(sql);
			
		} catch (SQLException e) {
			
			e.printStackTrace();
		}
		
		Hashtable ht;
		
		String cid;
		
		String sql_get_email = "select value from emailInfo where type=1 and cid=";
		
		for(int i=0;i<vector.size();i++){
			
			ht = (Hashtable) vector.get(i);
			
			cid = ht.get("cid").toString();
			
			String email = "";
			
			try {
				
				email = db.selectSingle(sql_get_email+cid);
				
			} catch (SQLException e) {
				
				e.printStackTrace();
			}
			
			ht.put("email",email);
		}
		
		return vector;
		
	}
	
	public Vector getContactsByGid(String gid,String type,String order){
		
		String sql = "";
		
		Vector vector = new Vector();
		
		if(type.equals("name")){
			
			if(order.equals("esc")){
				
				
				sql = "select a.cid, a.name from contacts a, contactGroupInfo b where a.cid=b.cid and b.gid="+gid+" order by a.name";
				
			}else {
				
				sql = "select a.cid, a.name from contacts a, contactGroupInfo b where a.cid=b.cid and b.gid="+gid+" order by a.name desc";
				
			}
		
			try {
				
				vector = db.select(sql);
				
			} catch (SQLException e) {
				
				e.printStackTrace();
			}
			
			Hashtable ht;
			
			String cid;
			
			String sql_get_email = "select value from emailInfo where type=1 and cid=";
			
			for(int i=0;i<vector.size();i++){
				
				ht = (Hashtable) vector.get(i);
				
				cid = ht.get("cid").toString();
				
				String email = "";
				
				try {
					
					email = db.selectSingle(sql_get_email+cid);
					
				} catch (SQLException e) {
					
					e.printStackTrace();
				}
				
				ht.put("email",email);
			}
			
			return vector;
			
			
		}else {
			
			if(order.equals("esc")){
				
				sql = "select a.cid, a.name,c.value as email " +
					" from contacts a, contactGroupInfo b ,emailInfo c " +
					" where a.cid=b.cid and b.gid="+gid+" and c.cid=a.cid order by c.value,a.name";
				
			}else {
				
				sql = "select a.cid, a.name,c.value as email " +
					  " from contacts a, contactGroupInfo b ,emailInfo c " +
					  " where a.cid=b.cid and b.gid="+gid+" and c.cid=a.cid order by c.value desc,a.name desc";
			}
			
			
			try {
				
				vector = db.select(sql);
				
			} catch (SQLException e) {
				
				e.printStackTrace();
			}
			
			return vector;
			
		}

	
		
	}

	public boolean isGroupExit(String uid, String groupName) {
		
		String sql = "select gid from groupInfo where uid="+uid+" and groupname='"+groupName+"'";
		
		Vector result = new Vector();
		
		try {
			
			result = db.select(sql);
			
		} catch (SQLException e) {
			
			e.printStackTrace();
		}
		
		if(result.size()>0)
			
			return true;
		else 
			
			return false;
	}
	
	
	public boolean isUserExit(String userName){
		
		String sql = "select uid from account where username='"+userName+"'";
		
		Vector result = new Vector();
		
		try {
			
			result = db.select(sql);
			
		} catch (SQLException e) {
			
			e.printStackTrace();
		}
		
		if(result.size()>0)
			
			return true;
		else 
			
			return false;
		
	}
	
	public void addGroup(String uid, String groupName) {
		
		String sql = "insert into groupInfo (uid,groupname) " +
					 " values ("+uid+",'"+groupName+"')";
		
		try {
			
			db.insert(sql);
			
		} catch (SQLException e) {
			
			e.printStackTrace();
		}
		
	}
	
	public void deleteGroup (String gid,String uid){
		
		String sql_get_cid = "select cid from contactGroupInfo where gid="+gid;
		
		Vector cids = new Vector();
		
		try {
			
			cids = db.select(sql_get_cid);
			
		} catch (SQLException e) {
			
			e.printStackTrace();
		}
		
		Iterator it = cids.iterator();
		
		Hashtable ht;
		
		StringBuffer cidBuffer = new StringBuffer();
		
		while(it.hasNext()){
			
			ht=(Hashtable) it.next();
			
			cidBuffer.append(ht.get("cid")+",");
		}
		
		String s_cid = cidBuffer.toString();
		
		if(s_cid.length()>1){
		
			s_cid = s_cid.substring(0,s_cid.length()-1);
		
			String defaultGid = this.getDefautGid(uid);
			
			String sql_update_contactGroup = "update contactGroupInfo set gid="+defaultGid+" where cid in ("+s_cid+")";
			

			try {
				
				db.update(sql_update_contactGroup);
				
			} catch (SQLException e) {
				
				e.printStackTrace();
			}
		}
			/*
			 * delete group
			 */
			
			String sql_delete_group = "delete from groupInfo where gid="+gid;
			
	
			try {
				
				db.delete(sql_delete_group);
				
			} catch (SQLException e) {
				
				e.printStackTrace();
			}
			
		
	}
	
	public String getDefautGid(String uid){
		
		String sql = "select gid from groupInfo where uid="+uid;
		
		String gid ="";
		
		try {
			
			gid = db.selectSingle(sql);
			
		} catch (SQLException e) {
			
			e.printStackTrace();
		}
		
		return gid;
	}

	public void updateGroup(String gid, String groupName) {
		
		String sql =" update groupInfo set groupname='"+groupName+"' where gid="+gid;
		
		try {
			
			db.update(sql);
			
		} catch (SQLException e) {
			
			e.printStackTrace();
		}
	}
	
	public void addUser(String rUserName, String rPasswd) {
		
		String sql_addUser = "insert into account (username,password) values('"+rUserName+"','"+rPasswd+"')";
		
		int r = 0;
		
		try {
			r = db.insert(sql_addUser);
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
		
		if(r==-1){
			
			System.out.println(" add user failed,  because user already exits or some other reasons");
			
			return;
		}
		
		/*
		 * when add a new user, create a default group called defaultgroup from this user
		 */
		
		String uid = getUidByUsername(rUserName);
		
		String sql = "select * from groupInfo where uid="+uid+" and groupname='defaultgroup'";
		
		String result="";
		
		try {
			result = db.selectSingle(sql);
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		
		if("".equals(result)){
		
			String sql_addDefaultGroup ="insert into groupInfo (uid) values ("+uid+")";
	
			try{
				
				db.insert(sql_addDefaultGroup);
				
			}catch(Exception e){
				
				e.printStackTrace();
			}
			
		}
	}
	
	public String getUidByUsername(String username){
		
		String uid ="";
		
		String sql_getUid = "select uid from account where username='"+username+"'";
		
		try{
			
			uid = db.selectSingle(sql_getUid);
			
			if("".equals(uid)){
				
				System.out.println("can't get uid with username:"+username);
			}
			
		}catch(Exception e){
			
			e.printStackTrace();
		}
		
		return uid;
		
	}
	
  public void updateTime(String uid) {
		
		String sql = "update account set updatetime=null where uid="+uid;
		
		try{
			
			db.update(sql);
			
		}catch(Exception e){
			
			e.printStackTrace();
		}
	}

	/*
	
	public static void main(String args[])
	{
		DatabaseServices s = new DatabaseServices();
		
		String password = s.getPassword("wangcan");
		
		System.out.println(password);
		
	}
	*/
}