Boilerplate for the DBManager class.

gistfile1.java

package org.meetproj.model;

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

public final class DBManager {
	private static DBManager instance;

	/* Set this to true to use Eirik's MySQL server in Bergen, Norway instead of your local
	SQLite database. Everone else has access to this database as well! */
	private static final boolean USE_MYSQL = false;
	private static final String MYSQL_URI
	  = "jdbc:mysql://aud8.dyndns.org:3306/common_meetlab";
	private static final String SQLITE_URI_BASE = "jdbc:sqlite:";
	private static final String DB_USER = "meetuser";
	private static final String DB_PASS = "meetpass";

	private Connection connection;

	private DBManager() throws SQLException {
		try {
			Class.forName("org.sqlite.JDBC").newInstance();
			Class.forName("com.mysql.jdbc.Driver").newInstance();
		} catch (Exception e) {
			throw new RuntimeException(e);
		}

		String DB_URI = (USE_MYSQL) ? MYSQL_URI :
		  (SQLITE_URI_BASE +
		  new File(System.getProperty("user.home"), "limecat.sqlite").toString());
		System.out.println("Using DB " + DB_URI);
		connection = DriverManager.getConnection(DB_URI, DB_USER, DB_PASS);
		// Don't bother being to transactionally correct for this lab.
		connection.setAutoCommit(true);
	}

	public static DBManager getInstance() throws SQLException {
		if (instance == null)
			instance = new DBManager();
		return instance;
	}

	public void resetSchema() throws SQLException {
		Statement s = connection.createStatement();
		// Work around some DDL syntax differences between MySQL and SQLite. 
		String engine  = USE_MYSQL ? "ENGINE=InnoDB"  : "";
		String autoinc = USE_MYSQL ? "AUTO_INCREMENT" : "AUTOINCREMENT";
		try {
			// TODO: Add your tables below (one example is shown below).

			/* Note: With multiple tables, must drop in reverse of created order
			to avoid violating foreign key constraints. */
			s.execute("DROP TABLE IF EXISTS sometable;");

			s.execute(
				  "CREATE TABLE sometable ("
				+ "  id         INTEGER PRIMARY KEY " + autoinc + ","
				+ "  somestring VARCHAR(45)  NOT NULL,"
				+ "  someint    INTEGER      NOT NULL,"
				+ "  focusLng   DECIMAL(38)  NOT NULL"
				+ ") " + engine + ";"
			);
		} finally {
			s.close();
		}
	}

	public List<Video> loadVideos() throws SQLException {
		// TODO: Implement this method (when instructed in the lab description).
	}

	public Video insertVideo() throws SQLException {
		// TODO: Implement this method (when instructed in the lab description).
	}

	public void deleteVideo(Video v) throws SQLException {
		// TODO: Implement this method (when instructed in the lab description).
	}

	public void updateVideo(Video v) throws SQLException {
		// TODO: Implement this method (when instructed in the lab description).
	}
}