dbhandler.java.svn-base

rmi server web service for a stock track systerm

package ase.assignment.sts.db;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

import ase.assignment.sts.utils.STSConstants;

public class DBHandler {
	private Connection connection;
	private Statement statement;

	private static DBHandler instance = new DBHandler();

	private DBHandler() {
		System.out.println("DB URL=" + STSConstants.DB_URL);
		initDB();
	}

	public Statement createStatement() {
		try {
			Class.forName(STSConstants.DB_DRIVER);
			connection = DriverManager.getConnection(STSConstants.DB_URL,
					STSConstants.DB_USERNAME, STSConstants.DB_PASSWORD);
			statement = connection.createStatement();
		} catch (SQLException sqle) {
			System.err.println("Error creating connection");
		} catch (ClassNotFoundException cnfe) {
			System.err.println(cnfe.toString());
		}
		return statement;
	}

	public void dispose() {
		if (statement != null) {
			try {
				statement.close();
				statement = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (connection != null) {
			try {
				connection.close();
				connection = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public static DBHandler getInstance() {
		return instance;
	}

	private void initDB() {
		File file = new File(STSConstants.DERBY_PATH);
		if (file != null && !file.exists()) {
			System.out.println("Creating Database...");
			buildTables();
		}
	}

	/***************************************************************************
	 * This program expects to find a file with the name SQLStatements.txt in
	 * the same directory as the .class file for the program. This file should
	 * contain the SQL statements to be executed. The SQLStatements.txt file
	 * included with this program contains SQL that will create tables in the
	 * technical_library Access database that should initially be empty, and
	 * already registered as an ODBC database.
	 **************************************************************************/
	private void buildTables() {
		try {
			InputStream fis = DBHandler.class.getResource(
					STSConstants.DB_SQL_FILE).openStream();
			BufferedReader reader = new BufferedReader(new InputStreamReader(
					fis));

			Statement statement = createStatement();
			String SQLStatement = null;
			while ((SQLStatement = reader.readLine()) != null) {
				try {
					statement.executeUpdate(SQLStatement);
					System.out.println(SQLStatement);
				} catch (SQLException e) {
					System.err.println("Can't execute " + SQLStatement);

					// If drop fail, proceed to next sql
					if (!SQLStatement.startsWith("DROP")) {
						break;
					}
				}
			}
		} catch (FileNotFoundException cnfe) {
			System.err
					.println("SQL statements to create tables and their contents must be in/n"
							+ "a file with the name SQLStatements.txt in the same directory as this program file./n"
							+ "SQL statement must be one to a line.");
			System.err
					.println("File SQLStatements.txt does not exist. Terminating...");
		} catch (Exception e) {
			System.err.println(e);
		} finally {
			dispose();
		}
	}

	public static void main(String[] args) {
		DBHandler.getInstance().buildTables();
	}

}