databaseservices.java

基于Struts的通讯录系统

package addressbook.database.services;

import java.sql.SQLException;
import java.util.Hashtable;
import java.util.Iterator;
import java.util.Vector;

import addressbook.bean.BusinessInfo;
import addressbook.bean.Contact;
import addressbook.bean.EmailInfo;
import addressbook.bean.HomeInfo;
import addressbook.bean.ImInfo;

import addressbook.database.utils.DBTools;

public class DatabaseServices {

	DBTools db;
	
	public DatabaseServices(){
			
			db = new DBTools();	
	}
	
	public String getUid(String username){
		
		String sql = "select uid from account where username='"+username+"'";
		
		String uid = "";
		
		try {
			
			uid = db.selectSingle(sql);
			
		} catch (SQLException e) {
			
			e.printStackTrace();
		}
		
		return uid;
	}
	
	public String getPassword(String username){
		
		String sql ="select password from account where username='"+username+"'";
		
		System.out.println("-------sql is:"+sql);
		
		String password="";
		
		try {
			
			password = db.selectSingle(sql);
			
		} catch (SQLException e) {
			
			e.printStackTrace();
		}
		
		return password;
	}
	
	public Vector getAllContacts(String uid){
		
		String sql = "select a.cid, a.name, a.firstname,a.lastname,a.nickname from contacts a where a.uid="+uid;
		
		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 getAllContacts(String uid,String type,String order){
		
		String sql= "";
		
		Vector vector = new Vector();
		
		if(type.equals("name")){
			
			if(order.equals("esc"))
			
				sql = "select a.cid, a.name, a.firstname,a.lastname,a.nickname from contacts a where a.uid="+uid+" order by a.name";
			
			else 
				
				sql = "select a.cid, a.name, a.firstname,a.lastname,a.nickname from contacts a where a.uid="+uid+" 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 {// order by email
			
			if(order.equals("esc")){
				
				sql = "select a.cid,a.name,b.value as email from contacts a, emailInfo b where a.uid="+uid+" and a.cid=b.cid and b.type=1 order by b.value,a.name ";
				
			}else{
				
				sql = "select a.cid,a.name,b.value as email from contacts a, emailInfo b where a.uid="+uid+" and a.cid=b.cid and b.type=1 order by b.value desc,a.name desc";
			}
			
			try {
				
				vector = db.select(sql);
				
			} catch (SQLException e) {
				
				e.printStackTrace();
			}
			
			return vector;
			
		}
		
	}
	
	public Vector getMainInfo(String cid){
		
		String sql = "select a.cid, a.name,a.nickname,a.mobile,a.personalwebsite,a.birthday,a.anniversary,a.gender,a.notes from contacts a where a.cid="+cid;
		
		Vector vector = new Vector();
		
		try {
			
			vector = db.select(sql);
			
		} catch (SQLException e) {
			
			e.printStackTrace();
		}
		
		if(vector.size()>0){
			
			Hashtable ht = (Hashtable) vector.get(0);
			
			String gender = ht.get("gender").toString();
			
			if(gender.equals("0"))
				gender = "未知";
			else if(gender.equals("1"))
				gender = "男";
			else 
				gender = "女";
			
			ht.put("Gender",gender);
			
			/*
			 * get default email 
			 */
			
			String sql_get_email = "select value from emailInfo where type=1 and cid="+cid;
			
			String email = "";
			
			try {
				
				email = db.selectSingle(sql_get_email);
				
			} catch (SQLException e) {
				
				e.printStackTrace();
			}
			
			ht.put("email",email);
			
			/*
			 * get group info 
			 */
			
			String sql_get_groupInfo = "select a.gid ,a.groupname from groupinfo a,contactgroupinfo b where a.gid=b.gid and b.cid="+cid;
			
			String groupName="";
			
			String gid ="";
			
			try {
				
				Vector groupInfo = db.select(sql_get_groupInfo);
				
				if(groupInfo.size()>0){
					
					Hashtable h = (Hashtable)groupInfo.get(0);
					
					groupName = h.get("groupname").toString();
					
					gid = h.get("gid").toString();
				}
				
			} catch (SQLException e) {
				
				e.printStackTrace();
			}
			
			ht.put("gid",gid);
			
			if(groupName.equals("defaultgroup"))
				ht.put("groupName","默认组");
			else
				ht.put("groupName",groupName);
		}
		
		return vector;
	}
	
	
	public Vector getHomeInfo(String cid){
		
		String sql = "select phone,street,city,province,country,zipcode from homeinfo where cid="+cid;
		
		Vector vector = new Vector();
		
		try {
			
			vector = db.select(sql);
			
		} catch (SQLException e) {
			
			e.printStackTrace();
		}
		
		return vector;
	}
	
	public Vector getCompanyInfo(String cid){
		
		String sql = "select companyname,jobtitle,department,office,fax,pager,phone,street,ipphone,businesswebsite,city,province,country,zipcode " +
				" from businessInfo where cid="+cid;
		
		Vector vector = new Vector();
		
		try {
			
			vector = db.select(sql);
			
		} catch (SQLException e) {
			
			e.printStackTrace();
		}
		
		return vector;
	}
	
	public Vector getEmailInfo(String cid){
		
		String sql = "select value,type from emailInfo where cid="+cid;

		Vector vector = new Vector();

		try {
	
			vector = db.select(sql);
	
		} catch (SQLException e) {
			
			e.printStackTrace();
		}
		
		return vector;
	}
	
	public Vector getImInfo(String cid){
		
		String sql = "select qq,msn,google,icq,irc,skype,other from imInfo where cid="+cid;

		Vector vector = new Vector();

		try {
	
			vector = db.select(sql);
	
		} catch (SQLException e) {
			
			e.printStackTrace();
		}
		
		return vector;
	}
	
	public Vector getGroups(String uid){
		
		String sql = "select gid,groupname from groupInfo where uid="+uid;
		
		Vector vector = new Vector();
		
		try {
			
			vector = db.select(sql);
	
		} catch (SQLException e) {
			
			e.printStackTrace();
		}
		
		return vector;
	}
	
	public Vector getGroups(String uid,String gid){
		
		String sql = "select gid,groupname from groupInfo where uid="+uid+" and gid !="+gid;
		
		System.out.println("-----"+sql);
		
		Vector vector = new Vector();
		
		try {
			
			vector = db.select(sql);
	
		} catch (SQLException e) {
			
			e.printStackTrace();
		}
		
		return vector;
	}
	
	public String getGroupName(String uid,String gid){
		
		String sql = "select groupname from groupInfo where uid="+uid+" and gid="+gid;
		
		String groupName="";
		
		try {
			
			groupName=db.selectSingle(sql);
			
		} catch (SQLException e) {
			
			e.printStackTrace();
		}
		
		return groupName;
	}
	
	public String getGroupName(String cid){
		
		String sql = "select groupname from groupInfo a,contactGroupInfo b where a.gid=b.gid and b.cid="+cid;
		
		String groupName="";
		
		try {
			
			groupName=db.selectSingle(sql);
			
		} catch (SQLException e) {
			
			e.printStackTrace();
		}
		
		return groupName;
	}
	
	public void addContactToNewGroup(String uid,String groupName, Contact contact){
		
		/*
		 * update group info 
		 */
		String sql_add_group = "insert into groupInfo (uid,groupname) values ("+uid+",'"+groupName+"')";
		
		try{
			
			db.insert(sql_add_group);
			
		}catch(Exception e){
			
			e.printStackTrace();
		}
		
		String gid = this.getGid(uid,groupName);
		
		String cid = addContact(uid,contact);
			
		String sql_add_contactgroup="insert into contactGroupInfo values ("+gid+","+cid+")";
			
		try{
				
			db.insert(sql_add_contactgroup);
				
		}catch(Exception e){
				
			e.printStackTrace();
		}	
   }	

	public String addContact(String uid,String gid, Contact contact){
		
		
		String cid = addContact(uid,contact);
			
		String sql_add_contactgroup="insert into contactGroupInfo values ("+gid+","+cid+")";
		
		System.out.println(sql_add_contactgroup);
					
		try{
				
			db.insert(sql_add_contactgroup);
				
		}catch(Exception e){
				
			e.printStackTrace();
		}	
		
		return cid;
    }	
	
	
	public String addContact(String uid,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();