datatable.java

java 开发的电子通讯录

/**
 * Tables用来新建表单、插入数据、查找数据
 */
package edu.hqu.JYT.addressBook.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class DataTable {

	private final static String url = "jdbc:postgresql://127.0.0.1:5432/userInfo";
	private final String user;// 用户名
	private final String password;// 密码

	/**
	 * 定义关系常量
	 */
	private final static int FRIENDS = 1; // 朋友
	private final static int FAMILY = 2; // 家人
	private final static int CONNECTION = 3; // 亲戚
	private final static int COLLEAGUE = 4; // 同事
	private final static int CLASSMATE = 5; // 同学
	private final static int CLIENT = 6; // 客户
	private final static int OTHERS_1 = 7; // 其他1
	private final static int OTHERS_2 = 8; // 其他2
	private final static int OTHERS_3 = 9; // 其他3

	/**
	 * @param userName
	 * @param password
	 * @param firstRun
	 * @throws SQLException
	 */
	public DataTable(String userName, String password, boolean firstRun)
			throws SQLException {
		this.user = userName;
		this.password = password;
		if (firstRun) {
			createTables();
		}
	}

	/**
	 * @param userName
	 * @param password
	 * @throws SQLException
	 */
	public DataTable(String userName, String password) throws SQLException {
		this.user = userName;
		this.password = password;
	}

	/**
	 * 创建表单
	 *
	 * @throws SQLException
	 */
	private void createTables() throws SQLException {
		Connection con = DriverManager.getConnection(url, user, password);
		Statement st = con.createStatement();

		/*
		 * 信息表
		 */
		st.execute("create table personInfo(" + " 姓名 varchar(20) not null,"
				+ " 性别 bool default 'T'," + " 月 varchar(2) not null,"
				+ " 日 varchar(2) null," + "座机号码 varchar (13),"
				+ "办公室号码 varchar(13)," + "手机号码 varchar (13),"
				+ "EMail varchar(30)," + "QQ号 varchar(10),"
				+ "通讯地址 varchar(50)," + "邮编 varchar(6)" + ");");

		/*
		 * 关系表
		 */
		st.execute("create table relationships(" + "朋友 varchar(20),"
				+ "亲戚 varchar(20)," + "家人 varchar(20)," + "同事 varchar(20),"
				+ "同学 varchar(20)," + "客户 varchar(20)," + "其他1 varchar(20)," + // 此处用3个其他，避免那些关系不够用
				"其他2 varchar(20)," + "其他3 varchar(20));");

	}

	/**
	 * 按关系查寻
	 *
	 * @param relathionship
	 * @throws SQLException
	 */
	public ResultSet searchByRelationships(int relationship)
			throws SQLException {
		Connection con = DriverManager.getConnection(url, user, password);
		Statement st = con.createStatement();

		switch (relationship) {
		case CLASSMATE:
			return st.executeQuery("select \"同学\" from relationships");
		case CLIENT:
			return st.executeQuery("select \"客户\" from relationships");
		case COLLEAGUE:
			return st.executeQuery("select \"同事\" from relationships");
		case CONNECTION:
			return st.executeQuery("select \"亲戚\" from relationships");
		case FAMILY:
			return st.executeQuery("select \"家人\" from relationships");
		case FRIENDS:
			return st.executeQuery("select \"朋友\" from relationships");
		case OTHERS_1:
			return st.executeQuery("select \"其他1\" from relationships");
		case OTHERS_2:
			return st.executeQuery("select \"其他2\" from relationships");
		case OTHERS_3:
			return st.executeQuery("select \"其他3\" from relationships");
		}

		return null;
	}

	/**
	 * 按姓名查找
	 *
	 * @param name
	 * @throws SQLException
	 */
	public ResultSet searchByName(String name) throws SQLException {
		Connection con = DriverManager.getConnection(url, user, password);
		Statement st = con.createStatement();
		ResultSet rs = st.executeQuery("select * from personInfo where 姓名='"
				+ name + "';");
		con.close();
		return rs;
	}

	/**
	 * @param name
	 * @param relationship
	 */
	public void addInfo(String name, int relationship) throws SQLException {
		Connection con = DriverManager.getConnection(url, user, password);
		Statement st = con.createStatement();
		switch (relationship) {
		case CLASSMATE:
			st.execute("insert into relationships(同学)" + "values('" + name
					+ "');");
			break;
		case CLIENT:
			st.execute("insert into relationships(客户)" + "values('" + name
					+ "');");
			break;
		case COLLEAGUE:
			st.execute("insert into relationships(同事)" + "values('" + name
					+ "');");
			break;
		case CONNECTION:
			st.execute("insert into relationships(亲戚)" + "values('" + name
					+ "');");
			break;
		case FAMILY:
			st.execute("insert into relationships(家人)" + "values('" + name
					+ "');");
			break;
		case FRIENDS:
			st.execute("insert into relationships(朋友)" + "values('" + name
					+ "');");
			break;
		case OTHERS_1:
			st.execute("insert into relationships(其他1)" + "values('" + name
					+ "');");
			break;
		case OTHERS_2:
			st.execute("insert into relationships(其他2)" + "values('" + name
					+ "');");
			break;
		case OTHERS_3:
			st.execute("insert into relationships(其他3)" + "values('" + name
					+ "');");
			break;
		}
		st.close();
		con.close();
	}

	/**
	 * @param name
	 * @param sex
	 * @param month
	 * @param date
	 * @param telephoneNumber
	 * @param phoneNumber
	 * @param officePhoneNumber
	 * @param QQNumber
	 * @param emailAddress
	 * @param address
	 * @param postNumber
	 * @throws SQLException
	 */
	public void addInfo(String name, boolean sex, int month, int date,
			String telephoneNumber, String phoneNumber,
			String officePhoneNumber, String QQNumber, String emailAddress,
			String address, String postNumber) throws SQLException {
		Connection con = DriverManager.getConnection(url, user, password);
		Statement st = con.createStatement();
		st
				.execute("insert into personInfo(姓名,性别,月,日,座机号码,办公室号码,手机号码,EMail,通讯地址,邮编,QQ号)"
						+ "values('"
						+ name
						+ "','"
						+ sex
						+ "','"
						+ month
						+ "','"
						+ date
						+ "','"
						+ phoneNumber
						+ "','"
						+ officePhoneNumber
						+ "','"
						+ telephoneNumber
						+ "','"
						+ emailAddress
						+ "','"
						+ address
						+ "','"
						+ postNumber
						+ "','"
						+ QQNumber + "');");
		st.close();
		con.close();
	}

	public boolean deleteInfo(String name, int relationship)
			throws SQLException {
		Connection con = DriverManager.getConnection(url, user, password);
		Statement st = con.createStatement();
		st.execute("delete from personInfo where \"姓名\" = '" + name + "';");
		switch (relationship) {
		case CLASSMATE:
			return st.execute("delete from relationships where \"同学\" = '"
					+ name + "';");
		case CLIENT:
			return st.execute("delete from relationships where \"客户\" = '"
					+ name + "';");
		case COLLEAGUE:
			return st.execute("delete from relationships where \"同事\" = '"
					+ name + "';");
		case CONNECTION:
			return st.execute("delete from relationships where \"亲戚\" = '"
					+ name + "';");
		case FAMILY:
			return st.execute("delete from relationships where \"家人\" = '"
					+ name + "';");
		case FRIENDS:
			return st.execute("delete from relationships where \"朋友\" = '"
					+ name + "';");
		case OTHERS_1:
			return st.execute("delete from relationships where \"其他1\" = '"
					+ name + "';");
		case OTHERS_2:
			return st.execute("delete from relationships where \"其他1\" = '"
					+ name + "';");
		case OTHERS_3:
			return st.execute("delete from relationships where \"其他1\" = '"
					+ name + "';");

		}
		return false;
	}

	public ResultSet searchFor(int month, int date) throws SQLException {
		Connection con = DriverManager.getConnection(url, user, password);
		Statement st = con.createStatement();
		ResultSet rs = st
				.executeQuery("select \"姓名\", \"座机号码\", \"手机号码\", \"办公室号码\" from personInfo where \"月\" = '"
						+ month + "' and \"日\" = '" + date + "' ;");
		return rs;
	}
}