oraclestorage.java

一个简单的java邮件系统

/*
 * @(#)OracleStorage.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.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import oracle.jdbc.driver.OracleDriver;

public class OracleStorage extends Storage {

	public OracleStorage() {
		super();
		mConn = null;
		mConnString = "jdbc:oracle:thin:@localhost:1521:SID";
		mSchema = "";
	}

	public synchronized void logon(String user, String pass) throws Exception {
		if (!driverRegistered) {
			DriverManager.registerDriver(new OracleDriver());
			driverRegistered = true;
		}		
		mConn = DriverManager.getConnection(mConnString, user, pass);
		mConn.setAutoCommit(false);
	}

	public synchronized void close() throws Exception {
		if (mConn == null)
			return;
		if (!mConn.isClosed()) {
			mConn.rollback();
			mConn.close();
		}
		mConn = null;
	}

	public synchronized String getLastInsertedId() throws Exception {
		String id = "";
		PreparedStatement ps = mConn.prepareStatement("select " + mSchema
				+ "owm_seq.currval from dual");
		ResultSet resultset = ps.executeQuery();
		if (resultset.next()) {
			id = resultset.getString(1);
		}
		ps.close();
		resultset.close();
		return id;
	}

	protected boolean isIdAutoincremented() {
		return false;
	}

	protected String getId() throws Exception {
		PreparedStatement ps = mConn.prepareStatement("select " + mSchema
				+ "owm_seq.nextval from dual");
		ResultSet resultset = ps.executeQuery();
		String id = null;
		if (resultset.next())
			id = resultset.getString(1);
		ps.close();
		resultset.close();
		return id;
	}

	public synchronized String populateSchema() throws Exception {
		String sqls151[] = {
				"CREATE TABLE users (ID NUMBER(10) NOT NULL, NAME VARCHAR2(255) NOT NULL, COMMENTS VARCHAR2(255), PRIMARY KEY  (ID))",
				"CREATE TABLE domains (ID NUMBER(10) NOT NULL, NAME VARCHAR2(255) NOT NULL, COMMENTS VARCHAR2(255), PRIMARY KEY  (ID))",
				"CREATE TABLE accounts (ID NUMBER(10) NOT NULL, USER_ID NUMBER(10) NOT NULL, DOMAIN_ID NUMBER(10) NOT NULL, ACCOUNT VARCHAR2(255) NOT NULL, COMMENTS VARCHAR2(255), PRIMARY KEY  (ID))",
				"ALTER TABLE accounts ADD CONSTRAINT acnt_user_fk FOREIGN KEY (USER_ID) REFERENCES users (ID)",
				"CREATE INDEX acnt_user_fk_i on accounts (USER_ID)",
				"ALTER TABLE accounts ADD CONSTRAINT acnt_dmn_fk FOREIGN KEY (DOMAIN_ID) REFERENCES domains (ID)",
				"CREATE INDEX acnt_dmn_fk_i on accounts (DOMAIN_ID)",
				"CREATE TABLE addresses (ID NUMBER(10) NOT NULL, USER_ID NUMBER(10) NOT NULL, NAME VARCHAR2(255) NOT NULL, EMAIL VARCHAR2(255) NOT NULL, COMMENTS VARCHAR2(255), PRIMARY KEY  (ID))",
				"ALTER TABLE addresses ADD CONSTRAINT addr_user_fk FOREIGN KEY (USER_ID) REFERENCES users (ID)",
				"CREATE INDEX addr_user_fk_i on addresses (USER_ID)",
				"CREATE TABLE settings (ID NUMBER(10) NOT NULL, NAME VARCHAR2(40) NOT NULL, VALUE VARCHAR2(40), PRIMARY KEY (ID))",
				"create sequence owm_seq nocache nominvalue nomaxvalue nocycle" 
		};
		StringBuffer sb = new StringBuffer();
		int schemaVer = getSchemaVersion();
		if(schemaVer == getReleaseVersion())
			return sb.toString();
		if (schemaVer == SCHEMA_EMPTY) {		
			sb.append(execSqlArray(sqls151));
			try {
				sb.append("Setting schema version to 1.5.1:\n");
				setSettingsValue("schema_version", "1.5.1");
				sb.append("OK!");
			} catch (Exception exc) {
				sb.append(exc.getMessage()).append("\n");
			}
		}
		return sb.toString();
	}
		
}