<file_sep>package dbtLab3;

import java.sql.*;
import java.util.*;

/**
 * Database is a class that specifies the interface to the movie database. Uses
 * JDBC.
 */
public class Database {

	/**
	 * The database connection.
	 */
	private Connection conn;

	/**
	 * Create the database interface object. Connection to the database is
	 * performed later.
	 */
	public Database() {
		conn = null;
	}

	/**
	 * Open a connection to the database, using the specified user name and
	 * password.
	 */
	public boolean openConnection(String filename) {
		try {
			Class.forName("org.sqlite.JDBC");
			conn = DriverManager.getConnection("jdbc:sqlite:" + filename);
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			return false;
		}
		return true;
	}

	/**
	 * Close the connection to the database.
	 */
	public void closeConnection() {
		try {
			if (conn != null) {
				conn.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * Check if the connection to the database has been established
	 * 
	 * @return true if the connection has been established
	 */
	public boolean isConnected() {
		return conn != null;
	}

	public boolean userExists(String userId) {
		try {
			String sql = "SELECT * FROM users WHERE username = ?";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, userId);
			ResultSet rs = ps.executeQuery();
			return rs.next();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	public ArrayList<Movie> getMovies() {
		ArrayList<Movie> movies = new ArrayList<Movie>();
		try {
			String sql = "SELECT * FROM movies";
			PreparedStatement ps = conn.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				movies.add(new Movie(rs));
			}
			return movies;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return movies;
	}

	public ArrayList<String> getDates(String movieName) {
		ArrayList<String> dates = new ArrayList<String>();
		try {
			String sql = "SELECT date FROM performances WHERE name = ?";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, movieName);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				dates.add(rs.getString("date"));
			}
			return dates;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return dates;
	}

	public Performance getPerformance(String movieName, String date) {
		try {
			String sql = "SELECT * FROM performances WHERE name = ? AND date = ?";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, movieName);
			ps.setString(2, date);
			ResultSet rs = ps.executeQuery();
			return new Performance(rs);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	public boolean makeReservation(String movieName, String date) {
		try {
			conn.setAutoCommit(false);
			
			String sql = "SELECT available_seats FROM performances WHERE name = ? AND date = ?";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, movieName);
			ps.setString(2, date);
			ResultSet rs = ps.executeQuery();

			int seats = rs.getInt("available_seats");
			if (seats > 0) {
				sql = "UPDATE performances SET available_seats = ? WHERE name = ? AND date = ?";
				ps = conn.prepareStatement(sql);
				ps.setInt(1, seats - 1);
				ps.setString(2, movieName);
				ps.setString(3, date);
				ps.executeUpdate();

				sql = "INSERT INTO reservations (username, movie_name, date) VALUES (?, ?, ?)";
				ps = conn.prepareStatement(sql);
				ps.setString(1, CurrentUser.instance().getCurrentUserId());
				ps.setString(2, movieName);
				ps.setString(3, date);
				ps.executeUpdate();
				
				conn.commit();
				return true;
			} else {
				conn.rollback();
			}
			
			conn.setAutoCommit(true);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}
}
<file_sep># EDA216 - Database Technology
The labs for the course EDA216 - Database Technology
<file_sep>-- Delete the tables if they exist.
-- Disable foreign key checks, so the tables can
-- be dropped in arbitrary order.
PRAGMA foreign_keys=OFF;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS reservations;
DROP TABLE IF EXISTS performances;
DROP TABLE IF EXISTS movies;
DROP TABLE IF EXISTS theaters;

PRAGMA foreign_keys=ON;

-- Create the tables.
CREATE TABLE users (
  username      VARCHAR(15),
  name          TEXT NOT NULL,
  address       TEXT,
  phone_number  CHAR(10) NOT NULL,
  primary key (username)
);

CREATE TABLE performances (
  name    TEXT,
  date    DATE,
  theater_name TEXT,
  available_seats INTEGER check (available_seats >= 0),
  primary key (name, date),
  foreign key (name) references movies(name),
  foreign key (theater_name) references theaters(name)
);

CREATE TABLE reservations (
  reservation_number    INTEGER PRIMARY KEY,
  username              VARCHAR(15),
  movie_name            TEXT,
  date                  DATE,
  foreign key (movie_name, date) references performances(name, date),
  foreign key (username) references users(username)
);

CREATE TABLE movies (
  name    TEXT,
  primary key (name)
);

CREATE TABLE theaters (
  name    TEXT,
  seats   INTEGER NOT NULL CHECK (seats > 0),
  primary key (name)
);

-- Insert data into the tables.
INSERT INTO users (username, name, address, phone_number) VALUES
('andy', 'AndyTruong', 'LTH', 0789456321),
('andy2', 'Andy2', NULL, 0123456789),
('andy3123', 'Andy3', 'dawdaw', 0258695847);

INSERT INTO theaters (name, seats) VALUES
('Royal', 498),
('Filmstaden Entré', 60),
('Filmstaden Storgatan', 63);

INSERT INTO movies (name) VALUES
('<NAME>'),
('Allied'),
('American Pastoral'),
('Arrival'),
('Assassin''s Creed'),
('Bamse och häxans dotter'),
('Cosi fan tutte - opera från Parisoperan'),
('Elle'),
('En alldeles särskild dag - Klassiker'),
('En man som heter Ove'),
('En midommarnattsdröm - balett från Parisoperan'),
('Fifty shades darker'),
('Filmen om Badrock'),
('Fyren mellan haven'),
('Hundraettåringen som smet från notan och försvann'),
('Jackie'),
('Jag, <NAME>'),
('Jätten'),
('La La Land'),
('Lion'),
('Live by night'),
('Manchester by the sea'),
('Marcus & Martinus - Tillsammans mot drömmen'),
('<NAME>appa <NAME>'),
('<NAME>'),
('Passengers'),
('Pettson & Findus - Juligheter'),
('Resident Evil - The Final Chapter'),
('Rings'),
('Rogue One: A Star Wars Story'),
('Sing'),
('Skönheten i allt'),
('Split'),
('The Bye Bye Man'),
('The Handmaiden'),
('The Lego Batman Movie'),
('Trolls'),
('Trubaduren - opera från Royal Opera House'),
('Törnrosa - balett från Royal Opera House'),
('Vaiana'),
('V<NAME>'),
('xXx: The Return of Xander Cage');

INSERT INTO performances (name, date, theater_name, available_seats) VALUES
('La La Land', '2017-02-01', 'Royal', 496),
('La La Land', '2017-02-02', 'Filmstaden Storgatan', 63),
('Jätten', '2017-02-02', 'Filmstaden Storgatan', 63);

INSERT INTO reservations (username, movie_name, date) VALUES
('andy', 'La La Land', '2017-02-01'),
('andy2', 'La La Land', '2017-02-01');

-- And re-enable foreign key checks.

PRAGMA foreign_key = on;
<file_sep>package dbtLab3;

import java.sql.ResultSet;
import java.sql.SQLException;

public class Performance {
	public String movie;
	public String date;
	public String theater;
	public int available_seats;
	
	public Performance(ResultSet rs) throws SQLException {
		this.movie = rs.getString("name");
		this.date = rs.getString("date");
		this.theater = rs.getString("theater_name");
		this.available_seats = rs.getInt("available_seats");
	}
}
