update example

ProceduresMySQL.java

package com.pluralsight.jdbc.course.m6c2;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class Procedures
{
    private static final String URL
        = "jdbc:mysql://localhost:3306/classicmodels?user=root&password=my-secret-pw";

    public static void main(String[] args) throws SQLException
    {
        String nameColumn = "Motorcycles";
        List<String> list = Procedures.listProductsBy(nameColumn);
        list.forEach(System.out::println);

        int numEmployee = 1002;
        String newEmail = "[email protected]";

        String oldEmail = Procedures.updateEmail(numEmployee, newEmail);

        if (oldEmail != null)
        {
            System.out.println("SUCCESS: update");
        } else
        {
            System.out.println("FAIL: occurred");
        }

    }

    public static List<String> listProductsBy(String productLine) throws SQLException
    {

        /*
        * DELIMITER $$
        USE `classicmodels`$$
        CREATE PROCEDURE `listProductsFor`(IN prodLine VARCHAR(100))
        BEGIN
            SELECT p.productCode,
                   p.productName,
                   p.productLine,
                   p.productScale,
                   p.productVendor,
                   p.productDescription,
                   p.quantityInStock,
                   p.buyPrice,
                   p.MSRP
            FROM products p
            WHERE p.productLine = prodLine;
        end $$
        * */

        List<String> nameList = new ArrayList<>();

        try (Connection connection = DriverManager.getConnection(URL);
             CallableStatement callableStatement =
                 connection.prepareCall("{call listProductsFor(?)}"))
        {
            callableStatement.setString(1, productLine);
            boolean success = callableStatement.execute();
            if (success)
            {
                try (ResultSet resultSet = callableStatement.getResultSet())
                {
                    while (resultSet.next())
                    {
                        String name = resultSet.getString("productName");
                        nameList.add(name);
                    }

                    return nameList;
                }
            }
        }
        return nameList;
    }

    public static String updateEmail(int employeeNumber, String newEmail) throws SQLException
    {
        /*
        * DROP procedure IF EXISTS `updateEmail`;
        DELIMITER $$
        USE `classicmodels`$$
        CREATE PROCEDURE `updateEmail`(IN emp_num INT, INOUT e_mail VARCHAR(100))
        BEGIN
            DECLARE old_email VARCHAR(100) DEFAULT 'UNKNOWN';
            SELECT email
            INTO old_email
            FROM employees
            WHERE employeeNumber = emp_num;
        
            UPDATE employees
            SET email = e_mail
            WHERE employeeNumber = emp_num;
        
            SET e_mail = old_email;
        end $$
        * */
        
        
        try (Connection connection = DriverManager.getConnection(URL);
             CallableStatement callableStatement =
                 connection.prepareCall("{call updateEmail(?, ?)}"))
        {
            callableStatement.setInt(1, employeeNumber);
            callableStatement.registerOutParameter(2, java.sql.Types.VARCHAR);
            callableStatement.setString(2, newEmail);

            callableStatement.execute();

            String oldEmail = callableStatement.getString(2);
            return oldEmail;
        }
    }
}