storage.java

一个简单的java邮件系统

/*
 * @(#)Storage.java
 *
 * Copyright (C) 2006 Sergey Bredikhin
 *
 * This program is free software; you can redistribute it and/or
 * modify it under the terms of the GNU General Public License as
 * published by the Free Software Foundation; either version 2 of
 * the License, or (at your option) any later version.
 * This program is distributed in the hope that it will be
 * useful, but WITHOUT ANY WARRANTY; without even the implied
 * warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR
 * PURPOSE. See the GNU General Public License for more details.
 * You should have received a copy of the GNU General Public
 * License along with this program; if not, write to the Free
 * Software Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139,
 * USA.
 */

package olivax.webmail;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Vector;

import oliva.common.OlivaUtils;

public abstract class Storage {

	public static final int SCHEMA_EMPTY = 0;
	
	public static final int SCHEMA_1_5_0 = 1;
	
	public static final int SCHEMA_1_5_1 = 2;
	
	public static final int SCHEMA_RELEASE = SCHEMA_1_5_1;	
		
	protected boolean driverRegistered = false;	
	
	protected Connection mConn = null;

	protected String mUser = null;

	protected String mPass = null;

	protected String mConnString = null;

	protected String mSchema = null;

	protected String mUserId = null;
	
	protected String mUserEmail = null;
	
	protected boolean mUserCreateIfNone = false;

	public abstract void logon(String user, String pass) throws Exception;	

	public abstract void close() throws Exception;

	protected abstract boolean isIdAutoincremented();

	protected abstract String getLastInsertedId() throws Exception;

	protected abstract String getId() throws Exception;

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

	public void setAppUser(String userEmail,
			boolean createIfNone) {
		mUserEmail = userEmail;
		mUserCreateIfNone = createIfNone;
	}	
		
	private String getUserId() throws Exception {
		if(mUserId != null)
			return mUserId;
		
		String account = "";
		String domain = "";

		if (mUserEmail == null)
			mUserEmail = "";

		int idx = mUserEmail.indexOf('@');
		if (idx == -1)
			throw new InvalidAddress(mUserEmail);
		account = mUserEmail.substring(0, idx);
		domain = mUserEmail.substring(idx + 1);

		PreparedStatement ps = mConn
				.prepareStatement("select u.ID from USERS u, ACCOUNTS a, DOMAINS d where a.USER_ID = u.ID and a.DOMAIN_ID = d.ID and a.ACCOUNT = ? and d.NAME = ?");
		ps.setString(1, account);
		ps.setString(2, domain);

		String userId = null;
		ResultSet rs = ps.executeQuery();
		if (rs.next())
			userId = rs.getString(1);
		rs.close();
		ps.close();

		if (userId == null && mUserCreateIfNone) {
			userId = createUser(mUserEmail);
		}

		mUserId = userId;
		
		if(mUserId == null)
			throw new UserNotFound();
		
		return mUserId;
	}

	public synchronized String createUser(String emailAddress) throws Exception {
		try {
			String account = "";
			String domain = "";

			if (emailAddress == null)
				emailAddress = "";

			int idx = emailAddress.indexOf('@');
			if (idx == -1)
				throw new InvalidAddress(emailAddress);
			account = emailAddress.substring(0, idx);
			domain = emailAddress.substring(idx + 1);
			PreparedStatement ps = mConn
					.prepareStatement("select ID from DOMAINS WHERE NAME = ?");
			ps.setString(1, domain);
			String domainId = null;
			ResultSet rs = ps.executeQuery();
			if (rs.next())
				domainId = rs.getString(1);
			rs.close();
			ps.close();

			if (domainId == null) {
				ps = mConn
						.prepareStatement("insert into DOMAINS (NAME, COMMENTS) values (?, 'CREATED AUTOMATICALLY')");
				ps.setString(1, domain);
				ps.execute();
				ps.close();
				ps = mConn
						.prepareStatement("select ID from DOMAINS WHERE NAME = ?");
				ps.setString(1, domain);
				rs = ps.executeQuery();
				if (rs.next())
					domainId = rs.getString(1);
				rs.close();
				ps.close();
			}

			ps = mConn
					.prepareStatement("select USER_ID from ACCOUNTS WHERE ACCOUNT = ? and DOMAIN_ID = ?");
			ps.setString(1, account);
			ps.setString(2, domainId);
			String userId = null;
			rs = ps.executeQuery();
			if (rs.next())
				userId = rs.getString(1);
			rs.close();
			ps.close();

			if (userId == null) {
				ps = mConn
						.prepareStatement("insert into USERS (NAME, COMMENTS) values (?, 'CREATED AUTOMATICALLY')");
				ps.setString(1, account + "@" + domain);
				ps.execute();
				ps.close();

				ps = mConn
						.prepareStatement("select ID from USERS WHERE NAME = ?");
				ps.setString(1, account + "@" + domain);
				rs = ps.executeQuery();
				if (rs.next())
					userId = rs.getString(1);
				rs.close();
				ps.close();
				ps = mConn
						.prepareStatement("insert into ACCOUNTS (USER_ID, DOMAIN_ID, ACCOUNT, COMMENTS) values (?, ?, ?, 'CREATED AUTOMATICALLY')");
				ps.setString(1, userId);
				ps.setString(2, domainId);
				ps.setString(3, account);
				ps.execute();
				ps.close();
			}
			mConn.commit();
			return userId;
		} catch (Exception exc) {
			try {
				mConn.rollback();
			} catch (Exception e) {
				e.printStackTrace();
			}
			throw exc;
		}
	}

	public synchronized Address getAddress(String id) throws Exception {
		PreparedStatement ps = mConn
				.prepareStatement("select ID, USER_ID, NAME, EMAIL, COMMENTS from addresses where ID = ? and USER_ID = ?");
		ps.setString(1, id);
		ps.setString(2, getUserId());
		ResultSet rs = ps.executeQuery();
		Address addr = null;
		if (rs.next()) {
			addr = new Address();
			addr.id = rs.getString(1);
			addr.userId = rs.getString(2);
			addr.name = rs.getString(3);
			addr.email = rs.getString(4);
			addr.comments = rs.getString(5);
		}
		ps.close();
		rs.close();
		return addr;
	}

	public synchronized Vector getAddressList(String searchString,
			String orderBy) throws Exception {
		return getAddressList(searchString, searchString, searchString, orderBy);
	}

	public synchronized Vector getAddressList(String name, String email,
			String comments, String orderBy) throws Exception {
		StringBuffer sb = new StringBuffer(
				"select ID, USER_ID, NAME, EMAIL, COMMENTS from addresses where USER_ID = ?");

		name = OlivaUtils.nvl(name);
		if (!name.equals(""))
			sb.append(" and NAME like ?");
		email = OlivaUtils.nvl(email);
		if (!email.equals(""))
			sb.append(" and EMAIL like ?");
		comments = OlivaUtils.nvl(comments);
		if (!comments.equals(""))
			sb.append(" and COMMENTS like ?");
		orderBy = OlivaUtils.nvl(orderBy);
		if (!orderBy.equals(""))
			sb.append(orderBy);

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

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

		Vector result = new Vector(10);
		ResultSet rs = ps.executeQuery();
		while (rs.next()) {
			Address addr = new Address();
			addr.id = rs.getString(1);
			addr.userId = rs.getString(2);
			addr.name = rs.getString(3);
			addr.email = rs.getString(4);
			addr.comments = rs.getString(5);
			result.add(addr);
		}
		rs.close();
		ps.close();
		return result;
	}

	public synchronized Address saveAddress(Address addr) throws Exception {
		PreparedStatement ps = null;
		addr.userId = getUserId();
		if (addr.id == null || addr.id.equals("")) {
			String sql = null;
			int id1 = 0;
			if (isIdAutoincremented()) {
				sql = (new StringBuffer("insert into "))
						.append(mSchema)
						.append(
								"addresses (USER_ID, NAME, EMAIL, COMMENTS) values (?, ?, ?, ?)")
						.toString();
			} else {
				id1 = 1;
				sql = (new StringBuffer("insert into "))
						.append(mSchema)
						.append(
								"addresses (ID, USER_ID, NAME, EMAIL, COMMENTS) values (?, ?, ?, ?, ?)")
						.toString();
			}
			ps = mConn.prepareStatement(sql);
			if (!isIdAutoincremented()) {
				addr.id = getId();
				ps.setString(1, addr.id);
			}
			ps.setString(1 + id1, addr.userId);
			ps.setString(2 + id1, addr.name);
			ps.setString(3 + id1, addr.email);
			ps.setString(4 + id1, addr.comments);
		} else {
			ps = mConn
					.prepareStatement("update "
							+ mSchema
							+ "addresses set USER_ID = ?, NAME = ?, EMAIL = ?, COMMENTS = ? where ID = ?");
			ps.setString(1, addr.userId);
			ps.setString(2, addr.name);
			ps.setString(3, addr.email);
			ps.setString(4, addr.comments);
			ps.setString(5, addr.id);
		}
		ps.execute();
		ps.close();
		if (addr.id == null || addr.id.equals(""))
			addr.id = getLastInsertedId();
		mConn.commit();
		return addr;
	}

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

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

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

	public synchronized Vector getDomainList(String name, String comments,
			String orderBy) throws Exception {
		StringBuffer sb = new StringBuffer(
				"select ID, NAME, COMMENTS from domains");
		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++;