bsc Logo

Let's get started with a MySQL Database connection program!

  1. Open Eclipse
  2. Right-Click on your edu.buffalostate.cis425.fa19.exercises. package
  3. Choose: File -> New -> class
  4. In the Name: textbox type: Movies
  5. Click: Finish
  6. Copy the Movies.java Template (Java) code (below)
  7. Paste that code into your new Movies class in the editor
  8. Change both occurrences of put-your-lastname-here
  9. Replace the blank on the
    String password = "_____";
    line with:
    Temppw23
  10. After the
    while (rs.next()) {
    line, add these lines:
    System.out.println("\"" + rs.getString(2) + "\"");
    System.out.println("\tReleased on: " + rs.getString(3));
    System.out.println("\tDirected by: " + rs.getString(4) + ".");
  11. Click the Run icon
  12. Change
    Boolean DEBUG = false;
    to:
    Boolean DEBUG = true;
  13. Click the Run icon and observe the debug messages

Movies.java Template (Java)

package edu.buffalostate.cis425.fa19.exercises.put-your-lastname-here;

/**
 * File: Movies.java
 *
 * Write a program that connects to a MySQL database then selects the data and
 * displays the results.
 *
 * Fill in the blanks to make the program work 
 *
 */
/**
 *
 * @author put-your-name-here 
 *
 */

import java.sql.*;

public class Movies {
  
  public static void main( String args[] ) {
    Boolean DEBUG = false;
    try { 
      if (DEBUG) { System.out.println("Loading JDBC driver..."); }
      Class.forName("com.mysql.jdbc.Driver");
      if (DEBUG) { System.out.println("JDBC driver successfully loaded!"); }
    } catch (ClassNotFoundException e) {
      throw new RuntimeException(e);
    }
    String url = "jdbc:mysql://bscacad3.buffalostate.edu:3306/GERLANJR?useSSL=false";
    String username = "gerlanjr";
    String password = "________";
    String query = "SELECT movie_id, movie_name, movie_release, ";
    query += "people_fullname AS director, movietype_label ";
    query += "FROM moviesTable AS m ";
    query += "LEFT JOIN moviepeopleTable AS p ";
    query += "ON m.movie_director = p.people_id ";
    query += "LEFT JOIN movietypesTable AS t ";
    query += "ON m.movie_type = t.movietype_id ";
    query += "ORDER BY movie_release;";
    Connection connection = null;
    Statement stmt = null;

    try {
      if (DEBUG) { System.out.println("Connecting to the MySQL database..."); }
      connection = DriverManager.getConnection(url, username, password);
      if (DEBUG) { System.out.println("MySQL Database connected!"); }
      stmt = connection.createStatement();
      ResultSet rs = stmt.executeQuery(query);
      System.out.println();
      // Walk trough the resulting data set and display the data
      while (rs.next()) {
        _____________________________________________
        _____________________________________________
        _____________________________________________
      }
      stmt.close();
    } catch (SQLException e) {
      System.out.println(e.toString());
    } finally {
    	if (DEBUG) { System.out.println("Closing the connection."); }
        if (connection != null) {
          try {
             connection.close();
          } catch (SQLException ignore) {
        }
      }
    System.exit(0);
    }
  }
}

Movies Class Output

"To Kill a Mockingbird"
	Released on: 1963-05-19
	Directed by: Robert Mulligan.
"The Omen"
	Released on: 1976-06-25
	Directed by: Richard Donner.