Guide to Object-oriented Programming With Java (University at Buffalo Version)

Chapter 15: Working With Java and a MySQL Database

Java and MySQL

The process using the Java Database Connectivity (JDBC) API to work with a database in Java involves connecting to the database, setting up a query, executing the query, working with the results, and closing the database connection.

Before using Eclipse to connect to a MySQL database you need to follow these steps to install the MySQL Connector:

Download the mysql-connector-java-5.1.41-bin.jar file (or the latest version) from http://dev.mysql.com/downloads/connector/j/ and save this to your c:\workspace\.

Open Eclipse

Right click on your Project
Choose Build Path -> Configure Build Path
Click the Library tab
Click: Add External JARs...
Browse to where you placed the extracted the mysql-connector-java-5.1.40 folder
Add the mysql-connector-java-5.1.40.jar file
Click OK to exit the Build Configuration dialog window

Figure 15-a: Using the MySQL Connector with Eclipse

Connecting to a Database

Before you can work with a MySQL database using Java you need to create a connection to your database. First, you need to use import java.sql.* to make the Java SQL utilities accessible to your program. Next you need to use try/catch to setup the SQL driver. Then you need to setup the variables that store the server name, database name, your username for that database, your password for that database, and the SQL Query string:

package edu.buffalostate.cis425.sp24put-your-username-here;
  
  import java.sql.*;
  
  public class ConnectToDatabase {
    
  public static void main(String[] args) {
    try {
      System.out.println("Loading JDBC driver...");
      Class.forName("com.mysql.jdbc.Driver");
      System.out.println("JDBC driver successfully loaded!");
    } catch (ClassNotFoundException e) {
      throw new RuntimeException(e);
    }
    String url = "jdbc:mysql://bscacad3.buffalostate.edu:3306/your-bsc-username";
    String username = "your-bsc-username";
    String password = "your-Banner-ID";

Figure 15-1: Connecting To a Database Code

Creating a Query

After you have successfully connected to your database you then need to create an SQL query to use to select the information you want from the database table or tables.

    String query = "SELECT firstName, lastName, DeptName, locationName "; 
           query = query + "FROM employees AS e "; 
           query = query + "JOIN departments AS d ON e.dept = d.departmentID "; 
           query = query + "JOIN locations AS l ON d.location = l.locationID "; 
           query = query + "ORDER BY e.lastName";

Figure 15-2: Creating a Query Code

Executing a Query

The next step is to execute your query.

    Connection connection = null; 
    Statement stmt = null;  
    try { 
      System.out.println("Connecting to the MySQL database..." + url + "\n"); 
      connection = DriverManager.getConnection(url, username, password); 
      System.out.println("\nMySQL Database connected!\n"); 
      stmt = connection.createStatement(); 
      ResultSet rs =stmt.executeQuery(query);

Figure 15-3: Executing a Query Code

Working With The Query Reuslts

If your query executes successfully you can then work with the results.

      while (rs.next()) { 
        System.out.format("%s %s works in %s located in %s\n", 
            rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4)); 
      }

Figure 15-4 Working With The Query Results Code

Closing The connection

Once you have finished processing the rsults you caan close your query statement and your database connection.

      stmt.close(); 
    } catch (SQLException e) { 
      System.out.println(e.toString()); 
    } finally { 
      System.out.println("\nClosing the connection."); 
      if (connection != null) { 
        try { 
          connection.close(); 
    } catch (SQLException ignore) { 
  } 
  } 
    System.exit(0); 
  } 
     } 
}

Figure 15-5: Closing The Query and Connection Code

`ConnectToDatabase.java` Output

When your Run your ConnectToDatabase.java program you should see this output in the Console window:

Loading JDBC driver...
JDBC driver successfully loaded!
Connecting to the MySQL database...jdbc:mysql://bscacad3.buffalostate.edu:3306/your-bsc-username

Sun Oct 29 14:51:38 EDT 2017 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.

MySQL Database connected!

Joe Cocker works in HR located in Paris, FR
Bob Dylan works in HR located in New York, NY
John Prine works in HR located in London, UK

Closing the connection.

Figure 15-6: ConnectToDatabase.class Output

You can safely ignore that warning about the bscacad3.buffalostate.edu server not having a secure certificate installed.

Task - Create a Java Table Page From a MySQL Table

package edu.buffalostate.cis425.sp24put-your-username-here;

/**
*
* File: MySQLTableRead.java
*
* For this assignment you will need to follow the steps in
* the Useful Tips section to add the MySQL library to your Build
* Path.
*
* First you need to login to your bscacad3.buffalostate.edu
* via putty and use MySQL to create the monthsTable and insert
* the data from the table below.
*
* Then create a Java that:
* 1. Connects to your bscacad3.buffalostate.edu
* MySQL account via port 3306 using your BSC
* username as the name of your database and then your BSC
* username as your MySQL username and with your Banner ID
* as your MySQL password (starts with a capital B).
* 2. Reads the monthsTable table
* 3. displays the following table:
*
* +----+------------+------+
* | No | Month Name | Days |
* +----+------------+------+
* | 1 | January | 31 |
* | 2 | February   | 28 |
* | 3 | March | 31 |
* | 4 | April | 30 |
* | 5 | May | 31 |
* | 6 | June | 30 |
* | 7 | July | 31 |
* | 8 | August | 31 |
* | 9 | September | 30 |
* | 10 | October    | 31 |
* | 11 | November | 30 |
* | 12 | December | 31 |
* +----+------------+------+
*
* @author put-your-name-here
* @version 1.0 yyyy-mm-dd
* @since 1.0
*/

import java.sql.*;

public class BSCMySQLMonths {

public static void main(String[] args) {

try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
throw new RuntimeException(e);
}

String url = "jdbc:mysql://bscacad3.buffalostate.edu:3306/put-your-bsc-username-here";
String username = "put-your-bsc-username-here";
String password = "put-your-Banner=id-here-with-a-capital-B";
String query = "put-yout-select-statement-here";

Connection connection = null;
Statement stmt = null;

try { // connect to the database
connection = DriverManager.getConnection(url, username, password);
System.out.println("MySQL Database connected!");
stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery(query);

// Hint: put code here to display table header

while (rs.next()) {
// Hint: put code here to display a row of the table
   }

   // Hint: put code here to display table footer line

stmt.close();
} catch (SQLException e) {
System.out.println(e.toString());
} finally {
System.out.println("Closing the connection.");
if (connection != null) {
try {
connection.close();
} catch (SQLException ignore) {
}
}
System.exit(0);
}
}
}

Figure 15-7: Sample MySQLTableRead.java Code

Let's get started with a MySQL Database Application!