storage.java

一个简单的java邮件系统

			ps.setString(pos, "%" + comments + "%");
		}

		Vector result = new Vector(10);
		ResultSet rs = ps.executeQuery();
		while (rs.next()) {
			Domain domain = new Domain();
			domain.id = rs.getString(1);
			domain.name = rs.getString(2);
			domain.comments = rs.getString(3);
			result.add(domain);
		}
		rs.close();
		ps.close();
		return result;
	}

	public synchronized User getUser(String id) throws Exception {
		PreparedStatement ps = mConn
				.prepareStatement("select ID, NAME, COMMENTS from users where ID = ?");
		ps.setString(1, id);
		ResultSet rs = ps.executeQuery();
		User user = null;
		if (rs.next()) {
			user = new User();
			user.id = rs.getString(1);
			user.name = rs.getString(2);
			user.comments = rs.getString(3);
		}
		ps.close();
		rs.close();
		return user;
	}

	public synchronized User saveUser(User user) throws Exception {
		PreparedStatement ps = null;
		if (user.id == null || user.id.equals("")) {
			String sql = null;
			int id1 = 0;
			if (isIdAutoincremented()) {
				sql = (new StringBuffer("insert into ")).append(mSchema)
						.append("users (NAME, COMMENTS) values (?, ?)")
						.toString();
			} else {
				id1 = 1;
				sql = (new StringBuffer("insert into ")).append(mSchema)
						.append("users (ID, NAME, COMMENTS) values (?, ?, ?)")
						.toString();
			}
			ps = mConn.prepareStatement(sql);
			if (!isIdAutoincremented()) {
				user.id = getId();
				ps.setString(1, user.id);
			}
			ps.setString(1 + id1, user.name);
			ps.setString(2 + id1, user.comments);
		} else {
			ps = mConn.prepareStatement("update " + mSchema
					+ "users set NAME = ?, COMMENTS = ? where ID = ?");
			ps.setString(1, user.name);
			ps.setString(2, user.comments);
			ps.setString(3, user.id);
		}
		ps.execute();
		ps.close();
		if (user.id == null || user.id.equals(""))
			user.id = getLastInsertedId();
		mConn.commit();
		return user;
	}

	public synchronized Vector getUserList(String name, String comments,
			String orderBy) throws Exception {
		StringBuffer sb = new StringBuffer(
				"select ID, NAME, COMMENTS from users");
		boolean where = false;
		name = OlivaUtils.nvl(name);
		if (!name.equals("")) {
			sb.append(" where NAME like ?");
			where = true;
		}
		comments = OlivaUtils.nvl(comments);
		if (!comments.equals("")) {
			if (where)
				sb.append(" and ");
			else {
				sb.append(" where ");
				where = true;
			}
			sb.append("COMMENTS like ?");
		}
		orderBy = OlivaUtils.nvl(orderBy);
		if (!orderBy.equals(""))
			sb.append(orderBy);

		PreparedStatement ps = mConn.prepareStatement(sb.toString());

		int pos = 0;
		if (!name.equals("")) {
			pos++;
			ps.setString(pos, "%" + name + "%");
		}
		if (!comments.equals("")) {
			pos++;
			ps.setString(pos, "%" + comments + "%");
		}

		Vector result = new Vector(10);
		ResultSet rs = ps.executeQuery();
		while (rs.next()) {
			User user = new User();
			user.id = rs.getString(1);
			user.name = rs.getString(2);
			user.comments = rs.getString(3);
			result.add(user);
		}
		rs.close();
		ps.close();
		return result;
	}

	public synchronized void deleteUser(String id) throws Exception {
		PreparedStatement ps = mConn.prepareStatement("delete from " + mSchema
				+ "users where ID = ?");
		ps.setString(1, id);
		ps.execute();
		ps.close();
		mConn.commit();
	}

	public synchronized Account saveAccount(Account account) throws Exception {
		PreparedStatement ps = null;
		if (account.id == null || account.id.equals("")) {
			String sql = null;
			int id1 = 0;
			if (isIdAutoincremented()) {
				sql = (new StringBuffer("insert into "))
						.append(mSchema)
						.append(
								"accounts (USER_ID, DOMAIN_ID, ACCOUNT, COMMENTS) values (?, ?, ?, ?)")
						.toString();
			} else {
				id1 = 1;
				sql = (new StringBuffer("insert into "))
						.append(mSchema)
						.append(
								"accounts (ID, USER_ID, DOMAIN_ID, ACCOUNT, COMMENTS) values (?, ?, ?, ?, ?)")
						.toString();
			}
			ps = mConn.prepareStatement(sql);
			if (!isIdAutoincremented()) {
				account.id = getId();
				ps.setString(1, account.id);
			}
			ps.setString(1 + id1, account.userId);
			ps.setString(2 + id1, account.domainId);
			ps.setString(3 + id1, account.account);
			ps.setString(4 + id1, account.comments);
		} else {
			ps = mConn
					.prepareStatement("update "
							+ mSchema
							+ "accounts set USER_ID = ?, DOMAIN_ID = ?, ACCOUNT = ?, COMMENTS = ? where ID = ?");
			ps.setString(1, account.userId);
			ps.setString(2, account.domainId);
			ps.setString(3, account.account);
			ps.setString(4, account.comments);
			ps.setString(5, account.id);
		}
		ps.execute();
		ps.close();
		if (account.id == null || account.id.equals(""))
			account.id = getLastInsertedId();
		mConn.commit();
		return account;
	}

	public synchronized void deleteAccount(String id) throws Exception {
		PreparedStatement ps = mConn.prepareStatement("delete from " + mSchema
				+ "accounts where ID = ?");
		ps.setString(1, id);
		ps.execute();
		ps.close();
		mConn.commit();
	}

	public synchronized Account getAccount(String id) throws Exception {
		PreparedStatement ps = mConn
				.prepareStatement("select ID, USER_ID, DOMAIN_ID, ACCOUNT, COMMENTS from accounts where ID = ?");
		ps.setString(1, id);
		ResultSet rs = ps.executeQuery();
		Account acnt = null;
		if (rs.next()) {
			acnt = new Account();
			acnt.id = rs.getString(1);
			acnt.userId = rs.getString(2);
			acnt.domainId = rs.getString(3);
			acnt.account = rs.getString(4);
			acnt.comments = rs.getString(5);
		}
		ps.close();
		rs.close();
		return acnt;
	}

	public synchronized Vector getAccountList(String user_id, String domain_id, String account,
			String comments, String orderBy) throws Exception {
		StringBuffer sb = new StringBuffer(
				"select a.ID, a.USER_ID, a.DOMAIN_ID, a.ACCOUNT, a.COMMENTS, u.NAME, d.NAME from accounts a, users u, domains d where a.USER_ID = u.ID and a.DOMAIN_ID = d.ID");
		boolean where = true;
		user_id = OlivaUtils.nvl(user_id);
		if (!user_id.equals("")) {
			if (where)
				sb.append(" and ");
			else {
				sb.append(" where ");
				where = true;
			}			
			sb.append("USER_ID = ?");
		}
		domain_id = OlivaUtils.nvl(domain_id);
		if (!domain_id.equals("")) {
			if (where)
				sb.append(" and ");
			else {
				sb.append(" where ");
				where = true;
			}
			sb.append("DOMAIN_ID = ?");
		}
		account = OlivaUtils.nvl(account);
		if (!account.equals("")) {
			if (where)
				sb.append(" and ");
			else {
				sb.append(" where ");
				where = true;
			}
			sb.append("ACCOUNT like ?");
		}
		comments = OlivaUtils.nvl(comments);
		if (!comments.equals("")) {
			if (where)
				sb.append(" and ");
			else {
				sb.append(" where ");
				where = true;
			}
			sb.append("COMMENTS like ?");
		}
		orderBy = OlivaUtils.nvl(orderBy);
		if (!orderBy.equals(""))
			sb.append(orderBy);

		PreparedStatement ps = mConn.prepareStatement(sb.toString());

		int pos = 0;
		if (!user_id.equals("")) {
			pos++;
			ps.setString(pos, user_id);
		}		
		if (!domain_id.equals("")) {
			pos++;
			ps.setString(pos, domain_id);
		}
		if (!account.equals("")) {
			pos++;
			ps.setString(pos, "%" + account + "%");
		}
		if (!comments.equals("")) {
			pos++;
			ps.setString(pos, "%" + comments + "%");
		}

		Vector result = new Vector(10);
		ResultSet rs = ps.executeQuery();
		while (rs.next()) {
			Account acnt = new Account();
			acnt.id = rs.getString(1);
			acnt.userId = rs.getString(2);
			acnt.domainId = rs.getString(3);
			acnt.account = rs.getString(4);
			acnt.comments = rs.getString(5);
			acnt.userName = rs.getString(6);
			acnt.domainName = rs.getString(7);
			result.add(acnt);
		}
		rs.close();
		ps.close();
		return result;
	}

	public synchronized void setDatabaseUrl(String URL) {
		mConnString = URL;
	}

	public synchronized void setSchema(String schema) {
		mSchema = OlivaUtils.nvl(schema).trim();
		if (!mSchema.equals(""))
			mSchema += ".";
	}
		
	public synchronized void setCredentials(String user, String pass) {
		mUser = user;
		mPass = pass;
	}
	
	public synchronized void logon() throws Exception {
		logon(mUser, mPass);
	}

	public synchronized String execSqlArray(String[] sqls) {
		StringBuffer sb = new StringBuffer();
		for (int i = 0; i < sqls.length; i++) {
			try {
				java.sql.Statement stmt = mConn.createStatement();
				sb.append("Executing statement:\n");
				sb.append("\t").append(sqls[i]).append("\n");
				stmt.execute(sqls[i]);
				stmt.close();
				mConn.commit();
				sb.append("OK!\n");
			} catch (Exception exc) {
				sb.append(exc.getMessage() + "\n");
			}
		}
		return sb.toString();
	}	

	public synchronized String getSettingsValue(String name) throws Exception {
		PreparedStatement ps = mConn.prepareStatement("Select ID, VALUE from " + mSchema + "settings where NAME = ?");
		ps.setString(1, name);
		ResultSet rs = ps.executeQuery();
		String value = null;
		if (rs.next()) {
			value = rs.getString(2);
		}
		ps.close();
		rs.close();
		return value;
	}
		
	public synchronized int getSchemaVersion() throws Exception {
		int result = Storage.SCHEMA_EMPTY;
				
		DatabaseMetaData dbmd = mConn.getMetaData();		 
		ResultSet resultset = dbmd.getTables(null, null, "users", new String[]{"TABLE"});
		if (resultset.next()) {
			result = Storage.SCHEMA_1_5_0;
		}
		resultset.close();
		resultset = dbmd.getTables(null, null, "USERS", new String[]{"TABLE"});
		if (resultset.next()) {
			result = Storage.SCHEMA_1_5_0;
		}
		resultset.close();
		
		if(result == Storage.SCHEMA_EMPTY)
			return result;
				 
		resultset = dbmd.getTables(null, null, "settings", new String[]{"TABLE"});
		if (resultset.next()) {
			result = Storage.SCHEMA_1_5_1;
		}
		resultset.close();
		resultset = dbmd.getTables(null, null, "SETTINGS", new String[]{"TABLE"});
		if (resultset.next()) {
			result = Storage.SCHEMA_1_5_1;
		}
		resultset.close();

		if(result == Storage.SCHEMA_1_5_0)
			return result;		
		
		String schemaVer = getSettingsValue("schema_version");
		if(schemaVer == null)
			return Storage.SCHEMA_1_5_0;
			
		if (schemaVer.equals("1.5.1"))
			result = Storage.SCHEMA_1_5_1;
		else 
			result = Storage.SCHEMA_1_5_0;
		
		return result;		
	}
	
	public synchronized static int getReleaseVersion() {
		return SCHEMA_RELEASE;
	}

	public synchronized boolean needSchemaUpdate() throws Exception {
		return getSchemaVersion() != getReleaseVersion();
	}
	
	public synchronized void setSettingsValue(String name, String value) throws Exception {
		PreparedStatement ps = mConn.prepareStatement("select ID, VALUE from " + mSchema + "settings where NAME = ?");
		ps.setString(1, name);
		ResultSet rs = ps.executeQuery();
		String id = null;
		if (rs.next()) {
			id = rs.getString(1);
		}
		ps.close();
		rs.close();
		String sql = null;
		if(id == null) {
			int id1 = 0;
			if(isIdAutoincremented())
				sql = "INSERT INTO " + mSchema + "settings (NAME, VALUE) values (?, ?)";
			else {
				sql = "INSERT INTO " + mSchema + "settings (ID, NAME, VALUE) values (?, ?, ?)";
				id1 = 1;
			}
			ps = mConn.prepareStatement(sql);
			if (!isIdAutoincremented())
				ps.setString(1, getId());
			ps.setString(1 + id1, name);
			ps.setString(2 + id1, value);
		}
		else {
			sql = "UPDATE " + mSchema + "settings set NAME = ?, VALUE = ? where id = ?";
			ps = mConn.prepareStatement(sql);			
			ps.setString(1, name);
			ps.setString(2, value);
			ps.setString(3, id);
		}
		ps.execute();
		ps.close();
		mConn.commit();
	}
		
}