JDBCTryWithResources.java

• Download JDBCTryWithResources.java (Right-click and Save Link As...)

• You will also need to create a new script to run this code.

Script

#! /bin/sh

java -classpath lib/jdbc_lab.jar:/home/vscode/tomcat/lib/mysql-connector-java-8.0.16.jar java112.project4.JDBCTryWithResources              

Java Code

package java112.project4;
import java.sql.*;

/**
 * This class demonstrates how to select employees from a database using JDBC.
 * It connects to a MySQL database, executes a query to select employees whose last name starts with a specified string,
 * and prints the results to the console.
 * <p>
 * This class also serves as an example of using try-with-resources to automatically close JDBC resources,
 * such as Connection, Statement, and ResultSet.
 * </p>
 *
 * @author Kari Schumacher
 */
public class JDBCTryWithResources {

    /**
     * Runs the sample code to select employees from the database.
     * <p>
     * This method connects to the database, executes a query to select employees whose last name starts with a specified string,
     * and prints the results to the console.
     * </p>
     */
    public void runSample() {

        // STEP 1: Load the JDBC Driver
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException classNotFoundException) {
            System.out.println("Unable to load the MySQL JDBC driver.");
            classNotFoundException.printStackTrace();
            return;
        }


        // Step 2 & 3: Establish the connection and create a PreparedStatement object
        try (Connection connection = establishConnection();
             PreparedStatement preparedStatement = prepareStatement(connection)) {

            // Set parameters for the prepared statement
            setPreparedStatementParameters(preparedStatement, "Smith");
            // Step 4: Execute query and process results
            executeQueryAndProcessResults(preparedStatement);

        } catch (SQLException sqlException) {
            System.out.println("There was a problem executing the select SQL.");
            sqlException.printStackTrace();
        } catch (Exception exception) {
            System.out.println("General Error");
            exception.printStackTrace();
        }

    }

    /**
     * Establishes a connection to the database.
     *
     * @return the database connection
     * @throws SQLException if a sql operation or database access error occurs 
     */
    private Connection establishConnection() throws SQLException {
        return DriverManager.getConnection("jdbc:mysql://localhost/student", "student", "student");
    }

    /**
     * Prepares the SQL statement.
     *
     * @param connection the database connection
     * @return the prepared statement
     * @throws SQLException if a sql operation or database access error occurs 
     */
    private PreparedStatement prepareStatement(Connection connection) throws SQLException {
        return connection.prepareStatement("SELECT emp_id, first_name, last_name FROM employees WHERE last_name LIKE ?");
    }

    /**
     * Sets parameters for the prepared statement.
     *
     * @param preparedStatement the prepared statement
     * @param lastName           the last name to search for
     * @throws SQLException if a sql operation or database access error occurs 
     */
    private void setPreparedStatementParameters(PreparedStatement preparedStatement, String lastName) throws SQLException {
        preparedStatement.setString(1, lastName + "%");
    }

    /**
     * Executes the query and processes the results.
     *
     * @param preparedStatement the prepared statement
     * @throws SQLException if a sql operation or database access error occurs 
     */
    private void executeQueryAndProcessResults(PreparedStatement preparedStatement) throws SQLException {
        try (ResultSet resultSet = preparedStatement.executeQuery()) {
            while (resultSet.next()) {
                String employeeId = resultSet.getString("emp_id");
                String firstName = resultSet.getString("first_name");
                String lastName = resultSet.getString("last_name");
                System.out.println(" Row: " + employeeId + " "
                        + firstName + " " + lastName);
            }
            printResultSetMetadata(resultSet);
        }
    }

    /**
     * Prints metadata about the result set.
     *
     * @param resultSet the result set
     * @throws SQLException if a sql operation or database access error occurs 
     */
    private void printResultSetMetadata(ResultSet resultSet) throws SQLException {
        ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
        int columns = resultSetMetaData.getColumnCount();
        String nameOne = resultSetMetaData.getColumnName(1);
        String typeOne = resultSetMetaData.getColumnTypeName(1);
        String labelOne = resultSetMetaData.getColumnLabel(1);
        System.out.println(" Column count : " + columns);
        System.out.println(" Column 1 name : " + nameOne);
        System.out.println(" Column 1 type : " + typeOne);
        System.out.println(" Column 1 label name : " + labelOne);
        System.out.println();
    }

    /**
     * The main method to execute the JDBCTryWithResources class.
     *
     * @param args The command line arguments. This program does not use any command line arguments.
     */
    public static void main(String[] args) {
        JDBCTryWithResources jdbcTryWithResources = new JDBCTryWithResources();
        jdbcTryWithResources.runSample();
    }
}