Example for Johnatan56

Johnatan56_Example.java

// --- Main.java --------------------------------------------------------
import java.util.ArrayList;

public class Main 
{
  public static void main(String[] args)
  {
    ArrayList<Rental> rentals = SQLiteDB.getAllRecordsFor(Query.RENTALS);
    for (Rental rental : rentals)
    {
      System.out.println(rental);
    }  
    
    ArrayList<Movie> movies = SQLiteDB.getAllRecordsFor(Query.MOVIES);
    for (Movie movie : movies)
    {
      System.out.println(movie);
    }  
  }
}

// --- Here's the output from this: --------------------------------------------------------
[rent_id: '0', movie_id: '1', username: '450', withdrawn: '�565-03-15', returned: 'Optional.empty']
[rent_id: '0', movie_id: '4', username: '450', withdrawn: '�565-03-15', returned: 'Optional.empty']
[movie_id: '1', category_id: '2', location_id: '1', movie_name: 'The Umpire Strikes Back', movie_publisher: 'null', movie_date: '2015-01-01']
[movie_id: '2', category_id: '5', location_id: '1', movie_name: 'Balls of Fury', movie_publisher: 'null', movie_date: '2016-01-01']
[movie_id: '3', category_id: '4', location_id: '1', movie_name: 'Bridget Jones's Diarrhea', movie_publisher: 'null', movie_date: '2015-01-01']
[movie_id: '4', category_id: '5', location_id: '1', movie_name: 'Dumb and Dumberer', movie_publisher: 'null', movie_date: '2016-01-01']
[movie_id: '5', category_id: '3', location_id: '1', movie_name: 'Golden I-pod', movie_publisher: 'null', movie_date: '2015-01-01']
[movie_id: '6', category_id: '6', location_id: '1', movie_name: 'Harry Pothead and the Bong of Fire', movie_publisher: 'null', movie_date: '2016-01-01']
[movie_id: '7', category_id: '1', location_id: '1', movie_name: 'I See Stupid People', movie_publisher: 'null', movie_date: '2015-01-01']
[movie_id: '8', category_id: '3', location_id: '1', movie_name: 'Juwanna Mann', movie_publisher: 'null', movie_date: '2015-01-01']
[movie_id: '9', category_id: '6', location_id: '1', movie_name: 'Parrots of the Coffee-bean', movie_publisher: 'null', movie_date: '2016-01-01']
[movie_id: '10', category_id: '1', location_id: '1', movie_name: 'Who's your Caddy?', movie_publisher: 'null', movie_date: '2015-01-01']
[movie_id: '11', category_id: '7', location_id: '1', movie_name: 'You only Burp Thrice', movie_publisher: 'null', movie_date: '2015-01-01']

// --- Rental.java --------------------------------------------------------
import java.sql.ResultSet;
import java.util.Date;
import java.util.Optional;

public final class Rental 
{
    private Integer rent_id;
    private Integer movie_id;
    private String username;
    private Date withdrawn;
    
    // Optional type -- used for properties that are permitted to be null
    private Optional<Date> returned; 
    
    public Rental(int rentid, int movieid, String username, Date withdrawn, Date returned)
    {
      assert username != null || withdrawn != null;
      this.rent_id = rentid;
      this.movie_id = movieid;
      this.username = username;
      this.withdrawn = withdrawn;
      this.returned = (returned == null) ? Optional.empty() : Optional.of(returned);
    }
    
    // getters
    public Integer getRent_id() {
      return this.rent_id;
    }
    
    public Integer getMovie_id() {
      return this.movie_id;
    }
    
    public String getUsername() {
      return this.username;
    }

    public Date getWithdrawn() {
      return this.withdrawn;
    }

    public Optional<Date> getReturned() {
      return this.returned;
    }

    public static Rental newFromSQLRS(ResultSet rs)
    {
      Rental result = null;
      if (rs != null)
      {       
        int rentid = SQLiteDB.convertInteger(rs,"rent_id");
        String username = SQLiteDB.convertString(rs, "username");
        int movieid = SQLiteDB.convertInteger(rs, "movie_id");
        Date withdrawn = SQLiteDB.convertDate(rs, "withdrawn");
        Date returned = SQLiteDB.convertDate(rs, "returned");
        result = new Rental(rentid, movieid, username, withdrawn, returned);
      }
      return result;
    }

    @Override
    public String toString() 
    {       
      return "[rent_id: '" + this.rent_id + "', movie_id: '" + this.movie_id + 
          "', username: '" + this.username + "', withdrawn: '" + this.withdrawn +
          "', returned: '" + this.returned + "']";
    }     
}
 
// --- Movie.java -------------------------------------------------------- 
import java.sql.ResultSet;
import java.util.Date;

public class Movie {
  private Integer movie_id; 
  private Integer category_id;
  private Integer location_id;
  private String movie_name;

  private String movie_publisher;
  private Date movie_date;
  
  public Integer getMovie_id() {
    return this.movie_id;
  }

  public Integer getCategory_id() {
    return this.category_id;
  }

  public Integer getLocation_id() {
    return this.location_id;
  }

  public String getMovie_name() {
    return this.movie_name;
  }

  public String getMovie_publisher() {
    return this.movie_publisher;
  }

  public Date getMovie_date() {
    return this.movie_date;
  }

  public Movie(int movieid, int categoryid, int locationid, String name, String publisher, Date moviedate)
  {
    assert name != null || publisher != null || moviedate != null;
    this.movie_id = movieid;
    this.category_id = categoryid;
    this.location_id = locationid;
    this.movie_name = name;
    this.movie_date = moviedate;
  }
 
  public static Movie newFromSQLRS(ResultSet rs)
  {
    Movie result = null;
    if (rs != null)
    {       
      int movieid = SQLiteDB.convertInteger(rs, "movie_id");
      int categoryid = SQLiteDB.convertInteger(rs, "category_id");
      int locationid = SQLiteDB.convertInteger(rs, "location_id");
      String name = SQLiteDB.convertString(rs, "name");
      String publisher = SQLiteDB.convertString(rs, "publisher");
      Date moviedate = SQLiteDB.convertDate(rs, "date");
      result = new Movie(movieid, categoryid, locationid, name, publisher, moviedate);
    }
    return result;
  }
  
  @Override
  public String toString() {
    return "[movie_id: '" + this.movie_id + "', category_id: '" + this.category_id + 
        "', location_id: '" + this.location_id + "', movie_name: '" + this.movie_name + 
        "', movie_publisher: '" + this.movie_publisher + "', movie_date: '" + this.movie_date + "']";
  }
}

// --- SQLType.java --------------------------------------------------------
public enum SQLType 
{
  DATE, INT, STRING
}

// --- Query.java --------------------------------------------------------
public enum Query
{
  RENTALS, MOVIES
} 

// --- SQLite.java --------------------------------------------------------
import java.sql.*;

// Singleton class for SQLite connection
public final class SQLite
{
  private static final String dbPath = "/pathto/MontsRental.db";
  private static Connection connection = null;
  
  protected SQLite() 
  {
     // Defeat instantiation.
  }
  
  public static Connection getConnection() 
  {
    // Force reconnect if connection is closed (by setting connection to null).
    try
    {
      if (connection != null)
      {
        if (connection.isClosed())
        {
          connection = null;
        }
      }
    }
    catch (SQLException ex)
    {
      connection = null;
      ex.printStackTrace();
    }
    
    // Establish new connection if connection == null
    if(connection == null) 
    {
     try 
     {
      connection = DriverManager.getConnection("jdbc:sqlite:" + dbPath);
     } 
     catch (SQLException ex) 
     {
       ex.printStackTrace();
     }
    }
    return connection;
  }
  
  public static void Close()
  {
    try 
    {
      connection = null;
      connection.close();
    } 
    catch (SQLException e) 
    {
      connection = null;
      e.printStackTrace();
    }
  }
}

// --- SQLiteDB.java --------------------------------------------------------
import java.sql.*;
import java.util.ArrayList;

public final class SQLiteDB 
{
  // Generic SQL Type conversion
  @SuppressWarnings("unchecked")
  private static <T> T convert(ResultSet rs, String columnName, SQLType sqlType)
  {
    Object result = sqlType == SQLType.INT ? Integer.MIN_VALUE : null;
    try
    {
      switch (sqlType) 
      {
      case DATE:
        result = rs.getDate(columnName);
        break;
      case INT:
        result = Integer.valueOf(rs.getInt(columnName));
        break;
      case STRING:
        result = rs.getString(columnName);
        break;
      default:
        throw new IllegalArgumentException("Unmatched SQLType");
      }
    } 
    catch (Exception ex)
    {
      ex.printStackTrace();
    }
    return (T)result;
  }
  
  public static <T> T convertInteger(ResultSet rs, String columnName)
  {
    return SQLiteDB.convert(rs, columnName, SQLType.INT);
  }
  
  public static <T> T convertString(ResultSet rs, String columnName)
  {
    return SQLiteDB.convert(rs, columnName, SQLType.STRING);
  }
  
  public static <T> T convertDate(ResultSet rs, String columnName)
  {
    return SQLiteDB.convert(rs, columnName, SQLType.DATE);
  }
  
  @SuppressWarnings("unchecked")
  private static <T> ArrayList<T> processRecordsFor(PreparedStatement srps, Query query) throws SQLException
  {
    ArrayList<T> result = new ArrayList<T>();
    if (srps != null)
    {
      ResultSet rs = srps.executeQuery();
      boolean recordsAvailable = rs.next();
      while (recordsAvailable)
      {
        switch (query)
        {
          case RENTALS:
            result.add((T) Rental.newFromSQLRS(rs));
            break;
          case MOVIES:
            result.add((T) Movie.newFromSQLRS(rs));
            break;
        }  
        recordsAvailable = rs.next();
      }
    }
    return result;
  }
  
  public static <T> ArrayList<T> getAllRecordsFor(Query query)
  {
    ArrayList<T> result = new ArrayList<T>();
    PreparedStatement srps = null;
    try
    {
      switch (query)
      {
      case RENTALS:
        srps = SQLite.getConnection().prepareStatement("SELECT * FROM rentals;");
        break;
      case MOVIES:
        srps = SQLite.getConnection().prepareStatement("SELECT * FROM movies;");
        break;
      }
      result = SQLiteDB.processRecordsFor(srps, query);
    }
    catch (Exception ex)
    {
      ex.printStackTrace();
    }
    return result;
  }
}